tasq/supabase/migrations/20260927150000_add_programmer_day_sheets.sql

-- Programmer Day Sheets — daily approval workflow for programmer work logs.
-- Each programmer gets one sheet per calendar day (Manila timezone). Sheets
-- aggregate work-log entries and activity-log events, flow through
-- draft → pending → approved/disapproved state machine, and expose an
-- event trail (submitted, disapproved, justified, approved, amended).
--
-- Depends on:
--   20260927120000_add_programmer_tasks.sql  (programmer_tasks, programmer_task_activity_logs,
--                                             set_programmer_tasks_updated_at)
--   20260927140000_add_programmer_projects_worklogs_comments.sql
--                                            (programmer_task_work_logs)

-- ---------------------------------------------------------------------------
-- 1. manila_today() — canonical Manila-timezone "today" used everywhere
-- ---------------------------------------------------------------------------
create or replace function manila_today()
returns date
language sql
stable
security definer
set search_path = public
as $$
  select (now() at time zone 'Asia/Manila')::date;
$$;

-- ---------------------------------------------------------------------------
-- 2. programmer_day_sheets
-- ---------------------------------------------------------------------------
create table if not exists programmer_day_sheets (
  id               uuid        primary key default gen_random_uuid(),
  programmer_id    uuid        not null references profiles(id),
  work_date        date        not null,
  status           text        not null default 'draft'
                               check (status in ('draft','pending','disapproved','approved')),
  submitted_at     timestamptz,
  auto_submitted   bool        not null default false,
  reviewed_by      uuid        references profiles(id),
  reviewed_at      timestamptz,
  resubmissions    int         not null default 0,
  approved_snapshot jsonb,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),
  unique (programmer_id, work_date)
);

create index if not exists idx_programmer_day_sheets_status_work_date
  on programmer_day_sheets (status, work_date);

alter table programmer_day_sheets enable row level security;

-- updated_at trigger — reuse the existing function
drop trigger if exists trg_programmer_day_sheets_updated_at on programmer_day_sheets;
create trigger trg_programmer_day_sheets_updated_at
  before update on programmer_day_sheets
  for each row execute function set_programmer_tasks_updated_at();

-- ---------------------------------------------------------------------------
-- 3. programmer_day_sheet_events
-- ---------------------------------------------------------------------------
create table if not exists programmer_day_sheet_events (
  id               uuid        primary key default gen_random_uuid(),
  sheet_id         uuid        not null references programmer_day_sheets(id) on delete cascade,
  actor_id         uuid        references profiles(id),
  kind             text        not null
                               check (kind in ('submitted','auto_submitted','disapproved',
                                               'justified','approved','amended')),
  body             text,
  flagged_task_ids uuid[]      not null default '{}',
  created_at       timestamptz not null default now(),
  -- body must be non-empty for disapproval and justification events
  check (kind not in ('disapproved','justified') or (body is not null and body <> ''))
);

alter table programmer_day_sheet_events enable row level security;

-- ---------------------------------------------------------------------------
-- 4. work_date column on programmer_task_work_logs
-- ---------------------------------------------------------------------------
alter table programmer_task_work_logs
  add column if not exists work_date date;

-- Backfill: derive from created_at in Manila timezone
update programmer_task_work_logs
  set work_date = (created_at at time zone 'Asia/Manila')::date
  where work_date is null;

-- Now make it default + not null
alter table programmer_task_work_logs
  alter column work_date set default manila_today();

alter table programmer_task_work_logs
  alter column work_date set not null;

create index if not exists idx_programmer_task_work_logs_author_work_date
  on programmer_task_work_logs (author_id, work_date);

-- ---------------------------------------------------------------------------
-- 5. ensure_day_sheet(p_programmer, p_date) — idempotent sheet bootstrap
--    SECURITY DEFINER; callers (trigger functions) have no direct table access.
-- ---------------------------------------------------------------------------
create or replace function ensure_day_sheet(
  p_programmer uuid,
  p_date       date
)
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
  v_role   text;
  v_id     uuid;
  v_status text;
begin
  -- Only create sheets for programmers
  select role into v_role from profiles where id = p_programmer;
  if v_role is distinct from 'programmer' then
    return null;
  end if;

  -- Insert a draft sheet; conflict means the sheet already exists
  insert into programmer_day_sheets (programmer_id, work_date, status)
  values (p_programmer, p_date, 'draft')
  on conflict (programmer_id, work_date) do nothing;

  -- Fetch the (potentially pre-existing) sheet id and status
  select id, status
    into v_id, v_status
    from programmer_day_sheets
   where programmer_id = p_programmer
     and work_date = p_date;

  -- If the sheet was previously approved and we are touching today's date,
  -- reopen it to pending and record an amended event.
  if v_status = 'approved' and p_date = manila_today() then
    update programmer_day_sheets
       set status = 'pending',
           updated_at = now()
     where id = v_id;

    insert into programmer_day_sheet_events (sheet_id, actor_id, kind, body)
    values (v_id, null, 'amended', 'New work was logged after approval.');
  end if;

  return v_id;
end;
$$;

-- Restrict direct invocation — trigger functions are the only callers
revoke execute on function ensure_day_sheet(uuid, date) from public;
revoke execute on function ensure_day_sheet(uuid, date) from anon;
revoke execute on function ensure_day_sheet(uuid, date) from authenticated;

-- ---------------------------------------------------------------------------
-- 6. BEFORE INSERT trigger on programmer_task_work_logs
--    Validates and normalises work_date; raises work_date_locked when needed.
-- ---------------------------------------------------------------------------
create or replace function validate_work_log_date()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  v_sheet_status text;
begin
  -- Default work_date to today (Manila) when the caller left it null
  new.work_date := coalesce(new.work_date, manila_today());

  -- Future dates are never allowed
  if new.work_date > manila_today() then
    raise exception 'work_date_locked' using errcode = 'P0001';
  end if;

  -- Past dates are allowed only when the author already has an open sheet
  -- (status pending or disapproved) for that date — i.e. the window is still
  -- editable. No sheet or any other status → locked.
  if new.work_date < manila_today() then
    select status
      into v_sheet_status
      from programmer_day_sheets
     where programmer_id = new.author_id
       and work_date     = new.work_date;

    if v_sheet_status is null or v_sheet_status not in ('pending', 'disapproved') then
      raise exception 'work_date_locked' using errcode = 'P0001';
    end if;
  end if;

  return new;
end;
$$;

drop trigger if exists trg_work_logs_validate_date on programmer_task_work_logs;
create trigger trg_work_logs_validate_date
  before insert on programmer_task_work_logs
  for each row execute function validate_work_log_date();

-- ---------------------------------------------------------------------------
-- 7. AFTER INSERT trigger on programmer_task_work_logs
--    Ensures a day sheet exists for the author on the work_date.
-- ---------------------------------------------------------------------------
create or replace function after_work_log_ensure_sheet()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  perform ensure_day_sheet(new.author_id, new.work_date);
  return null;
end;
$$;

drop trigger if exists trg_work_logs_ensure_sheet on programmer_task_work_logs;
create trigger trg_work_logs_ensure_sheet
  after insert on programmer_task_work_logs
  for each row execute function after_work_log_ensure_sheet();

-- ---------------------------------------------------------------------------
-- 8. AFTER INSERT trigger on programmer_task_activity_logs
--    Ensures a day sheet for the task's assignee on relevant action types.
-- ---------------------------------------------------------------------------
create or replace function after_activity_log_ensure_sheet()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  v_assignee uuid;
begin
  if new.action_type not in ('started','paused','resumed','completed','cancelled','adjustment') then
    return null;
  end if;

  select assignee_id into v_assignee
    from programmer_tasks
   where id = new.task_id;

  if v_assignee is not null then
    perform ensure_day_sheet(
      v_assignee,
      (new.created_at at time zone 'Asia/Manila')::date
    );
  end if;

  return null;
end;
$$;

drop trigger if exists trg_activity_logs_ensure_sheet on programmer_task_activity_logs;
create trigger trg_activity_logs_ensure_sheet
  after insert on programmer_task_activity_logs
  for each row execute function after_activity_log_ensure_sheet();

-- ---------------------------------------------------------------------------
-- 9. RLS policies
-- ---------------------------------------------------------------------------

-- programmer_day_sheets: SELECT — own sheet or admin
drop policy if exists "programmer_day_sheets_select" on programmer_day_sheets;
create policy "programmer_day_sheets_select" on programmer_day_sheets
  for select to authenticated
  using (
    programmer_id = auth.uid()
    or exists (
      select 1 from profiles p
       where p.id = auth.uid()
         and p.role = 'admin'
    )
  );

-- programmer_day_sheet_events: SELECT — must be able to see the parent sheet
drop policy if exists "programmer_day_sheet_events_select" on programmer_day_sheet_events;
create policy "programmer_day_sheet_events_select" on programmer_day_sheet_events
  for select to authenticated
  using (
    exists (
      select 1 from programmer_day_sheets s
       where s.id = sheet_id
         and (
           s.programmer_id = auth.uid()
           or exists (
             select 1 from profiles p
              where p.id = auth.uid()
                and p.role = 'admin'
           )
         )
    )
  );

-- No client INSERT/UPDATE/DELETE policies on either table — all writes go
-- through SECURITY DEFINER functions or server-side triggers.

-- ---------------------------------------------------------------------------
-- 10. notifications.day_sheet_id FK
-- ---------------------------------------------------------------------------
alter table notifications
  add column if not exists day_sheet_id uuid
    references programmer_day_sheets(id) on delete cascade;

create index if not exists idx_notifications_day_sheet_id
  on notifications (day_sheet_id)
  where day_sheet_id is not null;

-- ---------------------------------------------------------------------------
-- 11. Realtime publication
-- ---------------------------------------------------------------------------
alter publication supabase_realtime add table programmer_day_sheets;
alter publication supabase_realtime add table programmer_day_sheet_events;