-- Holidays: admin-maintained list of special-holiday dates, replacing
-- legacy's invoice_settings.holidayPay.dates array (JavaScript.html:
-- 6803-6830) with its own real table, per this stage's confirmed spec:
-- viewable by the same audience as /schedule (real agents + billing
-- admins), writable by is_admin only.
--
-- This is a DIFFERENT axis from invoice_settings.holiday_pay (jsonb,
-- {enabled, rate, standardHours, dates}) which still owns the top-up
-- RATE/percentage config — this table only owns WHICH DATES are
-- holidays. buildLineItems()'s existing holidayPay.dates-array logic
-- is unchanged by this migration; wiring holidays -> that array (or
-- replacing it) is Stage 3 UI work, not a schema concern.
--
-- View audience mirrors /schedule's real (has_permission-gated) write
-- audience for context, but is intentionally its OWN, more specific
-- policy — not a copy of /schedule's own `using (true)` SELECT policy
-- (schedule_select_all is deliberately wide-open, confirmed intentional
-- earlier this session; holidays' view access is narrower by design:
-- real CS agents (is_agent=true) OR rate_and_schedule billing admins,
-- not literally everyone signed in).

create table if not exists public.holidays (
  id          uuid primary key default gen_random_uuid(),
  date        date not null unique,
  label       text not null,
  added_by    text,
  created_at  timestamptz not null default now()
);

alter table public.holidays enable row level security;

-- No existing helper checks "is the CURRENT signed-in user a real CS
-- agent" (profiles.is_agent is a plain column, added 0031, with no
-- is_invoice_reviewer()/is_invoice_approver()-style wrapper yet) — added
-- here since this policy is the first to need it, following the same
-- small-named-boolean-function convention as those two.
create or replace function public.is_current_user_agent()
returns boolean
language sql stable security definer set search_path = public as $$
  select coalesce((select is_agent from public.profiles where id = auth.uid()), false);
$$;

grant execute on function public.is_current_user_agent() to authenticated;

drop policy if exists "holidays_select" on public.holidays;
create policy "holidays_select"
on public.holidays
for select
to authenticated
using (
  public.is_current_user_agent()
  or public.has_permission('rate_and_schedule', need_write => true)
);

drop policy if exists "holidays_insert" on public.holidays;
create policy "holidays_insert"
on public.holidays
for insert
to authenticated
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

drop policy if exists "holidays_update" on public.holidays;
create policy "holidays_update"
on public.holidays
for update
to authenticated
using (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true))
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

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

do $migration_guard$
declare
  table_exists boolean;
  policy_count int;
  fn_exists boolean;
begin
  select exists (
    select 1 from information_schema.tables
    where table_schema = 'public' and table_name = 'holidays'
  ) into table_exists;
  if not table_exists then
    raise exception 'Aborting 0032: public.holidays table was not created';
  end if;

  select count(*) into policy_count from pg_policies where schemaname = 'public' and tablename = 'holidays';
  if policy_count <> 4 then
    raise exception 'Aborting 0032: expected 4 policies on holidays (select/insert/update/delete), found %', policy_count;
  end if;

  select exists (
    select 1 from pg_proc where proname = 'is_current_user_agent'
  ) into fn_exists;
  if not fn_exists then
    raise exception 'Aborting 0032: public.is_current_user_agent() function was not created';
  end if;
end $migration_guard$;