-- ═══════════════════════════════════════════════════════════════════════
-- ITEM MASTER — Digital Product Passport (Phase 2: Full Workspace)
--  One Item → One Digital Product Passport
--  Extends master_items with classification, buyer, warehouse, quality,
--  production, compliance, sustainability, pricing, AI insights, versions,
--  identifiers (barcode/qr/rfid) and alternative materials.
--  Idempotent: safe to re-run.
-- ═══════════════════════════════════════════════════════════════════════
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;

-- ---- 0. Allow 'finished' as an item category (Style Master integration) ----
ALTER TABLE `master_items`
  MODIFY `type` enum('fabric','trim','accessory','chemical','packaging','finished') NOT NULL DEFAULT 'fabric';

-- ---- 1. item_classification (material group, type, season, brand, buyer, SKU, HSN, origin) ----
CREATE TABLE IF NOT EXISTS `item_classification` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `short_name` varchar(100) DEFAULT NULL,
  `material_group` varchar(100) DEFAULT NULL,
  `material_type` enum('raw-material','semi-finished','finished') DEFAULT NULL,
  `season_id` int unsigned DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `hsn_code` varchar(50) DEFAULT NULL,
  `country_of_origin` varchar(100) 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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`),
  KEY `season_id` (`season_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 2. item_buyer_info (buyer-specific naming / part numbers) ----
CREATE TABLE IF NOT EXISTS `item_buyer_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `buyer_item_name` varchar(255) DEFAULT NULL,
  `buyer_part_no` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `notes` text,
  `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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 3. item_warehouses (default storage + tracking policy) ----
CREATE TABLE IF NOT EXISTS `item_warehouses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `zone` varchar(100) DEFAULT NULL,
  `rack` varchar(50) DEFAULT NULL,
  `shelf` varchar(50) DEFAULT NULL,
  `bin` varchar(50) DEFAULT NULL,
  `fifo` tinyint(1) NOT NULL DEFAULT 0,
  `fefo` tinyint(1) NOT NULL DEFAULT 0,
  `lot_tracking` tinyint(1) NOT NULL DEFAULT 0,
  `batch_tracking` tinyint(1) NOT NULL DEFAULT 0,
  `serial_tracking` tinyint(1) NOT NULL DEFAULT 0,
  `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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`),
  KEY `warehouse_id` (`warehouse_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 4. item_quality (inspection policy) ----
CREATE TABLE IF NOT EXISTS `item_quality` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `inspection_required` tinyint(1) NOT NULL DEFAULT 1,
  `aql` varchar(50) DEFAULT NULL,
  `four_point_inspection` tinyint(1) NOT NULL DEFAULT 0,
  `gsm_test` tinyint(1) NOT NULL DEFAULT 0,
  `shade_approval` tinyint(1) NOT NULL DEFAULT 0,
  `lab_test` tinyint(1) NOT NULL DEFAULT 0,
  `quality_plan` varchar(255) DEFAULT NULL,
  `acceptance_criteria` text,
  `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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 5. item_production (consumption & manufacturing policy) ----
CREATE TABLE IF NOT EXISTS `item_production` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `used_in_bom` tinyint(1) NOT NULL DEFAULT 1,
  `consumption_method` varchar(50) DEFAULT NULL,
  `backflush` tinyint(1) NOT NULL DEFAULT 0,
  `issue_method` varchar(50) DEFAULT NULL,
  `production_stage` varchar(100) DEFAULT NULL,
  `default_operation` varchar(100) DEFAULT NULL,
  `waste_pct` decimal(6,2) DEFAULT NULL,
  `marker_loss_pct` decimal(6,2) DEFAULT NULL,
  `expected_utilization_pct` decimal(6,2) 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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 6. item_compliance (certifications) ----
CREATE TABLE IF NOT EXISTS `item_compliance` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `oeko_tex` varchar(50) NOT NULL DEFAULT 'not-certified',
  `gots` varchar(50) NOT NULL DEFAULT 'not-certified',
  `grs` varchar(50) NOT NULL DEFAULT 'not-certified',
  `rcs` varchar(50) NOT NULL DEFAULT 'not-certified',
  `zdhc` varchar(50) NOT NULL DEFAULT 'not-certified',
  `reach` varchar(50) NOT NULL DEFAULT 'not-certified',
  `rsl` varchar(50) NOT NULL DEFAULT 'not-certified',
  `chemical_compliance` 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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 7. item_sustainability ----
CREATE TABLE IF NOT EXISTS `item_sustainability` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `recycled_content_pct` decimal(6,2) DEFAULT NULL,
  `organic_pct` decimal(6,2) DEFAULT NULL,
  `carbon_footprint` decimal(12,4) DEFAULT NULL,
  `water_consumption` decimal(12,4) DEFAULT NULL,
  `energy_consumption` decimal(12,4) DEFAULT NULL,
  `traceability_status` varchar(100) 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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 8. item_prices (costing) ----
CREATE TABLE IF NOT EXISTS `item_prices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `purchase_price` decimal(14,4) DEFAULT NULL,
  `average_cost` decimal(14,4) DEFAULT NULL,
  `last_purchase_price` decimal(14,4) DEFAULT NULL,
  `standard_cost` decimal(14,4) DEFAULT NULL,
  `replacement_cost` decimal(14,4) DEFAULT NULL,
  `selling_price` decimal(14,4) DEFAULT NULL,
  `currency_code` varchar(3) DEFAULT 'USD',
  `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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 9. item_ai_insights ----
CREATE TABLE IF NOT EXISTS `item_ai_insights` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `ai_quality_score` int unsigned DEFAULT NULL,
  `supplier_risk` varchar(50) DEFAULT NULL,
  `inventory_health` varchar(50) DEFAULT NULL,
  `slow_moving` tinyint(1) NOT NULL DEFAULT 0,
  `fast_moving` tinyint(1) NOT NULL DEFAULT 0,
  `recommended_safety_stock` decimal(14,4) DEFAULT NULL,
  `forecast_consumption` decimal(14,4) DEFAULT NULL,
  `alternative_materials` text,
  `insights` text,
  `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 `item_id` (`item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 10. item_versions (version history / audit trail) ----
CREATE TABLE IF NOT EXISTS `item_versions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `version_label` varchar(20) NOT NULL COMMENT 'V1.0, V1.1, V2.0...',
  `change_description` text,
  `changed_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 11. item_identifiers (barcode / QR / RFID) ----
CREATE TABLE IF NOT EXISTS `item_identifiers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `identifier_type` enum('barcode','qrcode','rfid','ean13','code128') NOT NULL DEFAULT 'code128',
  `identifier_value` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `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 `item_type_value` (`item_id`, `identifier_type`, `identifier_value`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 12. item_alternative_materials (AI substitute suggestions) ----
CREATE TABLE IF NOT EXISTS `item_alternative_materials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `alt_item_id` int unsigned DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `match_score` decimal(5,2) DEFAULT NULL,
  `status` enum('suggested','approved','rejected') NOT NULL DEFAULT 'suggested',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `alt_item_id` (`alt_item_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
