-- ======================================================================
-- FashCognitive Style Season Master — Phase 3: Documents, QR, Print,
-- Approvals & Version History
--   * season_documents  — uploaded season documents (agreements, spec sheets…)
--   * season_history    — change log + version_label tracking
--   * season_approvals  — approval workflow trail (submit / approve / reject)
--   * master_seasons    — approval_status, approval_note, share_token, version_label
-- Idempotent: safe to re-run (CREATE IF NOT EXISTS + conditional ALTERs).
-- ======================================================================


CREATE TABLE IF NOT EXISTS `season_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT 'other',
  `file_path` varchar(500) NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT 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_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `action` varchar(100) NOT NULL,
  `field` varchar(100) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `note` text,
  `version_label` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `season_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `season_id` int unsigned NOT NULL,
  `status` enum('submitted','approved','rejected') NOT NULL,
  `acted_by` int unsigned DEFAULT NULL,
  `acted_by_name` varchar(255) DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- master_seasons extras (idempotent ALTERs)
SET @has_approval_status = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_seasons' AND COLUMN_NAME = 'approval_status');
SET @sql = IF(@has_approval_status = 0,
  'ALTER TABLE `master_seasons` ADD COLUMN `approval_status` enum(''draft'',''submitted'',''approved'',''rejected'') NOT NULL DEFAULT ''draft'' AFTER `status`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

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

