"""Personal question libraries.

Revision ID: n7a8b9c0d142
Revises: m6a7b8c9d031
"""
from alembic import op

revision = "n7a8b9c0d142"
down_revision = "m6a7b8c9d031"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS user_collections (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        title VARCHAR(200) NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )""")
    op.execute("""
    CREATE TABLE IF NOT EXISTS user_collection_questions (
        id SERIAL PRIMARY KEY,
        collection_id INTEGER NOT NULL REFERENCES user_collections(id) ON DELETE CASCADE,
        question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
        CONSTRAINT uq_collection_question UNIQUE (collection_id, question_id)
    )""")


def downgrade():
    op.execute("DROP TABLE IF EXISTS user_collection_questions")
    op.execute("DROP TABLE IF EXISTS user_collections")