CREATE TABLE IF NOT EXISTS `master_seasons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_name` varchar(100) NOT NULL,
  `season_code` varchar(20) NOT NULL DEFAULT '',
  `year` smallint 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_seasons` (`id`, `company_id`, `season_name`, `season_code`, `year`, `status`) VALUES
(1, 1, 'Spring/Summer 2025', 'SS25', 2025, 'active'),
(2, 1, 'Fall/Winter 2025', 'FW25', 2025, 'active'),
(3, 1, 'Spring/Summer 2026', 'SS26', 2026, 'active'),
(4, 1, 'Fall/Winter 2026', 'FW26', 2026, 'active'),
(5, 1, 'Resort 2025', 'RS25', 2025, 'active'),
(6, 1, 'Pre-Fall 2025', 'PF25', 2025, 'active'),
(7, 1, 'Capsule Collection', 'CC', NULL, 'active'),
(8, 1, 'Core/Basic', 'CB', NULL, 'active');

-- Add season_id foreign key to master_styles
ALTER TABLE `master_styles` ADD COLUMN `season_id` int unsigned DEFAULT NULL AFTER `season`;
ALTER TABLE `master_styles` ADD KEY `season_id` (`season_id`);
