-- Shift Handover — ported from legacy state.handovers[date] = [...entries]
-- (JavaScript.html:5295-5573). One row per handover note instead of a
-- per-date array; agent_id is free text (matching invoices.agent_id,
-- schedule.agent_id) with no FK, per this project's established
-- convention (no real `agents` table exists to reference).
--
-- author_id is nullable: null means admin-authored, matching legacy's
-- own '__admin__' sentinel (resolveAuthor() there special-cases it to
-- "Admin"). accepted_by/accepted_by_name/accepted_at are all null until
-- someone accepts ownership — accepted_by_name is denormalized since
-- this project has no names table (profiles has no display-name column;
-- agent_id is shown verbatim everywhere else).
--
-- RLS: deliberately open, matching schedule's own "no dedicated gate"
-- precedent (0011's schedule_select_all is using(true) for any
-- authenticated user) — legacy's handover feature has NO permission
-- gate at all (every admin and every agent can post/accept), unlike
-- performance/invoicing which are behind has_permission(). Read/insert/
-- update (the "accept" action) are all using(true)/with check(true);
-- delete is restricted to is_admin, matching legacy's admin-only delete
-- button (the agent view has no delete option at all, only accept).

create table if not exists public.handovers (
  id                uuid primary key default gen_random_uuid(),
  entry_date        date not null,
  from_shift        text not null,
  to_shift          text not null,
  author_id         text,              -- agent_id; null = admin-authored
  notes             text not null,
  accepted_by       text,              -- agent_id who accepted, null until accepted
  accepted_by_name  text,              -- denormalized display (no names table)
  accepted_at       timestamptz,
  created_at        timestamptz not null default now()
);

create index if not exists handovers_entry_date_idx on public.handovers (entry_date);

alter table public.handovers enable row level security;

create policy "handovers_select_all"
on public.handovers
for select
to authenticated
using (true);

create policy "handovers_insert_all"
on public.handovers
for insert
to authenticated
with check (true);

create policy "handovers_update_all"
on public.handovers
for update
to authenticated
using (true)
with check (true);

create policy "handovers_delete_admin"
on public.handovers
for delete
to authenticated
using (
  exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

do $migration_guard$
declare
  bad_count int;
begin
  select count(*) into bad_count
  from pg_policies
  where schemaname = 'public' and tablename = 'handovers';
  if bad_count <> 4 then
    raise exception 'Aborting 0027: expected 4 policies on handovers, found %', bad_count;
  end if;
end $migration_guard$;