-- Slice 8h-GoodsScrub · PC + Procurement 7-pillar scrub document (mirror of works_cr_*)

/* ── Header — one scrub per CONFIRMED Client PO ─────────────────────── */
CREATE TABLE IF NOT EXISTS `goods_scrubs` (
    `c0d`                 INT AUTO_INCREMENT PRIMARY KEY,
    `client_po_id`        INT           NOT NULL,
    `project_id`          INT           NOT NULL,
    `scrub_no`            VARCHAR(30)   NOT NULL,
    `status`              ENUM('DRAFT','COMPLETE','CANCELLED') NOT NULL DEFAULT 'DRAFT',

    -- 7-pillar totals + grand total
    `total_preliminaries` DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_manpower`      DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_materials`     DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_equipment`     DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_tools`         DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_subcontracts`  DECIMAL(20,2) NOT NULL DEFAULT 0,
    `total_overheads`     DECIMAL(20,2) NOT NULL DEFAULT 0,
    `grand_total`         DECIMAL(20,2) NOT NULL DEFAULT 0,

    `notes`               TEXT,
    `completed_by`        INT           NOT NULL DEFAULT 0,
    `completed_by_name`   VARCHAR(120)  NOT NULL DEFAULT '',
    `completed_on`        TIMESTAMP     NULL     DEFAULT NULL,
    `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,
    `deleted`             TINYINT(1)    NOT NULL DEFAULT 0,

    UNIQUE KEY `uq_client_po`  (`client_po_id`, `deleted`),
    UNIQUE KEY `uq_scrub_no`   (`scrub_no`),
    KEY `ix_project`           (`project_id`, `status`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 1 · Preliminaries (description + flat amount) ────────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_preliminaries` (
    `c0d`         INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`    INT           NOT NULL,
    `line_no`     INT           NOT NULL DEFAULT 0,
    `description` VARCHAR(500)  NOT NULL,
    `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,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 2 · Manpower (role × persons × days × rate) ─────────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_manpower` (
    `c0d`        INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`   INT           NOT NULL,
    `line_no`    INT           NOT NULL DEFAULT 0,
    `role`       VARCHAR(120)  NOT NULL,
    `persons`    INT           NOT NULL DEFAULT 1,
    `days`       DECIMAL(10,2) NOT NULL DEFAULT 1,
    `rate`       DECIMAL(20,4) NOT NULL DEFAULT 0,
    `total`      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,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 3 · Materials (seeded from client_po_items; PC/Proc enrich unit_cost) ─ */
CREATE TABLE IF NOT EXISTS `goods_scrub_materials` (
    `c0d`               INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`          INT           NOT NULL,
    `client_po_item_id` INT           NOT NULL DEFAULT 0,   -- 0 = ad-hoc line added at scrub time
    `line_no`           INT           NOT NULL DEFAULT 0,
    `description`       VARCHAR(500)  NOT NULL,
    `brand`             VARCHAR(120)  NOT NULL DEFAULT '',
    `source_pref`       ENUM('LOCAL','IMPORT','ANY') NOT NULL DEFAULT 'ANY',
    `qty`               DECIMAL(20,4) NOT NULL DEFAULT 0,
    `unit`              VARCHAR(20)   NOT NULL DEFAULT '',
    `unit_cost`         DECIMAL(20,4) NOT NULL DEFAULT 0,   -- estimated cost of goods (PC/Proc)
    `total`             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,
    KEY `ix_scrub`      (`scrub_id`, `deleted`),
    KEY `ix_po_item`    (`client_po_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 4 · Equipment (hours × rate, in-house flag) ─────────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_equipment` (
    `c0d`           INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`      INT           NOT NULL,
    `line_no`       INT           NOT NULL DEFAULT 0,
    `description`   VARCHAR(500)  NOT NULL,
    `asset_id`      INT           NOT NULL DEFAULT 0,
    `hours`         DECIMAL(10,2) NOT NULL DEFAULT 0,
    `rate`          DECIMAL(20,4) NOT NULL DEFAULT 0,
    `total`         DECIMAL(20,2) NOT NULL DEFAULT 0,
    `in_house_flag` TINYINT(1)    NOT NULL DEFAULT 1,
    `notes`         VARCHAR(500)  NOT NULL DEFAULT '',
    `created_on`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted`       TINYINT(1)    NOT NULL DEFAULT 0,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 5 · Tools (qty × unit_cost) ─────────────────────────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_tools` (
    `c0d`         INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`    INT           NOT NULL,
    `line_no`     INT           NOT NULL DEFAULT 0,
    `description` VARCHAR(500)  NOT NULL,
    `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,
    `notes`       VARCHAR(500)  NOT NULL DEFAULT '',
    `created_on`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted`     TINYINT(1)    NOT NULL DEFAULT 0,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 6 · Subcontracts (lump sum per subcontractor) ───────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_subcontracts` (
    `c0d`                INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`           INT           NOT NULL,
    `line_no`            INT           NOT NULL DEFAULT 0,
    `subcontractor_name` VARCHAR(200)  NOT NULL,
    `scope`              VARCHAR(500)  NOT NULL DEFAULT '',
    `lump_sum`           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,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Pillar 7 · Overheads (category, description, amount) ───────────── */
CREATE TABLE IF NOT EXISTS `goods_scrub_overheads` (
    `c0d`         INT AUTO_INCREMENT PRIMARY KEY,
    `scrub_id`    INT           NOT NULL,
    `line_no`     INT           NOT NULL DEFAULT 0,
    `category`    VARCHAR(60)   NOT NULL DEFAULT 'OTHER',
    `description` VARCHAR(500)  NOT NULL,
    `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,
    KEY `ix_scrub` (`scrub_id`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
