-- Initial schema for the job queue.
--
-- Design notes (reasoning kept in docs/architecture.md, not in this file beyond
-- a few non-obvious choices):
--   * UUID v7 ids are time-ordered, so we get a useful clustered insertion
--     order without needing a separate created_at index on the hot path.
--   * jobs_ready_idx is a *partial* index over (run_at) restricted to the
--     "ready" statuses. It is the index the SKIP LOCKED dequeue scan walks.
--     A full index over status would be wider and colder.
--   * idempotency_key has a partial UNIQUE index so multiple NULLs are
--     allowed (without the WHERE clause, Postgres would treat NULLs as
--     distinct under UNIQUE -- valid but unnecessarily permissive about
--     constraint shape).

DO $$
BEGIN
    CREATE TYPE job_status AS ENUM (
        'queued',
        'running',
        'succeeded',
        'retrying',
        'failed_permanent',
        'cancelled'
    );
EXCEPTION
    WHEN duplicate_object THEN NULL;
END
$$;

DO $$
BEGIN
    CREATE TYPE job_kind AS ENUM (
        'send_email',
        'resize_image',
        'summarize_text',
        'webhook_delivery'
    );
EXCEPTION
    WHEN duplicate_object THEN NULL;
END
$$;

CREATE TABLE IF NOT EXISTS jobs (
    id               UUID         PRIMARY KEY,
    kind             job_kind     NOT NULL,
    payload          JSONB        NOT NULL,
    status           job_status   NOT NULL DEFAULT 'queued',
    attempts         INTEGER      NOT NULL DEFAULT 0,
    max_attempts     INTEGER      NOT NULL DEFAULT 3,
    last_error       TEXT,
    run_at           TIMESTAMPTZ  NOT NULL DEFAULT now(),
    locked_at        TIMESTAMPTZ,
    locked_by        TEXT,
    cancel_requested BOOLEAN      NOT NULL DEFAULT FALSE,
    idempotency_key  TEXT,
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    CONSTRAINT jobs_attempts_nonneg          CHECK (attempts >= 0),
    CONSTRAINT jobs_max_attempts_positive    CHECK (max_attempts >= 1),
    CONSTRAINT jobs_attempts_le_max          CHECK (attempts <= max_attempts)
);

-- The dequeue index: walked by SELECT ... FOR UPDATE SKIP LOCKED.
CREATE INDEX IF NOT EXISTS jobs_ready_idx
    ON jobs (run_at)
    WHERE status IN ('queued', 'retrying');

-- Idempotency: partial unique so NULL keys do not collide.
CREATE UNIQUE INDEX IF NOT EXISTS jobs_idempotency_key_uidx
    ON jobs (idempotency_key)
    WHERE idempotency_key IS NOT NULL;

-- Lookup support for status-filtered list endpoints.
CREATE INDEX IF NOT EXISTS jobs_status_idx ON jobs (status);

-- Reverse-chronological listing on GET /jobs.
CREATE INDEX IF NOT EXISTS jobs_created_at_idx ON jobs (created_at DESC);
