-- Developer task board (Nash, 2026-10-21): work for the developer is posted
-- on a board instead of by email. The Workspace CTO (and any other project
-- holding a board API key) adds, edits, reorders and removes tasks; the
-- developer moves each one from pending to ongoing to completed. Every change
-- is kept in dev_task_events, so the history and the questions on a task are
-- never lost. Additive only: no existing table is touched.

create table if not exists public.dev_tasks (
  id uuid primary key default gen_random_uuid(),
  task_number bigint generated always as identity unique,
  project text not null default 'HHA Workspace' check (char_length(project) between 2 and 60),
  title text not null check (char_length(title) between 3 and 200),
  summary text check (summary is null or char_length(summary) <= 500),
  urgency text not null default 'normal' check (urgency in ('urgent', 'high', 'normal', 'low')),
  status text not null default 'pending' check (status in ('pending', 'ongoing', 'completed')),
  -- Order inside a column, lower first. New tasks are placed by urgency.
  position double precision not null default 0,
  why text check (why is null or char_length(why) <= 4000),
  steps jsonb not null default '[]'::jsonb check (jsonb_typeof(steps) = 'array'),
  done_when text check (done_when is null or char_length(done_when) <= 4000),
  report_back text check (report_back is null or char_length(report_back) <= 2000),
  do_not_touch text check (do_not_touch is null or char_length(do_not_touch) <= 2000),
  details text check (details is null or char_length(details) <= 20000),
  due_date date,
  source text not null default 'api' check (char_length(source) between 2 and 60),
  created_by text,
  started_at timestamptz,
  completed_at timestamptz,
  completion_note text check (completion_note is null or char_length(completion_note) <= 4000),
  archived_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists dev_tasks_board_idx on public.dev_tasks (status, position) where archived_at is null;
create index if not exists dev_tasks_updated_idx on public.dev_tasks (updated_at desc);

create table if not exists public.dev_task_events (
  id uuid primary key default gen_random_uuid(),
  task_id uuid not null references public.dev_tasks(id) on delete cascade,
  action text not null check (action in ('created', 'updated', 'moved', 'reordered', 'archived', 'restored', 'comment')),
  from_status text,
  to_status text,
  actor_kind text not null check (actor_kind in ('api', 'admin', 'developer')),
  actor_label text not null,
  detail jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists dev_task_events_task_idx on public.dev_task_events (task_id, created_at);
create index if not exists dev_task_events_recent_idx on public.dev_task_events (created_at desc);

-- Server-side only (service role). No client policies.
alter table public.dev_tasks enable row level security;
alter table public.dev_task_events enable row level security;

comment on table public.dev_tasks is 'Developer task board: tasks posted by the CTO or other projects, moved by the developer from pending to ongoing to completed.';
comment on table public.dev_task_events is 'Append-only history of every task change and every comment on the developer task board.';
