-- ====================================================================
-- Banners for the 3 homepage designs (2026-09-27)
--   1. New placement `home_hero_side` (the two promo cards beside the hero
--      slider in the "Modern" design).
--   2. `layouts` — which homepage designs a banner shows on
--      (NULL = all designs).
--   3. Optional text overlay: `show_text` switches it on per banner; the
--      look is set per banner (`text_position`, `text_theme`,
--      `overlay_opacity`) and the words are per language in banner_images
--      (`eyebrow`, `title`, `subtitle`, `cta_label`). The CTA goes to the
--      banner's existing `link_url`.
--   4. Optional `mobile_image` per language (portrait crop for phones).
-- Safe to run more than once.
--   npm run db:migrate -- 2026-09-27_banners_home_layouts.sql
-- ====================================================================

SET NAMES utf8mb4;

-- 1. placement: add home_hero_side (MODIFY is idempotent)
ALTER TABLE `banners`
  MODIFY `placement` ENUM('home_top', 'home_hero_side', 'home_middle', 'home_bottom', 'sidebar', 'category_page')
  DEFAULT 'home_top';

-- 2-3. banner-level columns
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'banners' AND COLUMN_NAME = 'layouts');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `banners`
     ADD COLUMN `layouts` SET(''home1'', ''home2'', ''home3'') NULL DEFAULT NULL COMMENT ''Homepage designs this banner shows on; NULL = all'' AFTER `is_hero_banner`,
     ADD COLUMN `show_text` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''Render the per-language text + CTA over the image'' AFTER `layouts`,
     ADD COLUMN `text_position` ENUM(''start'', ''center'', ''end'') NOT NULL DEFAULT ''start'' AFTER `show_text`,
     ADD COLUMN `text_theme` ENUM(''light'', ''dark'') NOT NULL DEFAULT ''light'' COMMENT ''light = white text for dark images'' AFTER `text_position`,
     ADD COLUMN `overlay_opacity` TINYINT UNSIGNED NOT NULL DEFAULT 40 COMMENT ''0-80, darkens/lightens the image behind the text'' AFTER `text_theme`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3-4. per-language columns
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'banner_images' AND COLUMN_NAME = 'title');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `banner_images`
     ADD COLUMN `mobile_image` VARCHAR(500) NULL DEFAULT NULL COMMENT ''Cloudinary public_id; optional phone crop'' AFTER `image`,
     ADD COLUMN `eyebrow` VARCHAR(80) NULL DEFAULT NULL AFTER `alt_text`,
     ADD COLUMN `title` VARCHAR(160) NULL DEFAULT NULL AFTER `eyebrow`,
     ADD COLUMN `subtitle` VARCHAR(300) NULL DEFAULT NULL AFTER `title`,
     ADD COLUMN `cta_label` VARCHAR(60) NULL DEFAULT NULL AFTER `subtitle`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
