import io import os import json import logging import re from pathlib import Path from typing import Any, Optional from decimal import Decimal from datetime import date, datetime import openpyxl from core.state import statements, _output_dir, _uploads_dir from core.logging_utils import _swap_logger from core.helpers import _tx_sort_key, _sort_dicts_chronologically, _heal_negated_cr_balances logger = logging.getLogger("befree.statement_io") try: from bank_enrichment import enrich_transactions, confidence_summary _bank_enrichment_available = True except ImportError: _bank_enrichment_available = False def enrich_transactions(txs, *a, **kw): return txs def confidence_summary(*a, **kw): return {} def _persist_swap_to_xlsx(statement_id: str) -> None: """Write the current in-memory transaction data back to the persisted XLSX.""" if statement_id not in statements: return stmt = statements[statement_id] xlsx_path = getattr(stmt, "xlsx_path", None) if not xlsx_path or not os.path.exists(xlsx_path): return try: from shared.models import PageAudit, StatementAudit, NormTransaction from shared.constants import TxType from bank_extractor.writer import write_excel import json pages_dict = {} for tx in stmt.transactions: pages_dict.setdefault(tx.get("pageNumber", 1), []).append(tx) pages_audit = [] for p_num, p_txs in sorted(pages_dict.items()): p_txs_sorted = sorted(p_txs, key=_tx_sort_key) closing = 0.0 for tx in p_txs_sorted: if tx.get("calculatedBalance") is not None: closing = float(tx.get("calculatedBalance")) break opening = closing - sum((float(tx.get("credit") or 0.0) - float(tx.get("debit") or 0.0)) for tx in p_txs) delta = 0.0 page_flagged = sum(1 for tx in p_txs if tx.get("auditFlags") or tx.get("status") == "flagged") if page_flagged > 0: for tx in p_txs_sorted: flags = tx.get("auditFlags") or [] if isinstance(flags, str): try: flags = json.loads(flags) except Exception: flags = [flags] for f in flags: if "balance_mismatch" in f or "Balance mismatch" in f: m = re.search(r"delta=([\d\.]+)", f) if m: try: delta = max(delta, float(m.group(1))) except Exception: delta = 1.0 else: delta = 1.0 if delta == 0.0: delta = 1.0 pages_audit.append(PageAudit( page_num=p_num, opening_balance=Decimal(str(opening)), closing_balance=Decimal(str(closing)), computed_balance=Decimal(str(closing)), delta=Decimal(str(delta)), status="clean" if page_flagged == 0 else "error", issues=[] )) aud_rep = StatementAudit( statement_id=statement_id, bank_id=stmt.bank_name or "generic", pages=pages_audit, extract_last_3_months=getattr(stmt, "extract_last_3_months", None) ) norm_txs_for_excel = [] for tx in stmt.transactions: debit = tx.get("debit") credit = tx.get("credit") amount = (credit or 0.0) - (debit or 0.0) page_num_val = tx.get("pageNumber", 1) - 1 tx_id_val = tx.get("id", "") parts = tx_id_val.split("_") row_num_val = 999 if len(parts) >= 3: try: row_num_val = int(parts[2]) except ValueError: pass date_val = None d_raw = tx.get("date") if d_raw: if isinstance(d_raw, date): date_val = d_raw elif isinstance(d_raw, datetime): date_val = d_raw.date() else: try: date_val = datetime.fromisoformat(str(d_raw).split("T")[0]).date() except Exception: pass raw_flags = tx.get("auditFlags") or [] if isinstance(raw_flags, str): try: raw_flags = json.loads(raw_flags) except Exception: raw_flags = [raw_flags] cat_val = tx.get("category") or "" third_party_val = tx.get("thirdParty") or tx.get("third_party") or "" confidence_val = tx.get("categoryConfidence") or tx.get("category_confidence") or 1.0 source_val = tx.get("categorySource") or tx.get("category_source") or "none" norm = NormTransaction( tx_id=tx_id_val, page_num=page_num_val, row_num=row_num_val, date=date_val, description=tx.get("description", ""), desc_raw=tx.get("description", ""), amount=Decimal(str(amount)), tx_type=TxType.DEBIT if amount < 0 else TxType.CREDIT, balance_after=Decimal(str(tx.get("calculatedBalance"))) if tx.get("calculatedBalance") is not None else None, confidence=tx.get("confidence", 1.0), audit_flags=raw_flags, source_extractor=tx.get("source_extractor", "coordinate_locked") ) setattr(norm, "category", cat_val) setattr(norm, "third_party", third_party_val) setattr(norm, "category_confidence", confidence_val) setattr(norm, "category_source", source_val) norm_txs_for_excel.append(norm) write_excel( norm_txs_for_excel, aud_rep, xlsx_path, statement_id, "", "coordinate_locked", stmt.bank_name or "generic", 0.0 ) logger.info(f"[PersistSwap] Successfully regenerated and wrote full premium XLSX for {statement_id}") except Exception as e: logger.error(f"[PersistSwap] Failed to regenerate XLSX for {statement_id}: {e}", exc_info=True) def _try_auto_swap( debit: Optional[float], credit: Optional[float], balance_before: float, stated_balance: float, tolerance: float = 0.01, tx_id: str = "", statement_id: Optional[str] = None, ) -> tuple[Optional[float], Optional[float], float, bool]: """Try swapping debit and credit. If swap matches stated balance, return swapped values.""" from decimal import Decimal def calc_balance(db, cr, prev): result = prev if db is not None and float(db) > 0: result -= float(db) if cr is not None and float(cr) > 0: result += float(cr) return round(result, 2) original_calc = calc_balance(debit, credit, balance_before) trial_calc = calc_balance(credit, debit, balance_before) # swapped original_diff = abs(Decimal(str(original_calc)) - Decimal(str(stated_balance))) trial_diff = abs(Decimal(str(trial_calc)) - Decimal(str(stated_balance))) will_swap = trial_diff <= Decimal(str(tolerance)) and trial_diff < original_diff _swap_logger.debug( f" [_try_auto_swap] tx={tx_id} | " f"debit={debit} credit={credit} | " f"bal_before={balance_before} stated={stated_balance} | " f"original_calc={original_calc} (diff={original_diff}) | " f"trial_calc={trial_calc} (diff={trial_diff}) | " f"SWAP={will_swap}", statement_id=statement_id ) if will_swap: return credit, debit, trial_calc, True else: return debit, credit, original_calc, False def auto_swap_all_transactions(statement_id: str) -> int: """Walk every transaction and auto-swap debit/credit if it fixes balance mismatch.""" if statement_id not in statements: return 0 stmt = statements[statement_id] transactions = stmt.transactions if not transactions: return 0 sorted_txs = _sort_dicts_chronologically(transactions) _swap_logger.debug(f"\n{'='*80}", statement_id=statement_id) _swap_logger.debug(f"[auto_swap_all] START for {statement_id}, {len(sorted_txs)} transactions", statement_id=statement_id) _swap_logger.debug(f"{'='*80}", statement_id=statement_id) running_balance: float | None = None swapped_count = 0 prev_file_idx: int | None = None for idx, tx in enumerate(sorted_txs): tx_id = tx.get("id", "?") desc = (tx.get("description") or "")[:40] status = tx.get("status") debit = tx.get("debit") credit = tx.get("credit") stated = tx.get("calculatedBalance") bal = tx.get("balance") current_file_idx = tx.get("file_index", 0) if prev_file_idx is not None and current_file_idx != prev_file_idx: _swap_logger.debug( f"[auto_swap_all] File index boundary detected ({prev_file_idx} -> {current_file_idx}). " f"Resetting running_balance (previous: {running_balance}) to None.", statement_id=statement_id ) running_balance = None prev_file_idx = current_file_idx _swap_logger.debug( f"[Row {idx}] tx={tx_id} | page={tx.get('pageNumber')} date={tx.get('date')} | " f"desc=\"{desc}\" | debit={debit} credit={credit} | " f"balance={bal} stated={stated} | status={status} | running_bal={running_balance}", statement_id=statement_id ) if status in ["verified", "corrected"] or tx.get("balanceOverride") is not None: if tx.get("balanceOverride") is not None: running_balance = float(tx["balanceOverride"]) tx["balance"] = running_balance _swap_logger.debug(f" → SKIP (override), running_bal={running_balance}", statement_id=statement_id) elif tx.get("balance") is not None: running_balance = float(tx["balance"]) _swap_logger.debug(f" → SKIP ({status}), running_bal={running_balance}", statement_id=statement_id) else: if running_balance is not None: d = tx.get("debit") c = tx.get("credit") if d is not None and float(d) > 0: running_balance -= float(d) if c is not None and float(c) > 0: running_balance += float(c) running_balance = round(running_balance, 2) tx["balance"] = running_balance _swap_logger.debug(f" → SKIP ({status}, no bal), calc running_bal={running_balance}", statement_id=statement_id) continue if running_balance is None: if tx.get("balance") is not None: running_balance = float(tx["balance"]) _swap_logger.debug(f" → ANCHOR from balance={running_balance}", statement_id=statement_id) elif stated is not None: running_balance = float(stated) tx["balance"] = running_balance _swap_logger.debug(f" → ANCHOR from stated={running_balance}", statement_id=statement_id) else: _swap_logger.debug(f" → NO ANCHOR, skip", statement_id=statement_id) continue prev_balance = running_balance expected = prev_balance if debit is not None and float(debit) > 0: expected -= float(debit) if credit is not None and float(credit) > 0: expected += float(credit) expected = round(expected, 2) _swap_logger.debug( f" calc: {prev_balance} + d={debit} c={credit} → expected={expected} vs stated={stated}", statement_id=statement_id ) stated_is_unreliable = False if stated is not None: s = float(stated) cr_f = float(credit) if credit is not None else None db_f = float(debit) if debit is not None else None if (cr_f is not None and cr_f > 0 and abs(s - cr_f) < 0.01 and abs(s - expected) > 1.0): stated_is_unreliable = True _swap_logger.debug( f" ⚠ UNRELIABLE stated={stated} == credit={credit} — " f"treating as missing (using computed balance)", statement_id=statement_id ) elif (db_f is not None and db_f > 0 and abs(s - db_f) < 0.01 and abs(s - expected) > 1.0): stated_is_unreliable = True _swap_logger.debug( f" ⚠ UNRELIABLE stated={stated} == debit={debit} — " f"treating as missing (using computed balance)", statement_id=statement_id ) if stated_is_unreliable: tx["balance"] = round(expected, 2) tx["calculatedBalance"] = round(expected, 2) running_balance = expected _swap_logger.debug(f" → FIXED unreliable stated. balance={expected}, running_bal={running_balance}", statement_id=statement_id) elif stated is not None and abs(float(stated) - expected) > 0.01: _swap_logger.debug(f" MISMATCH: |{stated} - {expected}| = {abs(float(stated) - expected):.4f} > 0.01 → trying swap", statement_id=statement_id) final_debit, final_credit, final_running_balance, was_swapped = _try_auto_swap( debit=debit, credit=credit, balance_before=prev_balance, stated_balance=float(stated), tolerance=0.01, tx_id=tx_id, statement_id=statement_id, ) if was_swapped: tx["debit"] = final_debit tx["credit"] = final_credit tx["balance"] = round(final_running_balance, 2) tx["calculatedBalance"] = round(final_running_balance, 2) tx["status"] = "corrected" tx["auditFlags"] = ["Auto-corrected: debit/credit swapped"] running_balance = tx["balance"] swapped_count += 1 _swap_logger.debug( f" → SWAPPED! debit={final_debit} credit={final_credit} " f"balance={tx['balance']} running_bal={running_balance}", statement_id=statement_id ) continue tx["balance"] = round(expected, 2) running_balance = float(stated) _swap_logger.debug(f" → NO SWAP (didn't help). Set balance={tx['balance']}, trust stated running_bal={running_balance}", statement_id=statement_id) else: tx["balance"] = expected running_balance = expected _swap_logger.debug(f" → OK. balance={expected}, running_bal={running_balance}", statement_id=statement_id) stmt.transactions = sorted(sorted_txs, key=_tx_sort_key) _swap_logger.debug(f"[auto_swap_all] DONE: swapped {swapped_count} rows\n", statement_id=statement_id) return swapped_count