import { query, queryOne } from "../../lib/db.js";
import { reverseGeocodeLabel } from "../geocoding/reverse-geocoding.js";

type OpenTripRow = {
  id: number;
  started_at: string;
};

type TripAddressFixRow = {
  id: number;
  start_lat: number;
  start_lon: number;
  end_lat: number | null;
  end_lon: number | null;
  start_label: string | null;
  end_label: string | null;
};

type HistoricalTripRow = {
  vehicle_id: string;
  started_at: string;
  ended_at: string;
  start_lat: number;
  start_lon: number;
  end_lat: number;
  end_lon: number;
};

type DistanceRow = {
  total_meters: number | null;
};

const FALLBACK_ADDRESS_LABEL = "Brak adresu";

const computeTripDistanceKm = async (vehicleId: string, from: string, to: string) => {
  const distance = await queryOne<DistanceRow>(
    `
      with scoped_positions as (
        select
          p.id,
          p.server_time,
          p.geom,
          p.ignition,
          lag(p.geom) over (order by p.server_time asc, p.id asc) as prev_geom,
          lag(p.server_time) over (order by p.server_time asc, p.id asc) as prev_server_time,
          lag(p.ignition) over (order by p.server_time asc, p.id asc) as prev_ignition
        from positions p
        where p.vehicle_id = $1
          and p.server_time between $2::timestamptz and $3::timestamptz
      )
      select
        coalesce(sum(
          case
            when prev_geom is null or prev_server_time is null then 0
            when extract(epoch from (server_time - prev_server_time)) <= 0 then 0
            when (
              st_distance(geom, prev_geom)
              / extract(epoch from (server_time - prev_server_time))
              * 3.6
            ) > 200 then 0
            when extract(epoch from (server_time - prev_server_time)) > 1800 then
              case
                when prev_ignition is true
                  and ignition is true
                then st_distance(geom, prev_geom)
                else 0
              end
            else st_distance(geom, prev_geom)
          end
        ), 0) as total_meters
      from scoped_positions
    `,
    [vehicleId, from, to],
  );

  const totalMeters = distance?.total_meters ?? 0;
  return Math.max(0, Math.round((totalMeters / 1000) * 10) / 10);
};

export const ensureVehicleTripsStorage = async () => {
  await query(`
    create table if not exists vehicle_trips (
      id bigserial primary key,
      vehicle_id uuid not null,
      device_id uuid null,
      started_at timestamptz not null,
      ended_at timestamptz null,
      start_lat double precision not null,
      start_lon double precision not null,
      end_lat double precision null,
      end_lon double precision null,
      start_label text null,
      end_label text null,
      duration_minutes integer null,
      distance_km double precision null,
      created_at timestamptz not null default now(),
      updated_at timestamptz not null default now()
    )
  `);

  await query(`
    alter table vehicle_trips
    add column if not exists distance_km double precision null
  `);

  await query(`
    create unique index if not exists vehicle_trips_vehicle_started_at_key
    on vehicle_trips (vehicle_id, started_at)
  `);

  await query(`
    create index if not exists vehicle_trips_vehicle_started_at_idx
    on vehicle_trips (vehicle_id, started_at desc)
  `);

  await query(`
    create index if not exists vehicle_trips_vehicle_ended_at_idx
    on vehicle_trips (vehicle_id, ended_at desc, id desc)
    where ended_at is not null
  `);

  await query(`
    create index if not exists vehicle_trips_open_idx
    on vehicle_trips (vehicle_id, started_at desc)
    where ended_at is null
  `);
};

export const syncVehicleTripFromTelemetry = async (input: {
  vehicleId: string | null;
  deviceId: string;
  ignition: boolean | null | undefined;
  lat: number;
  lon: number;
  eventTime: string;
}) => {
  if (!input.vehicleId || !Number.isFinite(input.lat) || !Number.isFinite(input.lon)) {
    return;
  }

  const openTrip = await queryOne<OpenTripRow>(
    `
      select id, started_at
      from vehicle_trips
      where vehicle_id = $1
        and ended_at is null
      order by started_at desc
      limit 1
    `,
    [input.vehicleId],
  );

  if (input.ignition === true) {
    if (openTrip) {
      return;
    }

    const startLabel = await reverseGeocodeLabel(input.lat, input.lon).catch(() => "Brak adresu");

    await query(
      `
        insert into vehicle_trips (
          vehicle_id,
          device_id,
          started_at,
          start_lat,
          start_lon,
          start_label,
          created_at,
          updated_at
        )
        values ($1, $2, $3, $4, $5, $6, now(), now())
        on conflict (vehicle_id, started_at) do nothing
      `,
      [input.vehicleId, input.deviceId, input.eventTime, input.lat, input.lon, startLabel],
    );

    return;
  }

  if (!openTrip) {
    return;
  }

  const endLabel = await reverseGeocodeLabel(input.lat, input.lon).catch(() => FALLBACK_ADDRESS_LABEL);
  const distanceKm = await computeTripDistanceKm(input.vehicleId, openTrip.started_at, input.eventTime).catch(() => 0);

  await query(
    `
      update vehicle_trips
      set
        ended_at = $2,
        end_lat = $3,
        end_lon = $4,
        end_label = $5,
        duration_minutes = greatest(1, ceil(extract(epoch from ($2::timestamptz - started_at)) / 60.0))::int,
        distance_km = $6,
        updated_at = now()
      where id = $1
        and ended_at is null
    `,
    [openTrip.id, input.eventTime, input.lat, input.lon, endLabel, distanceKm],
  );
};

export const backfillVehicleTrips = async (days = 14) => {
  const rows = await query<HistoricalTripRow>(
    `
      with raw as (
        select
          p.vehicle_id,
          p.id,
          p.lat,
          p.lon,
          p.ignition,
          coalesce(p.device_time, p.server_time) as event_time,
          lag(p.ignition) over (
            partition by p.vehicle_id
            order by coalesce(p.device_time, p.server_time) asc, p.id asc
          ) as prev_ignition
        from positions p
        where p.vehicle_id is not null
          and coalesce(p.device_time, p.server_time) >= now() - make_interval(days => $1::int)
      ),
      ordered as (
        select
          raw.*,
          sum(
            case
              when raw.ignition is true
                and coalesce(raw.prev_ignition, false) is not true
              then 1
              else 0
            end
          ) over (
            partition by raw.vehicle_id
            order by raw.event_time asc, raw.id asc
            rows between unbounded preceding and current row
          ) as trip_id
        from raw
      ),
      trip_points as (
        select
          vehicle_id,
          trip_id,
          id,
          event_time,
          lat,
          lon
        from ordered
        where ignition is true
          and trip_id is not null
      )
      select
        vehicle_id,
        min(event_time)::text as started_at,
        max(event_time)::text as ended_at,
        (array_agg(lat order by event_time asc, id asc))[1] as start_lat,
        (array_agg(lon order by event_time asc, id asc))[1] as start_lon,
        (array_agg(lat order by event_time desc, id desc))[1] as end_lat,
        (array_agg(lon order by event_time desc, id desc))[1] as end_lon
      from trip_points
      group by vehicle_id, trip_id
      order by started_at asc
    `,
    [days],
  );

  for (const row of rows) {
    const startLabel = await reverseGeocodeLabel(row.start_lat, row.start_lon).catch(() => FALLBACK_ADDRESS_LABEL);
    const endLabel = await reverseGeocodeLabel(row.end_lat, row.end_lon).catch(() => FALLBACK_ADDRESS_LABEL);
    const distanceKm = await computeTripDistanceKm(row.vehicle_id, row.started_at, row.ended_at).catch(() => 0);

    await query(
      `
        insert into vehicle_trips (
          vehicle_id,
          started_at,
          ended_at,
          start_lat,
          start_lon,
          end_lat,
          end_lon,
          start_label,
          end_label,
          duration_minutes,
          distance_km,
          created_at,
          updated_at
        )
        values (
          $1,
          $2::timestamptz,
          $3::timestamptz,
          $4,
          $5,
          $6,
          $7,
          $8,
          $9,
          greatest(1, ceil(extract(epoch from ($3::timestamptz - $2::timestamptz)) / 60.0))::int,
          $10,
          now(),
          now()
        )
        on conflict (vehicle_id, started_at) do update
        set
          ended_at = excluded.ended_at,
          end_lat = excluded.end_lat,
          end_lon = excluded.end_lon,
          start_label = coalesce(vehicle_trips.start_label, excluded.start_label),
          end_label = coalesce(vehicle_trips.end_label, excluded.end_label),
          duration_minutes = excluded.duration_minutes,
          distance_km = coalesce(vehicle_trips.distance_km, excluded.distance_km),
          updated_at = now()
      `,
      [
        row.vehicle_id,
        row.started_at,
        row.ended_at,
        row.start_lat,
        row.start_lon,
        row.end_lat,
        row.end_lon,
        startLabel,
        endLabel,
        distanceKm,
      ],
    );
  }
};

export const enrichVehicleTripAddresses = async (limit = 50) => {
  const trips = await query<TripAddressFixRow>(
    `
      select
        id,
        start_lat,
        start_lon,
        end_lat,
        end_lon,
        start_label,
        end_label
      from vehicle_trips
      where
        coalesce(start_label, '') = ''
        or start_label = $1
        or (
          end_lat is not null
          and end_lon is not null
          and (
            coalesce(end_label, '') = ''
            or end_label = $1
          )
        )
      order by started_at desc
      limit $2
    `,
    [FALLBACK_ADDRESS_LABEL, limit],
  );

  for (const trip of trips) {
    const nextStartLabel = (!trip.start_label || trip.start_label === FALLBACK_ADDRESS_LABEL)
      ? await reverseGeocodeLabel(trip.start_lat, trip.start_lon).catch(() => FALLBACK_ADDRESS_LABEL)
      : trip.start_label;
    const nextEndLabel = trip.end_lat != null && trip.end_lon != null && (!trip.end_label || trip.end_label === FALLBACK_ADDRESS_LABEL)
      ? await reverseGeocodeLabel(trip.end_lat, trip.end_lon).catch(() => FALLBACK_ADDRESS_LABEL)
      : trip.end_label;

    await query(
      `
        update vehicle_trips
        set
          start_label = $2,
          end_label = $3,
          updated_at = now()
        where id = $1
      `,
      [trip.id, nextStartLabel, nextEndLabel],
    );
  }
};