-- Koncowa, transakcyjna podmiana tabeli. Przed uruchomieniem zatrzymaj tracker-api.
-- Uruchomienie:
--   psql "$DATABASE_URL" -v positions_swap_confirm=YES \
--     -f scripts/swap-positions-shadow.sql

\set ON_ERROR_STOP on

\if :{?positions_swap_confirm}
\else
\set positions_swap_confirm 'NO'
\endif

SELECT upper(:'positions_swap_confirm') = 'YES' AS swap_confirmed \gset
\if :swap_confirmed
\else
\echo 'PRZERWANO: wymagane -v positions_swap_confirm=YES.'
\quit
\endif


BEGIN;

LOCK TABLE public.positions IN ACCESS EXCLUSIVE MODE;

DO $preflight$
BEGIN
  IF to_regclass('public.positions_compact_build') IS NULL THEN
    RAISE EXCEPTION 'Brak public.positions_compact_build';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM public.tracker_positions_rebuild_state
    WHERE singleton AND status = 'ready'
  ) THEN
    RAISE EXCEPTION 'Przebudowa nie ma statusu ready';
  END IF;
END
$preflight$;

INSERT INTO public.positions_compact_build (
  id,
  device_id,
  vehicle_id,
  imei,
  protocol_name,
  raw_payload,
  device_time,
  server_time,
  lat,
  lon,
  speed,
  heading,
  ignition,
  battery,
  gsm_signal,
  satellite_count,
  gps_valid,
  charging,
  defense,
  position_type,
  geom
)
SELECT
  p.id,
  p.device_id,
  p.vehicle_id,
  p.imei,
  p.protocol_name,
  CASE WHEN p.server_time >= state.raw_payload_cutoff THEN p.raw_payload ELSE NULL END,
  p.device_time,
  p.server_time,
  p.lat,
  p.lon,
  p.speed,
  p.heading,
  p.ignition,
  p.battery,
  p.gsm_signal,
  p.satellite_count,
  p.gps_valid,
  p.charging,
  p.defense,
  p.position_type,
  p.geom
FROM public.positions p
CROSS JOIN public.tracker_positions_rebuild_state state
WHERE state.singleton
  AND p.id > state.cutoff_id
ORDER BY p.id
ON CONFLICT (id) DO NOTHING;

DO $validate$
DECLARE
  source_count bigint;
  compact_count bigint;
  source_moving bigint;
  compact_moving bigint;
  source_overspeed bigint;
  compact_overspeed bigint;
  boundary_differences bigint;
BEGIN
  SELECT COUNT(*) INTO source_count FROM public.positions;
  SELECT COUNT(*) INTO compact_count FROM public.positions_compact_build;

  IF compact_count <= 0 OR compact_count >= source_count THEN
    RAISE EXCEPTION 'Podejrzana kompakcja: source %, compact %', source_count, compact_count;
  END IF;

  SELECT COUNT(*) INTO source_moving
  FROM public.positions
  WHERE NOT (ignition IS FALSE AND COALESCE(speed, 0) <= 0);
  SELECT COUNT(*) INTO compact_moving
  FROM public.positions_compact_build
  WHERE NOT (ignition IS FALSE AND COALESCE(speed, 0) <= 0);
  IF source_moving <> compact_moving THEN
    RAISE EXCEPTION 'Punkty jazdy nie sa identyczne: source %, compact %',
      source_moving, compact_moving;
  END IF;

  SELECT COUNT(*) INTO source_overspeed FROM public.positions WHERE speed > 140;
  SELECT COUNT(*) INTO compact_overspeed FROM public.positions_compact_build WHERE speed > 140;
  IF source_overspeed <> compact_overspeed THEN
    RAISE EXCEPTION 'Punkty >140 nie sa identyczne: source %, compact %',
      source_overspeed, compact_overspeed;
  END IF;

  SELECT COUNT(*) INTO boundary_differences
  FROM (
    SELECT vehicle_id, MIN(server_time) first_time, MAX(server_time) last_time
    FROM public.positions GROUP BY vehicle_id
    EXCEPT
    SELECT vehicle_id, MIN(server_time), MAX(server_time)
    FROM public.positions_compact_build GROUP BY vehicle_id
  ) differences;
  IF boundary_differences <> 0 THEN
    RAISE EXCEPTION 'Niezgodne granice historii dla % pojazdow', boundary_differences;
  END IF;
END
$validate$;

ALTER SEQUENCE public.positions_id_seq OWNED BY NONE;
ALTER TABLE public.positions RENAME TO positions_uncompacted_backup;
ALTER TABLE public.positions_compact_build RENAME TO positions;
ALTER TABLE public.positions OWNER TO postgres;
GRANT ALL PRIVILEGES ON TABLE public.positions TO tracker;
ALTER SEQUENCE public.positions_id_seq OWNED BY public.positions.id;
GRANT ALL PRIVILEGES ON SEQUENCE public.positions_id_seq TO tracker;

DROP TABLE public.positions_uncompacted_backup;

ALTER TABLE public.positions
  RENAME CONSTRAINT positions_compact_build_pkey TO positions_pkey;
ALTER TABLE public.positions
  RENAME CONSTRAINT positions_compact_build_device_id_fkey TO positions_device_id_fkey;
ALTER TABLE public.positions
  RENAME CONSTRAINT positions_compact_build_vehicle_id_fkey TO positions_vehicle_id_fkey;

ALTER INDEX public.idx_positions_vehicle_time_compact
  RENAME TO idx_positions_vehicle_time;
ALTER INDEX public.idx_positions_device_time_compact
  RENAME TO idx_positions_device_time;
ALTER INDEX public.idx_positions_raw_payload_retention_compact
  RENAME TO idx_positions_raw_payload_retention;

SELECT setval(
  'public.positions_id_seq',
  GREATEST((SELECT COALESCE(MAX(id), 0) FROM public.positions), 1),
  TRUE
);

UPDATE public.tracker_positions_rebuild_state
SET status = 'complete', completed_at = clock_timestamp()
WHERE singleton;

COMMIT;

ANALYZE public.positions;

SELECT
  COUNT(*) AS positions_count,
  MIN(server_time) AS first_position,
  MAX(server_time) AS last_position,
  pg_size_pretty(pg_relation_size('public.positions')) AS heap,
  pg_size_pretty(pg_indexes_size('public.positions')) AS indexes,
  pg_size_pretty(pg_total_relation_size('public.positions')) AS total
FROM public.positions;
