bank-statement-api / scripts /sync_flat_to_rules.py
3v324v23's picture
Feat: Added New column in xlsx
fbb917f
Raw History Blame Contribute Delete
8.2 kB
"""
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)