-- ======================================================================
-- FashCognitive Style Season Master — Phase 1: Foundation
-- Adds the Digital Season Workspace scaffolding:
--   * season_calendars        — 10 milestone calendar (feeds TNA)
--   * season_collections      — multiple collections per season
--   * season_delivery_windows — shipment drops / delivery windows
--   * season_buyer_mapping    — buyer's season naming -> internal season
--   * season_brand_mapping    — brand -> season / collection links
--   * master_seasons extras   — department, description, default_currency
-- Idempotent: safe to re-run (CREATE IF NOT EXISTS + conditional ALTERs).
-- ======================================================================


CREATE TABLE IF NOT EXISTS `season_calendars` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `milestone` varchar(100) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `planned_date` date DEFAULT NULL,
  `actual_date` date DEFAULT NULL,
  `status` enum('pending','completed','overdue','skipped') NOT NULL DEFAULT 'pending',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`),
  KEY `season_milestone` (`season_id`, `milestone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_collections` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `collection_name` varchar(150) NOT NULL,
  `brand_id` int unsigned DEFAULT NULL,
  `brand_name` varchar(255) DEFAULT NULL,
  `description` text,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_delivery_windows` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `drop_no` int DEFAULT NULL,
  `window_name` varchar(100) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_buyer_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `buyer_id` int unsigned NOT NULL,
  `buyer_season_code` varchar(50) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_season_buyer` (`season_id`, `buyer_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_brand_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `brand_name` varchar(255) NOT NULL,
  `collection_id` int unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_season_brand` (`season_id`, `brand_name`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- master_seasons extras (idempotent ALTERs)
SET @has_department = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_seasons' AND COLUMN_NAME = 'department');
SET @sql = IF(@has_department = 0,
  'ALTER TABLE `master_seasons` ADD COLUMN `department` varchar(100) DEFAULT NULL AFTER `season_code`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_description = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_seasons' AND COLUMN_NAME = 'description');
SET @sql = IF(@has_description = 0,
  'ALTER TABLE `master_seasons` ADD COLUMN `description` text AFTER `department`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_currency = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_seasons' AND COLUMN_NAME = 'default_currency');
SET @sql = IF(@has_currency = 0,
  'ALTER TABLE `master_seasons` ADD COLUMN `default_currency` varchar(3) DEFAULT NULL AFTER `description`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

