import os import json import glob import logging from pathlib import Path from datetime import datetime from typing import List, Dict, Any, Optional import openpyxl from models.statements import AppSettings logger = logging.getLogger("befree.state") class StatementData: def __init__(self, statement_id: str, file_name: str): self.id = statement_id self.file_name = file_name self.status = "uploaded" self.created_at = datetime.now().isoformat() self.pdf_path = None self.pdf_paths: List[str] = [] self.xlsx_path = None self.transactions: List[Dict[str, Any]] = [] self.accuracy = None self.bank_name = None # coordinate-locked layout additions self.layout_detection_result = None self.verified_columns = None self.header_page = None self.source_file_names: list[str] = [] def _uploads_dir() -> Path: """Return the directory where uploaded PDFs are stored.""" return Path(os.environ.get("UPLOADS_DIR", "data/uploads")) def _output_dir() -> Path: """Return the directory where generated outputs (xlsx/csv) are stored.""" return Path(os.environ.get("OUTPUT_DIR", "data/output")) def ensure_statement_loaded(statement_id: str) -> bool: """Helper to restore a statement's memory state from the Excel output file if not loaded.""" if dict.__contains__(statements, statement_id): # Already fully loaded in memory return True # Search for matching excel file in data/output xlsx_pattern = str((_output_dir() / "*.xlsx")) matched_file = None for filepath in glob.glob(xlsx_pattern): filename = os.path.basename(filepath) # Check if this file name starts with or matches statement_id if filename.startswith(statement_id): matched_file = filepath break if not matched_file: return False # File found! Let's reconstruct StatementData filename = os.path.basename(matched_file) orig_file_name = filename stmt = StatementData(statement_id=statement_id, file_name=orig_file_name) stmt.status = "completed" stmt.xlsx_path = matched_file # Try to find corresponding PDF in data/uploads pdf_pattern = str((_uploads_dir() / f"*{statement_id}*")) pdf_matches = glob.glob(pdf_pattern) if pdf_matches: # Filter for PDF files to avoid matching excel checkpoints or logs pdf_files = [p for p in pdf_matches if p.lower().endswith(".pdf")] if pdf_files: stmt.pdf_paths = sorted(pdf_files) stmt.pdf_path = stmt.pdf_paths[0] try: wb = openpyxl.load_workbook(matched_file, data_only=True) if "_metadata" in wb.sheetnames: ws_meta = wb["_metadata"] for row in ws_meta.iter_rows(values_only=True): if len(row) >= 2 and row[0] == "extract_last_3_months": val_str = str(row[1]).strip().lower() if val_str == "true": stmt.extract_last_3_months = True elif val_str == "false": stmt.extract_last_3_months = False if "Transactions" in wb.sheetnames: ws = wb["Transactions"] headers = [cell.value for cell in ws[1]] if headers: col_map = {str(h).lower().strip(): i for i, h in enumerate(headers) if h} transactions = [] for row_idx in range(2, ws.max_row + 1): row = [cell.value for cell in ws[row_idx]] if not any(row): continue def _get(col_name): idx = col_map.get(col_name) if idx is not None and idx < len(row): return row[idx] return None tx_id = _get("tx_id") if tx_id: # Parse file_index from suffix in tx_id tx_id_str = str(tx_id) parts = tx_id_str.split("_") file_index = 0 if len(parts) >= 4: try: file_index = int(parts[-1]) except ValueError: pass page_num = _get("page_num") or 0 try: page_num = int(page_num) except (ValueError, TypeError): page_num = 0 # Dates date_val = _get("date") if date_val and hasattr(date_val, "isoformat"): date_val = date_val.isoformat() else: date_val = str(date_val or "") desc = _get("description") or _get("desc_raw") or "" # Confidence conf = _get("confidence") try: confidence = float(conf) if conf is not None else 1.0 except ValueError: confidence = 1.0 # Validation errors / flags audit_flags = _get("auditflags") or _get("audit_flags") or _get("issues") if audit_flags: if isinstance(audit_flags, str): if audit_flags.startswith("[") and audit_flags.endswith("]"): try: audit_flags = json.loads(audit_flags) except Exception: audit_flags = [f.strip() for f in audit_flags[1:-1].replace("'", "").replace('"', '').split(",") if f.strip()] else: audit_flags = [f.strip() for f in audit_flags.split(",") if f.strip()] elif not isinstance(audit_flags, list): audit_flags = [str(audit_flags)] audit_flags = [f for f in audit_flags if f] if not audit_flags: audit_flags = None else: audit_flags = None # Map clean/error/flagged/corrected status status = _get("status") or _get("audit_status") or "valid" status = str(status).lower() if status in ("clean", "valid"): status = "valid" elif status in ("error", "warning", "flagged"): status = "flagged" elif status in ("corrected", "auto-corrected"): status = "corrected" elif status in ("verified", "manual"): status = "verified" elif status in ("deleted",): status = "deleted" else: status = "valid" is_auto_swapped = False if audit_flags: is_auto_swapped = any("debit/credit swapped" in str(f).lower() for f in audit_flags) if is_auto_swapped: status = "valid" audit_flags = None # Derive debit and credit from amount amount = _get("amount") debit = None credit = None if amount is not None: try: amt_f = float(str(amount).replace(",", "")) if is_auto_swapped: amt_f = -amt_f if amt_f < 0: debit = abs(amt_f) else: credit = amt_f except ValueError: pass balance = None extr_bal = _get("calculatedbalance") or _get("calculated_balance") or _get("stated_balance") or _get("balance_after") try: calculated_balance = float(extr_bal) if extr_bal is not None else None except ValueError: calculated_balance = None category = _get("category") third_party = _get("third_party") category_confidence = _get("categoryconfidence") or _get("category_confidence") category_source = _get("categorysource") or _get("category_source") source_extractor = _get("extractor_used") or _get("source_extractor") or "coordinate_locked" tx = { "id": tx_id, "pageNumber": page_num + 1, "date": date_val, "description": desc, "debit": debit, "credit": credit, "balance": balance, "calculatedBalance": calculated_balance, "confidence": confidence, "status": status, "suggestedCorrections": None, "category": category, "third_party": third_party, "categoryConfidence": category_confidence, "categorySource": category_source, "auditFlags": audit_flags, "source_extractor": source_extractor, "file_index": file_index, } transactions.append(tx) stmt.transactions = transactions if not getattr(stmt, "source_file_names", []): stmt.source_file_names = [ os.path.basename(p) for p in getattr(stmt, "pdf_paths", []) ] wb.close() except Exception as e: logger.exception(f"Failed to load transactions for statement {statement_id}: {e}") return False super(StatementDict, statements).__setitem__(statement_id, stmt) logger.info(f"Dynamically recovered statement {statement_id} from {matched_file}") try: from core.helpers import _heal_negated_cr_balances, update_statement_confidence from core.statement_io import auto_swap_all_transactions, _persist_swap_to_xlsx healed = _heal_negated_cr_balances(statement_id) if healed: logger.info(f"[HealCRBalances] Statement {statement_id}: healed {healed} negated balance(s)") swapped = auto_swap_all_transactions(statement_id) if swapped or healed: logger.info(f"[AutoSwapAll] Statement {statement_id}: auto-swapped {swapped} row(s) on DB load") _persist_swap_to_xlsx(statement_id) update_statement_confidence(statement_id) except Exception as e: logger.error(f"Error calculating confidence on dynamic load: {e}") return True def discover_and_load_statements(): """Scan data/output folder for generated excel statements to restore their in-memory models.""" out_dir = os.path.join("data", "output") if not os.path.exists(out_dir): return xlsx_files = glob.glob(os.path.join(out_dir, "*.xlsx")) for filepath in xlsx_files: filename = os.path.basename(filepath) if "checkpoint" in filename or filename.startswith("~$"): continue stmt_id = filename.replace(".xlsx", "") if not dict.__contains__(statements, stmt_id): ensure_statement_loaded(stmt_id) class StatementDict(dict): """Custom dict that dynamically loads statements from excel files on access.""" def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) def __getitem__(self, key): if not super().__contains__(key): ensure_statement_loaded(key) return super().__getitem__(key) def __contains__(self, key): if super().__contains__(key): return True return ensure_statement_loaded(key) def get(self, key, default=None): if not super().__contains__(key): ensure_statement_loaded(key) return super().get(key, default) def keys(self): discover_and_load_statements() return super().keys() def values(self): discover_and_load_statements() return super().values() def items(self): discover_and_load_statements() return super().items() statements = StatementDict() # ── Settings Persistence ───────────────────────────────────────────────────── SETTINGS_FILE = Path("data") / "settings.json" def _load_settings() -> AppSettings: """Load settings from file, or return defaults if file doesn't exist.""" if SETTINGS_FILE.exists(): try: with open(SETTINGS_FILE, "r") as f: data = json.load(f) return AppSettings(**data) except Exception as e: logger.warning(f"Failed to load settings from {SETTINGS_FILE}: {e}. Using defaults.") return AppSettings() def _save_settings(s: AppSettings) -> None: """Persist settings to file.""" try: SETTINGS_FILE.parent.mkdir(parents=True, exist_ok=True) with open(SETTINGS_FILE, "w") as f: json.dump(s.dict(), f, indent=2) except Exception as e: logger.error(f"Failed to save settings to {SETTINGS_FILE}: {e}") settings = _load_settings()