-- ME org pipeline core tables and views
-- Usage: run this in Supabase SQL Editor before running the crawler

-- 0) crawler_run is assumed to exist globally

-- 1) Raw HTML storage per run/page
create table if not exists public.me_org_raw (
  run_id uuid not null,
  kind text not null,           -- 'list' | 'detail'
  page text not null,           -- page number or label
  url text,
  html text,
  created_at timestamptz not null default now()
);
create index if not exists me_org_raw_run_id_idx on public.me_org_raw(run_id);

-- 2) Staging rows per run (dedupe happens in app code via key_hash)
create table if not exists public.me_org_stg (
  run_id uuid not null,
  department text not null,
  name text not null,
  position text not null,
  phone text,
  task text,
  key_hash text not null,
  row_hash text not null,
  created_at timestamptz not null default now()
);
create index if not exists me_org_stg_run_id_idx on public.me_org_stg(run_id);
create index if not exists me_org_stg_key_idx on public.me_org_stg(key_hash);

-- 3) SCD2 history store
create table if not exists public.me_org_hist (
  id bigserial primary key,
  key_hash text not null,
  department text not null,
  name text not null,
  position text not null,
  phone text,
  task text,
  row_hash text not null,
  valid_from timestamptz not null default now(),
  valid_to timestamptz,
  is_current boolean not null default true
);
create index if not exists me_org_hist_key_idx on public.me_org_hist(key_hash);
create index if not exists me_org_hist_current_idx on public.me_org_hist(is_current);

-- 4) Current view (open rows)
create or replace view public.me_org_cur as
select 
  key_hash,
  department,
  name,
  position,
  phone,
  task,
  row_hash,
  valid_from
from public.me_org_hist
where is_current = true and valid_to is null;

-- 5) Run snapshot
create table if not exists public.me_org_snapshot (
  run_id uuid primary key,
  rows int not null,
  status text not null, -- collected | passed | failed
  note text,
  created_at timestamptz not null default now()
);

