-- Slice 8c-Close · CR + phase closure with variance snapshot

/* ── works_crs · closure stamps ─────────────────────────────────────── */
ALTER TABLE `works_crs`
    ADD COLUMN `closed_by`      INT          NOT NULL DEFAULT 0 AFTER `superseded_on`,
    ADD COLUMN `closed_by_name` VARCHAR(120) NOT NULL DEFAULT '' AFTER `closed_by`,
    ADD COLUMN `closed_on`      TIMESTAMP    NULL     DEFAULT NULL AFTER `closed_by_name`,
    ADD COLUMN `close_reason`   VARCHAR(500) NOT NULL DEFAULT '' AFTER `closed_on`;

/* ── works_phases · closure stamps ──────────────────────────────────── */
ALTER TABLE `works_phases`
    ADD COLUMN `closed_by`      INT          NOT NULL DEFAULT 0 AFTER `actual_end`,
    ADD COLUMN `closed_by_name` VARCHAR(120) NOT NULL DEFAULT '' AFTER `closed_by`,
    ADD COLUMN `closed_on`      TIMESTAMP    NULL     DEFAULT NULL AFTER `closed_by_name`;

/* ── Per-pillar variance snapshot frozen at CR close ────────────────── */
CREATE TABLE IF NOT EXISTS `works_cr_closure_variance` (
    `c0d`             INT AUTO_INCREMENT PRIMARY KEY,
    `cr_id`           INT NOT NULL,
    `pillar`          VARCHAR(20)  NOT NULL,
    `budget`          DECIMAL(20,2) NOT NULL DEFAULT 0,
    `sourced`         DECIMAL(20,2) NOT NULL DEFAULT 0,
    `delivered`       DECIMAL(20,2) NOT NULL DEFAULT 0,
    `paid`            DECIMAL(20,2) NOT NULL DEFAULT 0,
    `variance_amount` DECIMAL(20,2) NOT NULL DEFAULT 0,  -- paid − budget (negative = under-spend)
    `variance_pct`    DECIMAL(6,2)  NOT NULL DEFAULT 0,
    `created_on`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_cr_pillar` (`cr_id`, `pillar`),
    KEY `ix_cr` (`cr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
