platform/modules/complexity.py

"""SKU Complexity — the BCG tail-rationalization view (cost-waste brief rec 2).

The canon point: revenue-proportional allocation OVERSTATES tail-SKU profitability — a $4 SKU
that generates 200 order lines, 40 picks and 3 returns costs the same to HANDLE as a $400 one.
This module re-costs every SKU by its ACTIVITY over the LTM, all-channel (the Amazon
channel-scope rule: a wholesale-only view would false-flag Amazon sellers).

Two-tier verdict — the kill test must not rest on an estimate:
  KILL CANDIDATE  gm − carrying < 0        (loses money on HARD costs alone: GM minus 25%/yr
                                            of its current inventory book value)
  REVIEW          hard-positive but gm − carrying − activity_cost < 0
                                           (underwater once the POOLED activity rate applies —
                                            an estimate, labeled as such)
  KEEP            covers both.
Pooled activity rate = LTM opex (expense-type bill lines, from modules/spend.spend_cube) ÷ total
activity units (SO lines + PO lines + picks + invoice lines + return lines) — self-consistent
with the GL, an AVERAGE (includes fixed rent/insurance), therefore an upper bound; the math is
shown in the page's verify expander. Guardrails the owner should apply before killing: basket
role (does it pull baskets?) and the count-trust set — both live in other modules; the export
carries the columns to join.
"""
import core.odoo as O
import core.periods as P
import modules.spend as spend_mod
import modules.customers as cust_mod

CARRY_RATE = 0.25          # $/yr carrying per $ of inventory book value (APQC 20-30% norm)


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


def _count_by_product(model, domain):
    out = {}
    for g in O.read_group(model, domain, ['id'], ['product_id'], lazy=False):
        pid = O.m2o_id(g.get('product_id'))
        if pid:
            out[pid] = g.get('__count') or 0
    return out


def build(t=None):
    t = t or P.today()
    lf, lt = P.ltm(t)
    ex = O.excluded_partner_ids()

    # sales: lines, qty, revenue, margin — ALL channels (no team filter), house accounts
    # excluded, PHYSICAL products only (service/delivery pseudo-products are not SKUs and
    # must not appear on a kill-list)
    phys = ('product_id.type', '=', 'product')
    sol_dom = [('state', 'in', ['sale', 'done']), ('product_id', '!=', False), phys,
               ('order_id.date_order', '>=', f'{lf} 00:00:00'),
               ('order_id.date_order', '<=', f'{lt} 23:59:59')]
    if ex:
        sol_dom.append(('order_partner_id', 'not in', list(ex)))
    sales = {}
    for g in O.read_group('sale.order.line', sol_dom,
                          ['price_subtotal:sum', 'product_uom_qty:sum', 'margin:sum'],
                          ['product_id'], lazy=False):
        pid = O.m2o_id(g.get('product_id'))
        if pid:
            sales[pid] = {'so_lines': g.get('__count') or 0,
                          'rev': g.get('price_subtotal') or 0.0,
                          'qty': g.get('product_uom_qty') or 0.0,
                          'gm': g.get('margin') or 0.0}

    po_lines = _count_by_product('purchase.order.line',
                                 [('state', 'in', ['purchase', 'done']),
                                  ('product_id', '!=', False), phys,
                                  ('order_id.date_order', '>=', f'{lf} 00:00:00'),
                                  ('order_id.date_order', '<=', f'{lt} 23:59:59')])
    picks = _count_by_product('stock.move',
                              [('state', '=', 'done'), ('product_id', '!=', False), phys,
                               ('date', '>=', f'{lf} 00:00:00'),
                               ('date', '<=', f'{lt} 23:59:59'),
                               ('picking_id.picking_type_id.code', '=', 'outgoing')])
    inv_lines = _count_by_product('account.move.line',
                                  [('move_id.move_type', '=', 'out_invoice'),
                                   ('parent_state', '=', 'posted'),
                                   ('product_id', '!=', False), phys,
                                   ('move_id.invoice_date', '>=', lf),
                                   ('move_id.invoice_date', '<=', lt)])
    ret_lines = _count_by_product('account.move.line',
                                  [('move_id.move_type', '=', 'out_refund'),
                                   ('parent_state', '=', 'posted'),
                                   ('product_id', '!=', False), phys,
                                   ('move_id.invoice_date', '>=', lf),
                                   ('move_id.invoice_date', '<=', lt)])

    # current inventory book value per product (valuation layers sum = current book)
    book = {}
    for g in O.read_group('stock.valuation.layer', [], ['value:sum'], ['product_id'],
                          lazy=False):
        pid = O.m2o_id(g.get('product_id'))
        if pid:
            book[pid] = g.get('value') or 0.0

    # the pooled activity rate — opex (expense-type) ÷ total activity units, GL-consistent
    cube = spend_mod.spend_cube(t)
    opex_pool = cube['spend_total']
    pids = set(sales) | set(po_lines) | set(picks) | set(inv_lines) | set(ret_lines) | \
        {p for p, v in book.items() if abs(v) > 1}
    total_units = sum(sales.get(p, {}).get('so_lines', 0) + po_lines.get(p, 0)
                      + picks.get(p, 0) + inv_lines.get(p, 0) + ret_lines.get(p, 0)
                      for p in pids)
    rate = (opex_pool / total_units) if total_units else 0.0

    meta = {}
    for ch in _chunk(list(pids)):
        for p in O.search_read('product.product',
                               [('id', 'in', ch), ('active', 'in', [True, False])],
                               ['default_code', 'name', 'categ_id']):
            meta[p['id']] = p

    rows = []
    for pid in pids:
        s = sales.get(pid, {'so_lines': 0, 'rev': 0.0, 'qty': 0.0, 'gm': 0.0})
        units = (s['so_lines'] + po_lines.get(pid, 0) + picks.get(pid, 0)
                 + inv_lines.get(pid, 0) + ret_lines.get(pid, 0))
        bv = max(book.get(pid, 0.0), 0.0)
        carrying = bv * CARRY_RATE
        activity = units * rate
        adj_hard = s['gm'] - carrying
        adj_full = adj_hard - activity
        if adj_hard < 0 and (bv > 0 or s['rev'] > 0):
            verdict = 'KILL CANDIDATE'
        elif adj_full < 0:
            verdict = 'REVIEW'
        else:
            verdict = 'KEEP'
        m = meta.get(pid, {})
        rows.append({'pid': pid, 'code': (m.get('default_code') or '').strip() or f'#{pid}',
                     'product': m.get('name') or '', 'category': O.m2o_name(m.get('categ_id')),
                     'rev': s['rev'], 'gm': s['gm'], 'so_lines': s['so_lines'],
                     'po_lines': po_lines.get(pid, 0), 'picks': picks.get(pid, 0),
                     'ret_lines': ret_lines.get(pid, 0), 'units_activity': units,
                     'book_value': bv, 'carrying': carrying, 'activity_cost': activity,
                     'adj_hard': adj_hard, 'adj_full': adj_full, 'verdict': verdict})
    rows.sort(key=lambda x: x['adj_full'])

    # whale curve: cumulative FULL-adjusted profit by descending adj_full rank
    ranked = sorted(rows, key=lambda x: -x['adj_full'])
    cum, whale = 0.0, []
    for i, r in enumerate(ranked, 1):
        cum += r['adj_full']
        if i % max(1, len(ranked) // 200) == 0 or i == len(ranked):
            whale.append({'rank': i, 'cum_profit': cum})
    peak = max((w['cum_profit'] for w in whale), default=0.0)

    n_kill = sum(1 for r in rows if r['verdict'] == 'KILL CANDIDATE')
    n_rev = sum(1 for r in rows if r['verdict'] == 'REVIEW')
    return {
        'rows': rows, 'whale': whale, 'peak_profit': peak,
        'final_profit': cum, 'n_skus': len(rows),
        'n_kill': n_kill, 'n_review': n_rev,
        'kill_book_value': sum(r['book_value'] for r in rows
                               if r['verdict'] == 'KILL CANDIDATE'),
        'kill_carrying': sum(r['carrying'] for r in rows if r['verdict'] == 'KILL CANDIDATE'),
        'rate': rate, 'opex_pool': opex_pool, 'total_units': total_units,
        'window': (lf, lt),
    }


def impact(pre, t=None, verdict='KILL CANDIDATE'):
    """Who feels it if we kill: LTM revenue on the verdict SKUs by CUSTOMER (with their
    share-of-book, so a dependency reads differently from a nuisance) and rolled up by AGENT.
    Σ(customer stake) == Σ(agent stake) == Σ(verdict SKUs' revenue) — the ties are asserted
    in validate(). Same scope as build(): all channels, physical products, house excluded."""
    t = t or P.today()
    lf, lt = pre['window']
    ex = O.excluded_partner_ids()
    kill_pids = [r['pid'] for r in pre['rows'] if r['verdict'] == verdict]

    base = [('state', 'in', ['sale', 'done']), ('product_id', '!=', False),
            ('product_id.type', '=', 'product'),
            ('order_id.date_order', '>=', f'{lf} 00:00:00'),
            ('order_id.date_order', '<=', f'{lt} 23:59:59')]
    if ex:
        base.append(('order_partner_id', 'not in', list(ex)))

    # revenue on the verdict SKUs per (customer × SKU) — chunked (grouped reads over large
    # id-domains echo the domain per group → server MemoryError)
    per_cust = {}
    for ch in _chunk(kill_pids, 400):
        for g in O.read_group('sale.order.line', base + [('product_id', 'in', ch)],
                              ['price_subtotal:sum'], ['order_partner_id', 'product_id'],
                              lazy=False):
            pid = O.m2o_id(g.get('order_partner_id'))
            if not pid:
                continue
            e = per_cust.setdefault(pid, {'pid': pid,
                                          'customer': O.m2o_name(g.get('order_partner_id')),
                                          'rev_stake': 0.0, 'skus': set()})
            e['rev_stake'] += g.get('price_subtotal') or 0.0
            e['skus'].add(O.m2o_id(g.get('product_id')))

    # each affected customer's TOTAL book (same scope) for the share-of-book column
    book = {}
    for g in O.read_group('sale.order.line', base, ['price_subtotal:sum'],
                          ['order_partner_id'], lazy=False):
        pid = O.m2o_id(g.get('order_partner_id'))
        if pid:
            book[pid] = g.get('price_subtotal') or 0.0

    attrs = cust_mod._partner_attrs(list(per_cust))
    by_customer = []
    for e in per_cust.values():
        total = book.get(e['pid'], 0.0)
        by_customer.append({'pid': e['pid'], 'customer': e['customer'],
                            'agent': (attrs.get(e['pid']) or {}).get('agent') or '(none)',
                            'rev_stake': e['rev_stake'], 'n_skus': len(e['skus']),
                            'book_rev': total,
                            'share_pct': (e['rev_stake'] / total * 100) if total else None})
    by_customer.sort(key=lambda x: -x['rev_stake'])

    by_agent = {}
    for r in by_customer:
        a = by_agent.setdefault(r['agent'], {'agent': r['agent'], 'customers': 0,
                                             'rev_stake': 0.0, 'book_rev': 0.0})
        a['customers'] += 1
        a['rev_stake'] += r['rev_stake']
        a['book_rev'] += r['book_rev']
    agents = [{**a, 'share_pct': (a['rev_stake'] / a['book_rev'] * 100)
               if a['book_rev'] else None} for a in by_agent.values()]
    agents.sort(key=lambda x: -x['rev_stake'])
    return {'by_customer': by_customer, 'by_agent': agents,
            'stake_total': sum(r['rev_stake'] for r in by_customer),
            'n_customers': len(by_customer), 'verdict': verdict}


def validate(t=None, team_id=None, pre=None):
    """Revenue and margin tie the server aggregates over the same domain; book value ties the
    server SVL sum (the GL-05000 figure)."""
    t = t or P.today()
    b = pre or build(t)
    lf, lt = b['window']
    ex = O.excluded_partner_ids()
    dom = [('state', 'in', ['sale', 'done']), ('product_id', '!=', False),
           ('product_id.type', '=', 'product'),
           ('order_id.date_order', '>=', f'{lf} 00:00:00'),
           ('order_id.date_order', '<=', f'{lt} 23:59:59')]
    if ex:
        dom.append(('order_partner_id', 'not in', list(ex)))
    checks = []
    srv_rev = O.sum_field('sale.order.line', dom, 'price_subtotal')
    a_rev = sum(r['rev'] for r in b['rows'])
    checks.append({'check': 'complexity: Σ SKU revenue == server Σ (all-channel LTM)',
                   'a': round(a_rev, 2), 'b': round(srv_rev, 2),
                   'gap': round(a_rev - srv_rev, 2),
                   'ok': abs(a_rev - srv_rev) <= max(1.0, srv_rev * 0.001)})
    srv_bv = O.sum_field('stock.valuation.layer', [], 'value')
    a_bv = sum(r['book_value'] for r in b['rows'])
    checks.append({'check': 'complexity: Σ SKU book value == server Σ valuation layers '
                            '(negatives clamped per SKU — gap = clamp effect)',
                   'a': round(a_bv, 2), 'b': round(srv_bv, 2),
                   'gap': round(a_bv - srv_bv, 2),
                   'ok': a_bv >= srv_bv - 1.0})
    imp = b.get('impact')
    if imp:
        kill_rev = sum(r['rev'] for r in b['rows'] if r['verdict'] == imp['verdict'])
        for key in ('by_customer', 'by_agent'):
            s = sum(r['rev_stake'] for r in imp[key])
            checks.append({'check': f'kill-impact {key} == Σ(kill SKUs revenue)',
                           'a': round(s, 2), 'b': round(kill_rev, 2),
                           'gap': round(s - kill_rev, 2),
                           'ok': abs(s - kill_rev) <= max(1.0, kill_rev * 0.001)})
    return checks