-- Slice 8c-Fulfil · Wire Works CRs to fulfilment
--
-- 1) Stamp source_cr_* on lpos + lpo_items so every LPO knows which CR line it fulfils.
-- 2) works_phase_items_issued — the phase's authoritative on-site delivery register,
--    populated when goods physically land on site (independent of the LPO's own GRN flow).

/* ── 1. lpos · trace back to a CR line + phase ────────────────────────── */
ALTER TABLE `lpos`
    ADD COLUMN `source_cr_id`     INT         NOT NULL DEFAULT 0 AFTER `sourced_from_client_po_id`,
    ADD COLUMN `source_cr_pillar` VARCHAR(20) NOT NULL DEFAULT '' AFTER `source_cr_id`,
    ADD COLUMN `source_phase_id`  INT         NOT NULL DEFAULT 0 AFTER `source_cr_pillar`,
    ADD KEY `ix_source_cr`    (`source_cr_id`),
    ADD KEY `ix_source_phase` (`source_phase_id`);

/* ── 2. lpo_items · line-level trace to the originating CR line ────────── */
ALTER TABLE `lpo_items`
    ADD COLUMN `source_cr_pillar` VARCHAR(20) NOT NULL DEFAULT '' AFTER `client_po_item_id`,
    ADD COLUMN `source_cr_line_id` INT        NOT NULL DEFAULT 0 AFTER `source_cr_pillar`,
    ADD KEY `ix_source_cr_line` (`source_cr_line_id`);

/* ── 3. Phase-level physical delivery register ────────────────────────── */
CREATE TABLE IF NOT EXISTS `works_phase_items_issued` (
    `c0d`             INT AUTO_INCREMENT PRIMARY KEY,
    `phase_id`        INT         NOT NULL,
    `cr_id`           INT         NOT NULL DEFAULT 0,
    `cr_pillar`       VARCHAR(20) NOT NULL DEFAULT '',
    `cr_line_id`      INT         NOT NULL DEFAULT 0,
    `lpo_id`          INT         NOT NULL DEFAULT 0,
    `lpo_item_id`     INT         NOT NULL DEFAULT 0,
    `description`     VARCHAR(500) NOT NULL DEFAULT '',
    `qty`             DECIMAL(20,4) NOT NULL DEFAULT 0,
    `unit`            VARCHAR(20)  NOT NULL DEFAULT '',
    `unit_cost`       DECIMAL(20,4) NOT NULL DEFAULT 0,
    `total`           DECIMAL(20,2) NOT NULL DEFAULT 0,
    `issued_by`       INT          NOT NULL DEFAULT 0,
    `issued_by_name`  VARCHAR(120) NOT NULL DEFAULT '',
    `issued_on`       DATE         NOT NULL,
    `notes`           VARCHAR(500) NOT NULL DEFAULT '',
    `created_on`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted`         TINYINT(1)   NOT NULL DEFAULT 0,
    KEY `ix_phase`      (`phase_id`, `deleted`),
    KEY `ix_cr_pillar`  (`cr_id`, `cr_pillar`),
    KEY `ix_cr_line`    (`cr_line_id`),
    KEY `ix_lpo_item`   (`lpo_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
