-- Create MOTIE heads target and view derived from motie_org_cur
-- Usage: run this in Supabase SQL Editor

-- 1) Targets (desired units with expected position, order, indent)
create table if not exists public.motie_heads_target (
  ord int primary key,
  unit text not null,
  expected_pos text not null,
  indent int not null default 0
);

-- Seed minimal sample (extend as needed; align with your current list)
insert into public.motie_heads_target(unit, expected_pos, ord, indent) values
  ('장관실','장관', 10, 0),
  ('대변인','대변인', 11, 1),
  ('홍보담당관','과장', 12, 2),
  ('홍보지원팀','팀장', 13, 2),
  ('감사관','감사관', 20, 1),
  ('감사담당관','과장', 21, 2),
  ('제1차관','1차관', 30, 0),
  ('운영지원과','과장', 31, 1),
  ('기획조정실','실장', 40, 1),
  ('정책기획관','국장', 41, 2)
on conflict (ord) do nothing;

-- 2) View: normalize basic whitespace and pick the best per unit from motie_org_cur
create or replace view public.motie_heads_cur as
with norm as (
  select trim(department) as department,
         trim(position) as position,
         trim(coalesce(name, '')) as name,
         trim(coalesce(phone, '')) as phone,
         trim(coalesce(task, '')) as task
  from public.motie_org_cur
),
candidates as (
  select t.unit, t.expected_pos, t.ord, t.indent,
         n.name, n.phone, n.task, n.position,
         (case 
            when n.position = t.expected_pos then 100
            when t.expected_pos = '대변인' and n.position like '%대변인%' and n.position not like '%부대변인%' then 90
            when n.position like '%'||t.expected_pos||'%' then 80
            else 0 end)
         + (case when nullif(n.phone, '') is not null and n.phone <> '-' then 5 else 0 end)
         + (case when nullif(n.name, '') is not null then 1 else 0 end) as score
  from public.motie_heads_target t
  left join norm n on n.department = t.unit
)
select distinct on (ord)
       unit,
       expected_pos as position,
       nullif(name,'') as name,
       coalesce(nullif(phone,''), '-') as phone,
       coalesce(nullif(task,''), '-') as task,
       ord, indent
from candidates
order by ord, score desc, name nulls last;

-- Optional: grant select to anon if needed
-- grant select on public.motie_heads_cur to anon;
