""" scripts/sync_flat_to_rules.py ────────────────────────────── Safely syncs classification_flat → classification_rules. For each category that has rows in classification_flat: - Finds (or creates) the primary "description" rule for that category - Sets its keywords to the full deduplicated set from classification_flat - Reactivates it if it was inactive - Leaves osko_reference / osko_purpose rules untouched Safe to re-run — fully idempotent. Dry-run by default. Pass --commit to apply changes. python scripts/sync_flat_to_rules.py # dry-run python scripts/sync_flat_to_rules.py --commit # apply """ from __future__ import annotations import argparse import sys import uuid from collections import defaultdict from pathlib import Path ROOT = Path(__file__).resolve().parent.parent sys.path.insert(0, str(ROOT)) from dotenv import load_dotenv load_dotenv(ROOT / ".env") from sqlalchemy import text from auth.database import SessionLocal from auth.rule_models import ClassificationCategory, ClassificationRule def _unique_lower_preserve_order(items: list[str]) -> list[str]: seen: set[str] = set() out: list[str] = [] for item in items: norm = item.strip().lower() if norm and norm not in seen: seen.add(norm) out.append(item.strip()) return out def sync(commit: bool = False) -> None: db = SessionLocal() try: print(f"=== sync_flat_to_rules (commit={commit}) ===\n") # ── 1. Load all flat keywords grouped by category name (sub_category) ── flat_rows = db.execute(text(""" SELECT cf.sub_category AS cat_name, cf.keyword FROM classification_flat cf WHERE cf.is_active = true ORDER BY cf.sub_category, cf.keyword """)).fetchall() # Group keywords by sub_category (that's the category name) flat_by_cat: dict[str, list[str]] = defaultdict(list) for cat_name, kw in flat_rows: if cat_name: flat_by_cat[cat_name.strip()].append(kw.strip()) # Also handle rows where sub_category IS NULL — use category column directly null_sub_rows = db.execute(text(""" SELECT cf.category AS cat_name, cf.keyword FROM classification_flat cf WHERE cf.is_active = true AND cf.sub_category IS NULL ORDER BY cf.category, cf.keyword """)).fetchall() for cat_name, kw in null_sub_rows: if cat_name: flat_by_cat[cat_name.strip()].append(kw.strip()) print(f"Loaded flat: {sum(len(v) for v in flat_by_cat.values())} keywords " f"across {len(flat_by_cat)} categories\n") # ── 2. Load all active categories with their IDs ────────────────────── cats = db.query(ClassificationCategory).filter_by(is_active=True).all() cat_by_name_lower: dict[str, ClassificationCategory] = { c.name.lower(): c for c in cats } # ── 3. For each flat category, sync its description rule ────────────── created = updated = reactivated = skipped = 0 for cat_name, keywords in sorted(flat_by_cat.items()): cat_obj = cat_by_name_lower.get(cat_name.lower()) if not cat_obj: print(f" [SKIP] Category not found in DB: {cat_name!r}") skipped += 1 continue deduped_kws = _unique_lower_preserve_order(keywords) # Find existing primary description rule for this category # Prefer: active prefilled > inactive prefilled > any all_rules_for_cat = ( db.query(ClassificationRule) .filter_by(category_id=cat_obj.id, match_on="description") .filter(ClassificationRule.tx_type_filter == None) # noqa: E711 .all() ) # Separate prefilled vs learned prefilled = [r for r in all_rules_for_cat if r.created_by not in ("auto_learned", "rule_learning_migration")] learned = [r for r in all_rules_for_cat if r.created_by in ("auto_learned", "rule_learning_migration")] # Pick best candidate: active prefilled first, then inactive prefilled prefilled_active = [r for r in prefilled if r.is_active] prefilled_inactive = [r for r in prefilled if not r.is_active] if prefilled_active: # Already has an active prefilled rule — merge keywords in rule = prefilled_active[0] existing_kws = rule.keywords merged = _unique_lower_preserve_order(existing_kws + deduped_kws) old_count = len(existing_kws) rule.keywords = merged new_count = len(merged) added = new_count - old_count if added > 0: print(f" [UPDATE] {cat_name:40s} +{added:3d} kws (total {new_count}) rule={rule.name!r}") updated += 1 else: # No new keywords — already in sync pass elif prefilled_inactive: # Reactivate the best inactive prefilled rule with merged keywords rule = prefilled_inactive[0] existing_kws = rule.keywords merged = _unique_lower_preserve_order(existing_kws + deduped_kws) rule.keywords = merged rule.is_active = True rule.created_by = rule.created_by or "system" print(f" [REACTIVATE] {cat_name:40s} {len(merged):3d} kws rule={rule.name!r}") reactivated += 1 else: # No prefilled rule at all — create one safe_name = ( cat_name.lower() .replace(" ", "_") .replace("&", "and") .replace("/", "_") .replace("-", "_") .replace("'", "") .replace(",", "") ) rule_name = f"rule_{safe_name}" rule = ClassificationRule( id=str(uuid.uuid4()), name=rule_name, category_id=cat_obj.id, match_on="description", priority=10, is_active=True, created_by="system", ) rule.keywords = deduped_kws db.add(rule) print(f" [CREATE] {cat_name:40s} {len(deduped_kws):3d} kws rule={rule_name!r}") created += 1 print(f"\nSummary: {created} created | {reactivated} reactivated | {updated} updated | {skipped} skipped") # ── 4. Verify ───────────────────────────────────────────────────────── if commit: db.commit() print("\n✅ Committed to database.") # Quick post-check total_active = db.execute(text( "SELECT COUNT(*) FROM classification_rules WHERE is_active=true" " AND created_by NOT IN ('auto_learned','rule_learning_migration')" " OR (is_active=true AND created_by IS NULL)" )).scalar() print(f" Active prefilled rules now: {total_active}") else: db.rollback() print("\n[DRY RUN] Nothing committed. Re-run with --commit to apply.") except Exception as e: db.rollback() print(f"\n❌ Failed: {e}") raise finally: db.close() if __name__ == "__main__": parser = argparse.ArgumentParser() parser.add_argument("--commit", action="store_true", help="Commit changes to DB") args = parser.parse_args() sync(commit=args.commit)