"""add classification snapshots

Revision ID: 5f8c1c2a9d40
Revises: b71a4e2d6c90
Create Date: 2026-06-12 03:30:00.000000
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = '5f8c1c2a9d40'
down_revision: Union[str, None] = 'b71a4e2d6c90'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS question_classification_snapshots (
            id SERIAL PRIMARY KEY,
            job_id VARCHAR(64),
            created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
            reason VARCHAR(120) NOT NULL DEFAULT 'before_ai_classification',
            question_count INTEGER NOT NULL DEFAULT 0,
            link_count INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT NOW()
        )
    """))
    conn.execute(sa.text("""
        CREATE INDEX IF NOT EXISTS ix_question_classification_snapshots_created_at
        ON question_classification_snapshots (created_at DESC)
    """))
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS question_classification_snapshot_links (
            snapshot_id INTEGER NOT NULL REFERENCES question_classification_snapshots(id) ON DELETE CASCADE,
            question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
            tag_name VARCHAR(200) NOT NULL,
            tag_type VARCHAR(50) NOT NULL,
            PRIMARY KEY (snapshot_id, question_id, tag_name, tag_type)
        )
    """))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP TABLE IF EXISTS question_classification_snapshot_links"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_question_classification_snapshots_created_at"))
    conn.execute(sa.text("DROP TABLE IF EXISTS question_classification_snapshots"))