"""Reorder points that prevent stock-outs. Data: UCI Online Retail II (Chen, 2019), a UK online giftware wholesaler, 1,067,371 invoice lines,
Dec 2009 to Dec 2011. Weekly unit demand for the 300 best-selling stock codes. Simulation of weekly ordering with a 2-week supplier lead time,
lost sales when out of stock, warm-up in 2010, test on 2011. Two policies, each run at several buffer levels to draw the stock/fill trade-off:
  rule: order up to (lead + review) x last-4-week average x (1 + buffer)      -- the common spreadsheet method
  model: order up to (lead + review) x EWMA forecast + z x error spread x sqrt(lead + review)."""
import pandas as pd, numpy as np, json, os
cache = '/workspace/cs6/data/retail_weekly.parquet'
if not os.path.exists(cache):
    a = pd.concat([pd.read_excel('/workspace/cs4/data/online_retail_II.xlsx', sheet_name=s) for s in [0, 1]])
    a = a[(a.Quantity > 0) & (a.Price > 0) & a.StockCode.astype(str).str.match(r'^\d{5}')]
    a['wk'] = pd.to_datetime(a.InvoiceDate).dt.to_period('W-SUN').dt.start_time
    top = a.groupby('StockCode').Quantity.sum().nlargest(300).index
    w = a[a.StockCode.isin(top)].groupby(['StockCode', 'wk']).agg(q=('Quantity', 'sum')).reset_index()
    pr = a[a.StockCode.isin(top)].groupby('StockCode').Price.median().rename('price').reset_index()
    w = w.merge(pr, on='StockCode'); w['StockCode'] = w.StockCode.astype(str); w.to_parquet(cache); print('lines', len(a))
w = pd.read_parquet(cache); W = w.pivot_table(index='wk', columns='StockCode', values='q', aggfunc='sum').fillna(0); W = W.iloc[1:-1]
price = w.groupby('StockCode').price.first().reindex(W.columns).values * .6  # stock valued at 60% of median sale price (cost proxy)
D = W.values; T, K = D.shape; weeks = W.index; t0 = int(np.searchsorted(weeks, pd.Timestamp('2011-01-03'))); L, Rv = 2, 1
def sim(policy, lvl):
    inv = np.zeros(K); pipe = [np.zeros(K) for _ in range(L)]; ew = D[:8].mean(0); err = np.zeros((T, K)); fill_d = fill_s = 0; so_weeks = 0; stock_val = 0; n = 0
    for t in range(T):
        inv += pipe.pop(0); dem = D[t]; sold = np.minimum(inv, dem); inv -= sold
        if t >= 8:
            err[t] = dem - ew; ew = .3 * dem + .7 * ew
        if t >= t0:
            fill_d += dem.sum(); fill_s += sold.sum(); so_weeks += ((dem > sold) & (dem > 0)).sum(); stock_val += (inv * price).sum(); n += 1
        H = L + Rv
        if policy == 'rule': target = H * D[max(0, t - 3): t + 1].mean(0) * (1 + lvl)
        else:
            sd = err[max(8, t - 25): t + 1].std(0) if t > 12 else D[:t + 1].std(0); target = H * ew + lvl * sd * np.sqrt(H)
        onorder = sum(pipe); q = np.maximum(target - inv - onorder, 0); pipe.append(q)
    return dict(fill=fill_s / fill_d, so=so_weeks / (n * K), stock=stock_val / n, lost=float(fill_d - fill_s))
rule = {b: sim('rule', b) for b in [0, .25, .5, .75, 1.0, 1.5, 2.0]}; mod = {z: sim('model', z) for z in [0, .5, 1.0, 1.28, 1.65, 2.0, 2.5]}
# matched comparison: model at the same average stock as the rule with 50% buffer
tgt = rule[.5]['stock']; zs = sorted(mod); zi = min(zs, key=lambda z: abs(mod[z]['stock'] - tgt))
cv = (W.iloc[t0:].std() / W.iloc[t0:].mean().replace(0, np.nan)).describe()
ex = W.columns[np.argsort(-(W.iloc[t0:].sum().values * price))[:3]]
out = dict(skus=K, weeks=T, test_weeks=T - t0, test_start=str(weeks[t0].date()), end=str(weeks[-1].date()), units_test=float(D[t0:].sum()), rule={str(k): v for k, v in rule.items()}, model={str(k): v for k, v in mod.items()},
  matched=dict(rule=rule[.5], model=mod[zi], z=zi), cv_median=float(cv['50%']), weekly_total=[[str(i.date()), float(v)] for i, v in W.sum(1).items()],
  examples={c: [[str(i.date()), float(v)] for i, v in W[c].items()] for c in ex}, desc=w.drop_duplicates('StockCode').set_index('StockCode').price.reindex(ex).to_dict())
json.dump(out, open('/workspace/cs6/out/stock.json', 'w'), indent=1)
for k, v in rule.items(): print('rule', k, {a: round(b, 4) for a, b in v.items()})
for k, v in mod.items(): print('model', k, {a: round(b, 4) for a, b in v.items()})
print('matched', out['matched'], 'cv', cv['50%'], K, T, t0)
