import { Router } from "express";

import { DEFAULT_VEHICLE_COLOR_KEY, getVehicleStatus } from "@tracker/shared";

import { env } from "../../config/env.js";
import { asyncHandler } from "../../lib/async-handler.js";
import { query, queryOne } from "../../lib/db.js";
import { HttpError } from "../../lib/http-error.js";
import { requireAuth, requireRole } from "../../middleware/auth.js";
import { validate } from "../../middleware/validate.js";
import { assertVehicleAccess } from "../permissions/access.js";
import {
  createVehicleSchema,
  deleteVehicleSchema,
  historyQuerySchema,
  previousPositionQuerySchema,
  tripsQuerySchema,
  updateVehicleInspectionSchema,
  updateVehicleSchema,
} from "./vehicles.schemas.js";

type VehicleRow = {
  id: string;
  name: string;
  plate_number: string | null;
  color_key: string | null;
  vehicle_type: "passenger" | "delivery";
  inspection_due_date: string | null;
  oil_change_due_date: string | null;
  oil_change_odometer: number | null;
  oil_change_interval_km: number | null;
  next_oil_change_odometer: number | null;
  estimated_current_odometer: number | null;
  tire_changed_at: string | null;
  next_tire_change_due_date: string | null;
  device_id: string | null;
  imei: string | null;
  phone_number: string | null;
  last_seen: string | null;
  last_device_time: string | null;
  device_battery: number | null;
  device_gsm_signal: number | null;
  device_ignition: boolean | null;
  device_charging: boolean | null;
  device_defense: boolean | null;
  device_gps_valid: boolean | null;
  device_satellite_count: number | null;
  device_position_type: string | null;
  lat: number | null;
  lon: number | null;
  speed: number | null;
  heading: number | null;
  device_time: string | null;
  server_time: string | null;
  ignition: boolean | null;
  battery: number | null;
  gsm_signal: number | null;
  satellite_count: number | null;
  gps_valid: boolean | null;
  charging: boolean | null;
  defense: boolean | null;
  position_type: string | null;
  first_history_at: string | null;
};

const VALID_TRIP_DISTANCE_KM_SQL = `
  case
    when t.distance_km is null or t.distance_km <= 0.1 then 0
    when t.duration_minutes is null or t.duration_minutes <= 0 then 0
    when (t.distance_km / t.duration_minutes * 60) > 200 then 0
    else t.distance_km
  end
`.trim();

const vehicleListSql = `
  select
    v.id,
    v.name,
    v.plate_number,
    v.color_key,
    v.vehicle_type,
    v.inspection_due_date,
    v.oil_change_due_date,
    v.oil_change_odometer,
    v.oil_change_interval_km,
    v.next_oil_change_odometer,
    case
      when v.oil_change_odometer is not null then v.oil_change_odometer + coalesce(ods.distance_since_oil_change_km, 0)
      else null
    end as estimated_current_odometer,
    v.tire_changed_at,
    v.next_tire_change_due_date,
    d.id as device_id,
    d.imei,
    d.phone_number,
    d.last_seen,
    d.last_device_time,
    d.battery as device_battery,
    d.gsm_signal as device_gsm_signal,
    d.ignition as device_ignition,
    d.charging as device_charging,
    d.defense as device_defense,
    d.gps_valid as device_gps_valid,
    d.satellite_count as device_satellite_count,
    d.position_type as device_position_type,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (
          lp.server_time is null
          or ST_DistanceSphere(
            ST_MakePoint(lp.lon, lp.lat),
            ST_MakePoint(parked_lp.lon, parked_lp.lat)
          ) <= 500
        )
        then parked_lp.lat
      else lp.lat
    end as lat,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (
          lp.server_time is null
          or ST_DistanceSphere(
            ST_MakePoint(lp.lon, lp.lat),
            ST_MakePoint(parked_lp.lon, parked_lp.lat)
          ) <= 500
        )
        then parked_lp.lon
      else lp.lon
    end as lon,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (
          lp.server_time is null
          or ST_DistanceSphere(
            ST_MakePoint(lp.lon, lp.lat),
            ST_MakePoint(parked_lp.lon, parked_lp.lat)
          ) <= 500
        )
        then parked_lp.speed
      else lp.speed
    end as speed,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (
          lp.server_time is null
          or ST_DistanceSphere(
            ST_MakePoint(lp.lon, lp.lat),
            ST_MakePoint(parked_lp.lon, parked_lp.lat)
          ) <= 500
        )
        then parked_lp.heading
      else lp.heading
    end as heading,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (lp.server_time is null or parked_lp.server_time >= lp.server_time - interval '12 hours')
        then parked_lp.device_time
      else lp.device_time
    end as device_time,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (lp.server_time is null or parked_lp.server_time >= lp.server_time - interval '12 hours')
        then parked_lp.server_time
      else lp.server_time
    end as server_time,
    case
      when coalesce(d.ignition, lp.ignition) is false
        and parked_lp.server_time is not null
        and (lp.server_time is null or parked_lp.server_time >= lp.server_time - interval '12 hours')
        then false
      else lp.ignition
    end as ignition,
    lp.battery,
    lp.gsm_signal,
    lp.satellite_count,
    lp.gps_valid,
    lp.charging,
    lp.defense,
    lp.position_type,
    hp.first_history_at
  from vehicles v
  left join devices d on d.vehicle_id = v.id
  left join last_positions lp on lp.vehicle_id = v.id
  left join lateral (
    select
      t.end_lat as lat,
      t.end_lon as lon,
      null::double precision as speed,
      null::double precision as heading,
      t.ended_at as device_time,
      t.ended_at as server_time
    from vehicle_trips t
    where t.vehicle_id = v.id
      and t.ended_at is not null
      and t.end_lat is not null
      and t.end_lon is not null
      and t.ended_at <= now() + interval '1 day'
    order by t.ended_at desc, t.id desc
    limit 1
  ) parked_lp on true
  left join lateral (
    select coalesce(sum(
      ${VALID_TRIP_DISTANCE_KM_SQL}
    ), 0)::double precision as distance_since_oil_change_km
    from vehicle_trips t
    where t.vehicle_id = v.id
      and v.oil_change_odometer is not null
      and t.started_at >= coalesce(v.oil_change_recorded_at, v.updated_at, v.created_at)
  ) ods on true
  left join lateral (
    select p.server_time as first_history_at
    from positions p
    where p.vehicle_id = v.id
    order by p.server_time asc, p.id asc
    limit 1
  ) hp on true
`;

export const vehiclesRouter = Router();

vehiclesRouter.use(requireAuth);

export const ensureVehicleServiceStorage = async () => {
  await query(`
    alter table vehicles add column if not exists oil_change_due_date date null;
    alter table vehicles add column if not exists oil_change_odometer numeric(12,1) null;
    alter table vehicles add column if not exists oil_change_interval_km numeric(12,1) null;
    alter table vehicles add column if not exists next_oil_change_odometer numeric(12,1) null;
    alter table vehicles add column if not exists oil_change_recorded_at timestamptz null;
    alter table vehicles add column if not exists tire_changed_at date null;
    alter table vehicles add column if not exists next_tire_change_due_date date null;
  `);
};

type PeriodDistanceStatsRow = {
  vehicle_id: string;
  vehicle_name: string;
  plate_number: string | null;
  color_key: string | null;
  vehicle_type: "passenger" | "delivery";
  total_km: number;
  period_start: string;
};

type BestDayStatsRow = {
  vehicle_id: string;
  vehicle_name: string;
  plate_number: string | null;
  color_key: string | null;
  vehicle_type: "passenger" | "delivery";
  total_km: number;
  distance_day: string;
};

type SelectedVehicleStatsRow = {
  vehicle_id: string;
  vehicle_name: string;
  plate_number: string | null;
  color_key: string | null;
  vehicle_type: "passenger" | "delivery";
  estimated_current_odometer: number | null;
  current_month_km: number;
  current_year_km: number;
  best_day_km: number;
  best_day: string | null;
  monthly_rank: number | null;
  yearly_rank: number | null;
};

type VehicleTripRow = {
  id: number | string;
  start_time: string;
  end_time: string;
  start_lat: number;
  start_lon: number;
  end_lat: number;
  end_lon: number;
  start_label: string | null;
  end_label: string | null;
  duration_minutes: number | null;
  distance_km: number | null;
};

const normalizeInspectionDate = (inspectionDueDate?: string | Date | null) => {
  if (!inspectionDueDate) {
    return null;
  }

  if (inspectionDueDate instanceof Date) {
    if (Number.isNaN(inspectionDueDate.getTime())) {
      return null;
    }

    const year = inspectionDueDate.getFullYear();
    const month = String(inspectionDueDate.getMonth() + 1).padStart(2, "0");
    const day = String(inspectionDueDate.getDate()).padStart(2, "0");

    return `${year}-${month}-${day}`;
  }

  const trimmed = inspectionDueDate.trim();
  if (!trimmed) {
    return null;
  }

  const dateOnlyMatch = trimmed.match(/^(\d{4}-\d{2}-\d{2})/);
  if (dateOnlyMatch) {
    return dateOnlyMatch[1] ?? null;
  }

  const parsed = new Date(trimmed);
  if (Number.isNaN(parsed.getTime())) {
    return null;
  }

  return parsed.toISOString().slice(0, 10);
};

const getInspectionStatus = (inspectionDueDate?: string | null) => {
  const normalizedInspectionDate = normalizeInspectionDate(inspectionDueDate);
  if (!normalizedInspectionDate) {
    return "missing" as const;
  }

  const today = new Date();
  const todayUtc = Date.UTC(today.getFullYear(), today.getMonth(), today.getDate());
  const due = new Date(`${normalizedInspectionDate}T00:00:00`);
  const dueUtc = Date.UTC(due.getFullYear(), due.getMonth(), due.getDate());

  if (Number.isNaN(dueUtc)) {
    return "missing" as const;
  }

  const dayDiff = Math.floor((dueUtc - todayUtc) / 86400000);
  return dayDiff <= 14 ? "due_soon" as const : "ok" as const;
};

const getTireChangeStatus = (tireChangedAt?: string | null, nextTireChangeDueDate?: string | null) => {
  if (!normalizeInspectionDate(tireChangedAt) || !normalizeInspectionDate(nextTireChangeDueDate)) {
    return "missing" as const;
  }

  return getInspectionStatus(nextTireChangeDueDate);
};

const getOilChangeStatus = (
  oilChangeDueDate?: string | null,
  oilChangeOdometer?: number | null,
  oilChangeIntervalKm?: number | null,
) => {
  if (
    !normalizeInspectionDate(oilChangeDueDate)
    || oilChangeOdometer == null
    || Number.isNaN(Number(oilChangeOdometer))
    || oilChangeIntervalKm == null
    || Number.isNaN(Number(oilChangeIntervalKm))
  ) {
    return "missing" as const;
  }

  return getInspectionStatus(oilChangeDueDate);
};

const buildPeriodDistanceStatsQuery = (
  scopeJoin: string,
  vehicleScopeWhere: string,
  period: "month" | "year",
) => `
  with period_totals as (
    select
      v.id as vehicle_id,
      v.name as vehicle_name,
      v.plate_number,
      v.color_key,
      v.vehicle_type,
      date_trunc('${period}', timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)))::date as period_start,
      coalesce(sum(
        ${VALID_TRIP_DISTANCE_KM_SQL}
      ), 0) as total_km
    from vehicles v
    ${scopeJoin}
    left join vehicle_trips t
      on t.vehicle_id = v.id
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) >= date_trunc('${period}', timezone('Europe/Warsaw', now()))
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) < date_trunc('${period}', timezone('Europe/Warsaw', now())) + interval '1 ${period}'
    ${vehicleScopeWhere}
    group by v.id, v.name, v.plate_number, v.color_key, v.vehicle_type, period_start
  )
  select
    vehicle_id,
    vehicle_name,
    plate_number,
    color_key,
    vehicle_type,
    total_km,
    coalesce(period_start::text, date_trunc('${period}', timezone('Europe/Warsaw', now()))::date::text) as period_start
  from period_totals
  order by total_km desc, vehicle_name asc
  limit 5
`.replace(/\n\s+\n/g, "\n");

const buildBestDayTripStatsQuery = (
  scopeJoin: string,
  vehicleScopeWhere: string,
) => `
  with day_totals as (
    select
      v.id as vehicle_id,
      v.name as vehicle_name,
      v.plate_number,
      v.color_key,
      v.vehicle_type,
      date_trunc('day', timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)))::date as distance_day,
      coalesce(sum(${VALID_TRIP_DISTANCE_KM_SQL}), 0) as total_km
    from vehicles v
    ${scopeJoin}
    left join vehicle_trips t on t.vehicle_id = v.id
    ${vehicleScopeWhere}
    group by v.id, v.name, v.plate_number, v.color_key, v.vehicle_type, distance_day
  )
  select
    vehicle_id,
    vehicle_name,
    plate_number,
    color_key,
    vehicle_type,
    total_km,
    distance_day::text
  from day_totals
  where distance_day is not null
    and total_km > 0.1
  order by total_km desc, distance_day desc, vehicle_name asc
  limit 5
`.replace(/\n\s+\n/g, "\n");

const buildSelectedVehicleStatsQuery = (
  scopeJoin: string,
  vehicleScopeWhere: string,
  selectedVehicleFilter: string,
  selectedVehicleParamIndex: number,
) => `
  with fleet_month as (
    select
      v.id as vehicle_id,
      coalesce(sum(${VALID_TRIP_DISTANCE_KM_SQL}), 0) as total_km
    from vehicles v
    ${scopeJoin}
    left join vehicle_trips t
      on t.vehicle_id = v.id
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) >= date_trunc('month', timezone('Europe/Warsaw', now()))
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) < date_trunc('month', timezone('Europe/Warsaw', now())) + interval '1 month'
    ${vehicleScopeWhere}
    group by v.id
  ),
  fleet_year as (
    select
      v.id as vehicle_id,
      coalesce(sum(${VALID_TRIP_DISTANCE_KM_SQL}), 0) as total_km
    from vehicles v
    ${scopeJoin}
    left join vehicle_trips t
      on t.vehicle_id = v.id
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) >= date_trunc('year', timezone('Europe/Warsaw', now()))
      and timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)) < date_trunc('year', timezone('Europe/Warsaw', now())) + interval '1 year'
    ${vehicleScopeWhere}
    group by v.id
  ),
  oil_distance as (
    select
      v.id as vehicle_id,
      coalesce(sum(${VALID_TRIP_DISTANCE_KM_SQL}), 0) as distance_since_oil_change_km
    from vehicles v
    left join vehicle_trips t
      on t.vehicle_id = v.id
      and v.oil_change_odometer is not null
      and t.started_at >= coalesce(v.oil_change_recorded_at, v.updated_at, v.created_at)
    group by v.id
  ),
  month_ranked as (
    select vehicle_id, rank() over (order by total_km desc, vehicle_id asc) as monthly_rank, total_km
    from fleet_month
  ),
  year_ranked as (
    select vehicle_id, rank() over (order by total_km desc, vehicle_id asc) as yearly_rank, total_km
    from fleet_year
  ),
  best_day as (
    select
      t.vehicle_id,
      date_trunc('day', timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)))::date as distance_day,
      sum(${VALID_TRIP_DISTANCE_KM_SQL}) as total_km,
      row_number() over (
        partition by t.vehicle_id
        order by sum(${VALID_TRIP_DISTANCE_KM_SQL}) desc,
        date_trunc('day', timezone('Europe/Warsaw', coalesce(t.ended_at, t.started_at)))::date desc
      ) as day_rank
    from vehicle_trips t
    where t.vehicle_id = $${selectedVehicleParamIndex}
    group by t.vehicle_id, distance_day
  )
  select
    v.id as vehicle_id,
    v.name as vehicle_name,
    v.plate_number,
    v.color_key,
    v.vehicle_type,
    case
      when v.oil_change_odometer is not null then v.oil_change_odometer + coalesce(od.distance_since_oil_change_km, 0)
      else null
    end as estimated_current_odometer,
    coalesce(m.total_km, 0) as current_month_km,
    coalesce(y.total_km, 0) as current_year_km,
    coalesce(b.total_km, 0) as best_day_km,
    b.distance_day::text as best_day,
    m.monthly_rank,
    y.yearly_rank
  from vehicles v
  ${scopeJoin}
  left join month_ranked m on m.vehicle_id = v.id
  left join year_ranked y on y.vehicle_id = v.id
  left join best_day b on b.vehicle_id = v.id and b.day_rank = 1
  left join oil_distance od on od.vehicle_id = v.id
  ${selectedVehicleFilter}
  limit 1
`.replace(/\n\s+\n/g, "\n");

vehiclesRouter.get(
  "/",
  asyncHandler(async (request, response) => {
    const rows = request.user?.role === "admin"
      ? await query<VehicleRow>(`${vehicleListSql} order by v.name asc`)
      : await query<VehicleRow>(
          `
            ${vehicleListSql}
            inner join user_vehicle_permissions uvp on uvp.vehicle_id = v.id
            where uvp.user_id = $1
            order by v.name asc
          `,
          [request.user?.id],
        );

    response.json({
      items: rows.map((row) => ({
        id: row.id,
        name: row.name,
        plateNumber: row.plate_number,
        colorKey: row.color_key ?? DEFAULT_VEHICLE_COLOR_KEY,
        vehicleKind: row.vehicle_type,
        deviceId: row.device_id,
        imei: row.imei,
        phoneNumber: row.phone_number,
        lastSeen: row.last_seen,
        firstHistoryAt: row.first_history_at,
        status: getVehicleStatus(
          row.last_seen,
          env.DEVICE_ONLINE_SECONDS,
          env.DEVICE_DELAYED_SECONDS,
        ),
        inspectionDueDate: normalizeInspectionDate(row.inspection_due_date),
        oilChangeDueDate: normalizeInspectionDate(row.oil_change_due_date),
        oilChangeOdometer: row.oil_change_odometer != null ? Number(row.oil_change_odometer) : null,
        oilChangeIntervalKm: row.oil_change_interval_km != null ? Number(row.oil_change_interval_km) : null,
        nextOilChangeOdometer: row.next_oil_change_odometer != null ? Number(row.next_oil_change_odometer) : null,
        estimatedCurrentOdometer: row.estimated_current_odometer != null ? Number(row.estimated_current_odometer) : null,
        tireChangedAt: normalizeInspectionDate(row.tire_changed_at),
        nextTireChangeDueDate: normalizeInspectionDate(row.next_tire_change_due_date),
        inspectionStatus: getInspectionStatus(row.inspection_due_date),
        oilChangeStatus: getOilChangeStatus(
          row.oil_change_due_date,
          row.oil_change_odometer,
          row.oil_change_interval_km,
        ),
        tireChangeStatus: getTireChangeStatus(row.tire_changed_at, row.next_tire_change_due_date),
        lastPosition: row.lat != null && row.lon != null
          ? {
              lat: row.lat,
              lon: row.lon,
              speed: row.speed,
              heading: row.heading,
              deviceTime: row.device_time,
              serverTime: row.server_time ?? row.last_seen ?? new Date(0).toISOString(),
              ignition: row.ignition,
              battery: row.battery,
              gsmSignal: row.gsm_signal,
              satelliteCount: row.satellite_count,
              gpsValid: row.gps_valid,
              charging: row.charging,
              defense: row.defense,
              positionType: row.position_type,
            }
          : null,
        deviceState: row.device_id
          ? {
              phoneNumber: row.phone_number,
              protocolName: null,
              deviceTime: row.last_device_time,
              battery: row.device_battery,
              gsmSignal: row.device_gsm_signal,
              ignition: row.device_ignition,
              charging: row.device_charging,
              defense: row.device_defense,
              gpsValid: row.device_gps_valid,
              satelliteCount: row.device_satellite_count,
              positionType: row.device_position_type,
            }
          : null,
      })),
    });
  }),
);

vehiclesRouter.get(
  "/stats/overview",
  asyncHandler(async (request, response) => {
    if (request.user?.role !== "admin" && !request.user?.canViewStats) {
      throw new HttpError(403, "Insufficient permissions");
    }

    const isAdmin = request.user?.role === "admin";
    const scopeJoin = isAdmin
      ? ""
      : "inner join user_vehicle_permissions uvp on uvp.vehicle_id = v.id";
    const vehicleScopeWhere = isAdmin
      ? ""
      : "where uvp.user_id = $1";
    const params = isAdmin ? [] : [request.user?.id];
    const selectedVehicleId = typeof request.query?.vehicleId === "string" && request.query.vehicleId
      ? request.query.vehicleId
      : null;

    if (selectedVehicleId) {
      await assertVehicleAccess(request.user, selectedVehicleId);
    }

    const [distanceRows, monthlyDistanceRows, yearlyDistanceRows, selectedVehicleStats] = await Promise.all([
      query<BestDayStatsRow>(buildBestDayTripStatsQuery(scopeJoin, vehicleScopeWhere), params),
      query<PeriodDistanceStatsRow>(buildPeriodDistanceStatsQuery(scopeJoin, vehicleScopeWhere, "month"), params),
      query<PeriodDistanceStatsRow>(buildPeriodDistanceStatsQuery(scopeJoin, vehicleScopeWhere, "year"), params),
      selectedVehicleId
        ? queryOne<SelectedVehicleStatsRow>(
            buildSelectedVehicleStatsQuery(
              scopeJoin,
              vehicleScopeWhere,
              `${vehicleScopeWhere ? `${vehicleScopeWhere} and` : "where"} v.id = $${params.length + 1}`,
              params.length + 1,
            ),
            [...params, selectedVehicleId],
          )
        : Promise.resolve(null),
    ]);

    const mapBestDayRecord = (row: BestDayStatsRow | null | undefined) => row
      ? {
          vehicleId: row.vehicle_id,
          vehicleName: row.vehicle_name,
          plateNumber: row.plate_number,
          colorKey: row.color_key ?? DEFAULT_VEHICLE_COLOR_KEY,
          vehicleKind: row.vehicle_type,
          value: Number(row.total_km.toFixed(1)),
          unit: "km" as const,
          recordedAt: row.distance_day,
          recordedDay: row.distance_day,
        }
      : null;

    const mapPeriodDistanceRecord = (row: PeriodDistanceStatsRow | null | undefined) => row
      ? {
          vehicleId: row.vehicle_id,
          vehicleName: row.vehicle_name,
          plateNumber: row.plate_number,
          colorKey: row.color_key ?? DEFAULT_VEHICLE_COLOR_KEY,
          vehicleKind: row.vehicle_type,
          value: Number(row.total_km.toFixed(1)),
          unit: "km" as const,
          recordedAt: row.period_start,
          recordedDay: row.period_start,
        }
      : null;

    response.json({
      item: {
        topSpeed: null,
        topDistance: mapBestDayRecord(distanceRows[0]),
        topSpeeds: [],
        topDistances: distanceRows.map((row) => mapBestDayRecord(row)).filter(Boolean),
        topMonthlyDistance: mapPeriodDistanceRecord(monthlyDistanceRows[0]),
        topYearlyDistance: mapPeriodDistanceRecord(yearlyDistanceRows[0]),
        topMonthlyDistances: monthlyDistanceRows.map((row) => mapPeriodDistanceRecord(row)).filter(Boolean),
        topYearlyDistances: yearlyDistanceRows.map((row) => mapPeriodDistanceRecord(row)).filter(Boolean),
        selectedVehicleStats: selectedVehicleStats
          ? {
              vehicleId: selectedVehicleStats.vehicle_id,
              vehicleName: selectedVehicleStats.vehicle_name,
              plateNumber: selectedVehicleStats.plate_number,
              colorKey: selectedVehicleStats.color_key ?? DEFAULT_VEHICLE_COLOR_KEY,
              vehicleKind: selectedVehicleStats.vehicle_type,
              estimatedCurrentOdometer: selectedVehicleStats.estimated_current_odometer != null
                ? Number(selectedVehicleStats.estimated_current_odometer)
                : null,
              currentMonthKm: Number(selectedVehicleStats.current_month_km.toFixed(1)),
              currentYearKm: Number(selectedVehicleStats.current_year_km.toFixed(1)),
              bestDayKm: Number(selectedVehicleStats.best_day_km.toFixed(1)),
              bestDay: selectedVehicleStats.best_day,
              monthlyRank: selectedVehicleStats.monthly_rank != null ? Number(selectedVehicleStats.monthly_rank) : null,
              yearlyRank: selectedVehicleStats.yearly_rank != null ? Number(selectedVehicleStats.yearly_rank) : null,
            }
          : null,
      },
    });
  }),
);

vehiclesRouter.post(
  "/",
  requireRole("admin"),
  validate(createVehicleSchema),
  asyncHandler(async (request, response) => {
    const created = await queryOne<{
      id: string;
      name: string;
      plate_number: string | null;
      color_key: string;
      vehicle_type: "passenger" | "delivery";
      inspection_due_date: string | null;
      oil_change_due_date: string | null;
      oil_change_odometer: number | null;
      oil_change_interval_km: number | null;
      next_oil_change_odometer: number | null;
      estimated_current_odometer: number | null;
      tire_changed_at: string | null;
      next_tire_change_due_date: string | null;
    }>(
      `
        insert into vehicles (
          name,
          plate_number,
          color_key,
          vehicle_type,
          inspection_due_date,
          oil_change_due_date,
          oil_change_odometer,
          oil_change_interval_km,
          next_oil_change_odometer,
          oil_change_recorded_at,
          tire_changed_at,
          next_tire_change_due_date
        )
        values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
        returning id, name, plate_number, color_key, vehicle_type, inspection_due_date, oil_change_due_date, oil_change_odometer, oil_change_interval_km, next_oil_change_odometer, oil_change_odometer as estimated_current_odometer, tire_changed_at, next_tire_change_due_date
      `,
      [
        request.body.name,
        request.body.plateNumber ?? null,
        request.body.colorKey ?? DEFAULT_VEHICLE_COLOR_KEY,
        request.body.vehicleKind ?? "passenger",
        request.body.inspectionDueDate ?? null,
        request.body.oilChangeDueDate ?? null,
        request.body.oilChangeOdometer ?? null,
        request.body.oilChangeIntervalKm ?? null,
        request.body.oilChangeOdometer != null && request.body.oilChangeIntervalKm != null
          ? request.body.oilChangeOdometer + request.body.oilChangeIntervalKm
          : null,
        request.body.oilChangeOdometer != null ? new Date().toISOString() : null,
        request.body.tireChangedAt ?? null,
        request.body.nextTireChangeDueDate ?? null,
      ],
    );

    if (!created) {
      throw new HttpError(500, "Failed to create vehicle");
    }

    if (request.body.deviceId) {
      await query(
        `
          update devices
          set vehicle_id = $2, updated_at = now()
          where id = $1
        `,
        [request.body.deviceId, created.id],
      );
    }

    for (const userId of request.body.userIds) {
      await query(
        `
          insert into user_vehicle_permissions (user_id, vehicle_id)
          values ($1, $2)
          on conflict do nothing
        `,
        [userId, created.id],
      );
    }

    response.status(201).json({
      item: {
        id: created.id,
        name: created.name,
        plateNumber: created.plate_number,
        colorKey: created.color_key,
        vehicleKind: created.vehicle_type,
        inspectionDueDate: normalizeInspectionDate(created.inspection_due_date),
        inspectionStatus: getInspectionStatus(created.inspection_due_date),
        oilChangeDueDate: normalizeInspectionDate(created.oil_change_due_date),
        oilChangeOdometer: created.oil_change_odometer != null ? Number(created.oil_change_odometer) : null,
        oilChangeIntervalKm: created.oil_change_interval_km != null ? Number(created.oil_change_interval_km) : null,
        nextOilChangeOdometer: created.next_oil_change_odometer != null ? Number(created.next_oil_change_odometer) : null,
        estimatedCurrentOdometer: created.estimated_current_odometer != null ? Number(created.estimated_current_odometer) : null,
        oilChangeStatus: getOilChangeStatus(
          created.oil_change_due_date,
          created.oil_change_odometer,
          created.oil_change_interval_km,
        ),
        tireChangedAt: normalizeInspectionDate(created.tire_changed_at),
        nextTireChangeDueDate: normalizeInspectionDate(created.next_tire_change_due_date),
        tireChangeStatus: getTireChangeStatus(created.tire_changed_at, created.next_tire_change_due_date),
      },
    });
  }),
);

vehiclesRouter.patch(
  "/:id",
  requireRole("admin"),
  validate(updateVehicleSchema),
  asyncHandler(async (request, response) => {
    const updated = await queryOne<{
      id: string;
      name: string;
      plate_number: string | null;
      color_key: string;
      vehicle_type: "passenger" | "delivery";
      inspection_due_date: string | null;
      oil_change_due_date: string | null;
      oil_change_odometer: number | null;
      oil_change_interval_km: number | null;
      next_oil_change_odometer: number | null;
      estimated_current_odometer: number | null;
      tire_changed_at: string | null;
      next_tire_change_due_date: string | null;
    }>(
      `
        update vehicles
        set
          name = $2,
          plate_number = $3,
          color_key = $4,
          vehicle_type = $5,
          inspection_due_date = $6,
          oil_change_due_date = $7,
          oil_change_odometer = $8,
          oil_change_interval_km = $9,
          next_oil_change_odometer = $10,
          oil_change_recorded_at = case
            when $8::numeric is not null and oil_change_odometer is distinct from $8::numeric then now()
            when $8::numeric is not null and oil_change_odometer is null then now()
            when $8 is null then null
            else oil_change_recorded_at
          end,
          tire_changed_at = $11,
          next_tire_change_due_date = $12,
          updated_at = now()
        where id = $1
        returning id, name, plate_number, color_key, vehicle_type, inspection_due_date, oil_change_due_date, oil_change_odometer, oil_change_interval_km, next_oil_change_odometer, oil_change_odometer as estimated_current_odometer, tire_changed_at, next_tire_change_due_date
      `,
      [
        request.params.id,
        request.body.name,
        request.body.plateNumber ?? null,
        request.body.colorKey,
        request.body.vehicleKind,
        request.body.inspectionDueDate ?? null,
        request.body.oilChangeDueDate ?? null,
        request.body.oilChangeOdometer ?? null,
        request.body.oilChangeIntervalKm ?? null,
        request.body.oilChangeOdometer != null && request.body.oilChangeIntervalKm != null
          ? request.body.oilChangeOdometer + request.body.oilChangeIntervalKm
          : null,
        request.body.tireChangedAt ?? null,
        request.body.nextTireChangeDueDate ?? null,
      ],
    );

    if (!updated) {
      throw new HttpError(404, "Vehicle not found");
    }

    response.json({
      item: {
        id: updated.id,
        name: updated.name,
        plateNumber: updated.plate_number,
        colorKey: updated.color_key,
        vehicleKind: updated.vehicle_type,
        inspectionDueDate: normalizeInspectionDate(updated.inspection_due_date),
        inspectionStatus: getInspectionStatus(updated.inspection_due_date),
        oilChangeDueDate: normalizeInspectionDate(updated.oil_change_due_date),
        oilChangeOdometer: updated.oil_change_odometer != null ? Number(updated.oil_change_odometer) : null,
        oilChangeIntervalKm: updated.oil_change_interval_km != null ? Number(updated.oil_change_interval_km) : null,
        nextOilChangeOdometer: updated.next_oil_change_odometer != null ? Number(updated.next_oil_change_odometer) : null,
        estimatedCurrentOdometer: updated.estimated_current_odometer != null ? Number(updated.estimated_current_odometer) : null,
        oilChangeStatus: getOilChangeStatus(
          updated.oil_change_due_date,
          updated.oil_change_odometer,
          updated.oil_change_interval_km,
        ),
        tireChangedAt: normalizeInspectionDate(updated.tire_changed_at),
        nextTireChangeDueDate: normalizeInspectionDate(updated.next_tire_change_due_date),
        tireChangeStatus: getTireChangeStatus(updated.tire_changed_at, updated.next_tire_change_due_date),
      },
    });
  }),
);

vehiclesRouter.patch(
  "/:id/inspection",
  validate(updateVehicleInspectionSchema),
  asyncHandler(async (request, response) => {
    if (!request.user) {
      throw new HttpError(401, "Missing authenticated user");
    }

    const vehicleId = String(request.params.id);

    if (request.user.role !== "admin") {
      if (!request.user.canEditVehicleInspection) {
        throw new HttpError(403, "Insufficient permissions");
      }
      await assertVehicleAccess(request.user, vehicleId);
    }

    const updated = await queryOne<{
      id: string;
      name: string;
      plate_number: string | null;
      color_key: string;
      vehicle_type: "passenger" | "delivery";
      inspection_due_date: string | null;
      oil_change_due_date: string | null;
      oil_change_odometer: number | null;
      oil_change_interval_km: number | null;
      next_oil_change_odometer: number | null;
      estimated_current_odometer: number | null;
      tire_changed_at: string | null;
      next_tire_change_due_date: string | null;
    }>(
      `
        update vehicles
        set
          inspection_due_date = $2,
          oil_change_due_date = $3,
          oil_change_odometer = $4,
          oil_change_interval_km = $5,
          next_oil_change_odometer = $6,
          oil_change_recorded_at = case
            when $4::numeric is not null and oil_change_odometer is distinct from $4::numeric then now()
            when $4::numeric is not null and oil_change_odometer is null then now()
            when $4 is null then null
            else oil_change_recorded_at
          end,
          tire_changed_at = $7,
          next_tire_change_due_date = $8,
          updated_at = now()
        where id = $1
        returning id, name, plate_number, color_key, vehicle_type, inspection_due_date, oil_change_due_date, oil_change_odometer, oil_change_interval_km, next_oil_change_odometer, oil_change_odometer as estimated_current_odometer, tire_changed_at, next_tire_change_due_date
      `,
      [
        vehicleId,
        request.body.inspectionDueDate ?? null,
        request.body.oilChangeDueDate ?? null,
        request.body.oilChangeOdometer ?? null,
        request.body.oilChangeIntervalKm ?? null,
        request.body.oilChangeOdometer != null && request.body.oilChangeIntervalKm != null
          ? request.body.oilChangeOdometer + request.body.oilChangeIntervalKm
          : null,
        request.body.tireChangedAt ?? null,
        request.body.nextTireChangeDueDate ?? null,
      ],
    );

    if (!updated) {
      throw new HttpError(404, "Vehicle not found");
    }

    response.json({
      item: {
        id: updated.id,
        name: updated.name,
        plateNumber: updated.plate_number,
        colorKey: updated.color_key,
        vehicleKind: updated.vehicle_type,
        inspectionDueDate: normalizeInspectionDate(updated.inspection_due_date),
        inspectionStatus: getInspectionStatus(updated.inspection_due_date),
        oilChangeDueDate: normalizeInspectionDate(updated.oil_change_due_date),
        oilChangeOdometer: updated.oil_change_odometer != null ? Number(updated.oil_change_odometer) : null,
        oilChangeIntervalKm: updated.oil_change_interval_km != null ? Number(updated.oil_change_interval_km) : null,
        nextOilChangeOdometer: updated.next_oil_change_odometer != null ? Number(updated.next_oil_change_odometer) : null,
        estimatedCurrentOdometer: updated.estimated_current_odometer != null ? Number(updated.estimated_current_odometer) : null,
        oilChangeStatus: getOilChangeStatus(
          updated.oil_change_due_date,
          updated.oil_change_odometer,
          updated.oil_change_interval_km,
        ),
        tireChangedAt: normalizeInspectionDate(updated.tire_changed_at),
        nextTireChangeDueDate: normalizeInspectionDate(updated.next_tire_change_due_date),
        tireChangeStatus: getTireChangeStatus(updated.tire_changed_at, updated.next_tire_change_due_date),
      },
    });
  }),
);

vehiclesRouter.delete(
  "/:id",
  requireRole("admin"),
  validate(deleteVehicleSchema),
  asyncHandler(async (request, response) => {
    const removed = await queryOne<{ id: string }>(
      `
        delete from vehicles
        where id = $1
        returning id
      `,
      [request.params.id],
    );

    if (!removed) {
      throw new HttpError(404, "Vehicle not found");
    }

    response.status(204).send();
  }),
);

vehiclesRouter.get(
  "/:id/previous-position",
  validate(previousPositionQuerySchema),
  asyncHandler(async (request, response) => {
    if (!request.user) {
      throw new HttpError(401, "Missing authenticated user");
    }

    const vehicleId = String(request.params.id);
    await assertVehicleAccess(request.user, vehicleId);

    const queryParams = request.query as unknown as {
      before: string;
    };

    const rows = await query<{
      id: number;
      lat: number;
      lon: number;
      speed: number | null;
      heading: number | null;
      device_time: string | null;
      server_time: string;
      ignition: boolean | null;
      battery: number | null;
      gsm_signal: number | null;
      satellite_count: number | null;
      gps_valid: boolean | null;
      charging: boolean | null;
      defense: boolean | null;
      position_type: string | null;
    }>(
      `
        select
          id,
          lat,
          lon,
          speed,
          heading,
          device_time,
          server_time,
          ignition,
          battery,
          gsm_signal,
          satellite_count,
          gps_valid,
          charging,
          defense,
          position_type
        from positions
        where vehicle_id = $1
          and server_time < $2
        order by server_time desc
        limit 1
      `,
      [vehicleId, queryParams.before],
    );

    response.json({
      item: rows[0] ?? null,
    });
  }),
);

vehiclesRouter.get(
  "/:id/history",
  validate(historyQuerySchema),
  asyncHandler(async (request, response) => {
    if (!request.user) {
      throw new HttpError(401, "Missing authenticated user");
    }

    const vehicleId = String(request.params.id);
    await assertVehicleAccess(request.user, vehicleId);

    const queryParams = request.query as unknown as {
      from?: string;
      to?: string;
      limit: number;
    };
    const from = queryParams.from ? new Date(queryParams.from) : new Date(Date.now() - 1000 * 60 * 60 * 24);
    const to = queryParams.to ? new Date(queryParams.to) : new Date();

    const rows = await query<{
      id: number;
      lat: number;
      lon: number;
      speed: number | null;
      heading: number | null;
      device_time: string | null;
      server_time: string;
      ignition: boolean | null;
      battery: number | null;
      gsm_signal: number | null;
      satellite_count: number | null;
      gps_valid: boolean | null;
      charging: boolean | null;
      defense: boolean | null;
      position_type: string | null;
    }>(
      `
        select
          id,
          lat,
          lon,
          speed,
          heading,
          device_time,
          server_time,
          ignition,
          battery,
          gsm_signal,
          satellite_count,
          gps_valid,
          charging,
          defense,
          position_type
        from positions
        where vehicle_id = $1
          and server_time between $2 and $3
        order by server_time desc
        limit $4
      `,
      [vehicleId, from.toISOString(), to.toISOString(), queryParams.limit],
    );

    response.json({
      items: rows,
    });
  }),
);

vehiclesRouter.get(
  "/:id/trips",
  validate(tripsQuerySchema),
  asyncHandler(async (request, response) => {
    if (!request.user) {
      throw new HttpError(401, "Missing authenticated user");
    }

    const vehicleId = String(request.params.id);
    await assertVehicleAccess(request.user, vehicleId);

    const queryParams = request.query as unknown as {
      days: number;
      limit: number;
      before?: string;
    };
    const before = queryParams.before ? new Date(queryParams.before) : new Date();
    const windowStart = new Date(before.getTime() - queryParams.days * 24 * 60 * 60 * 1000);

    const rows = await query<VehicleTripRow>(
      `
        select
          id,
          started_at::text as start_time,
          ended_at::text as end_time,
          start_lat,
          start_lon,
          end_lat,
          end_lon,
          start_label,
          end_label,
          duration_minutes,
          distance_km
        from vehicle_trips
        where vehicle_id = $1
          and ended_at is not null
          and coalesce(distance_km, 0) > 0.1
          and started_at >= $2
          and started_at < $3
        order by started_at desc
        limit $4
      `,
      [vehicleId, windowStart.toISOString(), before.toISOString(), queryParams.limit],
    );

    const hasMore = Boolean(await queryOne<{ id: number }>(
      `
        select id
        from vehicle_trips
        where vehicle_id = $1
          and ended_at is not null
          and coalesce(distance_km, 0) > 0.1
          and started_at < $2
        limit 1
      `,
      [vehicleId, windowStart.toISOString()],
    ));

    const items = rows.map((row) => ({
      tripId: Number(row.id),
      startTime: new Date(row.start_time).toISOString(),
      endTime: new Date(row.end_time).toISOString(),
      startLat: row.start_lat,
      startLon: row.start_lon,
      endLat: row.end_lat,
      endLon: row.end_lon,
      startLabel: row.start_label,
      endLabel: row.end_label,
      durationMinutes: row.duration_minutes,
      distanceKm: row.distance_km,
    }));

    response.json({
      items,
      hasMore,
      nextBefore: items.length > 0 ? items[items.length - 1]?.startTime ?? null : null,
    });
  }),
);