File size: 60,579 Bytes
f5696cb
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
 
b2aa446
1f123e1
 
 
f1ae536
bf8519f
 
 
 
b2aa446
bf8519f
 
 
 
 
f5696cb
bf8519f
 
 
1f123e1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c72f0a1
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
f1ae536
d598032
 
 
 
 
 
 
 
 
 
 
 
bf8519f
 
d598032
 
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
 
 
d598032
 
 
 
 
 
 
 
 
f1ae536
 
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
 
 
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1ad1eea
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
 
 
 
 
f5696cb
bf8519f
 
 
 
 
 
f5696cb
bf8519f
 
 
 
 
 
 
e1b3e71
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c72f0a1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e1b3e71
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cfa8541
 
 
 
 
 
e1b3e71
cfa8541
 
e1b3e71
 
 
 
cfa8541
 
e1b3e71
4b54047
e1b3e71
 
 
 
 
 
 
 
 
 
 
cfa8541
 
e1b3e71
 
4b54047
e1b3e71
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cfa8541
e1b3e71
 
 
 
 
4b54047
e1b3e71
 
 
 
d598032
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
d598032
 
 
 
 
b2aa446
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e1b3e71
 
bf8519f
b2aa446
bf8519f
 
b2aa446
bf8519f
 
 
 
b2aa446
bf8519f
 
 
 
 
b2aa446
bf8519f
b2aa446
 
bf8519f
 
 
 
 
f1ae536
 
 
 
 
 
 
 
 
 
 
 
 
d598032
f1ae536
 
d598032
 
f1ae536
 
 
 
 
 
bf8519f
 
 
 
 
 
 
 
 
 
cfa8541
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cfa8541
 
 
 
 
 
 
 
 
 
bf8519f
 
 
 
 
 
 
f1ae536
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cfa8541
 
 
 
 
 
 
 
 
bf8519f
 
 
 
 
 
 
 
 
cfa8541
 
 
 
 
 
 
 
 
f1ae536
 
 
7f72224
 
 
f1ae536
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
82f0f29
f1ae536
82f0f29
 
f1ae536
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
cfa8541
 
 
 
bf8519f
 
 
 
 
 
 
 
f1ae536
 
 
bf8519f
 
 
 
 
 
 
 
f1ae536
 
d598032
 
f1ae536
 
bf8519f
 
 
f1ae536
 
bf8519f
f1ae536
bf8519f
 
 
 
 
f1ae536
bf8519f
cfa8541
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1ad1eea
bf8519f
 
 
 
cfa8541
 
 
e1b3e71
 
 
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
1f123e1
 
 
 
 
 
 
 
 
 
 
 
 
bf8519f
d598032
 
 
 
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c72f0a1
bf8519f
 
 
cfa8541
 
 
 
bf8519f
 
 
f1ae536
82f0f29
 
 
 
 
bf8519f
 
 
 
 
 
 
f1ae536
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f1ae536
bf8519f
 
 
 
 
 
f1ae536
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1ad1eea
bf8519f
 
cfa8541
bf8519f
 
 
e1b3e71
 
 
 
 
 
 
bf8519f
1f123e1
 
 
 
 
 
d598032
 
bf8519f
 
 
 
 
 
c72f0a1
 
bf8519f
f1ae536
bf8519f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""Customer data: a scoped metric pool and saved table workspaces.

  1. POOL: one row per customer, composing sales, cadence, receivables and address data.
  2. VIEWS: ordinary saved table views hold filters, sorts, layout and selected members in
     'customer_table_workspace'. The old rule-formula list readers and shipped list templates
     were retired in wave 57 under R13. The 'customer_lists' bucket is retained without reads
     or writes here so existing tenant data is preserved.

The pool composes already-validated customers-module functions. Its own validate() also
reconciles the address family independently. The daily digest still uses queues(), which calls
the customers module's contact recommendations and at-risk calculations directly.

The user-to-agent link (users.py 'agent' field) scopes the pool to that agent's book; users with
no link (owner/CFO/admin) see the whole book.
"""
import datetime as dt
import re
from urllib.parse import quote_plus

from harness.canonical_source import odoo as O
import core.periods as P
import core.store as store
import core.table_store as table_store
import modules.ar as ar
import modules.collections_exec as collections_exec
import modules.customers as cust
import modules.map as map_mod
import modules.sales as S

LIMIT = 500          # honest builder display cap β€” always shown with the full count, never silent
# Rule-formula readers retired in wave 57 under R13; keep the bucket so no tenant data is dropped.
KEY = 'customer_lists'
TABLE_KEY = 'customer_table_workspace'

# ------------------------------------------------------------------ the Google Maps link
#: The sentinel every blank connector attribute collapses to (`customers._partner_attrs` writes
#: it; the client's `CONNECTOR_BLANK` renders it). It is a real, visible value on the grid, NOT a
#: null β€” so an address builder that treats it as text pastes the literal string "(none)" into a
#: query and sends the reader to a place called none.
BLANK_ATTR = '(none)'

#: A trailing ISO country code in parentheses, as `O.m2o_name(state_id)` yields it: Odoo's state
#: names read "Texas (US)". ⚠ ANCHORED and UPPERCASE-ONLY on purpose. A greedy "strip the last
#: parenthetical" would eat a legitimate name that happens to end in brackets, and there is no
#: reason to risk that for a tidiness fix.
_STATE_COUNTRY_SUFFIX = re.compile(r'\s*\([A-Z]{2,3}\)$')

#: Google's documented Maps URLs endpoint for a place SEARCH. The same shape the client already
#: builds for a pin (`customer-grid/mapProjection.ts`), so the two doors agree.
MAPS_SEARCH_URL = 'https://www.google.com/maps/search/?api=1&query='


def _addr_part(v):
    """One address line, or '' β€” the sentinel and whitespace both read as absent."""
    s = str(v or '').strip()
    return '' if not s or s == BLANK_ATTR else s


def maps_url(a):
    """A Google Maps link to a partner's address, or '' when there is no address to point at.

    ⭐⭐ W40-T08 (owner I27) β€” SERVER-SIDE, and that is the whole design, per the wave's AM-1. The
    owner's instruction offered a client Formula as one option; D-115 says a formula column's
    values exist only in the browser, so the link would be blank in every host-side export and
    invisible to anything reading the stored cell. Computed here it is an ordinary cell value on
    the pool row, so it exports, filters and reads back like any other column.

    β›” BLANK UNLESS THE ROW CAN ACTUALLY BE LOCATED. The gate is street OR city OR zip: a country
    alone ("United States") resolves to a link that opens the middle of a continent, which is
    worse than an empty cell because it looks like an answer. MEASURED 2026-08-23: 140 of 3,641
    customers have no street, city or zip, and those are the rows this returns '' for.

    ⚠ `state` is stripped of its country suffix. It arrives as `O.m2o_name(state_id)`, i.e.
    "Texas (US)", and the country is already its own part of the address. Google resolves the
    string either way, so this is a tidiness call, not a correctness one.
    """
    street, street2 = _addr_part(a.get('street')), _addr_part(a.get('street2'))
    city, zipcode = _addr_part(a.get('city')), _addr_part(a.get('zip'))
    if not (street or city or zipcode):
        return ''
    state = _STATE_COUNTRY_SUFFIX.sub('', _addr_part(a.get('state')))
    parts = [p for p in (street, street2, city, state, zipcode,
                         _addr_part(a.get('country'))) if p]
    return MAPS_SEARCH_URL + quote_plus(', '.join(parts))

# ------------------------------------------------------------------ the metric pool
# Field metadata for the rule builder: key -> (label, kind). 'num' fields take >=/<=/>/</=,
# 'text' fields take contains/=. Every field is a column of pool() rows.
FIELDS = {
    'customer':         ('Customer', 'text'),
    'status':           ('Status', 'text'),
    'agent':            ('Agent', 'text'),
    'city':             ('City', 'text'),
    'state':            ('State', 'text'),
    'revenue_ytd':      ('YTD $', 'num'),
    'revenue_ly':       ('LY (same period) $', 'num'),
    'yoy_pct':          ('YoY %', 'num'),
    'at_risk':          ('At risk $', 'num'),
    'ltm_rev':          ('LTM $', 'num'),
    'orders_24m':       ('Orders (24M)', 'num'),
    'aov':              ('Avg order $', 'num'),
    'days_since':       ('Days since last order', 'num'),
    'days_since_invoice': ('Days since last invoice', 'num'),
    'typical_gap_days': ('Typical gap (days)', 'num'),
    'overdue_days':     ('Overdue vs cadence (days)', 'num'),
}
NUM_OPS = ('>=', '<=', '>', '<', '=')
TEXT_OPS = ('contains', '=')


def agent_pids(agent_name):
    """Partner ids of an agent's book (None = unscoped/whole book)."""
    return cust.agent_partner_ids(agent_name) if agent_name else None


#: Wave 17 R2 β€” `ar` bucket label -> the grid's column key. ONE mapping, read by the attribute
#: builder, the empty-row default and validate(), so a bucket cannot exist under two spellings.
AR_BUCKET_FIELDS = {'1-30': 'ar_aged_1_30', '31-60': 'ar_aged_31_60',
                    '61-90': 'ar_aged_61_90', '90+': 'ar_aged_90_plus'}

#: `_ar_attrs` sets this key when the Overdue tie failed or could not run (standing rule 8).
AR_WITHHELD = '_overdue_withheld'
#: The Overdue family. One tie covers all of them, so a failed tie withholds all of them.
AR_OVERDUE_FIELDS = ('ar_open', 'ar_overdue', 'ar_outstanding', *AR_BUCKET_FIELDS.values())


def ar_cell(ar_a, pid, key):
    """One AR cell for a pool row: None while a failed Overdue tie withholds its family."""
    if key in AR_OVERDUE_FIELDS and ar_a.get(AR_WITHHELD):
        return None
    return (ar_a.get(pid) or {}).get(key, 0.0)


def _ar_withheld(exc):
    """Log a withheld Overdue family loudly: a blank column must say why it is blank."""
    try:
        import harness.telemetry as _tel
        _tel.error('customer_data:_ar_attrs', exc, fallback='Overdue family withheld')
    except Exception:
        pass
    print(f"[aios] WARNING customer_data._ar_attrs withheld the Overdue family "
          f"({type(exc).__name__}: {exc})")


def _ar_attrs(t=None, *, pids=None, st=None):
    """{pid: AR family}: Overdue on Odoo's Aged Receivable basis, plus the 5-day grace number.

    R37: `ar_overdue` ("Overdue") is `ar.aged_receivable()`: receivable lines aged by due date
    with no grace, New Customer Hold due at once, credits and unapplied payments netted per
    partner, every debtor. `ar_open` (not yet due), `ar_outstanding` (total) and the four
    `ar_aged_*` buckets are slices of the same lines, so they sum to Odoo's report columns.
    The family ships only when `ar.overdue_tie()` holds. Else every cell in it is None.

    `ar_overdue_past_grace` ("Overdue past grace") and `ar_exposure` stay on
    `ar.credit_exposure()`, the collections basis with its 5-day grace.

    `days_to_pay` is read from the nightly snapshot, never computed here (about 300 s).
    """
    out = {}
    try:
        aged = ar.aged_receivable(t, pids=pids)
        tie = ar.overdue_tie(aged)
        if not tie['ok']:
            raise ArithmeticError(f"{tie['check']}: field {tie['a']} vs Odoo {tie['b']}, "
                                  f"gap {tie['gap']}, {tie['detail']}")
        for pid, r in aged['rows'].items():
            out[pid] = {
                'ar_open': r['current'], 'ar_overdue': r['overdue'], 'ar_outstanding': r['total'],
                **{k: r[f'aged_{b}'] for b, k in AR_BUCKET_FIELDS.items()},
            }
    except Exception as e:
        out = {AR_WITHHELD: True}
        _ar_withheld(e)
    try:
        exp = ar.credit_exposure(t)
        for r in exp.get('_all_rows') or []:
            row = out.setdefault(r['pid'], {})
            row['ar_overdue_past_grace'] = r.get('overdue', 0.0) or 0.0
            row['ar_exposure'] = r.get('exposure', 0.0) or 0.0
    except Exception as e:
        try:
            import harness.telemetry as _tel
            _tel.error('customer_data:_ar_attrs', e, fallback='exposure columns zero')
        except Exception:
            pass
    snapshot = (ar.days_to_pay_snapshot() if pids is None and st is None else
                ar.days_to_pay_snapshot(pids=pids, st=st))
    for pid, avg in snapshot.items():
        # A customer with an AR row but no settled invoice in the window keeps `None` β€” blank,
        # not 0. "Pays in 0 days" is a claim, and a false one.
        out.setdefault(pid, {})['days_to_pay'] = avg
    return out


def _mix_attrs(pids=None, t=None):
    """{pid: {top_category, sku_count, top_sku, top_category_pct}} β€” the product-mix family.

    ONE read_group over sale.order.line by (partner, product) across the LTM window, then the
    product->category map, then aggregation in Python. Line grain is the only grain that can
    answer "what does this customer buy", and it is deliberately ALL-CHANNEL scope-wise for the
    same reason inventory analysis is (see [[ri-channel-scope-amazon]]): a customer's product mix
    is a fact about the customer, not about a sales team.

    `top_category_pct` is that category's share of the customer's LTM line revenue β€” the number
    that says whether "top" means dominant or merely first.

    ⚠ There is NO `team_id` parameter, and its absence is the point rather than an omission: for
    a BU-scoped viewer the revenue columns beside this one ARE team-scoped while these are not,
    so "top category" can name products bought through a channel that viewer's revenue figures
    exclude. That is the same trade the inventory modules make, and it is the right one β€” a
    customer's product mix is a fact about the customer.
    """
    t = t or P.today()
    mf, mt = P.ltm(t)
    # ⚠ `pids` NARROWS THE RESULT, NEVER THE DOMAIN β€” and that is not a style choice. Putting
    # the caller's ~1,500 partner ids into an `in` clause on a LINE-grain read_group makes Odoo
    # answer **502 Bad Gateway**; the same query unfiltered returns in 22s. Measured 2026-07-27,
    # after the filtered form took the live Customer page past 20 MINUTES without rendering.
    # The grouped result is per-partner anyway, so dropping the extras in Python costs nothing.
    dom = [('order_id.state', 'in', ['sale', 'done']),
           ('order_id.date_order', '>=', str(mf)), ('order_id.date_order', '<=', str(mt))]
    keep = set(pids) if pids else None
    rows = O.read_group('sale.order.line', dom, ['price_subtotal:sum'],
                        ['order_partner_id', 'product_id'], lazy=False)
    from harness.canonical_source import active
    source = active()
    if source is not None:
        return source.customer_mix(rows)
    prod_ids = {O.m2o_id(r.get('product_id')) for r in rows if r.get('product_id')}
    prod_ids.discard(None)
    cats = {}
    plist = list(prod_ids)
    for i in range(0, len(plist), 5000):
        for p in O.search_read('product.product', [('id', 'in', plist[i:i + 5000])], ['categ_id']):
            # `categ_id`'s display name is the full PATH ("All / Foams & Finishes / Styrofoam").
            # The column takes the LEAF, because a column and a group label have to be readable β€”
            # and the path's own root ("All") carries no information. ⚠ Two categories that share
            # a leaf name would merge in a group-by; measured against this catalogue that does not
            # happen, and readability is worth more than defending against a rename.
            full = O.m2o_name(p.get('categ_id')) or ''
            cats[p['id']] = (full.split(' / ')[-1].strip() or '(none)') if full else '(none)'

    per = {}
    for r in rows:
        pid = O.m2o_id(r.get('order_partner_id'))
        prod = O.m2o_id(r.get('product_id'))
        if pid is None or prod is None or (keep is not None and pid not in keep):
            continue
        val = r.get('price_subtotal', 0.0) or 0.0
        e = per.setdefault(pid, {'skus': set(), 'by_cat': {}, 'by_sku': {}, 'total': 0.0})
        e['skus'].add(prod)
        e['by_cat'][cats.get(prod, '(none)')] = e['by_cat'].get(cats.get(prod, '(none)'), 0.0) + val
        e['by_sku'][prod] = e['by_sku'].get(prod, 0.0) + val
        e['total'] += val

    top_prod_ids = {max(e['by_sku'], key=e['by_sku'].get) for e in per.values() if e['by_sku']}
    names = {}
    tlist = list(top_prod_ids)
    for i in range(0, len(tlist), 5000):
        for p in O.search_read('product.product', [('id', 'in', tlist[i:i + 5000])], ['name']):
            names[p['id']] = p.get('name') or ''

    out = {}
    for pid, e in per.items():
        cat = max(e['by_cat'], key=e['by_cat'].get) if e['by_cat'] else '(none)'
        sku = max(e['by_sku'], key=e['by_sku'].get) if e['by_sku'] else None
        out[pid] = {
            'top_category': cat,
            'top_category_pct': (e['by_cat'][cat] / e['total']) if e['total'] else None,
            'sku_count': len(e['skus']),
            'top_sku': names.get(sku, '(none)') if sku is not None else '(none)',
        }
    return out


def _salesperson_attrs(pids=None, t=None):
    """{pid: salesperson} β€” the DOMINANT order-taker over LTM, by order count.

    ⚠ Salesperson is NOT the Agent, and they are not interchangeable (owner, 2026-07-27):
    the SALESPERSON is whoever entered and took the order, the AGENT is the person the customer
    is assigned to and who earns the commission β€” including when the customer orders through the
    office and somebody else keys it in. So this reads `sale.order.user_id` (the ORDER's taker,
    fully populated: 20 people over $6.2M LTM) and NOT `res.partner.user_id`, which is a
    different, near-empty field.

    A customer can have several over a year, so the column takes the one who took the MOST of
    their orders β€” a single value the table can group and filter cleanly. Ties break toward the
    larger revenue.
    """
    t = t or P.today()
    mf, mt = P.ltm(t)
    # Same rule as `_mix_attrs`: the pid list narrows the RESULT, not the domain. Order grain is
    # far smaller than line grain so this one never actually 502'd, but a caller's id list has no
    # business being pushed into a query whose grouping already answers per partner.
    keep = set(pids) if pids else None
    dom = S.order_domain(str(mf), str(mt), None)
    rows = O.read_group('sale.order', dom, ['amount_untaxed:sum'],
                        ['partner_id', 'user_id'], lazy=False)
    per = {}
    for r in rows:
        pid = O.m2o_id(r.get('partner_id'))
        if pid is None or (keep is not None and pid not in keep):
            continue
        who = O.m2o_name(r.get('user_id')) or '(none)'
        n, rev = r.get('__count', 0) or 0, r.get('amount_untaxed', 0.0) or 0.0
        cur = per.get(pid)
        if cur is None or (n, rev) > (cur[1], cur[2]):
            per[pid] = (who, n, rev)
    return {pid: v[0] for pid, v in per.items()}


def pool_has_audit(rows):
    """A pre-audit snapshot cannot answer the newly declared Customer fields."""
    required = set(cust.AUDIT_ATTRS)
    return all(required <= set(row) for row in rows)


def validate_audit_attrs(rows, client):
    """Numeric attributes and booleans against independent, scoped Odoo aggregates."""
    live = [r for r in rows if not r.get('_ledger_revived')]
    pids = [int(r['pid']) for r in live]
    domain = [('id', 'in', pids), ('active', 'in', [True, False])]
    checks = []
    for key, (field, kind) in cust.AUDIT_ATTRS.items():
        if kind not in ('int', 'bool'):
            continue
        complete = all(r.get(key) is not None for r in live)
        if kind == 'int':
            groups = client.read_group('res.partner', domain, [field + ':sum'],
                                       ['create_uid'], lazy=False) if pids else []
            theirs = sum(g.get(field) or 0 for g in groups)
            ours = sum(r.get(key) or 0 for r in live)
        else:
            theirs = client.search_count('res.partner', domain + [(field, '=', True)]) if pids else 0
            ours = sum(r.get(key) is True for r in live)
        checks.append({'check': 'customer audit attribute ' + key, 'ours': ours,
                       'theirs': theirs, 'ok': complete and ours == theirs})
    return checks


def pool(agent_name=None, team_id=None):
    """One row per customer across the scoped book: the union of YTD buyers, same-period-LY
    buyers and anyone with an order in the 24-month cadence window β€” so lapsed/win-back
    accounts are in the pool, not just YTD actives. Reuses the customers module's validated
    building blocks (_cust_rev / _cadence_bulk / _partner_attrs) so every metric matches the
    Customers page definitions."""
    return _pool_build(agent_name, team_id, limit=None)['rows']


def pool_first(agent_name=None, team_id=None, limit=100):
    """Wave-7 W1 (C1 as amended): the cold-load FAST SLICE β€” the pool's first `limit` rows in
    its own default order + the true total, built from the CHEAP families only (revenue Γ—3,
    cadence, status, partner attributes β‰ˆ 12s measured). The AR / product-mix /
    salesperson / coords columns stay BLANK until the full build swaps in: `_mix_attrs` alone
    is a whole-book 20s read_group whose domain must never be pid-narrowed (502 β€” see its
    docstring), so "all columns on 100 rows" measured 44.6s vs 45.1s full β€” no win. A partial
    payload is a UI PHASE, never a reporting basis β€” validate() reconciles the full pool only."""
    return _pool_build(agent_name, team_id, limit=limit, fast=True)


# ------------------------------------------------------------------ the address reconciliation
#: {our pool key: the `res.partner` field an Odoo domain filters on}. The two m2o columns are
#: stored here as RESOLVED NAMES (`O.m2o_name`), so the oracle asks Odoo whether the id is SET
#: while we count whether the name is non-blank β€” a set id resolving to an empty name is the one
#: place those two questions could come apart, and the per-key leg is what would report it.
ADDRESS_FIELDS = {
    'street': 'street', 'street2': 'street2', 'city': 'city',
    'state': 'state_id', 'country': 'country_id', 'zip': 'zip',
}

#: OUR blank. `_partner_attrs` collapses every partner attribute to '(none)' when blank (MECE, so
#: a group-by has no null bucket), a pool row for a partner with NO attrs takes the same sentinel
#: from the row template, and `_reconcile_ledger`'s revived rows are written '(none)' outright.
_ADDRESS_BLANK = ('(none)', '', None)


def address_blank(value):
    """OUR blank predicate, in ONE place β€” the fold and the oracle must ask ONE question.

    ⚠ `.strip()` is part of it, and that is the half that diverges from Odoo. A domain
    `('street', '!=', False)` counts a whitespace-only street as PRESENT while this counts it as
    blank, so the two normalizers would disagree by exactly the whitespace-only population.
    MEASURED 2026-08-12 across `street`/`street2`/`city`/`zip` over the whole pool: **0**. The
    leg that keeps it 0 is asserted in `validate()` rather than assumed
    ([[one-question-two-normalizers]]).
    """
    return value in _ADDRESS_BLANK or (isinstance(value, str) and not value.strip())


def validate_invoice_cadence(rows, client, t=None):
    """Reconcile each shipped invoice age to an independent Odoo read_group invoice_date:max."""
    from harness import semantic as sem
    pids = [int(row['pid']) for row in rows if row.get('pid')]
    source = sem.customer_rollup_oracle('customer_days_since_invoice', client,
                                        partner_ids=pids, grouped=True, today=t or P.today())
    mismatches = sum(row.get('days_since_invoice') != source.get(int(row['pid']))
                     for row in rows if row.get('pid'))
    return [{'check': 'Days since last invoice ties per customer to Odoo read_group invoice_date:max',
             'column': 'days_since_invoice', 'ours': mismatches, 'theirs': 0,
             'gap': mismatches, 'ok': mismatches == 0,
             'query': 'account.move read_group invoice_date:max by partner_id, state=posted, move_type=out_invoice',
             'customers_checked': len(pids)}]


def validate(team_id=None):
    """Reconcile the ADDRESS family the Customer grid ships to an independent Odoo aggregate.

    β›” THE POINT OF THIS FUNCTION IS THAT THE NUMBER SURVIVES THE SESSION THAT TOOK IT. W30-T33
    shipped `street`/`street2` after measuring their coverage with a throwaway shell one-liner β€”
    a measurement, never a `validate()` β€” so nothing in the repo re-checked it and the figure
    died with that session. The house law is *every metric gets a `validate()` reconciling to an
    INDEPENDENT Odoo aggregate*, and an oracle nobody re-runs rots exactly like a mode nobody
    runs ([[rules-need-gates]]). `aios-web/api/verify_odoo_relational.py` is what re-runs it.

    ⚠ THE TRAP THAT REDS A CORRECT LEG, and it is why every domain below is scoped to the pool's
    OWN pid set: **the customer pool is the WHOLE BOOK**, not `customer_rank > 0 AND active`
    ([[customer-grid-pool-vs-odoo-count]]). The 3,617/3,478 pair quoted in the wave-30 mailbox is
    the rank>0-active population and is NOT this one; measured here the same day, the pool holds
    3,629 rows of which 3,487 carry a street. Comparing our count against a differently-scoped
    Odoo count is a true measurement of the wrong subject [[measure-the-real-call]].

    β›” IT BUILDS THE FULL `pool()`, NOT `pool_first`/`fast=True`, AND THE COST IS REAL β€” **444 s
    measured 2026-08-12** (the address itself is ~14 s of that; AR, product mix, salesperson,
    coords and DBA are the rest). Reported rather than optimised away, per R6's second sentence.
    `fast=True` is not the shipping basis: it skips `_reconcile_ledger`, so the RETAINED customers
    (I13) never enter and the population is a different one. The recommended fix if this ever has
    to get cheap is a `families=` argument on `_pool_build` so a reconciliation can ask for the
    families it reconciles β€” NOT a second cheaper pool builder, which would be two evaluators for
    one question [[one-evaluator-per-question]].
    """
    rows = pool(team_id=team_id)
    pids = [int(r['pid']) for r in rows if r.get('pid')]
    odoo = O.get_odoo()
    checks = []

    # ── the population, and the three numbers that keep the address legs honest ───────────────
    # Every source query spells both active states explicitly. Otherwise Odoo's `active_test`
    # context would exclude archived rows from the independent side while the pool now correctly
    # includes their addresses, making the widened population look like an address defect.
    # An archived Odoo record still exists at source and can carry current attributes. Only a
    # ledger-only row was genuinely revived after disappearing from Odoo.
    revived = [r for r in rows if r.get('_ledger_revived') is True]
    rpids = [int(r['pid']) for r in revived if r.get('pid')]
    archive_domain = [('active', 'in', [True, False])]
    n_visible = odoo.search_count('res.partner', [('id', 'in', pids)] + archive_domain)
    # β›” ASSERTED, NOT ASSUMED. "The retained rows are the ones Odoo lost" is trivially true when
    # checked against the ledger that did the retaining β€” that is the self-sealing shape. So it is
    # asked of ODOO: a retained pid Odoo can still see means `_reconcile_ledger` buried a LIVE
    # customer's address behind six '(none)' cells.
    still_live = (odoo.search_count('res.partner', [('id', 'in', rpids)] + archive_domain)
                  if rpids else 0)
    checks.append({
        'check': "every RETAINED customer (I13) is one Odoo genuinely cannot see; asked of "
                 "Odoo, never of the ledger that did the retaining",
        'ours': still_live, 'theirs': 0, 'ok': still_live == 0,
        'detail': {'pool_rows': len(rows), 'odoo_visible': n_visible, 'retained': len(revived),
                   'archived_not_retained': len(pids) - n_visible - len(revived)},
    })

    # ── the six address columns, each against a domain Odoo evaluates server-side ─────────────
    # β›” `search_count` WITH A DOMAIN, never a re-read-and-recount: it makes ODOO do the filtering,
    # so the oracle cannot inherit a bug from `_partner_attrs`, which is the code under test.
    for key, field in ADDRESS_FIELDS.items():
        ours = sum(1 for r in rows if not address_blank(r.get(key)))
        theirs = odoo.search_count(
            'res.partner', [('id', 'in', pids), (field, '!=', False)] + archive_domain)
        checks.append({
            'check': f"the grid's `{key}` column is non-blank on exactly the pool customers Odoo "
                     f"says carry `{field}`; an INDEPENDENT search_count over the pool's own "
                     f"pid set, never a count of the rows we just built",
            'ours': ours, 'theirs': theirs, 'ok': ours == theirs,
            # `column` is what the GATE matches on. Substring-matching the prose is how a leg
            # silently stops being checked when somebody rewords it, and this file's own twin
            # (`verify_product_pool`) matches on prose precisely because it had no key to use.
            'detail': {'column': key, 'odoo_field': field, 'blank_here': len(rows) - ours},
        })

    # ── the two normalizers are ONE question, and this is the leg that keeps them one ─────────
    # Everything above compares OUR `.strip() or '(none)'` against ODOO'S `!= False`. Those agree
    # on every value except a whitespace-only one, which Odoo reports present and this module
    # renders blank. Measured 0 today; if it ever stops being 0 the per-key legs above go red for
    # a reason that has nothing to do with the address being wrong, so the cause is named HERE.
    text_keys = [k for k, f in ADDRESS_FIELDS.items() if f == k]
    ws = {}
    if pids:
        # `O.search_read`, the same door `_partner_attrs` reads through β€” the client's own method
        # takes `domain=`/`fields=` as keywords and this wrapper is the one with a fixed shape.
        for got in O.search_read('res.partner', [('id', 'in', pids)] + archive_domain, text_keys):
            for k in text_keys:
                v = got.get(k)
                if isinstance(v, str) and v and not v.strip():
                    ws[k] = ws.get(k, 0) + 1
    checks.append({
        'check': "no pool customer carries a WHITESPACE-ONLY address value; the one input on "
                 "which our blank predicate and Odoo's `!= False` provably disagree",
        'ours': sum(ws.values()), 'theirs': 0, 'ok': not ws,
        'detail': {'per_field': ws, 'fields_checked': text_keys},
    })
    return (checks + validate_audit_attrs(rows, odoo) + validate_invoice_cadence(rows, odoo)
            + validate_overdue(rows))


def _partner_team(row):
    """The row's customer sales team name (res.partner.team_id), the Collection report's grouping."""
    return row.get('sales_team')


def validate_overdue(rows, t=None, *, team_of=_partner_team):
    """The pool's Overdue column against Odoo's past-due receivable lines, in total and per team.

    `team_of(row)` names the row's team. It exists so a gate can regroup by another team field.
    """
    aged = ar.aged_receivable(t)
    tie = ar.overdue_tie(aged)
    pool = {int(r['pid']) for r in rows if r.get('pid')}
    missing = sorted(set(aged['rows']) - pool)
    ours = sum(r.get('ar_overdue') or 0.0 for r in rows)
    checks = [{'check': f"Customer field Overdue: sum over the pool == {ar.OVERDUE_DEFINITION} "
                        f"on {aged['date']}",
               'column': 'ar_overdue', 'ours': round(ours, 2), 'theirs': tie['b'],
               'gap': round(ours - tie['b'], 2), 'ok': tie['ok'] and abs(ours - tie['b']) <= 0.01,
               'detail': tie['detail']},
              {'check': 'every debtor on the Aged Receivable is a pool row keyed by partner id',
               'column': 'ar_overdue', 'ours': len(missing), 'theirs': 0, 'ok': not missing,
               'detail': {'missing': missing[:20]}}]
    # Team ids are collections_exec's defaults and names come from live Odoo, like the team sums:
    # no tenant literal here, and the mirror does not carry crm.team.
    day = dt.date.fromisoformat(aged['date'])
    by_team = collections_exec.odoo_aging_by_team(day)
    names = {r['id']: r.get('name') or str(r['id']) for r in collections_exec.O.search_read(
        'crm.team', [('id', 'in', sorted(by_team)), ('active', 'in', [True, False])], ['name'])}
    for team, odoo_row in by_team.items():
        name = names.get(team, str(team))
        mine = sum(r.get('ar_overdue') or 0.0 for r in rows if team_of(r) == name)
        theirs = round(odoo_row['overdue'], 2)
        checks.append({
            'check': f"Customer field Overdue for {name}: pool sum == Odoo Aged Receivable overdue "
                     f"for {name} on {aged['date']}",
            'column': 'ar_overdue', 'ours': round(mine, 2), 'theirs': theirs,
            'gap': round(mine - theirs, 2), 'ok': abs(mine - theirs) <= 0.01,
            'detail': {'team_id': team}})
    return checks


def _multi_team_label(n_teams):
    """Name a multi-team customer using the tenant's team count."""
    return 'Both' if n_teams == 2 else 'All'


def dba_options():
    """Read the tenant's team labels and its multi-team choice."""
    names = list(O.active_team_names().values())
    return names + ([_multi_team_label(len(names))] if len(names) > 1 else [])


def _dba_attrs(pids, t):
    """Map customers to configured team labels over the pool's 24-month order window."""
    try:
        import datetime as _dt
        label = O.active_team_names()
        d_from = (_dt.date.fromisoformat(str(t)) - _dt.timedelta(days=731)).isoformat()
        pairs = O.read_group(
            'sale.order',
            [('state', 'in', ('sale', 'done')), ('date_order', '>=', d_from),
             ('partner_id', 'in', list(pids)), ('team_id', 'in', list(label))],
            ['partner_id'], ['partner_id', 'team_id'], lazy=False)
        teams = {}
        for r in pairs:
            pid = O.m2o_id(r.get('partner_id'))
            tid = O.m2o_id(r.get('team_id'))
            if pid and tid in label:
                teams.setdefault(pid, set()).add(tid)
        multi = _multi_team_label(len(label))
        return {pid: (multi if len(ts) > 1 else label[next(iter(ts))])
                for pid, ts in teams.items()}
    except Exception:
        return {}


def _book_records(team_id=None):
    """Yield the canonical rank, confirmed-order and posted-document identity union."""
    if team_id:
        groups = O.read_group('sale.order',
            [('state', 'in', ('sale', 'done')), ('team_id', '=', team_id)],
            ['partner_id'], ['partner_id'], lazy=False)
        for row in groups:
            pid = O.m2o_id(row.get('partner_id'))
            if pid is not None:
                yield {'id': pid}
        return
    yield from O.search_read('res.partner', [('customer_rank', '>', 0),
        ('active', 'in', [True, False])], ['id'], limit=200000)
    # The debtor leg keeps every partner on Odoo's Aged Receivable in the book (R37).
    for model, domain in [
        ('sale.order', [('state', 'in', ('sale', 'done'))]),
        ('account.move', [('state', '=', 'posted'), ('move_type', 'in', ('out_invoice', 'out_refund'))]),
        ('account.move.line', list(ar.AGED_LINE_DOMAIN))]:
        for row in O.read_group(model, domain, ['partner_id'], ['partner_id'], lazy=False):
            pid = O.m2o_id(row.get('partner_id'))
            if pid is not None:
                yield {'id': pid}


def _book_pids(team_id=None, agent_pids=None):
    """⭐ WAVE 20 (owner items 8 + 27, ruling R3) β€” THE WHOLE BOOK, not just who bought recently.

    Owner: *"Martin Pasternak showing only 313 accounts when I filter agent, whereas in Odoo it is
    494 … I want our App to show complete source of truth, even if no sale.order at all or not.
    Its important to see what customer is getting assigned to Martin or whether we can retarget
    them."* The pool was a 24-MONTH SALES universe (YTD βˆͺ LY-YTD βˆͺ cadence-window buyers), so a
    customer assigned to an agent who has never ordered β€” precisely a retargeting target β€” did not
    exist in the app at all.

    R3's original definition was measured against live Odoo 2026-08-05:
        customer_rank>0 active .............. 3,614
        ever ordered (confirmed, team 5/6) ...  1,748
        UNION ............................... 3,723   (vs 1,555 in the old 24-month pool)
    Martin's book under this rule is **494** β€” the owner's Odoo number, to the account.

    β›” **THE "agent-assigned" LEG OF R3 IS DELIBERATELY NOT A THIRD TERM, and that is a
    measurement, not a shortcut.** Taken literally it added 174 partners and pushed Martin to 503;
    every one of the 9 extras on his book was an ODOO ADDRESS RECORD rather than an account β€”
    `type` in (`delivery`, `other`), most carrying a `parent_id`, and TWO with `name: False`.
    Shipping them would have put nameless rows in the customer table and made "how many customers
    does Martin have" answer 503 against an Odoo screen that says 494.
    Excluding address types wholesale is also wrong (`type not in (delivery, invoice, other,
    private)` measured **488** β€” it drops 6 genuine accounts that happen to carry a delivery
    type). `customer_rank > 0` is the predicate that means "this partner is a customer record",
    it reproduces the owner's number exactly, and every agent-assigned ACCOUNT already satisfies
    it β€” so the leg is subsumed rather than dropped. Item 27's actual ask (the 126 assigned
    partners with no `sale.order` at all) is fully served: they are rank>0 and they are in.

    S63-T14 widens the rank leg to both Odoo active states. Archived accounts are source rows, not
    deleted rows: the shipped default View owns the ordinary removable `Archived is not true`
    condition. Keeping that distinction at the View layer is what lets removing the condition
    reveal the same row under ordinary permissions instead of requiring a second data path.

    S63-T15 makes the unscoped registry use the complete identity union already owned by the Odoo
    customer source/mirror contract: rank-positive partners, every confirmed-order partner, and
    every posted customer-document partner.  The document leg is load-bearing for direct invoices
    with no sale order and for historical records whose rank was reset.  It is identity only;
    revenue and permissions remain scoped by their own evaluators.

    ⚠ BU SCOPE IS APPLIED THROUGH ORDERS, NOT THROUGH THE PARTNER. `res.partner` carries no team,
    so a BU-scoped caller gets the partners who have ORDERED in that BU (plus their own agent
    book). Widening it to every partner for a scoped user would cross the BU isolation rule that
    the whole permissioning model rests on.
    """
    try:
        book = {r['id'] for r in _book_records(team_id)}
        # An agent filter NARROWS the book to that agent's partners β€” never widens it. The
        # intersection is what keeps an agent-login user inside their own book while still
        # gaining every no-order account assigned to them, which is the whole point of item 27.
        return (book & set(agent_pids)) if agent_pids is not None else book
    except Exception as e:
        # Degrade to the sales-derived universe rather than failing the page β€” but LOUDLY.
        #
        # β›” THIS DEGRADATION IS NOT LIKE THE OTHERS IN THIS MODULE, and the difference is what
        # makes silence wrong here. Every other family (`_ar_attrs`, `_mix_attrs`, …) degrades to
        # a blank COLUMN, which is visible: the user sees an empty column and asks why. This one
        # degrades to absent ROWS. The pool still builds from the sales legs, so the page renders
        # perfectly, the counts look plausible, and Martin is quietly back at 313 with nothing
        # anywhere saying the book leg failed. An invisible degradation of a COUNT is the failure
        # mode this codebase keeps writing rules against ([[no-unverifiable-aggregates]]).
        try:
            import harness.telemetry as _tel
            _tel.error('customer_data:_book_pids', e, fallback='sales-derived pool only')
        except Exception:
            pass
        print(f"[aios] WARNING customer_data._book_pids failed ({type(e).__name__}: {e}) - the "
              f"customer pool is falling back to the 24-month SALES universe, so accounts with "
              f"no recent orders (owner item 27) are MISSING from this build.")
        return set()


def _pool_identity(agent_name=None, team_id=None, t=None):
    """The canonical customer row universe and the source maps that establish it.

    This is the identity half of ``_pool_build``, extracted so sync reconciliation can compare
    the mirror with the actual compiled database without paying for address, AR, product-mix,
    mapping, or ledger cell families.  There is still one evaluator: the pool itself consumes
    this result.
    """
    t = t or P.today()
    yf, yt = P.ytd(t)
    lf, lt = P.ytd_last_year(t)
    mf, mt = P.ltm(t)
    pids = agent_pids(agent_name)
    this = cust._cust_rev(yf, yt, team_id, pids)
    last = cust._cust_rev(lf, lt, team_id, pids)
    ltm = cust._cust_rev(mf, mt, team_id, pids)
    cad = cust._cadence_bulk(t, team_id, agent_pids=pids)
    all_pids = set(this) | set(last) | set(cad) | _book_pids(team_id, pids)
    return {"today": t, "agentPids": pids, "this": this, "last": last,
            "ltm": ltm, "cadence": cad, "pids": all_pids}


def identity_pids(agent_name=None, team_id=None, t=None):
    """The exact Odoo partner ids the compiled customer database will build before retention."""
    return set(_pool_identity(agent_name, team_id, t)["pids"])


def source_batches(cur, *, st, agent_name=None, team_id=None, ids=None, needed=None,
                   ledger_batches=()):
    """Stream the canonical customer identity union and project only one batch at a time."""
    from harness.canonical_source import bounded, mirrored, BATCH
    with mirrored(cur, st, kind='customer'), \
            bounded(cur, 'identity' if ids is None else 'customer', ids or [], st) as identity:
        live = identity.snapshot(_book_records(team_id))
        where = ''
        if agent_name and agent_name not in {'All', 'All agents'}:
            members = identity.agent_members(agent_name)
            where = (f"WHERE json_extract_string(body, '/id') IN "
                     f"(SELECT json_extract_string(body, '/id') FROM \"{members.table}\")")
        population = identity.snapshot({'id': r[0], 'live': True} for r in ())
        cur.execute(f'INSERT INTO "{population.table}" SELECT row_number() OVER (), '
            f"json_object('id', try_cast(json_extract_string(body, '/id') AS BIGINT), 'live', true) "
            f'FROM "{live.table}" {where} GROUP BY json_extract_string(body, \'/id\')')
        if not agent_name and team_id is None:
            for batch in ledger_batches:
                records = []
                for key, entry in batch:
                    try:
                        pid = int(key)
                    except (TypeError, ValueError):
                        continue
                    if ids is None or pid in ids:
                        records.append({'id': pid, 'live': False, 'entry': entry})
                if records:
                    cur.execute(f'INSERT INTO "{population.table}" SELECT 0, j FROM unnest(?::JSON[]) r(j) '
                        f"WHERE try_cast(json_extract_string(j, '/id') AS BIGINT) NOT IN "
                        f"(SELECT try_cast(json_extract_string(body, '/id') AS BIGINT) FROM \"{live.table}\")",
                        [[__import__('json').dumps(r) for r in records]])
                records.clear()
        # Sort the complete live/retained union once; pages seek a physical key, not JSON.
        cur.execute(f'CREATE OR REPLACE TEMP TABLE "{population.table}" AS '
            f"SELECT ordinal, body, try_cast(json_extract_string(body, '/id') AS BIGINT) pid "
            f'FROM "{population.table}" ORDER BY pid')
        last = None
        while True:
            rows = cur.execute(f"SELECT pid, "
                f"json_extract_string(body, '/live') = 'true', json_extract(body, '/entry') "
                f'FROM "{population.table}" WHERE (?::BIGINT IS NULL OR pid > ?) ORDER BY pid LIMIT ?',
                [last, last, BATCH]).fetchall()
            if not rows:
                return
            live_ids = [pid for pid, present, _ in rows if present]
            ledger = {str(pid): __import__('json').loads(entry) for pid, present, entry in rows if not present}
            with bounded(cur, 'customer', live_ids, st):
                if needed is not None and not needed:
                    projected = [dict(cust.audit_attr_defaults(), pid=pid) for pid in live_ids]
                else:
                    projected = _pool_build(agent_name, team_id, None, selected=live_ids,
                                            needed=needed, st=st)['rows'] if live_ids else []
                projected, _ = _reconcile_ledger(projected, len(projected), agent_name, team_id,
                                                  ledger=ledger, write=False)
                yield projected
            last = rows[-1][0]
            projected.clear()
            rows.clear()


def _pool_build(agent_name, team_id, limit, fast=False, *, selected=None, needed=None, st=None):
    from harness.canonical_source import active, MirrorSource
    source = active()
    if isinstance(source, MirrorSource) and st is None:
        st = source.store
    if selected is None:
        identity = _pool_identity(agent_name, team_id)
    else:
        t = P.today()
        pids = set(selected)
        wants = lambda keys: needed is None or bool(set(keys) & set(needed))
        yf, yt = P.ytd(t)
        lf, lt = P.ytd_last_year(t)
        mf, mt = P.ltm(t)
        identity = {'today': t, 'agentPids': pids, 'pids': pids,
                    'this': cust._cust_rev(yf, yt, team_id, pids) if wants(
                        ['customer', 'status', 'revenue_ytd', 'yoy_pct', 'at_risk']) else {},
                    'last': cust._cust_rev(lf, lt, team_id, pids) if wants(
                        ['customer', 'status', 'revenue_ly', 'yoy_pct', 'at_risk']) else {},
                    'ltm': cust._cust_rev(mf, mt, team_id, pids) if wants(['customer', 'ltm_rev']) else {},
                    'cadence': cust._cadence_bulk(t, team_id, agent_pids=pids) if wants([
                        'orders_24m', 'aov', 'last_order', 'days_since', 'typical_gap_days', 'overdue_days']) else {}}
    t = identity["today"]
    pids = identity["agentPids"]
    this, last, ltm = identity["this"], identity["last"], identity["ltm"]
    cad, all_pids = identity["cadence"], identity["pids"]
    total = len(all_pids)
    if limit is not None and total > limit:
        # the SAME ordering the assembled pool ships (see the sort below) β€” the slice must be
        # the first page of the very list the full build renders, or the swap-in reshuffles.
        ranked = sorted(all_pids,
                        key=lambda p: -((this.get(p) or {}).get('rev', 0.0)
                                        + (last.get(p) or {}).get('rev', 0.0)))
        all_pids = set(ranked[:limit])
    attrs = cust._partner_attrs(list(all_pids), **({'needed': needed} if selected is not None else {}))
    invoice_days = {} if fast or (needed is not None and 'days_since_invoice' not in needed) \
        else cust._invoice_cadence_bulk(t, partner_ids=all_pids)
    if fast:
        # C1 fast phase: the slow families stay empty β€” their columns render blank behind the
        # toolbar's partial indicator until the full build swaps in.
        ar_a, mix_a, sp_a, geo_a, dba_a = {}, {}, {}, {}, {}
    else:
        # The three families the owner asked for (2026-07-27). Each degrades to {} rather than
        # raising: a customer table that cannot render because AR is momentarily unreachable is
        # a worse failure than one with a blank column.
        wants = lambda keys: needed is None or bool(set(keys) & set(needed))
        ar_a = (_ar_attrs(t, pids=all_pids, st=st) if source is not None else _ar_attrs(t)) \
            if wants(['ar_open', 'ar_overdue', 'ar_overdue_past_grace', 'ar_outstanding',
                      'ar_exposure', 'days_to_pay', *AR_BUCKET_FIELDS.values()]) else {}
        mix_a = _mix_attrs(all_pids, t) if wants(['top_category', 'top_category_pct', 'sku_count', 'top_sku']) else {}
        sp_a = _salesperson_attrs(all_pids, t) if wants(['salesperson']) else {}
        # W11 (wave-7): coordinates for the Map VIEW β€” Odoo coords else the geocode cache,
        # read-only reuse (coords_for never geocodes); missing β†’ null lat/lon, and the
        # client's map view discloses the count.
        geo_a = map_mod.coords_for(list(all_pids), **({'st': st} if source is not None else {})) \
            if wants(['lat', 'lon']) else {}
        # C-DBA (2026-08-02): the brand attribute rides the full build only, like the rest.
        dba_a = _dba_attrs(all_pids, t) if wants(['dba']) else {}
    # customers whose only activity sits in the older half of the 24-month cadence window
    # appear in no revenue map β€” fetch their names directly (else the lapsed cohort, exactly
    # the win-back targets this union exists for, would render as '?')
    unnamed = [pid for pid in all_pids
               if not ((this.get(pid) or {}).get('name') or (last.get(pid) or {}).get('name')
                       or (ltm.get(pid) or {}).get('name'))] if needed is None or 'customer' in needed else []
    extra_names = ({r['id']: r['name'] for r in
                    O.search_read('res.partner', [('id', 'in', unnamed),
                                                  ('active', 'in', [True, False])], ['name'])}
                   if unnamed else {})
    rows = []
    for pid in all_pids:
        tv = this.get(pid) or {}
        lv = last.get(pid) or {}
        c = cad.get(pid) or {}
        rev, ly = tv.get('rev', 0.0), lv.get('rev', 0.0)
        gap = c.get('typical_gap_days')
        overdue = c.get('overdue_days')
        aov = c.get('aov', 0.0)
        status = ('New' if rev > 0 and ly <= 0 else
                  'Lost' if ly > 0 and rev <= 0 else
                  'Declining' if 0 < rev < ly else
                  'Growing' if rev > 0 else 'Dormant')
        a = attrs.get(pid) or {}
        rows.append({
            **{key: a.get(key, default) for key, default in cust.audit_attr_defaults().items()},
            'pid': pid,
            'customer': tv.get('name') or lv.get('name')
                        or (ltm.get(pid) or {}).get('name') or extra_names.get(pid) or '?',
            'status': status,
            # A normal source boolean. Views decide whether to hide it; the population never
            # disappears an archived source record below the permission/filter layers.
            'archived': bool(a.get('archived', False)),
            'agent': a.get('agent', '(none)'),
            # ⭐ W30-T33 (carried W29-T51) β€” the street lines beside the city they belong to.
            'street': a.get('street', '(none)'), 'street2': a.get('street2', '(none)'),
            'city': a.get('city', '(none)'),
            # C-DBA: blank (not '(none)') β€” a select's blank is its own honest empty state.
            'dba': dba_a.get(pid, ''),
            'state': a.get('state', '(none)'),
            # Owner 2026-07-27, the partner-attribute family. `.get(..., default)` is not
            # defensive habit here: search_read returned 1,548 of 1,550 pool pids β€” two partners
            # in the sales history are archived or deleted β€” so a customer with NO attrs row is
            # a real case, and it must render as '(none)' rather than KeyError the whole page.
            'country': a.get('country', '(none)'), 'zip': a.get('zip', '(none)'),
            'payment_terms': a.get('payment_terms', '(none)'),
            'customer_since': a.get('customer_since', ''),
            # Row-level datum for the `created_time` field type (wave-5 item 11) β€” rides every
            # row like pid, no column of its own until a user creates one.
            '_created': a.get('created_at', ''),
            # ⭐⭐ W40-T08 (owner I24) β€” the DECLARED pre-set date column, beside the row datum
            # above rather than instead of it. `_created` is only ever readable through a
            # `created_time` column the user has to create; this is a real cell under its own key,
            # so it exports, filters and appears in the semantic topic. Truncated the way
            # `customer_since` is, and for the same reason: a date-typed column compared
            # ISO-LEXICALLY drifts by the time component if the time is left on.
            # ⚠ The value is IDENTICAL to `customer_since` on this database, because both come
            # from `res.partner.create_date`. That is not a duplicate by accident: "Customer
            # since" is a business fact about the relationship and cannot be the uniform
            # records-created column I24 asks every database for (Products has no such notion).
            'created_on': str(a.get('created_at') or '')[:10],
            # ⭐⭐ W40-T08 (owner I27) β€” the Google Maps link, replacing the hardcoded "Go" column.
            'maps_url': maps_url(a),
            'tags': a.get('tags', '(none)'), 'pricelist': a.get('pricelist', '(none)'),
            # AR: the Overdue family ties to Odoo's Aged Receivable, or every cell is None.
            **{k: ar_cell(ar_a, pid, k) for k in (
                'ar_open', 'ar_overdue', 'ar_overdue_past_grace', 'ar_outstanding',
                'ar_exposure', *AR_BUCKET_FIELDS.values())},
            'days_to_pay': (ar_a.get(pid) or {}).get('days_to_pay'),
            # Product mix, LTM, line grain, ALL-channel.
            'top_category': (mix_a.get(pid) or {}).get('top_category', '(none)'),
            'top_category_pct': (mix_a.get(pid) or {}).get('top_category_pct'),
            'sku_count': (mix_a.get(pid) or {}).get('sku_count', 0),
            'top_sku': (mix_a.get(pid) or {}).get('top_sku', '(none)'),
            # The ORDER-TAKER, not the agent. Different people, different questions.
            'salesperson': sp_a.get(pid, '(none)'),
            # W11: nullable β€” the Map view pins what it can and counts what it cannot.
            'lat': (geo_a.get(pid) or {}).get('lat'),
            'lon': (geo_a.get(pid) or {}).get('lon'),
            'revenue_ytd': rev, 'revenue_ly': ly,
            'yoy_pct': P.yoy_pct(rev, ly),
            'at_risk': max(ly - rev, 0.0),
            'ltm_rev': (ltm.get(pid) or {}).get('rev', 0.0),
            'orders_24m': c.get('n_orders', 0), 'aov': aov,
            'last_order': c.get('last_order', ''),
            'days_since': c.get('days_since'),
            'days_since_invoice': invoice_days.get(pid),
            'typical_gap_days': gap, 'overdue_days': overdue,
        })
    for r in rows:
        # Kept for compatibility with existing saved layouts while `archived` is the canonical
        # boolean new filters use.
        r['odoo_status'] = 'Archived' if r['archived'] else 'Active'
        r['_ledger_revived'] = False
    # I13 (owner item 13, contract C6): RETAIN customers that disappear from Odoo.
    # Only the FULL build reconciles the ledger β€” the fast slice is 100 rows by construction,
    # so letting it write would mark ~1,450 living customers archived on every cold load.
    if not fast and selected is None:
        if isinstance(source, MirrorSource):
            rows, total = _reconcile_ledger(rows, total, agent_name, team_id,
                ledger=(st.get(LEDGER_KEY) or {}) if st is not None else None, write=False)
        else:
            rows, total = _reconcile_ledger(rows, total, agent_name, team_id)
    rows.sort(key=lambda r: -(r['revenue_ytd'] + r['revenue_ly']))
    return {'rows': rows, 'total': total}


LEDGER_KEY = 'customer_ledger'


def _reconcile_ledger(rows, total, agent_name, team_id, *, ledger=None, write=True):
    """Remember every customer we have ever seen, and keep serving the ones Odoo stopped
    returning (owner item 13: "if it is deleted, that it goes to an archived tag immediately β€”
    so stored in our platform even if deleted in Odoo").

    Why a ledger at all: this pool is built from SALES HISTORY, not from a partner list, so a
    partner deleted in Odoo usually keeps appearing (their order lines remain) β€” until the day
    the lines go too, when the row would silently vanish along with every note, tag and cohort
    membership attached to it. Nothing else in the app would notice. The ledger is the only
    record that the customer ever existed, which is why it starts recording now rather than
    when the surfacing is finished: history you did not write down is not recoverable later.

    ⚠ SCOPE-SAFE. The ledger is written ONLY from an UNSCOPED build (no agent, no BU). A scoped
    build legitimately sees a fraction of the book, and reconciling from it would mark every
    customer outside that scope 'Archived' for everyone β€” the failure would look exactly like
    the data loss this is meant to prevent. Scoped builds READ the ledger and resurrect nothing
    they cannot prove they should see.
    """
    try:
        ledger = dict(store.get(LEDGER_KEY) or {}) if ledger is None else dict(ledger)
    except Exception:
        return rows, total                  # a ledger we cannot read must not break the table
    today = str(P.today())
    live = {r['pid'] for r in rows}
    unscoped = agent_name is None and team_id is None

    if unscoped and write:
        for r in rows:
            e = ledger.get(str(r['pid'])) or {}
            e.update({'name': r['customer'], 'last_seen': today})
            e.setdefault('first_seen', today)
            ledger[str(r['pid'])] = e

    # Rows the ledger knows and this build did not return. On an unscoped build that means
    # "gone from Odoo"; on a scoped one it usually just means "not in this agent's book", so
    # only the unscoped build may surface them.
    revived = []
    if unscoped:
        for key, e in ledger.items():
            try:
                pid = int(key)
            except (TypeError, ValueError):
                continue
            if pid in live:
                continue
            revived.append({
                **cust.audit_attr_defaults(),
                'pid': pid, 'customer': e.get('name') or '?', 'status': 'Dormant',
                'odoo_status': 'Archived',
                'archived': True, '_ledger_revived': True,
                # Everything else is genuinely UNKNOWN now β€” the record is gone. Blank is the
                # honest rendering; carrying the last-known numbers forward would state figures
                # as current that nothing can reconcile ([[no-unverifiable-aggregates]]).
                # ⚠ W30-T33: the address keys land HERE TOO, and this is the site a field addition
                # silently skips. `rows_from_pool` projects with `r.get(k)`, so a revived row
                # missing a key renders BLANK rather than raising β€” archived customers would show
                # an empty address column while live ones showed a full one, and nothing anywhere
                # would report it. Two row templates, one field contract.
                'agent': '(none)', 'street': '(none)', 'street2': '(none)',
                'city': '(none)', 'state': '(none)', 'country': '(none)',
                'zip': '(none)', 'payment_terms': '(none)', 'customer_since': '',
                # W40-T08 lands on BOTH row templates, which is the warning above being obeyed
                # rather than restated. A revived row has no address and no create_date to read,
                # so both are blank β€” and blank is exactly what `maps_url` returns for an
                # addressless customer anyway, so the archived rows agree with the live ones.
                '_created': '', 'created_on': '', 'maps_url': '',
                'tags': '(none)', 'pricelist': '(none)',
                'ar_open': 0.0, 'ar_overdue': 0.0, 'ar_overdue_past_grace': 0.0,
                'ar_outstanding': 0.0, 'ar_exposure': 0.0,
                'days_to_pay': None,
                **{k: 0.0 for k in AR_BUCKET_FIELDS.values()},
                'top_category': '(none)', 'top_category_pct': None, 'sku_count': 0,
                'top_sku': '(none)', 'salesperson': '(none)', 'lat': None, 'lon': None,
                'revenue_ytd': 0.0, 'revenue_ly': 0.0, 'yoy_pct': None, 'at_risk': 0.0,
                'ltm_rev': 0.0, 'orders_24m': 0, 'aov': 0.0, 'last_order': '',
                'days_since': None, 'days_since_invoice': None,
                'typical_gap_days': None, 'overdue_days': None,
            })
        if write and store.available():
            try:
                store.update(LEDGER_KEY, lambda cur: {**cur, **ledger}, flush='async')
            except Exception:
                pass                        # recording is best-effort; the table is not
    return rows + revived, total + len(revived)


# ------------------------------------------------------------------ Airtable-style table workspace
# This is intentionally separate from `customer_lists`: a LIST owns membership/formula
# semantics, while a VIEW owns presentation/query state (filters, multi-sort, grouping,
# visible fields, widths, color, row height). List views can reference the same formula
# without conflating the two persistence contracts.
#
# The store logic MOVED to core/table_store.py 2026-07-27 (the table-page factory): these
# names are the Customer table's instance of the generic per-object workspace store, kept as
# module functions so every existing caller (app.py's host loop, QA gates) is untouched.
# A NEW table object gets its own `core.table_store.make('<its>_table_workspace')`.
_TSTORE = table_store.make(TABLE_KEY)

#: Wave 16 C-TOPIC β€” the ops OBJECT, for callers that select a table by TOPIC (grid_events'
#: `EventCtx.table`). The module-level re-exports below stay for every existing caller.
TABLE_OPS = _TSTORE

table_workspace = _TSTORE.workspace
save_table_view = _TSTORE.save_view
delete_table_view = _TSTORE.delete_view
save_table_field = _TSTORE.save_field
delete_table_field = _TSTORE.delete_field
duplicate_table_field = _TSTORE.duplicate_field
patch_table_overlay = _TSTORE.patch_overlay
save_table_folders = _TSTORE.save_folders
save_table_active_view = _TSTORE.save_active_view   # owner item 3: last-opened view, per user
save_table_record_layout = _TSTORE.save_record_layout  # 2026-08-02 C-LAYOUT: record-detail order


# ------------------------------------------------------------------ digest compatibility
def queues(agent_name=None, team_id=None, limit=200):
    """The two canonical morning lists (call list + win-back) for the daily digest email β€”
    unchanged formulas via the customers module (the digest always uses the CANONICAL templates,
    not a user's edited copy, so every rep's email means the same thing)."""
    pids = agent_pids(agent_name)
    return {
        'calls': cust.contact_recommendations(team_id=team_id, limit=limit, agent_pids=pids),
        'risk': cust.at_risk(team_id=team_id, limit=limit, agent_pids=pids),
    }