-- ======================================================================
-- FashCognitive Style Season Master — Phase 2: AI Planning Engine
--   * season_ai             — stored AI analysis output per season/type
--                             (forecast, colors, fabrics, delivery, risk)
--   * season_color_themes   — approved color palette for a season
--   * season_fabric_themes  — approved fabric set for a season
-- Idempotent: safe to re-run (CREATE IF NOT EXISTS).
-- ======================================================================

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `season_ai` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `analysis_type` enum('forecast','colors','fabrics','delivery','risk') NOT NULL,
  `payload` json DEFAULT NULL,
  `method` varchar(100) DEFAULT 'heuristic',
  `run_count` int NOT NULL DEFAULT 1,
  `created_by` int unsigned 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_type` (`season_id`, `analysis_type`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_color_themes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `color_name` varchar(100) NOT NULL,
  `hex_code` varchar(9) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `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`),
  UNIQUE KEY `uq_season_color` (`season_id`, `color_name`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_fabric_themes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `fabric_name` varchar(150) NOT NULL,
  `sort_order` int NOT NULL DEFAULT 0,
  `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`),
  UNIQUE KEY `uq_season_fabric` (`season_id`, `fabric_name`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
