platform/modules/overstock.py

"""Overstock & dead-stock module (lives under Inventory).

A deeper, book-value-accurate lens on trapped inventory capital than the standard-cost coverage
view in inventory.py. Three things:

  1. DEEP per-SKU list — every in-stock SKU with its BOOK value (cumulative stock.valuation.layer,
     which ties to GL account 05000), days-of-inventory (DSI = on-hand / last-3mo annualised
     ALL-CHANNEL sales), a dead/excess/slow/healthy status, last-sold month and trailing units.
  2. SUMMARY — trapped capital = dead + excess; share of inventory; bucket distribution.
  3. ROLLING COHORT RECOVERY — each month-end classifies its dead cohort, then tracks it forward;
     a SKU "graduates" only when DSI < 120 days. Quantifies how little dead stock self-clears.

CONSOLIDATED (HQ): on-hand stock is one physical warehouse, not brand-tagged — ignores the DBA
filter, like the rest of Inventory. ALL-CHANNEL sales (no team/partner scope) are used on purpose:
a SKU that sells only through the Amazon/GIFTWARE channel is NOT dead.

READ-ONLY.
"""
import sys
import calendar
import datetime
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import core.odoo as O

DEAD_UNITS_12M = 5          # < this many units sold (all channels) in trailing 12mo = dead
GRADUATE_DSI = 120          # days-of-inventory below this = healthy turnover ("graduated")
EXCESS_DSI = 365            # sells, but holds > 1 year of supply = excess/overstock
_VAL_FLOOR = 50.0           # ignore sub-$50 book scraps as noise


# --------------------------------------------------------------------------- date helpers
def _today():
    return datetime.date.today()


def _eom(y, m):
    return datetime.date(y, m, calendar.monthrange(y, m)[1])


def _seq(y0, m0, y1, m1):
    out, y, m = [], y0, m0
    while (y, m) <= (y1, m1):
        out.append((y, m)); y, m = (y + (m // 12)), (m % 12 + 1)
    return out


# --------------------------------------------------------------------------- raw pulls
def _monthly_sales(yms):
    """All-channel confirmed sales UNITS per product per (y,m). Parallelised — one read_group/month."""
    def _one(ym):
        y, m = ym
        f = f"{y}-{m:02d}-01 00:00:00"; t = f"{y}-{m:02d}-{calendar.monthrange(y, m)[1]} 23:59:59"
        g = O.read_group('sale.order.line',
                         [('order_id.state', 'in', ['sale', 'done']), ('order_id.date_order', '>=', f),
                          ('order_id.date_order', '<=', t), ('product_id.type', '!=', 'service')],
                         ['product_uom_qty:sum', 'product_id'], ['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')}
    res = O.parallel([(lambda ym=ym: _one(ym)) for ym in yms])
    return dict(zip(yms, res))


def _onhand(asof):
    """Cumulative on-hand (qty, book value) per product from stock.valuation.layer as of `asof` (a
    'YYYY-MM-DD' date). Book value ties to GL 05000 (validated)."""
    g = O.read_group('stock.valuation.layer', [('create_date', '<=', f'{asof} 23:59:59')],
                     ['value:sum', 'quantity:sum', 'product_id'], ['product_id'], lazy=False)
    return {O.m2o_id(r['product_id']): ((r.get('quantity') or 0.0), (r.get('value') or 0.0))
            for r in g if r.get('product_id')}


def _cat_main(catid_map, categ):
    cid = O.m2o_id(categ)
    return catid_map.get(cid) or '(uncategorized)'


def _master(pids):
    """Product code/name/category for a set of ids (incl. archived — re-SKUed items)."""
    if not pids:
        return {}, {}
    prods = O.search_read('product.product', [('id', 'in', list(pids)), ('active', 'in', [True, False])],
                          ['default_code', 'name', 'categ_id'])
    cats = O.search_read('product.category', [], ['id', 'complete_name'])
    catmap = {}
    for c in cats:
        parts = [x.strip() for x in (c['complete_name'] or '').split('/')]
        catmap[c['id']] = parts[1] if len(parts) >= 2 else (parts[0] if parts else None)
    pm = {p['id']: {'sku': (p.get('default_code') or f"#{p['id']}"), 'name': p.get('name') or '',
                    'category': _cat_main(catmap, p.get('categ_id'))} for p in prods}
    return pm, catmap


# --------------------------------------------------------------------------- classification
def _dsi(on_hand, units_3m):
    if units_3m > 0:
        return on_hand / (units_3m / 91.25)
    return None   # no recent sales -> effectively infinite cover


def _status(units_12m, dsi):
    if units_12m < DEAD_UNITS_12M:
        return 'Dead (no sales 12mo)'
    if dsi is None or dsi > EXCESS_DSI:
        return 'Excess (>365d cover)'
    if dsi > GRADUATE_DSI:
        return 'Slow (120-365d)'
    return 'Healthy (<120d)'


def deep(asof=None):
    """The deep snapshot: per-SKU rows + summary KPIs + bucket distribution, as of `asof` (default today)."""
    asof = asof or _today().isoformat()
    y, m = int(asof[:4]), int(asof[5:7])
    yms = _seq(*(lambda d: (d.year, d.month))(_eom(y, m).replace(day=1) - datetime.timedelta(days=365)),
               y, m)                                   # 13 months incl. current
    sales = _monthly_sales(yms)
    onhand = _onhand(asof)
    pids = [pid for pid, (q, v) in onhand.items() if q > 0.5 and v > 0]
    pm, _ = _master(pids)

    def trailing(pid, n):
        return sum(sales.get(ym, {}).get(pid, 0.0) for ym in yms[-n:])

    def last_sold(pid):
        for ym in reversed(yms):
            if sales.get(ym, {}).get(pid, 0.0) > 0:
                return f"{calendar.month_abbr[ym[1]]} {ym[0]}"
        return '12+ mo ago'

    rows = []
    for pid in pids:
        q, v = onhand[pid]
        u12, u3 = trailing(pid, 12), trailing(pid, 3)
        dsi = _dsi(q, u3)
        meta = pm.get(pid, {})
        rows.append({
            'product_id': pid, 'sku': meta.get('sku', f'#{pid}'), 'code': meta.get('sku', f'#{pid}'),
            'product': meta.get('name', ''), 'name': meta.get('name', ''),
            'category': meta.get('category', '(uncategorized)'),
            'on_hand': float(q), 'book_value': float(v), 'unit_cost': float(v / q) if q else 0.0,
            'units_12mo': float(u12), 'units_3mo': float(u3),
            'dsi': (None if dsi is None else round(dsi, 0)), 'last_sold': last_sold(pid),
            'status': _status(u12, dsi),
        })
    rows.sort(key=lambda r: -r['book_value'])

    total = sum(r['book_value'] for r in rows)
    def grp(pred):
        sub = [r for r in rows if pred(r)]
        return len(sub), sum(r['book_value'] for r in sub)
    dn, dv = grp(lambda r: r['status'].startswith('Dead'))
    en, ev = grp(lambda r: r['status'].startswith('Excess'))
    sn, sv = grp(lambda r: r['status'].startswith('Slow'))
    hn, hv = grp(lambda r: r['status'].startswith('Healthy'))
    buckets = [{'status': lbl, 'skus': n, 'book_value': val,
                'share': (val / total * 100 if total else 0)}
               for lbl, (n, val) in [('Dead (no sales 12mo)', (dn, dv)), ('Excess (>365d cover)', (en, ev)),
                                     ('Slow (120-365d)', (sn, sv)), ('Healthy (<120d)', (hn, hv))]]
    summary = {
        'asof': asof, 'window': f'{yms[0][0]}-{yms[0][1]:02d} → {yms[-1][0]}-{yms[-1][1]:02d}',
        'total_book': total,
        'trapped_value': dv + ev, 'trapped_skus': dn + en, 'trapped_share': ((dv + ev) / total * 100 if total else 0),
        'dead_value': dv, 'dead_skus': dn, 'excess_value': ev, 'excess_skus': en,
        'slow_value': sv, 'slow_skus': sn, 'healthy_value': hv, 'healthy_skus': hn,
    }
    return {'rows': rows, 'summary': summary, 'buckets': buckets}


# --------------------------------------------------------------------------- rolling cohort recovery
def cohort_recovery(asof=None):
    """For each month-end (Jan-2025 → last full month) classify the dead cohort, then track it forward:
    a SKU graduates when DSI < 120. Returns the average recovery curve + per-cohort summary."""
    asof = asof or _today()
    if isinstance(asof, str):
        asof = datetime.date.fromisoformat(asof)
    last = asof.replace(day=1) - datetime.timedelta(days=1)     # last complete month
    cohorts = _seq(2025, 1, last.year, last.month)
    smon = _seq(2024, 1, last.year, last.month)                 # trailing-12 needs a year of runway
    sales = _monthly_sales(smon)

    def trail(pid, ym, n):
        i = smon.index(ym)
        return sum(sales.get(smon[j], {}).get(pid, 0.0) for j in range(max(0, i - n + 1), i + 1))

    # on-hand (qty, value) at each cohort month-end, in parallel
    ends = [_eom(y, m).isoformat() for (y, m) in cohorts]
    states = dict(zip(cohorts, O.parallel([(lambda e=e: _onhand(e)) for e in ends])))

    # per (cohort-month, pid): value, dead?, dsi
    flat = {}
    for ym in cohorts:
        d = {}
        for pid, (q, v) in states[ym].items():
            if q <= 0.5 or v <= 0:
                continue
            dsi = _dsi(q, trail(pid, ym, 3))
            d[pid] = (v, (v > _VAL_FLOOR and trail(pid, ym, 12) < DEAD_UNITS_12M), dsi)
        flat[ym] = d

    idx = {c: i for i, c in enumerate(cohorts)}
    def graduated_by(pid, c, k):
        for t in cohorts[idx[c]:idx[c] + k + 1]:
            st = flat[t].get(pid)
            if st and st[2] is not None and st[2] < GRADUATE_DSI:
                return True
        return False

    curve_num, curve_den = {}, {}
    rows = []
    for c in cohorts:
        cv = {pid: flat[c][pid][0] for pid in flat[c] if flat[c][pid][1]}   # dead pids -> entry value
        tot = sum(cv.values())
        if not tot:
            continue
        maxk = len(cohorts) - 1 - idx[c]
        for k in range(maxk + 1):
            rec = sum(v for pid, v in cv.items() if graduated_by(pid, c, k))
            curve_num[k] = curve_num.get(k, 0.0) + rec
            curve_den[k] = curve_den.get(k, 0.0) + tot

        def pct_at(k):
            return (sum(v for pid, v in cv.items() if graduated_by(pid, c, k)) / tot * 100) if k <= maxk else None
        stuck = sum(v for pid, v in cv.items() if not graduated_by(pid, c, maxk))
        rows.append({'cohort': f"{c[0]}-{c[1]:02d}", 'skus': len(cv), 'dead_value': tot,
                     'rec_3mo': pct_at(3), 'rec_6mo': pct_at(6),
                     'rec_today': (tot - stuck) / tot * 100, 'stuck_value': stuck})

    curve = [{'months_since': k, 'recovered_pct': (curve_num[k] / curve_den[k] * 100 if curve_den[k] else 0)}
             for k in sorted(curve_num)]
    cd = {p['months_since']: p['recovered_pct'] for p in curve}
    return {'curve': curve, 'cohorts': rows,
            'rec_3mo': cd.get(3), 'rec_6mo': cd.get(6), 'rec_12mo': cd.get(12),
            'latest_stuck': (rows[-1]['stuck_value'] if rows else 0.0)}


# --------------------------------------------------------------------------- validate
def validate(asof=None):
    """Reconcile to independent Odoo aggregates."""
    asof = asof or _today().isoformat()
    d = deep(asof)
    checks = []

    # 1. layer book value (in-stock SKUs) ties to GL account 05000 balance (the audited inventory line)
    acc = O.search_read('account.account', [('code', '=', '05000')], ['id'])
    if acc:
        gl = O.sum_field('account.move.line', [('parent_state', '=', 'posted'),
                         ('account_id', '=', acc[0]['id']), ('date', '<=', asof)], 'balance')
        ours = d['summary']['total_book']
        checks.append({'check': 'On-hand book value ≈ GL 05000 Inventory',
                       'a': round(ours, 0), 'b': round(gl, 0), 'gap': round(ours - gl, 0),
                       'ok': abs(ours - gl) <= max(2500.0, abs(gl) * 0.05)})

    # 2. buckets partition the total book value exactly
    bsum = sum(b['book_value'] for b in d['buckets'])
    tot = d['summary']['total_book']
    checks.append({'check': 'Σ(status buckets) == total book value', 'a': round(bsum, 2),
                   'b': round(tot, 2), 'gap': round(bsum - tot, 2), 'ok': abs(bsum - tot) <= 1.0})

    # 3. trapped = dead + excess (definition holds)
    s = d['summary']
    checks.append({'check': 'Trapped == dead + excess value', 'a': round(s['trapped_value'], 2),
                   'b': round(s['dead_value'] + s['excess_value'], 2),
                   'gap': round(s['trapped_value'] - s['dead_value'] - s['excess_value'], 2),
                   'ok': abs(s['trapped_value'] - s['dead_value'] - s['excess_value']) <= 1.0})
    return checks