-- ============================================================
-- FashCognitive Apparel MES - Phase 1: Master Production Orders
-- MPO splits a Buyer PO across production units/lines.
-- Applies to live DB; production_schema.sql is kept in sync.
-- ============================================================

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);
