<?php
/**
 * CustomerInvoices — trip billing engine.
 *
 * Lifecycle:
 *   draft → issued (posts JE, allocates invoice_no) → partially_paid → paid
 *   any → void
 *
 * The invoice binds N closed trips (or manual lines) to a customer, computes
 * VAT + totals, and on `issue()` posts a balanced journal:
 *   Dr 1110 Trade Receivables  (total)
 *   Cr 4010/4020 Revenue        (per line)
 *   Cr 2110 VAT Output          (aggregated)
 *
 * Analytical tags carry customer_id + trip_id + truck_id + driver_id on every
 * line so the P&L and per-truck reports drill through.
 */
class CustomerInvoices extends Model
{
    /**
     * Rewired to the client's COA on 2026-08-27 (Turn 1.5 · slice 21).
     * Single revenue line — service line / route / customer breakdown
     * comes off the analytical tags on the journal_lines, not extra
     * COA rows. VAT accounts were added specifically because the
     * client's own COA had no VAT lines (see slice 21 preamble).
     */
    private const AR_ACCOUNT       = '3007';   // Trade Receivables (was 1110)
    private const VAT_OUTPUT_ACC   = '4010';   // VAT Payable       (was 2110)
    private const DEFAULT_REVENUE  = '8001';   // Sales Revenue     (was 4010 Regional Freight)
    public  const VAT_RATE_DEFAULT = 18.00;    // Tanzania

    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return round((float)preg_replace('/[^\d\.\-]/', '', (string)$v), 2); }
    private function nn(int $v): ?int { return $v > 0 ? $v : null; }

    /* ==================== reads =========================================== */

    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['i.deleted = 0', 'i.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['q'])) {
            $where[] = '(i.invoice_no LIKE :q OR TRIM(CONCAT(c.fname, " ", c.lname)) LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))      { $where[] = 'i.status      = :st';  $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['customer_id'])) { $where[] = 'i.customer_id = :cid'; $params[':cid'] = (int)$filters['customer_id']; }
        if (!empty($filters['from_date']))   { $where[] = 'i.invoice_date >= :fd'; $params[':fd'] = $filters['from_date']; }
        if (!empty($filters['to_date']))     { $where[] = 'i.invoice_date <= :td'; $params[':td'] = $filters['to_date']; }
        if (!empty($filters['overdue']))     { $where[] = 'i.status IN ("issued","partially_paid") AND i.due_date < CURDATE()'; }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM customer_invoices i LEFT JOIN customers c ON c.c0d = i.customer_id WHERE $whereSql", $params);
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT i.c0d, i.invoice_no, i.invoice_date, i.due_date, i.currency,
                    i.subtotal, i.vat_amount, i.total_amount, i.amount_paid, i.status,
                    TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name, i.customer_id,
                    DATEDIFF(CURDATE(), i.due_date) AS days_overdue
               FROM customer_invoices i
          LEFT JOIN customers c ON c.c0d = i.customer_id
              WHERE $whereSql
           ORDER BY FIELD(i.status,'draft','issued','partially_paid','paid','void'), i.invoice_date DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function statusCounts(): array
    {
        $rs = (new DB())->mySelect(
            "SELECT status, COUNT(*) n, SUM(total_amount - amount_paid) outstanding
               FROM customer_invoices
              WHERE deleted = 0 AND tenant_id = :tenant
           GROUP BY status",
            [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
        $counts = ['draft'=>0,'issued'=>0,'partially_paid'=>0,'paid'=>0,'void'=>0,'outstanding'=>0.0];
        foreach ($rs as $r) {
            $counts[$r['status']] = (int)$r['n'];
            if (in_array($r['status'], ['issued','partially_paid'], true)) $counts['outstanding'] += (float)$r['outstanding'];
        }
        return $counts;
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT i.*, TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                    c.phone_1 AS customer_phone, c.email AS customer_email,
                    c.physical_address, c.city AS customer_city,
                    u.full_name AS issued_by_name
               FROM customer_invoices i
          LEFT JOIN customers c ON c.c0d = i.customer_id
          LEFT JOIN users     u ON u.c0d = i.issued_by
              WHERE i.c0d = :id AND i.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function linesFor(int $invoiceId): array
    {
        return (new DB())->mySelect(
            "SELECT l.*, t.trip_no
               FROM customer_invoice_lines l
          LEFT JOIN trips t ON t.c0d = l.trip_id
              WHERE l.invoice_id = :id
           ORDER BY l.line_no ASC",
            [':id' => $invoiceId]
        ) ?: [];
    }

    public function receiptsFor(int $invoiceId): array
    {
        return (new DB())->mySelect(
            "SELECT r.c0d AS receipt_id, r.receipt_no, r.receipt_date, r.amount, r.payment_method,
                    r.reference, r.status, a.amount_applied
               FROM customer_receipt_allocations a
               JOIN customer_receipts r ON r.c0d = a.receipt_id
              WHERE a.invoice_id = :id AND r.deleted = 0
           ORDER BY r.receipt_date DESC, r.c0d DESC",
            [':id' => $invoiceId]
        ) ?: [];
    }

    /** Closed trips for a customer that are not yet on any active invoice. */
    public function unbilledTripsForCustomer(int $customerId): array
    {
        return (new DB())->mySelect(
            "SELECT t.c0d AS trip_id, t.trip_no, t.actual_end, t.invoice_amount, t.currency,
                    t.actual_km, t.origin_address, t.destination_address,
                    t.truck_id, tr.reg_no AS truck_reg,
                    t.driver_id, d.full_name AS driver_name
               FROM trips t
          LEFT JOIN trucks  tr ON tr.c0d = t.truck_id
          LEFT JOIN drivers d  ON d.c0d  = t.driver_id
              WHERE t.customer_id = :cid AND t.deleted = 0 AND t.status = 'closed'
                AND t.invoice_amount > 0
                AND NOT EXISTS (
                    SELECT 1 FROM customer_invoice_lines l
                       JOIN customer_invoices i ON i.c0d = l.invoice_id
                     WHERE l.trip_id = t.c0d AND i.status <> 'void' AND i.deleted = 0
                )
           ORDER BY t.actual_end DESC",
            [':cid' => $customerId]
        ) ?: [];
    }

    public function customersWithUnbilled(): array
    {
        return (new DB())->mySelect(
            "SELECT c.c0d AS customer_id, TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                    COUNT(t.c0d) AS trips, SUM(t.invoice_amount) AS gross
               FROM trips t
               JOIN customers c ON c.c0d = t.customer_id
              WHERE t.deleted = 0 AND t.status = 'closed'
                AND t.invoice_amount > 0
                AND NOT EXISTS (
                    SELECT 1 FROM customer_invoice_lines l
                       JOIN customer_invoices i ON i.c0d = l.invoice_id
                     WHERE l.trip_id = t.c0d AND i.status <> 'void' AND i.deleted = 0
                )
           GROUP BY c.c0d
           ORDER BY gross DESC",
            []
        ) ?: [];
    }

    /* ==================== writes ========================================== */

    /** Create a draft invoice bundling N trips for one customer. */
    public function createFromTrips(int $customerId, array $tripIds, array $opts, int $uid): array
    {
        if ($customerId <= 0)   return ['ok' => false, 'msg' => 'Customer is required.'];
        if (empty($tripIds))    return ['ok' => false, 'msg' => 'Select at least one trip to bill.'];

        $db = new DB();

        // Load trips + guard: same customer, closed, unbilled
        $placeholders = implode(',', array_fill(0, count($tripIds), '?'));
        $trips = $db->mySelect(
            "SELECT t.c0d, t.trip_no, t.customer_id, t.status, t.invoice_amount, t.currency,
                    t.truck_id, t.driver_id, t.actual_end,
                    t.origin_address, t.destination_address, t.actual_km
               FROM trips t
              WHERE t.c0d IN ($placeholders) AND t.deleted = 0",
            $tripIds
        ) ?: [];
        if (count($trips) !== count($tripIds)) return ['ok' => false, 'msg' => 'One or more trips not found.'];
        foreach ($trips as $t) {
            if ((int)$t['customer_id'] !== $customerId) return ['ok' => false, 'msg' => "Trip {$t['trip_no']} belongs to a different customer."];
            if ($t['status'] !== 'closed')              return ['ok' => false, 'msg' => "Trip {$t['trip_no']} is not closed yet."];
            if ((float)$t['invoice_amount'] <= 0)       return ['ok' => false, 'msg' => "Trip {$t['trip_no']} has no invoice_amount."];
        }

        $currency = $opts['currency'] ?? ($trips[0]['currency'] ?: 'TZS');
        $vatRate  = isset($opts['vat_rate']) ? max(0, min(30, (float)$opts['vat_rate'])) : self::VAT_RATE_DEFAULT;

        $subtotal = 0.0; $vatTotal = 0.0; $grandTotal = 0.0;
        $linesOut = [];
        foreach ($trips as $i => $t) {
            $net = round((float)$t['invoice_amount'], 2);
            $vat = round($net * $vatRate / 100, 2);
            $tot = $net + $vat;
            $subtotal   += $net;
            $vatTotal   += $vat;
            $grandTotal += $tot;

            $route = trim(($t['origin_address'] ?? '') . ' → ' . ($t['destination_address'] ?? ''));
            if ($t['actual_km']) $route .= ' · ' . number_format((float)$t['actual_km'], 0) . ' km';

            $linesOut[] = [
                'line_no'       => $i + 1,
                'trip_id'       => (int)$t['c0d'],
                'description'   => "Trip {$t['trip_no']} — {$route}",
                'quantity'      => 1,
                'unit_price'    => $net,
                'net_amount'    => $net,
                'vat_rate'      => $vatRate,
                'vat_amount'    => $vat,
                'total_amount'  => $tot,
                'revenue_account' => $opts['revenue_account'] ?? self::DEFAULT_REVENUE,
                'truck_id'      => $this->nn((int)$t['truck_id']),
                'driver_id'     => $this->nn((int)$t['driver_id']),
                'route_summary' => mb_substr($route, 0, 255),
            ];
        }

        // Header
        $invDate = $opts['invoice_date'] ?? date('Y-m-d');
        $terms   = $this->s($opts['payment_terms'] ?? 'Net 30');
        $dueDate = $opts['due_date'] ?? $this->deriveDueDate($invDate, $terms);
        $draftNo = $this->nextDraftNo();

        $r = $db->insertUpdate(
            "INSERT INTO customer_invoices
                (tenant_id, invoice_no, customer_id, invoice_date, due_date, payment_terms, currency,
                 subtotal, vat_amount, total_amount, status,
                 billing_address, notes, footer_note, created_by, created_on)
             VALUES
                (:tenant, :ino, :cid, :idate, :ddate, :terms, :cur,
                 :sub, :vat, :tot, 'draft',
                 :addr, :notes, :footer, :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':ino'    => $draftNo,
                ':cid'    => $customerId,
                ':idate'  => $invDate,
                ':ddate'  => $dueDate,
                ':terms'  => $terms,
                ':cur'    => $currency,
                ':sub'    => round($subtotal, 2),
                ':vat'    => round($vatTotal, 2),
                ':tot'    => round($grandTotal, 2),
                ':addr'   => $this->s($opts['billing_address'] ?? ''),
                ':notes'  => $this->s($opts['notes'] ?? ''),
                ':footer' => $this->s($opts['footer_note'] ?? 'Please pay by bank transfer to the account on file. Reference the invoice number.'),
                ':uid'    => $uid,
            ]
        );
        $invoiceId = (int)$r['last_id'];
        if (!$invoiceId) return ['ok' => false, 'msg' => 'Could not save invoice.'];

        foreach ($linesOut as $ln) {
            $db->insertUpdate(
                "INSERT INTO customer_invoice_lines
                    (invoice_id, line_no, trip_id, description, quantity, unit_price,
                     net_amount, vat_rate, vat_amount, total_amount, revenue_account,
                     truck_id, driver_id, route_summary)
                 VALUES
                    (:iid, :ln, :tid, :desc, :qty, :up, :net, :vr, :va, :ta, :ra, :truck, :drv, :rt)",
                [
                    ':iid' => $invoiceId, ':ln' => $ln['line_no'], ':tid' => $ln['trip_id'],
                    ':desc'=> $ln['description'], ':qty' => $ln['quantity'], ':up' => $ln['unit_price'],
                    ':net' => $ln['net_amount'], ':vr' => $ln['vat_rate'], ':va' => $ln['vat_amount'], ':ta' => $ln['total_amount'],
                    ':ra'  => $ln['revenue_account'], ':truck' => $ln['truck_id'], ':drv' => $ln['driver_id'], ':rt' => $ln['route_summary'],
                ]
            );
        }

        return ['ok' => true, 'msg' => 'Draft invoice created.', 'id' => $invoiceId];
    }

    /** Issue: allocates real invoice_no, flips to 'issued', posts JE. Idempotent. */
    public function issue(int $id, int $uid, string $uname): array
    {
        $inv = $this->byId($id);
        if (!$inv)                        return ['ok' => false, 'msg' => 'Invoice not found.'];
        if ($inv['status'] !== 'draft')   return ['ok' => false, 'msg' => "Invoice is {$inv['status']} — can only issue drafts."];
        if ((float)$inv['total_amount'] <= 0) return ['ok' => false, 'msg' => 'Invoice total is zero — add lines first.'];

        $lines = $this->linesFor($id);
        if (empty($lines)) return ['ok' => false, 'msg' => 'Invoice has no lines.'];

        $db = new DB();
        $realNo = $this->nextInvoiceNo();

        // Build the journal:
        //   Dr 1110 AR (total_amount)
        //   Cr revenue_account per line (net_amount, aggregated by account)
        //   Cr 2110 VAT Output (sum vat_amount)
        $jeLines = [];
        $jeLines[] = [
            'account_code' => self::AR_ACCOUNT,
            'debit'        => round((float)$inv['total_amount'], 2),
            'narrative'    => "AR — Invoice {$realNo} " . $inv['customer_name'],
            'customer_id'  => (int)$inv['customer_id'],
        ];
        // Aggregate revenue by account code
        $revBucket = [];
        foreach ($lines as $ln) {
            $code = $ln['revenue_account'] ?: self::DEFAULT_REVENUE;
            $revBucket[$code][] = $ln;
        }
        foreach ($revBucket as $code => $lnGroup) {
            $sumNet = array_sum(array_map(fn($x) => (float)$x['net_amount'], $lnGroup));
            // If the bucket has exactly one line, tag its trip/truck/driver; if many, tag the first trip so the JE still drills.
            $first = $lnGroup[0];
            $jeLines[] = [
                'account_code' => $code,
                'credit'       => round($sumNet, 2),
                'narrative'    => 'Freight revenue — Invoice ' . $realNo,
                'customer_id'  => (int)$inv['customer_id'],
                'truck_id'     => (int)($first['truck_id']  ?? 0) ?: null,
                'driver_id'    => (int)($first['driver_id'] ?? 0) ?: null,
                'trip_id'      => (int)($first['trip_id']   ?? 0) ?: null,
            ];
        }
        if ((float)$inv['vat_amount'] > 0) {
            $jeLines[] = [
                'account_code' => self::VAT_OUTPUT_ACC,
                'credit'       => round((float)$inv['vat_amount'], 2),
                'narrative'    => 'VAT Output — Invoice ' . $realNo,
                'customer_id'  => (int)$inv['customer_id'],
            ];
        }

        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => (string)$inv['invoice_date'],
            'source_type'    => 'INVOICE_ISSUE',
            'source_id'      => $id,
            'source_ref'     => $realNo,
            'narrative'      => "Invoice {$realNo} issued to " . $inv['customer_name'],
            'currency'       => (string)$inv['currency'],
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines'          => $jeLines,
        ]);
        if (!$res['ok']) return ['ok' => false, 'msg' => 'JE failed: ' . $res['msg']];

        $db->insertUpdate(
            "UPDATE customer_invoices
                SET status = 'issued', invoice_no = :ino,
                    issued_by = :uid, issued_at = NOW(), journal_entry_id = :je,
                    edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':ino' => $realNo, ':uid' => $uid, ':je' => $res['id'], ':id' => $id]
        );

        return ['ok' => true, 'msg' => "Issued as {$realNo} · JE {$res['je_no']}.", 'invoice_no' => $realNo];
    }

    /** Void: reverses the JE (a mirror-image posting) and flips status. */
    public function voidInvoice(int $id, string $reason, int $uid, string $uname): array
    {
        $inv = $this->byId($id);
        if (!$inv)                              return ['ok' => false, 'msg' => 'Invoice not found.'];
        if ($inv['status'] === 'void')          return ['ok' => false, 'msg' => 'Invoice is already void.'];
        if ((float)$inv['amount_paid'] > 0)     return ['ok' => false, 'msg' => 'Cannot void — receipts have been applied. Void the receipts first.'];

        $db = new DB();
        // If it was already issued, post a reversing JE
        if ($inv['status'] !== 'draft' && $inv['journal_entry_id']) {
            $lines = $this->linesFor($id);
            $rev = [];
            $rev[] = ['account_code' => self::AR_ACCOUNT, 'credit' => (float)$inv['total_amount'], 'narrative' => "Reverse AR — Void {$inv['invoice_no']}", 'customer_id' => (int)$inv['customer_id']];
            $revBucket = [];
            foreach ($lines as $ln) $revBucket[$ln['revenue_account']][] = $ln;
            foreach ($revBucket as $code => $lnGroup) {
                $sumNet = array_sum(array_map(fn($x) => (float)$x['net_amount'], $lnGroup));
                $rev[] = ['account_code' => $code, 'debit' => round($sumNet, 2), 'narrative' => "Reverse revenue — Void {$inv['invoice_no']}", 'customer_id' => (int)$inv['customer_id']];
            }
            if ((float)$inv['vat_amount'] > 0) {
                $rev[] = ['account_code' => self::VAT_OUTPUT_ACC, 'debit' => (float)$inv['vat_amount'], 'narrative' => "Reverse VAT — Void {$inv['invoice_no']}", 'customer_id' => (int)$inv['customer_id']];
            }
            $jp = new JournalPosting();
            $r  = $jp->post([
                'entry_date'  => date('Y-m-d'),
                'source_type' => 'INVOICE_VOID',
                'source_id'   => $id,
                'source_ref'  => $inv['invoice_no'],
                'narrative'   => "Void invoice {$inv['invoice_no']}: {$reason}",
                'currency'    => $inv['currency'],
                'posted_by'   => $uid,
                'posted_by_name' => $uname,
                'lines'       => $rev,
            ]);
            if (!$r['ok']) return ['ok' => false, 'msg' => 'Reversal JE failed: ' . $r['msg']];
        }

        $db->insertUpdate(
            "UPDATE customer_invoices SET status = 'void', void_by = :uid, void_at = NOW(), void_reason = :r, edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':r' => $this->s($reason), ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Invoice voided.'];
    }

    /** Called by CustomerReceipts::allocate() to bump amount_paid + flip status. */
    public function refreshPaidState(int $id): void
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(a.amount_applied), 0) AS paid
               FROM customer_receipt_allocations a
               JOIN customer_receipts r ON r.c0d = a.receipt_id
              WHERE a.invoice_id = :id AND r.deleted = 0 AND r.status = 'recorded'",
            [':id' => $id]
        );
        $paid = round((float)($rs[0]['paid'] ?? 0), 2);

        $inv = $this->byId($id);
        if (!$inv) return;
        $total = (float)$inv['total_amount'];
        $status = $inv['status'];
        if ($status !== 'void') {
            if     ($paid <= 0)                $status = 'issued';
            elseif ($paid + 0.005 >= $total)   $status = 'paid';
            else                               $status = 'partially_paid';
        }
        $db->insertUpdate(
            "UPDATE customer_invoices SET amount_paid = :p, status = :s, edited_on = NOW() WHERE c0d = :id",
            [':p' => $paid, ':s' => $status, ':id' => $id]
        );
    }

    public function softDelete(int $id, int $uid): array
    {
        $inv = $this->byId($id);
        if (!$inv)                        return ['ok' => false, 'msg' => 'Invoice not found.'];
        if ($inv['status'] !== 'draft')   return ['ok' => false, 'msg' => 'Only draft invoices can be deleted. Void issued invoices instead.'];
        (new DB())->insertUpdate(
            "UPDATE customer_invoices SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true];
    }

    /* ==================== helpers ========================================= */

    private function nextInvoiceNo(): string
    {
        $year = (int)date('Y');
        $rs = (new DB())->mySelect(
            "SELECT invoice_no FROM customer_invoices WHERE invoice_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'INV-' . $year . '-%']
        );
        $seq = 1;
        if (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['invoice_no'], $m)) $seq = (int)$m[1] + 1;
        return sprintf('INV-%d-%04d', $year, $seq);
    }

    private function nextDraftNo(): string
    {
        $year = (int)date('Y');
        $rs = (new DB())->mySelect(
            "SELECT invoice_no FROM customer_invoices WHERE invoice_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'INV-DRAFT-' . $year . '-%']
        );
        $seq = 1;
        if (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['invoice_no'], $m)) $seq = (int)$m[1] + 1;
        return sprintf('INV-DRAFT-%d-%04d', $year, $seq);
    }

    private function deriveDueDate(string $invDate, string $terms): string
    {
        $days = 30;
        if (preg_match('/(\d+)/', $terms, $m)) $days = (int)$m[1];
        return date('Y-m-d', strtotime($invDate . ' +' . max(0, $days) . ' days'));
    }
}
