<?php
/**
 * Alerts — one row per fired rule. Also owns the evaluation logic invoked by
 * both GpsPositions::ingest() (synchronous, cheap rules only) and
 * cron/tick.php (async, expensive rules like device_offline).
 *
 * Cooldown dedup: before creating a new alert, check for an existing OPEN alert
 * of the same (rule_id, device_id) within rule.cooldown_minutes — if present,
 * skip. Keeps SMS spam under control.
 */
class Alerts extends Model
{
    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['a.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['status']))   { $where[] = 'a.status   = :st'; $params[':st'] = trim($filters['status']); }
        if (!empty($filters['kind']))     { $where[] = 'a.kind     = :kd'; $params[':kd'] = trim($filters['kind']); }
        if (!empty($filters['severity'])) { $where[] = 'a.severity = :sv'; $params[':sv'] = trim($filters['severity']); }
        if (!empty($filters['truck_id']))  { $where[] = 'a.truck_id  = :ti'; $params[':ti'] = (int)$filters['truck_id']; }
        if (!empty($filters['from_date'])) { $where[] = 'a.ts >= :fd'; $params[':fd'] = trim($filters['from_date']) . ' 00:00:00'; }
        if (!empty($filters['to_date']))   { $where[] = 'a.ts <= :td'; $params[':td'] = trim($filters['to_date'])   . ' 23:59:59'; }

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

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM alerts a 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 a.c0d, a.rule_id, a.kind, a.severity, a.title, a.message,
                    a.ts, a.lat, a.lng, a.status, a.sms_sent, a.email_sent,
                    a.truck_id, a.device_id, a.driver_id, a.geofence_id,
                    a.acknowledged_at, a.acknowledged_by,
                    t.reg_no AS truck_reg, d.full_name AS driver_name,
                    g.device_code, gf.name AS geofence_name,
                    r.name AS rule_name
               FROM alerts a
          LEFT JOIN trucks       t  ON t.c0d = a.truck_id
          LEFT JOIN drivers      d  ON d.c0d = a.driver_id
          LEFT JOIN gps_devices  g  ON g.c0d = a.device_id
          LEFT JOIN geofences    gf ON gf.c0d = a.geofence_id
          LEFT JOIN alert_rules  r  ON r.c0d = a.rule_id
              WHERE $whereSql
           ORDER BY a.status = 'open' DESC,
                    FIELD(a.severity, 'critical','high','medium','low','info'),
                    a.ts DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

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

    public function counts(): array
    {
        $rs = (new DB())->mySelect(
            "SELECT status, COUNT(*) n FROM alerts WHERE tenant_id = :tenant GROUP BY status",
            [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
        $counts = ['open'=>0,'acknowledged'=>0,'resolved'=>0,'dismissed'=>0];
        foreach ($rs as $r) $counts[$r['status']] = (int)$r['n'];
        return $counts;
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT a.*, t.reg_no AS truck_reg, d.full_name AS driver_name,
                    g.device_code, gf.name AS geofence_name, r.name AS rule_name,
                    u.full_name AS acknowledged_by_name
               FROM alerts a
          LEFT JOIN trucks       t  ON t.c0d = a.truck_id
          LEFT JOIN drivers      d  ON d.c0d = a.driver_id
          LEFT JOIN gps_devices  g  ON g.c0d = a.device_id
          LEFT JOIN geofences    gf ON gf.c0d = a.geofence_id
          LEFT JOIN alert_rules  r  ON r.c0d = a.rule_id
          LEFT JOIN users        u  ON u.c0d = a.acknowledged_by
              WHERE a.c0d = :id LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    /**
     * Insert an alert with dedup cooldown. Returns the alert id or 0 if suppressed.
     * $rule is the alert_rules row; $ctx is {device_id, truck_id, driver_id,
     * geofence_id, trip_id, position_id, event_id, ts, lat, lng, title, message, payload}.
     */
    public function emit(array $rule, array $ctx): int
    {
        $db = new DB();
        $cooldown = max(0, (int)($rule['cooldown_minutes'] ?? 15));
        if ($cooldown > 0) {
            // Dedup key = (rule_id, kind, whichever entity fields are set in ctx).
            // Covers device (GPS), truck (PM, doc expiries), driver (doc expiries),
            // geofence (fence exit), and even the doc_id via payload_json for
            // doc-expiry cases where multiple docs of same kind belong to one truck.
            $conds = ['rule_id = :r', 'kind = :k', "status IN ('open','acknowledged')", "ts >= (NOW() - INTERVAL :cd MINUTE)"];
            $params = [':r' => (int)$rule['c0d'], ':k' => $rule['kind'], ':cd' => $cooldown];
            foreach (['device_id', 'truck_id', 'driver_id', 'geofence_id'] as $key) {
                if (!empty($ctx[$key])) {
                    $conds[]     = "$key = :$key";
                    $params[":$key"] = (int)$ctx[$key];
                }
            }
            // For doc expiries, further dedup by doc_id embedded in payload — otherwise
            // truck with 3 docs of different types would collapse into one.
            if (!empty($ctx['payload']['doc_id'])) {
                // PDO binds numerics as strings; JSON_EXTRACT returns a JSON
                // integer literal that fails a string comparison. Unquote both
                // sides so we compare string-vs-string.
                $conds[] = "JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.doc_id')) = :did";
                $params[':did'] = (string)(int)$ctx['payload']['doc_id'];
            }
            $exists = $db->mySelect(
                "SELECT c0d FROM alerts WHERE " . implode(' AND ', $conds) . " LIMIT 1",
                $params
            );
            if (!empty($exists)) return 0;
        }

        $r = $db->insertUpdate(
            "INSERT INTO alerts
                (tenant_id, rule_id, kind, severity, device_id, truck_id, driver_id,
                 geofence_id, trip_id, position_id, event_id,
                 ts, lat, lng, title, message, payload_json, status, created_on)
             VALUES
                (:tenant, :rid, :k, :sv, :did, :tid, :drv,
                 :gf, :trp, :pos, :ev,
                 :ts, :lat, :lng, :title, :msg, :pl, 'open', NOW())",
            [
                ':tenant'=> defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':rid'   => (int)$rule['c0d'],
                ':k'     => $rule['kind'],
                ':sv'    => $rule['severity'] ?? 'medium',
                ':did'   => $ctx['device_id']   ?? null,
                ':tid'   => $ctx['truck_id']    ?? null,
                ':drv'   => $ctx['driver_id']   ?? null,
                ':gf'    => $ctx['geofence_id'] ?? null,
                ':trp'   => $ctx['trip_id']     ?? null,
                ':pos'   => $ctx['position_id'] ?? null,
                ':ev'    => $ctx['event_id']    ?? null,
                ':ts'    => $ctx['ts'] ?? date('Y-m-d H:i:s'),
                ':lat'   => $ctx['lat'] ?? null,
                ':lng'   => $ctx['lng'] ?? null,
                ':title' => $ctx['title'] ?? $rule['name'],
                ':msg'   => $ctx['message'] ?? '',
                ':pl'    => isset($ctx['payload']) ? (is_string($ctx['payload']) ? $ctx['payload'] : json_encode($ctx['payload'])) : null,
            ]
        );
        return (int)$r['last_id'];
    }

    public function acknowledge(int $id, int $uid): array
    {
        (new DB())->insertUpdate(
            "UPDATE alerts SET status = 'acknowledged', acknowledged_by = :uid, acknowledged_at = NOW() WHERE c0d = :id AND status = 'open'",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Alert acknowledged.'];
    }

    public function resolve(int $id, int $uid, string $note): array
    {
        (new DB())->insertUpdate(
            "UPDATE alerts SET status = 'resolved', resolution_note = :n, acknowledged_by = COALESCE(acknowledged_by, :uid), acknowledged_at = COALESCE(acknowledged_at, NOW()) WHERE c0d = :id",
            [':n' => $note, ':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Alert resolved.'];
    }

    public function dismiss(int $id, int $uid, string $note = ''): array
    {
        (new DB())->insertUpdate(
            "UPDATE alerts SET status = 'dismissed', resolution_note = :n, acknowledged_by = :uid, acknowledged_at = NOW() WHERE c0d = :id",
            [':n' => $note, ':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Alert dismissed.'];
    }

    /**
     * Fan-out SMS/email queueing for a rule that just emitted an alert.
     * Shared between the sync path (GpsPositions::ingest) and the async
     * path (CronController::sweepDeviceOffline) so the notification
     * semantics are identical.
     */
    public static function queueNotifications(array $rule, int $alertId, string $title, string $body): void
    {
        if (!$alertId) return;
        $ob = new Outbox();
        if (!empty($rule['notify_sms_to'])) {
            foreach (self::csv($rule['notify_sms_to']) as $m) {
                $ob->queueSms($m, "[Pacific] {$title}", ['alert_id' => $alertId, 'source_type' => 'alert', 'source_id' => $alertId]);
            }
        }
        if (!empty($rule['notify_email_to'])) {
            $html = "<div style='font-family:-apple-system,\"Instrument Sans\",Segoe UI,sans-serif;background:#F5F7FB;padding:24px;'>"
                  . "<div style='max-width:520px;margin:0 auto;background:#fff;border:1px solid #E2E7EF;border-radius:8px;overflow:hidden;'>"
                  . "<div style='background:#2626F5;color:#fff;padding:14px 20px;font-weight:600;letter-spacing:.02em;'>Pacific Group · Fleet Alerts</div>"
                  . "<div style='padding:24px;'>"
                  . "<h3 style='color:#0F1220;margin:0 0 12px;font-size:18px;font-weight:600;'>" . htmlspecialchars($title) . "</h3>"
                  . "<p style='color:#0F1220;margin:0;line-height:1.55;'>" . nl2br(htmlspecialchars($body)) . "</p>"
                  . "</div>"
                  . "<div style='border-top:1px solid #EEF1F6;padding:12px 20px;font-size:11px;color:#6B7484;letter-spacing:.04em;text-transform:uppercase;'>Pacific Group Company Ltd</div>"
                  . "</div></div>";
            foreach (self::csv($rule['notify_email_to']) as $e) {
                $ob->queueEmail($e, "[Pacific Alert] {$title}", $html, ['alert_id' => $alertId, 'source_type' => 'alert', 'source_id' => $alertId]);
            }
        }
    }

    private static function csv(?string $s): array
    {
        if (!$s) return [];
        return array_values(array_filter(array_map('trim', explode(',', $s))));
    }
}
