-- Slice 8c-Certificate · Interim Payment Certificates (customer-side)

CREATE TABLE IF NOT EXISTS `works_ipcs` (
    `c0d`                 INT AUTO_INCREMENT PRIMARY KEY,
    `project_id`          INT           NOT NULL,
    `boq_id`              INT           NOT NULL,
    `ipc_no`              VARCHAR(30)   NOT NULL,
    `ipc_seq`             INT           NOT NULL DEFAULT 1,       -- sequential 1,2,3,... per project
    `ipc_date`            DATE          NOT NULL,
    `period_from`         DATE          DEFAULT NULL,
    `period_to`           DATE          DEFAULT NULL,
    `status`              ENUM('DRAFT','SUBMITTED','APPROVED','CANCELLED') NOT NULL DEFAULT 'DRAFT',

    /* Money — computed by recomputeTotals() from lines + BOQ.retention_pct */
    `certified_gross`     DECIMAL(20,2) NOT NULL DEFAULT 0,       -- sum of this-period line amounts
    `retention_pct`       DECIMAL(5,2)  NOT NULL DEFAULT 0,       -- copied from works_boqs at draft
    `retention_amount`    DECIMAL(20,2) NOT NULL DEFAULT 0,       -- certified_gross * retention_pct
    `advance_recovery`    DECIMAL(20,2) NOT NULL DEFAULT 0,       -- manual field per IPC
    `net_amount`          DECIMAL(20,2) NOT NULL DEFAULT 0,       -- certified_gross - retention - advance_recovery
    `vat_pct`             DECIMAL(6,2)  NOT NULL DEFAULT 18.00,
    `vat_amount`          DECIMAL(20,2) NOT NULL DEFAULT 0,
    `invoice_total`       DECIMAL(20,2) NOT NULL DEFAULT 0,       -- net_amount + vat_amount

    /* Linkage to sales invoice, populated on approve */
    `sale_id`             INT           NOT NULL DEFAULT 0,

    `notes`               TEXT,
    `created_by`          INT           NOT NULL DEFAULT 0,
    `created_by_name`     VARCHAR(120)  NOT NULL DEFAULT '',
    `created_on`          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `edited_by`           INT           NOT NULL DEFAULT 0,
    `edited_on`           TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `approved_by`         INT           NOT NULL DEFAULT 0,
    `approved_by_name`    VARCHAR(120)  NOT NULL DEFAULT '',
    `approved_on`         TIMESTAMP     NULL     DEFAULT NULL,
    `deleted`             TINYINT(1)    NOT NULL DEFAULT 0,

    UNIQUE KEY `uq_ipc_no` (`ipc_no`),
    UNIQUE KEY `uq_project_seq` (`project_id`, `ipc_seq`, `deleted`),
    KEY `ix_project_status` (`project_id`, `status`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `works_ipc_lines` (
    `c0d`                 INT AUTO_INCREMENT PRIMARY KEY,
    `ipc_id`              INT           NOT NULL,
    `boq_item_id`         INT           NOT NULL,
    `item_no`             VARCHAR(30)   NOT NULL DEFAULT '',
    `section`             VARCHAR(200)  NOT NULL DEFAULT '',
    `description`         VARCHAR(500)  NOT NULL DEFAULT '',
    `unit`                VARCHAR(20)   NOT NULL DEFAULT '',

    `contract_qty`        DECIMAL(20,4) NOT NULL DEFAULT 0,
    `contract_rate`       DECIMAL(20,4) NOT NULL DEFAULT 0,
    `contract_amount`     DECIMAL(20,2) NOT NULL DEFAULT 0,

    `previous_qty`        DECIMAL(20,4) NOT NULL DEFAULT 0,       -- cumulative through prior APPROVED IPCs
    `this_period_qty`     DECIMAL(20,4) NOT NULL DEFAULT 0,       -- editable
    `this_period_amount`  DECIMAL(20,2) NOT NULL DEFAULT 0,       -- this_period_qty * contract_rate
    `cumulative_qty`      DECIMAL(20,4) NOT NULL DEFAULT 0,       -- previous_qty + this_period_qty
    `cumulative_amount`   DECIMAL(20,2) NOT NULL DEFAULT 0,

    `notes`               VARCHAR(500)  NOT NULL DEFAULT '',
    `created_on`          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted`             TINYINT(1)    NOT NULL DEFAULT 0,

    UNIQUE KEY `uq_ipc_boq_item` (`ipc_id`, `boq_item_id`, `deleted`),
    KEY `ix_boq_item` (`boq_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
