-- Jobs queue table for async job processing.
-- Used by pkg/queue for producer/consumer patterns.
CREATE TABLE IF NOT EXISTS jobs (
    id UUID PRIMARY KEY,
    job_type VARCHAR(255) NOT NULL,
    payload JSONB NOT NULL DEFAULT '{}',
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    priority INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    retry_count INT NOT NULL DEFAULT 0,
    max_retries INT NOT NULL DEFAULT 3,
    error TEXT,
    worker_id VARCHAR(255)
);

-- Index for efficient dequeue: pending jobs ordered by priority (desc) and age (asc).
-- Partial index only includes pending jobs for efficiency.
CREATE INDEX IF NOT EXISTS idx_jobs_dequeue ON jobs (priority DESC, created_at ASC)
    WHERE status = 'pending';

-- Index for finding stale running jobs that need requeue.
-- Used by RequeueStale to recover from crashed workers.
CREATE INDEX IF NOT EXISTS idx_jobs_stale ON jobs (started_at)
    WHERE status = 'running';

-- Index for listing/filtering jobs by type.
CREATE INDEX IF NOT EXISTS idx_jobs_type ON jobs (job_type, created_at DESC);

-- Index for listing jobs by status (useful for monitoring dashboards).
CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs (status, created_at DESC);