-- Time Off (NSD/PTO) feature — port from the legacy KHAIZEN Team Portal
-- (Google Apps Script + Sheet-as-database, shared static password). Real
-- per-user auth/permissions replace the shared password; approved
-- requests write into the EXISTING `schedule` table (via a new service-
-- role API route, not this migration) rather than building a second,
-- competing "who's off when" system.
--
-- This migration:
--   1. Adds feature_key 'time_off' to feature_registry.
--   2. Redefines has_permission() to exclude 'time_off' from the
--      is_broad_reviewer bypass too, alongside invoicing/rate_and_
--      schedule/coaching -- same reasoning as 0038 (coaching): this is
--      real HR/leave data, a broad-reviewer grant elsewhere shouldn't
--      silently expose it.
--   3. Creates time_off_balances (one row per agent per year --
--      entitlement/carryover_in/adjustment stored, `used` is always
--      COMPUTED client-side from approved time_off_requests + this
--      row's pre_migration_used bridge value, never stored, so there's
--      no second source of truth to drift).
--   4. Creates time_off_requests (pending/approved/denied/revoked
--      workflow, no delete policy -- same "never delete, transition
--      status instead" convention as `schedule` itself).
--   5. Grants Berry and Kenn 'time_off' can_view=true, can_write=true
--      (the stated approver list, alongside Edwin via is_admin).
--   6. Seeds real 2026 starting balances from the legacy tool's actual
--      screenshots, so nobody's real balance is lost in the migration.

-- ============================================================
-- 1. New feature_key
-- ============================================================

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('time_off', 'Time Off', 5)
on conflict (feature_key) do nothing;

-- ============================================================
-- 2. has_permission() -- is_broad_reviewer clause now also excludes
-- 'time_off', not just 'invoicing'/'rate_and_schedule'/'coaching'.
-- ============================================================

create or replace function public.has_permission(feature text, need_write boolean default false)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.permissions
    where user_id = auth.uid()
      and feature_key = feature
      and can_view
      and (not need_write or can_write)
  )
  or exists (
    select 1 from public.profiles where id = auth.uid() and is_admin = true
  )
  or (
    not need_write
    and feature not in ('invoicing', 'rate_and_schedule', 'coaching', 'time_off')
    and exists (
      select 1 from public.profiles where id = auth.uid() and is_broad_reviewer = true
    )
  );
$$;

grant execute on function public.has_permission(text, boolean) to authenticated;

-- ============================================================
-- 3. time_off_balances
-- ============================================================

create table if not exists public.time_off_balances (
  id                 uuid primary key default gen_random_uuid(),
  agent_id           text not null,
  year               int not null,
  entitlement        numeric not null default 15,
  carryover_in       numeric not null default 0,
  adjustment         numeric not null default 0,
  adjustment_note    text,
  adjustment_by      text,
  adjustment_at      timestamptz,
  pre_migration_used numeric not null default 0,
  preferred_date     text,
  updated_at         timestamptz not null default now(),
  unique (agent_id, year)
);

alter table public.time_off_balances enable row level security;

create policy "time_off_balances_select"
on public.time_off_balances
for select
to authenticated
using (
  agent_id = public.current_agent_id()
  or public.has_permission('time_off', need_write => true)
);

create policy "time_off_balances_insert_admin"
on public.time_off_balances
for insert
to authenticated
with check (public.has_permission('time_off', need_write => true));

create policy "time_off_balances_update_admin"
on public.time_off_balances
for update
to authenticated
using (public.has_permission('time_off', need_write => true))
with check (public.has_permission('time_off', need_write => true));

-- No delete policy -- balances are yearly rows meant to persist as a
-- historical record, same "never delete" convention as `schedule`.

-- ============================================================
-- 4. time_off_requests
-- ============================================================

create table if not exists public.time_off_requests (
  id            uuid primary key default gen_random_uuid(),
  agent_id      text not null,
  request_type  text not null check (request_type in ('NSD', 'PERSONAL', 'PREFERRED', 'OTHER')),
  start_date    date not null,
  end_date      date not null,
  reason        text,
  status        text not null default 'pending' check (status in ('pending', 'approved', 'denied', 'revoked')),
  requested_at  timestamptz not null default now(),
  decided_by    text,
  decided_at    timestamptz,
  denial_reason text,
  check (end_date >= start_date)
);

alter table public.time_off_requests enable row level security;

create policy "time_off_requests_select"
on public.time_off_requests
for select
to authenticated
using (
  agent_id = public.current_agent_id()
  or public.has_permission('time_off', need_write => true)
);

-- Self-insert only, and only as a fresh pending request -- mirrors
-- invoices_insert_own's exact shape (0008): agent_id must be your own,
-- status is fixed to the one value a brand-new request can start as.
create policy "time_off_requests_insert_own"
on public.time_off_requests
for insert
to authenticated
with check (
  agent_id = public.current_agent_id()
  and status = 'pending'
);

-- Decisions (approve/deny/revoke) go through the has_permission() gate
-- only -- deliberately NOT self-updatable even by the requester, so an
-- agent can never approve/revoke their own request by editing the row
-- directly. The /api/time-off/decide route is the only intended writer
-- of this transition; RLS is still the real enforcement either way.
create policy "time_off_requests_update_admin"
on public.time_off_requests
for update
to authenticated
using (public.has_permission('time_off', need_write => true))
with check (public.has_permission('time_off', need_write => true));

-- No delete policy -- "revoked" is a status transition, not a row
-- removal, same convention as everywhere else in this schema.

-- ============================================================
-- 5. Grant Berry and Kenn 'time_off' -- can_view=true, can_write=true.
-- Edwin already covered via is_admin. Nothing else about either
-- profile/permissions changes.
-- ============================================================

insert into public.permissions (user_id, feature_key, can_view, can_write) values
  ('e5f06a25-8e3e-4839-94be-c1ed852fc458', 'time_off', true, true), -- Berry
  ('c5bc18b2-99ac-4c8d-b8c0-7b49273c5719', 'time_off', true, true)  -- Kenn
on conflict (user_id, feature_key) do update
  set can_view = true, can_write = true;

-- ============================================================
-- 6. Seed real 2026 starting balances from the legacy tool's actual
-- screenshots (agent_id slugs per 0022's idOf(name) scheme). Berry gets
-- entitlement=25 (manager rate, per her own legacy card); everyone else
-- is the standard 15. pre_migration_used carries the legacy "Used"
-- figure forward since there are no real time_off_requests rows behind
-- it yet -- see the migration header comment / this session's plan doc
-- for why this field exists instead of a stored `used` column.
-- ============================================================

insert into public.time_off_balances (agent_id, year, entitlement, carryover_in, pre_migration_used, preferred_date) values
  ('mon',       2026, 15, 2.5,   9.5, 'Oct 14-15'),
  ('jurina',    2026, 15, 0,     2,   'Dec 30'),
  ('mayvel',    2026, 15, 0,     2,   'Sept 16'),
  ('kate',      2026, 15, 0,     4,   'Jul 20'),
  ('rubyrose',  2026, 15, 0,     2,   'Easter'),
  ('andrew',    2026, 15, 11.25, 0,   'Jan 20'),
  ('dominic',   2026, 15, 8,     0,   'Nov 7'),
  ('agent_042', 2026, 15, 13.75, 5,   null),
  ('berry',     2026, 25, 0,     6,   null)
on conflict (agent_id, year) do nothing;

-- ============================================================
-- Self-verification guards
-- ============================================================

do $migration_guard$
declare
  balances_policy_count int;
  requests_policy_count int;
  feature_count int;
  berry_perm_count int;
  kenn_perm_count int;
  seed_count int;
  mon_carryover numeric;
begin
  select count(*) into balances_policy_count from pg_policies where schemaname = 'public' and tablename = 'time_off_balances';
  select count(*) into requests_policy_count from pg_policies where schemaname = 'public' and tablename = 'time_off_requests';
  if balances_policy_count <> 3 then
    raise exception 'Aborting 0040: expected 3 policies on time_off_balances, found %', balances_policy_count;
  end if;
  if requests_policy_count <> 3 then
    raise exception 'Aborting 0040: expected 3 policies on time_off_requests, found %', requests_policy_count;
  end if;

  select count(*) into feature_count from public.feature_registry where feature_key = 'time_off';
  if feature_count <> 1 then
    raise exception 'Aborting 0040: expected feature_registry to have exactly 1 time_off row, found %', feature_count;
  end if;

  select count(*) into berry_perm_count from public.permissions
    where user_id = 'e5f06a25-8e3e-4839-94be-c1ed852fc458' and feature_key = 'time_off' and can_view = true and can_write = true;
  if berry_perm_count <> 1 then
    raise exception 'Aborting 0040: expected Berry to have exactly 1 time_off permissions row with view+write, found %', berry_perm_count;
  end if;

  select count(*) into kenn_perm_count from public.permissions
    where user_id = 'c5bc18b2-99ac-4c8d-b8c0-7b49273c5719' and feature_key = 'time_off' and can_view = true and can_write = true;
  if kenn_perm_count <> 1 then
    raise exception 'Aborting 0040: expected Kenn to have exactly 1 time_off permissions row with view+write, found %', kenn_perm_count;
  end if;

  select count(*) into seed_count from public.time_off_balances where year = 2026;
  if seed_count <> 9 then
    raise exception 'Aborting 0040: expected 9 seeded time_off_balances rows for 2026, found %', seed_count;
  end if;

  select carryover_in into mon_carryover from public.time_off_balances where agent_id = 'mon' and year = 2026;
  if mon_carryover <> 2.5 then
    raise exception 'Aborting 0040: expected Mon carryover_in = 2.5, found %', mon_carryover;
  end if;
end $migration_guard$;