-- 231_study_question_jobs.sql
-- Phase 4-A: study question 도메인 전용 비동기 작업 큐.
-- processing_queue 가 documents.id FK 라 study_questions 에 직접 재사용 불가.
--
-- 라이프사이클:
--   pending → processing → completed | failed | skipped
-- terminal status (completed/failed/skipped) 는 completed_at 항상 기록.
-- failed 재시도는 기존 row 를 pending 으로 되살리지 않고 새 row 생성 — 이력 누적.

CREATE TABLE study_question_jobs (
    id                  BIGSERIAL    PRIMARY KEY,
    study_question_id   BIGINT       NOT NULL REFERENCES study_questions(id) ON DELETE CASCADE,
    user_id             BIGINT       NOT NULL REFERENCES users(id)           ON DELETE CASCADE,
    kind                VARCHAR(40)  NOT NULL,
    status              VARCHAR(20)  NOT NULL DEFAULT 'pending',
    attempts            SMALLINT     NOT NULL DEFAULT 0,
    max_attempts        SMALLINT     NOT NULL DEFAULT 2,
    error_code          VARCHAR(40),
    error_message       TEXT,
    payload             JSONB,
    created_at          TIMESTAMPTZ  NOT NULL DEFAULT now(),
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ
);