-- ============================================================
-- FashCognitive Production Execution Schema
-- Extends existing database with MRP, Contractor, 
-- and Production Execution modules
-- ============================================================

-- ============================================================
-- 1. Existing Table Modifications
-- ============================================================

ALTER TABLE process_orders
  ADD COLUMN production_order_id int unsigned DEFAULT NULL AFTER style_id,
  ADD KEY production_order_id (production_order_id);

ALTER TABLE production_orders
  ADD COLUMN order_category enum('internal','outsource','mixed') NOT NULL DEFAULT 'internal' AFTER order_type,
  ADD COLUMN fg_warehouse_id int unsigned DEFAULT NULL AFTER work_center_id,
  ADD COLUMN mrp_run_id int unsigned DEFAULT NULL AFTER fg_warehouse_id,
  ADD KEY fg_warehouse_id (fg_warehouse_id),
  ADD KEY mrp_run_id (mrp_run_id);

-- ============================================================
-- 2. MRP Module
-- ============================================================

CREATE TABLE IF NOT EXISTS mrp_runs (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  production_order_id int unsigned NOT NULL,
  run_date date NOT NULL,
  status enum('draft','calculated','released','cancelled') NOT NULL DEFAULT 'draft',
  buyer_po_id int unsigned DEFAULT NULL,
  total_requirement_qty decimal(14,2) NOT NULL DEFAULT 0,
  total_stock_qty decimal(14,2) NOT NULL DEFAULT 0,
  total_shortage_qty decimal(14,2) NOT NULL DEFAULT 0,
  notes text,
  created_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY buyer_po_id (buyer_po_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mrp_requirements (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  mrp_run_id int unsigned NOT NULL,
  production_order_id int unsigned NOT NULL,
  item_id int unsigned DEFAULT NULL,
  item_name varchar(255) NOT NULL,
  material_type enum('fabric','trim','accessory','packaging','chemical','other') NOT NULL DEFAULT 'fabric',
  bom_qty decimal(14,4) NOT NULL DEFAULT 0,
  total_required_qty decimal(14,4) NOT NULL DEFAULT 0,
  stock_qty decimal(14,4) NOT NULL DEFAULT 0,
  shortage_qty decimal(14,4) NOT NULL DEFAULT 0,
  uom varchar(20) DEFAULT 'PCS',
  source_type enum('purchase','transfer','produce') NOT NULL DEFAULT 'purchase',
  status enum('open','suggested','ordered','fulfilled','cancelled') NOT NULL DEFAULT 'open',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY mrp_run_id (mrp_run_id),
  KEY production_order_id (production_order_id),
  KEY item_id (item_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mrp_purchase_suggestions (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  mrp_run_id int unsigned NOT NULL,
  requirement_id int unsigned DEFAULT NULL,
  suggestion_type enum('purchase','transfer','internal_produce') NOT NULL DEFAULT 'purchase',
  item_id int unsigned DEFAULT NULL,
  item_name varchar(255) NOT NULL,
  suggested_qty decimal(14,4) NOT NULL DEFAULT 0,
  uom varchar(20) DEFAULT 'PCS',
  source_warehouse_id int unsigned DEFAULT NULL,
  target_warehouse_id int unsigned DEFAULT NULL,
  preferred_supplier_id int unsigned DEFAULT NULL,
  required_by_date date DEFAULT NULL,
  status enum('pending','approved','converted_to_pr','converted_to_po','cancelled') NOT NULL DEFAULT 'pending',
  converted_to varchar(50) DEFAULT NULL COMMENT 'table name where this was converted',
  converted_id int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY mrp_run_id (mrp_run_id),
  KEY requirement_id (requirement_id),
  KEY item_id (item_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Contractor / Subcontractor Module
-- ============================================================

CREATE TABLE IF NOT EXISTS master_contractors (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  contractor_code varchar(30) NOT NULL,
  contractor_name varchar(255) NOT NULL,
  unit_type enum('internal','contractor') NOT NULL DEFAULT 'contractor',
  contact_person varchar(255) DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  phone varchar(50) DEFAULT NULL,
  address text,
  city_id int unsigned DEFAULT NULL,
  process_types json DEFAULT NULL COMMENT '["knitting","dyeing","printing","embroidery","washing","cutting","sewing","finishing"]',
  capacity_monthly int unsigned DEFAULT NULL,
  audit_score decimal(5,2) DEFAULT NULL,
  quality_rating decimal(3,2) DEFAULT NULL,
  delivery_rating decimal(3,2) DEFAULT NULL,
  compliance_certifications json DEFAULT NULL,
  status enum('active','inactive','blacklisted') 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 contractor_code (contractor_code, company_id),
  KEY company_id (company_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_rfqs (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  rfq_no varchar(30) NOT NULL,
  production_order_id int unsigned DEFAULT NULL,
  process_type enum('knitting','dyeing','compacting','printing','embroidery','washing','cutting','sewing','finishing','packing','other') NOT NULL,
  style_id int unsigned DEFAULT NULL,
  total_qty int unsigned NOT NULL DEFAULT 0,
  uom varchar(20) DEFAULT 'PCS',
  required_date date DEFAULT NULL,
  artwork_included tinyint(1) NOT NULL DEFAULT 0,
  spec_included tinyint(1) NOT NULL DEFAULT 0,
  tech_pack_included tinyint(1) NOT NULL DEFAULT 0,
  delivery_location varchar(255) DEFAULT NULL,
  status enum('draft','sent','quoting','expired','awarded','cancelled') NOT NULL DEFAULT 'draft',
  response_deadline datetime DEFAULT NULL,
  notes text,
  created_by int unsigned 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 rfq_no (rfq_no, company_id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY style_id (style_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_rfq_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  rfq_id int unsigned NOT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(50) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  rate decimal(12,2) DEFAULT NULL,
  delivery_date date DEFAULT NULL,
  notes text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY rfq_id (rfq_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_quotations (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  quotation_no varchar(30) NOT NULL,
  rfq_id int unsigned NOT NULL,
  contractor_id int unsigned NOT NULL,
  total_amount decimal(14,2) NOT NULL DEFAULT 0,
  currency_id int unsigned DEFAULT NULL,
  lead_time_days int unsigned DEFAULT NULL,
  valid_until date DEFAULT NULL,
  terms_conditions text,
  status enum('submitted','revised','accepted','rejected','withdrawn') NOT NULL DEFAULT 'submitted',
  submitted_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY quotation_no (quotation_no, company_id),
  KEY company_id (company_id),
  KEY rfq_id (rfq_id),
  KEY contractor_id (contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_quotation_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  quotation_id int unsigned NOT NULL,
  rfq_item_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(50) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  rate decimal(12,2) NOT NULL DEFAULT 0,
  total decimal(14,2) NOT NULL DEFAULT 0,
  lead_time_days int unsigned DEFAULT NULL,
  remarks text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY quotation_id (quotation_id),
  KEY rfq_item_id (rfq_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_evaluation_scores (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  rfq_id int unsigned NOT NULL,
  quotation_id int unsigned NOT NULL,
  contractor_id int unsigned NOT NULL,
  price_score decimal(5,2) DEFAULT 0,
  quality_score decimal(5,2) DEFAULT 0,
  delivery_score decimal(5,2) DEFAULT 0,
  capacity_score decimal(5,2) DEFAULT 0,
  audit_score decimal(5,2) DEFAULT 0,
  distance_score decimal(5,2) DEFAULT 0,
  total_score decimal(5,2) DEFAULT 0,
  ai_recommendation enum('recommended','alternative','not_recommended') DEFAULT NULL,
  ai_reason text,
  evaluation_weights json DEFAULT NULL,
  evaluated_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY rfq_id (rfq_id),
  KEY quotation_id (quotation_id),
  KEY contractor_id (contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_awards (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  award_no varchar(30) NOT NULL,
  quotation_id int unsigned NOT NULL,
  contractor_id int unsigned NOT NULL,
  production_order_id int unsigned DEFAULT NULL,
  process_type enum('knitting','dyeing','compacting','printing','embroidery','washing','cutting','sewing','finishing','packing','other') NOT NULL,
  awarded_qty int unsigned NOT NULL DEFAULT 0,
  rate decimal(12,2) NOT NULL DEFAULT 0,
  total_amount decimal(14,2) NOT NULL DEFAULT 0,
  currency_id int unsigned DEFAULT NULL,
  scheduled_start date DEFAULT NULL,
  scheduled_end date DEFAULT NULL,
  actual_start date DEFAULT NULL,
  actual_end date DEFAULT NULL,
  status enum('pending','active','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  notes text,
  created_by int unsigned 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 award_no (award_no, company_id),
  KEY company_id (company_id),
  KEY quotation_id (quotation_id),
  KEY contractor_id (contractor_id),
  KEY production_order_id (production_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. Production Execution Module
-- ============================================================

CREATE TABLE IF NOT EXISTS production_sourcing (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  production_order_id int unsigned NOT NULL,
  operation_name varchar(150) NOT NULL,
  operation_type enum('knitting','dyeing','compacting','printing','embroidery','washing','cutting','sewing','finishing','packing','other') NOT NULL,
  execution_mode enum('internal','external','mixed') NOT NULL DEFAULT 'internal',
  contractor_id int unsigned DEFAULT NULL,
  award_id int unsigned DEFAULT NULL,
  allocation_pct decimal(5,2) DEFAULT 100 COMMENT '% allocated to this source',
  planned_qty int unsigned NOT NULL DEFAULT 0,
  produced_qty int unsigned NOT NULL DEFAULT 0,
  status enum('planned','in_progress','completed') NOT NULL DEFAULT 'planned',
  fg_warehouse_id int unsigned DEFAULT NULL,
  rate decimal(12,2) DEFAULT 0,
  total_cost decimal(14,2) DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY contractor_id (contractor_id),
  KEY award_id (award_id),
  KEY fg_warehouse_id (fg_warehouse_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_confirmations (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  production_order_id int unsigned NOT NULL,
  process_order_id int unsigned DEFAULT NULL,
  operation_id int unsigned DEFAULT NULL,
  bundle_id int unsigned DEFAULT NULL,
  confirmation_type enum('operation','bundle','process','final') NOT NULL DEFAULT 'operation',
  input_qty int unsigned NOT NULL DEFAULT 0,
  output_qty int unsigned NOT NULL DEFAULT 0,
  rejected_qty int unsigned NOT NULL DEFAULT 0,
  rework_qty int unsigned NOT NULL DEFAULT 0,
  scrap_qty int unsigned NOT NULL DEFAULT 0,
  yield_pct decimal(5,2) GENERATED ALWAYS AS (ROUND((output_qty / NULLIF(input_qty, 0)) * 100, 2)) STORED,
  confirmed_by int unsigned DEFAULT NULL,
  confirmed_at datetime DEFAULT NULL,
  notes text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY process_order_id (process_order_id),
  KEY operation_id (operation_id),
  KEY bundle_id (bundle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS finished_goods_receipts (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  receipt_no varchar(30) NOT NULL,
  production_order_id int unsigned NOT NULL,
  producer_id int unsigned DEFAULT NULL,
  sourcing_id int unsigned DEFAULT NULL,
  process_order_id int unsigned DEFAULT NULL,
  warehouse_id int unsigned DEFAULT NULL,
  source_warehouse_id int unsigned DEFAULT NULL,
  factory_warehouse_id int unsigned DEFAULT NULL,
  shipment_strategy enum('central_warehouse','direct_shipment','customer_warehouse','distribution_center','3pl') DEFAULT NULL,
  internal_transfer_id int unsigned DEFAULT NULL,
  total_qty int unsigned NOT NULL DEFAULT 0,
  total_weight_kg decimal(10,2) DEFAULT NULL,
  received_by int unsigned DEFAULT NULL,
  received_at datetime DEFAULT NULL,
  status enum('draft','completed','cancelled') NOT NULL DEFAULT 'draft',
  notes text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY receipt_no (receipt_no, company_id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY process_order_id (process_order_id),
  KEY warehouse_id (warehouse_id),
  KEY producer_id (producer_id),
  KEY sourcing_id (sourcing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS finished_goods_receipt_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  receipt_id int unsigned NOT NULL,
  production_order_id int unsigned DEFAULT NULL,
  style_id int unsigned DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(50) DEFAULT NULL,
  quantity int unsigned NOT NULL DEFAULT 0,
  weight_kg decimal(10,2) DEFAULT NULL,
  bundle_id int unsigned DEFAULT NULL,
  batch_no varchar(50) DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY receipt_id (receipt_id),
  KEY production_order_id (production_order_id),
  KEY style_id (style_id),
  KEY bundle_id (bundle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_live_costs (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  production_order_id int unsigned NOT NULL,
  cost_category enum('material','labor','overhead','outsource','chemical','logistics','other') NOT NULL,
  cost_subcategory varchar(100) DEFAULT NULL,
  item_name varchar(255) NOT NULL,
  planned_amount decimal(14,2) NOT NULL DEFAULT 0,
  actual_amount decimal(14,2) NOT NULL DEFAULT 0,
  variance decimal(14,2) GENERATED ALWAYS AS (actual_amount - planned_amount) STORED,
  currency_id int unsigned DEFAULT NULL,
  reference_type varchar(50) DEFAULT NULL COMMENT 'e.g. material_issue, labor_entry, po_receipt',
  reference_id int unsigned DEFAULT NULL,
  recorded_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY production_order_id (production_order_id),
  KEY cost_category (cost_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. Contractor Portal Tables
-- ============================================================

CREATE TABLE IF NOT EXISTS contractor_portal_users (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  contractor_id int unsigned NOT NULL,
  email varchar(255) NOT NULL,
  password_hash varchar(255) NOT NULL,
  name varchar(255) DEFAULT NULL,
  phone varchar(50) DEFAULT NULL,
  role enum('admin','manager','operator','viewer') NOT NULL DEFAULT 'viewer',
  last_login_at datetime DEFAULT NULL,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY email (email),
  KEY company_id (company_id),
  KEY contractor_id (contractor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contractor_portal_submissions (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  contractor_id int unsigned NOT NULL,
  award_id int unsigned DEFAULT NULL,
  submission_type enum('production_update','quality_report','dispatch_asn','material_request','photos') NOT NULL,
  description text,
  attachments json DEFAULT NULL,
  submitted_by int unsigned DEFAULT NULL,
  submitted_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY contractor_id (contractor_id),
  KEY award_id (award_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. Master Production Orders (Apparel MES - Level 1 Planning)
-- ============================================================

CREATE TABLE IF NOT EXISTS master_production_orders (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  mpo_no varchar(30) NOT NULL,
  buyer_po_id int unsigned DEFAULT NULL COMMENT 'master_buyer_pos.id',
  style_id int unsigned DEFAULT NULL COMMENT 'master_styles.id',
  color varchar(50) DEFAULT NULL,
  size varchar(20) DEFAULT NULL,
  total_qty int unsigned NOT NULL DEFAULT 0,
  uom varchar(20) DEFAULT 'PCS',
  priority enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  planned_start_date date DEFAULT NULL,
  planned_end_date date DEFAULT NULL,
  status enum('draft','planned','released','completed','closed','cancelled') NOT NULL DEFAULT 'draft',
  notes text,
  created_by int unsigned DEFAULT NULL,
  approved_by int unsigned DEFAULT NULL,
  approved_at datetime DEFAULT NULL,
  released_at datetime 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 mpo_no (mpo_no, company_id),
  KEY company_id (company_id),
  KEY buyer_po_id (buyer_po_id),
  KEY style_id (style_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS master_production_order_lines (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  mpo_id int unsigned NOT NULL,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  allocated_qty int unsigned NOT NULL DEFAULT 0,
  split_pct decimal(6,2) NOT NULL DEFAULT 0,
  planned_start_date date DEFAULT NULL,
  planned_end_date date DEFAULT NULL,
  status enum('planned','released','completed','cancelled') NOT NULL DEFAULT 'planned',
  production_order_id int unsigned DEFAULT NULL COMMENT 'production_orders.id (set on release)',
  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),
  KEY mpo_id (mpo_id),
  KEY unit_id (unit_id),
  KEY line_id (line_id),
  KEY status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE production_orders
  ADD COLUMN mpo_id int unsigned DEFAULT NULL AFTER mrp_run_id,
  ADD KEY mpo_id (mpo_id);

-- ============================================================
-- 7. Planning Board & Capacity (Apparel MES - Level 1 APS)
-- ============================================================

CREATE TABLE IF NOT EXISTS production_shifts (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  shift_name varchar(50) NOT NULL,
  start_time time DEFAULT '08:00:00',
  end_time time DEFAULT '17:00:00',
  planned_operators int unsigned 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),
  KEY company_id (company_id),
  KEY unit_id (unit_id),
  KEY line_id (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_machines (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  work_center_id int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  machine_code varchar(50) NOT NULL,
  machine_name varchar(150) NOT NULL,
  machine_type varchar(100) DEFAULT NULL,
  status enum('available','in_use','maintenance','breakdown') NOT NULL DEFAULT 'available',
  hourly_rate decimal(10,2) DEFAULT 0,
  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 machine_code (machine_code, company_id),
  KEY company_id (company_id),
  KEY unit_id (unit_id),
  KEY line_id (line_id),
  KEY work_center_id (work_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_operator_assignments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  prod_order_id int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  operation_id int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  operator_id int unsigned DEFAULT NULL COMMENT 'users.id',
  operator_name varchar(150) DEFAULT NULL,
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  shift_id int unsigned DEFAULT NULL COMMENT 'production_shifts.id',
  machine_id int unsigned DEFAULT NULL COMMENT 'production_machines.id',
  assigned_date date DEFAULT NULL,
  start_time time DEFAULT NULL,
  end_time time DEFAULT NULL,
  role varchar(50) DEFAULT NULL,
  status enum('assigned','in_progress','completed','released') NOT NULL DEFAULT 'assigned',
  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),
  KEY prod_order_id (prod_order_id),
  KEY operation_id (operation_id),
  KEY operator_id (operator_id),
  KEY line_id (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_capacity_plans (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  plan_date date NOT NULL,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  production_order_id int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  operation_id int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  planned_pcs int unsigned DEFAULT 0,
  planned_hours decimal(8,2) DEFAULT 0,
  load_pct decimal(6,2) 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),
  KEY company_id (company_id),
  KEY plan_date (plan_date),
  KEY line_id (line_id),
  KEY production_order_id (production_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. Routing Library (Apparel MES - Level 1/2 Master Data)
-- ============================================================

CREATE TABLE IF NOT EXISTS production_routing (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  routing_code varchar(30) NOT NULL,
  routing_name varchar(150) NOT NULL,
  description text,
  version int unsigned NOT NULL DEFAULT 1,
  status enum('draft','active','obsolete') NOT NULL DEFAULT 'draft',
  style_id int unsigned DEFAULT NULL COMMENT 'Optional: default routing for a style',
  unit_id int unsigned DEFAULT NULL COMMENT 'Optional: default production unit (master_contractors.id)',
  total_smv decimal(10,2) DEFAULT NULL COMMENT 'Sum of step SMV',
  total_hours_100 decimal(10,2) DEFAULT NULL COMMENT 'Hours required for 100 pcs (total_smv * 100 / 60)',
  created_by int unsigned DEFAULT NULL COMMENT 'users.id',
  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_routing_code_company (company_id, routing_code),
  KEY company_id (company_id),
  KEY style_id (style_id),
  KEY unit_id (unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_routing_steps (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  routing_id int unsigned NOT NULL,
  sequence int unsigned NOT NULL DEFAULT 10,
  operation_name varchar(150) NOT NULL,
  operation_type enum('cutting','bundling','printing','embroidery','sewing','finishing','packing','quality','other') DEFAULT 'sewing',
  work_center_id int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  line_id int unsigned DEFAULT NULL COMMENT 'Optional: default line (master_production_lines.id)',
  machine_type varchar(100) DEFAULT NULL COMMENT 'Overlock, Flatlock, Single Needle, etc.',
  operators_required int unsigned DEFAULT 1,
  smv decimal(8,2) DEFAULT 0 COMMENT 'Standard Minute Value',
  sam decimal(8,2) DEFAULT NULL COMMENT 'Standard Allowed Minutes (= SMV)',
  lead_time_hours decimal(6,2) DEFAULT NULL,
  rework_pct decimal(5,2) DEFAULT 0 COMMENT 'Expected % rework',
  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),
  KEY company_id (company_id),
  KEY routing_id (routing_id),
  KEY work_center_id (work_center_id),
  KEY line_id (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 9. AI / Intelligence Layer (Apparel MES - Level 4)
-- ============================================================

CREATE TABLE IF NOT EXISTS production_ai_analysis (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  ref_type enum('order','line','mpo','unit','global') DEFAULT 'global' COMMENT 'What the analysis is attached to',
  ref_id int unsigned DEFAULT NULL,
  analysis_type varchar(50) NOT NULL COMMENT 'line_balancing | bottleneck | predictive_quality | mpo_release | ai_insights',
  risk_score decimal(5,2) DEFAULT NULL COMMENT '0-100 score',
  severity enum('low','medium','high','critical') DEFAULT 'low',
  summary varchar(500) DEFAULT NULL,
  recommendation text,
  detail json DEFAULT NULL,
  ai_insights text,
  ai_provider varchar(30) DEFAULT NULL,
  ai_model varchar(80) DEFAULT NULL,
  analyzed_by int unsigned DEFAULT NULL COMMENT 'users.id',
  status enum('pending','completed','failed') NOT NULL DEFAULT 'completed',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY analysis_type (analysis_type),
  KEY ref (ref_type, ref_id),
  KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 10. Logistics Layer (Apparel MES - Level 5)
--
-- The logistics layer aggregates the existing outbound pipeline
-- that was built across the packing / FG-receipt / warehouse
-- modules (tables below are owned by those migrations):
--
--   production_orders
--     -> finished_goods_receipts / finished_goods_receipt_items
--       -> packing_orders / packing_order_lines / cartons
--         -> dispatch_notes / dispatch_note_items
--           -> shipment_manifests / shipment_manifest_items
--
-- and tracks buyer-PO fulfillment against
-- master_buyer_po_shipments (the scheduled shipment lines).
--
-- Supporting indexes required by the Production Logistics
-- Execution Console (api/manufacturing/logistics.php):
--
-- ALTER TABLE shipment_manifest_items
--   ADD KEY idx_mi_prod_order (production_order_id);
-- ALTER TABLE cartons
--   ADD KEY idx_carton_status (packing_order_id, status);
-- ============================================================

-- ============================================================
-- 11. Control Center (Apparel MES - Level 3: Control)
--
-- Read-only operational cockpit (api/manufacturing/control-center.php)
-- aggregating Planning, Execution, Intelligence (AI) and Logistics
-- into a single view with a deterministic exception/alert engine.
-- Introduces no new operational tables.
--
-- Supporting index required by the Control Center and AI
-- analytics for per-order operation rollups:
--
-- ALTER TABLE production_order_operations
--   ADD KEY idx_opo_order_seq (prod_order_id, sequence, status);
-- ============================================================

-- ============================================================
-- 12. Production Bundle Movements (Apparel MES - Level 2 Execution)
--
-- Append-only bundle passport trail (QR/barcode scan traceability).
-- Every creation, scan, advance, QC transition and completion of a
-- bundle is recorded so WIP history can be reconstructed per bundle.
-- Mirrors database/migration_bundle_movements.sql.
-- ============================================================

CREATE TABLE IF NOT EXISTS `production_bundle_movements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `bundle_id` int unsigned NOT NULL COMMENT 'production_order_bundles.id',
  `prod_order_id` int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  `from_station` varchar(100) DEFAULT NULL,
  `to_station` varchar(100) DEFAULT NULL,
  `from_operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `to_operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `operator_name` varchar(150) DEFAULT NULL,
  `action` enum('created','scanned','advanced','sent_qc','passed','rejected','packed','completed','remark') NOT NULL DEFAULT 'scanned',
  `qty` int unsigned DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL,
  `scanned_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `bundle_id` (`bundle_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `scanned_at` (`scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 13. BOM Consumption Ledger (Apparel MES - Level 2 Execution)
--
-- Per-operation material consumption history. Written by the
-- auto-consumption engine (autoConsumeBom on confirmation) and by
-- manual per-operation consumption (execution.php material-consume).
-- Mirrors database/migration_bom_consumption.sql.
-- ============================================================

CREATE TABLE IF NOT EXISTS `bom_consumption` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `production_order_id` int unsigned NOT NULL,
  `process_order_id` int unsigned DEFAULT NULL,
  `operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `confirmation_id` int unsigned DEFAULT NULL,
  `material_id` int unsigned NOT NULL COMMENT 'production_order_materials.id',
  `item_id` int unsigned DEFAULT NULL COMMENT 'master_items.id',
  `item_name` varchar(255) NOT NULL,
  `bom_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `output_qty` int unsigned NOT NULL DEFAULT 0,
  `consumed_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `wastage_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `uom` varchar(20) DEFAULT 'PCS',
  `batch_no` varchar(50) DEFAULT NULL,
  `lot_no` varchar(50) DEFAULT NULL,
  `rate` decimal(12,2) DEFAULT 0,
  `total_cost` decimal(14,2) DEFAULT 0,
  `consumed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `production_order_id` (`production_order_id`),
  KEY `operation_id` (`operation_id`),
  KEY `material_id` (`material_id`),
  KEY `confirmation_id` (`confirmation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
