-- Sub-created invoices submitted to team for payment
create table public.subcontractor_invoices (
  id uuid default gen_random_uuid() primary key,
  profile_id uuid references public.profiles(id) on delete cascade not null,
  invoice_number text not null,
  status text not null default 'draft' check (status in ('draft', 'submitted', 'paid')),
  notes text not null default '',
  submitted_at timestamptz,
  paid_at timestamptz,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

create table public.subcontractor_invoice_items (
  id uuid default gen_random_uuid() primary key,
  invoice_id uuid references public.subcontractor_invoices(id) on delete cascade not null,
  task_id uuid references public.tasks(id) on delete set null,
  description text not null,
  quantity numeric(10,2) not null default 1,
  unit_price numeric(10,2) not null default 0,
  sort_order integer not null default 0,
  created_at timestamptz default now() not null
);

alter table public.subcontractor_invoices enable row level security;
alter table public.subcontractor_invoice_items enable row level security;

-- Team: full access
create policy "Team all subcontractor_invoices" on public.subcontractor_invoices
  for all using (get_my_role() = 'team') with check (get_my_role() = 'team');

-- Subs: read own
create policy "Sub select own invoices" on public.subcontractor_invoices
  for select using (profile_id = auth.uid() and get_my_role() = 'external');

-- Subs: create own
create policy "Sub insert own invoices" on public.subcontractor_invoices
  for insert with check (profile_id = auth.uid() and get_my_role() = 'external');

-- Subs: update own non-paid (submit draft, etc.)
create policy "Sub update own non-paid invoices" on public.subcontractor_invoices
  for update using (profile_id = auth.uid() and get_my_role() = 'external' and status != 'paid');

-- Subs: delete own drafts only
create policy "Sub delete own draft invoices" on public.subcontractor_invoices
  for delete using (profile_id = auth.uid() and get_my_role() = 'external' and status = 'draft');

-- Team: full access to items
create policy "Team all sub invoice items" on public.subcontractor_invoice_items
  for all using (get_my_role() = 'team') with check (get_my_role() = 'team');

-- Subs: read items on own invoices
create policy "Sub read own invoice items" on public.subcontractor_invoice_items
  for select using (
    invoice_id in (select id from public.subcontractor_invoices where profile_id = auth.uid())
  );

-- Subs: manage items on own draft invoices only
create policy "Sub insert draft invoice items" on public.subcontractor_invoice_items
  for insert with check (
    invoice_id in (select id from public.subcontractor_invoices where profile_id = auth.uid() and status = 'draft')
  );

create policy "Sub update draft invoice items" on public.subcontractor_invoice_items
  for update using (
    invoice_id in (select id from public.subcontractor_invoices where profile_id = auth.uid() and status = 'draft')
  );

create policy "Sub delete draft invoice items" on public.subcontractor_invoice_items
  for delete using (
    invoice_id in (select id from public.subcontractor_invoices where profile_id = auth.uid() and status = 'draft')
  );