<?php
/**
 * PacificExecDashboard — landing-page data for the executive / finance
 * leadership team.
 *
 * All reads over Pacific's own tables:
 *   Cash    : journal_lines on cash-flagged accounts + 3003/3006/3009
 *   AR      : customer_invoices (posted or partially_paid, not fully paid)
 *   AP      : supplier_bills   (posted or partially_paid, not fully paid)
 *   Revenue : posted journal_lines on account_type='REVENUE'
 *   Trips   : trips grouped by status
 *   Docs    : driver.licence_expires + truck_docs.expires_on + driver_docs.expires_on
 *
 * No caching — every method re-queries. The dashboard's an eyeballing tool,
 * not a report generator, and the queries are small (single-tenant, current
 * period) so freshness beats a snapshot table for this use case.
 */
class PacificExecDashboard extends Model
{
    private int $tenantId;

    public function __construct(?int $tenantId = null)
    {
        $this->tenantId = $tenantId ?? (defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1);
    }

    /* ══════════════════════════════════════════════════════════════════
       Financial summary — 4 headline KPIs + a trajectory chip.
       ══════════════════════════════════════════════════════════════════ */
    public function financialSummary(): array
    {
        $db = new DB();

        // Cash on hand = sum of all cash / cash-equivalent balances up to today
        $cash = (float)($db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               FROM journal_lines jl
               JOIN journal_entries    je  ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date <= CURDATE()
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1
                 OR coa.account_code IN ('3003','3006','3009')",
            []
        )[0]['bal'] ?? 0);

        // AR = open customer invoices, remaining balance
        $ar = (float)($db->mySelect(
            "SELECT COALESCE(SUM(total_amount - amount_paid), 0) AS ar
               FROM customer_invoices
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('posted','partially_paid')
                AND (total_amount - amount_paid) > 0.005",
            [':t' => $this->tenantId]
        )[0]['ar'] ?? 0);

        // AP = open supplier bills, remaining balance
        $ap = (float)($db->mySelect(
            "SELECT COALESCE(SUM(total_amount - amount_paid), 0) AS ap
               FROM supplier_bills
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('posted','paid')
                AND (total_amount - amount_paid) > 0.005",
            [':t' => $this->tenantId]
        )[0]['ap'] ?? 0);

        // Net position = cash + collectible AR − payable AP
        $net = $cash + $ar - $ap;

        if ($net < 0)                    { $chip = 'danger';  $label = 'Liquidity risk'; }
        elseif (($net - $cash) < 0)      { $chip = 'warning'; $label = 'Payables exceed AR'; }
        elseif ($ap > $cash)             { $chip = 'warning'; $label = 'AR-dependent cash cover'; }
        else                             { $chip = 'success'; $label = 'Healthy trajectory'; }

        return [
            'cash'       => round($cash, 2),
            'ar'         => round($ar, 2),
            'ap'         => round($ap, 2),
            'net'        => round($net, 2),
            'chip'       => $chip,
            'chip_label' => $label,
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Monthly revenue for a sparkline. Uses REVENUE account net-credit
       movement (matches P&L's revenue_total for the same month).
       ══════════════════════════════════════════════════════════════════ */
    public function monthlyRevenue(int $months = 6): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT DATE_FORMAT(je.entry_date, '%Y-%m') AS ym,
                    COALESCE(SUM(jl.credit - jl.debit), 0) AS rev
               FROM journal_lines jl
               JOIN journal_entries    je  ON je.c0d = jl.journal_entry_id
                                          AND je.status = 'POSTED' AND je.deleted = 0
                                          AND je.entry_date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'),
                                                                       INTERVAL :m MONTH)
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.account_type = 'REVENUE'
           GROUP BY DATE_FORMAT(je.entry_date, '%Y-%m')
           ORDER BY ym",
            [':m' => $months]
        ) ?: [];

        // Zero-fill missing months so the sparkline draws consistently.
        $byYm = [];
        foreach ($rows as $r) $byYm[$r['ym']] = (float)$r['rev'];

        $today = new DateTimeImmutable('first day of this month');
        $out = [];
        for ($i = $months - 1; $i >= 0; $i--) {
            $ts = $today->modify("-$i months");
            $ym = $ts->format('Y-m');
            $out[] = ['ym' => $ym, 'label' => $ts->format('M'), 'rev' => round($byYm[$ym] ?? 0, 2)];
        }
        return $out;
    }

    /* ══════════════════════════════════════════════════════════════════
       Top overdue customer invoices.
       ══════════════════════════════════════════════════════════════════ */
    public function topOverdueAr(int $limit = 5): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT ci.c0d, ci.invoice_no, ci.invoice_date, ci.due_date,
                    (ci.total_amount - ci.amount_paid) AS outstanding,
                    DATEDIFF(CURDATE(), COALESCE(ci.due_date, ci.invoice_date)) AS days_overdue,
                    ci.customer_id, TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name
               FROM customer_invoices ci
          LEFT JOIN customers c ON c.c0d = ci.customer_id
              WHERE ci.deleted = 0 AND ci.tenant_id = :t
                AND ci.status IN ('posted','partially_paid')
                AND (ci.total_amount - ci.amount_paid) > 0.005
                AND DATEDIFF(CURDATE(), COALESCE(ci.due_date, ci.invoice_date)) > 0
           ORDER BY days_overdue DESC, outstanding DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    /* ══════════════════════════════════════════════════════════════════
       Top due-soon (or overdue) supplier bills.
       ══════════════════════════════════════════════════════════════════ */
    public function topDueSoonAp(int $limit = 5): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT sb.c0d, sb.bill_no, sb.bill_date, sb.due_date,
                    (sb.total_amount - sb.amount_paid) AS outstanding,
                    DATEDIFF(COALESCE(sb.due_date, sb.bill_date), CURDATE()) AS days_to_due,
                    sb.vendor_id, v.vendor_name
               FROM supplier_bills sb
          LEFT JOIN vendors v ON v.c0d = sb.vendor_id
              WHERE sb.deleted = 0 AND sb.tenant_id = :t
                AND sb.status IN ('posted','paid')
                AND (sb.total_amount - sb.amount_paid) > 0.005
           ORDER BY sb.due_date IS NULL, sb.due_date, outstanding DESC
              LIMIT $limit",
            [':t' => $this->tenantId]
        ) ?: [];
    }

    /* ══════════════════════════════════════════════════════════════════
       Trip pipeline: how many trips sit in each status right now, plus a
       "closed this month" count so exec sees delivery volume.
       ══════════════════════════════════════════════════════════════════ */
    public function tripPipeline(): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT status, COUNT(*) AS n
               FROM trips
              WHERE deleted = 0 AND tenant_id = :t
                AND status IN ('dispatched','in_transit','delivered','closed')
           GROUP BY status",
            [':t' => $this->tenantId]
        ) ?: [];

        $out = ['dispatched' => 0, 'in_transit' => 0, 'delivered' => 0, 'closed_month' => 0];
        foreach ($rs as $r) {
            if ($r['status'] === 'closed') continue;
            $out[$r['status']] = (int)$r['n'];
        }
        $rs = $db->mySelect(
            "SELECT COUNT(*) AS n FROM trips
              WHERE deleted = 0 AND tenant_id = :t AND status = 'closed'
                AND actual_end >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)",
            [':t' => $this->tenantId]
        );
        $out['closed_month'] = (int)($rs[0]['n'] ?? 0);

        return $out;
    }

    /* ══════════════════════════════════════════════════════════════════
       Compliance — licence + truck-doc + driver-doc expiries.
       ══════════════════════════════════════════════════════════════════ */
    public function complianceCounts(): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                (SELECT COUNT(*) FROM drivers    WHERE deleted = 0 AND tenant_id = :t
                    AND licence_expires IS NOT NULL AND licence_expires < CURDATE()) AS lic_expired,
                (SELECT COUNT(*) FROM drivers    WHERE deleted = 0 AND tenant_id = :t
                    AND licence_expires >= CURDATE()
                    AND licence_expires <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS lic_soon,
                (SELECT COUNT(*) FROM truck_docs WHERE deleted = 0 AND tenant_id = :t
                    AND expires_on IS NOT NULL AND expires_on < CURDATE()) AS truck_expired,
                (SELECT COUNT(*) FROM truck_docs WHERE deleted = 0 AND tenant_id = :t
                    AND expires_on >= CURDATE()
                    AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS truck_soon,
                (SELECT COUNT(*) FROM driver_docs WHERE deleted = 0
                    AND expires_on IS NOT NULL AND expires_on < CURDATE()) AS drvdoc_expired,
                (SELECT COUNT(*) FROM driver_docs WHERE deleted = 0
                    AND expires_on >= CURDATE()
                    AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS drvdoc_soon",
            [':t' => $this->tenantId]
        );
        $r = $r[0] ?? [];
        foreach ($r as $k => $v) $r[$k] = (int)$v;
        $r['total_expired'] = $r['lic_expired'] + $r['truck_expired'] + $r['drvdoc_expired'];
        $r['total_soon']    = $r['lic_soon']    + $r['truck_soon']    + $r['drvdoc_soon'];
        return $r;
    }

    /* ══════════════════════════════════════════════════════════════════
       Recent journal activity — for the "what just posted" strip.
       ══════════════════════════════════════════════════════════════════ */
    public function recentJournals(int $limit = 10): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT je.c0d, je.je_no, je.entry_date, je.source_type,
                    je.total_debit, je.narrative
               FROM journal_entries je
              WHERE je.status = 'POSTED' AND je.deleted = 0
           ORDER BY je.posted_on DESC, je.c0d DESC
              LIMIT $limit",
            []
        ) ?: [];
    }
}
