-- 0054 — Record every deleted trip, permit, document and trip participant (2026-10-06).
--
-- Trips have disappeared from production with no trace of who or what
-- deleted them. From now on every such delete, including rows removed by a
-- cascade when a trip is deleted, is copied here with the full row, the time
-- and the database role and client that ran it. Additive only; nothing reads
-- this table in the app. Paste in the Supabase SQL editor.

create table if not exists public.deleted_records_log (
  id bigint generated always as identity primary key,
  table_name text not null,
  record_id uuid,
  trip_id uuid,
  label text,
  row_data jsonb not null,
  deleted_at timestamptz not null default now(),
  db_role text not null default current_user,
  client_info text
);
create index if not exists deleted_records_log_trip_idx on public.deleted_records_log (trip_id);
create index if not exists deleted_records_log_time_idx on public.deleted_records_log (deleted_at desc);
alter table public.deleted_records_log enable row level security;

create or replace function public.log_deleted_record() returns trigger
language plpgsql security definer set search_path = public as $$
declare
  data jsonb := to_jsonb(old);
begin
  insert into public.deleted_records_log (table_name, record_id, trip_id, label, row_data, client_info)
  values (
    tg_table_name,
    nullif(data->>'id', '')::uuid,
    case when tg_table_name = 'trips' then nullif(data->>'id', '')::uuid else nullif(data->>'trip_id', '')::uuid end,
    coalesce(data->>'ref_code', data->>'file_name', data->>'permit_number', data->>'email'),
    data,
    concat_ws(' · ',
      nullif(current_setting('application_name', true), ''),
      nullif(nullif(current_setting('request.jwt.claims', true), '')::jsonb->>'role', ''),
      inet_client_addr()::text)
  );
  return old;
exception when others then
  -- Logging must never block the delete itself.
  raise warning 'deleted_records_log: %', sqlerrm;
  return old;
end $$;

revoke all on function public.log_deleted_record() from public, anon, authenticated;

drop trigger if exists log_deleted_trip on public.trips;
create trigger log_deleted_trip after delete on public.trips
  for each row execute function public.log_deleted_record();
drop trigger if exists log_deleted_permit on public.permits;
create trigger log_deleted_permit after delete on public.permits
  for each row execute function public.log_deleted_record();
drop trigger if exists log_deleted_document on public.documents;
create trigger log_deleted_document after delete on public.documents
  for each row execute function public.log_deleted_record();
drop trigger if exists log_deleted_participant on public.trip_participants;
create trigger log_deleted_participant after delete on public.trip_participants
  for each row execute function public.log_deleted_record();
