-- Slice 8Z-x · TZ invoice enrichment + delivery photos + partial payment allocations
--
-- Adds:
--  1) goods_customer_delivery_photos — photo evidence at PoD (missing table)
--  2) sales.tin/vrn snapshots (denorm at issue so historical invoices don't drift)
--  3) sales WHT fields (Tanzania parastatals/NGOs/corporates withhold 5-15% at source)
--  4) sales payment_terms_days + dunning tracking + outstanding_amount denorm
--  5) invoice_receipt_allocations junction — one receipt can settle N invoices
--  6) receipts.wht_amount + wht_certificate_path (customer WHT paid at settlement time)

-- ── 1. goods_customer_delivery_photos ─────────────────────────────────
CREATE TABLE IF NOT EXISTS goods_customer_delivery_photos (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dn_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    original_name VARCHAR(300) NOT NULL,
    mime VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    kind ENUM('DELIVERY','SIGNATURE','OTHER') NOT NULL DEFAULT 'DELIVERY',
    caption VARCHAR(300) NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_by_name VARCHAR(160) NULL,
    uploaded_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_dn (dn_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2-4. sales enrichment ──────────────────────────────────────────────
SET @sch := DATABASE();
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='sales' AND column_name='customer_tin_snapshot');
SET @sql := IF(@c=0, "ALTER TABLE sales
    ADD COLUMN customer_tin_snapshot VARCHAR(40) NULL COMMENT 'TIN at issue time — frozen so historical invoices stay canonical',
    ADD COLUMN customer_vrn_snapshot VARCHAR(40) NULL,
    ADD COLUMN customer_address_snapshot TEXT NULL,
    ADD COLUMN payment_terms_days SMALLINT UNSIGNED NULL DEFAULT 30,
    ADD COLUMN wht_applicable TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN wht_percent DECIMAL(5,2) NULL,
    ADD COLUMN wht_expected_amount DECIMAL(20,2) NULL COMMENT 'Denorm at issue',
    ADD COLUMN wht_settled_amount DECIMAL(20,2) NOT NULL DEFAULT 0 COMMENT 'Sum of wht from receipts',
    ADD COLUMN outstanding_amount DECIMAL(20,2) NULL COMMENT 'Denorm = total - amount_paid - wht_settled_amount',
    ADD COLUMN days_overdue SMALLINT NULL COMMENT 'Denorm, refreshed daily by cron',
    ADD COLUMN first_reminder_sent_at DATETIME NULL,
    ADD COLUMN last_reminder_sent_at DATETIME NULL,
    ADD COLUMN reminder_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN issued_at DATETIME NULL,
    ADD COLUMN issued_by INT UNSIGNED NULL,
    ADD COLUMN issued_by_name VARCHAR(160) NULL,
    ADD COLUMN pod_dn_id INT UNSIGNED NULL COMMENT 'Delivery note the PC used to gate drafting',
    ADD KEY idx_dueoverdue (due_date, invoice_workflow_status),
    ADD KEY idx_outstanding (outstanding_amount)",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 5. invoice_receipt_allocations ────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoice_receipt_allocations (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sale_id INT UNSIGNED NOT NULL,
    receipt_id INT UNSIGNED NOT NULL,
    allocated_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
    wht_amount_of_allocation DECIMAL(20,2) NOT NULL DEFAULT 0,
    allocated_by INT UNSIGNED NULL,
    allocated_by_name VARCHAR(160) NULL,
    allocated_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes VARCHAR(500) NULL,
    KEY idx_sale (sale_id),
    KEY idx_receipt (receipt_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 6. receipts WHT capture ────────────────────────────────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='receipts' AND column_name='wht_amount');
SET @sql := IF(@c=0, "ALTER TABLE receipts
    ADD COLUMN wht_amount DECIMAL(20,2) NOT NULL DEFAULT 0 COMMENT 'Withholding tax deducted at source',
    ADD COLUMN wht_certificate_no VARCHAR(80) NULL,
    ADD COLUMN wht_certificate_path VARCHAR(500) NULL,
    ADD COLUMN wht_certificate_original_name VARCHAR(300) NULL,
    ADD COLUMN receipt_status ENUM('DRAFT','ISSUED','PENDING_BANK_CONFIRM','CONFIRMED','REVERSED') NOT NULL DEFAULT 'CONFIRMED'",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 7. Finance settings for dunning cadence ───────────────────────────
INSERT INTO finance_settings (setting_key, setting_value, description) VALUES
    ('dunning_soft_days',      '3',  'Days before due_date to send soft reminder.'),
    ('dunning_firm_days',      '7',  'Days past due_date to send firm reminder.'),
    ('dunning_fm_escalate',    '14', 'Days past due_date to escalate to FM.'),
    ('dunning_exec_escalate',  '30', 'Days past due_date to escalate to Executive.'),
    ('dunning_legal_flag',     '60', 'Days past due_date to flag for legal / debt collection.'),
    ('default_wht_percent',    '5',  'Default WHT % for local services (5%; 15% for rent/interest).')
ON DUPLICATE KEY UPDATE description = VALUES(description);
