# auth/seed_from_flat.py import sys import os sys.path.insert(0, os.path.dirname(os.path.dirname(__file__))) from dotenv import load_dotenv load_dotenv() from sqlalchemy.orm import Session from auth.database import SessionLocal from auth.rule_models import ( ClassificationFlat, ClassificationGroup, ClassificationCategory, ClassificationRule, ) import uuid from collections import defaultdict def _unique_preserve_order(items: list[str]) -> list[str]: seen: set[str] = set() unique: list[str] = [] for item in items: normalized = item.strip().lower() if normalized not in seen: seen.add(normalized) unique.append(item) return unique def seed_from_flat(): db: Session = SessionLocal() try: # ── Step A: Deactivate all existing records (non-destructive rollback path) db.query(ClassificationRule).update({"is_active": False}) db.query(ClassificationCategory).update({"is_active": False}) db.query(ClassificationGroup).update({"is_active": False}) db.commit() print("[OK] Deactivated all old groups, categories, rules") # ── Step B: Load all active rows from classification_flat flat_rows = db.query(ClassificationFlat).filter_by(is_active=True).all() print(f"[OK] Loaded {len(flat_rows)} rows from classification_flat") # ── Step C: Build group → category → keywords index groups = {} category_map = defaultdict(list) category_metadata = {} for row in flat_rows: cat = row.category.strip() sub = row.sub_category.strip() if row.sub_category else None kw = row.keyword.strip() cat_name = sub if sub else cat if cat not in groups: groups[cat] = None # placeholder category_map[cat_name].append(kw) if cat_name not in category_metadata: category_metadata[cat_name] = { "group_name": cat, "sheet_name": cat, "sub_column": sub if sub else cat, } # Deduplicate keywords inside each category group while preserving order. keyword_groups: dict[str, set[str]] = defaultdict(set) for cat_name, keywords in category_map.items(): category_map[cat_name] = _unique_preserve_order(keywords) for kw in category_map[cat_name]: keyword_groups[kw.strip().lower()].add(cat_name) for kw, groups_set in keyword_groups.items(): if len(groups_set) > 1: print(f"[WARN] Keyword '{kw}' appears in multiple categories: {sorted(groups_set)}") # ── Step D: Insert or reactivate groups for cat_name in groups: existing = db.query(ClassificationGroup).filter_by(name=cat_name).first() if existing: existing.is_active = True groups[cat_name] = existing else: g = ClassificationGroup( id=str(uuid.uuid4()), name=cat_name, is_active=True, ) db.add(g) db.flush() groups[cat_name] = g db.commit() print(f"[OK] Upserted {len(groups)} groups") # ── Step E: Insert or reactivate categories + rules category_count = 0 rule_count = 0 # Pre-fetch all categories and rules to avoid N+1 queries in loop all_cats = db.query(ClassificationCategory).all() cats_by_name = {c.name: c for c in all_cats} all_rules = db.query(ClassificationRule).all() rules_by_cat_id = {r.category_id: r for r in all_rules} for cat_name, keywords in category_map.items(): meta = category_metadata[cat_name] sheet_name = meta["sheet_name"] sub_column = meta["sub_column"] group = groups[meta["group_name"]] # Find or create category existing_cat = cats_by_name.get(cat_name) if existing_cat: existing_cat.is_active = True existing_cat.sheet_name = sheet_name existing_cat.sub_column = sub_column existing_cat.group_id = group.id cat_obj = existing_cat else: cat_obj = ClassificationCategory( id=str(uuid.uuid4()), name=cat_name, sheet_name=sheet_name, sub_column=sub_column, group_id=group.id, is_active=True, ) db.add(cat_obj) db.flush() cats_by_name[cat_name] = cat_obj category_count += 1 # Find or create rule for this category existing_rule = rules_by_cat_id.get(cat_obj.id) if existing_rule: existing_rule.is_active = True existing_rule.keywords = _unique_preserve_order(keywords) existing_rule.match_on = "description" else: rule = ClassificationRule( id=str(uuid.uuid4()), name=f"rule_{cat_name.lower().replace(' ', '_').replace('&', 'and').replace('/', '_')}", category_id=cat_obj.id, match_on="description", is_active=True, ) rule.keywords = _unique_preserve_order(keywords) db.add(rule) rules_by_cat_id[cat_obj.id] = rule rule_count += 1 db.commit() print(f"[OK] Upserted {category_count} categories, {rule_count} rules") # ── Step F: Verification query from sqlalchemy import text result = db.execute(text( "SELECT category, COUNT(*) as cnt FROM classification_flat " "WHERE is_active = true GROUP BY category ORDER BY cnt DESC" )).fetchall() print("\n-- Verification: flat table group counts --") for row in result: print(f" {row[0]:45s} {row[1]} keywords") finally: db.close() if __name__ == "__main__": seed_from_flat()