platform/modules/backorders.py

"""Backorders — what we owe customers, aged (the mirror image of AR aging).

Every confirmed-but-undelivered sale line, aged against the order's promise date
(commitment_date, else the earliest open outgoing picking's scheduled date), valued at the
line's discounted unit price, joined to supply (on-hand + earliest inbound PO ETA) so each
row carries ONE verb action: Ship (stock is on hand) / Expedite the PO (inbound exists) /
Call the customer (no supply in sight). Line-level fill canon: ≥95% industry bar.

Scope: wholesale teams (5/6), GIFTWARE/Amazon excluded — a backorder board is about promises
to dealers. BU-filterable.
"""
import datetime as dt

import core.odoo as O
import core.periods as P
import modules.procurement as proc

BUCKETS = ['not due', '0-7', '8-21', '22-45', '45+', 'no promise']
_OPEN_PICK_DOMAIN = [('state', 'in', ['confirmed', 'assigned', 'waiting']),
                     ('picking_type_id.code', '=', 'outgoing')]


def _chunk(ids, n=2000):
    ids = list(ids)
    for i in range(0, len(ids), n):
        yield ids[i:i + n]


def _open_pickings():
    """Open outgoing pickings → {origin(SO name): earliest scheduled date}."""
    picks = O.search_read('stock.picking', list(_OPEN_PICK_DOMAIN),
                          ['origin', 'scheduled_date'])
    sched = {}
    for p in picks:
        o = (p.get('origin') or '').strip()
        if not o:
            continue
        d = str(p.get('scheduled_date') or '')[:10]
        if d and (o not in sched or d < sched[o]):
            sched[o] = d
    return picks, sched


def board(team_id=None, t=None):
    """The full open-line board + bucket rollup. Returns dict(rows, buckets, orders, coverage)."""
    t = t or P.today()
    today = t
    picks, sched = _open_pickings()

    # the sale orders behind those pickings, in wholesale scope
    orders = []
    names = list(sched)
    ex = O.excluded_partner_ids()
    for ch in _chunk(names, 500):
        dom = [('name', 'in', ch), ('state', 'in', ['sale', 'done'])]
        dom.append(('team_id', '=', team_id) if team_id is not None
                   else ('team_id', 'in', O.TEAM_IDS))
        if ex:
            dom.append(('partner_id', 'not in', list(ex)))
        orders += O.search_read('sale.order', dom,
                                ['name', 'partner_id', 'team_id', 'date_order',
                                 'commitment_date'])
    by_oid = {o['id']: o for o in orders}

    # their lines, open qty computed locally (a domain cannot compare two fields)
    lines = []
    for ch in _chunk(list(by_oid), 500):
        lines += O.search_read('sale.order.line',
                               [('order_id', 'in', ch), ('display_type', '=', False),
                                ('product_id', '!=', False)],
                               ['order_id', 'order_partner_id', 'product_id',
                                'product_uom_qty', 'qty_delivered', 'price_unit',
                                'price_subtotal', 'name'])
    open_lines = []
    for r in lines:
        openq = (r.get('product_uom_qty') or 0.0) - (r.get('qty_delivered') or 0.0)
        if openq <= 1e-3:
            continue
        qty = r.get('product_uom_qty') or 0.0
        unit = (r.get('price_subtotal') or 0.0) / qty if qty else (r.get('price_unit') or 0.0)
        r['open_qty'] = openq
        r['open_value'] = openq * unit
        open_lines.append(r)

    # supply joins: on-hand + earliest inbound ETA per product
    pids = list({O.m2o_id(r['product_id']) for r in open_lines})
    onhand, codes = {}, {}
    for ch in _chunk(pids):
        for p in O.search_read('product.product',
                               [('id', 'in', ch), ('active', 'in', [True, False])],
                               ['qty_available', 'default_code']):
            onhand[p['id']] = p.get('qty_available') or 0.0
            codes[p['id']] = (p.get('default_code') or '').strip()
    _all_pol, pol = proc._open_po_lines()
    inbound = {}
    for l in pol:
        pid = O.m2o_id(l.get('product_id'))
        if not pid:
            continue
        cur = inbound.setdefault(pid, {'qty': 0.0, 'eta': None})
        cur['qty'] += l.get('open_qty') or 0.0
        eta = l.get('expected') or ''
        if eta and (cur['eta'] is None or eta < cur['eta']):
            cur['eta'] = eta

    rows = []
    n_promised = 0
    for r in open_lines:
        oid = O.m2o_id(r['order_id'])
        o = by_oid.get(oid) or {}
        promise = str(o.get('commitment_date') or '')[:10] or sched.get(o.get('name', ''), '')
        if str(o.get('commitment_date') or '')[:10]:
            n_promised += 1
        if promise:
            days = (today - dt.date.fromisoformat(promise)).days
            bucket = ('not due' if days < 0 else '0-7' if days <= 7
                      else '8-21' if days <= 21 else '22-45' if days <= 45 else '45+')
        else:
            days, bucket = None, 'no promise'
        pid_prod = O.m2o_id(r['product_id'])
        oh = onhand.get(pid_prod, 0.0)
        inb = inbound.get(pid_prod)
        if oh >= r['open_qty']:
            action = 'Ship — stock on hand'
        elif inb and inb.get('eta'):
            action = f"Expedite PO — ETA {inb['eta']}"
        else:
            action = 'Call customer — no supply inbound'
        rows.append({
            'pid': O.m2o_id(r['order_partner_id']),
            'customer': O.m2o_name(r['order_partner_id']),
            'order': o.get('name', ''),
            'bu': O.TEAM_NAMES.get(O.m2o_id(o.get('team_id')), '?'),
            'sku': codes.get(pid_prod, ''), 'product': O.m2o_name(r['product_id']),
            'open_qty': r['open_qty'], 'open_value': r['open_value'],
            'promise': promise or '—', 'days_past': days if days is not None else '',
            'bucket': bucket, 'on_hand': oh,
            'inbound_eta': (inb or {}).get('eta') or '',
            'action': action,
        })
    rows.sort(key=lambda x: -x['open_value'])
    bucket_rollup = []
    for bu in sorted({r['bu'] for r in rows}):
        for b in BUCKETS:
            v = sum(r['open_value'] for r in rows if r['bu'] == bu and r['bucket'] == b)
            if v:
                bucket_rollup.append({'bu': bu, 'bucket': b, 'value': v})
    return {
        'rows': rows,
        'buckets': bucket_rollup,
        'n_orders': len({r['order'] for r in rows}),
        'open_value': sum(r['open_value'] for r in rows),
        'past_value': sum(r['open_value'] for r in rows
                          if r['bucket'] in ('0-7', '8-21', '22-45', '45+')),
        'promise_coverage': (n_promised / len(open_lines) * 100.0) if open_lines else 0.0,
        'n_pickings': len(picks),
    }


def validate(t=None, team_id=None):
    """(1) the picking pull is complete (search_count ties the row pull — truncation guard);
    (2) the local open-qty arithmetic ties Odoo's own aggregates: for the pulled order set,
    Σ(product_uom_qty) − Σ(qty_delivered) via server-side read_group == Σ of our per-row
    open_qty (same universe, independent arithmetic path)."""
    b = board(team_id, t)
    n_count = O.get_odoo().search_count('stock.picking', list(_OPEN_PICK_DOMAIN))
    checks = [{'check': 'open outgoing pickings — pull complete',
               'a': b['n_pickings'], 'b': n_count,
               'gap': b['n_pickings'] - n_count, 'ok': b['n_pickings'] == n_count}]
    oids = sorted({r['order'] for r in b['rows']})
    if oids:
        dom = [('order_id.name', 'in', oids[:500]), ('display_type', '=', False),
               ('product_id', '!=', False)]
        try:
            g = O.read_group('sale.order.line', dom,
                             ['product_uom_qty:sum', 'qty_delivered:sum'], [], lazy=False)
            srv_open = ((g[0].get('product_uom_qty') or 0.0)
                        - (g[0].get('qty_delivered') or 0.0)) if g else 0.0
            # NET open recomputed row-level (includes over-shipped negatives, which the board
            # excludes) so both sides measure the same universe
            rl = O.search_read('sale.order.line', dom, ['product_uom_qty', 'qty_delivered'])
            ours = sum((l.get('product_uom_qty') or 0.0) - (l.get('qty_delivered') or 0.0)
                       for l in rl)
            checks.append({'check': 'open qty — server aggregate vs row arithmetic',
                           'a': round(ours, 2), 'b': round(srv_open, 2),
                           'gap': round(ours - srv_open, 2),
                           'ok': abs(ours - srv_open) < 1.0})
        except Exception as e:
            checks.append({'check': 'open qty aggregate (read_group)', 'a': '—',
                           'b': str(e)[:60], 'gap': '', 'ok': False})
    return checks