-- Slice 8Z-r · Supplier LPO — structured payment terms + Issue to supplier (Slice D1)
--
-- Adds:
--  1) lpo_payment_terms — one row per term milestone on a SUPPLIER LPO.
--     Trigger types: ADVANCE / ON_ORDER / ON_DELIVERY / RETENTION / NET_DAYS / MILESTONE.
--     Percent-based OR fixed amount. `claimed_amount` accumulates as PRs get raised (Slice D2).
--  2) lpos.terms_summary        — denormalised human string ("30% Advance · 70% On Delivery") for lists.
--  3) lpos.issue_method         — Email / Portal / WhatsApp / Courier / In-person / Other.
--  4) lpos.issue_ref            — supplier-side ref (email msg id, tracking #, WhatsApp thread ref, etc.)
--  5) lpos.issue_note           — free-text note about how it was sent.
--  6) lpos.issue_attachment_*   — optional signed-LPO PDF stashed with the record.
--  7) lpos.issued_at / issued_by / issued_by_name — audit stamps.
--  8) Perms: Lpos.issueToSupplierAj, Lpos.addPaymentTermAj / updatePaymentTermAj / removePaymentTermAj.

-- ── 1. lpo_payment_terms ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS lpo_payment_terms (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lpo_id INT UNSIGNED NOT NULL,
    seq_no TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Display + payment order (1 = first PR raised)',
    label VARCHAR(120) NOT NULL COMMENT 'Human label — e.g. "Advance 30%", "Balance on delivery"',
    trigger_type ENUM('ADVANCE','ON_ORDER','ON_DELIVERY','RETENTION','NET_DAYS','MILESTONE') NOT NULL DEFAULT 'ON_DELIVERY',
    percent DECIMAL(6,2) NULL COMMENT 'Percent of LPO amount (nullable if fixed_amount is used)',
    fixed_amount DECIMAL(20,2) NULL COMMENT 'Fixed TZS override (nullable if percent is used)',
    computed_amount DECIMAL(20,2) NOT NULL DEFAULT 0 COMMENT 'Denorm = percent × lpo.amount OR fixed_amount',
    claimed_amount DECIMAL(20,2) NOT NULL DEFAULT 0 COMMENT 'Slice D2 · sum of PRs raised against this term',
    net_days SMALLINT UNSIGNED NULL COMMENT 'Only for NET_DAYS trigger — days after supplier invoice',
    retention_days SMALLINT UNSIGNED NULL COMMENT 'Only for RETENTION trigger — days after FULLY_RECEIVED',
    milestone_condition VARCHAR(500) NULL COMMENT 'Only for MILESTONE trigger — free-text condition text',
    grn_percent_gate DECIMAL(6,2) NULL COMMENT 'Only for ON_DELIVERY — GRN qty % must reach this before claimable (default 100)',
    notes VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_lpo (lpo_id),
    KEY idx_trigger (trigger_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 2-7. lpos columns ──────────────────────────────────────────────────
SET @sch := DATABASE();
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='lpos' AND column_name='terms_summary');
SET @sql := IF(@c=0, "ALTER TABLE lpos
    ADD COLUMN terms_summary VARCHAR(500) NULL AFTER receipt_status,
    ADD COLUMN issue_method VARCHAR(30) NULL,
    ADD COLUMN issue_ref VARCHAR(200) NULL,
    ADD COLUMN issue_note VARCHAR(500) NULL,
    ADD COLUMN issue_attachment_path VARCHAR(500) NULL,
    ADD COLUMN issue_attachment_original_name VARCHAR(300) NULL,
    ADD COLUMN issue_attachment_mime VARCHAR(120) NULL,
    ADD COLUMN issue_attachment_size_bytes INT UNSIGNED NULL,
    ADD COLUMN issued_at DATETIME NULL,
    ADD COLUMN issued_by INT UNSIGNED NULL,
    ADD COLUMN issued_by_name VARCHAR(160) NULL",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 8. Perms ───────────────────────────────────────────────────────────
-- Lpos.issueToSupplierAj + payment-term CRUD — Procurement + Administrator only.
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'Lpos', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'issueToSupplierAj' AS ac
            UNION ALL SELECT 'addPaymentTermAj'
            UNION ALL SELECT 'updatePaymentTermAj'
            UNION ALL SELECT 'removePaymentTermAj'
            UNION ALL SELECT 'issueAttachmentDownload') actions
WHERE g.user_group_name IN ('Procurement','Administrator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'Lpos' AND p.action_allowed = actions.ac);
