-- ======================================================================
-- FashCognitive Global Size Management — Phase 4 (AI Size Ratio & History)
-- Adds: size_series_ai_recommendations (per-series per-size ratio/suggestion
-- derived from purchase order history or manual input) and
-- size_series_history (change log of the size series master).
-- Idempotent: safe to re-run.
-- ======================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------
-- 1. AI Size Recommendations (per-series per-size ratio)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_series_ai_recommendations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `size_code` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `ratio` decimal(6,2) NOT NULL DEFAULT 0.00,
  `ratio_type` enum('suggested','manual') NOT NULL DEFAULT 'manual',
  `based_on` enum('manual','po_history') NOT NULL DEFAULT 'manual',
  `period_label` varchar(100) DEFAULT NULL,
  `note` text,
  `display_order` int unsigned 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 `uk_ai_series_size` (`size_series_id`, `size_code`),
  KEY `size_series_id` (`size_series_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- 2. Size Series History (change log)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `size_series_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `action` varchar(50) 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,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
