File size: 14,835 Bytes
fbb917f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
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()