-- Open Order Book - schema

CREATE TABLE IF NOT EXISTS llx_openorderbook_paper (
  rowid                     INTEGER AUTO_INCREMENT PRIMARY KEY,
  entity                    INTEGER NOT NULL DEFAULT 1,
  fk_soc                    INTEGER NULL,
  customer_order_number     VARCHAR(64) NOT NULL,
  date_received             DATE NULL,
  note_private              TEXT NULL,
  status                    VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
  datec                     DATETIME NULL,
  tms                       TIMESTAMP NULL,
  fk_user_author            INTEGER NULL,
  UNIQUE KEY uk_oob_paper_cust_order (entity, fk_soc, customer_order_number),
  KEY idx_oob_paper_entity (entity),
  KEY idx_oob_paper_soc (entity, fk_soc)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS llx_openorderbook_paperline (
  rowid                 INTEGER AUTO_INCREMENT PRIMARY KEY,
  entity                INTEGER NOT NULL DEFAULT 1,
  fk_paper              INTEGER NOT NULL,
  line_no               INTEGER NOT NULL,
  our_part_fk_product   INTEGER NULL,
  our_part_ref          VARCHAR(128) NULL,
  customer_pn           VARCHAR(128) NULL,
  description           VARCHAR(255) NOT NULL,
  qty                   DOUBLE NOT NULL DEFAULT 0,
  price                 DOUBLE NULL,
  contracted_month      DATE NULL,
  predicted_date        DATE NULL,
  action                VARCHAR(16) NOT NULL DEFAULT 'OPEN',
  datec                 DATETIME NULL,
  tms                   TIMESTAMP NULL,
  -- Link to Sales Order line (llx_commandedet.rowid) set when ORDER_VALIDATE fires
  fk_soc_line           BIGINT UNSIGNED DEFAULT NULL,
  -- Link paperline to MO for job_number / subcon traceability (set on MRP_MO_VALIDATE)
  fk_mo                 BIGINT UNSIGNED DEFAULT NULL,
  KEY idx_oob_paperline_paper (entity, fk_paper),
  KEY idx_oob_paperline_fk_paper (fk_paper),
  KEY idx_oob_paperline_fk_soc_line (fk_soc_line),
  KEY idx_oob_paperline_fk_mo (fk_mo)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

-- Upgrade: add fk_soc_line and fk_mo to existing paperline table (no-op on fresh install)
-- Dolibarr run_sql ignores DB_ERROR_COLUMN_ALREADY_EXISTS and DB_ERROR_KEY_NAME_ALREADY_EXISTS
ALTER TABLE llx_openorderbook_paperline ADD COLUMN fk_soc_line BIGINT UNSIGNED DEFAULT NULL;
ALTER TABLE llx_openorderbook_paperline ADD INDEX idx_oob_paperline_fk_soc_line (fk_soc_line);
-- Upgrade: contracted_month from VARCHAR(16) to DATE (data already stored as YYYY-MM-DD)
ALTER TABLE llx_openorderbook_paperline MODIFY COLUMN contracted_month DATE NULL;
ALTER TABLE llx_openorderbook_paperline ADD COLUMN fk_mo BIGINT UNSIGNED DEFAULT NULL;
ALTER TABLE llx_openorderbook_paperline ADD INDEX idx_oob_paperline_fk_mo (fk_mo);

-- Cleanup: drop unused tables introduced in early development
DROP TABLE IF EXISTS llx_openorderbook_customer_pn;
DROP TABLE IF EXISTS llx_openorderbook_line_supplier;
DROP TABLE IF EXISTS llx_openorderbook_link;

