-- ═══════════════════════════════════════════════════════════════════════
-- ITEM MASTER — Digital Product Passport (Phase 1: MVP)
--  Digital passport tables for the Items screen (Item Explorer + Workspace)
--  1. item_attributes  — fabric / trim specific fields (1 row per item)
--  2. item_suppliers   — purchasing: default supplier + price/lead time/MOQ
--  3. item_inventory   — stock/issue UOM, safety stock, ABC classification
--  4. item_images      — product photos
--  5. item_documents   — certificates, test reports, tech docs
--  Idempotent: safe to re-run (CREATE TABLE IF NOT EXISTS).
-- ═══════════════════════════════════════════════════════════════════════
START TRANSACTION;

-- ---- 1. item_attributes (fabric / trim specific) ----
CREATE TABLE IF NOT EXISTS item_attributes (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  item_id int unsigned NOT NULL,
  -- fabric fields
  gsm decimal(10,2) DEFAULT NULL,
  width decimal(10,2) DEFAULT NULL,
  composition varchar(255) DEFAULT NULL,
  construction varchar(255) DEFAULT NULL,
  finish varchar(255) DEFAULT NULL,
  shrinkage decimal(6,2) DEFAULT NULL,
  shade varchar(100) DEFAULT NULL,
  color_fastness varchar(100) DEFAULT NULL,
  relaxation_time varchar(100) DEFAULT NULL,
  -- trim fields
  trim_type varchar(100) DEFAULT NULL,
  material varchar(255) DEFAULT NULL,
  trim_color varchar(100) DEFAULT NULL,
  trim_size varchar(100) DEFAULT NULL,
  thickness decimal(10,2) DEFAULT NULL,
  buyer_part_no varchar(100) DEFAULT NULL,
  supplier_part_no 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;

-- ---- 2. item_suppliers (purchasing) ----
CREATE TABLE IF NOT EXISTS item_suppliers (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  item_id int unsigned NOT NULL,
  supplier_id int unsigned NOT NULL,
  is_default tinyint(1) NOT NULL DEFAULT 0,
  unit_price decimal(14,4) DEFAULT NULL,
  currency_code varchar(3) DEFAULT 'USD',
  lead_time_days int unsigned DEFAULT NULL,
  moq decimal(14,4) DEFAULT NULL,
  purchase_uom_id int unsigned DEFAULT NULL,
  incoterm varchar(50) DEFAULT NULL,
  payment_term_id int unsigned DEFAULT NULL,
  notes text,
  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 item_id (item_id),
  KEY supplier_id (supplier_id),
  KEY company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 3. item_inventory (1 row per item) ----
CREATE TABLE IF NOT EXISTS item_inventory (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  item_id int unsigned NOT NULL,
  stock_uom_id int unsigned DEFAULT NULL,
  issue_uom_id int unsigned DEFAULT NULL,
  conversion_factor decimal(14,4) NOT NULL DEFAULT 1.0000,
  min_stock decimal(14,4) NOT NULL DEFAULT 0.0000,
  max_stock decimal(14,4) NOT NULL DEFAULT 0.0000,
  safety_stock decimal(14,4) NOT NULL DEFAULT 0.0000,
  reorder_level decimal(14,4) NOT NULL DEFAULT 0.0000,
  abc_classification enum('A','B','C') 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;

-- ---- 4. item_images ----
CREATE TABLE IF NOT EXISTS item_images (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  item_id int unsigned NOT NULL,
  file_path varchar(500) NOT NULL,
  caption varchar(255) DEFAULT NULL,
  is_primary tinyint(1) NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY item_id (item_id),
  KEY company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 5. item_documents ----
CREATE TABLE IF NOT EXISTS item_documents (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  item_id int unsigned NOT NULL,
  title varchar(255) DEFAULT NULL,
  category varchar(100) DEFAULT NULL,
  file_path varchar(500) NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY item_id (item_id),
  KEY company_id (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
