"""Topic articles and card/question associations.

Revision ID: e8d4f1a27c93
Revises: d94a26b8f302
"""
from alembic import op
import sqlalchemy as sa

revision = "e8d4f1a27c93"
down_revision = "d94a26b8f302"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS articles (
        id SERIAL PRIMARY KEY,
        slug VARCHAR(120) NOT NULL UNIQUE,
        title VARCHAR(300) NOT NULL,
        summary TEXT,
        content TEXT,
        sections JSON NOT NULL DEFAULT '[]',
        category_id INTEGER REFERENCES question_categories(id) ON DELETE SET NULL,
        section_id INTEGER REFERENCES sections(id) ON DELETE SET NULL,
        user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'draft',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )""")
    op.execute("CREATE INDEX IF NOT EXISTS ix_articles_id ON articles (id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_articles_slug ON articles (slug)")
    op.execute("""
    CREATE TABLE IF NOT EXISTS question_article_links (
        id SERIAL PRIMARY KEY,
        question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
        article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
        section_id VARCHAR(64),
        user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT uq_question_article_section UNIQUE (question_id, article_id, section_id)
    )""")
    op.execute("CREATE INDEX IF NOT EXISTS ix_question_article_links_id ON question_article_links (id)")
    op.execute("""
    CREATE TABLE IF NOT EXISTS flashcard_question_links (
        id SERIAL PRIMARY KEY,
        flashcard_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,
        question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
        CONSTRAINT uq_card_question UNIQUE (flashcard_id, question_id)
    )""")
    op.execute("CREATE INDEX IF NOT EXISTS ix_flashcard_question_links_id ON flashcard_question_links (id)")
    op.execute("""
    CREATE TABLE IF NOT EXISTS flashcard_article_links (
        id SERIAL PRIMARY KEY,
        flashcard_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,
        article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
        article_section_id VARCHAR(64),
        CONSTRAINT uq_card_article_section UNIQUE (flashcard_id, article_id, article_section_id)
    )""")
    op.execute("CREATE INDEX IF NOT EXISTS ix_flashcard_article_links_id ON flashcard_article_links (id)")


def downgrade():
    op.execute("DROP TABLE IF EXISTS flashcard_article_links")
    op.execute("DROP TABLE IF EXISTS flashcard_question_links")
    op.execute("DROP TABLE IF EXISTS question_article_links")
    op.execute("DROP TABLE IF EXISTS articles")