-- CairnQ canonical schema (Postgres dialect) — protocol_version 1
-- Single source of truth for the task table on Postgres. Idempotent; safe to
-- re-run. Time is stored as bigint epoch milliseconds (`*_ms`), generated by the
-- DB clock inside each statement (NOT supplied by the SDK — see sql/postgres/*).
-- JSON columns are jsonb. Status is a CHECK constraint (not a PG enum) so the
-- conformance status-set check matches across dialects. Ordered migrations are
-- canonical; there is no separate schema.sql.

create table if not exists cairnq_tasks (
    id text primary key,

    name text not null,
    queue text not null default 'default',

    status text not null check (
        status in ('queued', 'running', 'succeeded', 'failed', 'canceled')
    ),

    payload jsonb not null,
    result jsonb,
    error jsonb,
    metadata jsonb not null default '{}'::jsonb,

    progress double precision,
    message text,

    attempt integer not null default 0,
    max_attempts integer not null default 3,

    priority integer not null default 0,

    worker_id text,
    lease_until_ms bigint,

    run_at_ms bigint not null,

    cancel_requested_at_ms bigint,

    parent_id text,
    root_id text,
    correlation_id text,

    created_at_ms bigint not null,
    updated_at_ms bigint not null,
    completed_at_ms bigint
);

-- Serves the claim query: WHERE queue=? AND status='queued' ORDER BY priority
-- desc, created_at_ms asc (run_at_ms applied as a residual filter).
create index if not exists cairnq_tasks_claim_idx
    on cairnq_tasks (queue, status, priority desc, created_at_ms);
create index if not exists cairnq_tasks_status_idx on cairnq_tasks (status);
create index if not exists cairnq_tasks_name_idx on cairnq_tasks (name);
create index if not exists cairnq_tasks_root_idx on cairnq_tasks (root_id);
create index if not exists cairnq_tasks_correlation_idx on cairnq_tasks (correlation_id);

-- key = business-stable pointer to the *current* task for that key.
-- task_id = one concrete execution. Kept separate (not a unique constraint on
-- tasks) so reuse / reject / replace are natural.
create table if not exists cairnq_task_keys (
    key text primary key,
    task_id text not null references cairnq_tasks(id) on delete cascade,
    created_at_ms bigint not null,
    updated_at_ms bigint not null
);
