File size: 92,264 Bytes
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
e88a3a3
 
 
 
cf09724
e88a3a3
aa1490a
e88a3a3
aa1490a
e88a3a3
 
 
 
 
 
792ee39
9a6cb9a
d598032
e88a3a3
 
 
 
 
 
 
 
 
 
d598032
 
 
 
e88a3a3
792ee39
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
792ee39
 
 
 
 
 
 
 
 
 
 
81a8217
792ee39
 
 
 
 
 
 
 
 
 
 
 
 
e88a3a3
07bca1c
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
07bca1c
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e88a3a3
 
 
4d24cf6
 
e88a3a3
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
e88a3a3
cf09724
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
 
cf09724
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e88a3a3
0e600b9
 
 
 
 
 
 
 
e88a3a3
 
1ad1eea
e88a3a3
 
 
 
 
 
 
 
 
 
1ad1eea
 
e88a3a3
 
cf09724
 
 
e88a3a3
 
0e600b9
e88a3a3
0e600b9
 
e88a3a3
 
 
cf09724
 
 
 
 
 
 
 
 
 
 
 
c70f19a
e88a3a3
c70f19a
 
7f72224
c70f19a
 
 
7f72224
e88a3a3
7f72224
 
e88a3a3
 
 
 
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cf09724
 
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
a93637f
 
 
 
 
 
 
 
aa1490a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
aa1490a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c70f19a
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c70f19a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
aa1490a
a93637f
 
 
7f72224
 
 
 
 
 
 
 
 
 
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
a93637f
 
 
7f72224
a93637f
 
 
cf09724
 
 
 
7f72224
a93637f
 
 
 
7f72224
 
 
a93637f
 
 
affe37f
 
e88a3a3
 
 
81a8217
cf09724
 
e88a3a3
 
a93637f
 
 
 
 
 
e88a3a3
9a6cb9a
 
 
 
 
cf09724
e88a3a3
cf09724
 
81a8217
e88a3a3
7f72224
e88a3a3
85f794d
 
daae64a
 
cf09724
e88a3a3
a93637f
 
 
 
 
 
 
81a8217
792ee39
81a8217
 
 
 
 
 
 
7f72224
 
 
 
4d24cf6
7f72224
 
affe37f
 
e88a3a3
 
 
cf09724
 
 
4d24cf6
 
cf09724
 
 
4d24cf6
 
 
 
cf09724
 
 
e88a3a3
 
 
 
 
81a8217
 
 
cf09724
 
 
 
e88a3a3
 
9a6cb9a
 
 
 
 
 
 
 
 
 
 
81a8217
4d24cf6
 
daae64a
 
9a6cb9a
 
 
 
 
 
50e71ce
 
 
 
 
 
 
 
 
 
c70f19a
9a6cb9a
 
 
 
 
a5981db
c70f19a
 
9a6cb9a
 
 
 
 
50e71ce
9a6cb9a
 
98c8696
bd7fcd1
 
 
98c8696
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f11dd69
 
 
85f794d
 
f11dd69
85f794d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bd7fcd1
f11dd69
bd7fcd1
 
 
 
9a6cb9a
 
 
 
 
 
 
 
 
 
 
 
 
 
85f794d
 
 
 
 
c70f19a
7f72224
9a6cb9a
85f794d
9a6cb9a
81a8217
7f72224
 
 
 
 
 
 
d598032
81a8217
 
 
 
7f72224
81a8217
 
 
 
 
 
f1ae536
 
7f72224
f1ae536
7f72224
f1ae536
 
 
7f72224
 
 
 
81a8217
 
 
 
 
 
 
9a6cb9a
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
81a8217
9a6cb9a
81a8217
 
 
9a6cb9a
 
cf09724
 
1ad1eea
cf09724
 
1ad1eea
a93637f
 
c70f19a
a93637f
f1ae536
9a6cb9a
f1ae536
 
85f794d
 
 
 
f1ae536
9a6cb9a
 
c70f19a
 
 
 
 
85f794d
 
c70f19a
 
85f794d
 
 
 
 
 
 
c70f19a
 
0e600b9
9a6cb9a
0e600b9
 
9a6cb9a
 
 
 
 
 
 
85f794d
 
 
9a6cb9a
 
 
0e600b9
 
9a6cb9a
 
81a8217
 
 
d598032
81a8217
 
 
 
 
 
d598032
81a8217
 
 
 
e88a3a3
 
 
 
9a6cb9a
 
 
 
 
e88a3a3
 
 
9a6cb9a
0e600b9
 
 
81a8217
0e600b9
cf09724
a93637f
 
 
 
7f72224
 
 
81a8217
 
7f72224
e88a3a3
 
 
a93637f
0e600b9
 
 
9a6cb9a
 
 
 
e88a3a3
aa1490a
9a6cb9a
aa1490a
 
 
 
 
 
 
 
7f72224
 
aa1490a
9a6cb9a
aa1490a
 
 
9a6cb9a
 
e88a3a3
 
 
bf2d58a
 
9a6cb9a
aa1490a
bf2d58a
9a6cb9a
 
 
 
 
 
bf2d58a
 
 
 
aa1490a
 
 
 
 
 
 
 
 
 
 
 
c70f19a
aa1490a
bf2d58a
 
aa1490a
 
 
bf2d58a
aa1490a
7f72224
 
 
 
 
 
 
c70f19a
aa1490a
9a6cb9a
aa1490a
c70f19a
 
7f72224
c70f19a
aa1490a
c70f19a
aa1490a
 
c70f19a
 
 
 
 
 
 
 
7f72224
 
 
 
c70f19a
 
 
 
7f72224
c70f19a
 
 
 
7f72224
c70f19a
 
 
 
 
aa1490a
9a6cb9a
 
7f72224
9a6cb9a
 
e88a3a3
 
 
 
 
 
 
 
 
 
9a6cb9a
e88a3a3
 
 
 
 
 
 
 
a5981db
 
 
 
 
 
 
9a6cb9a
affe37f
 
e88a3a3
cf09724
 
 
 
 
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
e88a3a3
 
 
cf09724
85f794d
 
 
cf09724
e88a3a3
 
 
 
 
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
81a8217
7f72224
81a8217
 
7f72224
 
 
81a8217
 
 
 
 
 
 
 
 
7f72224
 
81a8217
7f72224
 
81a8217
 
7f72224
 
81a8217
 
 
7f72224
 
 
 
 
81a8217
 
 
 
 
 
 
 
 
 
7f72224
81a8217
 
7f72224
 
 
81a8217
7f72224
 
81a8217
 
7f72224
 
 
 
 
 
 
81a8217
 
 
 
 
7f72224
81a8217
 
 
 
7f72224
 
 
 
 
 
 
 
 
 
 
 
81a8217
 
 
7f72224
81a8217
7f72224
81a8217
 
 
 
 
7f72224
 
 
 
 
 
 
 
 
81a8217
 
7f72224
81a8217
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7f72224
81a8217
 
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
 
 
81a8217
9a6cb9a
 
81a8217
 
7f72224
 
 
 
 
 
d598032
 
 
85f794d
 
 
9a6cb9a
 
 
 
81a8217
9a6cb9a
 
81a8217
9a6cb9a
 
81a8217
 
 
 
 
 
d598032
 
81a8217
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
e88a3a3
 
 
9a6cb9a
81a8217
 
a5981db
cf09724
 
81a8217
cf09724
81a8217
e88a3a3
cf09724
 
affe37f
 
 
 
 
 
 
 
 
 
 
81a8217
 
 
7f72224
 
 
 
81a8217
 
 
 
 
 
 
db355ce
 
 
 
 
9a6cb9a
 
 
 
a93637f
9a6cb9a
 
affe37f
 
 
 
 
9a6cb9a
a93637f
 
9a6cb9a
 
 
 
 
 
81a8217
9a6cb9a
 
 
 
 
e88a3a3
cf09724
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
 
 
 
0e600b9
 
 
cf09724
0e600b9
 
 
 
 
 
 
85f794d
 
 
 
 
07bca1c
 
 
d598032
 
85f794d
 
daae64a
 
81a8217
7f72224
e88a3a3
 
 
78749d4
 
9a6cb9a
e88a3a3
 
 
9a6cb9a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
 
 
 
 
 
 
e88a3a3
 
 
 
 
0e600b9
 
 
 
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9a6cb9a
 
e88a3a3
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""amazon_relational.py — the Amazon mirror becomes LOCKED DATABASES under Data (wave 52, R14).

`harness/amazon_store.py` pulls SP-API report documents into the tenant's DuckDB mirror; this reads
that mirror and spawns the `ut_amzn_*` grids a person opens. It is `meta_relational.py`'s shape
with one source swapped, which is itself `odoo_relational.py`'s shape.

    plan(cur)                 the rows that WOULD be written — no store write at all
    apply_plan(rt, built)     create-or-merge every table and its rows, in ONE sync write
    refresh(rt, why)          both, with the mirror opened for this tenant

⭐⭐ **THE COLUMNS ARE DERIVED FROM THE MIRROR, NEVER TYPED OUT HERE**, exactly as Meta's are. For
Amazon that is doubly load-bearing: five of the seven tables have their columns MEASURED from the
report document's own header row at sync time (`amazon_schema.PROVENANCE`), so a hand-written field
list here would not merely rot, it would contradict the only measurement that exists. Add a column
to the report and it appears in the grid with no edit in this file.

⛔⛔ **R14 — THIS FILE IS WHERE THE CONNECTOR ENDS.** *"this is just database from Amazon, all its
fields mapped into our app so it can be seen under Data module and later be pulled into Sheet. All
database connector has this exact flow."* No dashboard, no module, no metric page. `Data` lists
these tables because they are `ut_*` tables; `sheet/BindingEditor.tsx:61` binds them on the `ut_`
prefix. Both are prefix-driven, so **neither needs a line of work here** — step 5 of `/connector` is
a verification, not a build.

⛔ **R12 — AMAZON IS A STANDALONE DATABASE SET.** Owner: *"Its all different. Assume that its a
whole new databases which they are."* `LINKS` below therefore joins Amazon tables only to OTHER
AMAZON TABLES: order lines to their order, pricing to its listing. **Nothing here joins to
`product_data`, to `ut_odoo_*`, or to the hand-maintained ASIN mastersheet.** ⭐ That is what
removes the [[amazon-conversion-k-is-inverted]] defect class outright: no Odoo unit is ever
converted to an Amazon unit, because the two never meet.

History stays in the mirror. Small snapshots may be materialised; a row or byte budget switches
them to read-through too. The shared document holds their schemas, never a truncated population.
"""
import hashlib
import json
import os
import re
import sys
from collections import OrderedDict
from pathlib import Path
from threading import RLock

_API = Path(__file__).resolve().parent
if str(_API.parent.parent / "platform") not in sys.path:
    sys.path.insert(0, str(_API.parent.parent / "platform"))

import odoo_relational as _odoo                                        # noqa: E402
from core import connector_spec                                        # noqa: E402
from harness import amazon_client, amazon_schema, amazon_store, datastore  # noqa: E402
from harness import amazon_ads_store                                   # noqa: E402

Refused = _odoo.Refused

#: ⛔ THE PREFIX IS `ut_amzn_`, AND IT MUST ALSO BE IN `routes_connectors.CONNECTED_PREFIXES` —
#: otherwise `is_connected` falls through and every Amazon table is silently re-capped at
#: `MAX_ROWS`, breaking standing rule 1 with nothing raising. `verify_amazon` asserts the pair.
PREFIX = "ut_amzn_"

#: `ut_*` key -> the mirror table `amazon_store` writes. Derived from ONE list so a table cannot
#: exist on one side and not the other.
#: S79-T13: the Ads tables (`ut_amzn_ads_*`, connector `amazon_ads`) live in the same mirror.
_ADS_SPEC = {s.key: s for s in connector_spec.tables_for("amazon_ads")}
SOURCE = {**{PREFIX + t: amazon_store.table_name(t) for t in amazon_store.DATABASE_TABLES},
          **{k: amazon_ads_store.table_name(s.source) for k, s in _ADS_SPEC.items()}}

#: ⭐⭐ **THE TABLE SHAPE IS NOW READ FROM `platform/core/connector_spec.py`, NOT TYPED HERE**
#: (W53-T37, contract C2). `LABELS`, `PINNED`, `IDENTITY` and `LINKS` keep their module-level
#: names because `verify_amazon`, `verify_meta.section_connector_spec` and the connector routes all
#: read them BY NAME — but each is a DERIVED VIEW over ONE declaration now, so the shape can no
#: longer be stated twice and say two things. Every comment that used to justify a literal here
#: (the two spines, R12, the report-vs-API spellings, the cardinalities) travelled with the values
#: into `connector_spec.AMAZON_TABLES`; read it there.
_SPEC = {**{s.key: s for s in connector_spec.tables_for("amazon")}, **_ADS_SPEC}


def _identity_columns(table, columns=()):
    """An Ads row is identified by its stored `_row_key`; every other table by amazon_schema."""
    if PREFIX + table in _ADS_SPEC:
        return ("_row_key",)
    return amazon_schema.row_key_columns_for(table, columns)


def _fallback_columns(table):
    if PREFIX + table in _ADS_SPEC:
        return ("_report_id",)
    return amazon_schema.row_fallback_columns_for(table)

#: ⛔ A `ut_*` KEY THAT `amazon_store` SPAWNS BUT THE SPEC DOES NOT DECLARE IS ABSENT FROM ALL FOUR
#: DICTS BELOW, ON PURPOSE. `verify_amazon.section_relational` asserts the four key sets equal
#: `SOURCE`'s, so the drift reddens a gate rather than raising at import inside a live Space, and
#: it names the missing table instead of a KeyError deep in `apply_plan`.
LABELS = {k: _SPEC[k].label for k in SOURCE if k in _SPEC}

#: The column each grid shows first. ⚠ Every one is a name Amazon's own reports use, so a table
#: whose header does not carry it simply has no pinned column rather than a broken one.
PINNED = {k: _SPEC[k].pinned for k in SOURCE if k in _SPEC}

#: Legacy descriptor view. Row keys use amazon_schema's complete identity and source lineage.
IDENTITY = {k: tuple(_SPEC[k].identity) for k in SOURCE if k in _SPEC}

#: `{ut_ key: [(this table's column, target ut_ key, the column it matches there), ...]}`.
#: ⚠ THE CARDINALITY IS DROPPED HERE AND KEPT IN THE SPEC. This view stays a 3-tuple because that
#: is the shape `verify_amazon.section_relational` unpacks; `connector_spec.edges_for` is what
#: `_fields_for` actually renders from, and it carries all four.
#: ⛔ AMAZON TO AMAZON ONLY (R12), and a link is DECLARED in the spec but VERIFIED AT SPAWN: five of
#: seven tables are `measured_at_sync`, so every entry is a hypothesis about a report column name
#: until `_fields_for` confirms BOTH sides carry it. A wrong guess here is cheap; a wrong guess in a
#: JOIN would not be. A table with no links is absent rather than present-and-empty, matching the
#: dict this replaced.
LINKS = {k: [(c, t, o) for c, t, o, _cardinality in _SPEC[k].links]
         for k in SOURCE if k in _SPEC and _SPEC[k].links}


def source_attribution(key):
    """Durable origin metadata for one materialised Amazon database.

    DPP 1.8 permits a database separation strategy or a mechanism that tags and identifies the
    origin of data.  The DuckDB mirror uses the reserved ``amzn_*`` namespace already; this stamp
    is the corresponding mechanism in the shared ``user_tables`` document.  It is written on
    every refresh so a user-facing label change cannot erase the Amazon origin or the exact raw
    table/report that supplied the rows.
    """
    if key not in SOURCE or key not in _SPEC:
        raise KeyError(f"unknown Amazon database {key!r}")
    return {
        "key": "amazon_ads" if key in _ADS_SPEC else "amazon_sp",
        "origin": ("Amazon Ads API" if key in _ADS_SPEC
                   else "Apify Amazon search dataset" if key == PREFIX + "keyword_ranks"
                   else "Amazon Services API"),
        "rawTable": SOURCE[key],
        "native": _SPEC[key].native,
        "readOnly": True,
    }


def credential_table_owner(_key, _rt):
    """Combined Amazon tables have row owners and never belong to one credential."""
    return None


CONNECTION_REVOCATIONS_KEY = "amazon_connection_revocations"


def record_connection_revocation(rt, connection_id, *, entries_before):
    """Keep metadata for retrying scoped cleanup after the credential itself has been removed."""
    cid = str(connection_id or "")
    entries = [{key: e[key] for key in ("id", "connection_id", "type", "slot", "label") if key in e}
               for e in entries_before if e.get("type") == "amazon_sp"]
    if not cid or not any(e.get("connection_id") == cid for e in entries):
        raise ValueError("Amazon revocation requires the original connection metadata")
    def save(doc):
        doc[cid] = {"state": "pending", "entries": entries}
        return doc
    rt.update(CONNECTION_REVOCATIONS_KEY, save, flush="sync")


def pending_connection_revocation(rt, connection_id):
    """Return retry metadata only while this connection's cleanup remains unfinished."""
    row = (rt.get(CONNECTION_REVOCATIONS_KEY) or {}).get(str(connection_id)) or {}
    return row.get("entries") if row.get("state") == "pending" else None


def connection_footprint(rt, connection_id, *, entries_before=None):
    """Disclose this connection's rows without claiming ownership of a combined table."""
    from contextlib import closing
    from core import keychain
    entries = amazon_store._ordered_account_entries(
        entries_before if entries_before is not None else keychain.list_entries(rt))
    cid = str(connection_id or "")
    if not cid or not any(e["connection_id"] == cid for e in entries):
        raise ValueError("Amazon footprint requires an explicit stored connection")
    path = amazon_store._tenant_mirror_path(rt.key, rt)
    out = {"type": "amazon_sp", "connectionId": cid, "action": "purge", "purge": [], "keep": [],
           "rows": 0, "rowsKnown": path.is_file(), "why": "Only this connection's rows are deleted. Shared tables remain."}
    projection = getattr(rt, "get_projection", None)
    definitions = projection("user_tables", drop=("rows",)) if callable(projection) else rt.get("user_tables")
    if definitions is None:
        definitions = {}
    if not isinstance(definitions, dict):
        raise RuntimeError("Amazon database definitions are unreadable")
    if not path.is_file():
        out["purge"] = [{"key": key, "label": defn.get("label") or key, "rows": 0, "rowsKnown": False}
                        for key, defn in definitions.items() if key in SOURCE and key not in _ADS_SPEC]
        return out
    import duckdb
    cursor = datastore.ro_cursor() if path.resolve() == Path(datastore.DB_PATH).resolve() else datastore.bounded_connection(
        duckdb.connect(str(path), read_only=True))
    with closing(cursor) as cur:
        bound = amazon_store._read_state(cur, "amzn_account_binding")
        legacy_owner = bound["detail"] if bound else entries[0]["connection_id"]
        for key in SOURCE.keys() - _ADS_SPEC.keys():
            defn = definitions.get(key) or {}
            columns = amazon_store._columns_of(cur, SOURCE[key])
            if not columns and key not in definitions:
                continue
            if not columns:
                count = 0
            elif amazon_schema.CONNECTION_ID in columns:
                count = cur.execute(f'SELECT count(*) FROM {SOURCE[key]} WHERE "_connection_id"=?', [cid]).fetchone()[0]
            else:
                count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0] if cid == legacy_owner else 0
            out["purge"].append({"key": key, "label": defn.get("label") or LABELS.get(key) or key,
                                 "rows": count, "rowsKnown": True})
            out["rows"] += count
    out["purge"].sort(key=lambda table: table["key"])
    return out


def purge_connection(rt, connection_id, *, entries_before=None):
    """Remove this account's stored snapshots and observations while retaining shared definitions."""
    cid = str(connection_id or "")
    if entries_before is None:
        entries_before = pending_connection_revocation(rt, cid)
    if not entries_before:
        raise ValueError("Amazon scoped cleanup requires saved revocation metadata")
    record_connection_revocation(rt, cid, entries_before=entries_before)
    footprint = connection_footprint(rt, cid, entries_before=entries_before)
    from contextlib import closing
    con, _path = amazon_store._connect_tenant_mirror(rt.key, rt)
    with closing(con):
        binding = amazon_store._read_state(con, "amzn_account_binding")
    entries = amazon_store._ordered_account_entries(entries_before)
    legacy_owner = binding["detail"] if binding else entries[0]["connection_id"]
    def sweep(doc):
        for key, table in doc.items():
            if key not in SOURCE or key in _ADS_SPEC or not isinstance(table, dict):
                continue
            rows = table.get("rows") or {}
            for rid, row in list(rows.items()):
                if row.get(amazon_schema.CONNECTION_ID, legacy_owner) == cid:
                    rows.pop(rid)
        return doc
    rt.update("user_tables", sweep, flush="sync")
    removed = amazon_store.purge_connection(rt.key, cid, rt=rt, entries_before=entries_before)
    def complete(doc):
        doc[cid] = {"state": "done", "snapshotId": removed["snapshotId"]}
        return doc
    rt.update(CONNECTION_REVOCATIONS_KEY, complete, flush="sync")
    return {"purged": [table["key"] for table in footprint["purge"]], "refused": [], "failed": [],
            "rows": removed["rows"], "rowsKnown": True, "footprint": footprint}


#: Columns whose NAME makes them a date whatever they are stored as. Amazon reports ISO-8601.
_DATEISH = ("purchase_date", "last_updated_date", "payments_date", "settlement_start_date",
            "settlement_end_date", "deposit_date", "posted_date", "open_date", "date",
            "report_date", "start_date", "end_date", "_snapshot_date", "month", "_time",
            "valid_from", "valid_to")

#: ⛔ MONEY IS `currency`, NEVER `int`. An Amazon `item-price` is fractional in every marketplace,
#: and an int column would round every row of a settlement. The grid shows what the report said and
#: converts nothing.
_MONEY = {"item_price", "item_tax", "shipping_price", "shipping_tax", "gift_wrap_price",
          "gift_wrap_tax", "item_promotion_discount", "ship_promotion_discount", "price",
          "amount", "total_amount", "principal", "shipping", "tax", "promotion", "fee",
          "your_price", "landed_price", "listing_price", "open_date_price",
          "sales_by_asin_ordered_product_sales_amount",
          "sales_by_asin_ordered_product_sales_b2_b_amount"}
_MONEY.update({"estimated_fee_total", "estimated_referral_fee_per_unit",
               "estimated_variable_closing_fee", "estimated_order_handling_fee_per_order",
               "estimated_pick_pack_fee_per_unit", "estimated_weight_handling_fee_per_unit",
               "expected_fulfillment_fee_per_unit", "estimated_monthly_storage_fee"})

#: S78-T01: money the report stores as text or names without an `_amount` / `_fee` suffix.
#: ⚠ No `_price` suffix rule: `attributes_list_price` and the `*_median_*_price` details are JSON.
_MONEY.update({"business_price", "label_cost", "fee_total", "sales_price", "item_price_per_unit",
               "featuredoffer_price", "lowest_price_new_plus_shipping", "lowest_price_used",
               "recommended_sales_price", "estimated_cost_savings_of_recommended_actions",
               "estimated_storage_cost_next_month", "sales_last_30_days", "amount_charged",
               "rate_surcharge", "estimated_referral_fee_per_item", "amount_per_unit",
               "amount_total", "base_rate", "est_base_msf", "est_sus", "utilization_surcharge_rate",
               "estimated_future_fee_current_selling_on_amazon_future_fulfillment_fees",
               "estimated_future_order_handling_fee_per_order",
               "estimated_future_pick_pack_fee_per_unit",
               "estimated_future_weight_handling_fee_per_unit",
               "expected_future_fulfillment_fee_per_unit", "lost_revenue_due_to_oos",
               "non_subscriber_average_revenue", "subscriber_average_revenue",
               "total_subscriptions_revenue"})
_MONEY_PREFIX = ("cost_", "sales_shipped_last_", "estimated_ais_", "sns_sale_price_week_",
                 "revenue_from_")
_MONEY_INFIX = ("_life_time_value_",)

#: ⭐ S78-T01: rates and shares are `pct`, and the grid shows a `pct` value as the number plus
#: "%" with no scaling. So the scale is MEASURED per column on the staging mirror (2026-10-20):
#: `_PERCENT` columns already hold 0 to 100, `_FRACTION_PERCENT` columns hold 0 to 1 and are
#: multiplied by 100 in the read path (`grid_source`), so 0.15 reads "15%" and never "0.15%".
#: ⛔ A rate whose scale was not measured stays out of both sets (see `oos_rate_week_*`).
_PERCENT_SUFFIX = ("_percentage", "_percentage_b2_b")
_PERCENT = {"browse_node_metrics_occurrence_percentage_all_products",
            "sales_by_asin_refund_rate", "sales_by_asin_refund_rate_b2_b",
            "cart_add_data_asin_cart_add_share", "click_data_asin_click_share",
            "impression_data_asin_impression_share", "purchase_data_asin_purchase_share",
            "cart_add_data_total_cart_add_rate", "click_data_total_click_rate",
            "purchase_data_total_purchase_rate", "revenue_penetration",
            "coupons_revenue_penetration", "share_of_coupon_subscriptions",
            "subscriber_retention_for30_days", "subscriber_retention_for90_days",
            *(f"{metric}_for{n}_percent_seller_funding" for metric in
              ("revenue_penetration", "signup_conversion") for n in (0, 5, 10))}
_FRACTION_PERCENT = {"click_share", "conversion_share", "click_data_click_rate",
                     "purchase_data_conversion_rate", "combination_pct"}
#: Account health reports each rate metric and its target as a 0 to 1 fraction.
_FRACTION_PERCENT_RE = re.compile(r"[a-z_]+_rate(?:_afn|_mfn)?_(?:rate|target_value)")

#: Non-money numbers stored as text. With the DOUBLE columns that are not money, they take
#: `currency` (the grid's only fractional type) with an EMPTY currency symbol, so no "$" shows.
_NUMBER = {"storage_utilization_ratio"}


def _is_money(col):
    return (col in _MONEY or col.endswith(("_amount", "_fee", "_fees")) or col.startswith(_MONEY_PREFIX)
            or any(part in col for part in _MONEY_INFIX))


def _is_fraction_percent(col):
    return col in _FRACTION_PERCENT or bool(_FRACTION_PERCENT_RE.fullmatch(col))


def _is_percent(col):
    return col in _PERCENT or col.endswith(_PERCENT_SUFFIX) or _is_fraction_percent(col)

# Inventory reports remain VARCHAR when a quantity column mixes numbers and blanks.
# Names identify units; identifiers such as SKU and FNSKU must stay text.
_QUANTITY = {"quantity", "quantity_purchased", "starting_warehouse_balance",
             "in_transit_between_warehouses", "receipts", "customer_shipments",
             "customer_returns", "vendor_returns", "warehouse_transfer_in",
             "warehouse_transfer_out", "found", "lost", "damaged", "disposed",
             "other_events", "ending_warehouse_balance", "unknown_events"}


def _label(col):
    return connector_spec._label(col)


def _ftype(col, duck_type):
    """The grid field type for one mirror column. Name first, storage second.

    ⚠ NAME BEATS STORAGE FOR DATES ON PURPOSE. Every flat-file column arrives as text, so a
    purchase date is a VARCHAR in the mirror and would render as a string in the grid unless the
    name is allowed to override. The store's `_coltype` already refuses to guess a type for text
    it cannot parse; this is the presentation half of the same judgement.
    """
    if col == "per_unit_volume":
        return "int"
    if any(col == s or col.endswith(s) for s in _DATEISH):
        return "date"
    if _is_percent(col):
        return "pct"
    if _is_money(col) or col in _NUMBER:
        return "currency"
    base = str(duck_type or "").upper().split("(")[0]
    if base in ("DOUBLE", "FLOAT", "DECIMAL"):
        return "currency"
    if col in _QUANTITY or base in ("BIGINT", "INTEGER", "HUGEINT"):
        return "int"
    return "text"


def _format(col, duck_type):
    """The display format a field ships with, or None for the grid's default."""
    kind = _ftype(col, duck_type)
    if col == "per_unit_volume":
        return {"decimals": 4}
    if kind == "pct":
        return {"decimals": 2}
    if kind == "currency" and not _is_money(col):
        return {"currencySymbol": "", "decimals": 2}
    return None


def mirror_columns(cur, table, *, strict=False):
    """[(name, type)] for a mirror table, or [] when it has never been synced."""
    if strict:
        # A successful empty metadata query proves absence on this cursor only.
        return [(name, kind) for name, kind in cur.execute(
            "SELECT column_name, data_type FROM information_schema.columns "
            "WHERE table_catalog = current_database() AND table_schema = current_schema() "
            "AND table_name = ? ORDER BY ordinal_position", [table]).fetchall()
                if name not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
    try:
        return [(r[1], r[2]) for r in cur.execute(f"PRAGMA table_info('{table}')").fetchall()
                if r[1] not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
    except Exception:                                                  # noqa: BLE001
        return []


#: ⭐⭐ S67-T30 (instr 13, R29 / O23) — THE PRODUCT LOOKUPS. Every Amazon database with an ASIN
#: shows its "Parent ASIN" and "SKU" beside it, and every one with a SKU its "ASIN" and "Parent
#: ASIN", with no configuration. They are PLAIN TEXT fields whose value the SERVER derives, not
#: link fields, on purpose: a link renders as a count bubble, is not groupable
#: (`types.ts:isGroupableField`) and has no SQL binding for a filter (`routes_odoo_tables.
#: _filter_columns` and `_group_pin` read `spec["cols"]` only). A text field with a `cols`
#: expression groups, filters and sorts through the machinery every other column already uses.
#: ⚠ The ASIN / SKU column of a table is the `from_col` of its declared listings link (on `asin1`
#: / `seller_sku`), so the spec's links are the one statement of which column is which. A table
#: that carries its own `parent_asin` (pricing, economics) gains no derived Parent ASIN; one that
#: carries its own SKU or ASIN gains no duplicate of it; listings gains Parent ASIN only.
#: ⛔ Parent comes from `amazon_store.asin_parents_sql` ALONE (latest catalog, else economics, else
#: the ASIN itself), never from `amzn_catalog_items` directly: that table keeps older snapshot days
#: (D-3031) and five own ASINs have no catalog row. SKU and ASIN come from the LATEST listings day
#: only, because listings keeps one copy per snapshot date.
LISTINGS = PREFIX + "listings"
LOOKUP_PARENT, LOOKUP_SKU, LOOKUP_ASIN = "lookup_parent_asin", "lookup_sku", "lookup_asin"
LOOKUP_KEYS = frozenset((LOOKUP_PARENT, LOOKUP_SKU, LOOKUP_ASIN))
#: Native columns that already show a row's ASIN (orders, order items, replenishment carry `asin`).
_NATIVE_ASIN = frozenset(("asin", "asin1", "child_asin"))
_LOOKUP_LABELS = {LOOKUP_PARENT: "Parent ASIN", LOOKUP_SKU: "SKU", LOOKUP_ASIN: "ASIN"}
_LOOKUP_DESCRIPTIONS = {
    LOOKUP_PARENT: ("The parent ASIN of this row's product, from Amazon's catalog, else the "
                    "economics report, else the ASIN itself. Click it to open the listing."),
    LOOKUP_SKU: ("Your seller SKUs for this row's ASIN in the latest listings report. "
                 "Click it to open the listing."),
    LOOKUP_ASIN: ("The ASIN of this row's SKU in the latest listings report. "
                  "Click it to open the listing."),
}


def _product_columns(key):
    """`(asin_col, sku_col, asin_link_key, sku_link_key)` as the spec declares them for `key`."""
    if key == LISTINGS:
        return "asin1", "seller_sku", None, None     # the row IS the listing: Parent ASIN only
    asin = sku = asin_via = sku_via = None
    for edge in connector_spec.edges_for(key):
        if edge["target"] != LISTINGS:
            continue
        if edge["to_col"] == "asin1" and asin is None:
            asin, asin_via = edge["from_col"], edge["key"]
        elif edge["to_col"] == "seller_sku" and sku is None:
            sku, sku_via = edge["from_col"], edge["key"]
    return asin, sku, asin_via, sku_via


def _latest_listings_sql(listing_names):
    """`SELECT sku, asin` over the LATEST listings snapshot day, or None without both columns."""
    if not {"seller_sku", "asin1"} <= set(listing_names):
        return None
    table = SOURCE[LISTINGS]
    if amazon_schema.CONNECTION_ID in listing_names:
        latest = (', max("_snapshot_date") OVER (PARTITION BY "_connection_id") AS latest_date'
                  if "_snapshot_date" in listing_names else "")
        date = ', "_snapshot_date"' if latest else ""
        predicate = ' AND "_snapshot_date" IS NOT DISTINCT FROM latest_date' if latest else ""
        return ('SELECT DISTINCT "_connection_id", sku, asin FROM (SELECT "_connection_id", '
                'TRIM(CAST(seller_sku AS VARCHAR)) AS sku, TRIM(CAST(asin1 AS VARCHAR)) AS asin'
                f'{date}{latest} FROM {table}) WHERE sku <> \'\' AND asin <> \'\'{predicate}')
    latest = (f' AND "_snapshot_date" = (SELECT MAX("_snapshot_date") FROM {table})'
              if "_snapshot_date" in listing_names else "")
    return ("SELECT DISTINCT sku, asin FROM (SELECT TRIM(CAST(seller_sku AS VARCHAR)) AS sku, "
            f"TRIM(CAST(asin1 AS VARCHAR)) AS asin FROM {table} "
            f"WHERE seller_sku IS NOT NULL AND asin1 IS NOT NULL{latest}) "
            "WHERE sku <> '' AND asin <> ''")


# One current resolution per tenant mirror, with a small process-wide tenant budget.
_PARENT_CACHE = OrderedDict()
_PARENT_CACHE_MAX = 8
_PARENT_CACHE_LOCK = RLock()
_PARENT_SOURCES = ("listings", "catalog_items", "pricing", "economics")


def _parent_revision(cur):
    """Tenant mirror identity and the resolver's source sync markers, or no safe memo key.

    The cursor's actual file identifies the tenant, never the mutable global DB_PATH.
    Unnamed in-memory mirrors and mirrors without sync bookkeeping stay uncached.
    """
    try:
        database = cur.execute("SELECT current_database()").fetchone()[0]
        path = next((row[2] for row in cur.execute("PRAGMA database_list").fetchall()
                     if row[1] == database and row[2]), None)
        if not path:
            return None
        entities = [amazon_store.entity_key(prefix + source)
                    for source in _PARENT_SOURCES
                    for prefix in ("", "sync_tick_", amazon_store.WATERMARK_PREFIX)]
        entities.extend(amazon_store.ENTITY_STATE_KEYS[source] for source in _PARENT_SOURCES
                        if source in amazon_store.ENTITY_STATE_KEYS)
        revision = tuple(cur.execute(
            "SELECT entity, phase, cursor_wd, rows, updated_at FROM _sync_state "
            f"WHERE entity IN ({','.join('?' for _ in entities)}) ORDER BY entity",
            entities).fetchall())
        return (str(Path(path).resolve()), revision) if revision else None
    except Exception:                                                  # noqa: BLE001
        return None


def _parent_lookup_sql(cur):
    """Resolve parents once per source revision; pages join the resolved data, not its ranks.

    JSON is a read-only SQL relation here, with SQL quotes escaped separately from JSON.
    No temporary table or tenant mirror write is needed, and the complete mapping is retained.
    """
    sources = {"amzn_" + source: amazon_store._columns_of(cur, "amzn_" + source)
               for source in _PARENT_SOURCES}
    scoped = [table for table, names in sources.items() if amazon_schema.CONNECTION_ID in names]
    if scoped:
        base = amazon_store.asin_parents_sql(cur)
        if base is None:
            return None
        ids = cur.execute(" UNION ".join(f'SELECT DISTINCT "_connection_id" FROM {table}'
                                         for table in scoped)).fetchall()
        parts = []
        for (cid,) in ids:
            literal = "NULL::VARCHAR" if cid is None else "'" + str(cid).replace("'", "''") + "'"
            ctes = []
            for table, names in sources.items():
                if not names:
                    continue
                where = (f'"_connection_id" IS NOT DISTINCT FROM {literal}'
                         if amazon_schema.CONNECTION_ID in names else "FALSE")
                ctes.append(f'{table} AS (SELECT * FROM main.{table} WHERE {where})')
            parts.append(f'(WITH {", ".join(ctes)} SELECT {literal} AS "_connection_id", p.* FROM ({base}) p)')
        return " UNION ALL ".join(parts) if parts else None
    identity = _parent_revision(cur)
    if identity is None:
        return amazon_store.asin_parents_sql(cur)
    tenant, revision = identity
    with _PARENT_CACHE_LOCK:
        cached = _PARENT_CACHE.get(tenant)
        if cached is not None and cached[0] == revision:
            _PARENT_CACHE.move_to_end(tenant)
            return cached[1]
        sql = amazon_store.asin_parents_sql(cur)
        resolved = None
        if sql is not None:
            parents = {asin: parent for asin, parent, _source in cur.execute(sql).fetchall()}
            payload = json.dumps(parents, ensure_ascii=True, separators=(",", ":"))
            resolved = ("SELECT key AS asin, json_extract_string(value, '$') AS parent_asin "
                        f"FROM json_each('{payload.replace(chr(39), chr(39) * 2)}')")
        # Never publish a resolution if a sync advanced while it was being built.
        if _parent_revision(cur) == identity:
            _PARENT_CACHE[tenant] = (revision, resolved)
            _PARENT_CACHE.move_to_end(tenant)
            while len(_PARENT_CACHE) > _PARENT_CACHE_MAX:
                _PARENT_CACHE.popitem(last=False)
        return resolved


def lookups(cur, key, names=None, *, allowed=True):
    """`[{key, label, after, expr, opens, description}]`: the product lookups `key` gains.

    `expr` is ONE correlated scalar subquery per lookup, qualified by the mirror table's own name
    (every reader selects `FROM <table>` unaliased), so it binds to the outer row and never to the
    resolver's own `asin`. DuckDB decorrelates it into a single hash join per query, which is what
    keeps a grouped page of a read-through grid set-based rather than per-row. No match is NULL,
    which every reader serialises as a blank cell. Nothing is capped (standing rule 1).
    """
    if names is None:
        names = {name for name, _dtype in mirror_columns(cur, SOURCE[key])}
    asin, sku, asin_via, sku_via = _product_columns(key)
    asin = asin if asin in names else None
    sku = sku if sku in names else None
    if not asin and not sku:
        return []
    if not allowed:
        # Keep declared cells blank without inspecting a denied lookup dependency.
        keys = []
        if asin:
            if "parent_asin" not in names:
                keys.append((LOOKUP_PARENT, asin, asin_via))
            if not sku:
                keys.append((LOOKUP_SKU, asin, asin_via))
        elif sku:
            if not names & _NATIVE_ASIN:
                keys.append((LOOKUP_ASIN, sku, sku_via))
            if "parent_asin" not in names:
                keys.append((LOOKUP_PARENT, sku, sku_via))
        return [{"key": name, "label": _LOOKUP_LABELS[name], "after": after,
                 "expr": "CAST(NULL AS VARCHAR)", "opens": {"via": via} if via else None,
                 "description": _LOOKUP_DESCRIPTIONS[name]}
                for name, after, via in keys if name not in names]
    listing_names = amazon_store._columns_of(cur, SOURCE[LISTINGS])
    parents = None if "parent_asin" in names else _parent_lookup_sql(cur)
    latest = _latest_listings_sql(listing_names)
    table = SOURCE[key]
    out = []
    scoped = amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, table)
    parent_scoped = any(amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, "amzn_" + source)
                        for source in _PARENT_SOURCES)
    def account_match(alias):
        tagged = parent_scoped if alias == "_p" else amazon_schema.CONNECTION_ID in listing_names
        if scoped and tagged:
            return f' AND {alias}."_connection_id" IS NOT DISTINCT FROM {table}."_connection_id"'
        if scoped:
            return " AND FALSE"
        return f' AND {alias}."_connection_id" IS NULL' if tagged else ""

    def add(lookup_key, after, expr, via):
        if lookup_key in names:
            return
        out.append({"key": lookup_key, "label": _LOOKUP_LABELS[lookup_key], "after": after,
                    "expr": expr, "opens": {"via": via} if via else None,
                    "description": _LOOKUP_DESCRIPTIONS[lookup_key]})

    if asin:
        ref = f"TRIM(CAST({table}.{_quoted(asin)} AS VARCHAR))"
        via = asin_via if "asin1" in listing_names else None
        if parents:
            add(LOOKUP_PARENT, asin,
                f"(SELECT _p.parent_asin FROM ({parents}) AS _p WHERE _p.asin = {ref}{account_match('_p')})", via)
        if latest and not sku:
            add(LOOKUP_SKU, asin,
                f"(SELECT string_agg(DISTINCT _l.sku, ', ' ORDER BY _l.sku) "
                f"FROM ({latest}) AS _l WHERE _l.asin = {ref}{account_match('_l')})", via)
    elif sku and latest:
        ref = f"TRIM(CAST({table}.{_quoted(sku)} AS VARCHAR))"
        via = sku_via if "seller_sku" in listing_names else None
        # S78-T01: a table that carries its own ASIN column gains no second "ASIN".
        if not names & _NATIVE_ASIN:
            add(LOOKUP_ASIN, sku,
                f"(SELECT string_agg(DISTINCT _l.asin, ', ' ORDER BY _l.asin) "
                f"FROM ({latest}) AS _l WHERE _l.sku = {ref}{account_match('_l')})", via)
        if parents:
            add(LOOKUP_PARENT, sku,
                f"(SELECT string_agg(DISTINCT _p.parent_asin, ', ' ORDER BY _p.parent_asin) "
                f"FROM ({latest}) AS _l JOIN ({parents}) AS _p ON _p.asin = _l.asin "
                + ('AND _p."_connection_id" IS NOT DISTINCT FROM _l."_connection_id" '
                   if parent_scoped and amazon_schema.CONNECTION_ID in listing_names else "")
                + f"WHERE _l.sku = {ref}{account_match('_l')})", via)
    return out


def _fields_for(cur, key, *, read_through=False):
    """Derive source fields and include the synthetic row stamp only for materialized definitions."""
    cols = mirror_columns(cur, SOURCE[key])
    if not cols:
        return []
    declared = {field["name"]: field for field in connector_spec.AMAZON_FIELDS.get(key, ())}
    written = connector_spec.AMAZON_LABELS.get(key, {})
    shown = default_columns(key)
    names = {c[0] for c in cols}
    pinned = PINNED.get(key) if PINNED.get(key) in names else None
    # S67-T30: each lookup lands IMMEDIATELY after the column it derives from, shown by default,
    # and the ASIN / SKU column it sits beside is shown by default too (inventory's `msku` was not).
    derived = {}
    for lookup in lookups(cur, key, names):
        derived.setdefault(lookup["after"], []).append(lookup)
    product = {c for c in _product_columns(key)[:2] if c in names}
    out = []
    # The identity column goes FIRST, the rest keep the report's own order. A record reached
    # through a link is titled by its first field (the record drawer's frame label), and a
    # report's first column is whatever Amazon put there (`item_name`, `_data_origin`), so a
    # listing opened from an order must lead with its seller SKU, as Odoo's pinned fields do.
    for name, dtype in sorted(cols, key=lambda col: col[0] != pinned):
        fmt = _format(name, dtype)
        out.append(_odoo._preset({
            "key": name,
            "label": declared.get(name, {}).get("label") or written.get(name) or _label(name),
            "type": _ftype(name, dtype),
            "source": "overlay",
            "default": name == pinned or name in shown or name in product or name == amazon_schema.STORE,
            "pinned": name == pinned,
            **({"description": amazon_schema.REVIEW_UNAVAILABLE_NOTE}
               if name == "asin" and key in (PREFIX + "review_topics", PREFIX + "review_trends") else {}),
            **({"description": amazon_schema.SQP_LIMIT_NOTE}
               if name == "asin" and key == PREFIX + "search_query_performance" else {}),
            **({"format": fmt} if fmt else {}),
        }, flow="amazon_relational"))
        for lookup in derived.get(name, ()):
            field = {"key": lookup["key"], "label": lookup["label"], "type": "text",
                     "source": "overlay", "default": True,
                     "description": lookup["description"]}
            if lookup["opens"]:
                field["opens"] = dict(lookup["opens"])
            out.append(_odoo._preset(field, flow="amazon_relational"))
    live_columns = {key: names}
    for edge in connector_spec.edges_for(key):
        target = edge["target"]
        if target not in live_columns:
            columns = mirror_columns(cur, SOURCE.get(target, ""))
            if columns:
                live_columns[target] = {name for name, _dtype in columns}
    out.extend(_odoo._preset(field, flow="amazon_relational")
               for field in connector_spec.system_link_fields(key, live_columns).get(key, ()))
    if amazon_schema.STORE in names:
        excess = max(0, sum(bool(field.get("default")) for field in out) - 12)
        for field in reversed(out):
            if excess and field.get("default") and field["key"] not in {
                    pinned, amazon_schema.STORE, *product, *LOOKUP_KEYS, *_HISTORY_SHOWN}:
                field["default"] = False
                excess -= 1
    if not read_through and "refreshed" not in names:
        out.append(_odoo._preset(_odoo._refreshed_field(), flow="amazon_relational"))
    return out


def default_columns(key):
    """The columns `key` opens on beyond the pinned, product, lookup and link fields."""
    declared = connector_spec.AMAZON_DEFAULT_COLUMNS.get(key)
    if declared is None and key.removeprefix(PREFIX) in amazon_store.HISTORY_TABLES:
        return _UNDECLARED_DEFAULT_COLUMNS | _HISTORY_SHOWN
    return frozenset(declared) if declared is not None else _UNDECLARED_DEFAULT_COLUMNS


#: A history table always opens on the dates each version held.
_HISTORY_SHOWN = frozenset(("valid_from", "valid_to"))


#: The shared fallback for a table `connector_spec.AMAZON_DEFAULT_COLUMNS` does not declare yet
#: (one the staging mirror has never synced). A declared table reads only its own list.
_UNDECLARED_DEFAULT_COLUMNS = frozenset({
    "amazon_order_id", "purchase_date", "order_status", "sku", "seller_sku", "asin", "asin1",
    "quantity", "quantity_purchased", "item_price", "item_status", "product_name", "item_name",
    "settlement_id", "amount", "amount_type", "amount_description", "posted_date",
    "parent_asin", "child_asin", "search_term", "search_frequency_rank",
    "traffic_by_asin_buy_box_percentage", "sales_by_asin_units_ordered",
    "backend_keywords",
    "month", "search_query", "impressions", "clicks", "cart_adds", "purchases", "source_basis",
    "keyword", "date", "organic_rank", "sponsored_rank", "sponsored_rank_basis",
})

#: Every name any Amazon table may open on (`verify_agent_knowledge` reads it by name).
_DEFAULT_COLUMNS = _UNDECLARED_DEFAULT_COLUMNS.union(*connector_spec.AMAZON_DEFAULT_COLUMNS.values())


#: The original seven used first-field identities. Retained for legacy-id migration
#: and for distinguishing history from the original small snapshots.
_LEGACY_IDENTITY_TABLES = frozenset(("orders", "order_items", "listings", "settlements",
                                     "inventory", "pricing", "brand_analytics"))

# The original seven do not declare snapshot/window metadata. Keep their four small
# tables materialised; every newer non-snapshot report/API is history, including monthly ones.
READ_THROUGH = frozenset(
    PREFIX + table for table, spec in amazon_client.REPORT_TYPES.items()
    if table in {"settlements", "orders", "order_items", "transactions", "economics", "storage_fees"}
    or (table not in _LEGACY_IDENTITY_TABLES and not spec.get("snapshot")
        and (spec.get("report") or spec.get("api") or spec.get("monthly")))) | frozenset(
            PREFIX + table for table in amazon_store.DERIVED_TABLES) | frozenset(
            # Change history grows without a cap (standing rule 1), so it is never stored.
            PREFIX + table for table in amazon_store.HISTORY_TABLES) | frozenset((
            PREFIX + "competitor_snapshots", PREFIX + "competitor_images"))


def _quoted(name):
    return '"' + name.replace('"', '""') + '"'


#: ⛔⛔ HOTFIX 2026-09-27 (D-3284's cost): an Amazon snapshot table larger than this is served
#: READ-THROUGH, never stored in the tenant document. The per-table `MAX_TABLE_BYTES` (48 MB) let
#: catalog items (32.9 MB for 2,317 rows), pricing (11.7 MB) and FBA inventory (7.5 MB) in once
#: connector databases stopped counting toward MAX_TABLES, and `user_tables` doubled to 118 MB.
#: Every whole-document read and write doubled with it: the 2 GB DigitalOcean staging node ran
#: out of memory and PostgreSQL restarted (2026-09-27 16:36Z), and on Neon each read is billed at
#: the document's text size. Small tables stay stored; nothing is capped (standing rule 1).
AMAZON_STORED_BYTES = 4 * 1024 * 1024


def _read_through(cur, key, count, *, columns=None):
    """Choose storage before reading rows; estimate wide snapshots with a bounded sample."""
    ut = _odoo._ut()
    if key in READ_THROUGH or count > ut.MAX_ROWS:
        return True
    if not count:
        return key == PREFIX + "inventory"
    names = [name for name, _dtype in (columns if columns is not None
                                     else mirror_columns(cur, SOURCE[key]))]
    sample = cur.execute(f"SELECT * FROM {SOURCE[key]} LIMIT 32").fetchall()
    # Include the stored id, refreshed stamp and row-object punctuation, with the same
    # ASCII escaping as the document writer. The largest sampled row is conservative.
    size = max((len(json.dumps(dict(zip(names, row)), ensure_ascii=True,
                              default=str).encode("utf-8")) + 96 for row in sample), default=0)
    return count * size > min(ut.MAX_TABLE_BYTES, AMAZON_STORED_BYTES)


#: Legacy resident-loop diagnostic. Tenant readiness is read through restore_pending.
RESTORE_PENDING = {"since": None}


def restore_pending(rt=None):
    """Read the Amazon portion of the tenant's shared catch-up state."""
    from harness import mirror_store
    try:
        if rt is None:
            from harness import runtime
            tenant = runtime.tenant_for_store_path()
            if not tenant:
                return True
            rt = runtime.get_runtime(tenant)
        sources = mirror_store.connected_sources(rt)
        return "amazon" in sources and mirror_store.source_catching_up(rt, "amazon")
    except Exception:
        return True


_UNRESOLVED = object()


def source_status(cur, key):
    """D-2643: `{empty, code, reason}` from `amazon_store.table_empty_status` for a present table.

    An empty table always answers. A table with rows answers only beside a recorded Amazon
    refusal, with `empty` False. None otherwise, and for keys outside the declared databases.
    """
    table = key.removeprefix(PREFIX)
    if key not in SOURCE or table not in amazon_store.DATABASE_TABLES:
        return None
    present = cur.execute(f"SELECT 1 FROM {_quoted(SOURCE[key])} LIMIT 1").fetchone() is not None
    # `rows=1` marks a table that holds rows. The producer then answers a refusal only.
    status = amazon_store.table_empty_status(cur, table, rows=int(present))
    if not status.get("code") or (present and status["code"] != "refused_by_amazon"):
        return None
    return {"empty": not present, "code": status["code"], "reason": status.get("reason") or ""}


def missing_source(key, rt=None, *, pending=None, cur=None):
    """The ONE sentence every door gives for an Amazon table this server's mirror does not hold yet.

    `pending` is a `restore_pending()` answer the caller already holds, so a listing reads the keychain once.
    `cur` is the admitted mirror cursor. With it, a saved refusal or a measured empty report names
    its own reason instead of the loading sentence.
    """
    table = key.removeprefix(PREFIX)
    # Schema-only probe first: a mirror without sync bookkeeping keeps the loading sentences.
    if (cur is not None and key != PREFIX + "inventory"
            and table in amazon_store.DATABASE_TABLES and mirror_columns(cur, "_sync_state")):
        status = amazon_store.table_empty_status(cur, table, rows=None)
        if (status.get("code") and status.get("reason")
                and status["reason"] != amazon_store.loading_reason(table)):
            return {"missing": status["reason"],
                    "sourceStatus": {"empty": True, "code": status["code"], "reason": status["reason"]}}
    if key == PREFIX + "inventory":
        if rt is None:
            from harness import runtime
            tenant = runtime.tenant_for_store_path()
            rt = runtime.get_runtime(tenant) if tenant else None
        definitions = rt.get_projection("user_tables", drop=("rows",)) if rt is not None else {}
        status = ((definitions or {}).get(key) or {}).get("sourceStatus") or {}
        if status.get("empty") is True and isinstance(status.get("reason"), str) and status["reason"].strip():
            return {"missing": status["reason"]}
        return {"missing": "Inventory request metadata is unavailable, "
                           "so retry Amazon sync to record the FBA ledger request."}
    label = key.removeprefix(PREFIX).replace("_", " ")
    if restore_pending(rt) if pending is None else pending:
        return {"missing": f"Amazon {label} is still loading after a server restart, "
                           "so ask again in 5 minutes."}
    return {"missing": f"Amazon {label} is still loading from Amazon, "
                       "so ask again after the next Amazon sync finishes."}


def _text(value):
    return "" if value is None else str(value)


def _number(value):
    return None if value in (None, "") else float(value)


def _integer(value):
    return None if value in (None, "") else int(value)


def grid_source(cur, key, *, columns=None, allow_lookups=True, status=_UNRESOLVED):
    """One binding from the current mirror schema, or an explicit unloaded-mirror reason.

    `status` is a `source_status` answer the caller already holds.
    """
    columns = mirror_columns(cur, SOURCE[key]) if columns is None else columns
    columns = [(name, kind) for name, kind in columns if name not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
    if not columns:
        return missing_source(key, cur=cur)
    names = {name for name, _dtype in columns}
    table = key.removeprefix(PREFIX)
    prepared = {row[0] for row in cur.execute(
        "SELECT column_name FROM information_schema.columns WHERE table_catalog = current_database() "
        "AND table_schema = current_schema() AND table_name = ? AND column_name IN (?, ?, ?)",
        [SOURCE[key], amazon_schema.IDENTITY_OCCURRENCE, amazon_schema.IDENTITY_PID,
         amazon_schema.CONNECTION_ID]).fetchall()}
    identity_names = names | prepared
    identity = _identity_columns(table, identity_names)
    fallback = sorted(set((*identity, *_fallback_columns(table))))
    source = _quoted(SOURCE[key])

    def value(name, owner=source):
        return (f"NULLIF(CAST({owner}.{_quoted(name)} AS VARCHAR), '')"
                if name in identity_names else "NULL::VARCHAR")

    def digest(values):
        return f"sha256(to_json(list_value({', '.join(values)})))"

    complete = " AND ".join(f"{value(name)} IS NOT NULL" for name in identity)
    peer = '"_amazon_identity_peer"'
    peer_complete = " AND ".join(f"{value(name, peer)} IS NOT NULL" for name in identity)
    partition = ", ".join(value(name, peer) for name in fallback)
    # Unprepared mirrors rank once in a materialized relation, outside the row lookup.
    occurrence = (
        '(WITH "_amazon_missing_identity" AS MATERIALIZED '
        f'(SELECT {peer}.rowid AS "_identity_rowid", '
        f'row_number() OVER (PARTITION BY {partition} ORDER BY {peer}.rowid) - 1 '
        f'AS "_identity_occurrence" FROM {source} AS {peer} WHERE NOT ({peer_complete})) '
        'SELECT CAST("_identity_occurrence" AS VARCHAR) FROM "_amazon_missing_identity" '
        f'WHERE "_identity_rowid" = {source}.rowid)')
    if amazon_schema.IDENTITY_OCCURRENCE in prepared:
        occurrence = f'CAST({source}.{_quoted(amazon_schema.IDENTITY_OCCURRENCE)} AS VARCHAR)'
    fallback_digest = digest(["'__amazon_missing_identity__'",
                              *(value(name) for name in fallback), occurrence])
    digest_sql = (f"CASE WHEN {complete} THEN {digest([value(name) for name in identity])} "
                  f"ELSE {fallback_digest} END")
    if table in amazon_store.DERIVED_TABLES:
        # Derived views construct complete keys and have no physical rowid for fallback order.
        digest_sql = digest([value(name) for name in identity])
    # The shared grid, relation and selection contracts all use numeric pids. Thirteen
    # hex digits retain a stable 52-bit identity that JavaScript represents exactly.
    ident = f"greatest(1, CAST(('0x' || substr(({digest_sql}), 1, 13)) AS BIGINT))"
    if amazon_schema.IDENTITY_PID in prepared:
        ident = f'{source}.{_quoted(amazon_schema.IDENTITY_PID)}'
    elif amazon_schema.CONNECTION_ID in prepared:
        legacy_owner = amazon_store.legacy_identity_owner(cur, table)
        if legacy_owner:
            literal = "'" + str(legacy_owner).replace("'", "''") + "'"
            old_identity = _identity_columns(table, identity_names - {amazon_schema.CONNECTION_ID})
            old_fallback = sorted(set((*old_identity, *_fallback_columns(table))))
            old_complete = " AND ".join(f"{value(name)} IS NOT NULL" for name in old_identity)
            old_peer_complete = " AND ".join(f"{value(name, peer)} IS NOT NULL" for name in old_identity)
            old_partition = ", ".join(value(name, peer) for name in old_fallback)
            old_occurrence = (
                '(WITH "_amazon_legacy_identity" AS MATERIALIZED '
                f'(SELECT {peer}.rowid AS "_identity_rowid", '
                f'row_number() OVER (PARTITION BY {old_partition} ORDER BY {peer}.rowid) - 1 '
                f'AS "_identity_occurrence" FROM {source} AS {peer} '
                f'WHERE {peer}."_connection_id"={literal} AND NOT ({old_peer_complete})) '
                'SELECT CAST("_identity_occurrence" AS VARCHAR) FROM "_amazon_legacy_identity" '
                f'WHERE "_identity_rowid" = {source}.rowid)')
            old_missing = digest(["'__amazon_missing_identity__'", *(value(name) for name in old_fallback),
                                  old_occurrence])
            old_digest = (f"CASE WHEN {old_complete} THEN {digest([value(name) for name in old_identity])} "
                          f"ELSE {old_missing} END")
            old_ident = f"greatest(1, CAST(('0x' || substr(({old_digest}), 1, 13)) AS BIGINT))"
            ident = f'CASE WHEN {source}."_connection_id"={literal} THEN {old_ident} ELSE {ident} END'
    cols = {}
    personal = set(amazon_store.personal_columns(table))
    for name, dtype in columns:
        if name in personal:
            cols[name] = (_quoted(name), _text)
            continue
        kind = _ftype(name, dtype)
        expr = _quoted(name)
        fractional = kind in ("currency", "pct") or name == "per_unit_volume"
        if kind in ("currency", "pct", "int"):
            expr = f"TRY_CAST({expr} AS {'DOUBLE' if fractional else 'BIGINT'})"
        if kind == "pct" and _is_fraction_percent(name):
            expr = f"ROUND({expr} * 100, 8)"     # 8 places drops float noise, keeps every digit
        cols[name] = (expr, _number if fractional else {"int": _integer}.get(kind, _text))
    # S67-T30: the product lookups bind like any mirror column, so a filter (`=` a parent), a
    # group (count by parent) and a sort compile against them in SQL with no per-row Python.
    for lookup in lookups(cur, key, names, allowed=allow_lookups):
        cols[lookup["key"]] = (lookup["expr"], _text)
    spec = {"table": SOURCE[key], "where": "", "id": ident,
            "needs_excluded": False, "cols": cols}
    if table not in amazon_store.DERIVED_TABLES:
        spec["page_key"] = f"{source}.rowid"
    # D-2643: an empty table carries its code and reason to every door that reads this binding.
    status = source_status(cur, key) if status is _UNRESOLVED else status
    if status is not None:
        spec["sourceStatus"] = status
    return spec


def page_source(cur, key, definition=None, *, allow_lookups=True):
    """Resolve one target's storage policy on the admitted cursor without provider discovery."""
    columns = mirror_columns(cur, SOURCE[key], strict=True)
    mandatory = key in READ_THROUGH or bool((definition or {}).get("readThrough"))
    if not columns:
        return missing_source(key, cur=cur) if mandatory else None
    status = _UNRESOLVED
    if not mandatory:
        count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
        # D-2643: an empty table, or one beside a recorded refusal, reads through, so its
        # binding names its reason in the Data grid.
        if count and not _read_through(cur, key, count, columns=columns):
            status = source_status(cur, key)
            if status is None:
                return None
    return grid_source(cur, key, columns=columns, allow_lookups=allow_lookups, status=status)


def grid_sources(cur=None):
    """Only Amazon read-through grids; no keys at all on an Odoo-only mirror."""
    own_cursor = cur is None
    cur = datastore.ro_cursor() if own_cursor else cur
    try:
        present = {key for key, table in SOURCE.items() if mirror_columns(cur, table)}
        if not present:
            return {}
        keys = set(READ_THROUGH)
        for key in present - keys:
            count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
            # D-2643: an empty or refused table registers too, so the grid opens it on the
            # read-through door that carries its reason.
            if _read_through(cur, key, count) or source_status(cur, key) is not None:
                keys.add(key)
        return {key: grid_source(cur, key) for key in SOURCE if key in keys}
    finally:
        if own_cursor:
            cur.close()


def _row_id(row, key, occurrence=0):
    """Return a legacy row key from declared identity, lineage and equal-key occurrence."""
    table = key.removeprefix(PREFIX)
    columns = _identity_columns(table, row)
    identity = tuple(row.get(name) for name in columns)
    if all(value not in (None, "") for value in identity):
        if table in _LEGACY_IDENTITY_TABLES and len(identity) == 1:
            return str(identity[0])
        payload = json.dumps(identity, ensure_ascii=True, default=str)
    else:
        fallback = sorted(set((*columns, *_fallback_columns(table))))
        values = tuple(None if row.get(name) in (None, "") else str(row[name])
                       for name in fallback)
        payload = json.dumps(("__amazon_missing_identity__", *values, str(occurrence)),
                             ensure_ascii=True, default=str)
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()[:32]


def _read(cur, key):
    """Small snapshots use the same numeric identity as their read-through grid.

    A SKU or hex digest used as the stored row key is silently skipped by
    perm_scope._read_user_table, and cannot be opened by the record drawer.
    """
    cols = [c[0] for c in mirror_columns(cur, SOURCE[key])]
    if not cols:
        return []
    spec = grid_source(cur, key)
    # Stored snapshots and mirror windows share their numeric casts. Empty report cells
    # become null; decimals remain numbers instead of text that defeats aggregation.
    quoted = ", ".join(spec["cols"][c][0] for c in cols)
    personal = set(amazon_store.personal_columns(key))
    numeric = {name for name, dtype in mirror_columns(cur, SOURCE[key])
               if name not in personal and _ftype(name, dtype) in ("currency", "pct", "int")}
    # S67-T30: a stored snapshot carries the same product lookups, computed by the SAME SQL
    # expressions the read-through binding uses, in this one SELECT (never per row).
    derived = [(lookup["key"], lookup["expr"]) for lookup in lookups(cur, key)]
    extra = "".join(f", {expr}" for _k, expr in derived)
    owner_columns = ([amazon_schema.CONNECTION_ID]
                     if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[key]) else [])
    extra += "".join(f", {_quoted(name)}" for name in owner_columns)
    rows = cur.execute(f'SELECT {spec["id"]}, {quoted}{extra} FROM {SOURCE[key]} '
                       f'ORDER BY {_quoted(SOURCE[key])}.rowid').fetchall()
    names = cols + [k for k, _expr in derived] + owner_columns
    out = []
    for r in rows:
        row = {}
        for name, val in zip(names, r[1:]):
            if name in numeric:
                row[name] = spec["cols"][name][1](val)
                continue
            # Postgres JSON cannot hold U+0000, and one such byte refused the whole Amazon write.
            row[name] = "" if val is None else (val if isinstance(val, (int, float))
                                                else str(val).replace("\x00", ""))
        row["_id"] = str(r[0])
        out.append(row)
    def read_targets(target, matches):
        target_spec = grid_source(cur, target)
        columns = sorted(matches)
        clauses, params = [], []
        for column in columns:
            values = sorted(matches[column])
            if values:
                clauses.append(f'CAST({_quoted(column)} AS VARCHAR) IN '
                               f'({",".join("?" for _ in values)})')
                params.extend(values)
        if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[target]):
            columns.append(amazon_schema.CONNECTION_ID)
        projection = ", ".join(_quoted(column) for column in columns)
        found = cur.execute(
            f'SELECT {target_spec["id"]}, {projection} FROM {SOURCE[target]} '
            f'WHERE {" OR ".join(clauses) or "FALSE"}', params).fetchall()
        return [dict(zip(["pid", *columns], row)) for row in found]

    fill_link_cells(out, _fields_for(cur, key), read_targets)
    return out


def link_edges(fields):
    """`[(field, target, from, on)]` for every derived Amazon link `fill_link_cells` fills.

    Shared by the page filler and scoped reader; manual/inverse/through edges stay separate."""
    out = []
    for field in fields:
        bag = field.get("link") or {}
        target, frm, on = bag.get("table"), bag.get("from"), bag.get("on")
        if (field.get("type") != "link" or target not in SOURCE or not frm or not on
                or bag.get("inverse") or bag.get("through") or bag.get("selectionMode") == "manual"):
            continue
        out.append((field, target, frm, on))
    return out


def wall_link_expression(source, predicate, identity):
    """The pre-window SQL form of fill_link_cells' sorted, distinct numeric pids.

    The caller supplies a tenant-bound target source and its permission predicate.
    An unreadable target is denied by the caller's answerability guard, never supplied
    as an empty set that an `isEmpty` wall could mistake for evidence.
    """
    return (f"(SELECT string_agg(CAST(_wall_link_pid AS VARCHAR), ',' ORDER BY _wall_link_pid) "
            f"FROM (SELECT DISTINCT {identity} AS _wall_link_pid FROM {source} "
            f"WHERE {predicate}) AS _wall_links)")


def fill_link_cells(rows, fields, read_targets, *, max_bytes=None, capacity_refusal=None):
    """Populate derived Amazon links with one read per distinct target on this page.

    The snapshot planner reads its tenant mirror; the HTTP reader supplies a scoped
    target reader taking `(target, {column: set of keys})`. OR the column predicates
    inside the target's ordinary row wall, then fan out by each edge's own column.
    Neither path stores history rows or caps the matching target set.
    """
    edges = link_edges(fields)
    scoped = {target for field, target, _frm, _on in edges
              if (field.get("link") or {}).get("connectionScope") == amazon_schema.CONNECTION_ID}
    def match_key(target, row, value):
        if target not in scoped:
            return value
        cid = row.get(amazon_schema.CONNECTION_ID)
        return (str(cid), value) if cid not in (None, "") else None
    requests, weights = {}, {}
    for _field, target, frm, on in edges:
        values = {str(row[frm]) for row in rows if row.get(frm) not in (None, "")}
        requests.setdefault(target, {}).setdefault(on, set()).update(values)
        for row in rows:
            if row.get(frm) not in (None, ""):
                slot = (target, on, match_key(target, row, str(row[frm])))
                weights[slot] = weights.get(slot, 0) + 1
    indexes = {}
    remaining = max_bytes
    for target, matches in requests.items():
        indexes[target] = {column: {} for column in matches}
        iterator = iter(read_targets(target, matches) if any(matches.values()) else ())
        try:
            for row in iterator:
                pid = int(row["pid"])
                for column, wanted in matches.items():
                    value = str(row[column]) if row.get(column) not in (None, "") else None
                    if value not in wanted:
                        continue
                    matched = match_key(target, row, value)
                    if matched is None or not weights.get((target, column, matched)):
                        continue
                    bucket = indexes[target][column].get(matched)
                    if bucket is not None and pid in bucket:
                        continue
                    if remaining is not None:
                        # Charge every output occurrence before allocating its distinct ID.
                        cost = (len(str(pid)) + (1 if bucket else 2)) * weights.get((target, column, matched), 0)
                        if cost > remaining:
                            raise capacity_refusal()
                        remaining -= cost
                    if bucket is None:
                        bucket = indexes[target][column][matched] = set()
                    bucket.add(pid)
        finally:
            close = getattr(iterator, "close", None)
            if close is not None:
                close()
    for field, target, frm, on in edges:
        for row in rows:
            row[field["key"]] = ",".join(str(pid) for pid in sorted(
                indexes[target][on].get(match_key(target, row, str(row.get(frm))), ())))


def available(tenant_key="royal-imports"):
    """Has this tenant's mirror ever been fed by `amazon_store`? Cheap; never fetches."""
    try:
        counts = amazon_store.status(tenant_key)
    except Exception:                                                  # noqa: BLE001
        return False
    return any(bool((v or {}).get("rows")) for v in counts.values())


def plan(cur, rt=None):
    """Rows for small snapshots, schema/count stubs for history or oversized tables."""
    built = {"problems": []}
    for key in SOURCE:
        # ⛔ COUNT BEFORE READ. `_read` materialises the whole table as python dicts, so checking
        # the cap afterwards means the one table too big to keep is the one built in memory first.
        try:
            n = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
        except Exception:                                              # noqa: BLE001
            continue                                                   # absent = never synced
        if key == PREFIX + "inventory" and not n:
            # A measured empty inventory report remains a declared database with a source reason.
            status = amazon_store.inventory_table_status(cur)
            built[key] = {"readThrough": True,
                          "fields": _fields_for(cur, key, read_through=True), "count": n,
                          "sourceStatus": {name: status[name] for name in ("empty", "code", "reason")}}
            continue
        if _read_through(cur, key, n):
            built[key] = {"readThrough": True,
                          "fields": _fields_for(cur, key, read_through=True), "count": n}
            continue
        if not n:
            # S78-T02: an emptied mirror table is an empty population, never a skip, or the stored
            # definition keeps rows the source no longer holds. `apply_plan` creates no table for it.
            built[key] = []
            continue
        rows = _read(cur, key)
        if rows:
            built[key] = rows
    return built


def plan_fingerprint(cur, built):
    """Digest the exact derived Amazon content that would be published.

    `amazon_store.sync()` counts fetched rows, not changed rows, so it is not equality evidence.
    Row order out of DuckDB is arbitrary and therefore non-semantic, so rows are canonicalised
    before hashing. Callers fail safe to a refresh if this raises.
    """
    tables = []
    for key in SOURCE:
        if isinstance(built.get(key), dict) and built[key].get("readThrough"):
            tables.append({"key": key, **built[key]})
            continue
        rows = built.get(key) or []
        canonical = sorted(rows, key=lambda row: json.dumps(
            row, sort_keys=True, ensure_ascii=True, separators=(",", ":"), default=str))
        # S78-T02: an empty population differs from an absent table, so a stale copy is rewritten.
        # D-2643: a changed empty reason is a changed publication too.
        empty = ({"empty": True, "sourceStatus": source_status(cur, key)
                  if mirror_columns(cur, SOURCE[key]) else None} if built.get(key) == [] else {})
        tables.append({"key": key, "fields": _fields_for(cur, key), "rows": canonical, **empty})
    payload = json.dumps(tables, sort_keys=True, ensure_ascii=True,
                         separators=(",", ":"), default=str)
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


def _place_lookups(stored, fields):
    """An EXISTING table's fields with each NEW product lookup placed right after its source.

    `_ensure_table_inplace` only appends, so a lookup first planned for a table stored before
    S67-T30 would land at the far end of the grid instead of beside the ASIN or SKU it derives
    from. A lookup already stored keeps its place (a person may have moved it) and only has its
    `opens` bag brought up to date, which `_ensure_table_inplace` does not forward-migrate.
    """
    out = list(stored)
    have = {f.get("key"): i for i, f in enumerate(out)}
    anchor = None
    for field in fields:
        fkey = field.get("key")
        if fkey not in LOOKUP_KEYS:
            anchor = fkey
            continue
        if fkey in have:
            old = out[have[fkey]]
            if (old.get("automation") or {}).get("flowId") == "amazon_relational" \
                    and not _odoo._ut().user_edited(old):
                if field.get("opens"):
                    old["opens"] = dict(field["opens"])
                else:
                    old.pop("opens", None)
            continue
        keys = [f.get("key") for f in out]
        if anchor not in keys:
            continue                     # no source column stored yet: the append places it
        at = keys.index(anchor) + 1
        while at < len(keys) and keys[at] in LOOKUP_KEYS:
            at += 1                      # after any lookup already beside the same source
        out.insert(at, dict(field))
        have = {f.get("key"): i for i, f in enumerate(out)}
        anchor = fkey
    return out


def _lead_with_identity(stored, report, pinned):
    """An EXISTING table's fields with its identity column moved first, while the order is still
    the report's own.

    `_ensure_table_inplace` keeps a stored table's field order and only appends, so the
    identity-first order `_fields_for` gives a NEW table never reaches one stored before it. The
    move happens only when the stored mirror columns sit exactly in the report's order: any other
    order is a person's arrangement, and theirs wins.
    """
    keys = [f.get("key") for f in stored]
    if not pinned or pinned not in keys or keys[0] == pinned:
        return stored
    in_report = set(report)
    if [k for k in keys if k in in_report] != [k for k in report if k in set(keys)]:
        return stored
    return ([f for f in stored if f.get("key") == pinned]
            + [f for f in stored if f.get("key") != pinned])


def _listing_identity_rows(cur):
    """Capture connection/SKU targets and recoverable numeric IDs from earlier listing declarations."""
    key = PREFIX + "listing_items"
    names = {name for name, _dtype in mirror_columns(cur, SOURCE[key])}
    owner = (_quoted(amazon_schema.CONNECTION_ID)
             if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[key])
             else "NULL::VARCHAR")
    old = ["_snapshot_date", "sku", "_report_row_number"]
    if "_report_document_id" in names:
        old.append("_report_document_id")
    values = [f"NULLIF(CAST({_quoted(name)} AS VARCHAR), '')" if name in names
              else "NULL::VARCHAR" for name in old]
    complete = " AND ".join(f"{value} IS NOT NULL" for value in values)
    digest = f"sha256(to_json(list_value({', '.join(values)})))"
    legacy = f"CASE WHEN {complete} THEN greatest(1, CAST(('0x' || substr({digest}, 1, 13)) AS BIGINT)) END"
    sku = _quoted("sku") if "sku" in names else "NULL::VARCHAR"
    unscoped = (f"greatest(1, CAST(('0x' || substr(sha256(to_json(list_value(CAST({sku} AS VARCHAR)))), "
                "1, 13)) AS BIGINT))")
    spec = grid_source(cur, key)
    return cur.execute(f'SELECT {owner}, {sku}, {spec["id"]}, {legacy}, {unscoped} '
                       f'FROM {SOURCE[key]}').fetchall()


def _migrate_listing_identity(rt, current, identities, source_fields, read_through, legacy_owner=None):
    """Copy authored listing cells only when connection/SKU or retained lineage proves their target."""
    from core import shared_overlay

    key = PREFIX + "listing_items"
    targets, aliases, target_ids, by_sku, candidates = {}, {}, set(), {}, {}
    version = "connection_sku" if any(row[0] not in (None, "") for row in identities) else "sku"
    previous_version = current.get("amazonListingIdentity")
    migrating = previous_version != version
    legacy_owner = str(legacy_owner) if legacy_owner not in (None, "") else None

    def refuse():
        raise Refused("Amazon listing SKU migration is ambiguous. Existing cells were retained.")

    def alias(old, target):
        old, target = str(old), str(target)
        if old in aliases and aliases[old] != target:
            refuse()
        aliases[old] = target

    for owner, sku, pid, previous, unscoped in identities:
        if sku in (None, ""):
            continue
        owner = str(owner) if owner not in (None, "") else None
        identity = (owner, str(sku))
        if identity in targets or str(pid) in target_ids:
            refuse()
        targets[identity] = str(pid)
        by_sku.setdefault(str(sku), []).append(identity)
        target_ids.add(str(pid))
        alias(pid, pid)
        old_ids = []
        if migrating and previous_version not in ("sku", "connection_sku") and previous is not None:
            old_ids.append(previous)
        if migrating and owner is not None:
            old_ids.append(unscoped)
        for old in old_ids:
            candidates.setdefault(str(old), set()).add(identity)

    authored = {field["key"] for field in current.get("fields", ())
                if (field.get("automation") or {}).get("flowId") != "amazon_relational"
                or _odoo._ut().user_edited(field)}
    stored = current.get("rows") or {}
    source_names = {*source_fields, "refreshed", amazon_schema.CONNECTION_ID}
    moved, notes, seen = {}, {}, set()
    for old, row in stored.items():
        sku = str(row.get("sku") or "")
        values = {name: value for name, value in row.items()
                  if name != amazon_schema.CONNECTION_ID and (name in authored or name not in source_names)}
        owner = row.get(amazon_schema.CONNECTION_ID)
        if owner not in (None, ""):
            target = targets.get((str(owner), sku))
        elif str(old) in target_ids:
            target = str(old) if any(targets[identity] == str(old) for identity in by_sku.get(sku, ())) else None
        elif legacy_owner is not None and version == "connection_sku" and previous_version != "connection_sku":
            target = targets.get((legacy_owner, sku))
        else:
            matches = by_sku.get(sku, ())
            target = targets[matches[0]] if len(matches) == 1 else None
        if target is None:
            if values:
                refuse()
            continue
        if target in seen:
            refuse()
        seen.add(target)
        alias(old, target)
        moved[target] = dict(row)
        if read_through and values:
            notes[target] = values

    # Stored ownership is stronger evidence than an old numeric alias shared by two accounts.
    for old, matches in candidates.items():
        if old in aliases:
            continue
        owned = {identity for identity in matches if identity[0] == legacy_owner} if legacy_owner else set()
        resolved = owned if legacy_owner is not None and version == "connection_sku" else matches
        if len(resolved) == 1:
            alias(old, targets[next(iter(resolved))])

    # Read strictly before declaring migration complete. A failed read is never an empty overlay.
    captured = dict((rt.get(shared_overlay.bucket(key)) or {}).get("cells") or {})
    if migrating and any(
            values and str(pid) not in aliases for pid, values in captured.items()):
        refuse()
    for old, target in aliases.items():
        for name, value in (captured.get(old) or {}).items():
            dest = notes.setdefault(target, {})
            if name in dest and dest[name] != value:
                refuse()
            dest[name] = value
    for target, values in notes.items():
        held = captured.get(target) or {}
        if any(name in held and held[name] != value for name, value in values.items()):
            refuse()

    class Writer:
        def update(self, bucket, updater, flush=None):
            def guarded(data):
                if ((data or {}).get("cells") or {}) != captured:
                    refuse()
                return updater(data)

            return rt.update(bucket, guarded, flush="sync")

    # Copy before publishing new IDs. Retain old overlay cells for recovery and retries.
    delta = {target: {name: value for name, value in values.items()
                      if name not in (captured.get(target) or {})}
             for target, values in notes.items()}
    if any(delta.values()):
        shared_overlay.put_rows(key, delta, st=Writer())
    current["rows"] = moved
    current["amazonListingIdentity"] = version


def apply_plan(rt, built, username="amazon", today=None):
    """Create-or-merge every table in `built`, in ONE `flush="sync"` write.

    ⚠ ONE write for all seven, not seven. `Store.update` is a full download plus a full upload of
    the tenant document, so seven would be seven round trips and seven commits per resync. It is
    also ATOMIC: a `Refused` at MAX_TABLES aborts before anything persists, rather than leaving the
    half-spawn `plan()` exists to prevent.
    """
    if built.get("problems"):
        raise Refused("; ".join(built["problems"]))
    stamp = today or _odoo._iso_today()
    written = {}
    cur_mirror = datastore.ro_cursor()
    try:
        plans = [(key, LABELS[key], (built[key]["fields"] if isinstance(built[key], dict)
                                   else _fields_for(cur_mirror, key)), built[key])
                 for key in SOURCE if key in built]
        legacy_ids = {}
        candidate_counts = {}
        report_order = {key: [name for name, _dtype in mirror_columns(cur_mirror, SOURCE[key])]
                        for key, _label, _fields, _rows in plans}
        listing_ids = (_listing_identity_rows(cur_mirror)
                       if PREFIX + "listing_items" in built else ())
        listing_owner = None
        if PREFIX + "listing_items" in built:
            from core import keychain
            binding = amazon_store._read_state(cur_mirror, "amzn_account_binding")
            listing_owner = ((binding or {}).get("detail")
                             or keychain.default_connection_id(rt, "amazon_sp"))
        # S79-T13: each Ads table's description states its history window and any recorded limit.
        ads_descriptions = {key: amazon_ads_store.table_description(SOURCE[key], cur_mirror)
                            for key, *_rest in plans if key in _ADS_SPEC}
        # D-2643: an emptied stored table keeps the code and reason its mirror answers with.
        empty_status = {key: source_status(cur_mirror, key) for key, _label, _fields, rows in plans
                        if isinstance(rows, list) and not rows}
        for key, _label, _fields, rows in plans:
            if isinstance(rows, dict):
                continue
            names = report_order[key]
            occurrences = {}
            for row in rows:
                raw = {name: row.get(name) for name in names}
                # Content hashes are legacy migration candidates, never new row identities.
                payload = json.dumps(raw, sort_keys=True, ensure_ascii=True,
                                     separators=(",", ":"), default=str)
                base_id = _row_id(raw, key)
                occurrence = occurrences.get(base_id, 0)
                occurrences[base_id] = occurrence + 1
                candidates = (
                    _row_id(raw, key, occurrence),
                    hashlib.sha256(payload.encode("utf-8")).hexdigest()[:32])
                declared = (("_row_key",) if key in _ADS_SPEC
                            else amazon_schema.TABLE_SPECS[key.removeprefix(PREFIX)]["row_identity"])
                if all(raw.get(name) not in (None, "") for name in declared):
                    old_composite = json.dumps(tuple(raw[name] for name in declared),
                                               ensure_ascii=True, default=str)
                    candidates = (*candidates,
                                  hashlib.sha256(old_composite.encode("utf-8")).hexdigest()[:32])
                if key.removeprefix(PREFIX) in _LEGACY_IDENTITY_TABLES:
                    first = next((str(raw[name]) for name in IDENTITY[key]
                                  if raw.get(name) not in (None, "")), None)
                    if first is not None:
                        candidates = (*candidates, first)
                legacy_ids[(key, row["_id"])] = tuple(dict.fromkeys(candidates))
                for candidate in legacy_ids[(key, row["_id"])]:
                    slot = (key, candidate)
                    candidate_counts[slot] = candidate_counts.get(slot, 0) + 1
    finally:
        cur_mirror.close()

    not_stored = {}

    def _apply_all(doc):
        doc = doc if isinstance(doc, dict) else {}
        not_stored.clear()
        live_columns = {key: {field["key"] for field in table.get("fields", ())}
                        for key, table in doc.items() if key in SOURCE and isinstance(table, dict)}
        # Empty materialized snapshots reconcile existing definitions and create none.
        unborn = {key for key, _label, _fields, rows in plans
                  if isinstance(rows, list) and not rows and not isinstance(doc.get(key), dict)}
        live_columns.update({key: {field["key"] for field in fields}
                             for key, _label, fields, _rows in plans if key not in unborn})
        generated = connector_spec.system_link_fields("amazon", live_columns)
        for key, label, fields, rows in plans:
            if key in unborn:
                continue
            stub = isinstance(rows, dict) and rows.get("readThrough") is True

            def unbacked_refreshed(field):
                return (stub and "refreshed" not in report_order[key]
                        and field.get("key") == "refreshed"
                        and (field.get("automation") or {}).get("flowId") == "amazon_relational"
                        and (field.get("automation") or {}).get("preset")
                        and not _odoo._ut().user_edited(field))

            # Retire only the machine row stamp that read-through SQL cannot read.
            fields = [field for field in fields if not unbacked_refreshed(field)]
            if key == PREFIX + "listing_items":
                current = doc.get(key) or {}
                _migrate_listing_identity(rt, current, listing_ids, report_order[key],
                                          isinstance(rows, dict) and rows.get("readThrough") is True,
                                          legacy_owner=listing_owner)
                listing_version = ("connection_sku" if any(row[0] not in (None, "") for row in listing_ids)
                                   else "sku")
            fields = [field for field in fields
                      if not (field.get("type") == "link"
                              and (field.get("automation") or {}).get("flowId") == "amazon_relational")]
            at = next((i for i, field in enumerate(fields) if field.get("key") == "refreshed"),
                      len(fields))
            fields[at:at] = [_odoo._preset(field, flow="amazon_relational")
                             for field in generated.get(key, ())]
            # ⭐⭐ D-3284 (owner ruling 2026-09-27): S66-T42/T43 created a NEW Amazon table only while
            # the workspace had room under MAX_TABLES and reported the rest `not_stored`; ten Amazon
            # databases stayed missing that way. Connector databases now take no MAX_TABLES slot
            # (`user_tables.counts_toward_table_cap`), so every planned Amazon table is created.
            # `not_stored` stays as bookkeeping: empty, it clears any earlier record.
            # Corrected/removed measured joins retire their machine-owned old fields.
            # Preserve fields a person created or explicitly took over.
            current = doc.get(key) or {}
            wanted = {f["key"] for f in fields}
            # S67-T30: a product lookup the mirror no longer supports retires the same way.
            current["fields"] = _lead_with_identity(
                [f for f in current.get("fields", [])
                 if not (unbacked_refreshed(f)
                         or ((f.get("type") == "link" or f.get("key") in LOOKUP_KEYS)
                             and (f.get("automation") or {}).get("flowId") == "amazon_relational"
                             and not _odoo._ut().user_edited(f)
                             and f.get("key") not in wanted))],
                report_order.get(key) or [], PINNED.get(key))
            if current["fields"]:
                current["fields"] = _place_lookups(current["fields"], fields)
            if not stub:
                stored = current.get("rows") or {}
                for row in rows:
                    if row["_id"] in stored:
                        continue
                    for old_id in legacy_ids.get((key, row["_id"]), ()):
                        if old_id in stored and candidate_counts[(key, old_id)] == 1:
                            # Carry authored cells along when replacing an unusable string id.
                            stored[row["_id"]] = stored.pop(old_id)
                            break
            removed = len((doc.get(key) or {}).get("rows") or {}) if stub else 0
            written[key] = _odoo._ensure_table_inplace(doc, key, label, fields, None if stub else rows,
                                                       username, stamp)
            # S78-T01: `_ensure_table_inplace` does not forward `format`, so a stored preset field
            # would keep showing "$" on a weight or 0 decimals on a percent.
            formats = {f["key"]: f["format"] for f in fields if f.get("format")}
            for stored_field in doc[key].get("fields", []):
                if (stored_field.get("key") in formats
                        and (stored_field.get("automation") or {}).get("flowId") == "amazon_relational"
                        and (stored_field.get("automation") or {}).get("preset")
                        and not _odoo._ut().user_edited(stored_field)):
                    stored_field["format"] = dict(formats[stored_field["key"]])
            if stub:
                doc[key]["readThrough"] = True
                doc[key]["rows"] = {}
                doc[key].pop(_odoo._ut().OVERSIZE_FIELD, None)
                written[key].update(rows=rows["count"], removed=removed, readThrough=True)
            else:
                doc[key].pop("readThrough", None)
                # The shared merger compares string spellings, so an old "2" and a new
                # integer 2 otherwise compare equal and leave the old cell untyped.
                numeric = {f["key"] for f in doc[key].get("fields", [])
                           if f.get("type") in ("currency", "pct", "int")
                           and (f.get("automation") or {}).get("flowId") == "amazon_relational"
                           and not _odoo._ut().user_edited(f)}
                stored = doc[key].get("rows") or {}
                for row in rows:
                    saved = stored.get(row["_id"])
                    if numeric and isinstance(saved, dict):
                        saved.update({name: row[name] for name in numeric if name in row})
            status = rows.get("sourceStatus") if isinstance(rows, dict) else empty_status.get(key)
            if status is not None:
                doc[key]["sourceStatus"] = dict(status)
            else:
                doc[key].pop("sourceStatus", None)
            # DPP 1.8 attribution is explicit in the stored definition, not inferred from a
            # mutable display label or from the process having imported this module.
            doc[key]["connector"] = source_attribution(key)
            if ads_descriptions.get(key):
                doc[key]["description"] = ads_descriptions[key]
            if key in (PREFIX + "review_topics", PREFIX + "review_trends"):
                doc[key]["description"] = amazon_schema.REVIEW_UNAVAILABLE_NOTE
            if key == PREFIX + "search_query_performance":
                doc[key]["description"] = amazon_schema.SQP_LIMIT_NOTE
            if key == PREFIX + "listing_items":
                doc[key]["amazonListingIdentity"] = listing_version
        return doc

    rt.update(_odoo._ut().STORE_KEY, _apply_all, flush="sync")
    from core import shares
    shares.repair_published_database_grants(rt, table_keys=tuple(written))
    _record_not_stored({key for key, *_rest in plans}, dict(not_stored))
    return written


def _record_not_stored(planned, not_stored):
    """Durable per-table marker in the Amazon mirror, read by `connectors_amazon.sync_state`.

    A table refused at the store's table cap gets `not_stored` with its reason; a table that was
    stored clears any earlier marker. Best effort: the store write above already happened, and a
    marker failure must not turn it into an error.
    """
    try:
        con = datastore.connect()
        for key in planned:
            table = key[len(PREFIX):]
            entity = amazon_store.entity_key("not_stored_" + table)
            if key in not_stored:
                amazon_store._record_state(con, entity, "not_stored", 0, not_stored[key])
            else:
                con.execute("DELETE FROM _sync_state WHERE entity = ?", [entity])
    except Exception:                                                  # noqa: BLE001
        pass


def refresh_state(rt, why="boot", tenant_key=None, previous_fingerprint=None, log=print):
    """Open, plan and conditionally apply the Amazon mirror.

    Returns `{}` and says why when the mirror holds no Amazon tables — a tenant that never
    connected Amazon is a normal state, not a failure.
    """
    state = {"present": False, "known": False, "fingerprint": None,
             "changed": False, "applied": False, "written": {}}
    tenant = tenant_key or getattr(getattr(rt, "tenant", None), "key", None) or "royal-imports"
    path = datastore.path_for(tenant)
    if Path(datastore.DB_PATH) != Path(path):
        datastore.use_path(path)
    cur = datastore.ro_cursor()
    try:
        return _refresh_from_cursor(cur, rt, why, tenant, previous_fingerprint, log, state)
    finally:
        cur.close()


def _refresh_from_cursor(cur, rt, why, tenant, previous_fingerprint, log, state):
    if not any(mirror_columns(cur, t) for t in SOURCE.values()):
        log(f"[amazon_relational] {why}: no Amazon tables in {tenant}'s mirror, nothing to spawn "
            f"(run `python platform/harness/amazon_store.py --sync --tenant {tenant}` first)")
        return state
    state["present"] = True
    # S70-T12 / D-3495: publish immediately from the restored cursor, before planning or
    # the unchanged-fingerprint return. Registry readiness must not wait for a store write.
    import routes_odoo_tables
    routes_odoo_tables._learn_amazon_sources(cur)
    built = plan(cur, rt)
    problems = built.get("problems") or []
    for p in problems:
        log(f"[amazon_relational] PROBLEM {p}")
    applicable = {k: v for k, v in built.items() if k != "problems"}
    if not problems:
        try:
            state["fingerprint"] = plan_fingerprint(cur, applicable)
            state["known"] = True
        except Exception as e:                                         # noqa: BLE001
            log(f"[amazon_relational] {why}: plan fingerprint unavailable; forcing refresh "
                f"({type(e).__name__}: {e})")
    if state["known"] and state["fingerprint"] == previous_fingerprint:
        log(f"[amazon_relational] {why}: Amazon plan unchanged; skipped full user_tables write")
        return state
    state["changed"] = True
    state["written"] = apply_plan(rt, applicable)
    state["applied"] = True
    import routes_odoo_tables
    routes_odoo_tables.invalidate_sources()
    for key, counts in state["written"].items():
        log(f"[amazon_relational] {why}: {key:<28} {counts}")
    return state


def refresh(rt, why="boot", tenant_key=None, log=print):
    """Backward-compatible facade. Use `refresh_state` for egress-aware state."""
    return refresh_state(rt, why, tenant_key, log=log)["written"]


def main(argv=None):
    """`python aios-web/api/amazon_relational.py --refresh [--tenant X]` — the manual door."""
    import argparse
    ap = argparse.ArgumentParser()
    ap.add_argument("--refresh", action="store_true")
    ap.add_argument("--plan", action="store_true")
    ap.add_argument("--tenant", default="royal-imports")
    a = ap.parse_args(argv)
    os.environ.setdefault("AIOS_PREWARM", "0")
    from harness import runtime as _rt
    rt = _rt.get_runtime(a.tenant)
    if a.plan:
        path = datastore.path_for(a.tenant)
        if Path(datastore.DB_PATH) != Path(path):
            datastore.use_path(path)
        built = plan(datastore.ro_con(), rt)
        for k, v in built.items():
            print(f"  {k:<28} {len(v) if isinstance(v, list) else v}")
        return 0
    if a.refresh:
        refresh(rt, why="cli", tenant_key=a.tenant)
        return 0
    ap.print_help()
    return 2


if __name__ == "__main__":
    sys.exit(main())