File size: 63,347 Bytes
66162e0
 
 
 
 
 
39c3242
66162e0
39c3242
 
 
 
66162e0
 
 
 
 
 
 
 
 
39c3242
 
66162e0
 
 
 
 
39c3242
66162e0
 
39c3242
 
 
 
 
 
 
 
66162e0
 
39c3242
 
 
 
66162e0
 
39c3242
66162e0
 
 
 
39c3242
 
 
 
 
 
 
 
 
 
 
66162e0
 
 
 
39c3242
66162e0
 
 
 
 
39c3242
 
 
 
 
 
66162e0
 
624b1f5
 
 
 
5db6253
 
 
 
66162e0
4cd748e
 
66162e0
4cd748e
5db6253
 
66162e0
4cd748e
 
 
 
624b1f5
5db6253
 
 
4cd748e
624b1f5
0637451
 
624b1f5
 
4cd748e
624b1f5
 
4cd748e
 
 
 
 
 
 
5db6253
 
 
4cd748e
0637451
4cd748e
5db6253
 
 
 
 
 
0637451
 
 
 
5db6253
 
0637451
5db6253
0637451
 
5db6253
0637451
 
 
5db6253
 
4cd748e
 
66162e0
4cd748e
5db6253
 
4cd748e
 
5db6253
 
 
 
 
 
 
66162e0
4cd748e
66162e0
4cd748e
66162e0
624b1f5
5db6253
4cd748e
624b1f5
4cd748e
624b1f5
4cd748e
 
5db6253
4cd748e
 
 
66162e0
624b1f5
5db6253
624b1f5
 
 
 
4cd748e
 
66162e0
 
4cd748e
66162e0
 
39c3242
66162e0
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
edd7cc2
39c3242
 
 
 
 
66162e0
 
39c3242
66162e0
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
66162e0
39c3242
 
 
 
 
 
66162e0
39c3242
 
 
 
 
 
 
66162e0
39c3242
 
 
 
 
66162e0
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bad12de
 
 
 
 
 
 
 
 
 
39c3242
 
 
 
 
 
66162e0
39c3242
edd7cc2
 
66162e0
39c3242
66162e0
39c3242
 
 
edd7cc2
 
39c3242
 
 
 
 
edd7cc2
39c3242
 
 
 
 
 
 
 
 
edd7cc2
39c3242
 
 
 
 
edd7cc2
39c3242
 
 
66162e0
39c3242
66162e0
39c3242
edd7cc2
66162e0
39c3242
 
 
66162e0
39c3242
66162e0
39c3242
 
 
66162e0
 
 
39c3242
 
 
 
 
 
 
 
 
 
 
66162e0
39c3242
 
 
66162e0
 
 
5db6253
39c3242
5db6253
 
39c3242
5db6253
66162e0
5db6253
 
66162e0
39c3242
5db6253
66162e0
 
5db6253
39c3242
5db6253
 
 
 
 
 
 
 
 
 
 
 
 
 
39c3242
5db6253
 
 
 
39c3242
5db6253
 
39c3242
5db6253
39c3242
 
 
66162e0
 
39c3242
66162e0
 
39c3242
5db6253
 
 
66162e0
 
39c3242
66162e0
39c3242
 
8304ec0
39c3242
 
 
66162e0
39c3242
 
66162e0
5db6253
39c3242
5db6253
 
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8304ec0
39c3242
 
 
 
 
 
 
 
 
 
5db6253
 
39c3242
 
5db6253
 
39c3242
 
 
66162e0
39c3242
 
 
66162e0
39c3242
 
8304ec0
edd7cc2
39c3242
edd7cc2
 
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8304ec0
0150680
 
 
 
39c3242
 
 
 
0150680
 
 
 
 
 
 
 
39c3242
0150680
 
 
39c3242
6b99b26
0150680
 
6b99b26
 
0150680
39c3242
 
6b99b26
 
0150680
 
6b99b26
 
 
0150680
39c3242
6b99b26
 
 
 
39c3242
6b99b26
 
39c3242
6b99b26
0150680
6b99b26
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
39c3242
6b99b26
 
 
 
 
 
 
39c3242
 
6b99b26
 
 
39c3242
 
6b99b26
 
 
 
 
 
 
 
 
 
 
 
 
 
 
0150680
 
 
bad12de
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
39c3242
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8304ec0
66162e0
 
 
 
 
8304ec0
 
 
 
 
39c3242
 
 
 
 
0150680
bad12de
8304ec0
66162e0
 
8304ec0
66162e0
 
 
8304ec0
66162e0
 
 
 
8304ec0
39c3242
 
 
 
 
0150680
bad12de
66162e0
8304ec0
 
66162e0
bad12de
39c3242
66162e0
 
39c3242
66162e0
 
 
 
 
 
39c3242
 
 
 
 
 
66162e0
8304ec0
5db6253
39c3242
5db6253
 
 
 
 
39c3242
 
8304ec0
5db6253
66162e0
 
 
 
 
 
39c3242
66162e0
 
 
f02fc86
39c3242
 
 
 
f02fc86
66162e0
 
 
 
 
 
39c3242
66162e0
 
 
f02fc86
39c3242
 
 
 
 
f02fc86
66162e0
8304ec0
39c3242
bad4491
f02fc86
bad4491
 
39c3242
464e464
bad4491
 
 
 
 
 
 
 
 
 
39c3242
bad4491
39c3242
 
bad4491
 
39c3242
 
bad4491
 
088ab4e
39c3242
01b4780
bad4491
 
66162e0
bad4491
8304ec0
66162e0
39c3242
bad4491
 
39c3242
bad4491
39c3242
 
 
 
 
 
bad4491
 
 
 
5db6253
39c3242
bad4491
15b5069
39c3242
15b5069
66162e0
39c3242
15b5069
5db6253
8227cf3
5db6253
 
 
 
 
 
 
 
 
39c3242
 
 
 
 
 
5db6253
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
8227cf3
3d55948
 
 
 
 
 
 
 
 
 
15b5069
8227cf3
39c3242
 
 
 
 
8227cf3
5db6253
 
 
66717fb
39c3242
66717fb
5db6253
39c3242
 
 
15b5069
8227cf3
 
 
 
39c3242
 
5db6253
 
39c3242
5db6253
 
 
 
39c3242
 
 
8304ec0
8227cf3
39c3242
8227cf3
 
39c3242
 
8227cf3
 
66162e0
39c3242
 
8227cf3
39c3242
01b4780
 
 
39c3242
8304ec0
39c3242
66717fb
 
39c3242
66717fb
39c3242
 
 
 
 
66717fb
 
 
8304ec0
66162e0
5db6253
39c3242
 
3d55948
 
5db6253
66162e0
 
 
 
13572cd
 
 
 
 
 
4cd748e
 
 
 
 
 
 
13572cd
bad12de
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
13572cd
4cd748e
 
13572cd
 
 
 
 
 
 
 
 
 
66162e0
 
13572cd
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
66162e0
 
2d875c8
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
import os
import re
import json
import hashlib
from datetime import datetime
from io import BytesIO
from concurrent.futures import ThreadPoolExecutor

try:
    import pymupdf as fitz
except ImportError:
    import fitz
import pdfplumber
import pandas as pd
import requests

GROQ_API_KEY = os.getenv("GROQ_API_KEY", "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")
GEMINI_API_KEY = os.getenv("GEMINI_API_KEY", "AQ.Ab8RN6KAedFsuHHpJ3aizbHW7ptE0xf8UPdq8cvLTLdUp8ILrQ")
OPENROUTER_API_KEY = os.getenv("OPENROUTER_API_KEY", "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")

PROVIDERS = [
    {"name": "groq_fast", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions",
     "api_key": GROQ_API_KEY, "model": "llama-3.1-8b-instant"},
    {"name": "groq", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions",
     "api_key": GROQ_API_KEY, "model": "llama-3.3-70b-versatile"},
    {"name": "gemini", "kind": "gemini", "url": "https://generativelanguage.googleapis.com/v1beta/models/{model}:generateContent",
     "api_key": GEMINI_API_KEY, "model": "gemini-2.0-flash"},
    {"name": "openrouter", "kind": "openai", "url": "https://openrouter.ai/api/v1/chat/completions",
     "api_key": OPENROUTER_API_KEY, "model": "openrouter/free"},
]

DATE_FORMATS = [
    "%d/%m/%Y", "%d-%m-%Y", "%d.%m.%Y",
    "%d/%m/%y", "%d-%m-%y", "%d.%m.%y",
    "%d-%b-%Y", "%d-%b-%y", "%d %b %Y", "%d %b %y",
    "%d-%B-%Y", "%d %B %Y",
    "%Y/%m/%d", "%Y-%m-%d", "%Y.%m.%d",
    "%d%b%Y", "%d%B%Y"
]

def to_float(s):
    if s is None:
        return None
    s_clean = str(s).replace(",", "").replace(" ", "").replace("`", "").strip()
    if not s_clean or s_clean in ("-", "--", "None", "null", "CR", "DR", "Cr", "Dr"):
        return None
    try:
        return float(s_clean)
    except ValueError:
        return None

def clean_desc(parts):
    cleaned = []
    for p in parts:
        if not p:
            continue
        p_str = str(p).strip()
        p_str = re.sub(r"^[|\[{(~#_\\/ -]+", "", p_str)
        p_str = re.sub(r"[|\]})~#_\\/ -]+$", "", p_str)
        p_str = re.sub(r"\s+", " ", p_str).strip()
        if p_str and p_str not in ("-", "--", "|", "/"):
            cleaned.append(p_str)
    return " ".join(cleaned)

def parse_any_date(d):
    if not d:
        return None
    d_clean = str(d).strip().replace(" ", "-").replace("/", "-").replace(".", "-")
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(d.strip(), fmt)
        except ValueError:
            continue
    # Try normalized hyphen format
    for fmt in ["%d-%m-%Y", "%d-%m-%y", "%d-%b-%Y", "%d-%b-%y", "%Y-%m-%d"]:
        try:
            return datetime.strptime(d_clean, fmt)
        except ValueError:
            continue
    return None

def assign_debit_credit(rows, lines=None):
    if not rows:
        return rows

    for r in rows:
        if r.get("description"):
            r["description"] = clean_desc([str(r["description"])])

    parsed_dates = [parse_any_date(r.get("date", "")) for r in rows]
    valid_dates = [(i, d) for i, d in enumerate(parsed_dates) if d is not None]

    ascending = True
    if len(valid_dates) >= 2:
        if valid_dates[0][1] != valid_dates[-1][1]:
            ascending = valid_dates[0][1] < valid_dates[-1][1]

    if ascending:
        chrono_indices = list(range(len(rows)))
    else:
        chrono_indices = list(range(len(rows) - 1, -1, -1))

    debit_kw = ["/DR/", " DR", "DR ", "WITHDRAWAL", "TO ", "ATM", "UPI", "POS", "DEBIT", "CHARGES", "FEE", "PAY", "IMPS", "CHQ", "CHEQUE", "EMI", "BILL", "CARD", "TAX", "WDR", "PURCHASE", "COMMISSION", "GST", "TRANSFER TO"]
    credit_kw = ["/CR/", " CR", "CR ", "DEPOSIT", "BY ", "INTEREST", "SALARY", "CREDIT", "REFUND", "REVERSAL", "DIVIDEND", "CASH DEP", "TRANSFER FROM"]

    opening_balance = None
    if lines:
        header_lines = lines[:80]
        for line in header_lines:
            om = re.search(r"(?:opening\s*bal(?:ance)?|bal(?:ance)?\s*b/?f|brought\s*forward|previous\s*balance)\s*:?\s*(-?[\d,]+\.\d{2})", line, re.IGNORECASE)
            if om:
                opening_balance = to_float(om.group(1))
                break

    oldest_idx = chrono_indices[0]
    oldest_row = rows[oldest_idx]
    prev_balance = opening_balance

    if prev_balance is None:
        b1 = oldest_row.get("balance")
        a1 = oldest_row.get("amount")
        if a1 is None:
            a1 = oldest_row.get("debit") if oldest_row.get("debit") is not None else oldest_row.get("credit")

        desc1 = str(oldest_row.get("description", "")).upper()

        if b1 is not None and a1 is not None:
            if oldest_row.get("sign") == "debit" or (oldest_row.get("debit") is not None and oldest_row.get("credit") is None):
                oldest_row["sign"] = "debit"
                prev_balance = round(b1 + a1, 2)
            elif oldest_row.get("sign") == "credit" or (oldest_row.get("credit") is not None and oldest_row.get("debit") is None):
                oldest_row["sign"] = "credit"
                prev_balance = round(b1 - a1, 2)
            else:
                cand_debit = round(b1 + a1, 2)
                cand_credit = round(b1 - a1, 2)

                is_deb = any(kw in desc1 for kw in debit_kw) or desc1.startswith("TO ") or "DR" in desc1
                is_cred = any(kw in desc1 for kw in credit_kw) or desc1.startswith("BY ") or "CR" in desc1

                if is_deb and not is_cred:
                    oldest_row["sign"] = "debit"
                    prev_balance = cand_debit
                elif is_cred and not is_deb:
                    oldest_row["sign"] = "credit"
                    prev_balance = cand_credit
                else:
                    oldest_row["sign"] = "debit"
                    prev_balance = cand_debit

    for idx in chrono_indices:
        r = rows[idx]
        amt = r.get("amount")
        if amt is None:
            amt = r.get("debit") if r.get("debit") is not None else r.get("credit")
        bal = r.get("balance")

        if r.get("debit") is not None and r.get("credit") is None:
            r["sign"] = "debit"
            amt = r["debit"]
        elif r.get("credit") is not None and r.get("debit") is None:
            r["sign"] = "credit"
            amt = r["credit"]

        if r.get("sign") == "debit":
            r["debit"], r["credit"] = amt, None
        elif r.get("sign") == "credit":
            r["debit"], r["credit"] = None, amt
        else:
            if prev_balance is not None and bal is not None and amt is not None:
                diff = round(bal - prev_balance, 2)
                if diff > 0.001:
                    r["debit"], r["credit"] = None, amt
                elif diff < -0.001:
                    r["debit"], r["credit"] = amt, None
                else:
                    desc = str(r.get("description", "")).upper()
                    if any(kw in desc for kw in debit_kw):
                        r["debit"], r["credit"] = amt, None
                    else:
                        r["debit"], r["credit"] = None, amt
            else:
                desc = str(r.get("description", "")).upper()
                if any(kw in desc for kw in debit_kw):
                    r["debit"], r["credit"] = amt, None
                else:
                    r["debit"], r["credit"] = None, amt

        if bal is not None:
            prev_balance = bal
        r.pop("sign", None)
        r.pop("amount", None)

    return rows

# ── Spatial Line Extraction (Grouping Words into Rows) ──────────────────────

def extract_spatial_lines_from_page(page, y_tolerance=4.0):
    """
    Groups words extracted from PDF page into visual horizontal lines.
    Prevents column splitting bugs where Dates and Balances end up in separate blocks.
    """
    words = page.get_text("words")
    if not words:
        raw_text = page.get_text("text") or ""
        return [l.strip() for l in raw_text.splitlines() if l.strip()]

    lines_dict = {}
    for w in words:
        x0, y0, x1, y1, text = w[:5]
        matched_y = None
        for y in sorted(lines_dict.keys()):
            if abs(y - y0) <= y_tolerance:
                matched_y = y
                break
        if matched_y is None:
            matched_y = y0
            lines_dict[matched_y] = []
        lines_dict[matched_y].append((x0, text))

    result_lines = []
    for y in sorted(lines_dict.keys()):
        line_words = [t[1] for t in sorted(lines_dict[y], key=lambda item: item[0])]
        result_lines.append(" ".join(line_words).strip())
    return result_lines


# ── Fast Parallel OCR Engine ───────────────────────────────────────────────

def _setup_tesseract():
    tesseract_paths = [
        r"C:\Program Files\Tesseract-OCR",
        r"C:\Program Files (x86)\Tesseract-OCR",
        os.path.expanduser(r"~\AppData\Local\Programs\Tesseract-OCR"),
    ]
    for t_path in tesseract_paths:
        if os.path.exists(t_path) and t_path not in os.environ.get("PATH", ""):
            os.environ["PATH"] = t_path + ";" + os.environ.get("PATH", "")
        t_exe = os.path.join(t_path, "tesseract.exe")
        if os.path.exists(t_exe):
            try:
                import pytesseract
                pytesseract.pytesseract.tesseract_cmd = t_exe
            except Exception:
                pass

def _ocr_page_worker(pix_data):
    w, h, samples = pix_data
    from PIL import Image
    import pytesseract
    img = Image.frombytes("RGB", [w, h], samples)
    return pytesseract.image_to_string(img, lang="eng", config="--psm 6")

def ocr_extract_pdf_lines(pdf_bytes, target_pages=None, password=None, progress_callback=None):
    """
    Ultra-Fast Parallel OCR Engine for scanned PDF statements.
    Renders pages at 150 DPI and executes Tesseract OCR in parallel across CPU cores.
    """
    _setup_tesseract()
    lines = []

    # Fast Path: Multi-Threaded PyMuPDF Pixmap + PyTesseract
    try:
        import pytesseract
        if progress_callback:
            progress_callback(25, "Running high-speed multi-threaded OCR...")

        doc = fitz.open(stream=pdf_bytes, filetype="pdf")
        if password and doc.is_encrypted:
            doc.authenticate(password)

        total_pages = len(doc)
        indices = target_pages if target_pages else list(range(total_pages))

        page_pixmaps = []
        for idx in indices:
            if idx < total_pages:
                page = doc[idx]
                pix = page.get_pixmap(dpi=150)
                page_pixmaps.append((idx, (pix.width, pix.height, pix.samples)))
        doc.close()

        workers = min(8, max(1, os.cpu_count() or 4))
        with ThreadPoolExecutor(max_workers=workers) as ex:
            futures = [(idx, ex.submit(_ocr_page_worker, pdata)) for idx, pdata in page_pixmaps]
            for idx, fut in futures:
                txt = fut.result() or ""
                lines.append(f"--- PAGE {idx + 1} ---")
                for l in txt.splitlines():
                    cleaned_l = re.sub(r"^[|\[{(~#_\\/ -]+", "", l.strip())
                    if cleaned_l:
                        lines.append(cleaned_l)

        if len(re.findall(r"[a-zA-Z0-9]", "".join(lines))) >= 30:
            return lines
    except Exception:
        pass

    # Fallback Path: ocrmypdf
    try:
        import ocrmypdf
        import tempfile
        if progress_callback:
            progress_callback(35, "Running ocrmypdf OCR fallback...")

        with tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) as in_file, \
             tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) as out_file:
            in_file.write(pdf_bytes)
            in_path = in_file.name
            out_path = out_file.name

        try:
            ocr_kwargs = {
                "skip_text": True,
                "deskew": False,
                "rotate_pages": False,
                "oversample": 150,
                "optimize": 0,
                "jobs": max(1, os.cpu_count() or 4),
                "progress_bar": False,
            }
            if password:
                ocr_kwargs["password"] = password
            ocrmypdf.ocr(in_path, out_path, **ocr_kwargs)

            with fitz.open(out_path) as ocr_doc:
                for idx in range(len(ocr_doc)):
                    lines.append(f"--- PAGE {idx + 1} ---")
                    lines.extend(extract_spatial_lines_from_page(ocr_doc[idx]))
        finally:
            for p in (in_path, out_path):
                if os.path.exists(p):
                    try:
                        os.remove(p)
                    except OSError:
                        pass
    except Exception:
        pass

    return lines


# ── Text Extraction (PyMuPDF Fast Path) ────────────────────────────────────

def extract_pdf_lines(pdf_bytes, page_numbers=None, password=None, progress_callback=None):
    lines = []
    target_pages = []

    try:
        doc = fitz.open(stream=pdf_bytes, filetype="pdf")
        if password and doc.is_encrypted:
            doc.authenticate(password)

        total_pages = len(doc)
        target_pages = list(range(total_pages))

        if page_numbers:
            parsed_pages = []
            for part in str(page_numbers).split(","):
                part = part.strip()
                if not part:
                    continue
                if "-" in part:
                    try:
                        start_str, end_str = part.split("-", 1)
                        start = int(start_str.strip()) - 1
                        end = int(end_str.strip()) - 1
                        if start > end:
                            start, end = end, start
                        parsed_pages.extend(range(max(0, start), min(total_pages, end + 1)))
                    except ValueError:
                        continue
                else:
                    try:
                        p = int(part) - 1
                        if 0 <= p < total_pages:
                            parsed_pages.append(p)
                    except ValueError:
                        continue
            if parsed_pages:
                target_pages = sorted(list(set(parsed_pages)))

        for idx in target_pages:
            page = doc[idx]
            lines.append(f"--- PAGE {idx + 1} ---")
            lines.extend(extract_spatial_lines_from_page(page))
        doc.close()
    except Exception:
        # Fallback to pdfplumber
        try:
            pdf_file = BytesIO(pdf_bytes)
            open_kwargs = {}
            if password:
                open_kwargs["password"] = password
            with pdfplumber.open(pdf_file, **open_kwargs) as pdf:
                total_pages = len(pdf.pages)
                target_pages = list(range(total_pages))
                for idx in target_pages:
                    lines.append(f"--- PAGE {idx + 1} ---")
                    t = pdf.pages[idx].extract_text() or ""
                    lines.extend([l.strip() for l in t.splitlines() if l.strip()])
        except Exception as e:
            err_msg = str(e).lower()
            if "password" in err_msg or "encrypted" in err_msg or "protected" in err_msg:
                raise ValueError("This PDF is password-protected. Please enter the PDF password and try again.")
            raise e

    # Check if text is genuine or if it is a scanned image PDF
    combined_text = "".join(lines).strip()
    alnum_count = len(re.findall(r"[a-zA-Z0-9]", combined_text))
    num_target = len(target_pages) if target_pages else 1

    # Accurate Scanned PDF Check: Only trigger OCR if extracted characters are virtually empty (< 35 per page)
    if alnum_count < (35 * num_target):
        if progress_callback:
            progress_callback(20, "Scanned image PDF detected! Extracting via high-speed OCR...")
        ocr_lines = ocr_extract_pdf_lines(pdf_bytes, target_pages=target_pages, password=password, progress_callback=progress_callback)
        if len(re.findall(r"[a-zA-Z0-9]", "".join(ocr_lines))) > alnum_count:
            lines = ocr_lines

    cleaned_lines = []
    for l in lines:
        if l.startswith("--- PAGE ") and l.endswith(" ---"):
            cleaned_lines.append(l)
        else:
            cleaned = re.sub(r"^[^\w\s/.-]+", "", l.strip()).strip()
            if cleaned:
                cleaned_lines.append(cleaned)
            elif l.strip():
                cleaned_lines.append(l.strip())

    return cleaned_lines


# ── Individual Bank Parsers ────────────────────────────────────────────────

DATE_PATTERN = (
    r"(\d{4}[/-]\d{2}[/-]\d{2}|"
    r"\d{1,2}[/-]\d{1,2}[/-]\d{2,4}|"
    r"\d{1,2}\s+[A-Za-z]{3,9}\s+\d{2,4}|"
    r"\d{1,2}-[A-Za-z]{3,9}-\d{2,4}|"
    r"\d{1,2}\.[A-Za-z]{3,9}\.\d{2,4}|"
    r"\d{1,2}\.\d{1,2}\.\d{2,4}|"
    r"\d{1,2}[A-Za-z]{3}\d{2,4})"
)
DATE_RE = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN, re.IGNORECASE)
NUMBER_RE = re.compile(r"(-?[\d,]+(?:\s*,\s*\d+)*\.\d{2})")

MONTH_MAP = {
    "january": 1, "february": 2, "march": 3, "april": 4,
    "may": 5, "june": 6, "july": 7, "august": 8,
    "september": 9, "october": 10, "november": 11, "december": 12,
    "jan": 1, "feb": 2, "mar": 3, "apr": 4, "jun": 6,
    "jul": 7, "aug": 8, "sep": 9, "sept": 9, "oct": 10, "nov": 11, "dec": 12
}
MONTH_REGEX = r"(?:January|February|March|April|May|June|July|August|September|October|November|December|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Sept|Oct|Nov|Dec)"
DATE_AMEX_TEXT_RE = re.compile(r"^(" + MONTH_REGEX + r")\s+(\d{1,2})\b", re.IGNORECASE)

def normalize_numbers_in_line(line):
    line = re.sub(r"(\d+)\s*,\s*(\d+)", r"\1,\2", line)
    line = re.sub(r"(\d+)\s*\.\s*(\d+)", r"\1.\2", line)
    return line

def parse_icici(lines, pdf_path=None):
    row_re = re.compile(
        r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$",
        re.IGNORECASE
    )
    rows = []
    n = len(lines)
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue
        m = row_re.match(stripped)
        if m:
            date = m.group(1)
            desc_part = m.group(2)
            amount = to_float(m.group(3))
            balance = to_float(m.group(4))

            prev_line = lines[i - 1].strip() if i > 0 else ""
            next_line = lines[i + 1].strip() if i + 1 < n else ""

            desc_parts = []
            if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", prev_line, re.IGNORECASE):
                desc_parts.append(prev_line)
            desc_parts.append(desc_part)
            if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not NUMBER_RE.search(next_line):
                desc_parts.append(next_line)

            rows.append({
                "date": date,
                "description": clean_desc(desc_parts),
                "amount": amount,
                "balance": balance,
                "sign": None,
            })
    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_hdfc(lines, pdf_path=None):
    row_re = re.compile(
        r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(?:(\d{5,})\s+)?" + DATE_PATTERN + r"\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$",
        re.IGNORECASE
    )
    alt_re = re.compile(
        r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$",
        re.IGNORECASE
    )
    rows = []
    n = len(lines)
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue
        m = row_re.match(stripped)
        if m:
            date, desc, _ref, _vdate, amount, balance = m.group(1), m.group(2), m.group(3), m.group(4), to_float(m.group(5)), to_float(m.group(6))
            prev_line = lines[i - 1].strip() if i > 0 else ""
            full_desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc])
            rows.append({"date": date, "description": full_desc, "amount": amount, "balance": balance, "sign": None})
        else:
            m2 = alt_re.match(stripped)
            if m2 and not re.search(r"Opening\s*Balance|Statement|Page", stripped, re.IGNORECASE):
                date, desc, amount, balance = m2.group(1), m2.group(2), to_float(m2.group(3)), to_float(m2.group(4))
                prev_line = lines[i - 1].strip() if i > 0 else ""
                full_desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc])
                rows.append({"date": date, "description": full_desc, "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_sbi(lines, pdf_path=None):
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue
        m = DATE_RE.match(stripped)
        if not m:
            continue
        parts = stripped.split()
        if len(parts) < 4:
            continue
        date = m.group(1)
        balance = to_float(parts[-1])
        if balance is None:
            continue

        amount, debit, credit = None, None, None
        if len(parts) >= 6:
            cand1 = to_float(parts[-3])
            cand2 = to_float(parts[-2])
            if cand1 is not None and (cand2 is not None or parts[-2] in ("-", "0", "0.00")):
                debit = cand1
                credit = cand2 if parts[-2] not in ("-", "0", "0.00") else None
                desc = " ".join(parts[1:-3])
            elif cand2 is not None and (cand1 is not None or parts[-3] in ("-", "0", "0.00")):
                debit = cand1 if parts[-3] not in ("-", "0", "0.00") else None
                credit = cand2
                desc = " ".join(parts[1:-3])
            else:
                amount = to_float(parts[-2])
                desc = " ".join(parts[1:-2])
        else:
            amount = to_float(parts[-2])
            desc = " ".join(parts[1:-2])

        prev_line = lines[i - 1].strip() if i > 0 else ""
        if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page", prev_line, re.IGNORECASE):
            desc = clean_desc([prev_line, desc])

        rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "debit": debit, "credit": credit, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_axis(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})(?:\s+\d+)?\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue
        m = row_re.match(stripped)
        if not m:
            continue
        date, tail, amount, balance = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4))
        prev_line = lines[i - 1].strip() if i > 0 else ""
        desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", tail])
        rows.append({"date": date, "description": desc, "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_kotak(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})(?:\s*\((?:Cr|Dr)\))?\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue
        m = row_re.match(stripped)
        if not m:
            continue
        date, desc_part, amount, balance = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4))
        sign = "credit" if "CR" in stripped.upper() else ("debit" if "DR" in stripped.upper() else None)
        prev_line = lines[i - 1].strip() if i > 0 else ""
        desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc_part])
        rows.append({"date": date, "description": desc, "amount": amount, "balance": balance, "sign": sign})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_pnb(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*(.*)$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        m = row_re.match(stripped)
        if not m:
            continue
        date, amount, balance, trail = m.group(1), to_float(m.group(2)), to_float(m.group(3)), m.group(4)
        prev_line = lines[i - 1].strip() if i > 0 else ""
        desc_parts = [prev_line if prev_line and not DATE_RE.match(prev_line) else "", trail]
        rows.append({"date": date, "description": clean_desc(desc_parts), "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_ubi(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s+(Cr|Dr)\b", re.IGNORECASE)
    alt_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        m = row_re.match(stripped)
        if m:
            date, desc, amount, balance, balsign = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4)), m.group(5)
            rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": "credit" if balsign.upper() == "CR" else "debit"})
        else:
            m2 = alt_re.match(stripped)
            if m2 and not re.search(r"Opening|Statement|Page", stripped, re.IGNORECASE):
                date, desc, amount, balance = m2.group(1), m2.group(2), to_float(m2.group(3)), to_float(m2.group(4))
                rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_idfc(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(?:" + DATE_PATTERN + r"\s+)?(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        m = row_re.match(stripped)
        if m:
            date, desc, amount, balance = m.group(1), m.group(3), to_float(m.group(4)), to_float(m.group(5))
            rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_indusind(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+(Dr|Cr)\s+([\d,]+\.\d{2})\s+(Cr|Dr)\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        m = row_re.match(stripped)
        if m:
            date, desc, amount, txn_sign, balance, _balsign = m.group(1), m.group(2), to_float(m.group(3)), m.group(4), to_float(m.group(5)), m.group(6)
            rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": "debit" if txn_sign.upper() == "DR" else "credit"})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_idbi(lines, pdf_path=None):
    row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(?:" + DATE_PATTERN + r"\s+)?(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE)
    rows = []
    for i, line in enumerate(lines):
        stripped = normalize_numbers_in_line(line.strip())
        m = row_re.match(stripped)
        if m:
            date, desc, amount, balance = m.group(1), m.group(3), to_float(m.group(4)), to_float(m.group(5))
            rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None})

    if len(rows) >= 2:
        return assign_debit_credit(rows, lines)
    return parse_generic_statement(lines)

def parse_bob(lines, pdf_path=None):
    """Bank of Baroda Statement Parser"""
    return parse_generic_statement(lines)

def parse_canara(lines, pdf_path=None):
    """Canara Bank Statement Parser"""
    return parse_generic_statement(lines)

def parse_hsbc(lines, pdf_path=None):
    """HSBC Bank Statement Parser"""
    return parse_generic_statement(lines)

def parse_federal(lines, pdf_path=None):
    """Federal Bank Statement Parser"""
    return parse_generic_statement(lines)

def parse_yesbank(lines, pdf_path=None):
    """Yes Bank Statement Parser"""
    return parse_generic_statement(lines)

def parse_credit_card(lines, pdf_path=None):
    """
    Dedicated Credit Card Statement Parser.
    Handles Credit Card statement table formats:
    - Separate DR Column & CR Column
    - Single Amount + DR/CR Flag Column
    - Inline DR/CR Suffixes
    - Scanned tables with bracket/pipe borders
    """
    if not lines:
        return []

    rows = []
    n = len(lines)

    for i, line in enumerate(lines):
        stripped = re.sub(r"^[|\[{(~#_\\/ -]+", "", line.strip())
        if not stripped or stripped.startswith("--- PAGE "):
            continue

        m = DATE_RE.match(stripped)
        if not m:
            continue

        date_str = m.group(1)
        rest = stripped[m.end():].strip()

        drcr_match = re.findall(r"\b(DR|CR|Dr|Cr|CRs|oR|pr|pR)\b", rest, re.IGNORECASE)
        num_matches = NUMBER_RE.findall(rest)

        if not num_matches:
            continue

        floats = [to_float(nm) for nm in num_matches if to_float(nm) is not None]
        if not floats:
            continue

        debit, credit, sign = None, None, None

        if drcr_match:
            last_drcr = drcr_match[-1].upper()
            amt = floats[0]
            if last_drcr in ("DR", "OR", "PR"):
                debit = amt
                sign = "debit"
            elif last_drcr in ("CR", "CRS"):
                credit = amt
                sign = "credit"
        elif len(floats) >= 2:
            deb_cand = floats[0]
            cred_cand = floats[1]
            if deb_cand != 0 and cred_cand == 0:
                debit = deb_cand
                sign = "debit"
            elif cred_cand != 0 and deb_cand == 0:
                credit = cred_cand
                sign = "credit"
            else:
                if re.search(r"PAYMENT|REFUND|REVERSAL|CASHBACK|CREDIT", rest, re.IGNORECASE):
                    credit = cred_cand if cred_cand != 0 else deb_cand
                    sign = "credit"
                else:
                    debit = deb_cand
                    sign = "debit"
        else:
            amt = floats[0]
            if re.search(r"PAYMENT|REFUND|REVERSAL|CASHBACK|CREDIT", rest, re.IGNORECASE):
                credit = amt
                sign = "credit"
            else:
                debit = amt
                sign = "debit"

        desc = rest
        for nm in num_matches:
            desc = desc.replace(nm, "")
        for dc in ["DR", "CR", "Dr", "Cr", "CRs", "oR", "pr", "pR"]:
            desc = re.sub(r"\b" + dc + r"\b", "", desc, flags=re.IGNORECASE)
        desc = re.sub(r"\s+", " ", desc).strip()

        prev_line = lines[i - 1].strip() if i > 0 else ""
        next_line = lines[i + 1].strip() if i + 1 < n else ""

        desc_parts = []
        if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Statement|Page|Card Number|Total|Minimum|Date|Transaction|Description|Amount|Particulars|Balance|SERIAL", prev_line, re.IGNORECASE):
            desc_parts.append(prev_line)

        desc_parts.append(desc)

        if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not re.search(r"Statement|Page|Card Number|Total|Minimum|Date|Transaction|Description|Amount|Particulars|Balance|SERIAL", next_line, re.IGNORECASE):
            if not NUMBER_RE.search(next_line):
                desc_parts.append(next_line)

        full_desc = clean_desc(desc_parts)

        if re.search(r"Total Amount Due|Minimum Amount Due|Payment Due Date|Credit Limit|Available Limit", full_desc, re.IGNORECASE):
            continue

        rows.append({
            "date": date_str,
            "description": full_desc,
            "debit": debit,
            "credit": credit,
            "balance": None,
            "sign": sign,
        })

    return assign_debit_credit(rows, lines)

def parse_amex(lines, pdf_path=None):
    """
    American Express (Amex) Credit Card Statement Parser.
    Extracts transactions across multi-page statements:
    - Spends, Domestic & Overseas purchases, Taxes/GST, Membership Fees, Installments
    - Payments Received, Refunds, and Credits
    - Excludes non-transaction summary blocks (e.g. Summary of New Installment Plans Created)
    - Accurately tracks Opening Balance, debits, credits, and running card balances
    """
    if not lines:
        return []

    stmt_day, stmt_month, stmt_year = None, None, None
    opening_bal = None
    closing_bal = None

    header_text = " ".join(lines[:70])
    m_date = re.search(r"\b(\d{2})/(\d{2})/(\d{4})\b", header_text)
    if m_date:
        stmt_day, stmt_month, stmt_year = int(m_date.group(1)), int(m_date.group(2)), int(m_date.group(3))
    else:
        m_date2 = re.search(r"(?:Statement\s+includes\s+payments.*?by|Statement\s+Period\s+From.*?to)\s+([A-Za-z]+)\s+(\d{1,2}),?\s+(\d{4})", header_text, re.I)
        if m_date2:
            m_name = m_date2.group(1).lower()
            stmt_day = int(m_date2.group(2))
            stmt_month = MONTH_MAP.get(m_name, 1)
            stmt_year = int(m_date2.group(3))

    m_sum = re.search(
        r"Opening\s*Balance\s*Rs.*?([\d,]+\.\d{2}(?:\s*CR)?)\s*-\s*([\d,]+\.\d{2}(?:\s*CR)?)\s*\+\s*([\d,]+\.\d{2}(?:\s*CR)?)\s*=\s*([\d,]+\.\d{2}(?:\s*CR)?)",
        header_text,
        re.I,
    )
    if m_sum:
        def parse_bal(s):
            val = to_float(s.replace("CR", "").replace("Cr", "").replace("cr", ""))
            if val is None:
                return None
            return -val if "CR" in s.upper() else val

        opening_bal = parse_bal(m_sum.group(1))
        closing_bal = parse_bal(m_sum.group(4))
    else:
        m_op = re.search(r"Opening\s*Balance\s*Rs\s*[:\n]?\s*([\d,]+\.\d{2}(?:\s*CR)?)", header_text, re.I)
        if m_op:
            s = m_op.group(1).upper()
            val = to_float(s.replace("CR", ""))
            if val is not None:
                opening_bal = -val if "CR" in s else val

    transactions = []
    in_details = False
    in_new_plans_summary = False

    n = len(lines)
    i = 0
    while i < n:
        line = lines[i].strip()

        # Table start markers
        if re.search(r"Details\s+(?:Foreign\s+Spending\s+)?Amount\s+Rs", line, re.I) or re.search(r"\bDetails\s+Amount\s+Rs\b", line, re.I):
            in_details = True
            i += 1
            continue

        # Informational section to skip
        if re.search(r"Summary of New Installment Plans Created", line, re.I):
            in_new_plans_summary = True
            i += 1
            continue
        if re.search(r"Total of New Installment Plans Created", line, re.I):
            in_new_plans_summary = False
            i += 1
            continue

        # End markers on current page
        if any(marker in line for marker in [
            "Payment Advice", "Please return this advice", "Payment Information",
            "Drop Boxes", "Mailing Preference", "Grievances Redressal",
            "Missing Payments may affect", "CardMember Offers", "Annual Fee",
            "Interest Free Grace Period", "Sample Interest Illustration",
            "Most Important Terms & Conditions"
        ]):
            in_details = False
            i += 1
            continue

        if line.startswith("--- PAGE "):
            in_details = False
            i += 1
            continue

        if not in_details or in_new_plans_summary:
            i += 1
            continue

        # Subtotals and Section Headers to ignore
        if any(sub in line for sub in [
            "New domestic transactions for",
            "New overseas transactions for",
            "Total of other account transactions",
            "Total of Installments",
            "TOTAL OVERSEAS SPEND IN CURRENT MONTH",
            "OTHER ACCOUNT TRANSACTIONS",
            "Installment Plan Transactions",
            "Credit Summary",
            "Current Rates of Interest",
            "Monthly Rate",
            "Statement Period From",
            "Payment Due Date"
        ]):
            i += 1
            continue

        m_dt = DATE_AMEX_TEXT_RE.match(line)
        if m_dt:
            month_name = m_dt.group(1).lower()
            day_num = int(m_dt.group(2))
            month_num = MONTH_MAP.get(month_name)

            txn_year = stmt_year
            if stmt_month and stmt_year and month_num:
                if stmt_month < month_num:
                    txn_year = stmt_year - 1
                else:
                    txn_year = stmt_year

            date_str = f"{day_num:02d}/{month_num:02d}/{txn_year}" if txn_year and month_num else f"{day_num:02d}/{month_name}"

            num_matches = NUMBER_RE.findall(line)
            if not num_matches:
                i += 1
                continue

            amt = to_float(num_matches[-1])
            if amt is None:
                i += 1
                continue

            rest = line[m_dt.end():].strip()
            rest = re.sub(re.escape(num_matches[-1]) + r"\s*$", "", rest).strip()
            if len(num_matches) > 1:
                for fnm in num_matches[:-1]:
                    rest = rest.replace(fnm, "").strip()

            is_cr = bool(re.search(r"\bCR\b", line, re.I) or line.strip().endswith("CR"))

            desc_parts = [rest]
            j = i + 1
            while j < n:
                next_l = lines[j].strip()
                if not next_l or next_l.startswith("--- PAGE "):
                    break
                if DATE_AMEX_TEXT_RE.match(next_l):
                    break
                if any(hdr in next_l for hdr in [
                    "New domestic transactions", "New overseas transactions",
                    "Total of", "OTHER ACCOUNT TRANSACTIONS", "Installment Plan Transactions",
                    "Payment Advice", "Payment Information", "Summary of New", "Drop Boxes", "Please return"
                ]):
                    break

                if re.search(r"\bCR\b", next_l, re.I):
                    is_cr = True

                cleaned_desc_l = re.sub(r"\b(CR|Card\s+Number\s+XXXX-XXXXXX-\d+|SINGAPORE\s+DOLLAR|USD|EUR|GBP|AUD)\b", "", next_l, flags=re.I).strip()
                if cleaned_desc_l:
                    desc_parts.append(cleaned_desc_l)

                j += 1
                if j - i >= 2:
                    break

            i = j
            clean_desc_str = clean_desc(desc_parts)

            if re.search(r"PAYMENT RECEIVED|TRANSFERRED TO INSTALLMENT PLAN|REFUND|CASHBACK", clean_desc_str, re.I):
                is_cr = True

            debit = None if is_cr else amt
            credit = amt if is_cr else None

            transactions.append({
                "date": date_str,
                "description": clean_desc_str,
                "debit": debit,
                "credit": credit,
                "balance": None
            })
            continue

        i += 1

    # Running balance calculation
    if opening_bal is not None:
        curr_bal = opening_bal
        for t in transactions:
            if t.get("credit") is not None:
                curr_bal -= t["credit"]
            if t.get("debit") is not None:
                curr_bal += t["debit"]
            t["balance"] = round(curr_bal, 2)

    return transactions


# ── Universal Multi-Page Generic Statement Parser (Zero AI Tokens) ────────

def parse_generic_statement(lines, pdf_path=None):
    """
    Universal Pure-Python Multi-Page Bank Statement Parser.
    Extracts all rows across all pages without dropping transactions.
    """
    if not lines:
        return []

    rows = []
    n = len(lines)

    for i, line in enumerate(lines):
        raw_clean = re.sub(r"^[|\[{(~#_\\/ -]+", "", line.strip())
        if not raw_clean or raw_clean.startswith("--- PAGE "):
            continue

        normalized = normalize_numbers_in_line(raw_clean)
        m = DATE_RE.match(normalized)
        if not m:
            continue

        date_str = m.group(1)
        num_matches = NUMBER_RE.findall(normalized)
        if not num_matches:
            continue

        floats = []
        for nm in num_matches:
            val = to_float(nm)
            if val is not None:
                floats.append(val)

        if not floats:
            continue

        balance = floats[-1]
        amount, debit, credit, sign = None, None, None, None

        if len(floats) >= 3:
            cand_deb = floats[-3]
            cand_cred = floats[-2]
            if re.search(r"\bDr\b|/DR/|\bDR\b", normalized, re.IGNORECASE):
                debit = cand_deb if cand_deb != 0 else cand_cred
                sign = "debit"
            elif re.search(r"\bCr\b|/CR/|\bCR\b", normalized, re.IGNORECASE):
                credit = cand_cred if cand_cred != 0 else cand_deb
                sign = "credit"
            else:
                amount = cand_cred if cand_cred != 0 else cand_deb
        elif len(floats) == 2:
            amount = floats[0]
            balance = floats[1]
        elif len(floats) == 1:
            balance = floats[0]

        desc_line = normalized[m.end():].strip()
        for nm in num_matches:
            desc_line = desc_line.replace(nm, "").strip()

        prev_line = lines[i - 1].strip() if i > 0 else ""
        next_line = lines[i + 1].strip() if i + 1 < n else ""

        desc_parts = []
        if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", prev_line, re.IGNORECASE):
            desc_parts.append(prev_line)

        desc_parts.append(desc_line)

        if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", next_line, re.IGNORECASE):
            if not NUMBER_RE.search(next_line):
                desc_parts.append(next_line)

        full_desc = clean_desc(desc_parts)

        # Skip summary lines
        if re.search(r"Opening Balance|Balance B/F|Closing Balance|Statement Summary|Total Withdrawal|Total Deposit", full_desc, re.IGNORECASE) and len(floats) == 1:
            continue

        rows.append({
            "date": date_str,
            "description": full_desc,
            "amount": amount,
            "debit": debit,
            "credit": credit,
            "balance": balance,
            "sign": sign,
        })

    return assign_debit_credit(rows, lines)


# ── Bank Parsers Registry & Signatures ─────────────────────────────────────

BANK_PARSERS = {
    "pnb": parse_pnb,
    "ubi": parse_ubi,
    "icici": parse_icici,
    "idfc": parse_idfc,
    "indusind": parse_indusind,
    "kotak": parse_kotak,
    "sbi": parse_sbi,
    "axis": parse_axis,
    "hdfc": parse_hdfc,
    "idbi": parse_idbi,
    "bob": parse_bob,
    "canara": parse_canara,
    "hsbc": parse_hsbc,
    "federal": parse_federal,
    "yesbank": parse_yesbank,
    "creditcard": parse_credit_card,
    "amex": parse_amex,
}

BANK_NAMES = {
    "axis": "Axis Bank",
    "icici": "ICICI Bank",
    "sbi": "State Bank of India",
    "kotak": "Kotak Mahindra Bank",
    "hdfc": "HDFC Bank",
    "pnb": "Punjab National Bank",
    "ubi": "Union Bank of India",
    "idfc": "IDFC First Bank",
    "indusind": "IndusInd Bank",
    "idbi": "IDBI Bank",
    "bob": "Bank of Baroda",
    "canara": "Canara Bank",
    "hsbc": "HSBC Bank",
    "federal": "Federal Bank",
    "yesbank": "Yes Bank",
    "creditcard": "Credit Card Statement Parser",
    "amex": "American Express",
    "other": "Other Bank (AI Parser)",
}

BANK_SIGNATURES = [
    (re.compile(r"AMERICAN EXPRESS|americanexpress|AEBC Card", re.IGNORECASE), parse_amex),
    (re.compile(r"HDFC0\d|HDFC BANK|HO HDFC BANK|SDEC BANK", re.IGNORECASE), parse_hdfc),
    (re.compile(r"\bPNB\b|PUNB\d|PUNJAB NATIONAL BANK", re.IGNORECASE), parse_pnb),
    (re.compile(r"UBIN\d|UNION BANK", re.IGNORECASE), parse_ubi),
    (re.compile(r"ICIC\d|ICICI BANK|khayaal aapka", re.IGNORECASE), parse_icici),
    (re.compile(r"IDFB\d|IDFC FIRST", re.IGNORECASE), parse_idfc),
    (re.compile(r"INDB\d|INDUSIND", re.IGNORECASE), parse_indusind),
    (re.compile(r"KKBK\d|KOTAK", re.IGNORECASE), parse_kotak),
    (re.compile(r"SBIN\d|STATE BANK OF INDIA", re.IGNORECASE), parse_sbi),
    (re.compile(r"UTIB\d|AXIS BANK", re.IGNORECASE), parse_axis),
    (re.compile(r"IBKL\d|IDBI BANK", re.IGNORECASE), parse_idbi),
    (re.compile(r"BARB0\d|BANK OF BARODA", re.IGNORECASE), parse_bob),
    (re.compile(r"CNRB0\d|CANARA BANK", re.IGNORECASE), parse_canara),
    (re.compile(r"HSBC0\d|HSBC BANK|Statement of Accounts\s*Account Statement", re.IGNORECASE), parse_hsbc),
    (re.compile(r"FDRL0\d|FEDERAL BANK", re.IGNORECASE), parse_federal),
    (re.compile(r"YESB0\d|YES BANK", re.IGNORECASE), parse_yesbank),
    (re.compile(r"CREDIT CARD|CARD STATEMENT|MINIMUM AMOUNT DUE|TOTAL AMOUNT DUE|Purchases & Debits", re.IGNORECASE), parse_credit_card),
]

def detect_bank_parser(lines):
    sample = " ".join(lines[:60])
    for sig_re, parser_fn in BANK_SIGNATURES:
        if sig_re.search(sample):
            return parser_fn
    return None


# ── AI Self-Learning Engine ────────────────────────────────────────────────

def call_llm(provider, prompt, max_tokens=4096):
    if provider["kind"] == "openai":
        resp = requests.post(
            provider["url"],
            headers={"Authorization": f"Bearer {provider['api_key']}", "Content-Type": "application/json"},
            json={"model": provider["model"], "messages": [{"role": "user", "content": prompt}],
                  "temperature": 0, "max_tokens": max_tokens},
            timeout=40,
        )
        if resp.status_code != 200:
            raise RuntimeError(f"{provider['name']} error {resp.status_code}: {resp.text[:300]}")
        data = resp.json()
        choices = data.get("choices", [])
        if not choices:
            raise ValueError("No choices in LLM response")
        return choices[0].get("message", {}).get("content") or ""

    elif provider["kind"] == "gemini":
        url = provider["url"].format(model=provider["model"]) + f"?key={provider['api_key']}"
        resp = requests.post(
            url,
            json={"contents": [{"parts": [{"text": prompt}]}],
                  "generationConfig": {"temperature": 0, "maxOutputTokens": max_tokens}},
            timeout=40,
        )
        if resp.status_code != 200:
            raise RuntimeError(f"{provider['name']} error {resp.status_code}: {resp.text[:300]}")
        data = resp.json()
        candidates = data.get("candidates", [])
        if not candidates:
            raise ValueError("No candidates in Gemini response")
        parts = candidates[0].get("content", {}).get("parts", [])
        return parts[0].get("text", "") or ""

    raise ValueError(f"Unknown provider kind: {provider['kind']}")

def get_multi_page_sample(lines_input, lines_per_page=35):
    if not lines_input:
        return ""
    if isinstance(lines_input, str):
        lines_input = lines_input.split("\n")
    sample = lines_input[:70]
    return "\n".join(sample)

def get_ai_parser(lines_or_sample):
    if isinstance(lines_or_sample, list):
        sample_text = get_multi_page_sample(lines_or_sample)
        all_lines = lines_or_sample
    else:
        sample_text = str(lines_or_sample)
        all_lines = sample_text.split("\n")

    prompt = f"""You are an expert Python developer and financial statement parser.
Below are sample text lines extracted from a bank statement:

--- STATEMENT SAMPLE ---
{sample_text[:3000]}
--- END SAMPLE ---

Write a standalone Python function `parse_statement(lines)` that extracts all financial transactions.
Return ONLY Python code inside ```python ``` block:
```python
def parse_statement(lines):
    transactions = []
    # your logic here
    return assign_debit_credit(transactions, lines)
```
"""
    for provider in PROVIDERS:
        if not provider.get("api_key"):
            continue
        try:
            raw_text = call_llm(provider, prompt, max_tokens=2048)
            code_match = re.search(r"```python\s*([\s\S]*?)```", raw_text, re.IGNORECASE)
            if code_match:
                func_code = code_match.group(1).strip()
                local_ns = {}
                safe_ns = {
                    "re": re, "datetime": datetime, "to_float": to_float,
                    "clean_desc": clean_desc, "parse_any_date": parse_any_date,
                    "assign_debit_credit": assign_debit_credit, "len": len, "range": range
                }
                exec(func_code, safe_ns, local_ns)
                if "parse_statement" in local_ns:
                    fn = local_ns["parse_statement"]
                    test_res = fn(all_lines)
                    if isinstance(test_res, list) and len(test_res) > 0:
                        return fn, f"Other Bank ({provider['name'].upper()} AI Parser)", func_code
        except Exception:
            continue

    raise RuntimeError("AI layout learning unavailable. Falling back to universal engine.")


# ── Global Caches & Pipeline ───────────────────────────────────────────────

CACHE_FILE = os.path.join(os.path.dirname(__file__), "learned_parsers_cache.json")
DYNAMIC_LEARNED_PARSERS = {}
PARSED_STATEMENTS_CACHE = {}

def load_learned_parsers_from_disk():
    if os.path.exists(CACHE_FILE):
        try:
            with open(CACHE_FILE, "r", encoding="utf-8") as f:
                saved = json.load(f)
            for slug_or_sig, code_str in saved.items():
                local_ns = {}
                safe_ns = {
                    "re": re, "datetime": datetime, "to_float": to_float,
                    "clean_desc": clean_desc, "parse_any_date": parse_any_date,
                    "assign_debit_credit": assign_debit_credit, "len": len, "range": range
                }
                exec(code_str, safe_ns, local_ns)
                if "parse_statement" in local_ns:
                    DYNAMIC_LEARNED_PARSERS[slug_or_sig] = local_ns["parse_statement"]
        except Exception:
            pass

load_learned_parsers_from_disk()

def save_learned_parser_to_disk(slug_or_sig, code_str):
    saved = {}
    if os.path.exists(CACHE_FILE):
        try:
            with open(CACHE_FILE, "r", encoding="utf-8") as f:
                saved = json.load(f)
        except Exception:
            saved = {}
    saved[slug_or_sig] = code_str
    try:
        with open(CACHE_FILE, "w", encoding="utf-8") as f:
            json.dump(saved, f, indent=2)
    except Exception:
        pass

def clear_all_caches():
    DYNAMIC_LEARNED_PARSERS.clear()
    PARSED_STATEMENTS_CACHE.clear()
    if os.path.exists(CACHE_FILE):
        try:
            with open(CACHE_FILE, "w", encoding="utf-8") as f:
                json.dump({}, f)
        except Exception:
            pass

def parse_statement_pdf(pdf_bytes, bank_slug=None, page_numbers=None, password=None, progress_callback=None):
    """
    Blazing Fast 4-Layer Statement Parser Pipeline:
    Layer 1: Instant In-Memory Cache Check.
    Layer 2: Fast Spatial Line Extraction (PyMuPDF < 0.05s).
    Layer 3: Native High-Performance Bank Parser / Universal Engine.
    Layer 4: Intelligent Parallel OCR & AI Layout Fallback.
    """
    file_hash = hashlib.md5(pdf_bytes).hexdigest()
    cache_key = f"{file_hash}_{bank_slug or ''}_{page_numbers or ''}"
    if cache_key in PARSED_STATEMENTS_CACHE:
        cached_df = PARSED_STATEMENTS_CACHE[cache_key]
        if cached_df is not None and not cached_df.empty:
            return cached_df.copy()

    if progress_callback:
        progress_callback(10, "Extracting PDF text and table layout...")

    lines = extract_pdf_lines(pdf_bytes, page_numbers=page_numbers, password=password, progress_callback=progress_callback)

    transactions = []
    bank_name = BANK_NAMES.get(bank_slug, bank_slug.upper() if bank_slug else "Unknown") if bank_slug else None

    # Try matching specific parser first
    if bank_slug and bank_slug in BANK_PARSERS and bank_slug != "other":
        parser = BANK_PARSERS[bank_slug]
        if progress_callback:
            progress_callback(40, f"Parsing {bank_name} statement...")
        try:
            transactions = parser(lines)
        except Exception:
            transactions = []

    # If no bank selected or parsed 0 rows, auto-detect
    if not transactions:
        parser = detect_bank_parser(lines)
        if parser:
            detected_name = parser.__name__.replace("parse_", "").upper()
            try:
                transactions = parser(lines)
                if transactions:
                    bank_name = bank_name or detected_name
            except Exception:
                transactions = []

    # Universal Engine Fallback (Fast, Pure-Python, Multi-Page)
    if not transactions or len(transactions) <= 2:
        if progress_callback:
            progress_callback(60, "Running Universal Statement Engine...")
        gen_txs = parse_generic_statement(lines)
        if len(gen_txs) > len(transactions):
            transactions = gen_txs
            bank_name = bank_name or "Generic Engine"

    # AI Layout Revamp Fallback (Only if entries are missing or < 3)
    if not transactions or len(transactions) <= 2:
        if progress_callback:
            progress_callback(75, "Learning custom statement layout...")
        try:
            ai_fn, ai_bank_name, func_code = get_ai_parser(lines)
            ai_txs = ai_fn(lines)
            if isinstance(ai_txs, list) and len(ai_txs) > len(transactions):
                transactions = ai_txs
                bank_name = f"{bank_name or 'Bank'} (AI Revamped)"
        except Exception:
            pass

    df = pd.DataFrame(transactions, columns=["date", "description", "debit", "credit", "balance"])
    df.insert(0, "bank_name", bank_name or "Unknown")

    if not df.empty:
        if len(PARSED_STATEMENTS_CACHE) > 100:
            PARSED_STATEMENTS_CACHE.clear()
        PARSED_STATEMENTS_CACHE[cache_key] = df

    return df

def excel_bytes_from_df(df, filename="statement.xlsx"):
    buffer = BytesIO()
    if df is not None and not df.empty:
        df = df.copy()
        for col in ["debit", "credit", "balance"]:
            if col in df.columns:
                df[col] = pd.to_numeric(df[col], errors="coerce")

        if "balance" in df.columns and "description" in df.columns:
            has_opening = any("opening" in str(d).lower() or "b/f" in str(d).lower() for d in df["description"])
            if not has_opening and len(df) > 0:
                first_row = df.iloc[0]
                first_bal = first_row.get("balance")
                first_deb = first_row.get("debit")
                first_cred = first_row.get("credit")
                if pd.notna(first_bal):
                    bname = str(first_row.get("bank_name", "")).lower()
                    is_cc = "credit card" in bname or "amex" in bname or "american express" in bname
                    if is_cc:
                        if pd.notna(first_cred) and first_cred > 0:
                            open_bal = first_bal + first_cred
                        elif pd.notna(first_deb) and first_deb > 0:
                            open_bal = first_bal - first_deb
                        else:
                            open_bal = first_bal
                    else:
                        if pd.notna(first_cred) and first_cred > 0:
                            open_bal = first_bal - first_cred
                        elif pd.notna(first_deb) and first_deb > 0:
                            open_bal = first_bal + first_deb
                        else:
                            open_bal = first_bal

                    first_date = first_row.get("date", "")
                    bank_name = first_row.get("bank_name", "")
                    open_row = pd.DataFrame([{
                        "bank_name": bank_name,
                        "date": first_date,
                        "description": "Opening Balance / Balance B/F",
                        "debit": None,
                        "credit": None,
                        "balance": round(float(open_bal), 2)
                    }])
                    df = pd.concat([open_row, df], ignore_index=True)

    with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="Transactions", index=False)
        worksheet = writer.sheets["Transactions"]

        from openpyxl.styles import Font, PatternFill, Alignment
        from openpyxl.utils import get_column_letter

        header_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid")
        num_format = "#,##0.00"

        for col_idx in range(1, len(df.columns) + 1):
            cell = worksheet.cell(row=1, column=col_idx)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center", vertical="center")

        for row_idx in range(2, len(df) + 2):
            for col_idx, col_name in enumerate(df.columns, start=1):
                cell = worksheet.cell(row=row_idx, column=col_idx)
                if col_name in ["debit", "credit", "balance"]:
                    if cell.value is not None and isinstance(cell.value, (int, float)):
                        cell.number_format = num_format
                        cell.alignment = Alignment(horizontal="right")
                elif col_name == "date":
                    cell.alignment = Alignment(horizontal="center")

        for col in worksheet.columns:
            max_len = 0
            col_letter = get_column_letter(col[0].column)
            for cell in col:
                val_str = str(cell.value or "")
                if len(val_str) > max_len:
                    max_len = len(val_str)
            worksheet.column_dimensions[col_letter].width = max(max_len + 4, 12)

    buffer.seek(0)
    return buffer


def excel_bytes_from_multi_statements(file_dfs, combined_df):
    """
    Builds a multi-tab Excel workbook from multiple parsed statements:
    - Sheet 1: 'Consolidated' with all transactions across all statements
    - Subsequent Sheets: Individual statement tabs named after each file
    """
    buffer = BytesIO()
    with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
        combined_df.to_excel(writer, sheet_name="Consolidated", index=False)
        used_names = set(["Consolidated"])

        for fname, df in file_dfs:
            base_sname = re.sub(r"[\\/*?:\[\]]", "", fname.replace(".pdf", "").replace(".PDF", "")).strip()[:28]
            sname = base_sname or "Statement"
            counter = 1
            while sname in used_names:
                sname = f"{base_sname}_{counter}"[:31]
                counter += 1
            used_names.add(sname)
            df.to_excel(writer, sheet_name=sname, index=False)

        from openpyxl.styles import Font, PatternFill, Alignment
        from openpyxl.utils import get_column_letter

        header_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid")
        num_format = "#,##0.00"

        for sname in writer.sheets:
            worksheet = writer.sheets[sname]
            for col_idx in range(1, worksheet.max_column + 1):
                cell = worksheet.cell(row=1, column=col_idx)
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = Alignment(horizontal="center", vertical="center")

            for row_idx in range(2, worksheet.max_row + 1):
                for col_idx in range(1, worksheet.max_column + 1):
                    cell = worksheet.cell(row=row_idx, column=col_idx)
                    col_header = str(worksheet.cell(row=1, column=col_idx).value or "").lower()
                    if col_header in ["debit", "credit", "balance"]:
                        if cell.value is not None and isinstance(cell.value, (int, float)):
                            cell.number_format = num_format
                            cell.alignment = Alignment(horizontal="right")
                    elif col_header in ["date"]:
                        cell.alignment = Alignment(horizontal="center")

            for col in worksheet.columns:
                max_len = 0
                col_letter = get_column_letter(col[0].column)
                for cell in col:
                    val_str = str(cell.value or "")
                    if len(val_str) > max_len:
                        max_len = len(val_str)
                worksheet.column_dimensions[col_letter].width = max(max_len + 4, 12)

    buffer.seek(0)
    return buffer