"""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())