-- worker_jobs: queue for AI-mediated work the dashboard requests and
-- the worker daemon fulfills.
--
-- Currently only `kind = 'generate_rules'` is implemented:
--   input  = { intent: string, current: string }
--   output = { draft: string, explain: string }
--
-- The dashboard inserts a row with status='pending', subscribes via
-- realtime to its own row id, and renders the result when status flips
-- to 'done' (or 'error').

create table if not exists public.worker_jobs (
    id           bigserial primary key,
    kind         text        not null,
    scope        text        not null,
    input        jsonb       not null,
    output       jsonb,
    status       text        not null default 'pending',
    error        text,
    client_id    text,
    created_at   timestamptz not null default now(),
    updated_at   timestamptz not null default now()
);

create index if not exists worker_jobs_pending_idx
    on public.worker_jobs (created_at)
    where status = 'pending';

create or replace function public.worker_jobs_touch_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at := now();
    return new;
end;
$$;

drop trigger if exists worker_jobs_touch on public.worker_jobs;
create trigger worker_jobs_touch
    before update on public.worker_jobs
    for each row execute function public.worker_jobs_touch_updated_at();

alter table public.worker_jobs enable row level security;

drop policy if exists "auth read jobs" on public.worker_jobs;
create policy "auth read jobs"
    on public.worker_jobs
    for select
    to authenticated
    using (true);

drop policy if exists "auth insert jobs" on public.worker_jobs;
create policy "auth insert jobs"
    on public.worker_jobs
    for insert
    to authenticated
    with check (true);

-- The daemon runs as service-role, which bypasses RLS — so claim/complete
-- writes don't need an authenticated UPDATE policy.

alter publication supabase_realtime add table public.worker_jobs;
