-- Slice 8S-OverheadPools · schema
-- Overhead pool lines per pricing version. Clearly separate from tender_items (buyer's spec) and
-- tender_pricing_lines (priced BOQ). display_mode controls whether the client sees the overhead:
--   PRELIMS_LINE   → renders as a visible "Preliminaries" section line on the client BOQ
--   DISTRIBUTED    → allocated across item line prices pro-rata by line_price (client sees higher unit prices)
--   INTERNAL_ONLY  → never surfaces to client (used for internal true-cost accounting only)

CREATE TABLE IF NOT EXISTS pricing_version_overheads (
    c0d          INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    version_id   INT UNSIGNED     NOT NULL,
    tender_id    INT UNSIGNED     NOT NULL,
    line_no      INT              NOT NULL DEFAULT 1,
    category     ENUM('MANPOWER','EQUIPMENT','TRANSPORT','DISMANTLE_ASSEMBLE','SITE_SETUP','ADMIN_OVERHEAD','CUSTOMS_DUTY','FREIGHT_INSURANCE','CLEARING_HANDLING','OTHER') NOT NULL DEFAULT 'OTHER',
    label        VARCHAR(200)     NOT NULL,
    amount       DECIMAL(20,2)    NOT NULL DEFAULT 0.00,
    display_mode ENUM('PRELIMS_LINE','DISTRIBUTED','INTERNAL_ONLY') NOT NULL DEFAULT 'DISTRIBUTED',
    notes        VARCHAR(500)     NULL,
    deleted      TINYINT(1)       NOT NULL DEFAULT 0,
    created_by      INT UNSIGNED  NULL,
    created_by_name VARCHAR(160)  NULL,
    created_on      TIMESTAMP     NULL DEFAULT CURRENT_TIMESTAMP,
    edited_by       INT UNSIGNED  NULL,
    edited_on       TIMESTAMP     NULL,
    PRIMARY KEY (c0d),
    KEY idx_pvo_version (version_id, deleted, line_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend tender_pricing_versions with procurement_mode + client_boq_visibility.
-- procurement_mode is Goods-specific for now (LOCAL / IMPORT / MIXED / NA-for-non-goods).
-- client_boq_visibility controls what the client-facing export shows:
--   LUMPSUM_ONLY       → total only (no BOQ)
--   ITEMS_ONLY         → flat item list with prices, no sections
--   ITEMS_AND_SECTIONS → grouped BOQ (default)
ALTER TABLE tender_pricing_versions
    ADD COLUMN procurement_mode ENUM('LOCAL','IMPORT','MIXED','NA') NOT NULL DEFAULT 'NA' AFTER lumpsum_notes,
    ADD COLUMN client_boq_visibility ENUM('LUMPSUM_ONLY','ITEMS_ONLY','ITEMS_AND_SECTIONS') NOT NULL DEFAULT 'ITEMS_AND_SECTIONS' AFTER procurement_mode,
    ADD COLUMN sum_overhead DECIMAL(20,2) NOT NULL DEFAULT 0.00 AFTER sum_cost;

-- Extend pricing_defaults with default procurement mode (for goods) + starter pool template hint.
ALTER TABLE pricing_defaults
    ADD COLUMN default_procurement_mode ENUM('LOCAL','IMPORT','MIXED','NA') NOT NULL DEFAULT 'LOCAL' AFTER default_currency,
    ADD COLUMN default_client_boq_visibility ENUM('LUMPSUM_ONLY','ITEMS_ONLY','ITEMS_AND_SECTIONS') NOT NULL DEFAULT 'ITEMS_AND_SECTIONS' AFTER default_procurement_mode;

-- Backfill existing goods versions to LOCAL as a sane default; keep others at NA.
UPDATE tender_pricing_versions v
   JOIN tender_tracker t ON t.c0d = v.tender_id
    SET v.procurement_mode = 'LOCAL'
  WHERE t.project_type = 'goods';
