-- Pacific Logistics · slice 19 · Trip Invoicing + Receipts + Aged Receivables
--
-- Iter 4 Turn 1 converts closed trips into billable customer invoices, records
-- customer payments (receipts) against them, and ages the receivables.
--
-- Data model:
--   customer_invoices          — one row per invoice sent to a customer
--   customer_invoice_lines     — one line per billable trip (or manual line)
--   customer_receipts          — one row per money-in event
--   customer_receipt_allocations — many-to-many: split a receipt across invoices
--
-- Journal posting policy (via JournalPosting::post):
--   ISSUE invoice:  Dr 1110 Trade Receivables (gross)
--                   Cr 4010 Regional Freight Revenue (or 4020 In-town — chosen per line)
--                   Cr 2110 VAT Output (if any)
--   APPLY receipt:  Dr <bank/cash per payment_method>   (allocated amount)
--                   Cr 1110 Trade Receivables            (allocated amount)
--
-- Analytical tags on every JE line: customer_id + trip_id + truck_id + driver_id
-- so the P&L drills to source.
--
-- Payment method → GL credit account (mirrors Iter 2 fuel/expense mapping):
--   cash          → 1010 Cash on Hand
--   petty_cash    → 1040 Petty Cash
--   mobile_money  → 1050 Mobile Money Float
--   bank_transfer → 1020 Bank - Main Operating
--   cheque        → 1020 Bank - Main Operating
--   fuel_card     → not applicable for receipts (this is money-in)


-- ═══════════════════════════════════════════════════════════════════════
-- customer_invoices — one per invoice, N trips per invoice
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS customer_invoices (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    invoice_no       VARCHAR(20)  NOT NULL,           -- INV-YYYY-NNNN (allocated on issue)
    customer_id      INT UNSIGNED NOT NULL,
    invoice_date     DATE         NOT NULL,
    due_date         DATE         NULL,
    payment_terms    VARCHAR(40)  NULL,               -- 'Net 30', 'On receipt', etc
    currency         VARCHAR(6)   NOT NULL DEFAULT 'TZS',
    -- Amounts
    subtotal         DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum(lines.net)
    vat_amount       DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_amount     DECIMAL(14,2) NOT NULL DEFAULT 0,   -- subtotal + vat
    amount_paid      DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum(allocated receipts)
    -- Lifecycle
    status           VARCHAR(20)  NOT NULL DEFAULT 'draft',
                                                    -- draft | issued | partially_paid | paid | void
    issued_by        INT UNSIGNED NULL,
    issued_at        DATETIME     NULL,
    journal_entry_id INT UNSIGNED NULL,             -- JE for the issue posting
    void_by          INT UNSIGNED NULL,
    void_at          DATETIME     NULL,
    void_reason      VARCHAR(255) NULL,
    -- Content
    billing_address  TEXT         NULL,             -- snapshot at issue time
    notes            TEXT         NULL,
    footer_note      VARCHAR(500) NULL,             -- payment instructions etc
    -- Audit
    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,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_ci_invoice_no (invoice_no),
    KEY idx_ci_customer_status (customer_id, status, deleted),
    KEY idx_ci_status_date (status, invoice_date),
    KEY idx_ci_due (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- customer_invoice_lines — one line per trip billed, or a manual line
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS customer_invoice_lines (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    invoice_id       INT UNSIGNED NOT NULL,
    line_no          INT UNSIGNED NOT NULL DEFAULT 1,
    trip_id          INT UNSIGNED NULL,              -- NULL for manual / adhoc lines
    description      VARCHAR(500) NOT NULL,
    quantity         DECIMAL(10,3) NOT NULL DEFAULT 1,
    unit_price       DECIMAL(14,2) NOT NULL DEFAULT 0,
    net_amount       DECIMAL(14,2) NOT NULL DEFAULT 0,
    vat_rate         DECIMAL(5,2)  NOT NULL DEFAULT 0,    -- e.g. 18.00
    vat_amount       DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_amount     DECIMAL(14,2) NOT NULL DEFAULT 0,
    revenue_account  VARCHAR(10)   NOT NULL DEFAULT '4010',    -- COA code
    -- Snapshot analytics from the trip (denormalised for reporting even if trip is later edited)
    truck_id         INT UNSIGNED NULL,
    driver_id        INT UNSIGNED NULL,
    route_summary    VARCHAR(255)  NULL,             -- "Dar → Chalinze · 400 km"
    PRIMARY KEY (c0d),
    KEY idx_cil_invoice (invoice_id, line_no),
    KEY idx_cil_trip    (trip_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- customer_receipts — one per money-in event
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS customer_receipts (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    receipt_no       VARCHAR(20)  NOT NULL,           -- RCPT-YYYY-NNNN
    customer_id      INT UNSIGNED NOT NULL,
    receipt_date     DATE         NOT NULL,
    amount           DECIMAL(14,2) NOT NULL,
    amount_allocated DECIMAL(14,2) NOT NULL DEFAULT 0, -- sum of allocations rows
    currency         VARCHAR(6)   NOT NULL DEFAULT 'TZS',
    payment_method   VARCHAR(30)  NOT NULL DEFAULT 'bank_transfer',
                                                    -- cash | petty_cash | mobile_money | bank_transfer | cheque
    reference        VARCHAR(120) NULL,             -- txn ref, cheque no, mobile ref
    -- Lifecycle
    status           VARCHAR(20)  NOT NULL DEFAULT 'recorded',
                                                    -- 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,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_cr_receipt_no (receipt_no),
    KEY idx_cr_customer (customer_id, status),
    KEY idx_cr_date (receipt_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- customer_receipt_allocations — one receipt splits across N invoices
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS customer_receipt_allocations (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    receipt_id       INT UNSIGNED NOT NULL,
    invoice_id       INT UNSIGNED NOT NULL,
    amount_applied   DECIMAL(14,2) NOT NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_cra (receipt_id, invoice_id),
    KEY idx_cra_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Accounting/Admin/Exec scope. Ops+Dispatcher can view + create,
-- Accountant can issue + void + record receipts.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- CustomerInvoices
(10, 'CustomerInvoices', 'index',    1, NOW()), (10, 'CustomerInvoices', 'view',      1, NOW()),
(10, 'CustomerInvoices', 'billTrips',1, NOW()), (10, 'CustomerInvoices', 'create',    1, NOW()),
(10, 'CustomerInvoices', 'edit',     1, NOW()), (10, 'CustomerInvoices', 'update',    1, NOW()),
(10, 'CustomerInvoices', 'issue',    1, NOW()), (10, 'CustomerInvoices', 'void',      1, NOW()),
(10, 'CustomerInvoices', 'delete',   1, NOW()), (10, 'CustomerInvoices', 'printable', 1, NOW()),
(30, 'CustomerInvoices', 'index',    1, NOW()), (30, 'CustomerInvoices', 'view',      1, NOW()),
(30, 'CustomerInvoices', 'billTrips',1, NOW()), (30, 'CustomerInvoices', 'create',    1, NOW()),
(30, 'CustomerInvoices', 'edit',     1, NOW()), (30, 'CustomerInvoices', 'update',    1, NOW()),
(30, 'CustomerInvoices', 'issue',    1, NOW()), (30, 'CustomerInvoices', 'void',      1, NOW()),
(30, 'CustomerInvoices', 'printable',1, NOW()),
(20, 'CustomerInvoices', 'index',    1, NOW()), (20, 'CustomerInvoices', 'view',      1, NOW()),
(20, 'CustomerInvoices', 'billTrips',1, NOW()), (20, 'CustomerInvoices', 'create',    1, NOW()),
(20, 'CustomerInvoices', 'printable',1, NOW()),
(22, 'CustomerInvoices', 'index',    1, NOW()), (22, 'CustomerInvoices', 'view',      1, NOW()),
(1,  'CustomerInvoices', 'index',    1, NOW()), (1,  'CustomerInvoices', 'view',      1, NOW()),
-- CustomerReceipts
(10, 'CustomerReceipts', 'index',    1, NOW()), (10, 'CustomerReceipts', 'view',      1, NOW()),
(10, 'CustomerReceipts', 'record',   1, NOW()), (10, 'CustomerReceipts', 'save',      1, NOW()),
(10, 'CustomerReceipts', 'void',     1, NOW()),
(30, 'CustomerReceipts', 'index',    1, NOW()), (30, 'CustomerReceipts', 'view',      1, NOW()),
(30, 'CustomerReceipts', 'record',   1, NOW()), (30, 'CustomerReceipts', 'save',      1, NOW()),
(30, 'CustomerReceipts', 'void',     1, NOW()),
(20, 'CustomerReceipts', 'index',    1, NOW()), (20, 'CustomerReceipts', 'view',      1, NOW()),
(1,  'CustomerReceipts', 'index',    1, NOW()), (1,  'CustomerReceipts', 'view',      1, NOW()),
-- AgedReceivables
(10, 'AgedReceivables', 'index',     1, NOW()),
(30, 'AgedReceivables', 'index',     1, NOW()),
(20, 'AgedReceivables', 'index',     1, NOW()),
(1,  'AgedReceivables', 'index',     1, NOW());
