pdf-quiz-generator/backend/scripts/merge_duplicate_conditions.py

"""Give each condition one home, and keep its other systems as cross-links.

The tag→category conversion made every question's subject tag a parent, so a
question tagged both Pulmonology and Emergency Medicine produced an "Asthma" row
under each. "Asthma" is therefore not one topic with 45 questions but several
fragments no filter ever adds up — 330 of 618 condition names live in more than
one system, a few in eight.

Merging keeps the row with the most questions and folds the rest into it. The
association with the other systems is not thrown away: each moved question gains
a `question_category_links` row pointing at the system it came from, which is the
mechanism built for "this matters to nephrology *and* emergency medicine".
Duplicate rows are the wrong way to say that; a link is the right one.

Only names appearing in several systems are touched, and only when one row is a
clear plurality — an even split is reported for a human to decide rather than
broken by an arbitrary rule.

    docker compose exec backend python -m scripts.merge_duplicate_conditions
    docker compose exec backend python -m scripts.merge_duplicate_conditions --apply
    docker compose exec backend python -m scripts.merge_duplicate_conditions --min-questions 5
"""
import sys
from collections import defaultdict

from sqlalchemy import text as sa_text

from app.database import SessionLocal


def direct_count(db, category_id: int) -> int:
    return db.execute(sa_text("""
        SELECT (SELECT COUNT(*) FROM questions WHERE question_category_id = :c)
             + (SELECT COUNT(*) FROM question_category_links WHERE category_id = :c)
    """), {"c": category_id}).scalar() or 0


def root_of(db, category_id: int) -> int:
    """The system a row sits under, walking up at most a few levels."""
    cursor, guard = category_id, 0
    while guard < 8:
        parent = db.execute(sa_text(
            "SELECT parent_id FROM question_categories WHERE id = :c"), {"c": cursor}).scalar()
        if parent is None:
            return cursor
        cursor, guard = parent, guard + 1
    return cursor


def main():
    apply_changes = "--apply" in sys.argv
    threshold = 0
    if "--min-questions" in sys.argv:
        threshold = int(sys.argv[sys.argv.index("--min-questions") + 1])

    db = SessionLocal()
    try:
        rows = db.execute(sa_text("""
            SELECT c.id, c.name, c.parent_id FROM question_categories c
            WHERE c.parent_id IS NOT NULL
        """)).fetchall()
        names = dict(db.execute(sa_text("SELECT id, name FROM question_categories")).fetchall())

        by_name: dict[str, list] = defaultdict(list)
        for cid, name, _parent in rows:
            by_name[name.strip().lower()].append(cid)

        plans, ambiguous = [], []
        for key, ids in by_name.items():
            if len(ids) < 2:
                continue
            counts = {cid: direct_count(db, cid) for cid in ids}
            total = sum(counts.values())
            if total < threshold:
                continue
            ranked = sorted(ids, key=lambda cid: (-counts[cid], cid))
            best, runner_up = counts[ranked[0]], counts[ranked[1]]
            entry = (names[ranked[0]], ranked[0], ranked[1:], counts, total)
            # An even split has no evidence-based winner, so leave it to a human.
            if best == runner_up:
                ambiguous.append(entry)
            else:
                plans.append(entry)

        plans.sort(key=lambda e: -e[4])
        ambiguous.sort(key=lambda e: -e[4])

        moved_questions = 0
        if apply_changes:
            for _name, keeper, losers, _counts, _total in plans:
                for loser in losers:
                    system = root_of(db, loser)
                    # Preserve the association with the system being folded away.
                    db.execute(sa_text("""
                        INSERT INTO question_category_links (question_id, category_id)
                        SELECT q.id, :sys FROM questions q
                        WHERE q.question_category_id = :loser
                          AND NOT EXISTS (SELECT 1 FROM question_category_links l
                                          WHERE l.question_id = q.id AND l.category_id = :sys)
                    """), {"sys": system, "loser": loser})
                    db.execute(sa_text(
                        "UPDATE questions SET question_category_id = :k WHERE question_category_id = :l"),
                        {"k": keeper, "l": loser})
                    db.execute(sa_text("""
                        UPDATE question_category_links SET category_id = :k
                        WHERE category_id = :l AND question_id NOT IN (
                            SELECT question_id FROM question_category_links WHERE category_id = :k)
                    """), {"k": keeper, "l": loser})
                    db.execute(sa_text("DELETE FROM question_category_links WHERE category_id = :l"),
                               {"l": loser})
                    db.execute(sa_text("UPDATE articles SET category_id = :k WHERE category_id = :l"),
                               {"k": keeper, "l": loser})
                    db.execute(sa_text("UPDATE flashcard_decks SET category_id = :k WHERE category_id = :l"),
                               {"k": keeper, "l": loser})
                    db.execute(sa_text("UPDATE question_categories SET parent_id = :k WHERE parent_id = :l"),
                               {"k": keeper, "l": loser})
                    db.execute(sa_text("DELETE FROM category_grants WHERE category_id = :l"), {"l": loser})
                    db.execute(sa_text("DELETE FROM question_categories WHERE id = :l"), {"l": loser})
                    moved_questions += 1
            db.commit()

        print("APPLIED" if apply_changes else "DRY RUN")
        print(f"  duplicated names   : {len(plans) + len(ambiguous)}")
        print(f"  merges planned     : {len(plans)}  (rows removed: {sum(len(p[2]) for p in plans)})")
        print(f"  need a human call  : {len(ambiguous)}\n")

        print("  Largest merges (keeper shown first):")
        for name, keeper, losers, counts, total in plans[:25]:
            homes = " + ".join(
                f"{names.get(root_of(db, cid), '?')}:{counts[cid]}" for cid in [keeper, *losers])
            print(f"    {name} — {total} questions across {len(losers) + 1} systems  [{homes}]")

        if ambiguous:
            print("\n  Even splits — pick a home yourself, nothing was merged:")
            for name, keeper, losers, counts, total in ambiguous[:15]:
                homes = ", ".join(
                    f"{names.get(root_of(db, cid), '?')}" for cid in [keeper, *losers])
                print(f"    {name} — {total} questions, tied across {homes}")

        if not apply_changes:
            print("\n  Re-run with --apply to merge, or --min-questions N to limit scope.")
    finally:
        db.close()


if __name__ == "__main__":
    sys.exit(main())