create table if not exists public.subcontractor_po_items (
  id uuid default gen_random_uuid() primary key,
  po_id uuid references public.subcontractor_payments(id) on delete cascade not null,
  task_id uuid references public.tasks(id) on delete set null,
  description text not null,
  amount numeric(10,2) not null,
  sort_order integer default 0 not null,
  created_at timestamptz default now() not null
);

alter table public.subcontractor_po_items enable row level security;

drop policy if exists "Team all subcontractor_po_items" on public.subcontractor_po_items;
create policy "Team all subcontractor_po_items" on public.subcontractor_po_items
  for all using (get_my_role() = 'team') with check (get_my_role() = 'team');

drop policy if exists "External reads own subcontractor_po_items" on public.subcontractor_po_items;
create policy "External reads own subcontractor_po_items" on public.subcontractor_po_items
  for select using (
    get_my_role() = 'external'
    and po_id in (
      select id from public.subcontractor_payments
      where profile_id = auth.uid()
    )
  );

insert into public.subcontractor_po_items (po_id, task_id, description, amount, sort_order)
select id, null, description, amount, 0
from public.subcontractor_payments sp
where not exists (
  select 1 from public.subcontractor_po_items item
  where item.po_id = sp.id
);