"""Grants can name an exam and a discipline, not only a category.

An admin should be able to say "you edit Step 1 Cardiology" rather than only
"you edit this category". Each dimension is nullable and means "any", so an
existing category-only grant keeps working unchanged.

Revision ID: c1d2e3f4a5b6
Revises: b0c1d2e3f4a5
"""
from alembic import op

revision = "c1d2e3f4a5b6"
down_revision = "b0c1d2e3f4a5"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("ALTER TABLE category_grants ADD COLUMN IF NOT EXISTS exam_id INTEGER REFERENCES exams(id) ON DELETE CASCADE")
    op.execute("ALTER TABLE category_grants ADD COLUMN IF NOT EXISTS tag_id INTEGER REFERENCES question_tags(id) ON DELETE CASCADE")
    op.execute("ALTER TABLE category_grants ALTER COLUMN category_id DROP NOT NULL")
    # A grant naming nothing at all would silently mean "everything".
    op.execute("""
    ALTER TABLE category_grants DROP CONSTRAINT IF EXISTS ck_grant_has_a_dimension
    """)
    op.execute("""
    ALTER TABLE category_grants ADD CONSTRAINT ck_grant_has_a_dimension
    CHECK (category_id IS NOT NULL OR exam_id IS NOT NULL OR tag_id IS NOT NULL)
    """)
    op.execute("ALTER TABLE category_grants DROP CONSTRAINT IF EXISTS uq_category_grant")
    op.execute("""
    CREATE UNIQUE INDEX IF NOT EXISTS uq_grant_dimensions ON category_grants
    (user_id, COALESCE(category_id, 0), COALESCE(exam_id, 0), COALESCE(tag_id, 0))
    """)


def downgrade():
    op.execute("DROP INDEX IF EXISTS uq_grant_dimensions")
    op.execute("ALTER TABLE category_grants DROP CONSTRAINT IF EXISTS ck_grant_has_a_dimension")
    op.execute("DELETE FROM category_grants WHERE category_id IS NULL")
    op.execute("ALTER TABLE category_grants ALTER COLUMN category_id SET NOT NULL")
    op.execute("ALTER TABLE category_grants DROP COLUMN IF EXISTS tag_id")
    op.execute("ALTER TABLE category_grants DROP COLUMN IF EXISTS exam_id")