-- Slice 8-UX · Regrouped contract terms + Executive-override audit trail
-- New columns on tender_contract_terms + new audit table. Idempotent.

-- ── Payment basis
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE tender_contract_terms ADD COLUMN payment_basis VARCHAR(20) NOT NULL DEFAULT "" AFTER payment_terms',
    'SELECT 1') FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tender_contract_terms' AND column_name='payment_basis');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Advance & APG bundle
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE tender_contract_terms
        ADD COLUMN apg_required TINYINT(1) NOT NULL DEFAULT 0 AFTER advance_pct,
        ADD COLUMN apg_bank VARCHAR(120) NOT NULL DEFAULT "" AFTER apg_required,
        ADD COLUMN apg_validity_months INT NOT NULL DEFAULT 0 AFTER apg_bank',
    'SELECT 1') FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tender_contract_terms' AND column_name='apg_required');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Performance guarantee bundle
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE tender_contract_terms
        ADD COLUMN pg_type VARCHAR(20) NOT NULL DEFAULT "" AFTER performance_bond_value,
        ADD COLUMN pg_bank VARCHAR(120) NOT NULL DEFAULT "" AFTER performance_bond_ref',
    'SELECT 1') FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tender_contract_terms' AND column_name='pg_type');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Retention + Warranty + LD
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE tender_contract_terms
        ADD COLUMN retention_release_trigger VARCHAR(255) NOT NULL DEFAULT "" AFTER retention_pct,
        ADD COLUMN warranty_coverage TEXT NULL AFTER warranty_months,
        ADD COLUMN ld_pct_per_day DECIMAL(5,3) NOT NULL DEFAULT 0.000 AFTER warranty_coverage,
        ADD COLUMN ld_cap_pct DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER ld_pct_per_day',
    'SELECT 1') FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tender_contract_terms' AND column_name='retention_release_trigger');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Tax & compliance
SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE tender_contract_terms
        ADD COLUMN tax_treatment VARCHAR(20) NOT NULL DEFAULT "" AFTER vat_treatment,
        ADD COLUMN tax_exemption_basis VARCHAR(255) NOT NULL DEFAULT "" AFTER tax_treatment,
        ADD COLUMN gl_code VARCHAR(60) NOT NULL DEFAULT "" AFTER wht_pct,
        ADD COLUMN fx_matches_cost_base TINYINT(1) NOT NULL DEFAULT 1 AFTER gl_code',
    'SELECT 1') FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='tender_contract_terms' AND column_name='tax_treatment');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Audit trail table (per-field changes)
CREATE TABLE IF NOT EXISTS tender_contract_terms_audit (
    c0d               INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tender_id         INT NOT NULL,
    changed_by        INT NOT NULL DEFAULT 0,
    changed_by_name   VARCHAR(120) NOT NULL DEFAULT '',
    changed_by_group  VARCHAR(60)  NOT NULL DEFAULT '',
    field_name        VARCHAR(80)  NOT NULL,
    old_value         TEXT NULL,
    new_value         TEXT NULL,
    reason            VARCHAR(500) NOT NULL DEFAULT '',
    post_approval     TINYINT(1) NOT NULL DEFAULT 0,
    changed_on        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_tender (tender_id, changed_on),
    KEY ix_actor  (changed_by, changed_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
