-- Open Order Book - SUBCON process linked to Manufacturing Order (MO)
-- SUBCON is a process (not a production stage): it generates a PO, stock moves, and cost.
-- One MO can have at most one SUBCON process record (UNIQUE on fk_mo).
-- process_target: SF (semi-finished) or FG (finished goods)
-- Linked to MO via fk_mo (references llx_mrp_mo.rowid).

CREATE TABLE IF NOT EXISTS llx_openorderbook_mo_subcon (
  rowid                BIGINT UNSIGNED      NOT NULL AUTO_INCREMENT,
  entity               INT                  NOT NULL DEFAULT 1,
  fk_mo                BIGINT UNSIGNED      NOT NULL,

  -- What the subcon process targets: SF (semi-finished) or FG (finished goods)
  process_target       VARCHAR(16)          NOT NULL DEFAULT 'FG',

  -- Lifecycle status: PLANNED / PO_CREATED / SENT / RETURNED / DONE / CANCELLED
  status               VARCHAR(32)          NOT NULL DEFAULT 'PLANNED',

  fk_supplier          BIGINT UNSIGNED      DEFAULT NULL,

  -- Quantities
  qty_planned          DECIMAL(24,8)        NOT NULL DEFAULT 0,
  qty_sent             DECIMAL(24,8)        NOT NULL DEFAULT 0,
  qty_returned         DECIMAL(24,8)        NOT NULL DEFAULT 0,

  -- Costing
  cost_type            VARCHAR(16)          NOT NULL DEFAULT 'UNIT',
  unit_cost            DECIMAL(24,8)        NOT NULL DEFAULT 0,
  total_cost           DECIMAL(24,8)        NOT NULL DEFAULT 0,

  -- Linked Purchase Order (generated by Create PO step)
  fk_po                BIGINT UNSIGNED      DEFAULT NULL,
  fk_po_line           BIGINT UNSIGNED      DEFAULT NULL,

  -- Warehouse routing
  fk_warehouse_from    BIGINT UNSIGNED      DEFAULT NULL,   -- source warehouse (outgoing stock move)
  fk_warehouse_subcon  BIGINT UNSIGNED      DEFAULT NULL,   -- subcontractor virtual warehouse
  fk_warehouse_return  BIGINT UNSIGNED      DEFAULT NULL,   -- return warehouse (incoming stock move)

  -- Timeline
  date_planned         DATETIME             DEFAULT NULL,
  date_sent            DATETIME             DEFAULT NULL,
  date_returned        DATETIME             DEFAULT NULL,
  date_done            DATETIME             DEFAULT NULL,
  date_creation        DATETIME             NOT NULL,
  tms                  TIMESTAMP            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  -- Audit
  fk_user_creat        BIGINT UNSIGNED      DEFAULT NULL,
  fk_user_modif        BIGINT UNSIGNED      DEFAULT NULL,

  -- Notes
  note_public          TEXT                 DEFAULT NULL,
  note_private         TEXT                 DEFAULT NULL,

  PRIMARY KEY (rowid),

  -- One MO = max one SUBCON process record (relaxable in a future migration if partial subcon per batch is needed)
  UNIQUE KEY uk_oob_mo_subcon_mo (fk_mo),

  KEY idx_oob_mo_subcon_entity   (entity),
  KEY idx_oob_mo_subcon_fk_mo    (fk_mo),
  KEY idx_oob_mo_subcon_status   (status),
  KEY idx_oob_mo_subcon_supplier (fk_supplier),
  KEY idx_oob_mo_subcon_fk_po    (fk_po)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
