File size: 118,347 Bytes
d598032
 
 
 
 
9e15897
d598032
 
9e15897
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
d598032
 
 
 
 
 
 
 
 
9e15897
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
b2aa446
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
d598032
85f794d
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
d598032
 
 
 
 
 
 
 
9e15897
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
7f72224
 
 
 
 
 
9e15897
 
7f72224
 
 
9e15897
 
7f72224
9e15897
 
 
7f72224
 
9e15897
 
 
 
 
7f72224
9e15897
 
7f72224
9e15897
7f72224
9e15897
 
 
 
 
 
 
7f72224
9e15897
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
7f72224
 
 
 
 
 
 
 
9e15897
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
7f72224
 
 
 
9e15897
 
 
b2aa446
9e15897
 
 
 
 
 
 
 
b2aa446
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
d598032
9e15897
d598032
 
9e15897
 
d598032
9e15897
 
 
d598032
 
 
 
 
9e15897
 
d598032
 
 
 
 
 
 
 
9e15897
d598032
 
 
9e15897
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
d598032
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
9e15897
 
d598032
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
9e15897
 
 
7f72224
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
d598032
 
 
 
 
7f72224
9e15897
 
7f72224
 
d598032
 
 
 
 
 
 
9e15897
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9e15897
 
b2aa446
 
d598032
9e15897
b2aa446
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
"""Payouts App: each Amazon settlement as a waterfall, its tie to economics, the open settlement and owned items.

Sources (connector tables, read only): `amzn_settlements` (`ut_amzn_settlements`) and `amzn_economics`
(`ut_amzn_economics`). A settlement has one header row (Amazon's `total_amount`, the deposit) and many
line rows (`amount`). The waterfall sums the lines into nine buckets and ties them to the header.
Bank matches read `account_bank_statement_line` and resolve currency labels in the same tenant mirror.

Store documents (tenant runtime store, App owned, never the mirror and never Amazon):
  payouts_settings  {default_owner, bank_match_days}
  payouts_items     {items: {settlement_id: item}}

Rule 13: this module imports no other App. Deleting it and its two documents leaves every
`ut_amzn_*` table intact. Rule 1: nothing caps settlements, lines or items. Each document is one JSON
value under the store's 128 MB ceiling, which holds about 200,000 items.
"""
import atexit
import contextlib
import datetime as dt
import hashlib
import json
import re
import shutil
import statistics
import tempfile
import threading
import time
from pathlib import Path
from core.connector_spec import odoo_model_table_key

KEY_SETTINGS = "payouts_settings"
KEY_ITEMS = "payouts_items"
STORE_KEYS = (KEY_SETTINGS, KEY_ITEMS)
TENANT_ZERO = "royal-imports"

SETTLEMENTS = "amzn_settlements"
ECONOMICS = "amzn_economics"
SOURCE_KEY = "ut_amzn_settlements"
BANK_TABLE = "account_bank_statement_line"
BANK_SOURCE_KEY = odoo_model_table_key("account.bank.statement.line")
# Match the dataset age rule without importing the API layer.
BANK_STALE_AFTER_DAYS = 7
BANK_MATCH_DAYS = 7

STATUSES = ("Open", "Asked Amazon", "Resolved", "Ignore")
DECISIONS = ("approved", "dismissed")
DUE_DAYS = 14
CENT = 0.005
TIE_TOLERANCE = 0.02
PAGE = 200

LOAN_REPAYMENT = "Automated Loan Repayment"
WITHHELD_TAX = "ItemWithheldTax"

#: Waterfall buckets in QI7 order. `other` keeps the lines summing to the deposit.
BUCKETS = (("sales", "Sales"), ("fees", "Fees"), ("refunds", "Refunds"), ("ad_cost", "Ad cost"),
           ("reserve", "Reserve"), ("loan_repayment", "Loan repayment"), ("withheld_tax", "Withheld tax"),
           ("other", "Other"))
LABELS = dict(BUCKETS)
EXCLUSIONS = ("loan_repayment", "withheld_tax")
#: Other-transaction descriptions that are a fee or a charge.
FEE_DESCRIPTIONS = {"subscription fee", "storage fee", "storagerenewalbilling", "fbainboundtransportationfee",
                    "removalcomplete", "inbound defect fee"}
FEE_TYPES = {"fbafees", "amazonfees", "servicefee"}
FEE_ESTIMATES = "amzn_fba_fee_estimates"
FEE_ESTIMATE_SOURCE = "ut_amzn_fba_fee_estimates"
FULFILLMENT_DESCRIPTION = "FBAPerUnitFulfillmentFee"
PLACEMENT_TYPE = "FBA Inbound Placement Service Fee"
DEFECT_TYPE = "Inbound Defect Fee"
DEFECT_DESCRIPTION = "Inbound Defect Fee - Shipments delivered to the wrong location"
FEE_STATUSES = ("Open", "Disputed")
# Each policy has a different clock. A charge date cannot stand in for a notification date.
FULFILLMENT_POLICY_URL = "https://sellercentral.amazon.com/help/hub/reference/external/GL7U4JFSDXUTQAJ?locale=en-US"
FULFILLMENT_DISPUTE_DAYS = 90
INBOUND_POLICY_URL = "https://sellercentral.amazon.com/help/hub/reference/external/GL5XA3MNXAJKJE8E?locale=en"
INBOUND_DISPUTE_DAYS = 90
INBOUND_DUPLICATE_POLICY_FROM = "2026-01-15"
FEE_POLICY_READ_DATE = "2026-10-28"
#: Marketplace to currency, used only when the economics report names no currency column.
MARKETPLACE_CURRENCY = {"ATVPDKIKX0DER": "USD", "A2EUQ1WTGCTBG2": "CAD", "A1AM78C64UM0Y8": "MXN"}

_REQUIRED = ("settlement_id", "amount", "total_amount", "currency", "transaction_type", "amount_type",
             "amount_description", "posted_date", "settlement_start_date", "settlement_end_date", "deposit_date")
_ADS_TOTAL = re.compile(r"ads_[a-z0-9_]+_amount")
_POSTED = ("COALESCE(TRY_CAST(left(CAST(posted_date AS VARCHAR), 10) AS DATE), "
           "CAST(try_strptime(left(CAST(posted_date AS VARCHAR), 10), '%d.%m.%Y') AS DATE))")
SNAPSHOT_TTL_S = 3600

#: The settlements list (C4): one module table row per settlement, split into sales, fees and other.
TABLE_KEY = "ut_amazon_payouts"
TABLE_LABEL = "Payouts settlements"
TABLE_WRITER = "automation"
TABLE_SOURCES = (SOURCE_KEY, "ut_amzn_economics")
#: Bump when the fields or the split change, so the next build rewrites every row.
TABLE_VERSION = 1
TIES, GAP, NO_TOTAL = "Ties", "Gap", "No total"
#: Every bucket that is neither sales nor fees is an other cost.
OTHER_BUCKETS = tuple(k for k, _label in BUCKETS if k not in ("sales", "fees"))


class SourceUnavailable(RuntimeError):
    """The Amazon settlements are not stored for this tenant, or lack a column this module reads."""


class TableRefused(RuntimeError):
    """The module table helper refused to create or write the settlements table."""


def _now():
    return dt.datetime.now(dt.timezone.utc)


def _stamp(at):
    return at.astimezone(dt.timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


def _iso(moment):
    return _stamp(moment) if moment else None


def _money(value):
    return round(float(value or 0.0) + 0.0, 2)


# --------------------------------------------------------------------------- dates

def parse_moment(text):
    """A UTC datetime from Amazon's ISO or DD.MM.YYYY text, with or without a time; None when unreadable."""
    if isinstance(text, dt.datetime):
        return text if text.tzinfo else text.replace(tzinfo=dt.timezone.utc)
    if isinstance(text, dt.date):
        return dt.datetime(text.year, text.month, text.day, tzinfo=dt.timezone.utc)
    s = str(text or "").strip()
    if s.upper().endswith(" UTC"):
        s = s[:-4].strip()
    if not s:
        return None
    moment = None
    try:
        moment = dt.datetime.fromisoformat(s.replace("Z", "+00:00"))
    except ValueError:
        for fmt in ("%d.%m.%Y %H:%M:%S", "%d.%m.%Y %H:%M", "%d.%m.%Y"):
            try:
                moment = dt.datetime.strptime(s, fmt)
                break
            except ValueError:
                continue
    if moment is None:
        return None
    return moment.replace(tzinfo=dt.timezone.utc) if moment.tzinfo is None else moment.astimezone(dt.timezone.utc)


def parse_date(text):
    """The calendar date of Amazon's date text (ISO or DD.MM.YYYY), or None."""
    moment = parse_moment(text)
    return moment.date() if moment else None


def _day(moment):
    return f"{moment.day} {moment:%b %Y}" if moment else "an unknown date"


def _month_label(month):
    try:
        return dt.date.fromisoformat(month + "-01").strftime("%B %Y")
    except (TypeError, ValueError):
        return str(month)


# --------------------------------------------------------------------------- store

def _rt(tenant_key, st=None):
    """The tenant runtime store handle (get and update), or raise when it cannot be resolved."""
    if st is not None:
        return st
    from harness import amazon_store
    rt = amazon_store._runtime_for(tenant_key)
    if rt is None:
        raise RuntimeError(f"The store for tenant {tenant_key!r} could not be opened.")
    return rt


def _doc(st, key):
    value = st.get(key)
    return value if isinstance(value, dict) else {}


# --------------------------------------------------------------------------- mirror reads

def _mirror_path(tenant_key, st):
    from harness import amazon_store
    rt = st if hasattr(st, "datastore_path") else None
    return amazon_store._tenant_mirror_path(tenant_key, rt)


@contextlib.contextmanager
def _reader(tenant_key, st):
    """A read-only cursor on the tenant mirror, or None when the mirror file does not exist."""
    try:
        path = Path(_mirror_path(tenant_key, st))
    except Exception:                                                  # noqa: BLE001
        path = None
    if path is None or not path.exists():
        yield None
        return
    from harness import datastore
    try:
        shared = Path(datastore.DB_PATH).resolve() == path.resolve()
    except Exception:                                                  # noqa: BLE001
        shared = False
    # The process holds one writer on its own mirror: a separate read-only connect would refuse.
    if shared:
        cur = datastore.ro_cursor()
        try:
            yield cur
        finally:
            cur.close()
        return
    import duckdb
    con = duckdb.connect(str(path), read_only=True)
    try:
        yield con
    finally:
        con.close()


def _columns(cur, table):
    try:
        return {r[0] for r in cur.execute(
            "SELECT column_name FROM information_schema.columns WHERE table_name = ?", [table]).fetchall()}
    except Exception:                                                  # noqa: BLE001
        return set()


_SNAPSHOT = {}
_SNAPSHOT_LOCK = threading.Lock()


def snapshot_cursor(tenant=TENANT_ZERO, tables=(SETTLEMENTS, ECONOMICS)):
    """An in-memory DuckDB over the stored mirror snapshot. Downloads only; writes nothing back."""
    key = (tenant, tuple(tables))
    with _SNAPSHOT_LOCK:
        held = _SNAPSHOT.get(key)
        if held and time.monotonic() - held[1] < SNAPSHOT_TTL_S:
            return held[0].cursor()
        import duckdb
        from harness import runtime as R
        from harness import mirror_store
        rt = R.get_runtime(tenant)
        mirror = mirror_store.for_connector("amazon")
        raw = mirror._download(rt, "manifest.json")
        if raw is None:
            raise SourceUnavailable("No Amazon settlements are stored for this workspace yet.")
        manifest = json.loads(raw)
        if manifest.get("tenant") != tenant:
            raise SourceUnavailable("The stored Amazon data names another workspace.")
        folder = Path(tempfile.mkdtemp(prefix="payouts-"))
        atexit.register(shutil.rmtree, folder, True)
        con = duckdb.connect()
        for name in tables:
            meta = (manifest.get("tables") or {}).get(name)
            if not isinstance(meta, dict):
                continue
            paths = []
            for index, part in enumerate(meta.get("parts") or [meta]):
                blob_key = part.get("key") or f"{name}.parquet"
                data = mirror._download(rt, blob_key)
                if data is None or (part.get("sha256") and hashlib.sha256(data).hexdigest() != part["sha256"]):
                    raise SourceUnavailable(f"The stored {name} snapshot failed its checksum.")
                path = folder / f"{name}.{index:04d}.parquet"
                path.write_bytes(data)
                paths.append(str(path))
            if paths:
                listed = ", ".join("'" + p.replace("'", "''") + "'" for p in paths)
                con.execute(f"CREATE VIEW {name} AS SELECT * FROM read_parquet([{listed}], union_by_name=true)")
        if held:
            with contextlib.suppress(Exception):
                held[0].close()
        _SNAPSHOT[key] = (con, time.monotonic())
        return con.cursor()


@contextlib.contextmanager
def _source(tenant_key, st=None, con=None):
    """`(cursor, source)`: the given cursor, the local mirror when it holds settlements, else the stored snapshot."""
    if con is not None:
        yield con, "given"
        return
    with _reader(tenant_key, st) as cur:
        if cur is not None and _columns(cur, SETTLEMENTS):
            yield cur, "local mirror"
            return
    cur = snapshot_cursor(tenant_key)
    try:
        yield cur, "stored snapshot"
    finally:
        with contextlib.suppress(Exception):
            cur.close()


# --------------------------------------------------------------------------- classification

def bucket(transaction_type, amount_type, description):
    """The waterfall bucket key of one settlement line, by the fixed precedence."""
    tt = str(transaction_type or "").strip()
    at = str(amount_type or "").strip()
    ad = str(description or "").strip()
    tl, al, dl = tt.lower(), at.lower(), ad.lower()
    if tl == LOAN_REPAYMENT.lower() or al == LOAN_REPAYMENT.lower():
        return "loan_repayment"
    if al == WITHHELD_TAX.lower() or "withholding" in dl:
        return "withheld_tax"
    if "reserve" in dl:
        return "reserve"
    if al == "cost of advertising" or dl == "cost of advertising":
        return "ad_cost"
    if tl.startswith("refund"):
        return "refunds"
    if al in ("itemprice", "promotion"):
        return "sales"
    if (al in ("itemfees", "itemfee") or tl in FEE_TYPES
            or (tl.startswith("other-transaction") and (dl in FEE_DESCRIPTIONS or al in FEE_DESCRIPTIONS))):
        return "fees"
    return "other"


# --------------------------------------------------------------------------- reads

def _require(cur):
    cols = _columns(cur, SETTLEMENTS)
    if not cols:
        raise SourceUnavailable("No Amazon settlements are stored for this workspace yet.")
    missing = [c for c in _REQUIRED if c not in cols]
    if missing:
        raise SourceUnavailable("Amazon settlements lack the columns " + ", ".join(missing) + ".")
    return cols


def _load(cur):
    """`(headers, groups)`: one header per settlement and line sums by settlement, type and posted month."""
    _require(cur)
    headers = {}
    for sid, start, end, deposit, total, currency, n in cur.execute(
            "SELECT CAST(settlement_id AS VARCHAR), any_value(CAST(settlement_start_date AS VARCHAR)), "
            "any_value(CAST(settlement_end_date AS VARCHAR)), any_value(CAST(deposit_date AS VARCHAR)), "
            "any_value(TRY_CAST(total_amount AS DOUBLE)), any_value(CAST(currency AS VARCHAR)), count(*) "
            f"FROM {SETTLEMENTS} WHERE total_amount IS NOT NULL AND settlement_id IS NOT NULL GROUP BY 1").fetchall():
        headers[sid] = {"start": start, "end": end, "deposit": deposit, "total": total,
                        "currency": (currency or "").strip(), "headers": int(n)}
    groups = cur.execute(
        "SELECT CAST(settlement_id AS VARCHAR), COALESCE(CAST(transaction_type AS VARCHAR), ''), "
        "COALESCE(CAST(amount_type AS VARCHAR), ''), COALESCE(CAST(amount_description AS VARCHAR), ''), "
        f"strftime({_POSTED}, '%Y-%m'), SUM(TRY_CAST(amount AS DOUBLE)), count(*) "
        f"FROM {SETTLEMENTS} WHERE amount IS NOT NULL AND settlement_id IS NOT NULL "
        "GROUP BY 1, 2, 3, 4, 5").fetchall()
    return headers, groups


def _economics(cur, month=None):
    """`(currency, {month: net proceeds}, basis, first day, last day)` from the economics report; empty when absent."""
    cols = _columns(cur, ECONOMICS)
    if not cols or "start_date" not in cols:
        return None, {}, None, None, None
    currency = None
    named = (["currency_code"] if "currency_code" in cols else []) + sorted(
        c for c in cols if c.endswith("_currency_code") and c != "currency_code")
    for col in named:
        currency = cur.execute(f'SELECT any_value("{col}") FROM {ECONOMICS} WHERE "{col}" IS NOT NULL').fetchone()[0]
        if currency:
            break
    if not currency and "marketplace_id" in cols:
        market = cur.execute(f"SELECT any_value(marketplace_id) FROM {ECONOMICS}").fetchone()[0]
        currency = MARKETPLACE_CURRENCY.get(str(market or ""))
    if "net_proceeds_total_amount" in cols:
        expr, basis = "COALESCE(TRY_CAST(net_proceeds_total_amount AS DOUBLE), 0)", "net proceeds"
    else:
        ads = " + ".join(f'COALESCE("{c}", 0)' for c in sorted(cols) if _ADS_TOTAL.fullmatch(c)
                         and "_charge_" not in c) or "0"
        sales = "COALESCE(sales_net_product_sales_amount, 0)" if "sales_net_product_sales_amount" in cols else "0"
        fees = "COALESCE(fees_total_amount, 0)" if "fees_total_amount" in cols else "0"
        expr, basis = f"{sales} - {fees} - ({ads})", "net sales minus fees and ads"
    first, last = cur.execute(f"SELECT min(left(CAST(start_date AS VARCHAR), 10)), "
                              f"max(left(CAST(start_date AS VARCHAR), 10)) FROM {ECONOMICS}").fetchone()
    where, args = "", []
    if month:
        where = (" WHERE left(CAST(start_date AS VARCHAR), 7) = ? OR left(CAST(start_date AS VARCHAR), 7) = "
                 f"(SELECT max(left(CAST(e.start_date AS VARCHAR), 7)) FROM {ECONOMICS} e "
                 "WHERE left(CAST(e.start_date AS VARCHAR), 7) < ? "
                 "AND CAST(left(CAST(e.start_date AS VARCHAR), 7) || '-01' AS DATE) >= TRY_CAST(? AS DATE) "
                 "AND last_day(TRY_CAST(left(CAST(e.start_date AS VARCHAR), 10) AS DATE)) <= TRY_CAST(? AS DATE) "
                 f"AND EXISTS (SELECT 1 FROM {SETTLEMENTS} s WHERE "
                 f"strftime({_POSTED}, '%Y-%m') = left(CAST(e.start_date AS VARCHAR), 7)))")
        args = [month, month, first, last]
    months = {m: float(v or 0.0) for m, v in cur.execute(
        f"SELECT left(CAST(start_date AS VARCHAR), 7), SUM({expr}) FROM {ECONOMICS}{where} GROUP BY 1", args).fetchall() if m}
    return (str(currency).strip() if currency else None), months, basis, parse_date(first), parse_date(last)


def _waterfall(parts, deposit):
    """Bucket rows in QI7 order, each with its parts by absolute amount, then the deposit row."""
    rows = []
    for key, label in BUCKETS:
        mine = sorted((p for p in parts if p["bucket"] == key), key=lambda p: -abs(p["amount"]))
        rows.append({"key": key, "label": label, "amount": _money(sum(p["amount"] for p in mine)),
                     "lines": sum(p["lines"] for p in mine),
                     "parts": [{k: (_money(v) if k == "amount" else v) for k, v in p.items() if k != "bucket"}
                               for p in mine]})
    rows.append({"key": "deposit", "label": "Deposit", "amount": None if deposit is None else _money(deposit),
                 "lines": 0, "parts": []})
    return rows


def sums_to(rows, deposit):
    """True when the bucket rows (deposit row excluded) sum to the deposit to the cent."""
    if deposit is None:
        return False
    total = sum(r["amount"] for r in rows if r["key"] != "deposit")
    return abs(total - float(deposit)) < CENT


def _build(headers, groups):
    """Every settlement with its waterfall, gap and parsed dates, newest end first."""
    parts, months, undated = {}, {}, {}
    for sid, tt, at, ad, month, amount, n in groups:
        key = (tt, at, ad)
        mine = parts.setdefault(sid, {})
        part = mine.setdefault(key, {"transactionType": tt, "amountType": at, "description": ad,
                                     "amount": 0.0, "lines": 0, "bucket": bucket(tt, at, ad)})
        part["amount"] += float(amount or 0.0)
        part["lines"] += int(n)
        if month:
            per = months.setdefault(sid, {}).setdefault(month, {})
            per[part["bucket"]] = per.get(part["bucket"], 0.0) + float(amount or 0.0)
        else:
            undated[sid] = undated.get(sid, 0) + int(n)
    out = []
    for sid in sorted(set(headers) | set(parts)):
        head = headers.get(sid) or {}
        mine = list((parts.get(sid) or {}).values())
        lines_total = sum(p["amount"] for p in mine)
        total = head.get("total")
        start, end, deposit_at = (parse_moment(head.get(k)) for k in ("start", "end", "deposit"))
        gap = None if total is None else _money(lines_total - total)
        reconciled = total is not None and abs(lines_total - total) < CENT
        currency = head.get("currency") or ""
        out.append({
            "id": sid, "currency": currency, "start": _iso(start), "end": _iso(end),
            "depositDate": _iso(deposit_at), "startText": head.get("start"), "endText": head.get("end"),
            "depositText": head.get("deposit"),
            "title": f"Settlement {_day(start)} to {_day(end)}" + (f", {currency}" if currency else ""),
            "amazonTotal": None if total is None else _money(total), "linesTotal": _money(lines_total),
            "gap": gap, "reconciled": reconciled, "headerRows": head.get("headers", 0),
            "lineCount": sum(p["lines"] for p in mine), "undatedLines": undated.get(sid, 0),
            "waterfall": _waterfall(mine, total),
            "_start": start, "_end": end, "_deposit": deposit_at, "_months": months.get(sid) or {},
        })
    far = dt.datetime.min.replace(tzinfo=dt.timezone.utc)
    out.sort(key=lambda s: (s["_end"] or far, s["id"]), reverse=True)
    return out


def _reason(row):
    cur = row["currency"] or ""
    if row["amazonTotal"] is None:
        return f"The lines total {row['linesTotal']:,.2f} {cur} but Amazon's settlement total is missing.".replace("  ", " ")
    return (f"The lines total {row['linesTotal']:,.2f} {cur} but Amazon's total is {row['amazonTotal']:,.2f} {cur}, "
            f"a gap of {row['gap']:,.2f}.").replace("  ", " ")


def _public(row, *, parts=True):
    out = {k: v for k, v in row.items() if not k.startswith("_")}
    if not row["reconciled"]:
        out["reason"] = _reason(row)
    if not parts:
        out["waterfall"] = [{k: v for k, v in r.items() if k != "parts"} for r in row["waterfall"]]
    return out


# --------------------------------------------------------------------------- tie to economics

def _month_totals(rows, currency):
    """`{month: {bucket: amount}}` over every settlement in one currency, by posted month."""
    out = {}
    for row in rows:
        if row["currency"] != currency:
            continue
        for month, per in row["_months"].items():
            into = out.setdefault(month, {})
            for key, amount in per.items():
                into[key] = into.get(key, 0.0) + amount
    return out


def _bounds(month):
    """The first and last calendar day of a `YYYY-MM` month."""
    first = dt.date.fromisoformat(month + "-01")
    return first, (first.replace(day=28) + dt.timedelta(days=4)).replace(day=1) - dt.timedelta(days=1)


def _covered(month, first_day, last_day):
    """True when economics covers every day of the month (only a whole month ties, F13)."""
    lo, hi = _bounds(month)
    return bool(first_day and last_day and first_day <= lo and last_day >= hi)


def _gap(settled, econ):
    gap = settled - econ
    pct = gap / abs(econ) if econ else None
    return gap, pct, (pct is not None and abs(pct) <= TIE_TOLERANCE)


def _reference(totals, months, first_day, last_day, before=None):
    """The tie of the newest month economics covers whole that also has settlements, or None."""
    for month in sorted(months, reverse=True):
        if (before and month >= before) or month not in totals or not _covered(month, first_day, last_day):
            continue
        per = totals[month]
        settled = sum(v for k, v in per.items() if k not in EXCLUSIONS)
        gap, pct, matched = _gap(settled, months[month])
        return {"month": month, "monthLabel": _month_label(month), "settlements": _money(settled),
                "economics": _money(months[month]), "gap": _money(gap),
                "gapPct": None if pct is None else round(pct * 100, 2), "matched": matched,
                "message": (f"{_month_label(month)} matched within {abs(pct) * 100:.1f} percent." if matched
                            else f"{_month_label(month)} differs by {abs(pct or 0) * 100:.1f} percent.")}
    return None


def _tie(row, rows, economics):
    """The settlement's posted month against economics, with the two exclusions named; final only for a whole month."""
    currency, months, basis, first_day, last_day = economics
    if not currency or not months:
        return {"available": False, "message": "The economics report is not stored for this workspace yet."}
    if row["currency"] != currency:
        return {"available": False, "currency": row["currency"], "economicsCurrency": currency,
                "message": f"Economics covers {currency} only. This settlement is in {row['currency'] or 'an unknown currency'}."}
    month = row["_end"].strftime("%Y-%m") if row["_end"] else None
    if not month:
        return {"available": False, "message": "This settlement has no readable end date."}
    totals = _month_totals(rows, currency)
    per = totals.get(month, {})
    including = sum(per.values())
    excluded = {key: per.get(key, 0.0) for key in EXCLUSIONS}
    settled = including - sum(excluded.values())
    econ = months.get(month)
    own = {key: next((r["amount"] for r in row["waterfall"] if r["key"] == key), 0.0) for key in EXCLUSIONS}
    exclusions = [{"key": key, "label": LABELS[key], "monthAmount": _money(excluded[key]),
                   "settlementAmount": _money(own[key])} for key in EXCLUSIONS]
    final = _covered(month, first_day, last_day)
    reference = None if final else _reference(totals, months, first_day, last_day, before=month)
    if econ is None:
        return {"available": False, "month": month, "currency": currency, "exclusions": exclusions, "final": False,
                "reference": reference, "message": f"Economics has no rows for {_month_label(month)}."}
    gap, pct, matched = _gap(settled, econ)
    out = {"available": True, "month": month, "monthLabel": _month_label(month), "currency": currency,
           "basis": basis, "settlementsIncluding": _money(including), "settlements": _money(settled),
           "economics": _money(econ), "gap": _money(gap), "gapPct": None if pct is None else round(pct * 100, 2),
           "matched": matched, "final": True, "tolerancePct": TIE_TOLERANCE * 100, "exclusions": exclusions,
           "economicsFrom": first_day.isoformat() if first_day else None,
           "economicsThrough": last_day.isoformat() if last_day else None, "reference": None,
           "message": (f"{_month_label(month)} settlements without loan repayment and withheld tax are "
                       f"{settled:,.2f} {currency} against {econ:,.2f} in economics, a gap of {gap:,.2f}"
                       + (f" ({pct * 100:.1f} percent)." if pct is not None else "."))}
    if not final:
        lo, _hi = _bounds(month)
        if first_day and first_day > lo:
            why = (f"Economics starts {_day(first_day)}, so {_month_label(month)} is not complete in economics. "
                   "This tie is not final.")
        else:
            why = (f"{_month_label(month)} is not complete in economics yet (through {_day(last_day)}). "
                   "The tie is final after the month closes.")
        out.update(final=False, matched=None, reference=reference, message=why)
    return out


# --------------------------------------------------------------------------- streams, open settlement, next deposit

def _median_seconds(values, default):
    values = [v for v in values if v is not None and v > 0]
    return statistics.median(values) if values else default


def streams(rows, now=None):
    """Settlement streams by currency and chaining, each with its open settlement; largest 90-day deposits first."""
    now = parse_moment(now) if now is not None else _now()
    chains = []
    dated = sorted((r for r in rows if r["_start"] and r["_end"]), key=lambda r: (r["_start"], r["id"]))
    for row in dated:
        home = next((c for c in chains if c["currency"] == row["currency"] and c["rows"][-1]["_end"] == row["_start"]),
                    None)
        if home is None:
            home = {"currency": row["currency"], "rows": []}
            chains.append(home)
        home["rows"].append(row)
    out = []
    since = now - dt.timedelta(days=90)
    for chain in chains:
        mine = chain["rows"]
        length = _median_seconds([(r["_end"] - r["_start"]).total_seconds() for r in mine], 86400 * 14)
        lag = _median_seconds([(r["_deposit"] - r["_end"]).total_seconds() for r in mine if r["_deposit"]], 86400 * 2)
        newest = max(mine, key=lambda r: r["_end"])
        close = newest["_end"] + dt.timedelta(seconds=length)
        expected_deposit = close + dt.timedelta(seconds=lag)
        active = (now - newest["_end"]).total_seconds() <= 2 * length + 7 * 86400
        recent = sum(r["amazonTotal"] or 0.0 for r in mine if r["_deposit"] and since <= r["_deposit"] <= now)
        out.append({"currency": chain["currency"], "settlements": len(mine), "active": active,
                    "medianDays": round(length / 86400, 2), "depositLagDays": round(lag / 86400, 2),
                    "deposits90d": _money(recent), "newestSettlement": newest["id"],
                    "open": None if not active else {
                        "start": _iso(newest["_end"]), "expectedClose": _iso(close),
                        "expectedDeposit": _iso(expected_deposit), "currency": chain["currency"],
                        "title": f"Open settlement from {_day(newest['_end'])}, {chain['currency']}".rstrip(", ")},
                    "_expected": expected_deposit if active else None})
    out.sort(key=lambda s: (-s["deposits90d"], s["currency"], s["newestSettlement"]))
    for index, stream in enumerate(out, 1):
        stream["id"] = f"{stream['currency'] or 'unknown'}-{index}"
    return out


def next_deposit(rows, stream_rows, now=None):
    """The earliest issued deposit after `now`, else the earliest expected one across open settlements."""
    now = parse_moment(now) if now is not None else _now()
    pending = sorted((r for r in rows if r["_deposit"] and r["_deposit"] > now), key=lambda r: (r["_deposit"], r["id"]))
    if pending:
        row = pending[0]
        return {"expected": False, "date": _iso(row["_deposit"]), "amount": row["amazonTotal"],
                "currency": row["currency"], "settlementId": row["id"]}
    expected = sorted((s for s in stream_rows if s["_expected"]), key=lambda s: s["_expected"])
    if not expected:
        return None
    ahead = [s for s in expected if s["_expected"] > now]
    stream = ahead[0] if ahead else expected[-1]
    return {"expected": True, "date": _iso(stream["_expected"]), "amount": None, "currency": stream["currency"],
            "stream": stream["id"], "overdue": not ahead}


def _strip_streams(stream_rows):
    return [{k: v for k, v in s.items() if not k.startswith("_")} for s in stream_rows]


# --------------------------------------------------------------------------- public reads

def _all(tenant_key, st=None, con=None):
    with _source(tenant_key, st, con) as (cur, source):
        headers, groups = _load(cur)
        economics = _economics(cur)
    return _build(headers, groups), economics, source


def _window(rows, start=None, end=None):
    """Keep settlements whose end date is in [start, end]; a `YYYY-MM` bound covers the whole month."""
    lo = hi = None
    if start:
        lo = parse_date(str(start) + "-01" if len(str(start)) == 7 else start)
    if end:
        text = str(end)
        if len(text) == 7:
            first = parse_date(text + "-01")
            hi = (first.replace(day=28) + dt.timedelta(days=4)).replace(day=1) - dt.timedelta(days=1) if first else None
        else:
            hi = parse_date(text)
    if (start and lo is None) or (end and hi is None):
        raise ValueError("A window bound must be a month such as 2026-09 or a date such as 2026-09-30.")
    return [r for r in rows if r["_end"] and (lo is None or r["_end"].date() >= lo)
            and (hi is None or r["_end"].date() <= hi)]


def _page(rows, cursor, limit):
    try:
        offset = int(cursor or 0)
    except (TypeError, ValueError):
        raise ValueError("The cursor is not valid. Start again from the first page.") from None
    if offset < 0:
        raise ValueError("The cursor is not valid. Start again from the first page.")
    if limit is None:
        return rows[offset:], None
    size = max(1, int(limit))
    chunk = rows[offset:offset + size]
    return chunk, (str(offset + size) if offset + size < len(rows) else None)


def settlements(tenant_key, *, st=None, con=None, currency=None, start=None, end=None, cursor=None, limit=PAGE):
    """Every settlement newest first, with bucket totals and reconciliation, paged by cursor. No cap."""
    rows, _economics_, source = _all(tenant_key, st, con)
    rows = _window(rows, start, end)
    if currency:
        rows = [r for r in rows if r["currency"] == str(currency).strip().upper()]
    chunk, nxt = _page(rows, cursor, limit)
    return {"settlements": [_public(r, parts=False) for r in chunk], "cursor": nxt, "total": len(rows),
            "source": source}


def settlement(tenant_key, settlement_id, *, st=None, con=None, bank_access=True):
    """One settlement: the waterfall with every part, its gap and its tie to economics. KeyError when unknown."""
    rows, economics, source = _all(tenant_key, st, con)
    row = next((r for r in rows if r["id"] == str(settlement_id)), None)
    if row is None:
        raise KeyError(settlement_id)
    out = _public(row)
    out["tie"] = _tie(row, rows, economics)
    out["source"] = source
    with _source(tenant_key, st, con) as (cur, _source_):
        out["bankMatch"] = _bank_detail(cur, _settlement_sql(cur), out,
            _match_days(tenant_key, st, con), _now(), bank_access)
    return out


def overview(tenant_key, *, st=None, con=None, now=None):
    """Counts, unreconciled settlements, open settlements, the next deposit and the newest settlement's tie."""
    now = parse_moment(now) if now is not None else _now()
    rows, economics, source = _all(tenant_key, st, con)
    stream_rows = streams(rows, now)
    newest = rows[0] if rows else None
    return {
        "settlementCount": len(rows), "reconciledCount": sum(1 for r in rows if r["reconciled"]),
        "unreconciled": [{"id": r["id"], "title": r["title"], "gap": r["gap"], "reason": _reason(r)}
                         for r in rows if not r["reconciled"]],
        "currencies": sorted({r["currency"] for r in rows if r["currency"]}),
        "economicsCurrency": economics[0],
        "openSettlements": [s["open"] for s in stream_rows if s["open"]],
        "nextDeposit": next_deposit(rows, stream_rows, now), "streams": _strip_streams(stream_rows),
        "latest": None if newest is None else {**_public(newest, parts=False), "tie": _tie(newest, rows, economics)},
        "source": source, "asOf": _stamp(now),
    }


# --------------------------------------------------------------------------- bounded screen reads

def _moment_sql(name):
    text = f"replace(trim(CAST({name} AS VARCHAR)), ' UTC', '')"
    return (f"COALESCE(TRY_CAST({text} AS TIMESTAMPTZ) AT TIME ZONE 'UTC', "
            f"try_strptime({text}, ['%d.%m.%Y %H:%M:%S', '%d.%m.%Y %H:%M', '%d.%m.%Y']))")


def _settlement_sql(cur):
    """Aggregate in SQL before any settlement crosses into Python."""
    _require(cur)
    return f"""WITH heads AS (
        SELECT CAST(settlement_id AS VARCHAR) AS id,
          any_value(CAST(settlement_start_date AS VARCHAR)) FILTER (WHERE total_amount IS NOT NULL) AS start_text,
          any_value(CAST(settlement_end_date AS VARCHAR)) FILTER (WHERE total_amount IS NOT NULL) AS end_text,
          any_value(CAST(deposit_date AS VARCHAR)) FILTER (WHERE total_amount IS NOT NULL) AS deposit_text,
          any_value(TRY_CAST(total_amount AS DOUBLE)) AS total,
          trim(COALESCE(any_value(CAST(currency AS VARCHAR)) FILTER (WHERE total_amount IS NOT NULL), '')) AS currency,
          COALESCE(sum(TRY_CAST(amount AS DOUBLE)), 0) AS lines,
          count(*) FILTER (WHERE total_amount IS NOT NULL) AS headers,
          count(*) FILTER (WHERE amount IS NOT NULL) AS line_count
        FROM {SETTLEMENTS} WHERE settlement_id IS NOT NULL GROUP BY 1),
      dated AS (SELECT *, {_moment_sql('start_text')} AS start_at,
        {_moment_sql('end_text')} AS end_at, {_moment_sql('deposit_text')} AS deposit_at FROM heads),
      settlements AS (SELECT *, total IS NOT NULL AND abs(lines - total) < {CENT} AS reconciled FROM dated)"""


def _bounds_page(cursor, limit, total):
    _page([], cursor, 1)
    size = max(1, int(limit))
    start = int(cursor or 0)
    if start >= total:
        start = max(0, (total - 1) // size * size)
    return start, size


def _summary_row(values):
    sid, currency, start, end, deposit, total, lines, reconciled = values
    start, end, deposit = (parse_moment(v) for v in (start, end, deposit))
    return {"id": sid, "currency": currency, "start": _iso(start), "end": _iso(end),
            "depositDate": _iso(deposit), "amazonTotal": None if total is None else _money(total),
            "linesTotal": _money(lines), "gap": None if total is None else _money(lines - total),
            "reconciled": reconciled,
            "title": f"Settlement {_day(start)} to {_day(end)}" + (f", {currency}" if currency else "")}


_SUMMARY_FIELDS = "id, currency, start_at, end_at, deposit_at, total, lines, reconciled"


def _bank_lines_sql(cur):
    """Resolve Odoo currency identity from mirror labels, never from a numeric ID guess."""
    cols = _columns(cur, BANK_TABLE)
    if not {"id", "amount", "date", "payment_ref", "currency_id"} <= cols:
        return None
    names = [f"b.{name}" for name in ("currency_name", "currency_id_name") if name in cols]
    join = ""
    if {"id", "name"} <= _columns(cur, "res_currency"):
        join = " LEFT JOIN res_currency c ON b.currency_id = c.id"
        names.append("c.name")
    if not names:
        return None
    currency = "COALESCE(" + ", ".join(
        f"CASE WHEN regexp_full_match(upper(trim(CAST({name} AS VARCHAR))), '[A-Z]{{3}}') "
        f"THEN upper(trim(CAST({name} AS VARCHAR))) END" for name in names) + ", NULL)"
    return (f"SELECT CAST(b.id AS VARCHAR) AS bank_id, TRY_CAST(b.date AS DATE) AS bank_date, "
            "TRY_CAST(b.amount AS DECIMAL(38, 2)) AS bank_amount, "
            f"CAST(b.payment_ref AS VARCHAR) AS payment_ref, {currency} AS bank_currency "
            f"FROM {BANK_TABLE} b{join}")


def _bank_sql(cur, sql, days, now, bank_access=True):
    """Allocate equal-currency deposits to distinct bank lines before filtering or paging."""
    bank_sql = _bank_lines_sql(cur) if bank_access else None
    feed = {"available": bank_sql is not None, "restricted": not bank_access,
            "newestDate": None, "stale": None, "message": "", "matchDays": days}
    if bank_sql is None:
        return sql + """, payouts AS (SELECT *, NULL::VARCHAR AS bank_id, NULL::DATE AS bank_date,
            NULL::DECIMAL(38, 2) AS bank_amount, NULL::VARCHAR AS payment_ref,
            NULL::VARCHAR AS bank_currency FROM settlements)""", feed
    newest = cur.execute(f"SELECT max(bank_date) FROM ({bank_sql})").fetchone()[0]
    latest_deposit = cur.execute(sql + " SELECT max(CAST(deposit_at AS DATE)) FROM settlements "
                                "WHERE deposit_at <= ? AND total IS NOT NULL", [now.replace(tzinfo=None)]).fetchone()[0]
    if newest:
        feed.update(newestDate=newest.isoformat(), stale=(now.date() - newest).days > BANK_STALE_AFTER_DAYS)
        if feed["stale"] or (latest_deposit and newest < latest_deposit):
            feed["message"] = f"Bank lines end on {_day(newest)}"
    # Equal-width date intervals admit a chronological, earliest-line allocation.
    sql = sql.replace("WITH heads AS", "WITH RECURSIVE heads AS", 1)
    sql += f""", bank_lines AS ({bank_sql}), bank_deposits AS (
        SELECT id, upper(currency) AS currency, CAST(total AS DECIMAL(38, 2)) AS deposit_amount,
          CAST(deposit_at AS DATE) AS deposit_day,
          row_number() OVER (PARTITION BY upper(currency), CAST(total AS DECIMAL(38, 2))
            ORDER BY deposit_at, id) AS seq
        FROM settlements WHERE total IS NOT NULL AND deposit_at IS NOT NULL
          AND deposit_at <= TIMESTAMP '{now.replace(tzinfo=None).isoformat()}'),
      bank_walk AS (
        SELECT d.*, b.bank_id, list_filter([b.bank_id], x -> x IS NOT NULL) AS used
        FROM bank_deposits d LEFT JOIN LATERAL (
          SELECT bank_id FROM bank_lines b WHERE b.bank_currency = d.currency
            AND b.bank_amount = d.deposit_amount
            AND date_diff('day', d.deposit_day, b.bank_date) BETWEEN -{days} AND {days}
          ORDER BY bank_date, bank_id LIMIT 1) b ON TRUE WHERE d.seq = 1
        UNION ALL
        SELECT d.*, b.bank_id, list_concat(w.used, list_filter([b.bank_id], x -> x IS NOT NULL))
        FROM bank_walk w JOIN bank_deposits d ON d.currency = w.currency
          AND d.deposit_amount = w.deposit_amount AND d.seq = w.seq + 1
        LEFT JOIN LATERAL (
          SELECT bank_id FROM bank_lines b WHERE b.bank_currency = d.currency
            AND b.bank_amount = d.deposit_amount AND NOT list_contains(w.used, b.bank_id)
            AND date_diff('day', d.deposit_day, b.bank_date) BETWEEN -{days} AND {days}
          ORDER BY bank_date, bank_id LIMIT 1) b ON TRUE),
      payouts AS (SELECT s.*, b.bank_id, b.bank_date, b.bank_amount, b.payment_ref, b.bank_currency
        FROM settlements s LEFT JOIN bank_walk w ON w.id = s.id
        LEFT JOIN bank_lines b ON b.bank_id = w.bank_id)"""
    return sql, feed


_BANK_FIELDS = "bank_id, bank_date, bank_amount, payment_ref, bank_currency"


def _bank_match(row, values, feed, now):
    """Describe a match and suppress missing-bank work beyond the bank feed's last date."""
    bank_id, date, amount, reference, currency = values
    deposit = parse_moment(row.get("depositDate"))
    paid = deposit is not None and deposit <= now and row.get("amazonTotal") is not None
    status = ("restricted" if feed["restricted"] else "unavailable" if not feed["available"]
              else "matched" if bank_id is not None else "unmatched" if paid else "pending")
    newest = parse_date(feed["newestDate"])
    missing = bool(status == "unmatched" and newest and deposit.date() <= newest
                   and (now.date() - deposit.date()).days > feed["matchDays"])
    return {"status": status, "matched": status == "matched", "missing": missing,
            "matchDays": feed["matchDays"],
            "bankLine": None if bank_id is None else {"id": bank_id, "date": date.isoformat(),
                "amount": _money(amount), "paymentRef": reference or "", "currency": currency},
            "difference": None if bank_id is None else _money(row["amazonTotal"] - float(amount))}


def _bank_detail(cur, sql, row, days, now, bank_access):
    sql, feed = _bank_sql(cur, sql, days, now, bank_access)
    values = cur.execute(sql + f" SELECT {_BANK_FIELDS} FROM payouts WHERE id = ?", [row["id"]]).fetchone()
    return _bank_match(row, values, feed, now)


def _match_days(tenant_key, st, con):
    return BANK_MATCH_DAYS if st is None and con is not None else settings(tenant_key, st=st)["bankMatchDays"]


#: KPI tiles over the settlement split: `(id, label)`. Fees and other costs keep their signs.
TILES = (("deposits", "Deposits"), ("sales", "Sales"), ("fees", "Fees"), ("other", "Other costs"))
#: Tiles that show a cost as a positive amount, so a rise reads "+" and paints red.
COST_TILES = ("fees", "other")
TILE_DAYS = 30
TILE_WEEKS = 12
TILE_REFUSED = "This figure did not tie to the settlement lines."


def _tile_windows(now):
    """`(current start, prior start, first week start, end)` as naive UTC timestamps."""
    end = now.astimezone(dt.timezone.utc).replace(tzinfo=None)
    monday = dt.datetime.combine(end.date() - dt.timedelta(days=end.weekday()), dt.time())
    return (end - dt.timedelta(days=TILE_DAYS), end - dt.timedelta(days=2 * TILE_DAYS),
            monday - dt.timedelta(weeks=TILE_WEEKS - 1), end)


def _tile_figures(cur, sql, currency, now):
    """`{span: {tile: amount}}` for `now`, `prior` and each ISO week start, in one SQL aggregate."""
    current, prior, first, end = _tile_windows(now)
    other = ("bucket IN (" + ", ".join(f"'{k}'" for k in OTHER_BUCKETS) + ")") if OTHER_BUCKETS else "FALSE"
    sums = ("sum(deposit), sum(sales), sum(fees), sum(other)")
    rows = cur.execute(sql + f""", tile_lines AS (
        SELECT CAST(settlement_id AS VARCHAR) AS id, {_bucket_sql()} AS bucket,
          TRY_CAST(amount AS DECIMAL(38, 2)) AS amount
        FROM {SETTLEMENTS} WHERE settlement_id IS NOT NULL AND amount IS NOT NULL),
      tile_parts AS (SELECT id, sum(amount) FILTER (WHERE bucket = 'sales') AS sales,
          sum(amount) FILTER (WHERE bucket = 'fees') AS fees, sum(amount) FILTER (WHERE {other}) AS other
        FROM tile_lines GROUP BY id),
      tile_rows AS (SELECT s.deposit_at, CAST(s.total AS DECIMAL(38, 2)) AS deposit,
          COALESCE(p.sales, 0) AS sales, COALESCE(p.fees, 0) AS fees, COALESCE(p.other, 0) AS other
        FROM settlements s LEFT JOIN tile_parts p ON p.id = s.id
        WHERE s.currency = ? AND s.total IS NOT NULL AND s.deposit_at IS NOT NULL AND s.deposit_at <= ?)
      SELECT 'now', NULL, {sums} FROM tile_rows WHERE deposit_at > ?
      UNION ALL SELECT 'prior', NULL, {sums} FROM tile_rows WHERE deposit_at > ? AND deposit_at <= ?
      UNION ALL SELECT 'week', CAST(date_trunc('week', deposit_at) AS DATE), {sums}
        FROM tile_rows WHERE deposit_at >= ? GROUP BY 2""",
                       [currency, end, current, prior, current, first]).fetchall()
    out = {"now": {}, "prior": {}, "weeks": {}}
    for span, week, *amounts in rows:
        into = out["weeks"].setdefault(week.isoformat(), {}) if span == "week" else out[span]
        into.update({key: _money(amount) for (key, _label), amount in zip(TILES, amounts)})
    return out


def _tile_check(cur, currency, now):
    """Rule 8: the tile sums again, straight from header totals and line amounts, in the same windows."""
    _require(cur)
    current, prior, first, end = _tile_windows(now)
    heads = f"""WITH head AS (
        SELECT CAST(settlement_id AS VARCHAR) AS id, max(TRY_CAST(total_amount AS DECIMAL(38, 2))) AS total,
          max(trim(CAST(currency AS VARCHAR))) FILTER (WHERE total_amount IS NOT NULL) AS currency,
          max({_moment_sql('deposit_date')}) FILTER (WHERE total_amount IS NOT NULL) AS paid
        FROM {SETTLEMENTS} WHERE settlement_id IS NOT NULL GROUP BY 1
        HAVING count(total_amount) > 0),
      paid AS (SELECT *, CASE WHEN paid > ? THEN 'now' WHEN paid > ? THEN 'prior' END AS span,
          CASE WHEN paid >= ? THEN CAST(date_trunc('week', paid) AS DATE) END AS week
        FROM head WHERE currency = ? AND total IS NOT NULL AND paid IS NOT NULL AND paid <= ?)"""
    args = [current, prior, first, currency, end]
    out = {"now": {}, "prior": {}, "weeks": {}}

    def put(span, week, key, amount):
        if span is not None:
            out[span].setdefault(key, 0.0)
            out[span][key] = _money(out[span][key] + float(amount or 0))
        if week is not None:
            into = out["weeks"].setdefault(week.isoformat(), {})
            into[key] = _money(into.get(key, 0.0) + float(amount or 0))

    for span, week, amount in cur.execute(heads + " SELECT span, week, sum(total) FROM paid "
                                          "WHERE span IS NOT NULL OR week IS NOT NULL GROUP BY 1, 2", args).fetchall():
        put(span, week, "deposits", amount)
    for span, week, kind, amount in cur.execute(heads + f""" SELECT p.span, p.week, {_bucket_sql()} AS kind,
            sum(TRY_CAST(l.amount AS DECIMAL(38, 2))) FROM paid p JOIN {SETTLEMENTS} l
            ON CAST(l.settlement_id AS VARCHAR) = p.id
            WHERE l.amount IS NOT NULL AND (p.span IS NOT NULL OR p.week IS NOT NULL) GROUP BY 1, 2, 3""",
                                                args).fetchall():
        put(span, week, kind if kind in ("sales", "fees") else "other", amount)
    for spans in (out["now"], out["prior"], *out["weeks"].values()):
        for key, _label in TILES:
            spans.setdefault(key, 0.0)
    return out


def _tiles_tie(figures, check):
    """`{tile: ok}`: each tile's value, prior value and weekly series equal the independent sums."""
    weeks = sorted(set(figures["weeks"]) | set(check["weeks"]))
    zero = {key: 0.0 for key, _label in TILES}
    return {key: all(abs(figures[span].get(key, 0.0) - check[span].get(key, 0.0)) < CENT for span in ("now", "prior"))
            and all(abs(figures["weeks"].get(w, zero).get(key, 0.0) - check["weeks"].get(w, zero).get(key, 0.0)) < CENT
                    for w in weeks)
            for key, _label in TILES}


def _change(value, previous):
    absolute = _money(value - previous)
    percent = round(absolute / abs(previous) * 100, 1) if abs(previous) >= CENT else None
    return {"previous": previous, "absolute": absolute, "percent": percent,
            "reason": None if percent is not None else "No prior period"}


def _tiles(cur, sql, currency, now):
    """Four tiles in one currency: last 30 days, change on the 30 before, one series point per settlement week."""
    if not currency:
        return []
    figures, check = _tile_figures(cur, sql, currency, now), _tile_check(cur, currency, now)
    ties = _tiles_tie(figures, check)
    out = []
    for key, label in TILES:
        tile = {"id": key, "label": label, "currency": currency, "unit": currency,
                "period": f"Last {TILE_DAYS} days", "validated": ties[key], "reason": "", "cost": key in COST_TILES}
        if ties[key]:
            # The tie runs on signed sums. A cost tile then shows its cost as a positive amount.
            sign = -1 if key in COST_TILES else 1
            value, previous = _money(sign * figures["now"].get(key, 0.0)), _money(sign * figures["prior"].get(key, 0.0))
            tile.update(value=value, change=_change(value, previous),
                        series=[{"week": w, "value": _money(sign * figures["weeks"][w].get(key, 0.0))}
                                for w in sorted(figures["weeks"])])
        else:
            tile.update(value=None, change=None, series=[], reason=TILE_REFUSED)
        out.append(tile)
    return out


def _validate_tiles(tenant_key, *, st=None, con=None, now=None):
    """Tie every currency's tiles to the independent header and line sums."""
    now = parse_moment(now) if isinstance(now, str) else now or _now()
    try:
        with _source(tenant_key, st, con) as (cur, _origin):
            sql = _settlement_sql(cur)
            currencies = [r[0] for r in cur.execute(sql + " SELECT DISTINCT currency FROM settlements "
                                                        "WHERE currency <> '' ORDER BY 1").fetchall()]
            ties = {c: _tiles_tie(_tile_figures(cur, sql, c, now), _tile_check(cur, c, now)) for c in currencies}
    except SourceUnavailable as exc:
        return {"ok": False, "byCurrency": {}, "error": str(exc)}
    return {"ok": all(all(t.values()) for t in ties.values()), "byCurrency": ties}


def _screen_kpis(cur, sql, currency, now):
    """Read fixed-size deposit and stream projections with SQL aggregate scans."""
    args = [currency, currency]
    where = "(? IS NULL OR currency = ?)"
    paid = cur.execute(sql + f" SELECT {_SUMMARY_FIELDS} FROM settlements WHERE {where} "
                       "AND deposit_at <= ? AND total IS NOT NULL ORDER BY deposit_at DESC, id DESC LIMIT 1",
                       [*args, now.replace(tzinfo=None)]).fetchone()
    last = None
    if paid:
        previous = cur.execute(sql + " SELECT total, deposit_at FROM settlements WHERE currency = ? "
                               "AND total IS NOT NULL AND (deposit_at, id) < (?, ?) "
                               "ORDER BY deposit_at DESC, id DESC LIMIT 1", [paid[1], paid[4], paid[0]]).fetchone()
        last = {"settlementId": paid[0], "date": _iso(parse_moment(paid[4])), "amount": _money(paid[5]),
                "currency": paid[1], "previousAmount": None if previous is None else _money(previous[0]),
                "previousDate": None if previous is None else _iso(parse_moment(previous[1])),
                "change": None if previous is None else _money(paid[5] - previous[0])}
    pending = cur.execute(sql + f" SELECT {_SUMMARY_FIELDS} FROM settlements WHERE {where} "
                          "AND deposit_at > ? ORDER BY deposit_at, id LIMIT 1",
                          [*args, now.replace(tzinfo=None)]).fetchone()
    # Chain statistics stay in SQL, including medians over the complete history.
    chains = sql + """, ordered AS (
        SELECT *, lag(end_at) OVER (PARTITION BY currency ORDER BY start_at, id) AS previous_end
        FROM settlements WHERE start_at IS NOT NULL AND end_at IS NOT NULL),
      numbered AS (SELECT *, sum(CASE WHEN previous_end = start_at THEN 0 ELSE 1 END)
        OVER (PARTITION BY currency ORDER BY start_at, id) AS chain FROM ordered),
      streams AS (SELECT currency, chain, max(end_at) AS newest,
        first(id ORDER BY end_at DESC, start_at, id) AS newest_id,
        COALESCE(median(epoch(end_at - start_at)) FILTER (WHERE end_at > start_at), 1209600) AS length,
        COALESCE(median(epoch(deposit_at - end_at)) FILTER (WHERE deposit_at > end_at), 172800) AS lag,
        COALESCE(sum(total) FILTER (WHERE deposit_at BETWEEN ? - INTERVAL 90 DAY AND ?), 0) AS recent
        FROM numbered GROUP BY currency, chain),
      ranked AS (SELECT *, row_number() OVER (ORDER BY recent DESC, currency, newest_id) AS stream_rank FROM streams),
      active AS (SELECT *, newest + length * INTERVAL 1 SECOND AS close_at,
        newest + (length + lag) * INTERVAL 1 SECOND AS expected_at FROM ranked
        WHERE epoch(? - newest) <= 2 * length + 604800)"""
    at = now.replace(tzinfo=None)
    opened = cur.execute(chains + f" SELECT currency, newest, close_at, expected_at FROM active WHERE {where} "
                         "ORDER BY stream_rank LIMIT 1", [at, at, at, *args]).fetchone()
    expected = cur.execute(chains + f" SELECT currency, expected_at, stream_rank FROM active WHERE {where} "
                           "ORDER BY expected_at <= ?, CASE WHEN expected_at > ? THEN expected_at END, "
                           "expected_at DESC LIMIT 1", [at, at, at, *args, at, at]).fetchone()
    nxt = ({"expected": False, "date": _iso(parse_moment(pending[4])), "amount": pending[5],
            "currency": pending[1], "settlementId": pending[0]} if pending else
           {"expected": True, "date": _iso(parse_moment(expected[1])), "amount": None,
            "currency": expected[0], "stream": f"{expected[0] or 'unknown'}-{expected[2]}",
            "overdue": expected[1] <= at} if expected else None)
    tile_currency = currency or (paid[1] if paid else None)
    return {"lastDeposit": last, "nextDeposit": nxt, "tileCurrency": tile_currency,
            "tiles": _tiles(cur, sql, tile_currency, now),
            "openSettlement": None if opened is None else {"currency": opened[0],
                "start": _iso(parse_moment(opened[1])), "expectedClose": _iso(parse_moment(opened[2])),
                "expectedDeposit": _iso(parse_moment(opened[3])),
                "title": f"Open settlement from {_day(parse_moment(opened[1]))}, {opened[0]}".rstrip(", ")}}


def screen(tenant_key, *, st=None, con=None, currency=None, view="all", q="", cursor=None, limit=50, now=None,
           bank_access=True):
    """Return one searchable settlement page and fixed-size KPI projections."""
    now = parse_moment(now) if now is not None else _now()
    currency = str(currency).strip().upper() if currency else None
    if view not in ("all", "unreconciled", "unmatched"):
        raise ValueError("Choose all, unreconciled or unmatched settlements.")
    with _source(tenant_key, st, con) as (cur, _source_):
        base_sql = _settlement_sql(cur)
        days = _match_days(tenant_key, st, con)
        sql, feed = _bank_sql(cur, base_sql, days, now, bank_access)
        where = "(? IS NULL OR currency = ?) AND contains(lower(concat_ws(' ', id, currency, start_text, end_text, deposit_text)), ?)"
        args = [currency, currency, str(q or "").strip().lower()]
        unmatched = (f"bank_id IS NULL AND total IS NOT NULL AND deposit_at <= "
                     f"TIMESTAMP '{now.replace(tzinfo=None).isoformat()}'" if feed["available"] else "FALSE")
        total, bad, unbanked = cur.execute(sql + f" SELECT count(*), count(*) FILTER (WHERE NOT reconciled), "
                                         f"count(*) FILTER (WHERE {unmatched}) FROM payouts WHERE {where}", args).fetchone()
        count = {"all": total, "unreconciled": bad, "unmatched": unbanked}[view]
        start, size = _bounds_page(cursor, limit, count)
        clause = {"all": "", "unreconciled": " AND NOT reconciled", "unmatched": f" AND ({unmatched})"}[view]
        rows = cur.execute(sql + f" SELECT {_SUMMARY_FIELDS}, {_BANK_FIELDS} FROM payouts WHERE {where}{clause} "
                           "ORDER BY end_at DESC NULLS LAST, id DESC LIMIT ? OFFSET ?", [*args, size, start]).fetchall()
        currencies = [r[0] for r in cur.execute(base_sql + " SELECT DISTINCT currency FROM settlements "
                                               "WHERE currency <> '' ORDER BY currency").fetchall()]
        source_count = cur.execute(base_sql + " SELECT count(*) FROM settlements").fetchone()[0]
        kpis = _screen_kpis(cur, base_sql, currency, now)
        listed = []
        for values in rows:
            row = _summary_row(values[:8])
            row["bankMatch"] = _bank_match(row, values[8:], feed, now)
            listed.append(row)
    return {"empty": source_count == 0, "asOf": _stamp(now), "currencies": currencies,
            "counts": {"all": total, "unreconciled": bad, "unmatched": unbanked}, "settlements": listed,
            "bankFeed": feed, "bankMatchDays": days,
            "cursor": str(start + size) if start + size < count else None, "offset": start, "limit": size,
            "kpis": {**kpis, "settlements": total, "unreconciled": bad}}


def _bucket_sql():
    tt = "lower(trim(COALESCE(CAST(transaction_type AS VARCHAR), '')))"
    at = "lower(trim(COALESCE(CAST(amount_type AS VARCHAR), '')))"
    ad = "lower(trim(COALESCE(CAST(amount_description AS VARCHAR), '')))"
    quoted = lambda values: ", ".join("'" + v.replace("'", "''") + "'" for v in sorted(values))
    return f"""CASE WHEN {tt} = 'automated loan repayment' OR {at} = 'automated loan repayment' THEN 'loan_repayment'
      WHEN {at} = 'itemwithheldtax' OR contains({ad}, 'withholding') THEN 'withheld_tax'
      WHEN contains({ad}, 'reserve') THEN 'reserve'
      WHEN {at} = 'cost of advertising' OR {ad} = 'cost of advertising' THEN 'ad_cost'
      WHEN starts_with({tt}, 'refund') THEN 'refunds'
      WHEN {at} IN ('itemprice', 'promotion') THEN 'sales'
      WHEN {at} IN ('itemfees', 'itemfee') OR {tt} IN ({quoted(FEE_TYPES)})
        OR (starts_with({tt}, 'other-transaction') AND ({ad} IN ({quoted(FEE_DESCRIPTIONS)})
          OR {at} IN ({quoted(FEE_DESCRIPTIONS)}))) THEN 'fees' ELSE 'other' END"""


# --------------------------------------------------------------------------- settlements table (C4)

def _table_field(key, label, ftype, description, **extra):
    out = {"key": key, "label": label, "type": ftype, "origin": "loopable", "valueAuthority": "computed",
           "scope": "tenant", "description": description}
    if ftype == "currency":
        out["format"] = {"decimals": 2}
    out.update(extra)
    return out


#: No bank field: bank state stays in the settlement drawer behind the bank-source grant.
TABLE_FIELDS = (
    _table_field("deposit_date", "Deposit date", "date", "When Amazon paid the deposit."),
    _table_field("settlement_id", "Settlement ID", "text", "Amazon settlement id."),
    _table_field("currency", "Currency", "text", "Currency of the amounts."),
    _table_field("sales", "Sales", "currency", "Item price and promotion lines."),
    _table_field("fees", "Fees", "currency", "Amazon fee lines."),
    _table_field("other", "Other costs", "currency",
                 "Refunds, ad cost, reserve, loan repayment, withheld tax and other lines."),
    _table_field("deposit", "Deposit", "currency", "Amazon's settlement total."),
    _table_field("tie", "Tie", "select", "Whether the lines sum to the deposit.",
                 options=[TIES, GAP, NO_TOTAL],
                 optionColors={TIES: "#EBF6EF", GAP: "#FCEEEC", NO_TOTAL: "#FBF4E0"}),
)
_TABLE_LOCK = threading.Lock()


def _module_tables():
    """The one door to S82-T21's module table helpers."""
    from core import module_tables
    return module_tables


def _table_present(st):
    try:
        import core.user_tables as ut
        return ut.get_one(TABLE_KEY, st) is not None
    except Exception:                                                  # noqa: BLE001
        return False


def _table_stamp(cur):
    """The source identity: a change to any settlement line or header changes it."""
    _require(cur)
    cols = ", ".join(f"CAST({c} AS VARCHAR)" for c in _REQUIRED)
    count, total = cur.execute(f"SELECT count(*), COALESCE(sum(hash({cols})), 0) FROM {SETTLEMENTS}").fetchone()
    return f"v{TABLE_VERSION}:{int(count)}:{int(total)}"


def _split_sql(cur):
    """Each settlement's sales, fees and other costs in one aggregate, exact to the cent."""
    other = ("p.bucket IN (" + ", ".join(f"'{k}'" for k in OTHER_BUCKETS) + ")") if OTHER_BUCKETS else "FALSE"
    return _settlement_sql(cur) + f""", split AS (
        SELECT CAST(settlement_id AS VARCHAR) AS id, {_bucket_sql()} AS bucket,
          sum(TRY_CAST(amount AS DECIMAL(38, 2))) AS amount
        FROM {SETTLEMENTS} WHERE settlement_id IS NOT NULL AND amount IS NOT NULL GROUP BY 1, 2)
      SELECT s.id, s.currency, s.deposit_at, s.total, s.lines, s.reconciled,
        COALESCE(sum(p.amount) FILTER (WHERE p.bucket = 'sales'), 0),
        COALESCE(sum(p.amount) FILTER (WHERE p.bucket = 'fees'), 0),
        COALESCE(sum(p.amount) FILTER (WHERE {other}), 0)
      FROM settlements s LEFT JOIN split p ON p.id = s.id
      GROUP BY s.id, s.currency, s.deposit_at, s.total, s.lines, s.reconciled
      ORDER BY s.deposit_at DESC NULLS LAST, s.id DESC"""


def table_cells(cur):
    """`{settlement_id: cells}` for every settlement. No cap (rule 1)."""
    out = {}
    for sid, currency, deposit_at, total, _lines, reconciled, sales, fees, other in cur.execute(_split_sql(cur)).fetchall():
        moment = parse_moment(deposit_at)
        out[sid] = {"deposit_date": moment.date().isoformat() if moment else None, "settlement_id": sid,
                    "currency": currency or None, "sales": _money(sales), "fees": _money(fees),
                    "other": _money(other), "deposit": None if total is None else _money(total),
                    "tie": NO_TOTAL if total is None else TIES if reconciled else GAP}
    return out


def settlement_table(tenant_key, *, st=None, con=None):
    """Build `ut_amazon_payouts` when the source stamp moved; otherwise write nothing. Returns `{table, rows, stamp}`."""
    st = _rt(tenant_key, st)
    with _TABLE_LOCK:
        with _source(tenant_key, st, con) as (cur, _origin):
            stamp = _table_stamp(cur)
            held = _doc(st, KEY_SETTINGS)
            if held.get("table_stamp") == stamp and _table_present(st):
                return {"table": TABLE_KEY, "rows": int(held.get("table_rows") or 0), "stamp": stamp, "written": False}
            computed = table_cells(cur)
        helper = _module_tables()
        try:
            keys = helper.ensure_module_table(st, TABLE_WRITER,
                                             [{"key": TABLE_KEY, "label": TABLE_LABEL,
                                               "fields": [dict(f) for f in TABLE_FIELDS],
                                               "sources": TABLE_SOURCES}], owner=TABLE_WRITER)
            key, = keys
            helper.ensure_everyone_grant(st, [key], owner=TABLE_WRITER)
            counts = helper.write_module_rows(st, key, computed, key_column="settlement_id",
                                              writer=TABLE_WRITER, via="payouts", label=TABLE_LABEL,
                                              clear_keys=(), origin="Amazon settlements")
        except Exception as exc:                                       # noqa: BLE001
            if type(exc).__name__.endswith("Refused"):
                raise TableRefused(str(exc)) from exc
            raise

        def _keep(doc):
            doc = doc if isinstance(doc, dict) else {}
            doc.update(table_stamp=stamp, table_rows=len(computed))
            return doc
        st.update(KEY_SETTINGS, _keep)
    return {"table": key, "rows": len(computed), "stamp": stamp, "written": True,
            "created": int((counts or {}).get("created") or 0), "updated": int((counts or {}).get("updated") or 0)}


def screen_settlement(tenant_key, settlement_id, *, st=None, con=None, bucket_key=None, cursor=None, limit=50, q="",
                      bank_access=True, now=None):
    """Read one settlement, aggregate its tie, and page each waterfall bucket's parts in SQL."""
    with _source(tenant_key, st, con) as (cur, source):
        sql = _settlement_sql(cur)
        values = cur.execute(sql + f" SELECT {_SUMMARY_FIELDS}, start_text, end_text, deposit_text, headers, line_count "
                             "FROM settlements WHERE id = ?", [str(settlement_id)]).fetchone()
        if values is None:
            raise KeyError(settlement_id)
        row = _summary_row(values[:8])
        row["bankMatch"] = _bank_detail(cur, sql, row, _match_days(tenant_key, st, con),
                                       parse_moment(now) if now is not None else _now(), bank_access)
        row.update(startText=values[8], endText=values[9], depositText=values[10], headerRows=values[11], lineCount=values[12])
        row["undatedLines"] = cur.execute(f"SELECT count(*) FROM {SETTLEMENTS} WHERE "
                                         f"CAST(settlement_id AS VARCHAR) = ? AND amount IS NOT NULL AND {_POSTED} IS NULL",
                                         [str(settlement_id)]).fetchone()[0]
        parts_sql = (f"WITH parts AS (SELECT {_bucket_sql()} AS bucket, "
                     "COALESCE(CAST(transaction_type AS VARCHAR), '') AS tt, "
                     "COALESCE(CAST(amount_type AS VARCHAR), '') AS atyp, "
                     "COALESCE(CAST(amount_description AS VARCHAR), '') AS ad, "
                     f"sum(TRY_CAST(amount AS DOUBLE)) AS amount, count(*) AS n FROM {SETTLEMENTS} "
                     "WHERE CAST(settlement_id AS VARCHAR) = ? AND amount IS NOT NULL GROUP BY 1, 2, 3, 4)")
        sums = {k: (amount, n, count) for k, amount, n, count in cur.execute(
            parts_sql + " SELECT bucket, sum(amount), sum(n), count(*) FROM parts GROUP BY 1", [str(settlement_id)]).fetchall()}
        waterfall = []
        if bucket_key is not None and bucket_key not in LABELS:
            raise ValueError("That waterfall bucket does not exist.")
        for key, label in BUCKETS:
            amount, n, count = sums.get(key, (0, 0, 0))
            filters = [str(settlement_id), key, str(q or "").strip().lower()]
            where = "bucket = ? AND contains(lower(concat_ws(' ', tt, atyp, ad)), ?)"
            total = cur.execute(parts_sql + f" SELECT count(*) FROM parts WHERE {where}", filters).fetchone()[0]
            start, size = _bounds_page(cursor if bucket_key == key else None, limit, total)
            chosen = cur.execute(parts_sql + f" SELECT tt, atyp, ad, amount, n FROM parts WHERE {where} "
                                 "ORDER BY abs(amount) DESC, tt, atyp, ad LIMIT ? OFFSET ?", [*filters, size, start]).fetchall() \
                if bucket_key in (None, key) else []
            waterfall.append({"key": key, "label": label, "amount": _money(amount), "lines": n,
                              "parts": [{"transactionType": tt, "amountType": at, "description": ad,
                                         "amount": _money(a), "lines": n} for tt, at, ad, a, n in chosen],
                              "total": total, "offset": start,
                              "cursor": str(start + size) if start + size < total else None})
        waterfall.append({"key": "deposit", "label": "Deposit", "amount": row["amazonTotal"], "lines": 0, "parts": []})
        month = values[3].strftime("%Y-%m") if values[3] else None
        economics = _economics(cur, month=month or "0001-01")
        months = list(economics[1])
        totals = {}
        if months:
            grouped = cur.execute(sql + f" SELECT strftime({_POSTED}, '%Y-%m'), {_bucket_sql()}, "
                                  f"sum(TRY_CAST(amount AS DOUBLE)) FROM {SETTLEMENTS} s "
                                  "JOIN settlements h ON CAST(s.settlement_id AS VARCHAR) = h.id "
                                  f"WHERE h.currency = ? AND strftime({_POSTED}, '%Y-%m') IN "
                                  "(" + ",".join("?" for _ in months) + ") GROUP BY 1, 2", [row["currency"], *months]).fetchall()
            for m, key, amount in grouped:
                totals.setdefault(m, {})[key] = float(amount or 0)
        row.update(waterfall=waterfall, _end=parse_moment(values[3]))
        tie = _tie(row, [{"currency": row["currency"], "_months": totals}], economics)
    if not row["reconciled"]:
        row["reason"] = _reason(row)
    row.pop("_end")
    return {**row, "tie": tie, "source": source}


def screen_item(tenant_key, row, *, st=None, bank_access=True):
    """Read one owned override or project the same default item without writing during a GET."""
    st = _rt(tenant_key, st)
    from core import store, store_pg
    resolve = getattr(st, "_document_store", None)
    backend = resolve() if callable(resolve) else st
    if backend is store and store.backend() == "pg":
        backend = store._d()
    if isinstance(backend, store_pg.PgStore):
        if backend.tenant_slug != str(tenant_key).strip().lower():
            raise PermissionError("The Payouts store belongs to another tenant.")
        namespace = getattr(st, "store_key", lambda name: name)
        with store_pg._pool().connection() as con:
            result = con.execute(store_pg._q(
                "SELECT (value #> %s::text[]) - 'history' FROM {tbl} WHERE key = %s",
                "store_kv", backend.tenant_slug), (["items", row["id"]], namespace(KEY_ITEMS))).fetchone()
        item = result[0] if result else None
    else:
        item = (_doc(st, KEY_ITEMS).get("items") or {}).get(row["id"])
    if item and not bank_access:
        item = (None if item.get("kind") == "bank_missing" else
                {**item, "causes": [c for c in item.get("causes", ["waterfall"]) if c != "bank_missing"]})
    return item or {"id": row["id"], "status": "Open", "owner": settings(tenant_key, st=st)["defaultOwner"],
                    "due": (_now() + dt.timedelta(days=DUE_DAYS)).date().isoformat()}


# --------------------------------------------------------------------------- fee checks

@contextlib.contextmanager
def _fee_source(tenant_key, st=None, con=None, tables=(SETTLEMENTS, FEE_ESTIMATES)):
    """Read fee inputs from one tenant snapshot without restoring or changing the tenant store."""
    if con is not None:
        yield con
        return
    with _reader(tenant_key, st) as cur:
        if cur is not None and _columns(cur, SETTLEMENTS) and _columns(cur, FEE_ESTIMATES):
            yield cur
            return
    cur = snapshot_cursor(tenant_key, tables=tuple(tables))
    try:
        yield cur
    finally:
        cur.close()


def _fee_sql(cur):
    """Keep settlement line identity and select one unambiguous current estimate per SKU and market currency."""
    cols = _require(cur)

    def field(name, fallback="''"):
        return f'CAST("{name}" AS VARCHAR)' if name in cols else fallback

    def literal(value):
        return "'" + value.replace("'", "''") + "'"

    sql = f"""WITH raw AS (
      SELECT CAST(settlement_id AS VARCHAR) AS sid, {field('store')} AS store,
        {field('_connection_id')} AS connection, {field('sku')} AS sku,
        {field('shipment_id')} AS shipment, {field('quantity_purchased', 'NULL')} AS quantity,
        {field('_report_document_id')} AS document, {field('_report_row_number')} AS report_row,
        CAST(currency AS VARCHAR) AS currency, transaction_type, amount_type, amount_description,
        TRY_CAST(amount AS DOUBLE) AS amount, {_POSTED} AS charged_on FROM {SETTLEMENTS}
    ), headers AS (
      SELECT sid, COALESCE(store, '') AS store, COALESCE(connection, '') AS connection,
        max(nullif(currency, '')) AS currency FROM raw GROUP BY 1, 2, 3
    ), lines AS (
      SELECT r.*, COALESCE(nullif(r.currency, ''), h.currency, '') AS fee_currency,
        'fee:' || sha256(concat_ws('|', r.sid, r.store, r.connection, r.document,
          CASE WHEN nullif(r.document, '') IS NOT NULL AND nullif(r.report_row, '') IS NOT NULL
            THEN r.report_row ELSE CAST(row_number() OVER (ORDER BY r.sid, r.store, r.connection,
              r.charged_on, r.sku, r.shipment, r.transaction_type, r.amount_type, r.amount_description, r.amount)
              AS VARCHAR) END)) AS id,
        lower(trim(COALESCE(r.transaction_type, ''))) AS tt,
        lower(trim(COALESCE(r.amount_type, ''))) AS atyp,
        lower(trim(COALESCE(r.amount_description, ''))) AS description
      FROM raw r JOIN headers h ON r.sid = h.sid AND COALESCE(r.store, '') = h.store
        AND COALESCE(r.connection, '') = h.connection WHERE r.amount IS NOT NULL
    ), checked AS (
      SELECT *, -amount AS charged, TRY_CAST(quantity AS DOUBLE) AS units FROM lines
      WHERE amount < 0 AND ((tt = 'order' AND atyp IN ('itemfee', 'itemfees')
        AND description = {literal(FULFILLMENT_DESCRIPTION.lower())})
        OR (tt = 'other-transaction' AND atyp = {literal(DEFECT_TYPE.lower())}
          AND description = {literal(DEFECT_DESCRIPTION.lower())}))
    ), placements AS (
      SELECT DISTINCT shipment, COALESCE(store, '') AS store, COALESCE(connection, '') AS connection,
        fee_currency FROM lines WHERE tt = 'fbafees' AND atyp = {literal(PLACEMENT_TYPE.lower())}
        AND description = 'base fee' AND amount < 0 AND nullif(trim(shipment), '') IS NOT NULL
    )"""
    estimate_cols = _columns(cur, FEE_ESTIMATES)
    required = {'sku', 'amazon_store', 'currency', '_snapshot_date', 'expected_fulfillment_fee_per_unit'}
    if required <= estimate_cols:
        store = 'COALESCE(CAST(store AS VARCHAR), \'\')' if 'store' in estimate_cols else "''"
        connection = 'COALESCE(CAST(_connection_id AS VARCHAR), \'\')' if '_connection_id' in estimate_cols else "''"
        sql += f""", estimate_rows AS (
          SELECT CAST(sku AS VARCHAR) AS sku, CAST(amazon_store AS VARCHAR) AS market,
            CAST(currency AS VARCHAR) AS currency, {store} AS store, {connection} AS connection,
            TRY_CAST(_snapshot_date AS DATE) AS estimate_date,
            TRY_CAST(expected_fulfillment_fee_per_unit AS DOUBLE) AS expected
          FROM {FEE_ESTIMATES}
        ), latest AS (
          SELECT * FROM estimate_rows QUALIFY estimate_date = max(estimate_date) OVER
            (PARTITION BY sku, market, currency, store, connection)
        ), estimates AS (
          SELECT sku, currency, store, connection, min(expected) AS expected, max(estimate_date) AS estimate_date,
            min(market) AS market FROM latest GROUP BY 1, 2, 3, 4
          HAVING count(DISTINCT market) = 1 AND count(DISTINCT expected) = 1 AND min(expected) >= 0
        )"""
    else:
        sql += """, estimates AS (SELECT '' AS sku, '' AS currency, '' AS store, '' AS connection,
          CAST(NULL AS DOUBLE) AS expected, CAST(NULL AS DATE) AS estimate_date, '' AS market WHERE FALSE)"""
    sql += """, estimated AS (
      SELECT c.*, 'fulfillment' AS kind, e.expected * c.units AS expected_amount,
        e.expected AS expected_unit, e.estimate_date, e.market FROM checked c JOIN estimates e
        ON c.sku = e.sku AND c.fee_currency = e.currency AND COALESCE(c.store, '') = e.store
        AND COALESCE(c.connection, '') = e.connection
      WHERE c.tt = 'order' AND c.units > 0
    ), candidates AS (
      SELECT * FROM estimated c WHERE c.charged / c.units > c.expected_unit
      UNION ALL
      SELECT c.*, 'inbound_defect' AS kind, 0.0 AS expected_amount, NULL AS expected_unit,
        NULL AS estimate_date, '' AS market FROM checked c
      WHERE c.tt = 'other-transaction' AND EXISTS (SELECT 1 FROM placements p
        WHERE p.shipment = c.shipment AND p.fee_currency = c.fee_currency
          AND p.store = COALESCE(c.store, '') AND p.connection = COALESCE(c.connection, ''))
    )"""
    return sql, required <= estimate_cols


def _fee_item(values, owner, overrides):
    (item_id, sid, sku, shipment, currency, charged, expected, units, expected_unit,
     charged_on, estimate_date, kind, market) = values
    deadline = (charged_on + dt.timedelta(days=FULFILLMENT_DISPUTE_DAYS)).isoformat() if (
        kind == "fulfillment" and charged_on) else None
    held = overrides.get(item_id) or {}
    return {"id": item_id, "settlementId": sid, "sku": sku, "shipmentId": shipment, "currency": currency,
            "kind": kind, "title": "Fulfillment fee" if kind == "fulfillment" else "Inbound defect fee",
            "chargedAmount": _money(charged), "expectedAmount": _money(expected), "units": units,
            "chargedPerUnit": charged / units if kind == "fulfillment" else None,
            "expectedPerUnit": expected_unit, "estimateDate": estimate_date.isoformat() if estimate_date else None,
            "estimateMarket": market, "chargedOn": charged_on.isoformat() if charged_on else None,
            "disputeBy": deadline, "due": deadline, "owner": held.get("owner") or owner,
            "status": held.get("status") if held.get("status") in FEE_STATUSES else "Open",
            "deadlineBasis": "90 days from charge" if kind == "fulfillment" else
                "90 days from shipment problem notification. Unknown: notification date is unavailable",
            "expectedBasis": "Current fee estimate" if kind == "fulfillment" else
                "Duplicate fee rule from 15 Jan 2026. Shipment creation date unknown. Review eligibility.",
            "policyUrl": FULFILLMENT_POLICY_URL if kind == "fulfillment" else INBOUND_POLICY_URL}


def _fee_overrides(tenant_key, st, ids):
    """Read only this page's saved fee state and omit its history from Postgres reads."""
    if not ids:
        return {}
    from core import store, store_pg
    resolve = getattr(st, "_document_store", None)
    backend = resolve() if callable(resolve) else st
    if backend is store and store.backend() == "pg":
        backend = store._d()
    if isinstance(backend, store_pg.PgStore):
        if backend.tenant_slug != str(tenant_key).strip().lower():
            raise PermissionError("The Payouts store belongs to another tenant.")
        namespace = getattr(st, "store_key", lambda name: name)
        with store_pg._pool().connection() as con:
            rows = con.execute(store_pg._q(
                "SELECT id, (value #> ARRAY['feeChecks', id]) - 'history' FROM {tbl} "
                "CROSS JOIN unnest(%s::text[]) AS fee_ids(id) WHERE key = %s",
                "store_kv", backend.tenant_slug), (ids, namespace(KEY_ITEMS))).fetchall()
        return {key: value for key, value in rows if isinstance(value, dict)}
    held = _doc(st, KEY_ITEMS).get("feeChecks") or {}
    return {key: held[key] for key in ids if key in held}


def fee_checks(tenant_key, *, st=None, con=None, cursor=None, limit=50, q="", item_id=None):
    """Project owned fee checks without persisting default items during a GET."""
    st = _rt(tenant_key, st)
    owner = settings(tenant_key, st=st)["defaultOwner"]
    with _fee_source(tenant_key, st, con) as cur:
        sql, estimates = _fee_sql(cur)
        where = "contains(lower(concat_ws(' ', sid, sku, shipment, kind)), ?) AND (? IS NULL OR id = ?)"
        args = [str(q or "").strip().lower(), item_id, item_id]
        total = cur.execute(sql + f" SELECT count(*) FROM candidates WHERE {where}", args).fetchone()[0]
        start, size = _bounds_page(cursor, limit, total)
        rows = cur.execute(sql + f""" SELECT id, sid, sku, shipment, fee_currency, charged, expected_amount,
          units, expected_unit, charged_on, estimate_date, kind, market FROM candidates WHERE {where}
          ORDER BY charged_on NULLS LAST, id LIMIT ? OFFSET ?""", [*args, size, start]).fetchall()
        uncompared = cur.execute(sql + " SELECT EXISTS (SELECT 1 FROM checked c WHERE tt = 'order' "
                                "AND NOT EXISTS (SELECT 1 FROM estimated e WHERE e.id = c.id))").fetchone()[0]
    overrides = _fee_overrides(tenant_key, st, [row[0] for row in rows])
    return {"items": [_fee_item(r, owner, overrides) for r in rows], "total": total, "offset": start,
            "cursor": str(start + size) if start + size < total else None, "statuses": list(FEE_STATUSES),
            "message": ("Fee estimates unavailable. Fulfillment fees were not compared." if not estimates else
                        "Some fulfillment fees lack an estimate or unit count." if uncompared else "")}


def set_fee_status(tenant_key, user, item_id, status, *, st=None, con=None):
    """Save a local dispute status for one verified fee check without contacting Amazon."""
    who = _who(user)
    if status not in FEE_STATUSES:
        raise ValueError("Choose Open or Disputed.")
    st = _rt(tenant_key, st)
    page = fee_checks(tenant_key, st=st, con=con, item_id=item_id, limit=1)
    if not page["items"]:
        raise KeyError(item_id)
    item = page["items"][0]

    def apply(doc):
        doc = doc if isinstance(doc, dict) else {}
        saved = doc.setdefault("feeChecks", {}).setdefault(item_id, {"owner": item["owner"]})
        saved["status"] = status
        saved.setdefault("history", []).append({"at": _stamp(_now()), "by": who, "status": status})
        return doc
    st.update(KEY_ITEMS, apply)
    _fee_table_row(tenant_key, st, con, item_id)
    return {**item, "status": status}


def _validate_fees(tenant_key, *, st=None, con=None, settlement_ids=None):
    """Tie checked charges by currency to a separate sum over the source settlement fee lines."""
    with _fee_source(tenant_key, st, con) as cur:
        sql, _estimates = _fee_sql(cur)
        ids = list(settlement_ids) if settlement_ids is not None else None
        scope = "" if ids is None else " AND sid IN (SELECT unnest(?))"
        args = [] if ids is None else [ids]
        app = dict(cur.execute(sql + " SELECT fee_currency, sum(charged) FROM checked WHERE TRUE" + scope
                               + " GROUP BY 1", args).fetchall())
        cols = _columns(cur, SETTLEMENTS)
        scope_columns = [name for name in ("store", "_connection_id") if name in cols]
        header_scope = "".join(f', COALESCE(CAST("{name}" AS VARCHAR), \'\') AS "{name}"' for name in scope_columns)
        group_scope = "".join(f', COALESCE(CAST("{name}" AS VARCHAR), \'\')' for name in scope_columns)
        join_scope = "".join(f' AND COALESCE(CAST(s."{name}" AS VARCHAR), \'\') = h."{name}"' for name in scope_columns)
        independent = dict(cur.execute(f"""SELECT COALESCE(nullif(s.currency, ''), h.currency, ''),
          -sum(TRY_CAST(s.amount AS DOUBLE)) FROM {SETTLEMENTS} s
          LEFT JOIN (SELECT settlement_id{header_scope}, max(nullif(currency, '')) AS currency FROM {SETTLEMENTS}
            GROUP BY settlement_id{group_scope}) h ON s.settlement_id = h.settlement_id{join_scope}
          WHERE TRY_CAST(s.amount AS DOUBLE) < 0 AND (
            (lower(trim(s.transaction_type)) = 'order' AND lower(trim(s.amount_type)) IN ('itemfee', 'itemfees')
              AND lower(trim(s.amount_description)) = 'fbaperunitfulfillmentfee') OR
            (lower(trim(s.transaction_type)) = 'other-transaction' AND lower(trim(s.amount_type)) = 'inbound defect fee'
              AND lower(trim(s.amount_description)) = 'inbound defect fee - shipments delivered to the wrong location'))
          {'' if ids is None else 'AND CAST(s.settlement_id AS VARCHAR) IN (SELECT unnest(?))'} GROUP BY 1""", args).fetchall())
    checks = [{"currency": code, "checked": _money(app.get(code)), "settlements": _money(independent.get(code)),
               "ok": abs(float(app.get(code) or 0) - float(independent.get(code) or 0)) < CENT}
              for code in sorted(app.keys() | independent.keys())]
    return {"ok": all(c["ok"] for c in checks), "byCurrency": checks}


# --------------------------------------------------------------------------- fee checks table (C4)

FEE_TABLE_KEY = "ut_amazon_fee_checks"
FEE_TABLE_LABEL = "Payouts fee checks"
FEE_TABLE_VERSION = 1
LISTINGS = "amzn_listings"
LISTING_SOURCE_KEY = "ut_amzn_listings"
FEE_TABLE_SOURCES = (SOURCE_KEY, FEE_ESTIMATE_SOURCE, LISTING_SOURCE_KEY)
FEE_TABLE_TABLES = (SETTLEMENTS, FEE_ESTIMATES, LISTINGS)
_FEE_COLUMNS = ("c.id, c.sid, c.sku, c.shipment, c.fee_currency, c.charged, c.expected_amount, c.units, "
                "c.expected_unit, c.charged_on, c.estimate_date, c.kind, c.market")

#: The product cell is first; status changes only through the fee status route, so every field is computed.
FEE_TABLE_FIELDS = (
    _table_field("product", "Product", "text", "Listing title, or the SKU when no listing names it.",
                 format={"display": "product", "pictureKey": "picture", "subKey": "sku"}),
    _table_field("fee_type", "Fee type", "text", "Fulfillment fee or inbound defect fee."),
    _table_field("expected", "Expected", "currency", "What the current fee estimate or policy expects."),
    _table_field("charged", "Charged", "currency", "What Amazon charged."),
    _table_field("difference", "Difference", "currency", "Charged minus expected."),
    _table_field("dispute_by", "Dispute deadline", "date", "Last day to dispute, when the policy clock is known."),
    _table_field("status", "Status", "select", "Open, or Disputed with Amazon.", options=list(FEE_STATUSES),
                 optionColors={"Open": "#FBF4E0", "Disputed": "#EBF6EF"}),
    _table_field("picture", "Picture", "image", "Main Amazon listing picture."),
    _table_field("sku", "SKU", "text", "Seller SKU on the fee line."),
    _table_field("asin", "ASIN", "text", "Listing ASIN for the SKU."),
    _table_field("currency", "Currency", "text", "Currency of the amounts."),
    _table_field("settlement_id", "Settlement ID", "text", "Settlement that holds the fee line."),
    _table_field("fee_id", "Fee ID", "text", "Fee check id."),
)


def _fee_table_present(st):
    try:
        import core.user_tables as ut
        return ut.get_one(FEE_TABLE_KEY, st) is not None
    except Exception:                                                  # noqa: BLE001
        return False


def _listing_sql(cur):
    """One title and ASIN per seller SKU from the newest listing snapshot; empty without listings."""
    cols = _columns(cur, LISTINGS)
    if "seller_sku" not in cols:
        return """, listing AS (SELECT '' AS sku, CAST(NULL AS VARCHAR) AS title, CAST(NULL AS VARCHAR) AS asin
          WHERE FALSE)"""
    order = "COALESCE(CAST(_snapshot_date AS VARCHAR), '')" if "_snapshot_date" in cols else "1"
    title = f"arg_max(CAST(item_name AS VARCHAR), {order})" if "item_name" in cols else "CAST(NULL AS VARCHAR)"
    asin = f"arg_max(TRIM(CAST(asin1 AS VARCHAR)), {order})" if "asin1" in cols else "CAST(NULL AS VARCHAR)"
    return f""", listing AS (SELECT CAST(seller_sku AS VARCHAR) AS sku, {title} AS title, {asin} AS asin
      FROM {LISTINGS} WHERE seller_sku IS NOT NULL GROUP BY 1)"""


def fee_table_cells(rows, overrides, refs):
    """`{fee_id: cells}` for every fee check, past deadline included. No cap (rule 1)."""
    out = {}
    for values in rows:
        item = _fee_item(values[:13], "", overrides)
        title, asin = values[13], values[14]
        expected, charged = item["expectedAmount"], item["chargedAmount"]
        out[item["id"]] = {
            "product": title or item["sku"] or item["shipmentId"] or item["title"], "fee_type": item["title"],
            "expected": expected, "charged": charged, "difference": _money(charged - expected),
            "dispute_by": item["disputeBy"], "status": item["status"], "picture": refs.get(asin) if asin else None,
            "sku": item["sku"] or None, "asin": asin or None, "currency": item["currency"] or None,
            "settlement_id": item["settlementId"], "fee_id": item["id"]}
    return out


def _fee_cells(tenant_key, st, con=None, item_id=None):
    """Project fee checks with listing titles and pictures in one SQL read."""
    with _fee_source(tenant_key, st, con, tables=FEE_TABLE_TABLES) as cur:
        sql, _estimates = _fee_sql(cur)
        rows = cur.execute(sql + _listing_sql(cur) + f""" SELECT {_FEE_COLUMNS}, l.title, l.asin
          FROM candidates c LEFT JOIN listing l ON l.sku = c.sku WHERE ? IS NULL OR c.id = ?
          ORDER BY c.charged_on NULLS LAST, c.id""", [item_id, item_id]).fetchall()
    try:
        from harness import amazon_pictures
        refs = amazon_pictures.picture_refs(st) or {}
    except Exception:                                                  # noqa: BLE001
        refs = {}
    return fee_table_cells(rows, _fee_overrides(tenant_key, st, [r[0] for r in rows]), refs)


def _write_fee_rows(st, computed):
    helper = _module_tables()
    try:
        keys = helper.ensure_module_table(st, TABLE_WRITER,
                                         [{"key": FEE_TABLE_KEY, "label": FEE_TABLE_LABEL,
                                           "fields": [dict(f) for f in FEE_TABLE_FIELDS],
                                           "sources": FEE_TABLE_SOURCES}], owner=TABLE_WRITER)
        key, = keys
        helper.ensure_everyone_grant(st, [key], owner=TABLE_WRITER)
        counts = helper.write_module_rows(st, key, computed, key_column="fee_id",
                                          writer=TABLE_WRITER, via="payouts", label=FEE_TABLE_LABEL,
                                          clear_keys=(), origin="Amazon fee checks")
    except Exception as exc:                                           # noqa: BLE001
        if type(exc).__name__.endswith("Refused"):
            raise TableRefused(str(exc)) from exc
        raise
    return key, counts or {}


def fee_table(tenant_key, *, st=None, con=None):
    """Build `ut_amazon_fee_checks` when the fee projection moved; otherwise write nothing. Returns `{table, rows, stamp}`."""
    st = _rt(tenant_key, st)
    with _TABLE_LOCK:
        computed = _fee_cells(tenant_key, st, con)
        digest = hashlib.sha256(json.dumps(computed, sort_keys=True, default=str).encode("utf-8")).hexdigest()
        stamp = f"v{FEE_TABLE_VERSION}:{len(computed)}:{digest[:32]}"
        held = _doc(st, KEY_SETTINGS)
        if held.get("fee_table_stamp") == stamp and _fee_table_present(st):
            return {"table": FEE_TABLE_KEY, "rows": len(computed), "stamp": stamp, "written": False}
        key, counts = _write_fee_rows(st, computed)

        def _keep(doc):
            doc = doc if isinstance(doc, dict) else {}
            doc.update(fee_table_stamp=stamp, fee_table_rows=len(computed))
            return doc
        st.update(KEY_SETTINGS, _keep)
    return {"table": key, "rows": len(computed), "stamp": stamp, "written": True,
            "created": int(counts.get("created") or 0), "updated": int(counts.get("updated") or 0)}


def _fee_table_row(tenant_key, st, con, item_id):
    """Rewrite one fee row after a status change so its pill turns; the next POST heals a failed rewrite."""
    if not _fee_table_present(st):
        return False
    try:
        with _TABLE_LOCK:
            cells = _fee_cells(tenant_key, st, con, item_id=item_id)
            if cells:
                _write_fee_rows(st, cells)
        return bool(cells)
    except Exception:                                                  # noqa: BLE001
        return False


# --------------------------------------------------------------------------- settings and owner

#: Accounts that sign in for a process, never a person who can own an item.
SERVICE_ACCOUNTS = ("admin", "automation", "scheduler", "qa-runner", "leadership")


def _is_service(name, rec):
    name = str(name or "").strip().lower()
    return (not name or name.startswith("qa") or name in SERVICE_ACCOUNTS
            or name.endswith("-admin") or bool(rec.get("platform_admin")))


def owner_account(st, tenant_key=None):
    """The tenant's earliest active person Administrator, else its `admin` account, else its first Administrator."""
    try:
        users = st.get("users")
    except Exception:                                                  # noqa: BLE001
        users = None
    want = str(tenant_key or "").strip().lower()
    admins = [(str(name), rec) for name, rec in (users or {}).items()
              if isinstance(rec, dict) and rec.get("active", True)
              and str(rec.get("role") or "").lower() == "admin"
              and (not want or str(rec.get("tenant") or "royal-imports").strip().lower() == want)]
    people = sorted(((n, r) for n, r in admins if not _is_service(n, r)),
                    key=lambda item: ("@" not in item[0], not item[1].get("password_changed_at"),
                                      str(item[1].get("password_changed_at") or ""), item[0]))
    if people:
        return str(people[0][1].get("name") or people[0][0])
    names = sorted(n for n, _r in admins)
    return "admin" if "admin" in names or not names else names[0]


def settings(tenant_key, *, st=None):
    """The default owner, with the owner account filled in, and the item statuses."""
    st = _rt(tenant_key, st)
    doc = _doc(st, KEY_SETTINGS)
    setting = doc.get("default_owner") or None
    days = doc.get("bank_match_days", BANK_MATCH_DAYS)
    if isinstance(days, bool) or not isinstance(days, int) or days < 0:
        days = BANK_MATCH_DAYS
    return {"defaultOwnerSetting": setting, "defaultOwner": setting or owner_account(st, tenant_key),
            "statuses": list(STATUSES), "dueDays": DUE_DAYS, "bankMatchDays": days}


def set_settings(tenant_key, *, default_owner=None, bank_match_days=None, st=None):
    """Set the default owner and the whole-day bank matching window."""
    st = _rt(tenant_key, st)
    if bank_match_days is not None and (isinstance(bank_match_days, bool)
            or not isinstance(bank_match_days, int) or bank_match_days < 0):
        raise ValueError("Bank match days must be a whole number of zero or more.")

    def _apply(doc):
        doc = doc if isinstance(doc, dict) else {}
        if default_owner is not None:
            doc["default_owner"] = str(default_owner).strip() or None
        if bank_match_days is not None:
            doc["bank_match_days"] = bank_match_days
        return doc
    st.update(KEY_SETTINGS, _apply)
    return settings(tenant_key, st=st)


# --------------------------------------------------------------------------- owned items (C4)

def _refresh_items(tenant_key, st, rows, now):
    """Keep one owned item per settlement with a waterfall gap or an overdue missing bank line."""
    doc = _doc(st, KEY_ITEMS)
    held = doc.get("items") or {}
    states = {r["id"]: r for r in rows}
    def causes(row):
        return (["waterfall"] if not row["reconciled"] else []) + (
            ["bank_missing"] if (row.get("bankMatch") or {}).get("missing") else [])
    fresh = [r for r in rows if causes(r) and r["id"] not in held]
    moved = [sid for sid, item in held.items() if sid in states and (
        bool(item.get("reconciledNow")) != (not causes(states[sid]))
        or item.get("causes", ["waterfall"]) != causes(states[sid]))]
    if not fresh and not moved:
        return doc
    owner = settings(tenant_key, st=st)["defaultOwner"]
    at = _stamp(now)

    def _apply(current):
        current = current if isinstance(current, dict) else {}
        items = current.setdefault("items", {})
        for row in fresh:
            if row["id"] in items:
                continue
            items[row["id"]] = {
                "id": row["id"], "title": row["title"], "status": "Open", "owner": owner,
                "due": (now + dt.timedelta(days=DUE_DAYS)).date().isoformat(),
                "reason": (_reason(row) if not row["reconciled"] else "No matching bank line for this deposit."),
                "causes": causes(row), "kind": "bank_missing" if row["reconciled"] else "settlement_gap",
                "currency": row["currency"], "gap": row["gap"], "linesTotal": row["linesTotal"],
                "amazonTotal": row["amazonTotal"], "firstSeen": at, "reconciledNow": False,
                "history": [{"at": at, "by": "payouts", "status": "Open"}]}
        for sid in moved:
            if sid in items:
                active = causes(states[sid])
                was_bank_only = items[sid].get("causes") == ["bank_missing"]
                history = items[sid].setdefault("history", [])
                system_resolved = bool(history and history[-1].get("by") == "payouts"
                                       and items[sid].get("status") == "Resolved")
                items[sid]["reconciledNow"] = not active
                items[sid]["causes"] = active
                if active:
                    row = states[sid]
                    items[sid]["reason"] = (_reason(row) if not row["reconciled"] else
                                            "No matching bank line for this deposit.")
                if was_bank_only and not active and items[sid].get("status") == "Open":
                    items[sid]["status"] = "Resolved"
                    history.append({"at": at, "by": "payouts", "status": "Resolved"})
                elif active and system_resolved:
                    items[sid]["status"] = "Open"
                    history.append({"at": at, "by": "payouts", "status": "Open"})
        return current
    st.update(KEY_ITEMS, _apply)
    return _doc(st, KEY_ITEMS)


def items(tenant_key, user, *, status=None, cursor=None, limit=PAGE, st=None, con=None, now=None, refresh=True,
          bank_access=True):
    """Every owned item, due first, paged by cursor. No cap. The caller checks the App grant."""
    if status is not None and status not in STATUSES:
        raise ValueError("Status must be one of: " + ", ".join(STATUSES) + ".")
    st = _rt(tenant_key, st)
    now = parse_moment(now) if now is not None else _now()
    if refresh:
        rows = _item_rows(tenant_key, st, con, now)
        doc = _refresh_items(tenant_key, st, rows, now)
    else:
        doc = _doc(st, KEY_ITEMS)
    listed = [dict(i) for i in (doc.get("items") or {}).values()
              if (status is None or i.get("status") == status)
              and (bank_access or (i.get("kind") != "bank_missing" and i.get("causes") != ["bank_missing"]))]
    if not bank_access:
        for item in listed:
            if "causes" in item:
                item["causes"] = [c for c in item["causes"] if c != "bank_missing"]
    listed.sort(key=lambda i: (str(i.get("due") or ""), str(i.get("id"))))
    chunk, nxt = _page(listed, cursor, limit)
    return {"items": chunk, "cursor": nxt}


def _item_rows(tenant_key, st, con, now):
    rows, _economics_, _source_ = _all(tenant_key, st, con)
    states = {r["id"]: r for r in rows}
    with _source(tenant_key, st, con) as (cur, _source_):
        sql, feed = _bank_sql(cur, _settlement_sql(cur), settings(tenant_key, st=st)["bankMatchDays"], now)
        result = cur.execute(sql + f" SELECT id, {_BANK_FIELDS} FROM payouts")
        while batch := result.fetchmany(PAGE):
            for sid, *values in batch:
                row = states[sid]
                row["bankMatch"] = _bank_match(row, values, feed, now)
    return rows


def refresh_items(tenant_key, *, st=None, con=None, now=None):
    """Generate owned settlement items in the worker, so screen reads preserve their stored due dates."""
    st = _rt(tenant_key, st)
    now = parse_moment(now) if now is not None else _now()
    rows = _item_rows(tenant_key, st, con, now)
    doc = _refresh_items(tenant_key, st, rows, now)
    return len(doc.get("items") or {})


def _who(user):
    """The acting name, or raise PermissionError for no user, a viewer, or a user who may not edit."""
    if not user:
        raise PermissionError("Sign in to change a Payouts item.")
    if isinstance(user, dict):
        if str(user.get("role") or "").strip().lower() == "viewer":
            raise PermissionError("Your account can view Payouts but not change it.")
        if user.get("canEdit") is False or user.get("can_edit") is False:
            raise PermissionError("You can view this App but not change it.")
        return str(user.get("username") or user.get("name") or "unknown")
    return str(user)


def set_status(tenant_key, user, item_id, status, *, note=None, st=None, bank_access=True):
    """Move one item to a status in STATUSES. Returns the item."""
    who = _who(user)
    if status not in STATUSES:
        raise ValueError("Status must be one of: " + ", ".join(STATUSES) + ".")
    st = _rt(tenant_key, st)
    if str(item_id) not in (_doc(st, KEY_ITEMS).get("items") or {}):
        detail = screen_settlement(tenant_key, item_id, st=st, limit=1, bank_access=bank_access)
        if detail["reconciled"] and not detail["bankMatch"]["missing"]:
            raise KeyError(item_id)
        _refresh_items(tenant_key, st, [detail], _now())
    out = {}

    def _apply(doc):
        doc = doc if isinstance(doc, dict) else {}
        item = (doc.get("items") or {}).get(str(item_id))
        if not item:
            return doc
        if not bank_access and (item.get("kind") == "bank_missing" or "bank_missing" in (item.get("causes") or [])):
            raise KeyError(item_id)
        at = _stamp(_now())
        event = {"at": at, "by": who, "status": status}
        if note:
            event["note"] = str(note).strip()
        item["status"] = status
        item["updatedAt"], item["updatedBy"] = at, who
        item.setdefault("history", []).append(event)
        out["item"] = dict(item)
        return doc
    st.update(KEY_ITEMS, _apply)
    if "item" not in out:
        raise KeyError(item_id)
    return out["item"]


def proposals(tenant_key):
    """Payouts proposes nothing (`proposes: False`)."""
    return []


def on_decision(tenant_key, item_id, decision, reason, *, st=None):
    """Record a decision on the item when it exists. Never writes to Amazon or Odoo."""
    if decision not in DECISIONS:
        raise ValueError("A decision must be approved or dismissed.")
    st = _rt(tenant_key, st)
    out = {"recorded": False}

    def _apply(doc):
        doc = doc if isinstance(doc, dict) else {}
        item = (doc.get("items") or {}).get(str(item_id))
        if item:
            item.setdefault("history", []).append({"at": _stamp(_now()), "by": "decision", "decision": decision,
                                                   "reason": str(reason or "")})
            item["decision"] = decision
            out["recorded"] = True
        return doc
    st.update(KEY_ITEMS, _apply)
    return out


def remove(tenant_key, *, st=None):
    """Delete this App's store documents only. Never a mirror table."""
    st = _rt(tenant_key, st)
    removed = []
    for key in STORE_KEYS:
        if st.get(key) is None:
            continue
        st.delete_document(key)
        removed.append(key)
    return {"removed": removed}


# --------------------------------------------------------------------------- validate

def _validate_bank(tenant_key, rows, *, con=None, st=None, client=None, now=None):
    """Tie matched deposits to independent bank sums and reject work beyond the feed's last date."""
    now = parse_moment(now) if now is not None else _now()
    selected = {row["id"]: row for row in rows}
    with _source(tenant_key, st, con) as (cur, _source_):
        sql, feed = _bank_sql(cur, _settlement_sql(cur), _match_days(tenant_key, st, con), now)
        result = cur.execute(sql + f" SELECT id, {_BANK_FIELDS} FROM payouts WHERE bank_id IS NOT NULL")
        matches = []
        while batch := result.fetchmany(PAGE):
            matches.extend((selected[sid], values) for sid, *values in batch if sid in selected)
        ids = [values[0] for row, values in matches]
        app = {}
        valid = len(ids) == len(set(ids))
        for row, values in matches:
            currency = row["currency"].upper()
            app[currency] = app.get(currency, 0) + row["amazonTotal"]
            valid = valid and currency == values[4] and abs(row["amazonTotal"] - float(values[2])) < CENT
            valid = valid and abs((parse_date(row["depositDate"]) - values[1]).days) <= feed["matchDays"]
        independent, count = {}, 0
        source = "fixture bank lines" if con is not None and client is None else "Odoo bank lines"
        if ids and con is not None and client is None:
            grouped = cur.execute(f"SELECT bank_currency, sum(bank_amount), count(*) FROM ({_bank_lines_sql(cur)}) "
                                  "WHERE bank_id = ANY(?) GROUP BY bank_currency", [ids]).fetchall()
            independent = {currency: _money(amount) for currency, amount, _n in grouped}
            count = sum(n for _currency, _amount, n in grouped)
    if ids and (con is None or client is not None):
        try:
            with contextlib.ExitStack() as stack:
                if client is None:
                    rt = _rt(tenant_key, st)
                    source_handle = rt.odoo_source()
                    if source_handle is None:
                        raise SourceUnavailable("The bank source is unavailable.")
                    stack.enter_context(source_handle._bound())
                    from core import odoo
                    client = odoo.get_odoo()
                groups = client.read_group("account.bank.statement.line", [("id", "in", [int(i) for i in ids])],
                                           ["amount:sum", "currency_id"], ["currency_id"], lazy=False)
                for group in groups:
                    identity = group.get("currency_id")
                    currency = str(identity[1]).strip().upper() if isinstance(identity, (list, tuple)) else ""
                    group_count = group.get("__count", group.get("currency_id_count"))
                    if not re.fullmatch("[A-Z]{3}", currency) or "amount" not in group or group_count is None:
                        raise ValueError("The bank source lacks currency, amount or count.")
                    independent[currency] = independent.get(currency, 0) + float(group["amount"])
                    count += int(group_count)
        except Exception as exc:                                      # noqa: BLE001
            return {"ok": False, "available": feed["available"], "source": source, "error": str(exc)}
    after_feed = []
    if st is not None:
        latest = parse_date(feed["newestDate"])
        for sid, item in (_doc(st, KEY_ITEMS).get("items") or {}).items():
            row = selected.get(sid)
            deposit = parse_date(row.get("depositDate")) if row else None
            if (item.get("status") == "Open" and "bank_missing" in (item.get("causes") or [])
                    and (latest is None or (deposit and deposit > latest))):
                after_feed.append(sid)
    app = {currency: _money(amount) for currency, amount in app.items()}
    independent = {currency: _money(amount) for currency, amount in independent.items()}
    return {"ok": valid and count == len(ids) and app == independent and not after_feed,
            "available": feed["available"], "source": source, "matchedCount": len(ids),
            "byCurrency": {currency: {"app": app.get(currency, 0), "bank": independent.get(currency, 0)}
                           for currency in sorted(set(app) | set(independent))},
            "itemsAfterFeed": after_feed, "newestDate": feed["newestDate"]}


def validate(tenant_key=TENANT_ZERO, *, start=None, end=None, con=None, st=None, bank_client=None, now=None,
             user=None, figures=None, **_period):
    """Tie waterfall sums to Amazon totals and matched deposits to independent Odoo bank sums."""
    if figures is not None:
        return _validate_dashboard(tenant_key, user=user, st=st, con=con, start=start, end=end, now=now, figures=figures)
    try:
        rows, _economics_, source = _all(tenant_key, st, con)
    except SourceUnavailable as exc:
        return {"ok": False, "app_total": None, "independent_total": None,
                "detail": {"error": str(exc), "settlements": [], "unreconciled": []}}
    rows = _window(rows, start or _period.get("month"), end or _period.get("month"))
    checks = [{"id": r["id"], "currency": r["currency"], "end": r["end"], "linesTotal": r["linesTotal"],
               "amazonTotal": r["amazonTotal"], "gap": r["gap"], "ok": r["reconciled"]} for r in rows]
    bad = [{"id": c["id"], "gap": c["gap"], "currency": c["currency"]} for c in checks if not c["ok"]]
    by_currency = {}
    for r in rows:
        into = by_currency.setdefault(r["currency"] or "unknown", {"app": 0.0, "amazon": 0.0, "settlements": 0})
        into["app"] += sum(w["amount"] for w in r["waterfall"] if w["key"] != "deposit")
        into["amazon"] += r["amazonTotal"] or 0.0
        into["settlements"] += 1
    for into in by_currency.values():
        into["app"], into["amazon"] = _money(into["app"]), _money(into["amazon"])
    app_total = _money(sum(v["app"] for v in by_currency.values()))
    independent = _money(sum(v["amazon"] for v in by_currency.values()))
    bank = _validate_bank(tenant_key, rows, con=con, st=st, client=bank_client, now=now)
    fees = _validate_fees(tenant_key, st=st, con=con, settlement_ids=[r["id"] for r in rows])
    tiles = _validate_tiles(tenant_key, st=st, con=con, now=now)
    return {"ok": bool(rows) and not bad and bank["ok"] and fees["ok"] and tiles["ok"], "app_total": app_total, "independent_total": independent,
            "detail": {"source": source, "count": len(rows), "reconciled": len(rows) - len(bad),
                       "settlements": checks, "unreconciled": bad, "byCurrency": by_currency, "bankMatch": bank,
                       "feeChecks": fees, "tiles": tiles}}


def _dashboard_access(st, user):
    """Refuse raw settlement aggregates when the source has any unresolved row or field limit."""
    from core import perm_scope
    required = set(_REQUIRED) | {"settlement_start_date", "settlement_end_date", "deposit_date"}
    if (not isinstance(user, dict) or not user
            or not perm_scope.may_read(user, SOURCE_KEY, st=st)
            or perm_scope.wall_declared(user, SOURCE_KEY, st=st)
            or perm_scope.derive_pool_scope(user, SOURCE_KEY) != (None, None)
            or not required <= {f["key"] for f in perm_scope.scoped_fields(user, SOURCE_KEY, st=st)}):
        raise PermissionError("Dashboard needs access to settlement rows and fields.")


def _dashboard_rows(cur, start, end):
    """Read settlement aggregates in bounded batches, never source line rows."""
    sql = _settlement_sql(cur)
    result = cur.execute(sql + f""" SELECT id, currency, start_at, end_at, deposit_at, total, lines, headers,
                         (SELECT count(DISTINCT upper(trim(CAST(s.currency AS VARCHAR))))
                          FROM {SETTLEMENTS} s WHERE CAST(s.settlement_id AS VARCHAR) = settlements.id
                          AND nullif(trim(CAST(s.currency AS VARCHAR)), '') IS NOT NULL) AS currency_count
                         """
                         "FROM settlements ORDER BY end_at NULLS LAST, id")
    rows = []
    while batch := result.fetchmany(PAGE):
        for sid, currency, opened, closed, deposit, total, lines, headers, currency_count in batch:
            rows.append({"id": sid, "currency": currency.upper(), "_start": parse_moment(opened),
                         "_end": parse_moment(closed), "_deposit": parse_moment(deposit),
                         "amazonTotal": total, "linesTotal": lines, "headers": headers, "currencyCount": currency_count})
    return _window(rows, start, end)


def _dashboard_figures(cur, start, end, now):
    """Build currency-separated figures from SQL settlement and fee projections."""
    rows = _dashboard_rows(cur, start, end)
    ids = [r["id"] for r in rows]
    result = cur.execute(f"""SELECT CAST(settlement_id AS VARCHAR), {_bucket_sql()} AS kind,
        SUM(TRY_CAST(amount AS DOUBLE)) FROM {SETTLEMENTS}
        WHERE CAST(settlement_id AS VARCHAR) IN (SELECT unnest(?)) AND amount IS NOT NULL
        GROUP BY 1, 2""", [ids])
    parts = {}
    while batch := result.fetchmany(PAGE):
        for sid, kind, amount in batch:
            parts.setdefault(sid, {})[kind] = float(amount or 0)
    figures = []
    for currency in sorted({r["currency"] for r in rows}):
        mine = [r for r in rows if r["currency"] == currency]
        valid = bool(re.fullmatch("[A-Z]{3}", currency)) and all(
            r["headers"] == 1 and r["currencyCount"] == 1 and r["amazonTotal"] is not None
            and abs(r["linesTotal"] - r["amazonTotal"]) < CENT for r in mine)
        series = [{"label": f"{_day(r['_end'])}, {r['id']}", "value": _money(r["linesTotal"])} for r in mine]
        figures.append({"id": f"net-payout-{currency}", "label": "Net payout", "unit": currency,
                        "value": _money(sum(r["linesTotal"] for r in mine)), "series": series,
                        "source_table": SOURCE_KEY, "validated": valid,
                        "reason": "" if valid else "Settlement lines do not tie to one currency header."})
        sales = sum(parts.get(r["id"], {}).get("sales", 0) for r in mine)
        fees = -sum(parts.get(r["id"], {}).get("fees", 0) for r in mine)
        ratio_ok = valid and sales > 0 and all(parts.get(r["id"], {}).get("sales", 0) > 0 for r in mine)
        figures.append({"id": f"fee-share-{currency}", "label": f"Fees as share of sales ({currency})", "unit": "%",
                        "value": round(fees / sales * 100, 4) if ratio_ok else None,
                        "series": [{"label": point["label"], "value": round(
                            -parts[r["id"]].get("fees", 0) / parts[r["id"]]["sales"] * 100, 4)}
                                   for r, point in zip(mine, series)] if ratio_ok else [],
                        "source_table": SOURCE_KEY, "validated": ratio_ok,
                        "reason": "" if ratio_ok else "Fee share unavailable: missing or zero sales, or settlement mismatch."})
        nxt = next_deposit(mine, streams(mine, now), now)
        figures.append({"id": f"next-deposit-{currency}", "label": f"Next expected deposit ({currency})", "unit": "date",
                        "value": nxt["date"] if nxt else None, "series": [], "source_table": SOURCE_KEY,
                        "validated": valid and nxt is not None,
                        "reason": ("Expected from settlement cadence" if nxt and nxt["expected"] else "")
                        if nxt else "No active settlement or issued deposit."})
    return figures


def _validate_dashboard(tenant_key, *, user, st, con, start, end, now, figures):
    """Tie every displayed value and series to separately read settlement headers and line classifications."""
    _dashboard_access(st, user)
    with _reader(tenant_key, st) if con is None else contextlib.nullcontext(con) as cur:
        _require(cur)
        headers, groups = _load(cur)
        mixed = {row[0] for row in cur.execute(f"""SELECT CAST(settlement_id AS VARCHAR) FROM {SETTLEMENTS}
            WHERE nullif(trim(CAST(currency AS VARCHAR)), '') IS NOT NULL GROUP BY 1
            HAVING count(DISTINCT upper(trim(CAST(currency AS VARCHAR)))) <> 1""").fetchall()}
    raw = _window(_build(headers, groups), start, end)
    checks = []
    for figure in figures:
        kind, _, currency = figure["id"].rpartition("-")
        mine = sorted((r for r in raw if r["currency"].upper() == currency),
                      key=lambda r: (r["_end"], r["id"]))
        valid = bool(mine) and all(r["headerRows"] == 1 and r["reconciled"] and r["id"] not in mixed for r in mine)
        points = [{"label": f"{_day(r['_end'])}, {r['id']}", "value": r["amazonTotal"]} for r in mine]
        value = None
        if kind == "net-payout":
            value = _money(sum(r["amazonTotal"] or 0 for r in mine))
        elif kind == "fee-share":
            amounts = [{w["key"]: w["amount"] for w in r["waterfall"]} for r in mine]
            sales = sum(a["sales"] for a in amounts)
            valid = valid and sales > 0 and all(a["sales"] > 0 for a in amounts)
            value = round(-sum(a["fees"] for a in amounts) / sales * 100, 4) if valid else None
            points = [{"label": p["label"], "value": round(-a["fees"] / a["sales"] * 100, 4)}
                      for p, a in zip(points, amounts)] if valid else []
        elif kind == "next-deposit":
            nxt = next_deposit(mine, streams(mine, now), now)
            value, points = nxt["date"] if nxt else None, []
            valid = valid and nxt is not None
        else:
            valid = False
        unit = "%" if kind == "fee-share" else "date" if kind == "next-deposit" else currency
        checks.append({"id": figure["id"], "ok": bool(valid and re.fullmatch("[A-Z]{3}", currency)
                       and figure["unit"] == unit and figure["source_table"] == SOURCE_KEY
                       and figure["value"] == value and figure["series"] == points)})
    return {"ok": bool(checks) and all(c["ok"] for c in checks), "figures": checks}


def dashboard(tenant_key, *, user=None, st=None, start=None, end=None, now=None, con=None):
    """Serve validated settlement figures from this tenant's mirror without snapshot downloads or writes."""
    st = _rt(tenant_key, st)
    _dashboard_access(st, user)
    now = parse_moment(now) if now is not None else _now()
    with _reader(tenant_key, st) if con is None else contextlib.nullcontext(con) as cur:
        _require(cur)
        figures = _dashboard_figures(cur, start, end, now)
    checks = validate(tenant_key, user=user, st=st, con=con, start=start, end=end, now=now, figures=figures)
    passing = {c["id"] for c in checks["figures"] if c["ok"]}
    for figure in figures:
        figure["validated"] = figure["validated"] and figure["id"] in passing
        if not figure["validated"]:
            figure.update(value=None, series=[], reason=figure["reason"] or "Settlement validation failed.")
    return {"figures": figures}