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 sku_backlog(t=None, team_id=None):
    """`(pre_book_by_code, open_backlog_by_code, report)` for the product catalogue.

    This is deliberately NOT `board()`'s ``not due`` bucket.  That board enters through open
    pickings and substitutes a picking scheduled date when an order has no commitment date;
    pre-book means the stricter, stored-order fact ``commitment_date > end of t``.  Both measures
    are confirmed wholesale goods that Odoo still calls pending, started, or partial.  Their
    quantity is the positive part of ordered minus delivered, grouped by product and then by the
    catalogue's code.
    """
    t = t or P.today()
    if isinstance(t, dt.datetime):
        t = t.date()
    end_of_day = f"{t.isoformat()} 23:59:59"
    teams = [team_id] if team_id is not None else O.active_team_ids()
    common = [
        ('state', 'in', ['sale', 'done']),
        ('product_id', '!=', False),
        ('order_id.delivery_status', 'in', ['pending', 'started', 'partial']),
        ('product_id.type', '!=', 'service'),
    ]
    if teams:
        common.append(('order_id.team_id', 'in', teams))
    excluded = O.excluded_partner_ids()
    if excluded:
        common.append(('order_partner_id', 'not in', list(excluded)))

    def _by_product(domain):
        groups = O.read_group('sale.order.line', domain,
                              ['product_uom_qty:sum', 'qty_delivered:sum'], ['product_id'],
                              lazy=False)
        out = {}
        for group in groups:
            pid = O.m2o_id(group.get('product_id'))
            if not pid:
                continue
            open_qty = max(0.0, float(group.get('product_uom_qty') or 0.0)
                           - float(group.get('qty_delivered') or 0.0))
            if open_qty > 1e-9:
                out[pid] = open_qty
        return out

    open_by_pid = _by_product(list(common))
    pre_by_pid = _by_product(list(common) + [('order_id.commitment_date', '>', end_of_day)])
    pids = sorted(set(open_by_pid) | set(pre_by_pid))
    codes = {}
    for ch in _chunk(pids, 500):
        for product in O.search_read('product.product',
                                     [('id', 'in', ch), ('active', 'in', [True, False])],
                                     ['default_code']):
            pid = product.get('id')
            codes[pid] = str(product.get('default_code') or f"pid:{pid}").strip()

    def _by_code(by_pid):
        out = {}
        for pid, qty in by_pid.items():
            code = codes.get(pid, f"pid:{pid}")
            out[code] = out.get(code, 0.0) + qty
        return out

    pre_by_code, open_by_code = _by_code(pre_by_pid), _by_code(open_by_pid)
    report = {
        'as_of': t.isoformat(),
        'team_ids': teams,
        'pre_book_qty': round(sum(pre_by_code.values()), 2),
        'pre_book_skus': len(pre_by_code),
        'open_backlog_qty': round(sum(open_by_code.values()), 2),
        'open_backlog_skus': len(open_by_code),
    }
    return pre_by_code, open_by_code, report


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