-- Read-only Workspace copy of GPT MongoDB all_states. Mongo remains the source
-- of truth; this table does not replace permit.state_code or Synchron's data.
-- Preserve every source document, including the four duplicate state codes.
-- Exactly one document per code is marked canonical for future Workspace use.
create table if not exists public.all_states (
  mongo_document_id text primary key,
  source_state_id bigint,
  state_code text not null check (state_code ~ '^[A-Z]{2}$'),
  state_name text not null,
  country_code text not null check (country_code in ('US', 'CA')),
  is_canonical boolean not null default false,
  source_present boolean not null default true,
  details jsonb not null default '{}'::jsonb,
  source_updated_at text,
  source_sha256 text not null check (source_sha256 ~ '^[0-9a-f]{64}$'),
  synced_at timestamptz not null default now()
);

create index if not exists all_states_state_code_idx
  on public.all_states (state_code);
create unique index if not exists all_states_one_canonical_per_code_idx
  on public.all_states (state_code) where is_canonical;

-- No browser-facing RLS policy: only the service-role sync may read/write this
-- catalog until a reviewed state-information API is added. The sync imports
-- an explicit allowlist of operational fields and no portal credentials.
alter table public.all_states enable row level security;

comment on table public.all_states is
  'Service-only, credential-free mirror of GPT MongoDB all_states; Mongo is authoritative.';
