-- One serialized publisher and durable Mongo checkpoint. No browser access.
create table public.state_catalog_sync_status (
  singleton boolean primary key default true check (singleton),
  holder text,
  lease_until timestamptz,
  heartbeat_at timestamptz,
  resume_token jsonb,
  last_success_at timestamptz,
  last_error_code text,
  last_changed_count integer,
  last_source_count integer
);
alter table public.state_catalog_sync_status enable row level security;
insert into public.state_catalog_sync_status (singleton) values (true);
grant select on public.state_catalog_sync_status to service_role;

create function public.claim_state_catalog_sync(p_holder text, p_ttl_seconds integer default 300)
returns boolean language plpgsql security definer set search_path=public,pg_temp as $$
begin
  if p_holder is null or length(p_holder) < 8 then raise exception 'Invalid holder'; end if;
  update public.state_catalog_sync_status set holder=p_holder,
    lease_until=now()+make_interval(secs=>least(greatest(p_ttl_seconds,30),600)), heartbeat_at=now()
  where singleton and (holder=p_holder or holder is null or lease_until<now());
  return found;
end $$;

create function public.release_state_catalog_sync(p_holder text)
returns void language sql security definer set search_path=public,pg_temp as $$
  update public.state_catalog_sync_status set holder=null,lease_until=null where singleton and holder=p_holder;
$$;

create function public.publish_state_catalog_snapshot(p_holder text,p_rows jsonb,p_resume_token jsonb default null)
returns jsonb language plpgsql security definer set search_path=public,pg_temp as $$
declare total integer; active integer; removed integer; changed integer:=0; written integer;
begin
  perform 1 from public.state_catalog_sync_status where singleton and holder=p_holder and lease_until>now() for update;
  if not found then raise exception 'State catalog lease lost'; end if;
  if p_rows is null or jsonb_typeof(p_rows)<>'array' then raise exception 'Snapshot must be an array'; end if;
  total:=jsonb_array_length(p_rows);
  if total<1 or total>1000 then raise exception 'Invalid snapshot size'; end if;
  if total<>(select count(distinct x->>'mongo_document_id') from jsonb_array_elements(p_rows) x)
    or exists(select 1 from jsonb_array_elements(p_rows) x where
      coalesce(x->>'state_code','') !~ '^[A-Z]{2}$' or coalesce(x->>'state_name','')=''
      or coalesce(x->>'source_sha256','') !~ '^[0-9a-f]{64}$'
      or coalesce((x->>'source_present')::boolean,false) is not true)
    then raise exception 'Invalid snapshot records'; end if;
  if exists(select x->>'state_code' from jsonb_array_elements(p_rows) x group by x->>'state_code'
      having count(*) filter(where (x->>'is_canonical')::boolean)<>1)
    then raise exception 'Invalid canonical selection'; end if;
  select count(*) into active from public.all_states where source_present;
  select count(*) into removed from public.all_states s where s.source_present and not exists
    (select 1 from jsonb_array_elements(p_rows) x where x->>'mongo_document_id'=s.mongo_document_id);
  if active>0 and removed>active*0.25 then raise exception 'Excessive source removals'; end if;

  -- Clear conflicting canonical flags inside the same transaction as the upsert.
  update public.all_states s set is_canonical=false where s.is_canonical and not exists
    (select 1 from jsonb_array_elements(p_rows) x where x->>'mongo_document_id'=s.mongo_document_id
      and x->>'state_code'=s.state_code and (x->>'is_canonical')::boolean);
  insert into public.all_states as current (
    mongo_document_id,source_state_id,state_code,state_name,country_code,is_canonical,
    source_present,details,source_updated_at,source_sha256,synced_at)
  select x.mongo_document_id,x.source_state_id,x.state_code,x.state_name,x.country_code,x.is_canonical,
    true,x.details,x.source_updated_at,x.source_sha256,now()
  from jsonb_to_recordset(p_rows) as x(mongo_document_id text,source_state_id bigint,state_code text,
    state_name text,country_code text,is_canonical boolean,details jsonb,source_updated_at text,source_sha256 text)
  on conflict(mongo_document_id) do update set source_state_id=excluded.source_state_id,
    state_code=excluded.state_code,state_name=excluded.state_name,country_code=excluded.country_code,
    is_canonical=excluded.is_canonical,source_present=true,details=excluded.details,
    source_updated_at=excluded.source_updated_at,source_sha256=excluded.source_sha256,synced_at=now()
  where current.source_sha256<>excluded.source_sha256 or not current.source_present
    or current.is_canonical<>excluded.is_canonical;
  get diagnostics written=row_count;
  changed:=written;
  update public.all_states s set source_present=false,is_canonical=false,synced_at=now()
    where s.source_present and not exists
      (select 1 from jsonb_array_elements(p_rows) x where x->>'mongo_document_id'=s.mongo_document_id);
  update public.state_catalog_sync_status set resume_token=p_resume_token,last_success_at=now(),
    heartbeat_at=now(),last_error_code=null,last_changed_count=changed,last_source_count=total where singleton;
  return jsonb_build_object('source_records',total,'changed',changed,'removed',removed,'status','synced');
end $$;

create function public.report_state_catalog_sync_error(p_holder text,p_code text)
returns void language sql security definer set search_path=public,pg_temp as $$
  update public.state_catalog_sync_status set last_error_code=left(p_code,80),heartbeat_at=now()
  where singleton and holder=p_holder;
$$;
revoke all on function public.claim_state_catalog_sync(text,integer) from public,anon,authenticated;
revoke all on function public.release_state_catalog_sync(text) from public,anon,authenticated;
revoke all on function public.publish_state_catalog_snapshot(text,jsonb,jsonb) from public,anon,authenticated;
revoke all on function public.report_state_catalog_sync_error(text,text) from public,anon,authenticated;
grant execute on function public.claim_state_catalog_sync(text,integer) to service_role;
grant execute on function public.release_state_catalog_sync(text) to service_role;
grant execute on function public.publish_state_catalog_snapshot(text,jsonb,jsonb) to service_role;
grant execute on function public.report_state_catalog_sync_error(text,text) to service_role;
