<?php
class GpsDevices extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function nn(int $v): ?int { return $v > 0 ? $v : null; }

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

        if (!empty($filters['q'])) {
            $where[] = '(g.device_code LIKE :q OR g.sim_msisdn LIKE :q OR t.reg_no LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))      { $where[] = 'g.status      = :st'; $params[':st']  = $this->s($filters['status']); }
        if (!empty($filters['device_type'])) { $where[] = 'g.device_type = :dt'; $params[':dt']  = $this->s($filters['device_type']); }
        if (!empty($filters['unassigned']))  { $where[] = 'g.truck_id IS NULL'; }

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

        $countRs = $db->mySelect(
            "SELECT COUNT(*) AS n
               FROM gps_devices g
          LEFT JOIN trucks t ON t.c0d = g.truck_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 g.c0d, g.device_code, g.device_type, g.protocol, g.sim_msisdn,
                    g.status, g.last_seen_at, g.fuel_sensor, g.dashcam,
                    g.truck_id, t.reg_no AS truck_reg,
                    p.lat AS last_lat, p.lng AS last_lng, p.speed_kph AS last_speed, p.ts AS last_ts
               FROM gps_devices g
          LEFT JOIN trucks        t ON t.c0d = g.truck_id
          LEFT JOIN gps_positions p ON p.c0d = g.last_position_id
              WHERE $whereSql
           ORDER BY g.status = 'offline' DESC, g.last_seen_at DESC, g.device_code ASC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

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

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT g.*, t.reg_no AS truck_reg, t.make, t.model, t.current_odometer,
                    p.lat AS last_lat, p.lng AS last_lng, p.speed_kph AS last_speed,
                    p.ignition AS last_ignition, p.fuel_level_l AS last_fuel_level,
                    p.ts AS last_position_ts
               FROM gps_devices g
          LEFT JOIN trucks        t ON t.c0d = g.truck_id
          LEFT JOIN gps_positions p ON p.c0d = g.last_position_id
              WHERE g.c0d = :id AND g.deleted = 0
              LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function byCode(string $code): array
    {
        $rs = (new DB())->mySelect(
            "SELECT * FROM gps_devices WHERE device_code = :c AND deleted = 0 LIMIT 1",
            [':c' => trim($code)]
        );
        return $rs[0] ?? [];
    }

    /** Load device for the ingestion path — includes truck_id resolution. */
    public function byCodeForIngestion(string $code): array
    {
        $rs = (new DB())->mySelect(
            "SELECT g.c0d AS device_id, g.truck_id, g.tenant_id, g.status, g.deleted
               FROM gps_devices g
              WHERE g.device_code = :c LIMIT 1",
            [':c' => trim($code)]
        );
        return $rs[0] ?? [];
    }

    public function create(array $d, int $uid): array
    {
        $code = strtoupper($this->s($d['device_code'] ?? ''));
        if ($code === '') return ['ok' => false, 'msg' => 'Device code (IMEI / unique-id) is required.', 'id' => 0];
        if (!empty($this->byCode($code))) return ['ok' => false, 'msg' => "Device {$code} is already registered.", 'id' => 0];

        $r = (new DB())->insertUpdate(
            "INSERT INTO gps_devices
                (tenant_id, device_code, device_type, protocol, truck_id,
                 sim_msisdn, sim_iccid, installed_on, firmware_ver,
                 fuel_sensor, dashcam, status, notes, created_by, created_on)
             VALUES
                (:tenant, :code, :dt, :pr, :tid,
                 :sim, :iccid, :inst, :fw,
                 :fs, :dc, :st, :notes, :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':code'   => $code,
                ':dt'     => $this->s(($d['device_type'] ?? '') ?: 'teltonika_fmc920'),
                ':pr'     => $this->s(($d['protocol']    ?? '') ?: 'codec_8e'),
                ':tid'    => $this->nn((int)($d['truck_id'] ?? 0)),
                ':sim'    => $this->s($d['sim_msisdn'] ?? ''),
                ':iccid'  => $this->s($d['sim_iccid']  ?? ''),
                ':inst'   => !empty($d['installed_on']) ? $this->s($d['installed_on']) : null,
                ':fw'     => $this->s($d['firmware_ver'] ?? ''),
                ':fs'     => !empty($d['fuel_sensor']) ? 1 : 0,
                ':dc'     => !empty($d['dashcam'])     ? 1 : 0,
                ':st'     => $this->s(($d['status'] ?? '') ?: 'active'),
                ':notes'  => $this->s($d['notes'] ?? ''),
                ':uid'    => $uid,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Device registered.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function update(int $id, array $d, int $uid): array
    {
        $existing = $this->byId($id);
        if (!$existing) return ['ok' => false, 'msg' => 'Device not found.', 'id' => 0];
        $code = strtoupper($this->s($d['device_code'] ?? ''));
        if ($code === '') return ['ok' => false, 'msg' => 'Device code is required.', 'id' => 0];

        (new DB())->insertUpdate(
            "UPDATE gps_devices SET
                device_code = :code, device_type = :dt, protocol = :pr, truck_id = :tid,
                sim_msisdn = :sim, sim_iccid = :iccid, installed_on = :inst,
                firmware_ver = :fw, fuel_sensor = :fs, dashcam = :dc,
                status = :st, notes = :notes,
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id AND deleted = 0",
            [
                ':code'  => $code,
                ':dt'    => $this->s(($d['device_type'] ?? '') ?: 'teltonika_fmc920'),
                ':pr'    => $this->s(($d['protocol']    ?? '') ?: 'codec_8e'),
                ':tid'   => $this->nn((int)($d['truck_id'] ?? 0)),
                ':sim'   => $this->s($d['sim_msisdn'] ?? ''),
                ':iccid' => $this->s($d['sim_iccid']  ?? ''),
                ':inst'  => !empty($d['installed_on']) ? $this->s($d['installed_on']) : null,
                ':fw'    => $this->s($d['firmware_ver'] ?? ''),
                ':fs'    => !empty($d['fuel_sensor']) ? 1 : 0,
                ':dc'    => !empty($d['dashcam'])     ? 1 : 0,
                ':st'    => $this->s(($d['status'] ?? '') ?: 'active'),
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
                ':id'    => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Device updated.', 'id' => $id];
    }

    public function softDelete(int $id, int $uid): array
    {
        (new DB())->insertUpdate(
            "UPDATE gps_devices SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true];
    }

    /** For Trucks/view and Trips/view — the latest known position of a truck. */
    public function latestPositionForTruck(int $truckId): array
    {
        $rs = (new DB())->mySelect(
            "SELECT p.lat, p.lng, p.speed_kph, p.heading_deg, p.ts, p.ignition,
                    p.odometer_can_km, p.fuel_level_l, g.device_code, g.c0d AS device_id,
                    g.status AS device_status, g.last_seen_at
               FROM gps_devices g
          LEFT JOIN gps_positions p ON p.c0d = g.last_position_id
              WHERE g.truck_id = :tid AND g.deleted = 0
           ORDER BY g.status = 'active' DESC, p.ts DESC
              LIMIT 1",
            [':tid' => $truckId]
        );
        return $rs[0] ?? [];
    }

    public function recentPositionsForTruck(int $truckId, int $limit = 50): array
    {
        $limit = max(1, min(1000, $limit));
        return (new DB())->mySelect(
            "SELECT p.c0d, p.ts, p.lat, p.lng, p.speed_kph, p.ignition
               FROM gps_positions p
              WHERE p.truck_id = :tid
           ORDER BY p.ts DESC
              LIMIT $limit",
            [':tid' => $truckId]
        ) ?: [];
    }
}
