File size: 15,754 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
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
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