-- Slice 8L-TenderItems · schema
-- Buyer-facing item list per tender. Neutral: no cost, no markup.
-- CRM captures at intake. Pricing tab can later "Seed from tender items".
-- line_kind is a hint per tender.project_type:
--   goods       → GOOD
--   services    → SERVICE
--   works       → WORKS_BOQ
--   consultancy → SERVICE
-- LOGISTICS is available for any type (e.g., delivery/transport line on a Goods tender).

CREATE TABLE IF NOT EXISTS tender_items (
    c0d         INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    tender_id   INT UNSIGNED    NOT NULL,
    line_no     INT             NOT NULL DEFAULT 1,
    item_ref    VARCHAR(120)    NULL,
    description VARCHAR(500)    NOT NULL,
    qty         DECIMAL(20,4)   NOT NULL DEFAULT 1.0000,
    uom         VARCHAR(30)     NULL,
    spec_notes  VARCHAR(1000)   NULL,
    line_kind   ENUM('GOOD','SERVICE','WORKS_BOQ','LOGISTICS','OTHER') NOT NULL DEFAULT 'GOOD',
    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_ti_tender (tender_id, deleted, line_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
