-- Pacific Logistics · slice 30 · Accounts Payable module (Iter 5 Turn 4)
--
-- Mirror of the AR module (invoices + receipts + aged receivables):
--   supplier_bills                  ← customer_invoices
--   supplier_payments               ← customer_receipts
--   supplier_payment_allocations    ← customer_receipt_allocations
--
-- Reuses the existing `vendors` table (rsflow legacy) as the supplier
-- master — no new supplier CRUD schema needed, just a thin controller
-- exposed to the Accounting group.
--
-- COA codes used:
--   4009  Trade Payables       (credit-natural, cleared by payments)
--   3008  VAT Recoverable      (input VAT on VAT-registered supplier bills)
--   3001/3002/3003/3006/3009  bank/cash/momo/fuel-card credit accounts

CREATE TABLE IF NOT EXISTS supplier_bills (
    c0d               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL DEFAULT 1,
    bill_no           VARCHAR(20) NOT NULL UNIQUE,
    supplier_ref      VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'Supplier invoice number as printed',
    vendor_id         INT UNSIGNED NOT NULL,
    bill_date         DATE         NOT NULL,
    due_date          DATE         NULL,
    payment_terms     VARCHAR(40)  NULL,
    currency          VARCHAR(6)   NOT NULL DEFAULT 'TZS',
    subtotal          DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    vat_amount        DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_amount      DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    amount_paid       DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    expense_account_id INT UNSIGNED NULL COMMENT 'Which COA the net gets debited to',
    status            VARCHAR(20)  NOT NULL DEFAULT 'draft' COMMENT 'draft|posted|paid|void',
    issued_by         INT UNSIGNED NULL,
    issued_at         DATETIME     NULL,
    journal_entry_id  INT UNSIGNED NULL,
    void_by           INT UNSIGNED NULL,
    void_at           DATETIME     NULL,
    void_reason       VARCHAR(255) NULL,
    notes             TEXT         NULL,
    created_by        INT UNSIGNED NULL,
    created_on        DATETIME     NULL,
    edited_by         INT UNSIGNED NULL,
    edited_on         DATETIME     NULL,
    deleted           TINYINT(1)   NOT NULL DEFAULT 0,
    KEY idx_sb_vendor    (vendor_id, bill_date),
    KEY idx_sb_due       (due_date, status),
    KEY idx_sb_status    (status, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS supplier_payments (
    c0d               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL DEFAULT 1,
    payment_no        VARCHAR(20) NOT NULL UNIQUE,
    vendor_id         INT UNSIGNED NOT NULL,
    payment_date      DATE         NOT NULL,
    amount            DECIMAL(14,2) NOT NULL,
    amount_allocated  DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    currency          VARCHAR(6)   NOT NULL DEFAULT 'TZS',
    payment_method    VARCHAR(30)  NOT NULL DEFAULT 'bank_transfer' COMMENT 'bank_transfer|mobile_money|cash|fuel_card',
    reference         VARCHAR(120) NULL,
    status            VARCHAR(20)  NOT NULL DEFAULT 'recorded' COMMENT 'recorded|void',
    journal_entry_id  INT UNSIGNED NULL,
    void_by           INT UNSIGNED NULL,
    void_at           DATETIME     NULL,
    void_reason       VARCHAR(255) NULL,
    notes             TEXT         NULL,
    created_by        INT UNSIGNED NULL,
    created_on        DATETIME     NULL,
    deleted           TINYINT(1)   NOT NULL DEFAULT 0,
    KEY idx_sp_vendor  (vendor_id, payment_date),
    KEY idx_sp_status  (status, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS supplier_payment_allocations (
    c0d              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id       INT UNSIGNED NOT NULL,
    bill_id          INT UNSIGNED NOT NULL,
    amount_applied   DECIMAL(14,2) NOT NULL,
    created_on       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_spa_payment (payment_id),
    KEY idx_spa_bill    (bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Grants — Executive, Finance Manager, Accountant, Administrator, Procurement
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- SupplierBills
(1, 'SupplierBills',  'index', 1, NOW()), (1, 'SupplierBills',  'view', 1, NOW()),
(2, 'SupplierBills',  'index', 1, NOW()), (2, 'SupplierBills',  'view', 1, NOW()), (2, 'SupplierBills',  'add', 1, NOW()), (2, 'SupplierBills',  'save', 1, NOW()), (2, 'SupplierBills',  'post', 1, NOW()), (2, 'SupplierBills',  'voidBill', 1, NOW()),
(3, 'SupplierBills',  'index', 1, NOW()), (3, 'SupplierBills',  'view', 1, NOW()), (3, 'SupplierBills',  'add', 1, NOW()), (3, 'SupplierBills',  'save', 1, NOW()), (3, 'SupplierBills',  'post', 1, NOW()), (3, 'SupplierBills',  'voidBill', 1, NOW()),
(7, 'SupplierBills',  'index', 1, NOW()), (7, 'SupplierBills',  'view', 1, NOW()), (7, 'SupplierBills',  'add', 1, NOW()), (7, 'SupplierBills',  'save', 1, NOW()),
(10,'SupplierBills',  'index', 1, NOW()), (10,'SupplierBills',  'view', 1, NOW()), (10,'SupplierBills',  'add', 1, NOW()), (10,'SupplierBills',  'save', 1, NOW()), (10,'SupplierBills',  'post', 1, NOW()), (10,'SupplierBills',  'voidBill', 1, NOW()),
-- SupplierPayments
(1, 'SupplierPayments','index', 1, NOW()), (1, 'SupplierPayments','view', 1, NOW()),
(2, 'SupplierPayments','index', 1, NOW()), (2, 'SupplierPayments','view', 1, NOW()), (2, 'SupplierPayments','record', 1, NOW()), (2, 'SupplierPayments','save', 1, NOW()), (2, 'SupplierPayments','voidPayment', 1, NOW()),
(3, 'SupplierPayments','index', 1, NOW()), (3, 'SupplierPayments','view', 1, NOW()), (3, 'SupplierPayments','record', 1, NOW()), (3, 'SupplierPayments','save', 1, NOW()), (3, 'SupplierPayments','voidPayment', 1, NOW()),
(10,'SupplierPayments','index', 1, NOW()), (10,'SupplierPayments','view', 1, NOW()), (10,'SupplierPayments','record', 1, NOW()), (10,'SupplierPayments','save', 1, NOW()), (10,'SupplierPayments','voidPayment', 1, NOW()),
-- AgedPayables
(1, 'AgedPayables',   'index', 1, NOW()),
(2, 'AgedPayables',   'index', 1, NOW()),
(3, 'AgedPayables',   'index', 1, NOW()),
(10,'AgedPayables',   'index', 1, NOW()),
-- Suppliers (thin CRUD wrapper on vendors)
(1, 'Suppliers', 'index', 1, NOW()), (1, 'Suppliers', 'view', 1, NOW()),
(2, 'Suppliers', 'index', 1, NOW()), (2, 'Suppliers', 'view', 1, NOW()), (2, 'Suppliers', 'add', 1, NOW()), (2, 'Suppliers', 'save', 1, NOW()), (2, 'Suppliers', 'edit', 1, NOW()), (2, 'Suppliers', 'update', 1, NOW()),
(3, 'Suppliers', 'index', 1, NOW()), (3, 'Suppliers', 'view', 1, NOW()), (3, 'Suppliers', 'add', 1, NOW()), (3, 'Suppliers', 'save', 1, NOW()), (3, 'Suppliers', 'edit', 1, NOW()), (3, 'Suppliers', 'update', 1, NOW()),
(7, 'Suppliers', 'index', 1, NOW()), (7, 'Suppliers', 'view', 1, NOW()), (7, 'Suppliers', 'add', 1, NOW()), (7, 'Suppliers', 'save', 1, NOW()), (7, 'Suppliers', 'edit', 1, NOW()), (7, 'Suppliers', 'update', 1, NOW()),
(10,'Suppliers', 'index', 1, NOW()), (10,'Suppliers', 'view', 1, NOW()), (10,'Suppliers', 'add', 1, NOW()), (10,'Suppliers', 'save', 1, NOW()), (10,'Suppliers', 'edit', 1, NOW()), (10,'Suppliers', 'update', 1, NOW());


SELECT 'slice 30 imported' AS status,
       (SELECT COUNT(*) FROM supplier_bills)     AS bills,
       (SELECT COUNT(*) FROM supplier_payments)  AS payments;
