<?php
class Routes extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function d($v): ?float {
        $v = str_replace(',', '', (string)$v);
        return $v === '' ? null : (float)$v;
    }

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

        if (!empty($filters['q'])) {
            $where[] = '(r.name LIKE :q OR r.code LIKE :q OR r.origin_city LIKE :q OR r.destination_city LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['route_type'])) { $where[] = 'r.route_type = :rt'; $params[':rt'] = $this->s($filters['route_type']); }
        if (isset($filters['active']) && $filters['active'] !== '') { $where[] = 'r.active = :ac'; $params[':ac'] = (int)$filters['active']; }

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

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM routes r 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 r.c0d, r.code, r.name, r.route_type, r.origin_city, r.destination_city,
                    r.distance_km, r.avg_duration_hrs, r.active
               FROM routes r
              WHERE $whereSql
           ORDER BY r.name ASC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

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

    /** Dropdown-friendly list of active routes. */
    public function allActive(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, code, name, route_type, origin_city, destination_city, distance_km
               FROM routes
              WHERE deleted = 0 AND active = 1 AND tenant_id = :t
           ORDER BY name ASC",
            [':t' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM routes WHERE c0d = :id AND deleted = 0 LIMIT 1", [':id' => $id]);
        return $rs[0] ?? [];
    }

    public function create(array $d, int $uid): array
    {
        $name = $this->s($d['name'] ?? '');
        if ($name === '') return ['ok' => false, 'msg' => 'Route name is required.', 'id' => 0];

        $db = new DB();
        $r  = $db->insertUpdate(
            "INSERT INTO routes
                (tenant_id, code, name, route_type, origin_city, destination_city,
                 distance_km, avg_duration_hrs, stops_summary, notes, active,
                 created_by, created_on)
             VALUES
                (:tenant, :code, :name, :rt, :o, :d,
                 :dist, :dur, :stops, :notes, :ac,
                 :uid, NOW())",
            [
                ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':code'   => strtoupper($this->s($d['code'] ?? '')),
                ':name'   => $name,
                ':rt'     => $this->s(($d['route_type'] ?? '') ?: 'town'),
                ':o'      => $this->s($d['origin_city']      ?? ''),
                ':d'      => $this->s($d['destination_city'] ?? ''),
                ':dist'   => $this->d($d['distance_km']      ?? ''),
                ':dur'    => $this->d($d['avg_duration_hrs'] ?? ''),
                ':stops'  => $this->s($d['stops_summary'] ?? ''),
                ':notes'  => $this->s($d['notes'] ?? ''),
                ':ac'     => isset($d['active']) ? 1 : 1,   // default active on create
                ':uid'    => $uid,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Route added.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

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

        $db = new DB();
        $db->insertUpdate(
            "UPDATE routes SET
                code = :code, name = :name, route_type = :rt,
                origin_city = :o, destination_city = :d,
                distance_km = :dist, avg_duration_hrs = :dur,
                stops_summary = :stops, notes = :notes, active = :ac,
                edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id AND deleted = 0",
            [
                ':code' => strtoupper($this->s($d['code'] ?? '')),
                ':name' => $name,
                ':rt'   => $this->s(($d['route_type'] ?? '') ?: 'town'),
                ':o'    => $this->s($d['origin_city']      ?? ''),
                ':d'    => $this->s($d['destination_city'] ?? ''),
                ':dist' => $this->d($d['distance_km']      ?? ''),
                ':dur'  => $this->d($d['avg_duration_hrs'] ?? ''),
                ':stops'=> $this->s($d['stops_summary'] ?? ''),
                ':notes'=> $this->s($d['notes'] ?? ''),
                ':ac'   => !empty($d['active']) ? 1 : 0,
                ':uid'  => $uid,
                ':id'   => $id,
            ]
        );
        return ['ok' => true, 'msg' => 'Route updated.', 'id' => $id];
    }

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