-- Slice 8c-Retention · retention holdings on Works LPO payments

/* ── Contract retention terms on the BOQ header ────────────────────── */
ALTER TABLE `works_boqs`
    ADD COLUMN `retention_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER `variation_value`,
    ADD COLUMN `dlp_days`      INT          NOT NULL DEFAULT 365 AFTER `retention_pct`;

/* ── One row per payment that had retention withheld ───────────────── */
CREATE TABLE IF NOT EXISTS `works_retention_holdings` (
    `c0d`               INT AUTO_INCREMENT PRIMARY KEY,
    `project_id`        INT           NOT NULL,
    `payment_id`        INT           NOT NULL,
    `lpo_id`            INT           NOT NULL,
    `cr_id`             INT           NOT NULL DEFAULT 0,
    `gross_amount`      DECIMAL(20,2) NOT NULL DEFAULT 0,   -- what the payment was for (before retention)
    `retention_pct`     DECIMAL(5,2)  NOT NULL DEFAULT 0,   -- pct applied at hold time
    `retention_amount`  DECIMAL(20,2) NOT NULL DEFAULT 0,
    `held_on`           DATE          NOT NULL,             -- payment_date
    `dlp_end_on`        DATE          NOT NULL,             -- held_on + dlp_days
    `status`            ENUM('HELD','RELEASED') NOT NULL DEFAULT 'HELD',
    `released_by`       INT           NOT NULL DEFAULT 0,
    `released_by_name`  VARCHAR(120)  NOT NULL DEFAULT '',
    `released_on`       TIMESTAMP     NULL     DEFAULT NULL,
    `released_notes`    VARCHAR(500)  NOT NULL DEFAULT '',
    `created_on`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_payment` (`payment_id`),                 -- one holding per payment (idempotent)
    KEY `ix_project` (`project_id`, `status`),
    KEY `ix_lpo`     (`lpo_id`),
    KEY `ix_dlp`     (`dlp_end_on`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
