"""Remove the LMS

Courses, modules, lessons, enrolments, per-lesson progress, SCORM packages,
BigBlueButton sessions and completion certificates. None of it was used — one
draft course called "jk" with two empty lessons — and all of it was in the way:
every query that counted practice had to remember to exclude course attempts,
and every question predicate had to exclude a course's own questions.

`quizzes.allow_review` goes with them. It was only ever enforced for a course
quiz, so once courses are gone it is a promise nothing keeps — the public quiz
page was still saying "no answer review" about sessions that reviewed fine.

The tables are dropped rather than left orphaned: an unused table with foreign
keys into live ones is a trap for the next person reading the schema. Files the
LMS uploaded stay on disk and stay protected — see LEGACY_LMS_PREFIXES in
app/utils/upload_access.py.

Revision ID: n4e5f6a7b8c9
Revises: m3d4e5f6a7b8
"""
import sqlalchemy as sa
from alembic import op

revision = "n4e5f6a7b8c9"
down_revision = "m3d4e5f6a7b8"
branch_labels = None
depends_on = None

# Children first: each drop removes the foreign keys pointing at the next.
TABLES = ("course_lesson_progress", "course_lessons", "course_enrollments",
          "course_modules", "courses")


def upgrade() -> None:
    inspector = sa.inspect(op.get_bind())
    columns = {c["name"] for c in inspector.get_columns("quizzes")}
    # Dropped before the tables, because it is the foreign key into them.
    if "course_id" in columns:
        op.drop_column("quizzes", "course_id")
    if "allow_review" in columns:
        op.drop_column("quizzes", "allow_review")
    existing = set(inspector.get_table_names())
    for table in TABLES:
        if table in existing:
            op.drop_table(table)


def downgrade() -> None:
    # The schema can come back; the LMS cannot, and neither can the rows.
    op.create_table(
        "courses",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("thumbnail_path", sa.String()),
        sa.Column("user_id", sa.Integer(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("status", sa.String(), server_default="draft"),
        sa.Column("is_featured", sa.Integer(), server_default="0"),
        sa.Column("requires_subscription", sa.Integer(), server_default="0"),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(), server_default=sa.func.now()),
    )
    op.create_table(
        "course_modules",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("course_id", sa.Integer(), sa.ForeignKey("courses.id", ondelete="CASCADE"), nullable=False),
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("position", sa.Integer(), server_default="0"),
    )
    op.create_table(
        "course_lessons",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("module_id", sa.Integer(), sa.ForeignKey("course_modules.id", ondelete="CASCADE"), nullable=False),
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("position", sa.Integer(), server_default="0"),
        sa.Column("lesson_type", sa.String(), nullable=False),
        sa.Column("content_text", sa.Text()),
        sa.Column("video_url", sa.String()),
        sa.Column("video_provider", sa.String()),
        sa.Column("local_file_path", sa.String()),
        sa.Column("quiz_id", sa.Integer(), sa.ForeignKey("quizzes.id", ondelete="SET NULL")),
        sa.Column("live_session_url", sa.String()),
        sa.Column("live_session_start", sa.DateTime()),
        sa.Column("live_session_end", sa.DateTime()),
        sa.Column("bbb_meeting_id", sa.String()),
        sa.Column("duration_minutes", sa.Integer()),
        sa.Column("is_required", sa.Integer(), server_default="1"),
    )
    op.create_table(
        "course_enrollments",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("course_id", sa.Integer(), sa.ForeignKey("courses.id", ondelete="CASCADE"), nullable=False),
        sa.Column("user_id", sa.Integer(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("enrolled_at", sa.DateTime(), server_default=sa.func.now()),
        sa.Column("completed_at", sa.DateTime()),
        sa.Column("progress_pct", sa.Float(), server_default="0"),
        sa.Column("status", sa.String(), server_default="enrolled"),
        sa.UniqueConstraint("course_id", "user_id"),
    )
    op.create_table(
        "course_lesson_progress",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("enrollment_id", sa.Integer(), sa.ForeignKey("course_enrollments.id", ondelete="CASCADE"), nullable=False),
        sa.Column("lesson_id", sa.Integer(), sa.ForeignKey("course_lessons.id", ondelete="CASCADE"), nullable=False),
        sa.Column("status", sa.String(), server_default="not_started"),
        sa.Column("started_at", sa.DateTime()),
        sa.Column("completed_at", sa.DateTime()),
        sa.Column("score", sa.Float()),
        sa.Column("time_spent_seconds", sa.Integer(), server_default="0"),
        sa.UniqueConstraint("enrollment_id", "lesson_id"),
    )
    op.add_column("quizzes", sa.Column("course_id", sa.Integer(),
                                       sa.ForeignKey("courses.id", ondelete="CASCADE"), nullable=True))
    op.add_column("quizzes", sa.Column("allow_review", sa.Integer(), server_default="1"))