<?php
/**
 * FleetAnalytics — cross-cutting reports over the ops + accounting data.
 *
 * Every method takes an inclusive [$from, $to] date range and returns
 * rows sorted for the intended view. All numbers come from journal_lines
 * with the analytical tags (truck_id / driver_id / customer_id) — the P&L
 * is always in sync with the ledger, no separate reporting table.
 *
 * Sign conventions:
 *   Revenue    = SUM(credit - debit) on lines with account_type = 'REVENUE'
 *   Cost       = SUM(debit  - credit) on lines with account_type = 'EXPENSE'
 *
 * These queries lean on the composite indexes added in slice 23.
 */
class FleetAnalytics extends Model
{
    /* ==================== fleet-wide summary ============================== */

    public function fleetSummary(string $from, string $to): array
    {
        $rs = (new DB())->mySelect(
            "SELECT
                SUM(CASE WHEN coa.account_type = 'REVENUE' THEN jl.credit - jl.debit ELSE 0 END) AS revenue,
                SUM(CASE WHEN coa.account_type = 'EXPENSE' THEN jl.debit  - jl.credit ELSE 0 END) AS costs,
                (SELECT COUNT(*) FROM trips WHERE deleted = 0 AND status = 'closed' AND actual_end BETWEEN :f2 AND :t2) AS closed_trips,
                (SELECT COALESCE(SUM(actual_km), 0) FROM trips WHERE deleted = 0 AND status = 'closed' AND actual_end BETWEEN :f3 AND :t3) AS total_km,
                (SELECT COUNT(DISTINCT truck_id) FROM trips WHERE deleted = 0 AND status = 'closed' AND actual_end BETWEEN :f4 AND :t4) AS active_trucks,
                (SELECT COUNT(DISTINCT driver_id) FROM trips WHERE deleted = 0 AND status = 'closed' AND actual_end BETWEEN :f5 AND :t5) AS active_drivers
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE je.entry_date BETWEEN :f AND :t AND je.status = 'POSTED'",
            [':f' => $from, ':t' => $to, ':f2' => $from, ':t2' => $to, ':f3' => $from, ':t3' => $to, ':f4' => $from, ':t4' => $to, ':f5' => $from, ':t5' => $to]
        );
        $r = $rs[0] ?? ['revenue'=>0,'costs'=>0,'closed_trips'=>0,'total_km'=>0,'active_trucks'=>0,'active_drivers'=>0];
        $r['margin']       = (float)$r['revenue'] - (float)$r['costs'];
        $r['margin_pct']   = (float)$r['revenue'] > 0 ? round($r['margin'] / (float)$r['revenue'] * 100, 1) : 0;
        $r['cost_per_km']  = (float)$r['total_km'] > 0 ? round((float)$r['costs'] / (float)$r['total_km'], 2) : 0;
        $r['revenue_per_km'] = (float)$r['total_km'] > 0 ? round((float)$r['revenue'] / (float)$r['total_km'], 2) : 0;
        return $r;
    }

    /* ==================== per-truck P&L =================================== */

    public function truckPnl(string $from, string $to): array
    {
        $rows = (new DB())->mySelect(
            "SELECT
                t.c0d AS truck_id, t.reg_no, TRIM(CONCAT(t.make, ' ', t.model)) AS truck_desc, t.current_odometer,
                COALESCE(fin.revenue, 0) AS revenue,
                COALESCE(fin.costs,   0) AS costs,
                COALESCE(fin.revenue, 0) - COALESCE(fin.costs, 0) AS margin,
                COALESCE(ops.trips, 0) AS trips,
                COALESCE(ops.km, 0) AS km
             FROM trucks t
        LEFT JOIN (
                    SELECT jl.truck_id,
                           SUM(CASE WHEN coa.account_type = 'REVENUE' THEN jl.credit - jl.debit ELSE 0 END) AS revenue,
                           SUM(CASE WHEN coa.account_type = 'EXPENSE' THEN jl.debit  - jl.credit ELSE 0 END) AS costs
                      FROM journal_lines jl
                      JOIN journal_entries je ON je.c0d = jl.journal_entry_id
                      JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
                     WHERE je.entry_date BETWEEN :f AND :t AND je.status = 'POSTED'
                       AND jl.truck_id IS NOT NULL
                  GROUP BY jl.truck_id
                 ) fin ON fin.truck_id = t.c0d
        LEFT JOIN (
                    SELECT truck_id, COUNT(*) AS trips, COALESCE(SUM(actual_km), 0) AS km
                      FROM trips
                     WHERE deleted = 0 AND status = 'closed'
                       AND actual_end BETWEEN :f2 AND :t2
                  GROUP BY truck_id
                 ) ops ON ops.truck_id = t.c0d
            WHERE t.deleted = 0
         ORDER BY margin DESC, revenue DESC",
            [':f' => $from, ':t' => $to, ':f2' => $from, ':t2' => $to]
        ) ?: [];

        foreach ($rows as &$r) {
            $r['margin_pct']  = (float)$r['revenue'] > 0 ? round((float)$r['margin'] / (float)$r['revenue'] * 100, 1) : 0;
            $r['cost_per_km'] = (float)$r['km'] > 0 ? round((float)$r['costs'] / (float)$r['km'], 2) : 0;
            $r['rev_per_km']  = (float)$r['km'] > 0 ? round((float)$r['revenue'] / (float)$r['km'], 2) : 0;
        }
        return $rows;
    }

    /* ==================== customer revenue ================================ */

    public function customerRevenue(string $from, string $to): array
    {
        return (new DB())->mySelect(
            "SELECT
                c.c0d AS customer_id,
                TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                c.phone_1 AS phone,
                COALESCE(fin.revenue, 0)   AS revenue,
                COALESCE(inv.invoiced, 0)  AS invoiced,
                COALESCE(inv.paid, 0)      AS paid,
                COALESCE(inv.invoiced, 0) - COALESCE(inv.paid, 0) AS outstanding,
                COALESCE(ops.trips, 0)     AS trips,
                COALESCE(ops.km, 0)        AS km
              FROM customers c
         LEFT JOIN (
                    SELECT jl.customer_id,
                           SUM(CASE WHEN coa.account_type = 'REVENUE' THEN jl.credit - jl.debit ELSE 0 END) AS revenue
                      FROM journal_lines jl
                      JOIN journal_entries je ON je.c0d = jl.journal_entry_id
                      JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
                     WHERE je.entry_date BETWEEN :f AND :t AND je.status = 'POSTED'
                       AND jl.customer_id IS NOT NULL
                  GROUP BY jl.customer_id
                 ) fin ON fin.customer_id = c.c0d
         LEFT JOIN (
                    SELECT customer_id,
                           SUM(total_amount) AS invoiced,
                           SUM(amount_paid)  AS paid
                      FROM customer_invoices
                     WHERE deleted = 0 AND status <> 'void' AND invoice_date BETWEEN :f2 AND :t2
                  GROUP BY customer_id
                 ) inv ON inv.customer_id = c.c0d
         LEFT JOIN (
                    SELECT customer_id, COUNT(*) AS trips, COALESCE(SUM(actual_km), 0) AS km
                      FROM trips
                     WHERE deleted = 0 AND status = 'closed'
                       AND actual_end BETWEEN :f3 AND :t3
                  GROUP BY customer_id
                 ) ops ON ops.customer_id = c.c0d
             WHERE c.deleted = 0
        HAVING revenue > 0 OR invoiced > 0 OR trips > 0
      ORDER BY revenue DESC, invoiced DESC",
            [':f' => $from, ':t' => $to, ':f2' => $from, ':t2' => $to, ':f3' => $from, ':t3' => $to]
        ) ?: [];
    }

    /* ==================== driver scorecards =============================== */

    /**
     * Behaviour score = 100 - penalties. Penalties:
     *   -5 per over_speed event per 1000 km
     *   -10 per fuel_drop event
     *   -3 per ignition_after_hours event
     *   -2 per harsh_brake event per 1000 km
     * Grade: A ≥ 90, B ≥ 75, C ≥ 60, D ≥ 40, F < 40.
     */
    public function driverScorecards(string $from, string $to): array
    {
        $rows = (new DB())->mySelect(
            "SELECT
                d.c0d AS driver_id, d.full_name, d.employee_no, d.phone_1 AS phone,
                COALESCE(ops.trips, 0)         AS trips,
                COALESCE(ops.km, 0)            AS km,
                COALESCE(ops.revenue, 0)       AS revenue_attributed,
                COALESCE(ev.over_speed, 0)     AS over_speed,
                COALESCE(ev.fuel_drop, 0)      AS fuel_drop,
                COALESCE(ev.after_hours, 0)    AS after_hours,
                COALESCE(ev.harsh_brake, 0)    AS harsh_brake
              FROM drivers d
         LEFT JOIN (
                    SELECT t.driver_id,
                           COUNT(*) AS trips,
                           COALESCE(SUM(t.actual_km), 0) AS km,
                           COALESCE(SUM(t.invoice_amount), 0) AS revenue
                      FROM trips t
                     WHERE t.deleted = 0 AND t.status = 'closed'
                       AND t.actual_end BETWEEN :f AND :t
                  GROUP BY t.driver_id
                 ) ops ON ops.driver_id = d.c0d
         LEFT JOIN (
                    SELECT tr.driver_id,
                           SUM(CASE WHEN e.kind = 'over_speed'           THEN 1 ELSE 0 END) AS over_speed,
                           SUM(CASE WHEN e.kind = 'fuel_drop'            THEN 1 ELSE 0 END) AS fuel_drop,
                           SUM(CASE WHEN e.kind = 'ignition_after_hours' THEN 1 ELSE 0 END) AS after_hours,
                           SUM(CASE WHEN e.kind = 'harsh_brake'          THEN 1 ELSE 0 END) AS harsh_brake
                      FROM gps_events e
                      JOIN trips tr ON tr.truck_id = e.truck_id
                                   AND tr.actual_start IS NOT NULL
                                   AND e.ts BETWEEN tr.actual_start AND COALESCE(tr.actual_end, tr.actual_start + INTERVAL 30 DAY)
                     WHERE e.ts BETWEEN :f2 AND :t2
                  GROUP BY tr.driver_id
                 ) ev ON ev.driver_id = d.c0d
             WHERE d.deleted = 0
        HAVING trips > 0
      ORDER BY revenue_attributed DESC",
            [':f' => $from, ':t' => $to, ':f2' => $from, ':t2' => $to]
        ) ?: [];

        // Compute score + grade in PHP so we can tune weights easily without
        // refactoring the SQL.
        foreach ($rows as &$r) {
            $km          = max(1, (float)$r['km']);          // avoid div-by-zero
            $per1000     = 1000 / $km;
            $penalty     =
                (5  * (int)$r['over_speed']  * $per1000) +
                (10 * (int)$r['fuel_drop']) +
                (3  * (int)$r['after_hours']) +
                (2  * (int)$r['harsh_brake'] * $per1000);
            $score = max(0, round(100 - $penalty, 1));
            $r['score'] = $score;
            $r['grade'] =
                $score >= 90 ? 'A' :
                ($score >= 75 ? 'B' :
                ($score >= 60 ? 'C' :
                ($score >= 40 ? 'D' : 'F')));
        }
        // Re-sort by score (descending) since penalties can shuffle order
        usort($rows, fn($a, $b) => $b['score'] <=> $a['score']);
        return $rows;
    }

    /* ══════════════════════════════════════════════════════════════════
       Truck deep-drill matrix — one row per truck, one column per metric.
       This is the "which truck should I fix / retire / celebrate" report.

       Columns:
         revenue          — customer-invoice credits attributable to this truck
         cost_direct      — expenses + work-order costs tagged with this truck
         fuel_l · fuel_$  — litres + spend from fuel_entries
         km               — actual_km on closed trips
         trips_closed     — count
         on_time_pct      — closed trips where actual_end ≤ planned_delivery
         downtime_days    — days on maintenance status (approx from work-order duration)
         gross_margin     — revenue − (cost_direct + fuel_$)
         cost_per_km      — (cost_direct + fuel_$) ÷ km
         revenue_per_km   — revenue ÷ km
         l_per_100km      — fuel_l × 100 ÷ km  (fuel efficiency)
       ══════════════════════════════════════════════════════════════════ */
    public function truckMatrix(string $from, string $to): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT tk.c0d AS truck_id, tk.reg_no, tk.make, tk.model, tk.status,
                    -- Ops
                    COALESCE(ops.trips,        0) AS trips_closed,
                    COALESCE(ops.km,           0) AS km,
                    COALESCE(ops.ontime,       0) AS ontime_count,
                    -- Fuel
                    COALESCE(fu.liters,        0) AS fuel_l,
                    COALESCE(fu.spend,         0) AS fuel_cost,
                    -- Revenue + direct costs from the ledger (tagged with truck_id)
                    COALESCE(fin.revenue,      0) AS revenue,
                    COALESCE(fin.cost_direct,  0) AS cost_direct,
                    -- Downtime from work-order lifecycle
                    COALESCE(wo.down_days,     0) AS downtime_days
               FROM trucks tk
          LEFT JOIN (
                    SELECT t.truck_id,
                           COUNT(*)                                 AS trips,
                           COALESCE(SUM(t.actual_km), 0)            AS km,
                           SUM(CASE WHEN t.planned_delivery IS NOT NULL
                                    AND t.actual_end <= t.planned_delivery THEN 1 ELSE 0 END) AS ontime
                      FROM trips t
                     WHERE t.deleted = 0 AND t.status = 'closed'
                       AND t.actual_end BETWEEN :f AND :t
                  GROUP BY t.truck_id
                 ) ops ON ops.truck_id = tk.c0d
          LEFT JOIN (
                    SELECT fe.truck_id,
                           COALESCE(SUM(fe.liters),       0) AS liters,
                           COALESCE(SUM(fe.gross_amount), 0) AS spend
                      FROM fuel_entries fe
                     WHERE fe.deleted = 0
                       AND fe.entry_date BETWEEN :f2 AND :t2
                  GROUP BY fe.truck_id
                 ) fu ON fu.truck_id = tk.c0d
          LEFT JOIN (
                    SELECT jl.truck_id,
                           SUM(CASE WHEN coa.account_type = 'REVENUE' THEN (jl.credit - jl.debit) ELSE 0 END) AS revenue,
                           SUM(CASE WHEN coa.account_type = 'EXPENSE' THEN (jl.debit  - jl.credit) ELSE 0 END) AS cost_direct
                      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 BETWEEN :f3 AND :t3
                      JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
                                                 AND coa.account_type IN ('REVENUE','EXPENSE')
                     WHERE jl.truck_id IS NOT NULL
                  GROUP BY jl.truck_id
                 ) fin ON fin.truck_id = tk.c0d
          LEFT JOIN (
                    -- Rough downtime: days elapsed between started_at and
                    -- completed_at (or 'now' if still open) for WOs in window.
                    SELECT truck_id,
                           SUM(GREATEST(DATEDIFF(COALESCE(completed_at, NOW()),
                                                 COALESCE(started_at, created_on)), 0)) AS down_days
                      FROM work_orders
                     WHERE deleted = 0
                       AND COALESCE(started_at, created_on) BETWEEN :f4 AND :t4
                  GROUP BY truck_id
                 ) wo ON wo.truck_id = tk.c0d
              WHERE tk.deleted = 0 AND tk.tenant_id = :ten
           ORDER BY revenue DESC, tk.reg_no",
            [
                ':f'   => $from, ':t'   => $to,
                ':f2'  => $from, ':t2'  => $to,
                ':f3'  => $from, ':t3'  => $to,
                ':f4'  => $from, ':t4'  => $to,
                ':ten' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
            ]
        ) ?: [];

        // Derive the ratio columns in PHP so the SQL stays readable.
        foreach ($rows as &$r) {
            $km   = (float)$r['km'];
            $rev  = (float)$r['revenue'];
            $cost = (float)$r['cost_direct'] + (float)$r['fuel_cost'];

            $r['gross_margin']    = round($rev - $cost, 2);
            $r['cost_per_km']     = $km > 0 ? round($cost / $km, 2) : null;
            $r['revenue_per_km']  = $km > 0 ? round($rev  / $km, 2) : null;
            $r['margin_per_km']   = $km > 0 ? round(($rev - $cost) / $km, 2) : null;
            $r['l_per_100km']     = $km > 0 ? round(((float)$r['fuel_l'] * 100) / $km, 2) : null;
            $r['on_time_pct']     = (int)$r['trips_closed'] > 0
                                    ? round(((int)$r['ontime_count'] / (int)$r['trips_closed']) * 100, 1)
                                    : null;
            $r['utilization_pct'] = round(((int)$r['trips_closed'] / max(1, $this->daysInRange($from, $to))) * 100, 1);
        }
        unset($r);
        return $rows;
    }

    /**
     * Everything for one truck: monthly revenue/cost trend, fuel trend,
     * closed trips list, recent work orders, incident (event) counts.
     */
    public function truckDeepDrill(int $truckId, string $from, string $to): array
    {
        $db = new DB();
        $truck = $db->mySelect(
            "SELECT c0d, reg_no, make, model, year, chassis_no, status,
                    current_odometer, fuel_tank_liters, capacity_kg
               FROM trucks WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $truckId]
        );
        if (empty($truck)) return ['truck' => null];

        // Monthly revenue + fuel-spend + cost trend (6 buckets)
        $monthly = $db->mySelect(
            "SELECT DATE_FORMAT(m.d, '%Y-%m') AS ym,
                    COALESCE(SUM(CASE WHEN coa.account_type='REVENUE' THEN (jl.credit - jl.debit) END), 0) AS rev,
                    COALESCE(SUM(CASE WHEN coa.account_type='EXPENSE' THEN (jl.debit  - jl.credit) END), 0) AS cost
               FROM (
                    SELECT DATE_FORMAT(:f, '%Y-%m-01') + INTERVAL n MONTH AS d
                      FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) x
                    ) m
          LEFT JOIN journal_entries je    ON DATE_FORMAT(je.entry_date, '%Y-%m') = DATE_FORMAT(m.d, '%Y-%m')
                                         AND je.status='POSTED' AND je.deleted=0
          LEFT JOIN journal_lines jl      ON jl.journal_entry_id = je.c0d AND jl.truck_id = :tk
          LEFT JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
           GROUP BY m.d
           ORDER BY m.d",
            [':f' => date('Y-m-01', strtotime('-5 months', strtotime($to))), ':tk' => $truckId]
        ) ?: [];

        // Fuel-entries per month
        $fuel = $db->mySelect(
            "SELECT DATE_FORMAT(entry_date, '%Y-%m') AS ym,
                    COALESCE(SUM(liters), 0)       AS liters,
                    COALESCE(SUM(gross_amount), 0) AS spend
               FROM fuel_entries
              WHERE truck_id = :tk AND deleted = 0
                AND entry_date BETWEEN :f AND :t
           GROUP BY ym
           ORDER BY ym",
            [':tk' => $truckId, ':f' => $from, ':t' => $to]
        ) ?: [];

        // Recent closed trips
        $trips = $db->mySelect(
            "SELECT t.c0d, t.trip_no, t.actual_start, t.actual_end, t.actual_km,
                    t.planned_delivery,
                    TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                    d.full_name AS driver_name
               FROM trips t
          LEFT JOIN customers c ON c.c0d = t.customer_id
          LEFT JOIN drivers  d  ON d.c0d = t.driver_id
              WHERE t.truck_id = :tk AND t.deleted = 0 AND t.status = 'closed'
                AND t.actual_end BETWEEN :f AND :t
           ORDER BY t.actual_end DESC
              LIMIT 10",
            [':tk' => $truckId, ':f' => $from, ':t' => $to]
        ) ?: [];

        // Work orders (maintenance history)
        $workOrders = $db->mySelect(
            "SELECT wo.c0d, wo.wo_no, wo.status, wo.total_cost,
                    wo.started_at, wo.completed_at,
                    v.vendor_name AS shop
               FROM work_orders wo
          LEFT JOIN vendors v ON v.c0d = wo.vendor_id
              WHERE wo.truck_id = :tk AND wo.deleted = 0
                AND COALESCE(wo.started_at, wo.created_on) BETWEEN :f AND :t
           ORDER BY wo.started_at DESC
              LIMIT 10",
            [':tk' => $truckId, ':f' => $from, ':t' => $to]
        ) ?: [];

        // GPS incidents by kind
        $incidents = $db->mySelect(
            "SELECT kind, COUNT(*) AS n
               FROM gps_events
              WHERE truck_id = :tk
                AND ts BETWEEN :f AND :t
           GROUP BY kind
           ORDER BY n DESC",
            [':tk' => $truckId, ':f' => $from, ':t' => $to]
        ) ?: [];

        return [
            'truck'       => $truck[0],
            'monthly'     => $monthly,
            'fuel'        => $fuel,
            'trips'       => $trips,
            'work_orders' => $workOrders,
            'incidents'   => $incidents,
        ];
    }

    /** Rough day-count between two Y-m-d strings — inclusive. */
    private function daysInRange(string $from, string $to): int
    {
        $a = strtotime($from); $b = strtotime($to);
        if (!$a || !$b) return 1;
        return max(1, (int)round(($b - $a) / 86400) + 1);
    }

    /* ==================== date range helper =============================== */

    /** Preset range → [from, to]. Returns Y-m-d strings. */
    public static function presetRange(string $key): array
    {
        $today = new DateTimeImmutable('today');
        switch ($key) {
            case 'this_month':
                return [$today->format('Y-m-01'), $today->format('Y-m-t')];
            case 'last_month':
                $last = $today->modify('first day of last month');
                return [$last->format('Y-m-01'), $last->format('Y-m-t')];
            case 'this_quarter':
                $q = (int)ceil((int)$today->format('n') / 3);
                $startMonth = ($q - 1) * 3 + 1;
                $from = $today->setDate((int)$today->format('Y'), $startMonth, 1);
                $to   = $from->modify('+3 months -1 day');
                return [$from->format('Y-m-d'), $to->format('Y-m-d')];
            case 'ytd':
                return [$today->format('Y-01-01'), $today->format('Y-m-d')];
            case 'last_12m':
                return [$today->modify('-12 months')->format('Y-m-d'), $today->format('Y-m-d')];
            case 'all_time':
            default:
                return ['2020-01-01', $today->format('Y-m-d')];
        }
    }
}
