"""A staging area for extracted questions.

Extraction wrote straight into `questions`, so a machine's first attempt took a
permanent id the moment it was produced — and ids come from a sequence that
never reissues one, so every rejected draft burned an id and every draft that
needed fixing was in the bank while it was being fixed.

Drafts have their own table and their own sequence now. Nothing reaches the
bank, or takes a question id, until somebody accepts it.

Revision ID: c9d0e1f2a3b4
Revises: b8c9d0e1f2a3
"""
import sqlalchemy as sa
from alembic import op

revision = "c9d0e1f2a3b4"
down_revision = "b8c9d0e1f2a3"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "draft_batches",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column("title", sa.String(200), nullable=False),
        sa.Column("document_id", sa.Integer(), sa.ForeignKey("pdf_documents.id", ondelete="SET NULL"), nullable=True),
        sa.Column("section_id", sa.Integer(), sa.ForeignKey("sections.id", ondelete="SET NULL"), nullable=True),
        sa.Column("job_id", sa.String(64), nullable=True),
        sa.Column("model_id", sa.String(160), nullable=True),
        sa.Column("extraction_mode", sa.String(40), nullable=True),
        sa.Column("category_id", sa.Integer(), sa.ForeignKey("question_categories.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_by", sa.Integer(), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.Column("status", sa.String(20), server_default="open"),
    )
    op.create_index("ix_draft_batches_job_id", "draft_batches", ["job_id"])
    op.create_index("ix_draft_batches_status", "draft_batches", ["status"])

    op.create_table(
        "draft_questions",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column("batch_id", sa.Integer(), sa.ForeignKey("draft_batches.id", ondelete="CASCADE"), nullable=False),
        sa.Column("position", sa.Integer(), server_default="0"),
        sa.Column("question_text", sa.Text(), nullable=False),
        sa.Column("question_type", sa.String(), nullable=False, server_default="mcq"),
        sa.Column("options", sa.JSON(), nullable=True),
        sa.Column("correct_answer", sa.String(), nullable=True),
        sa.Column("explanation", sa.Text(), nullable=True),
        sa.Column("option_explanations", sa.JSON(), nullable=True),
        sa.Column("key_points", sa.JSON(), nullable=True),
        sa.Column("attending_tip", sa.Text(), nullable=True),
        sa.Column("difficulty", sa.String(10), nullable=True),
        sa.Column("page_reference", sa.Integer(), nullable=True),
        sa.Column("image_path", sa.String(), nullable=True),
        sa.Column("explanation_image_path", sa.String(), nullable=True),
        sa.Column("category_id", sa.Integer(), sa.ForeignKey("question_categories.id", ondelete="SET NULL"), nullable=True),
        sa.Column("status", sa.String(20), server_default="pending"),
        sa.Column("question_id", sa.Integer(), sa.ForeignKey("questions.id", ondelete="SET NULL"), nullable=True),
        sa.Column("note", sa.Text(), nullable=True),
        sa.Column("decided_by", sa.Integer(), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("decided_at", sa.DateTime(), nullable=True),
        sa.Column("edited", sa.Integer(), server_default="0"),
        sa.Column("created_at", sa.DateTime(), nullable=True),
    )
    op.create_index("ix_draft_questions_batch_id", "draft_questions", ["batch_id"])
    op.create_index("ix_draft_questions_status", "draft_questions", ["status"])


def downgrade() -> None:
    op.drop_table("draft_questions")
    op.drop_table("draft_batches")