<?php
/**
 * PacificDashboard — pulls the operational KPIs shown on the Pacific home
 * dashboard. All queries are tenant-scoped and count only non-deleted rows.
 * One PDO connection per query is fine for a dashboard (~15 counts); this
 * page is not on the hot path.
 */
class PacificDashboard extends Model
{
    private function tenant(): int { return defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1; }

    private function count(string $sql, array $params = []): int
    {
        $rs = (new DB())->mySelect($sql, $params);
        return (int)($rs[0]['n'] ?? 0);
    }

    public function kpis(): array
    {
        $t = $this->tenant();

        return [
            // Fleet
            'trucks_total'        => $this->count("SELECT COUNT(*) AS n FROM trucks WHERE deleted=0 AND tenant_id=:t", [':t' => $t]),
            'trucks_active'       => $this->count("SELECT COUNT(*) AS n FROM trucks WHERE deleted=0 AND tenant_id=:t AND status='active'", [':t' => $t]),
            'trucks_maintenance'  => $this->count("SELECT COUNT(*) AS n FROM trucks WHERE deleted=0 AND tenant_id=:t AND status='maintenance'", [':t' => $t]),

            // Drivers
            'drivers_total'       => $this->count("SELECT COUNT(*) AS n FROM drivers WHERE deleted=0 AND tenant_id=:t", [':t' => $t]),
            'drivers_available'   => $this->count("SELECT COUNT(*) AS n FROM drivers WHERE deleted=0 AND tenant_id=:t AND status='available'", [':t' => $t]),
            'drivers_on_trip'     => $this->count("SELECT COUNT(*) AS n FROM drivers WHERE deleted=0 AND tenant_id=:t AND status='on_trip'", [':t' => $t]),

            // Customers
            'customers_active'    => $this->count("SELECT COUNT(*) AS n FROM customers WHERE deleted=0 AND status='ACTIVE'"),

            // Assignments
            'assignments_active'  => $this->count("SELECT COUNT(*) AS n FROM driver_truck_assignments WHERE deleted=0 AND tenant_id=:t AND assigned_to IS NULL", [':t' => $t]),

            // Trip Requests
            'requests_draft'      => $this->count("SELECT COUNT(*) AS n FROM trip_requests WHERE deleted=0 AND tenant_id=:t AND status='draft'", [':t' => $t]),
            'requests_pending'    => $this->count("SELECT COUNT(*) AS n FROM trip_requests WHERE deleted=0 AND tenant_id=:t AND status='pending_review'", [':t' => $t]),
            'requests_approved'   => $this->count("SELECT COUNT(*) AS n FROM trip_requests WHERE deleted=0 AND tenant_id=:t AND status='approved'", [':t' => $t]),

            // Trips
            'trips_dispatched'    => $this->count("SELECT COUNT(*) AS n FROM trips WHERE deleted=0 AND tenant_id=:t AND status='dispatched'", [':t' => $t]),
            'trips_in_transit'    => $this->count("SELECT COUNT(*) AS n FROM trips WHERE deleted=0 AND tenant_id=:t AND status='in_transit'", [':t' => $t]),
            'trips_delivered'     => $this->count("SELECT COUNT(*) AS n FROM trips WHERE deleted=0 AND tenant_id=:t AND status='delivered'", [':t' => $t]),
            'trips_closed_month'  => $this->count("SELECT COUNT(*) AS n FROM trips WHERE deleted=0 AND tenant_id=:t AND status='closed' AND closed_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)", [':t' => $t]),

            // Compliance alerts
            'truck_docs_expired'  => $this->count("SELECT COUNT(*) AS n FROM truck_docs WHERE deleted=0 AND tenant_id=:t AND expires_on IS NOT NULL AND expires_on < CURDATE()", [':t' => $t]),
            'truck_docs_expiring' => $this->count("SELECT COUNT(*) AS n FROM truck_docs WHERE deleted=0 AND tenant_id=:t AND expires_on IS NOT NULL AND expires_on >= CURDATE() AND expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)", [':t' => $t]),

            'driver_licences_expired'  => $this->count("SELECT COUNT(*) AS n FROM drivers WHERE deleted=0 AND tenant_id=:t AND licence_expires IS NOT NULL AND licence_expires < CURDATE()", [':t' => $t]),
            'driver_licences_expiring' => $this->count("SELECT COUNT(*) AS n FROM drivers WHERE deleted=0 AND tenant_id=:t AND licence_expires IS NOT NULL AND licence_expires >= CURDATE() AND licence_expires <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)", [':t' => $t]),

            // Revenue snapshot (month-to-date closed trips)
            'revenue_month_tzs'   => (float)((new DB())->mySelect(
                "SELECT COALESCE(SUM(invoice_amount), 0) AS s
                   FROM trips WHERE deleted=0 AND tenant_id=:t AND status='closed'
                    AND currency='TZS'
                    AND closed_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
                [':t' => $t]
            )[0]['s'] ?? 0),

            // ── Iter 2 KPIs — Financial Ops ──────────────────────────────
            'work_orders_open'       => $this->count("SELECT COUNT(*) AS n FROM work_orders WHERE deleted=0 AND tenant_id=:t AND status='open'", [':t' => $t]),
            'work_orders_in_progress'=> $this->count("SELECT COUNT(*) AS n FROM work_orders WHERE deleted=0 AND tenant_id=:t AND status='in_progress'", [':t' => $t]),
            'trucks_maintenance_now' => $this->count("SELECT COUNT(*) AS n FROM trucks WHERE deleted=0 AND tenant_id=:t AND status='maintenance'", [':t' => $t]),

            'fuel_spend_month_tzs' => (float)((new DB())->mySelect(
                "SELECT COALESCE(SUM(gross_amount), 0) AS s
                   FROM fuel_entries
                  WHERE deleted=0 AND tenant_id=:t AND status='posted' AND currency='TZS'
                    AND entry_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
                [':t' => $t]
            )[0]['s'] ?? 0),
            'fuel_litres_month'    => (float)((new DB())->mySelect(
                "SELECT COALESCE(SUM(liters), 0) AS s
                   FROM fuel_entries
                  WHERE deleted=0 AND tenant_id=:t AND status='posted'
                    AND entry_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
                [':t' => $t]
            )[0]['s'] ?? 0),

            'maintenance_spend_month_tzs' => (float)((new DB())->mySelect(
                "SELECT COALESCE(SUM(total_cost), 0) AS s
                   FROM work_orders
                  WHERE deleted=0 AND tenant_id=:t AND status='closed' AND currency='TZS'
                    AND closed_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
                [':t' => $t]
            )[0]['s'] ?? 0),

            'expenses_submitted'   => $this->count("SELECT COUNT(*) AS n FROM expenses WHERE deleted=0 AND tenant_id=:t AND status='submitted'", [':t' => $t]),
            'expenses_reviewed'    => $this->count("SELECT COUNT(*) AS n FROM expenses WHERE deleted=0 AND tenant_id=:t AND status='reviewed'", [':t' => $t]),
            'expenses_approved_unpaid' => $this->count("SELECT COUNT(*) AS n FROM expenses WHERE deleted=0 AND tenant_id=:t AND status='approved'", [':t' => $t]),

            'expenses_spend_month_tzs' => (float)((new DB())->mySelect(
                "SELECT COALESCE(SUM(gross_amount), 0) AS s
                   FROM expenses
                  WHERE deleted=0 AND tenant_id=:t AND status IN ('approved','paid') AND currency='TZS'
                    AND expense_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
                [':t' => $t]
            )[0]['s'] ?? 0),
        ];
    }

    /** Most recent trip activity — 5 rows on the dashboard. */
    public function recentTrips(int $limit = 5): array
    {
        $limit = max(1, min(50, $limit));
        return (new DB())->mySelect(
            "SELECT t.c0d, t.trip_no, t.status, t.cargo_description,
                    COALESCE(t.actual_start, t.dispatched_at, t.created_on) AS at,
                    d.full_name AS driver_name, tr.reg_no AS truck_reg,
                    c.fname AS customer_name
               FROM trips t
          LEFT JOIN drivers   d  ON d.c0d  = t.driver_id
          LEFT JOIN trucks    tr ON tr.c0d = t.truck_id
          LEFT JOIN customers c  ON c.c0d  = t.customer_id
              WHERE t.deleted = 0 AND t.tenant_id = :t
           ORDER BY at DESC
              LIMIT $limit",
            [':t' => $this->tenant()]
        ) ?: [];
    }

    /** Trip requests awaiting attention. */
    public function pendingRequests(int $limit = 5): array
    {
        $limit = max(1, min(50, $limit));
        return (new DB())->mySelect(
            "SELECT tr.c0d, tr.request_no, tr.status, tr.priority, tr.pickup_date,
                    tr.cargo_description, tr.quote_amount, tr.currency,
                    c.fname AS customer_name
               FROM trip_requests tr
          LEFT JOIN customers c ON c.c0d = tr.customer_id
              WHERE tr.deleted = 0 AND tr.tenant_id = :t
                AND tr.status IN ('pending_review','approved')
           ORDER BY tr.status='pending_review' DESC,
                    tr.priority='urgent' DESC, tr.priority='high' DESC,
                    tr.pickup_date ASC
              LIMIT $limit",
            [':t' => $this->tenant()]
        ) ?: [];
    }

    /** Compliance alerts — top expired/expiring truck docs, sorted by urgency. */
    public function complianceAlerts(int $limit = 8): array
    {
        $limit = max(1, min(50, $limit));
        return (new DB())->mySelect(
            "SELECT 'truck_doc' AS kind, d.c0d, d.truck_id AS entity_id,
                    dt.display_name AS doc_type, d.expires_on,
                    t.reg_no AS entity_label,
                    DATEDIFF(d.expires_on, CURDATE()) AS days_left
               FROM truck_docs d
          LEFT JOIN truck_doc_types dt ON dt.c0d = d.doc_type_id
          LEFT JOIN trucks          t  ON t.c0d  = d.truck_id
              WHERE d.deleted = 0 AND d.tenant_id = :t
                AND d.expires_on IS NOT NULL
                AND d.expires_on <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)

             UNION ALL

             SELECT 'driver_licence' AS kind, dr.c0d, dr.c0d AS entity_id,
                    'Driver licence' AS doc_type, dr.licence_expires AS expires_on,
                    dr.full_name AS entity_label,
                    DATEDIFF(dr.licence_expires, CURDATE()) AS days_left
               FROM drivers dr
              WHERE dr.deleted = 0 AND dr.tenant_id = :t
                AND dr.licence_expires IS NOT NULL
                AND dr.licence_expires <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)

          ORDER BY days_left ASC
             LIMIT $limit",
            [':t' => $this->tenant()]
        ) ?: [];
    }
}
