bank-statement-api / auth /seed_from_flat.py
3v324v23's picture
Merge Conflict solved | Build error solved
205f25f
Raw History Blame Contribute Delete
6.51 kB
# 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()