platform/modules/warehouse.py

"""Warehouse module — SKU movement & warehouse efficiency, live from stock.picking / stock.move
(read-only). Physical operations are consolidated (one network of warehouses, not BU-tagged).

What the data supports (probed 2026-07-05): date_done is fully populated on done pickings, the
Ditmas Ave outbound runs an explicit Pick -> Pack -> Ship funnel (separate picking types), ~25k
pickings and ~221k moves a year, and inventory-adjustment moves (~10k/yr) give a shrinkage /
correction signal. Cycle times, on-time rates, throughput, backlog age and adjustment dollars are
all derivable; anything scanner-level (walk paths, per-picker rates) is NOT in the data and is
not claimed.

All metrics are computed from ONE 12-month done-picking pull + small side pulls; validate()
reconciles row-built aggregates to independent read_group/search_count paths.
"""
import sys
import datetime as dt
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import core.odoo as O
import core.periods as P

ONTIME_GRACE_DAYS = 1          # shipped within scheduled + 1 day counts as on time
# Main-warehouse funnel + Putaway (the dock-to-stock proxy: receipts sit until put away).
_STAGE_NAMES = {'Pick': 'Pick', 'Pack': 'Pack', 'Delivery Orders': 'Ship', 'Putaway': 'Putaway'}


def _dtp(v):
    try:
        return dt.datetime.fromisoformat(str(v)[:19])
    except Exception:
        return None


def _types():
    """picking_type_id -> {code, wh, name, stage} (stage set for the main-warehouse funnel)."""
    out = {}
    for t in O.search_read('stock.picking.type', [], ['name', 'code', 'warehouse_id']):
        wh = O.m2o_name(t.get('warehouse_id')) or ''
        stage = _STAGE_NAMES.get(t['name']) if 'Ditmas' in wh else None
        out[t['id']] = {'code': t['code'], 'wh': wh, 'name': t['name'], 'stage': stage}
    return out


def _done_pickings(y1):
    return O.search_read('stock.picking',
                         [('state', '=', 'done'), ('date_done', '>=', y1)],
                         ['picking_type_id', 'scheduled_date', 'date_done', 'date',
                          'backorder_id'])


def _open_pickings():
    return O.search_read('stock.picking',
                         [('state', 'in', ['confirmed', 'waiting', 'assigned'])],
                         ['picking_type_id', 'scheduled_date', 'date', 'origin', 'partner_id',
                          'state'])


def _median(v):
    if not v:
        return None
    s = sorted(v)
    n = len(s)
    return s[n // 2] if n % 2 else (s[n // 2 - 1] + s[n // 2]) / 2


def _p90(v):
    if not v:
        return None
    s = sorted(v)
    return s[min(len(s) - 1, int(len(s) * 0.9))]


def _iso_week(d):
    y, w, _ = d.isocalendar()
    return f'{y}-W{w:02d}', w


def _xmr(series):
    """Wheeler XmR (individuals) limits + the THREE detection rules — no more (extra rules =
    alarm fatigue; see the warehouse research brief). series = [(label, value)] in time order.
    Ported logic cribbed from xmrit (MIT); ~30 lines, no dependency."""
    vals = [v for _, v in series]
    if len(vals) < 8:
        return {'center': None, 'unpl': None, 'lnpl': None, 'points': [
            {'label': l, 'value': v, 'signal': ''} for l, v in series]}
    center = sum(vals) / len(vals)
    mrs = [abs(vals[i] - vals[i - 1]) for i in range(1, len(vals))]
    mr_bar = sum(mrs) / len(mrs) if mrs else 0.0
    unpl = min(100.0, center + 2.66 * mr_bar)
    lnpl = max(0.0, center - 2.66 * mr_bar)
    pts = []
    for i, (l, v) in enumerate(series):
        sig = ''
        if v > unpl or v < lnpl:
            sig = 'outside limits'                                   # rule 1
        if not sig and i >= 7 and all((x - center) > 0 for _, x in series[i - 7:i + 1]):
            sig = 'sustained shift (8 above center)'                  # rule 2
        if not sig and i >= 7 and all((x - center) < 0 for _, x in series[i - 7:i + 1]):
            sig = 'sustained shift (8 below center)'
        if not sig and i >= 3:
            last4 = [x for _, x in series[i - 3:i + 1]]
            near = sum(1 for x in last4
                       if abs(x - center) > 0.5 * ((unpl - center) if x >= center else (center - lnpl)))
            if near >= 3:
                sig = 'moderate shift emerging'                       # rule 3
        pts.append({'label': l, 'value': v, 'signal': sig})
    return {'center': center, 'unpl': unpl, 'lnpl': lnpl, 'points': pts}


def build(t=None):
    """The full warehouse-efficiency bundle from one done-picking pull + side pulls.
    Pull window ~14 months so the weekly volume series has a same-week-last-year comparison."""
    t = t or P.today()
    y1 = (t - dt.timedelta(days=430)).isoformat()
    types = _types()
    done = _done_pickings(y1)
    open_ = _open_pickings()
    _y365 = (t - dt.timedelta(days=365)).isoformat()

    # ---- monthly throughput by flow (12m) ----------------------------------
    monthly = {}
    for r in done:
        if str(r['date_done'])[:10] < _y365:
            continue
        ti = types.get(O.m2o_id(r['picking_type_id']), {})
        code = ti.get('code') or '?'
        m = str(r['date_done'])[:7]
        key = {'incoming': 'Receipts', 'outgoing': 'Deliveries',
               'internal': 'Internal'}.get(code, code)
        if ti.get('name') == 'Returns':
            key = 'Returns'
        e = monthly.setdefault(m, {'month': m, 'Receipts': 0, 'Deliveries': 0,
                                   'Internal': 0, 'Returns': 0})
        e[key] = e.get(key, 0) + 1
    monthly_rows = sorted(monthly.values(), key=lambda x: x['month'])

    # ---- outbound funnel cycle times (main warehouse Pick -> Pack -> Ship, + Putaway) --------
    stages, stage_m = {}, {}
    for r in done:
        if str(r['date_done'])[:10] < _y365:
            continue
        ti = types.get(O.m2o_id(r['picking_type_id']), {})
        if not ti.get('stage'):
            continue
        cr, dd, sch = _dtp(r['date']), _dtp(r['date_done']), _dtp(r['scheduled_date'])
        if not (cr and dd):
            continue
        cyc = (dd - cr).total_seconds() / 86400
        s = stages.setdefault(ti['stage'], {'stage': ti['stage'], 'n': 0,
                                            'cycle': [], 'vs_sched': []})
        s['n'] += 1
        s['cycle'].append(cyc)
        if sch:
            s['vs_sched'].append((dd - sch).total_seconds() / 86400)
        stage_m.setdefault((ti['stage'], str(r['date_done'])[:7]), []).append(cyc)
    stage_rows = []
    for name in ('Pick', 'Pack', 'Ship', 'Putaway'):
        s = stages.get(name)
        if s:
            stage_rows.append({'stage': name, 'done_12m': s['n'],
                               'median_days': round(_median(s['cycle']) or 0, 2),
                               'p90_days': round(_p90(s['cycle']) or 0, 2),
                               'vs_sched_median': round(_median(s['vs_sched']) or 0, 2)})
    stage_monthly = sorted(
        ({'stage': k[0], 'month': k[1], 'median_days': round(_median(v) or 0, 2),
          'p90_days': round(_p90(v) or 0, 2), 'n': len(v)} for k, v in stage_m.items()),
        key=lambda x: (x['month'], x['stage']))

    # ---- on-time ship (outgoing): monthly + WEEKLY (XmR grain) + clean-order --
    y365 = (t - dt.timedelta(days=365)).isoformat()
    d90 = (t - dt.timedelta(days=90)).isoformat()
    ontime_m, weekly, clean_m, wk_vol = {}, {}, {}, {}
    ship_cycles = []
    clean90 = {'n': 0, 'clean': 0, 'late': 0, 'bo': 0}
    for r in done:
        ti = types.get(O.m2o_id(r['picking_type_id']), {})
        if ti.get('code') != 'outgoing':
            continue
        dd, sch, cr = _dtp(r['date_done']), _dtp(r['scheduled_date']), _dtp(r['date'])
        if not dd:
            continue
        dstr = str(r['date_done'])[:10]
        wk_key, wk_no = _iso_week(dd.date())
        wk_vol.setdefault(wk_key, {'week': wk_key, 'wk_no': wk_no, 'year': dd.year, 'n': 0})
        wk_vol[wk_key]['n'] += 1
        late = not (sch and (dd - sch).total_seconds() / 86400 <= ONTIME_GRACE_DAYS)
        bo = bool(r.get('backorder_id'))
        if dstr < y365:
            continue                       # older-than-12m rows only feed the LY weekly compare
        m = dstr[:7]
        e = ontime_m.setdefault(m, {'month': m, 'shipped': 0, 'on_time': 0,
                                    'backorders': 0, 'cycle': []})
        e['shipped'] += 1
        if not late:
            e['on_time'] += 1
        if bo:
            e['backorders'] += 1
        if cr:
            c = (dd - cr).total_seconds() / 86400
            e['cycle'].append(c)
            ship_cycles.append(c)
        w = weekly.setdefault(wk_key, {'week': wk_key, 'n': 0, 'on_time': 0})
        w['n'] += 1
        if not late:
            w['on_time'] += 1
        cm = clean_m.setdefault(m, {'month': m, 'On time & complete': 0, 'Late only': 0,
                                    'Backordered only': 0, 'Late + backordered': 0})
        cat = ('On time & complete' if not late and not bo else
               'Late only' if late and not bo else
               'Backordered only' if bo and not late else 'Late + backordered')
        cm[cat] += 1
        if dstr >= d90:
            clean90['n'] += 1
            clean90['clean'] += (0 if (late or bo) else 1)
            clean90['late'] += (1 if late else 0)
            clean90['bo'] += (1 if bo else 0)
    ontime_rows = []
    for m in sorted(ontime_m):
        e = ontime_m[m]
        ontime_rows.append({'month': m, 'shipped': e['shipped'],
                            'on_time_pct': e['on_time'] / e['shipped'] * 100 if e['shipped'] else None,
                            'median_days': round(_median(e['cycle']) or 0, 2),
                            'backorder_pct': e['backorders'] / e['shipped'] * 100 if e['shipped'] else None})
    n_out = sum(e['shipped'] for e in ontime_m.values())
    n_ontime = sum(e['on_time'] for e in ontime_m.values())

    # weekly on-time series (complete weeks only — drop the in-progress week) -> XmR
    cur_wk = _iso_week(t)[0]
    wk_series = [(k, weekly[k]['on_time'] / weekly[k]['n'] * 100)
                 for k in sorted(weekly) if k != cur_wk and weekly[k]['n'] >= 5]
    xmr = _xmr(wk_series[-52:])

    # weekly volume: last 13 complete weeks vs same ISO week last year
    vol_rows = []
    for k in sorted(wk_vol):
        v = wk_vol[k]
        if k == cur_wk or v['year'] < t.year - 1:
            continue
        if v['year'] == t.year:
            ly = next((x for x in wk_vol.values()
                       if x['wk_no'] == v['wk_no'] and x['year'] == v['year'] - 1), None)
            vol_rows.append({'week': f"W{v['wk_no']:02d}", 'shipped': v['n'],
                             'shipped_ly': ly['n'] if ly else 0})
    vol_rows = vol_rows[-13:]
    clean_rows = [clean_m[m] for m in sorted(clean_m)]
    clean_pct_90d = (clean90['clean'] / clean90['n'] * 100) if clean90['n'] else None

    # ---- backlog (open pickings, oldest first) + expedite list --------------
    today = dt.datetime.combine(t, dt.time())
    ship_med = _median([s for s in ship_cycles]) or 1.0
    bl, expedite = {}, []
    for r in open_:
        ti = types.get(O.m2o_id(r['picking_type_id']), {})
        label = f"{ti.get('wh', '?')}: {ti.get('name', '?')}"
        sch = _dtp(r['scheduled_date']) or _dtp(r['date'])
        age = (today - sch).days if sch else None
        e = bl.setdefault(label, {'queue': label, 'open': 0, 'overdue': 0, 'oldest_days': 0})
        e['open'] += 1
        if age is not None and age > 0:
            e['overdue'] += 1
            e['oldest_days'] = max(e['oldest_days'], age)
        # Expedite: outgoing, past scheduled+grace, waited longer than the normal ship cycle.
        # 'waiting' state = unreserved stock -> that's a PROCUREMENT gap, routed separately
        # (don't tell the warehouse to expedite what it cannot pick).
        if (ti.get('code') == 'outgoing' and age is not None
                and age > ONTIME_GRACE_DAYS and age > ship_med):
            expedite.append({'order': r.get('origin') or '', 'customer': O.m2o_name(r.get('partner_id')),
                             'queue': label, 'scheduled': str(r.get('scheduled_date') or '')[:10],
                             'days_late': age,
                             'blocked_on_stock': r.get('state') == 'waiting'})
    backlog_rows = sorted(bl.values(), key=lambda x: (-x['oldest_days'], -x['open']))
    expedite.sort(key=lambda x: -x['days_late'])

    n_done_12m = sum(1 for r in done if str(r['date_done'])[:10] >= _y365)
    return {
        'monthly': monthly_rows, 'stages': stage_rows, 'stage_monthly': stage_monthly,
        'ontime': ontime_rows, 'backlog': backlog_rows, 'expedite': expedite,
        'xmr': xmr, 'weekly_vol': vol_rows, 'clean': clean_rows,
        'clean_pct_90d': clean_pct_90d,
        'kpi': {
            'done_12m': n_done_12m, 'open_now': len(open_),
            'ontime_pct': (n_ontime / n_out * 100) if n_out else None,
            'ship_median_days': round(_median(ship_cycles) or 0, 2),
            'shipped_12m': n_out,
            'backlog_overdue': sum(e['overdue'] for e in bl.values()),
        },
        '_n_out_rowpath': n_out,
    }


def adjustments(t=None, top=15):
    """Inventory-adjustment moves (to/from an 'inventory'-usage location) = the shrinkage /
    correction signal. Valued from Odoo's stock.valuation.layer (ACTUAL historical cost) where a
    layer exists for the move; current standard cost only as fallback — upgraded 2026-07-05 from
    the all-approximation version. Monthly gains/losses + the most-corrected SKUs."""
    t = t or P.today()
    y1 = (t - dt.timedelta(days=365)).isoformat()
    gains = O.search_read('stock.move',
                          [('state', '=', 'done'), ('date', '>=', y1),
                           ('location_id.usage', '=', 'inventory')],
                          ['product_id', 'product_uom_qty', 'date'])
    losses = O.search_read('stock.move',
                           [('state', '=', 'done'), ('date', '>=', y1),
                            ('location_dest_id.usage', '=', 'inventory')],
                           ['product_id', 'product_uom_qty', 'date'])
    pids = list({O.m2o_id(r['product_id']) for r in gains + losses if r.get('product_id')})
    cost = {}
    for i in range(0, len(pids), 5000):
        for p in O.search_read('product.product',
                               [('id', 'in', pids[i:i + 5000]), ('active', 'in', [True, False])],
                               ['default_code', 'standard_price']):
            cost[p['id']] = {'code': (p.get('default_code') or '').strip() or f"#{p['id']}",
                             'cost': p.get('standard_price') or 0.0}

    # Actual valuation per move from stock.valuation.layer (Σ |value| per move; a move can carry
    # several layers). Moves WITH a layer row use it — even a legitimate 0; only layerless moves
    # fall back to qty × current standard cost.
    mids = [m['id'] for m in gains + losses]
    layer_val, layer_seen = {}, set()
    for i in range(0, len(mids), 5000):
        for l in O.search_read('stock.valuation.layer',
                               [('stock_move_id', 'in', mids[i:i + 5000])],
                               ['stock_move_id', 'value']):
            mid = O.m2o_id(l['stock_move_id'])
            layer_seen.add(mid)
            layer_val[mid] = layer_val.get(mid, 0.0) + abs(l.get('value') or 0.0)

    d90 = (t - dt.timedelta(days=90)).isoformat()
    monthly, per_sku = {}, {}
    n_layer = n_fallback = 0
    for rows, sign, key in ((gains, +1, 'gains'), (losses, -1, 'losses')):
        for r in rows:
            pid = O.m2o_id(r['product_id'])
            c = cost.get(pid, {'code': '?', 'cost': 0.0})
            if r['id'] in layer_seen:
                val = layer_val.get(r['id'], 0.0)
                n_layer += 1
            else:
                val = (r.get('product_uom_qty') or 0.0) * c['cost']
                n_fallback += 1
            m = str(r['date'])[:7]
            e = monthly.setdefault(m, {'month': m, 'gains': 0.0, 'losses': 0.0, 'moves': 0})
            e[key] += val
            e['moves'] += 1
            s = per_sku.setdefault(pid, {'sku': c['code'], 'product': O.m2o_name(r['product_id']),
                                         'net_qty': 0.0, 'net_value': 0.0, 'moves': 0,
                                         'moves_90d': 0, 'gross_value': 0.0})
            s['net_qty'] += sign * (r.get('product_uom_qty') or 0.0)
            s['net_value'] += sign * val
            s['gross_value'] += abs(val)
            s['moves'] += 1
            if str(r['date'])[:10] >= d90:
                s['moves_90d'] += 1
    monthly_rows = sorted(monthly.values(), key=lambda x: x['month'])
    all_sku = list(per_sku.values())
    top_rows = sorted(all_sku, key=lambda x: -abs(x['net_value']))[:top]
    # Pareto: cumulative share of gross correction value (the "N SKUs = 80% of corrections" view)
    by_gross = sorted(all_sku, key=lambda x: -x['gross_value'])
    tot_gross = sum(s['gross_value'] for s in by_gross) or 1.0
    run = 0.0
    pareto = []
    for s in by_gross[:30]:
        run += s['gross_value']
        pareto.append({'sku': s['sku'], 'gross_value': s['gross_value'],
                       'cum_pct': run / tot_gross * 100})
    # Worklists (exception-first: every flag terminates in an actionable list)
    cc_list = sorted((s for s in all_sku if s['moves_90d'] >= 3),
                     key=lambda x: (-x['moves_90d'], -abs(x['net_value'])))
    churn_list = sorted((s for s in all_sku
                         if s['gross_value'] > 500 and s['gross_value'] > 2 * abs(s['net_value'])),
                        key=lambda x: -x['gross_value'])
    return {'monthly': monthly_rows, 'top': top_rows, 'pareto': pareto,
            # FULL worklists — the UI paginates; a silent [:40] cap made the headline counts
            # unverifiable in-app (owner rule: every sum drills to its rows)
            'cycle_count': cc_list, 'churn': churn_list,
            'n_cycle_count': len(cc_list), 'n_churn': len(churn_list),
            'total_losses': sum(m['losses'] for m in monthly_rows),
            'total_gains': sum(m['gains'] for m in monthly_rows),
            'n_moves': sum(m['moves'] for m in monthly_rows),
            'n_layer_valued': n_layer, 'n_cost_fallback': n_fallback,
            '_rowsum_loss_qty': sum((r.get('product_uom_qty') or 0.0) for r in losses),
            '_rowsum_layer_abs': sum(layer_val.values()),
            '_y1': y1}


def touches(t=None, top=25):
    """Most-handled SKUs by done stock-move count — where warehouse labor concentrates.
    Carries default_code so the rows open the canonical SKU drawer (cross-module interop)."""
    t = t or P.today()
    y1 = (t - dt.timedelta(days=365)).isoformat()
    g = O.read_group('stock.move', [('state', '=', 'done'), ('date', '>=', y1)],
                     ['product_uom_qty:sum'], ['product_id'], lazy=False)
    rows = []
    total = 0
    for r in g:
        n = r.get('__count') or 0
        total += n
        if r.get('product_id'):
            rows.append({'pid': O.m2o_id(r['product_id']), 'product': O.m2o_name(r['product_id']),
                         'moves': n, 'units': r.get('product_uom_qty') or 0.0})
    rows.sort(key=lambda x: -x['moves'])
    rows = rows[:top]
    pids = [r['pid'] for r in rows]
    codes = {p['id']: (p.get('default_code') or '').strip()
             for p in O.search_read('product.product',
                                    [('id', 'in', pids), ('active', 'in', [True, False])],
                                    ['default_code'])}
    for r in rows:
        r['share_pct'] = r['moves'] / total * 100 if total else None
        r['code'] = codes.get(r['pid'], '')
    return {'top': rows, 'total_moves': total}


def _sop(diagnose, fix, verify, cadence):
    """Embedded runbook per flag: versioned IN CODE (like metric definitions — an SOP that drifts
    in a UI editor breaks the explainability contract). Rendered as a drill-down under the flag."""
    return {'diagnose': diagnose, 'fix': fix, 'verify': verify, 'cadence': cadence}


def flags(b, adj, ph=None):
    """The recommendation engine: explainable rules from the warehouse research brief
    (wiki/research/warehouse.md), each terminating in an action AND carrying its SOP drill-down.
    Three Wheeler rules on the weekly on-time XmR, stage bottleneck/stalled-subset, clean-order
    breach, backorder jump, backlog breach, capacity-ahead-of-season, adjustment worklists,
    counting-program health."""
    out = []

    if ph and ph.get('ratio_pct') is not None and ph['ratio_pct'] > 1:
        out.append({'sev': 'high',
                    'title': f"Corrections churn = {ph['ratio_pct']:.0f}% of inventory value per year "
                             "(the accepted band is 1%; world-class runs under 0.5%)",
                    'detail': f"Gross adjustments {ph['gross_12m']:,.0f} against ~{ph['inv_value_approx']:,.0f} "
                              "of stock (ratio uses current-standard-cost inventory value - an approximation "
                              "for this ratio only).",
                    'action': 'This is a counting-PROGRAM problem, not per-SKU noise. Until it heals, '
                              'quantity-based recommendations for flagged SKUs carry count-trust '
                              'warnings on the Procurement, Inventory and Assortment pages.',
                    'sop': _sop(
                        ['Confirm the churn is process, not shrink: the gains-vs-losses chart '
                         'below - roughly offsetting bars = counting/UoM/receiving errors.',
                         'The Pareto names where the value concentrates; the churn worklist '
                         'names the process suspects.'],
                        ['Stand up the weekly cycle-count ritual (worklist below, 10-20 SKUs/wk, '
                         'counter =/= approver, root-cause tag on every correction).',
                         'Fix the top-5 churn SKUs\' unit-of-measure/receiving practice first - '
                         'one UoM fix can kill hundreds of corrections.',
                         'Track this ratio monthly; it should fall as counts come clean.'],
                        ['Ratio trending toward 1%, then 0.5%; the cycle-count worklist shrinking '
                         'month over month.'],
                        'Monthly program review; weekly count ritual.')})

    sigs = [p for p in b['xmr']['points'][-8:] if p['signal']]
    if sigs:
        out.append({'sev': 'high', 'title': 'On-time shipping shows a real signal, not noise',
                    'detail': '; '.join(f"{p['label']}: {p['value']:.0f}% ({p['signal']})" for p in sigs[-3:]),
                    'action': 'Investigate those weeks specifically - carrier, holiday, stockout or '
                              'staffing. XmR limits say this is beyond routine variation.',
                    'sop': _sop(
                        ['Open the signal week(s) in Odoo: Inventory > Transfers, filter Delivery '
                         'Orders, Done, date_done in that week, then sort by scheduled date.',
                         'Classify the late ones: carrier pickup missed / short-staffed day / '
                         'stock not available (backorder created) / holiday cluster.',
                         'One cause should cover most of the week - a signal week is a COMMON '
                         'cause, not many little ones.'],
                        ['Carrier: escalate with the carrier rep, add a second pickup on peak days.',
                         'Staffing: move the pack-day roster; see the Weekly-volume chart for which '
                         'weekdays run hot.',
                         'Stock: the backorder share in the Clean-orders chart confirms it - work '
                         'the Procurement buy list, not the floor.'],
                        ['The next 2 weekly points fall back inside the band.',
                         'If 8+ points stay above/below center, re-baseline the chart (the process '
                         'genuinely changed).'],
                        'On signal only - do NOT investigate routine (in-band) weeks.')})

    if b.get('clean_pct_90d') is not None and b['clean_pct_90d'] < 90:
        out.append({'sev': 'high', 'title': f"Clean-order rate {b['clean_pct_90d']:.0f}% (90d) - below the 90% bar",
                    'detail': 'Clean = shipped on time AND complete (no backorder). Top-quartile B2B is 95%+.',
                    'action': 'Decompose in the clean-order chart: a late-driven gap is a warehouse/carrier '
                              'issue; a backorder-driven gap is availability - route to Procurement.',
                    'sop': _sop(
                        ['Read the Clean-orders chart: which slice eats the gap - Late only (gold), '
                         'Backordered only (gray), or both (red)?',
                         'Late-driven: pull the expedite list + the signal-week SOP above.',
                         'Backorder-driven: these SKUs stocked out at ship time - cross-reference '
                         'the Procurement buy list and the open-PO late chase list.'],
                        ['Late-driven: fix the floor/carrier cause (see the on-time SOP).',
                         'Backorder-driven: expedite the late POs for the affected SKUs (Procurement '
                         '> Late deliveries names the supplier and days late); raise reorder points '
                         'on repeat offenders.',
                         'Both: work availability first - a complete late order beats an on-time '
                         'partial for most wholesale customers.'],
                        ['Trailing-90d clean rate back over 90% (top-quartile bar: 95%).'],
                        'Weekly review until >90%, then monthly.')})

    pr = b.get('promise') or {}
    if pr.get('on_promise_pct') is not None and pr['on_promise_pct'] < 85:
        out.append({'sev': 'high' if pr['on_promise_pct'] < 70 else 'medium',
                    'title': f"Promise-date OTIF {pr['on_promise_pct']:.0f}% - customers get "
                             f"{len(pr['missed']):,} of {pr['n_measured']:,} promised orders late",
                    'detail': 'Delivered vs the commitment_date QUOTED to the customer (+1d '
                              f"grace); p90 lateness +{pr['late_p90']}d. The internal on-time "
                              'XmR can look healthy while promises are still missed - the '
                              'promise is set at order entry, not by the warehouse.',
                    'action': 'Fix the PROMISE first: quote commitment dates from the real '
                              'schedule (order-to-ship p50 + a buffer), then work the missed '
                              'list below.',
                    'sop': _sop(
                        ['Open the Promise vs delivered section: compare the promise-OTIF trend '
                         'to the internal on-time XmR - a wide gap = promises are quoted '
                         'tighter than the process can ship.',
                         'Sample 10 missed orders: was the commitment_date realistic at entry '
                         '(vs the median order-to-ship time), or did the floor slip?'],
                        ['Unrealistic promises -> set commitment dates at entry from the real '
                         'p50 ship time + 1 day buffer (sales SOP, not a warehouse fix).',
                         'Floor slippage -> the expedite list + backlog SOPs above own it.'],
                        ['Promise-OTIF trend back over 90% and converging with the internal '
                         'on-time line.'],
                        'Weekly with the on-time review.')})

    om = b['ontime']
    if len(om) >= 7:
        last_bo = om[-1]['backorder_pct'] or 0
        base_bo = sum((r['backorder_pct'] or 0) for r in om[-7:-1]) / 6
        if last_bo - base_bo > 5:
            out.append({'sev': 'medium', 'title': f"Backorder rate jumped to {last_bo:.0f}% (was ~{base_bo:.0f}%)",
                        'detail': 'First-pass fill is slipping - an availability problem, not a floor problem.',
                        'action': 'Cross-check the Procurement buy list and the open-PO late chase list.',
                        'sop': _sop(
                            ['List last month\'s backordered deliveries in Odoo (Delivery Orders '
                             'with a backorder) and tally the missing SKUs.',
                             'For each: is it on the Procurement BUY list (never ordered) or on the '
                             'open-PO LATE list (ordered, supplier late)?'],
                            ['Never ordered: place the PO - the buy list has qty and supplier.',
                             'Supplier late: expedite via the late chase list; consider the '
                             'alternative vendor shown on the Procurement page.',
                             'Chronic repeat SKUs: raise the reorder baseline (lead-time cover).'],
                            ['Backorder share back within 5pts of its 6-month norm.'],
                            'Check at the biweekly procurement ritual.')})

    sm = b['stage_monthly']
    for stage in ('Pick', 'Pack', 'Ship', 'Putaway'):
        hist = [r for r in sm if r['stage'] == stage]
        if len(hist) >= 7:
            last = hist[-1]
            base = sorted(r['p90_days'] for r in hist[-7:-1])[3]   # median of prior 6
            if base > 0 and last['p90_days'] > 2 * base:
                out.append({'sev': 'medium', 'title': f"{stage} p90 cycle {last['p90_days']:.1f}d - over 2x its 6-month norm ({base:.1f}d)",
                            'detail': f"Month {last['month']}, n={last['n']}.",
                            'action': f'Bottleneck forming at {stage} - rebalance labor or clear holds there.',
                            'sop': _sop(
                                [f'Open the {stage} queue in Odoo, sort oldest first: is the tail a '
                                 'few ancient transfers (holds) or is the WHOLE queue slower (capacity)?',
                                 'Compare the stage trend chart: did volume also jump that month?'],
                                ['Holds: clear or cancel the stuck transfers (supervisor sign-off).',
                                 'Capacity: shift labor from the fastest stage for 1-2 weeks; the '
                                 'stage chart shows which stage has slack.'],
                                [f'{stage} p90 back under 2x its 6-month norm next month.'],
                                'Monthly, at the stage-trend review.')})
    for s in b['stages']:
        if s['median_days'] > 0 and s['p90_days'] / max(s['median_days'], 0.01) > 4 and s['p90_days'] > 1:
            out.append({'sev': 'info', 'title': f"{s['stage']}: a subset of transfers stalls (p90 {s['p90_days']:.1f}d vs median {s['median_days']:.1f}d)",
                        'detail': 'A long tail this wide is usually holds or stockouts, not capacity.',
                        'action': 'Audit the stalled tail - the expedite list below names the current ones.',
                        'sop': _sop(
                            [f'The median {s["stage"]} transfer takes {s["median_days"]:.1f}d - the slowest '
                             '10% take 4x+ longer. Sample 10 of the slowest done transfers this month '
                             '(Odoo: sort by date_done minus create date).',
                             'Tag each: waiting on stock / waiting on a person or approval / lost paperwork.'],
                            ['Stock waits: those belong to Procurement (see the stock-blocked flag).',
                             'Approval waits: name the approval and set a same-day rule for it.',
                             'Lost paperwork: cancel-and-recreate is usually cheaper than archaeology.'],
                            ['p90/median ratio trending toward 2-3x within two months.'],
                            'Monthly sample of 10.')})

    k = b['kpi']
    if k['open_now'] and k['backlog_overdue'] / k['open_now'] > 0.10:
        out.append({'sev': 'high', 'title': f"{k['backlog_overdue']:,} of {k['open_now']:,} open transfers are past schedule "
                                            f"({k['backlog_overdue'] / k['open_now'] * 100:.0f}% - the healthy bar is under 10%)",
                    'detail': 'Old queues (see Backlog) almost always contain dead paperwork as well as real work.',
                    'action': 'Clear or cancel the ancient queues (Putaway/Move Back to Stock first); '
                              'then work the expedite list daily until past-due is under 5%.',
                    'sop': _sop(
                        ['Backlog table below, oldest queue first: anything older than ~60 days is '
                         'almost certainly dead paperwork, not real work.',
                         'For each ancient queue, sample 10 transfers in Odoo: does the stock '
                         'movement it describes still need to happen?'],
                        ['Dead paperwork: CANCEL in Odoo (ops supervisor does it - this app is '
                         'read-only by design). One focused afternoon usually clears years.',
                         'Real work: schedule a catch-up block per queue (Putaway first - it '
                         'poisons on-hand accuracy, which poisons the buy list).',
                         'Then: the expedite list is the daily ritual until past-due < 5%.'],
                        ['Past-due share under 10% within a month, 5% steady-state; oldest-days '
                         'column under 30 everywhere.'],
                        'One-time cleanup, then daily expedite ritual + weekly backlog glance.')})

    vol = b['weekly_vol']
    if len(vol) >= 4:
        now4 = sum(r['shipped'] for r in vol[-4:])
        ly4 = sum(r['shipped_ly'] for r in vol[-4:])
        p90_rising = False
        ship_hist = [r for r in sm if r['stage'] == 'Ship']
        if len(ship_hist) >= 7:
            p90_rising = ship_hist[-1]['p90_days'] > sorted(r['p90_days'] for r in ship_hist[-7:-1])[3]
        if ly4 and now4 / ly4 > 1.15 and p90_rising:
            out.append({'sev': 'medium', 'title': f'Volume running {now4 / ly4 * 100 - 100:.0f}% above last year AND ship times rising',
                        'detail': f'Last 4 weeks: {now4:,} shipped vs {ly4:,} same weeks last year.',
                        'action': 'Capacity signal - open the staffing conversation now (practice: hire '
                                  '8-12 weeks before peak).',
                        'sop': _sop(
                            ['Weekly-volume chart: how many weeks until the seasonal peak (last '
                             'year\'s shape shows it)?',
                             'Estimate the gap: volume % above LY ~= extra hands needed % (floor '
                             'labor scales near-linearly at this size).'],
                            ['Under 12 weeks to peak: start temp hiring NOW (8-12 week practice; '
                             '12-14 in a tight market).',
                             'Over 12 weeks: schedule the decision, prep onboarding docs.',
                             'Short-term relief: shift labor to the bottleneck stage (stage trend '
                             'chart names it).'],
                            ['Ship p90 flat while volume grows; on-time XmR stays in band through '
                             'the peak.'],
                            'Re-check weekly during the run-up.')})

    if adj['n_cycle_count']:
        out.append({'sev': 'medium', 'title': f"{adj['n_cycle_count']} SKUs adjusted 3+ times in 90 days",
                    'detail': 'Repeat corrections = the system count for these is not trusted.',
                    'action': 'Work the cycle-count list below weekly until each SKU has two clean counts.',
                    'sop': _sop(
                        ['Print the cycle-count worklist (top of the list first - it is ranked by '
                         'repeat count then value).',
                         'For each SKU note WHERE the count went wrong last time (the SKU drawer '
                         'shows its movement; the churn list says if it is a process error).'],
                        ['Count 10-20 SKUs per week from the top of the list.',
                         'Counter and approver are DIFFERENT people (segregation - the practice '
                         'that keeps counts honest).',
                         'Tag every correction with a root cause (miscount / damage / receiving '
                         'error / UoM) - the tags are what fix the process.',
                         'A SKU exits the list after TWO consecutive clean counts.'],
                        ['Worklist shrinking month over month; adjustment gross $ trending down '
                         'on the monthly chart.'],
                        'Weekly, 10-20 SKUs; the list re-ranks itself as data updates.')})
    if adj['n_churn']:
        out.append({'sev': 'medium', 'title': f"{adj['n_churn']} SKUs churn both ways (gross corrections > 2x net)",
                    'detail': 'Offsetting gains and losses = a counting/receiving/UoM process error, not shrink.',
                    'action': 'Check unit-of-measure, receiving and put-away practice for the churn list below.',
                    'sop': _sop(
                        ['Take the top 5 churn SKUs: open each in the SKU drawer and note the pack '
                         'size (e.g. "288-Piece per Pack" - candles are classic UoM churn).',
                         'Ask receiving: are these counted in PIECES or PACKS on arrival? Ask the '
                         'floor: which unit does picking use?'],
                        ['UoM mismatch found: fix the unit on the product / retrain the count '
                         'habit - one setting usually kills hundreds of corrections.',
                         'Put-away confusion (same SKU in several spots): consolidate locations.',
                         'Receiving misses: count at the dock against the PO line, not from the '
                         'packing slip.'],
                        ['The SKU stops appearing in the churn list within two cycles; gross '
                         'corrections fall while net stays flat.'],
                        'Top-5 review at the weekly count session.')})

    real_exp = [e for e in b['expedite'] if not e['blocked_on_stock']]
    stock_exp = [e for e in b['expedite'] if e['blocked_on_stock']]
    if real_exp:
        out.append({'sev': 'medium', 'title': f'{len(real_exp)} shipments to expedite today',
                    'detail': 'Past schedule and older than the normal ship cycle, stock available.',
                    'action': 'Expedite list below, worst first.',
                    'sop': _sop(
                        ['Expedite list below, top rows first (worst days-late).',
                         'For each: confirm in Odoo the stock is actually reserved (assigned '
                         'state) and the order is not on customer hold.'],
                        ['Pick-pack-ship the top of the list TODAY; anything a customer has '
                         'called about jumps the queue.',
                         'On hold / no longer wanted: cancel the transfer so it stops polluting '
                         'the backlog stats.'],
                        ['Expedite list under 10 rows and oldest under 7 days late.'],
                        'Daily, first thing - it is a 15-minute ritual once the backlog is clean.')})
    if stock_exp:
        out.append({'sev': 'info', 'title': f'{len(stock_exp)} late shipments are blocked on STOCK, not the floor',
                    'detail': 'Waiting-state transfers cannot be picked - expediting them is pointless.',
                    'action': 'Routed to Procurement: cross-check the buy list / open-PO ETAs for these.',
                    'sop': _sop(
                        ['Filter the expedite list to "Blocked on stock = YES" - these SKUs are '
                         'sold but not on the shelf.',
                         'Procurement page: is each SKU on an open PO (check the inbound-matched '
                         'table for its ETA) or not ordered at all?'],
                        ['On a PO: tell the customer the ETA; expedite the PO if the customer '
                         'matters (late chase list has the supplier contact context).',
                         'Not ordered: it should be flashing on the buy list - order it or offer '
                         'the customer a substitute.'],
                        ['Stock-blocked count trending to near zero outside deep season.'],
                        'Reviewed at the biweekly procurement ritual.')})

    sev_rank = {'high': 0, 'medium': 1, 'info': 2}
    out.sort(key=lambda f: sev_rank.get(f['sev'], 9))
    return out


def untrusted_counts(t=None):
    """The cross-module COUNT-TRUST set: default_code -> adjustment count for SKUs adjusted 3+
    times in the last 90 days. For these, the system quantity is unverified until two clean
    cycle counts — any recommendation built on their on-hand (buy list cover, dead-stock DSI,
    season readiness) should say so. Deliberately lightweight (grouped reads over 90 days) so
    any page can join it without running the full adjustments()."""
    t = t or P.today()
    d90 = (t - dt.timedelta(days=90)).isoformat()
    per = {}
    for loc in ('location_id', 'location_dest_id'):
        for g in O.read_group('stock.move',
                              [('state', '=', 'done'), ('date', '>=', d90),
                               (loc + '.usage', '=', 'inventory')],
                              ['id'], ['product_id'], lazy=False):
            if g.get('product_id'):
                pid = O.m2o_id(g['product_id'])
                per[pid] = per.get(pid, 0) + (g.get('__count') or 0)
    pids = [p for p, n in per.items() if n >= 3]
    codes = {}
    for i in range(0, len(pids), 5000):
        for p in O.search_read('product.product',
                               [('id', 'in', pids[i:i + 5000]), ('active', 'in', [True, False])],
                               ['default_code']):
            c = (p.get('default_code') or '').strip()
            if c:
                codes[c] = per[p['id']]
    return codes


def program_health(adj):
    """Counting-program health: gross corrections (12m, layer-valued) as a share of inventory
    value. Inventory value here is qty x CURRENT standard cost — an approximation used only for
    this RATIO (the GL-tied book value lives in Overstock). WERC practice band: <=0.5% target,
    ~1% acceptable, beyond that = formal program review."""
    prods = O.search_read('product.product',
                          [('default_code', '!=', False), ('active', 'in', [True, False])],
                          ['default_code', 'name', 'qty_available', 'standard_price'])
    valued = [{'code': (p.get('default_code') or '').strip(), 'product': p.get('name') or '',
               'qty': p.get('qty_available') or 0.0, 'std_cost': p.get('standard_price') or 0.0,
               'value': (p.get('qty_available') or 0.0) * (p.get('standard_price') or 0.0)}
              for p in prods if (p.get('qty_available') or 0.0) > 0]
    inv_val = sum(v['value'] for v in valued)
    gross = adj['total_losses'] + adj['total_gains']
    # denominator composition, so the ratio is verifiable in-app down to SKUs (owner rule);
    # the numerator's rows are the adjustment worklists/Pareto on the same page
    valued.sort(key=lambda v: -v['value'])
    return {'inv_value_approx': inv_val, 'gross_12m': gross,
            'ratio_pct': (gross / inv_val * 100) if inv_val else None,
            'n_skus_valued': len(valued), 'value_rows': valued}


def freight(t=None):
    """Carrier mix + delivery-charge economics: who we ship with, what delivery revenue we
    charge, and who rides the free-delivery carrier (the coverage question: free delivery for a
    customer we then pay Uber/local fees to serve)."""
    t = t or P.today()
    y1 = (t - dt.timedelta(days=365)).isoformat()
    mix = []
    for g in O.read_group('stock.picking',
                          [('state', '=', 'done'), ('date_done', '>=', y1),
                           ('carrier_id', '!=', False)],
                          ['id'], ['carrier_id'], lazy=False):
        if g.get('carrier_id'):
            mix.append({'carrier': O.m2o_name(g['carrier_id']), 'pickings': g.get('__count') or 0})
    mix.sort(key=lambda x: -x['pickings'])
    tot_pk = sum(m['pickings'] for m in mix) or 1
    for m in mix:
        m['share_pct'] = m['pickings'] / tot_pk * 100

    ex = O.excluded_partner_ids()
    dom = [('is_delivery', '=', True), ('order_id.state', 'in', ['sale', 'done']),
           ('order_id.date_order', '>=', y1)]
    if ex:
        dom.append(('order_partner_id', 'not in', list(ex)))
    lines = O.search_read('sale.order.line', dom,
                          ['price_subtotal', 'order_partner_id', 'create_date'])
    monthly = {}
    for l in lines:
        m = str(l.get('create_date') or '')[:7]
        e = monthly.setdefault(m, {'month': m, 'revenue': 0.0, 'lines': 0})
        e['revenue'] += l.get('price_subtotal') or 0.0
        e['lines'] += 1
    rev_total = sum(l.get('price_subtotal') or 0.0 for l in lines)

    free = []
    for g in O.read_group('stock.picking',
                          [('state', '=', 'done'), ('date_done', '>=', y1),
                           ('carrier_id.name', 'ilike', 'free')],
                          ['id'], ['partner_id'], lazy=False):
        if g.get('partner_id'):
            free.append({'pid': O.m2o_id(g['partner_id']), 'customer': O.m2o_name(g['partner_id']),
                         'free_deliveries': g.get('__count') or 0})
    free.sort(key=lambda x: -x['free_deliveries'])

    return {'mix': mix, 'monthly': sorted(monthly.values(), key=lambda x: x['month']),
            'revenue_12m': rev_total, 'n_charge_lines': len(lines),
            'free_top': free[:20], 'n_free_customers': len(free),
            'n_free_deliveries': sum(f['free_deliveries'] for f in free),
            '_rowsum_rev': rev_total, '_dom_is_delivery': dom}


def promise_otif(t=None):
    """Delivered vs PROMISED — the CUSTOMER's on-time, distinct from the internal schedule the
    on-time XmR measures. 94% of confirmed orders carry a commitment_date; an order is
    on-promise when its LAST completed outgoing shipment finished by that date +1 day (the same
    grace the internal measure uses). Measured set = promised orders that HAVE shipped."""
    t = t or P.today()
    d12 = (t - dt.timedelta(days=365)).isoformat()
    orders = O.search_read('sale.order',
                           [('state', '=', 'sale'), ('date_order', '>=', d12),
                            ('commitment_date', '!=', False)],
                           ['name', 'partner_id', 'commitment_date', 'date_order'])
    picks = O.search_read('stock.picking',
                          [('picking_type_code', '=', 'outgoing'), ('state', '=', 'done'),
                           ('date_done', '>=', d12)],
                          ['origin', 'date_done'])
    last_done = {}
    for p_ in picks:
        o_, d = p_.get('origin'), str(p_.get('date_done') or '')[:10]
        if o_ and d and (o_ not in last_done or d > last_done[o_]):
            last_done[o_] = d
    rows = []
    for so in orders:
        dd = last_done.get(so['name'])
        if not dd:
            continue
        promised = str(so['commitment_date'])[:10]
        rows.append({'order': so['name'], 'pid': O.m2o_id(so.get('partner_id')),
                     'customer': O.m2o_name(so.get('partner_id')),
                     'promised': promised, 'delivered': dd,
                     'days_late': (dt.date.fromisoformat(dd)
                                   - dt.date.fromisoformat(promised)).days})
    n = len(rows)
    ontime = sum(1 for r in rows if r['days_late'] <= 1)
    lates = sorted(r['days_late'] for r in rows)
    per_m = {}
    for r in rows:
        e = per_m.setdefault(r['promised'][:7], [0, 0])
        e[0] += 1
        e[1] += 1 if r['days_late'] <= 1 else 0
    return {'n_measured': n, 'n_promised': len(orders), 'n_unshipped': len(orders) - n,
            'n_on_promise': ontime,
            'on_promise_pct': (ontime / n * 100) if n else None,
            'late_p90': lates[int(len(lates) * .9)] if lates else None,
            'missed': sorted((r for r in rows if r['days_late'] > 1),
                             key=lambda r: -r['days_late']),
            # trend only for months with a real sample — orders PROMISED before the pull window
            # can ship inside it and leave a 1-2 order month that drags the line to 0/100%
            'trend': [{'month': k, 'measured': v[0],
                       'on_promise_pct': v[1] / v[0] * 100}
                      for k, v in sorted(per_m.items()) if v[0] >= 10],
            '_d12': d12}


def summary(t=None):
    t = t or P.today()
    b = build(t)
    b['adjustments'] = adjustments(t)
    b['touches'] = touches(t)
    b['freight'] = freight(t)
    b['program_health'] = program_health(b['adjustments'])
    b['promise'] = promise_otif(t)
    b['flags'] = flags(b, b['adjustments'], b['program_health'])
    b['pulled_at'] = dt.datetime.now().strftime('%Y-%m-%d %H:%M')
    return b


def validate(t=None, team_id=None, pre=None, adj=None, fr=None):
    """pre/adj/fr: pass the bundle's precomputed build()/adjustments()/freight() to avoid
    re-running the heavy pulls (the ar.py lesson); validate.py calls bare and computes fresh."""
    t = t or P.today()
    y1 = (t - dt.timedelta(days=365)).isoformat()
    o = O.get_odoo()
    b = pre or build(t)
    checks = []

    n_out_direct = o.search_count('stock.picking',
                                  [('state', '=', 'done'), ('date_done', '>=', y1),
                                   ('picking_type_code', '=', 'outgoing')])
    checks.append({'check': 'Warehouse: row-built outgoing done == search_count (12m)',
                   'a': b['_n_out_rowpath'], 'b': n_out_direct,
                   'gap': b['_n_out_rowpath'] - n_out_direct,
                   'ok': abs(b['_n_out_rowpath'] - n_out_direct) <= max(2, n_out_direct * 0.001)})

    pr = b.get('promise') or promise_otif(t)
    if pr:
        n_srv = o.search_count('sale.order',
                               [('state', '=', 'sale'), ('date_order', '>=', pr['_d12']),
                                ('commitment_date', '!=', False)])
        checks.append({'check': 'Promise-OTIF: promised-order universe == server count',
                       'a': pr['n_promised'], 'b': n_srv, 'gap': pr['n_promised'] - n_srv,
                       'ok': abs(pr['n_promised'] - n_srv) <= max(2, n_srv * 0.001)})
        part = pr['n_on_promise'] + len(pr['missed'])
        checks.append({'check': 'Promise-OTIF: on-promise + missed == measured (partition)',
                       'a': part, 'b': pr['n_measured'], 'gap': part - pr['n_measured'],
                       'ok': part == pr['n_measured']})

    mm = sum(sum(v for k, v in m.items() if k != 'month') for m in b['monthly'])
    n_all = o.search_count('stock.picking', [('state', '=', 'done'), ('date_done', '>=', y1)])
    checks.append({'check': 'Warehouse: Σ(monthly throughput) == all done pickings (12m)',
                   'a': mm, 'b': n_all, 'gap': mm - n_all,
                   'ok': abs(mm - n_all) <= max(2, n_all * 0.001)})

    # Clean-order decomposition is exhaustive: the four categories partition every shipped picking.
    clean_sum = sum(sum(v for kk, v in m.items() if kk != 'month') for m in b['clean'])
    ship_sum = sum(r['shipped'] for r in b['ontime'])
    checks.append({'check': 'Warehouse: Σ(clean-order categories) == Σ shipped (partition test)',
                   'a': clean_sum, 'b': ship_sum, 'gap': clean_sum - ship_sum,
                   'ok': clean_sum == ship_sum})

    adj = adj or adjustments(t)
    agg_loss_qty = O.sum_field('stock.move',
                               [('state', '=', 'done'), ('date', '>=', adj['_y1']),
                                ('location_dest_id.usage', '=', 'inventory')],
                               'product_uom_qty')
    checks.append({'check': 'Warehouse: adjustment loss qty row-sum == read_group Σ (live-drift tol)',
                   'a': round(adj['_rowsum_loss_qty'], 2), 'b': round(agg_loss_qty, 2),
                   'gap': round(adj['_rowsum_loss_qty'] - agg_loss_qty, 2),
                   'ok': abs(adj['_rowsum_loss_qty'] - agg_loss_qty) <= max(1.0, abs(agg_loss_qty) * 0.001)})

    # Valuation layers: our per-move Σ|value| vs the signed read_group sums over the same layer
    # domains (gains positive, losses negative → |gains| + |losses| should tie).
    lay_g = O.sum_field('stock.valuation.layer',
                        [('stock_move_id.state', '=', 'done'), ('stock_move_id.date', '>=', adj['_y1']),
                         ('stock_move_id.location_id.usage', '=', 'inventory')], 'value')
    lay_l = O.sum_field('stock.valuation.layer',
                        [('stock_move_id.state', '=', 'done'), ('stock_move_id.date', '>=', adj['_y1']),
                         ('stock_move_id.location_dest_id.usage', '=', 'inventory')], 'value')
    lay_agg = abs(lay_g) + abs(lay_l)
    checks.append({'check': 'Warehouse: Σ|layer value| row-path == |read_group gains|+|losses| (tol)',
                   'a': round(adj['_rowsum_layer_abs'], 2), 'b': round(lay_agg, 2),
                   'gap': round(adj['_rowsum_layer_abs'] - lay_agg, 2),
                   'ok': abs(adj['_rowsum_layer_abs'] - lay_agg) <= max(5.0, lay_agg * 0.005)})

    # Freight: delivery-charge revenue row-sum vs an independent read_group sum, same domain.
    fr = fr or freight(t)
    agg_rev = O.sum_field('sale.order.line', fr['_dom_is_delivery'], 'price_subtotal')
    checks.append({'check': 'Warehouse: Σ delivery-charge revenue row-sum == read_group Σ (tol)',
                   'a': round(fr['_rowsum_rev'], 2), 'b': round(agg_rev, 2),
                   'gap': round(fr['_rowsum_rev'] - agg_rev, 2),
                   'ok': abs(fr['_rowsum_rev'] - agg_rev) <= max(1.0, abs(agg_rev) * 0.001)})
    return checks