platform/modules/procurement.py

"""Procurement module — biweekly buy list + the full Qualify-tab workflow, rebuilt from the Royal
Imports Inventory System (v24/v25).

Master data (Supplier / Country / Lead / CBM / container / first cost) comes from the curated map
`procurement_suppliers.json` (~3,960 SKUs from the Inventory System Mastersheet). The team's live
edits (change vendor/country/cost) are overlaid in the UI from a `procurement_overrides` store, so a
re-vendored SKU regroups instantly without re-pulling Odoo. Alternative vendors + prices come from
Odoo `product.supplierinfo` (the cheapest-vendor view). Demand / on-hand / incoming POs are live
from Odoo (read-only).

Reorder math (validated on the v24.4 Backend):
  8-month demand = units sold same 8-month window last year (all channels)
  avg/day        = 8M demand / window days
  days of cover  = (on-hand + incoming PO) / avg/day
  reorder point  = lead-time days * avg/day
  Reorder QTY    = ceil(reorder point * 1.05)                       [Baseline]
  Reorder +trend = Reorder QTY * (YTD units this yr / last yr)
  BUY TODAY      = days of cover < lead time
READ-ONLY on Odoo; the team's Order Qty + master-data edits persist in the platform store.
"""
import sys
import json
import math
import calendar
import datetime as dt
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from functools import lru_cache
import core.odoo as O
import core.periods as P

_HERE = Path(__file__).resolve().parent
_SAFETY = 0.05
_PROJ_MONTHS = 8
_NO_SVC = [('product_id.type', '!=', 'service')]


@lru_cache(maxsize=1)
def suppliers():
    """SKU(default_code) -> {desc, unit, qty_per_unit, first_cost, cbm, max_container, vendor,
    country, lead} from the curated map."""
    f = _HERE.parent / 'procurement_suppliers.json'
    try:
        return json.loads(f.read_text(encoding='utf-8'))
    except Exception:
        return {}


def _add_months(d, n):
    m = d.month - 1 + n
    y = d.year + m // 12
    m = m % 12 + 1
    return dt.date(y, m, min(d.day, calendar.monthrange(y, m)[1]))


def _proj_window(t):
    return t, _add_months(t, _PROJ_MONTHS)


def _demand_domain(a, b):
    return [('order_id.state', 'in', ('sale', 'done')), ('order_id.date_order', '>=', a),
            ('order_id.date_order', '<=', b)] + _NO_SVC


def _units(a, b):
    g = O.read_group('sale.order.line', _demand_domain(a, b), ['product_uom_qty:sum'],
                     ['product_id'], lazy=False)
    return {O.m2o_id(r['product_id']): (r.get('product_uom_qty') or 0.0)
            for r in g if r.get('product_id')}


def _incoming_po():
    out = {}
    for r in O.read_group('purchase.order.line', [('state', '=', 'purchase')],
                          ['product_qty:sum', 'qty_received:sum'], ['product_id'], lazy=False):
        pid = O.m2o_id(r.get('product_id'))
        q = (r.get('product_qty') or 0.0) - (r.get('qty_received') or 0.0)
        if pid and q > 0:
            out[pid] = q
    return out


def _bought_12m(cutoff_iso):
    """Units PURCHASED from suppliers in the last 12 months (confirmed POs), per product — the
    reference for 'how much we actually bought last year' next to the projected need."""
    out = {}
    for r in O.read_group('purchase.order.line',
                          [('order_id.state', 'in', ('purchase', 'done')),
                           ('order_id.date_order', '>=', cutoff_iso)],
                          ['product_qty:sum'], ['product_id'], lazy=False):
        pid = O.m2o_id(r.get('product_id'))
        if pid:
            out[pid] = r.get('product_qty') or 0.0
    return out


def _supplierinfo_by_tmpl():
    """{template_id: [{'vendor','vendor_id','price'}...]} — Odoo's vendors per product, for the
    multiple-vendor / cheapest-price views (Odoo prices are populated; lead times are not)."""
    out = {}
    for s in O.search_read('product.supplierinfo', [], ['product_tmpl_id', 'partner_id', 'price'],
                           limit=100000):
        t = O.m2o_id(s.get('product_tmpl_id'))
        if not t:
            continue
        out.setdefault(t, []).append({'vendor': O.m2o_name(s.get('partner_id')),
                                      'vendor_id': O.m2o_id(s.get('partner_id')),
                                      'price': s.get('price') or 0.0})
    return out


def _preorders(cutoff_iso):
    """Per-product open customer demand (undelivered units on confirmed orders) + earliest need-by
    date. STORED fields only — `qty_to_deliver` is a computed field and filtering/aggregating it forces
    Odoo to compute across the whole line population (8+ min); instead sum the stored
    `product_uom_qty - qty_delivered`. Bounded to orders since `cutoff_iso` so ancient never-closed
    backorders don't dominate the figures. Returns ({pid: qty}, {pid: 'YYYY-MM-DD'})."""
    dom = [('order_id.state', '=', 'sale'), ('order_id.date_order', '>=', cutoff_iso)]
    qty = {}
    for r in O.read_group('sale.order.line', dom, ['product_uom_qty:sum', 'qty_delivered:sum'],
                          ['product_id'], lazy=False):
        pid = O.m2o_id(r.get('product_id'))
        d = (r.get('product_uom_qty') or 0.0) - (r.get('qty_delivered') or 0.0)
        if pid and d > 0:
            qty[pid] = d
    # need-by: only never-shipped lines (qty_delivered is STORED, so this filter is cheap) AND only
    # for products that actually have open qty — need_by is consumed ONLY when preorder_qty>0 (pid in
    # qty), so scoping the pull to those product_ids is both correct and much faster (~849 of ~2,870
    # products → the search drops from ~8.4s to ~5s; the other ~2,000 products' rows were pure waste).
    open_pids = list(qty)
    if not open_pids:
        return qty, {}
    lines = O.search_read('sale.order.line',
                          dom + [('qty_delivered', '=', 0), ('product_id', 'in', open_pids)],
                          ['product_id', 'order_id'], limit=400000)
    odate = {}
    oids = list({O.m2o_id(l['order_id']) for l in lines if l.get('order_id')})
    for o in O.search_read('sale.order', [('id', 'in', oids)], ['commitment_date', 'date_order']):
        d = o.get('commitment_date') or o.get('date_order')
        if d:
            odate[o['id']] = str(d)[:10]
    need = {}
    for l in lines:
        pid, oid = O.m2o_id(l['product_id']), O.m2o_id(l['order_id'])
        d = odate.get(oid)
        if pid and d and (pid not in need or d < need[pid]):
            need[pid] = d
    return qty, need


def recommendations(t=None):
    """Base per-SKU table (curated master data + live Odoo demand/stock/PO + alt-vendors). The UI
    overlays the team's vendor/country/cost edits on top; reorder methods + CBM/container are here."""
    t = t or P.today()
    sup = suppliers()
    if not sup:
        return []
    codeset = set(sup.keys())
    ps, pe = _proj_window(t)
    window_days = max(1, (pe - ps).days)
    d8a, d8b = _add_months(t, -12).isoformat(), _add_months(pe, -12).isoformat()
    yf, yt = P.ytd(t)
    lf, lt = P.ytd_last_year(t)
    prods, demand8, ytd_now, ytd_prev, inpo, si, preorders, bought = O.parallel([
        lambda: O.search_read('product.product',
                              [('default_code', '!=', False), ('active', 'in', [True, False])],
                              ['default_code', 'name', 'qty_available', 'standard_price', 'product_tmpl_id']),
        lambda: _units(d8a, d8b),
        lambda: _units(yf, yt),
        lambda: _units(lf, lt),
        _incoming_po,
        _supplierinfo_by_tmpl,
        lambda: _preorders(d8a),
        lambda: _bought_12m(d8a),
    ])
    pre_qty, pre_need = preorders
    today_iso = t.isoformat()
    by_code = {}
    for p in prods:
        c = (p.get('default_code') or '').strip()
        if c and c in codeset and c not in by_code:
            by_code[c] = p
    if not by_code:
        return []

    rows = []
    for code, p in by_code.items():
        pid = p['id']
        meta = sup.get(code, {})
        lead = meta.get('lead') or 0
        tmpl = O.m2o_id(p.get('product_tmpl_id'))
        alts = si.get(tmpl, [])
        priced = sorted((a for a in alts if (a.get('price') or 0) > 0), key=lambda a: a['price'])
        cheapest = priced[0] if priced else None
        cost = (meta.get('first_cost') or (cheapest['price'] if cheapest else None)
                or p.get('standard_price') or 0.0)
        d8 = demand8.get(pid, 0.0)
        avg_day = d8 / window_days if window_days else 0.0
        on_hand = p.get('qty_available') or 0.0
        po = inpo.get(pid, 0.0)
        avail = on_hand + po
        days_cover = (avail / avg_day) if avg_day > 0 else (None if avail <= 0 else 9999.0)
        reorder_pt = lead * avg_day
        rbase = math.ceil(reorder_pt * (1 + _SAFETY)) if reorder_pt > 0 else 0
        yn, yp = ytd_now.get(pid, 0.0), ytd_prev.get(pid, 0.0)
        trend = (yn / yp) if yp > 0 else None
        rtrend = int(round(rbase * trend)) if trend is not None else rbase
        buy = (days_cover is not None and lead > 0 and avg_day > 0 and days_cover < lead)
        rows.append({
            'code': code, 'product': meta.get('desc') or p.get('name') or code,
            'unit': meta.get('unit') or '', 'qty_per_unit': meta.get('qty_per_unit'),
            'vendor': meta.get('vendor') or '(no supplier)', 'country': meta.get('country') or '',
            'lead': lead, 'on_hand': on_hand, 'incoming_po': po, 'demand_8m': d8, 'avg_day': avg_day,
            'bought_12m': bought.get(pid, 0.0),
            'days_cover': days_cover, 'reorder_baseline': rbase, 'reorder_trend': rtrend,
            'trend_pct': ((trend - 1) * 100) if trend is not None else None,
            'ytd_units': yn, 'ytd_units_ly': yp, 'unit_cost': cost, 'buy': buy,
            'cbm': meta.get('cbm'), 'max_container': meta.get('max_container'),
            'preorder_qty': pre_qty.get(pid, 0.0),
            'need_by': pre_need.get(pid) if pre_qty.get(pid, 0) > 0 else None,
            'overdue': bool(pre_qty.get(pid, 0) > 0 and pre_need.get(pid) and pre_need.get(pid) < today_iso),
            'email': meta.get('email'), 'city': meta.get('city'), 'street': meta.get('street'),
            'port': meta.get('port'),
            'n_vendors': len({a['vendor_id'] for a in alts if a.get('vendor_id')}),
            'cheapest_vendor': cheapest['vendor'] if cheapest else None,
            'cheapest_price': cheapest['price'] if cheapest else None,
            'alt_vendors': priced,
        })
    return rows


def purchase_need_key(r):
    """Sort key = 'what we need to purchase', most urgent first. SKUs flagged BUY (past the
    reorder point) come before OK SKUs; within each, lower days-of-cover (closer to stocking out)
    ranks higher; a bigger suggested reorder breaks ties. No-demand / fully-covered SKUs
    (days_cover None or 9999) sink to the bottom. This is what orders the per-vendor line list so
    that in 'All SKUs' mode the actionable SKUs float to the top while the whole catalogue stays
    visible below."""
    dc = r.get('days_cover')
    if dc is None or dc >= 9999:
        dc = float('inf')
    return (0 if r.get('buy') else 1, dc, -(r.get('reorder_trend') or 0))


def by_supplier(recs):
    """Group rows into per-vendor purchase orders (pure — call with overrides already applied).
    Each vendor's lines are ordered by purchase need (buy-first, most urgent on top — see
    purchase_need_key), so the buy list and the 'All SKUs per vendor' view surface what to order
    without hunting; the full SKU set for the vendor is still present, just sorted underneath."""
    pos = {}
    for r in recs:
        v = r['vendor']
        po = pos.setdefault(v, {'vendor': v, 'country': r.get('country', ''), 'lead': r.get('lead', 0),
                               'lines': [], 'skus': 0})
        po['lines'].append(r)
        po['skus'] += 1
    for po in pos.values():
        po['lines'].sort(key=purchase_need_key)
    return sorted(pos.values(), key=lambda x: (x['vendor'] or '~'))


def data_quality():
    sup = suppliers()
    codes = list(sup.keys())
    cs = set(codes)
    found = {c for c in ((p.get('default_code') or '').strip()
                         for p in O.search_read('product.product',
                                                [('default_code', '!=', False), ('active', 'in', [True, False])],
                                                ['default_code'])) if c in cs}
    missing = [c for c in codes if c not in found]
    no_vendor = [c for c, d in sup.items() if not d.get('vendor')]
    no_lead = [c for c, d in sup.items() if not d.get('lead')]
    return [
        {'issue': 'Curated SKU not found in Odoo (no live data)', 'count': len(missing),
         'fix': 'Check the SKU internal reference matches Odoo, or remove it from the mapping.',
         'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in missing]},
        {'issue': 'No supplier in the mapping', 'count': len(no_vendor),
         'fix': 'Set the vendor on the SKU (Procurement page or Vendor module) so a PO can be grouped.',
         'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in no_vendor]},
        {'issue': 'No lead time in the mapping', 'count': len(no_lead),
         'fix': 'Set the supplier lead time (days) — the buy trigger needs it.',
         'sample': [{'default_code': c, 'name': sup.get(c, {}).get('desc')} for c in no_lead]},
    ]


def _open_po_lines():
    """All confirmed-PO lines still (partly) undelivered: state='purchase', open qty > 0, with the
    line's expected date. One pull powers the PO board, the per-SKU inbound match and the late
    chase list. (Same open-qty semantics as _incoming_po — the two are cross-validated.)"""
    lines = O.search_read('purchase.order.line',
                          [('state', '=', 'purchase'), ('product_qty', '>', 0)],
                          ['order_id', 'partner_id', 'product_id', 'product_qty', 'qty_received',
                           'price_unit', 'price_subtotal', 'date_planned'])
    out = []
    for r in lines:
        open_qty = (r.get('product_qty') or 0) - (r.get('qty_received') or 0)
        if open_qty <= 1e-3:
            continue
        qty = r.get('product_qty') or 0
        unit = (r['price_subtotal'] / qty) if qty else (r.get('price_unit') or 0)
        r['open_qty'] = open_qty
        r['open_value'] = open_qty * unit
        r['expected'] = str(r.get('date_planned') or '')[:10]
        out.append(r)
    return lines, out


def open_pos(t=None):
    """The open-PO board matched to procurement: order-level list of every confirmed PO with
    undelivered quantity, per-SKU inbound (open qty + earliest ETA + late portion) keyed by
    default_code so it joins the buy list, and the late-delivery chase list."""
    t = t or P.today()
    today = t.isoformat()
    _all, lines = _open_po_lines()

    pids = list({O.m2o_id(l['product_id']) for l in lines if l.get('product_id')})
    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']):
            codes[p['id']] = (p.get('default_code') or '').strip()

    oids = list({O.m2o_id(l['order_id']) for l in lines if l.get('order_id')})
    odates = {}
    for i in range(0, len(oids), 5000):
        for o_ in O.search_read('purchase.order', [('id', 'in', oids[i:i + 5000])],
                                ['date_order', 'amount_total']):
            odates[o_['id']] = {'date_order': str(o_.get('date_order') or '')[:10],
                                'amount_total': o_.get('amount_total') or 0.0}

    by_po, by_sku, late_rows = {}, {}, []
    for l in lines:
        po_id = O.m2o_id(l['order_id'])
        po_nm = O.m2o_name(l['order_id'])
        sup = O.m2o_name(l['partner_id'])
        pid = O.m2o_id(l['product_id'])
        code = codes.get(pid) or O.m2o_name(l['product_id'])
        exp = l['expected']
        is_late = bool(exp and exp < today)

        e = by_po.setdefault(po_nm, {
            'po': po_nm, 'supplier': sup,
            'ordered': odates.get(po_id, {}).get('date_order', ''),
            'po_value': odates.get(po_id, {}).get('amount_total', 0.0),
            'expected': exp, 'open_lines': 0, 'open_value': 0.0, 'late_value': 0.0})
        e['open_lines'] += 1
        e['open_value'] += l['open_value']
        if exp and (not e['expected'] or exp < e['expected']):
            e['expected'] = exp
        if is_late:
            e['late_value'] += l['open_value']

        s = by_sku.setdefault(code, {'open_qty': 0.0, 'open_value': 0.0, 'eta': None,
                                     'late_qty': 0.0, 'pos': set()})
        s['open_qty'] += l['open_qty']
        s['open_value'] += l['open_value']
        s['pos'].add(po_nm)
        if exp and (s['eta'] is None or exp < s['eta']):
            s['eta'] = exp
        if is_late:
            s['late_qty'] += l['open_qty']

        if is_late:
            days_late = (t - dt.date.fromisoformat(exp)).days
            late_rows.append({'supplier': sup, 'po': po_nm, 'sku': code,
                              'product': O.m2o_name(l['product_id']),
                              'open_qty': l['open_qty'], 'open_value': l['open_value'],
                              'expected': exp, 'days_late': days_late})

    for s in by_sku.values():
        s['pos'] = sorted(s['pos'])
    pos = sorted(by_po.values(), key=lambda x: (x['expected'] or '9999'))
    late_rows.sort(key=lambda x: -x['days_late'])
    return {
        'pos': pos, 'by_sku': by_sku, 'late': late_rows,
        'n_pos': len(pos), 'open_value': sum(p['open_value'] for p in pos),
        'late_value': sum(p['late_value'] for p in pos), 'n_late_lines': len(late_rows),
    }


def _pctl(sorted_vals, q):
    return sorted_vals[min(len(sorted_vals) - 1, int(len(sorted_vals) * q))] if sorted_vals else None


def vendor_reliability(t=None):
    """Vendor delivery truth from COMPLETED POs (24m): actual lead (date_order → effective_date)
    and promise lateness (date_planned → effective_date), per vendor. purchase.order carries all
    three dates, so no picking join is needed (the 'incoming picking' route is polluted by
    customer returns — verified live: only 29% of incoming pickings match a PO).
    The buy trigger runs on the CURATED nominal lead; this measures how wrong that is per vendor."""
    t = t or P.today()
    cutoff = (t - dt.timedelta(days=730)).isoformat()
    pos_ = O.search_read('purchase.order',
                         [('state', 'in', ('purchase', 'done')), ('date_order', '>=', cutoff),
                          ('effective_date', '!=', False)],
                         ['name', 'partner_id', 'date_order', 'date_planned', 'effective_date',
                          'amount_total'])
    # nominal lead per vendor = the most common curated per-SKU lead for that vendor (upper-name join)
    nom = {}
    for d in suppliers().values():
        v = (d.get('vendor') or '').strip().upper()
        if v and d.get('lead'):
            nom.setdefault(v, {})
            nom[v][d['lead']] = nom[v].get(d['lead'], 0) + 1
    nominal = {v: max(c.items(), key=lambda kv: kv[1])[0] for v, c in nom.items()}

    rows, per = [], {}
    for p in pos_:
        v = (O.m2o_name(p.get('partner_id')) or '(unknown)').strip()
        o = dt.date.fromisoformat(str(p['date_order'])[:10])
        e = dt.date.fromisoformat(str(p['effective_date'])[:10])
        lead = (e - o).days
        late = None
        if p.get('date_planned'):
            late = (e - dt.date.fromisoformat(str(p['date_planned'])[:10])).days
        rows.append({'po': p['name'], 'vendor': v, 'ordered': o.isoformat(),
                     'promised': str(p.get('date_planned') or '')[:10] or None,
                     'received': e.isoformat(), 'lead_days': lead, 'days_late': late,
                     'po_value': p.get('amount_total') or 0.0})
        s = per.setdefault(v, {'vendor': v, 'n_pos': 0, 'value': 0.0, 'leads': [], 'lates': []})
        s['n_pos'] += 1
        s['value'] += p.get('amount_total') or 0.0
        s['leads'].append(lead)
        if late is not None:
            s['lates'].append(late)

    vendors = []
    for s in per.values():
        s['leads'].sort()
        s['lates'].sort()
        vu = s['vendor'].upper()
        v_nom = nominal.get(vu)
        p90 = _pctl(s['leads'], 0.9)
        vendors.append({'vendor': s['vendor'], 'n_pos': s['n_pos'], 'value': s['value'],
                        'lead_med': _pctl(s['leads'], 0.5), 'lead_p90': p90,
                        'nominal_lead': v_nom,
                        'lead_gap': (p90 - v_nom) if (v_nom and p90 is not None) else None,
                        'on_promise_pct': (sum(1 for d in s['lates'] if d <= 0) / len(s['lates']) * 100)
                                          if s['lates'] else None,
                        'late_med': _pctl(s['lates'], 0.5), 'late_p90': _pctl(s['lates'], 0.9)})
    vendors.sort(key=lambda x: -x['value'])
    all_lates = sorted(r['days_late'] for r in rows if r['days_late'] is not None)
    return {'vendors': vendors, 'rows': rows, 'n_pos': len(rows),
            'n_late': sum(1 for d in all_lates if d > 0),
            'late_share_pct': (sum(1 for d in all_lates if d > 0) / len(all_lates) * 100)
                              if all_lates else None,
            'late_med': _pctl(all_lates, 0.5), 'late_p90': _pctl(all_lates, 0.9),
            'n_vendors': len(vendors),
            'actual_p90_by_vendor': {v['vendor'].upper(): v['lead_p90'] for v in vendors
                                     if v['n_pos'] >= 3 and v['lead_p90'] is not None}}


def buy_under_actual_lead(recs, vr):
    """SKUs the nominal-lead trigger says are SAFE but that are already past the reorder point
    under their vendor's ACTUAL p90 lead (vendors with 3+ completed POs). The honest delta —
    the base buy list is untouched."""
    p90 = vr.get('actual_p90_by_vendor') or {}
    out = []
    for r in recs:
        if r.get('buy'):
            continue
        ap = p90.get((r.get('vendor') or '').strip().upper())
        if ap is None or r.get('days_cover') is None or r['days_cover'] >= 9999:
            continue
        eff = max(ap, r.get('lead') or 0)
        if r.get('avg_day', 0) > 0 and r['days_cover'] < eff:
            out.append({**r, 'actual_lead_p90': ap})
    out.sort(key=lambda x: (x['days_cover'] if x['days_cover'] is not None else 9999))
    return out


def summary(t=None):
    t = t or P.today()
    recs = recommendations(t)
    ps, pe = _proj_window(t)
    vr = vendor_reliability(t)
    return {
        'recs': recs, 'n_skus': len(recs),
        'window_days': max(1, (pe - ps).days), 'safety_pct': _SAFETY * 100,
        'inbound': open_pos(t),
        'data_quality': data_quality(),
        'vendor_reliability': vr,
        'buy_flip': buy_under_actual_lead(recs, vr),
        'pulled_at': dt.datetime.now().strftime('%Y-%m-%d %H:%M'),
    }


def vendor_book(t=None):
    """Per-vendor rollup for the Vendor module: each vendor's SKU count, lead, country, demand value,
    and the SKUs they supply. Plus the multiple-vendor / cheapest-price view."""
    recs = recommendations(t)
    multi = [r for r in recs if (r.get('n_vendors') or 0) > 1]
    return {'recs': recs, 'multi_vendor': multi, 'n_multi': len(multi)}


def validate(t=None, team_id=None):
    t = t or P.today()
    _, pe = _proj_window(t)
    f, to = _add_months(t, -12).isoformat(), _add_months(pe, -12).isoformat()
    g = O.read_group('sale.order.line', _demand_domain(f, to), ['product_uom_qty:sum'],
                     ['product_id'], lazy=False)
    per = sum((r.get('product_uom_qty') or 0.0) for r in g if r.get('product_id'))
    tot = O.sum_field('sale.order.line', _demand_domain(f, to), 'product_uom_qty')
    checks = [{'check': '8-month demand: Σ(per-SKU units) == line-level units',
               'a': round(per, 2), 'b': round(tot, 2), 'gap': round(per - tot, 2),
               'ok': abs(per - tot) <= 1.0}]

    # Open POs: row-by-row NET (product_qty − qty_received over ALL confirmed lines — the same
    # semantics as _incoming_po, over-receipts included) vs the _incoming_po read_group aggregate.
    # Two aggregation paths, small live-drift tolerance (two RPC snapshots on a live ledger).
    ib = open_pos(t)
    all_lines, _ = _open_po_lines()
    row_net = sum((l.get('product_qty') or 0) - (l.get('qty_received') or 0) for l in all_lines)
    agg_net = sum(_incoming_po().values())
    checks.append({'check': 'Open POs: row-level Σ net qty == read_group Σ net qty (live-drift tol)',
                   'a': round(row_net, 2), 'b': round(agg_net, 2),
                   'gap': round(row_net - agg_net, 2),
                   'ok': abs(row_net - agg_net) <= max(1.0, abs(agg_net) * 0.001)})

    # Internal consistency: the late chase list is exactly the late portion of the SKU rollup.
    late_val = sum(r['open_value'] for r in ib['late'])
    sku_late_val_ok = sum(p['late_value'] for p in ib['pos'])
    checks.append({'check': 'Open POs: Σ(late chase lines) == Σ(per-PO late value)',
                   'a': round(late_val, 2), 'b': round(sku_late_val_ok, 2),
                   'gap': round(late_val - sku_late_val_ok, 2),
                   'ok': abs(late_val - sku_late_val_ok) <= 1.0})

    # Vendor reliability: the per-vendor rollup partitions the PO rows exactly (counts AND value).
    vr = vendor_reliability(t)
    vn = sum(v['n_pos'] for v in vr['vendors'])
    checks.append({'check': 'Vendor reliability: Σ(per-vendor POs) == PO rows pulled',
                   'a': vn, 'b': vr['n_pos'], 'gap': vn - vr['n_pos'],
                   'ok': vn == vr['n_pos']})
    vv = sum(v['value'] for v in vr['vendors'])
    rv = sum(r['po_value'] for r in vr['rows'])
    checks.append({'check': 'Vendor reliability: Σ(per-vendor PO value) == Σ(row PO value)',
                   'a': round(vv, 2), 'b': round(rv, 2), 'gap': round(vv - rv, 2),
                   'ok': abs(vv - rv) <= 1.0})
    return checks