"""One-time cleanup script.

Tasks:
  1. Delete question_bank rows referencing nonexistent passages / images.
  2. Delete every quiz (and all cascaded data: student_results, reports,
     raw_files, parsed_data, answer_grids) EXCEPT the lowest-id quiz.

Run:
    cd chatkit/backend
    python cleanup.py           # dry-run (shows what would be deleted)
    python cleanup.py --confirm  # actually deletes
"""

from __future__ import annotations

import asyncio
import re
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent))

from sqlalchemy import delete, select, text

from app.db import get_sessionmaker
from app.models import QuestionBank, Quiz, Report, RawFile, StudentResult

# Same skip pattern as seed_from_table.py
_SKIP_RE = re.compile(
    r"look at the picture"
    r"|in the picture"
    r"|the picture shows"
    r"|the following picture"
    r"|according to the (?:passage|article|reading|text|graph|chart|table)"
    r"|based on the (?:passage|article|reading|text)"
    r"|the (?:passage|article|reading) (?:above|below|following)"
    r"|the following (?:passage|article|reading)",
    re.I,
)

DRY_RUN = "--confirm" not in sys.argv


async def main() -> None:
    if DRY_RUN:
        print("DRY RUN — pass --confirm to actually delete\n")

    async with get_sessionmaker()() as db:
        # ── 1. Question bank: find passage/image questions ─────────────────────
        res = await db.execute(select(QuestionBank.id, QuestionBank.question_text))
        rows = res.all()
        bad_ids = [r.id for r in rows if _SKIP_RE.search(r.question_text or "")]

        print(f"Question bank: {len(rows)} total, {len(bad_ids)} reference passages/images")
        if bad_ids:
            print("  Samples:")
            for qid in bad_ids[:5]:
                text_sample = next(r.question_text for r in rows if r.id == qid)
                print(f"    [{qid}] {text_sample[:80]}…")

        if bad_ids and not DRY_RUN:
            await db.execute(delete(QuestionBank).where(QuestionBank.id.in_(bad_ids)))
            print(f"  → Deleted {len(bad_ids)} question bank rows")

        # ── 2. Quizzes: keep only the lowest-id quiz ───────────────────────────
        res = await db.execute(
            select(Quiz.id, Quiz.title, Quiz.created_at).order_by(Quiz.id)
        )
        quizzes = res.all()

        if not quizzes:
            print("\nNo quizzes found.")
        else:
            keep = quizzes[0]
            to_delete = quizzes[1:]
            print(f"\nQuizzes: {len(quizzes)} total")
            print(f"  KEEP  [{keep.id}] {keep.title} ({keep.created_at})")
            for q in to_delete:
                print(f"  DEL   [{q.id}] {q.title} ({q.created_at})")

            if to_delete and not DRY_RUN:
                del_ids = [q.id for q in to_delete]

                # Count what will cascade-delete
                r_count = (await db.execute(
                    select(Report.id).where(Report.quiz_id.in_(del_ids))
                )).fetchall()
                sr_count = (await db.execute(
                    select(StudentResult.id).where(StudentResult.quiz_id.in_(del_ids))
                )).fetchall()
                rf_count = (await db.execute(
                    select(RawFile.id).where(RawFile.quiz_id.in_(del_ids))
                )).fetchall()

                await db.execute(delete(Quiz).where(Quiz.id.in_(del_ids)))
                print(
                    f"  → Deleted {len(to_delete)} quizzes "
                    f"(+{len(r_count)} reports, {len(sr_count)} results, {len(rf_count)} files)"
                )

        if not DRY_RUN:
            await db.commit()
            print("\nDone.")
        else:
            print("\nDry run complete — nothing deleted.")


asyncio.run(main())