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

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

-- 2) View: pick the best per unit from moef_org_cur without cross-department matching
create or replace view public.moef_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.moef_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 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.moef_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;

