"""Build panel specs (JSON) for the 30-task dashboard from the prediction CSVs, then write the HTML prototype."""
import json, numpy as np, pandas as pd
D = '/mnt/user-data/outputs/Binzagr_Data/11_Dashboard_Data/'
r = lambda n: pd.read_csv(D + n + '.csv')
CHC = {'GR': '#1F4E8C', 'MT': '#2F8F6B', 'WS': '#8E5BB5', 'DS': '#D9733B', 'EC': '#2C9AB0'}
CHN = {'GR': 'Grocery (van)', 'MT': 'Modern trade', 'WS': 'Wholesale', 'DS': 'Discounters', 'EC': 'E-commerce'}

def sar(v, d=1):
    v = float(v); a = abs(v)
    return f"SAR {v/1e6:.{d}f}M" if a >= 1e6 else (f"SAR {v/1e3:.0f}K" if a >= 1e3 else f"SAR {v:.0f}")
pct = lambda v, d=0: f"{float(v)*100:.{d}f}%"
num = lambda v: f"{float(v):,.0f}"
def K(label, value, note='', tone='', basis='Actual'): return dict(label=label, value=value, note=note, tone=tone, basis=basis)
def clean(x):
    if isinstance(x, (float, np.floating)): return None if np.isnan(x) else round(float(x), 4)
    if isinstance(x, (np.integer,)): return int(x)
    if isinstance(x, (np.bool_,)): return bool(x)
    return x
def ser(name, data, style, color=None): return dict(name=name, data=[clean(v) for v in data], style=style, color=color)
def table(df, cols, fmts):
    rows = []
    for _, rr in df.iterrows():
        rows.append([fmts[i](rr[c]) if fmts[i] and pd.notna(rr[c]) else ('' if pd.isna(rr[c]) else str(rr[c])) for i, c in enumerate(cols)])
    return rows
P = []
def panel(task, area, title, question, kpis, chart=None, tbl=None, src='', note=''):
    P.append(dict(task=task, area=area, title=title, question=question, kpis=kpis, chart=chart, table=tbl, source=src, note=note))

# ---------------- 1 sell-in vs daily target
t = r('t01_sellin_vs_daily_target'); t = t[t.date >= '2026-09-01']
tot = t.groupby('date')[['actual_sar', 'target_sar', 'predicted_sar', 'predicted_low_sar', 'predicted_high_sar']].sum(min_count=1)
oct_ = t[(t.date >= '2026-10-01') & (t.date <= '2026-10-20')]
y = t[(t.date >= '2026-10-14') & (t.date <= '2026-10-20')]
nxt = t[(t.date > '2026-10-20') & (t.date <= '2026-10-27')]
bych = {}
for ch in CHC:
    x = t[t.channel_id == ch].set_index('date')
    bych[ch] = dict(actual=[clean(v) for v in x.actual_sar], target=[clean(v) for v in x.target_sar], pred=[clean(v) for v in x.predicted_sar],
                    low=[clean(v) for v in x.predicted_low_sar], high=[clean(v) for v in x.predicted_high_sar])
panel(1, 'Cluster performance', 'Sell-in vs daily target', 'Are we selling enough each day to hit the plan?',
      [K('1–20 Oct sell-in', sar(oct_.actual_sar.sum()), f"vs {sar(oct_.target_sar.sum())} target to date", 'warn' if oct_.actual_sar.sum() < oct_.target_sar.sum() else 'good'),
       K('Last 7 days', sar(y.actual_sar.sum()), f"target {sar(y.target_sar.sum())}"),
       K('Next 7 days', sar(nxt.predicted_sar.sum()), f"target {sar(nxt.target_sar.sum())}", basis='Predicted')],
      dict(type='line', labels=list(tot.index), series=[ser('Actual', tot.actual_sar, 'actual'), ser('Target', tot.target_sar, 'target'),
           ser('Predicted', tot.predicted_sar, 'pred'), ser('Low', tot.predicted_low_sar, 'low'), ser('High', tot.predicted_high_sar, 'high')], bych=bych, yfmt='sar'),
      src='t01_sellin_vs_daily_target.csv', note='Forecast = recent 8-week level × day-of-week pattern; shaded band is the 80% range. E-commerce lifted for 11.11 (10–12 Nov) and White Friday (20–30 Nov, peak 26–28 Nov).')

# ---------------- 2 MTD run rate
t = r('t02_mtd_run_rate')
o = t[t.month == '2026-10-01'].groupby('channel_id')[['target_sar', 'actual_to_date_sar', 'straight_line_run_rate_sar', 'predicted_month_end_sar']].sum()
n = t[t.month == '2026-11-01'].groupby('channel_id')[['target_sar', 'predicted_month_end_sar']].sum()
dv = t[t.month == '2026-10-01'].groupby('division')[['target_sar', 'predicted_month_end_sar']].sum(); dv['att'] = dv.predicted_month_end_sar / dv.target_sar
panel(2, 'Cluster performance', 'Month-to-date run rate vs target', 'Where will October land, and November?',
      [K('October landing', pct(o.predicted_month_end_sar.sum() / o.target_sar.sum()), f"{sar(o.predicted_month_end_sar.sum())} of {sar(o.target_sar.sum())}", 'warn', 'Predicted'),
       K('Straight-line run rate', pct(o.straight_line_run_rate_sar.sum() / o.target_sar.sum()), 'if the last 20 days just repeat'),
       K('November landing', pct(n.predicted_month_end_sar.sum() / n.target_sar.sum()), f"{sar(n.predicted_month_end_sar.sum())} of {sar(n.target_sar.sum())}", 'warn', 'Predicted')],
      dict(type='bar', labels=[CHN[c] for c in o.index], series=[ser('Target', o.target_sar, 'target'), ser('Actual to 20 Oct', o.actual_to_date_sar, 'actual'),
           ser('Predicted month end', o.predicted_month_end_sar, 'pred')], yfmt='sar'),
      dict(cols=['Division', 'Oct target', 'Predicted', 'Attainment'], rows=table(dv.reset_index().sort_values('att'), ['division', 'target_sar', 'predicted_month_end_sar', 'att'], [None, sar, sar, pct])),
      't02_mtd_run_rate.csv', 'October plan in the data covers 1–20 Oct; scaled to the full month. November target = October + 2% (no November plan received).')

# ---------------- 3 channel mix
t = r('t03_channel_mix'); m = t.pivot(index='channel_id', columns='month', values='share')
plan = t.drop_duplicates('channel_id').set_index('channel_id').plan_share
months = ['2026-08-01', '2026-09-01', '2026-10-01', '2026-11-01']
gap = (m['2026-11-01'] - plan) * 100
panel(3, 'Cluster performance', 'Channel mix vs plan', 'Is the business moving away from the planned channel mix?',
      [K('Grocery share, Nov', pct(m.loc['GR', '2026-11-01'], 1), f"plan {pct(plan['GR'])}", basis='Predicted'),
       K('Biggest gap, Nov', f"{CHN[gap.abs().idxmax()]}", f"{gap[gap.abs().idxmax()]:+.1f} pts vs plan", 'warn', 'Predicted'),
       K('E-commerce share, Nov', pct(m.loc['EC', '2026-11-01'], 1), 'White Friday lift', basis='Predicted')],
      dict(type='bar', stacked=True, labels=['Plan', 'Aug', 'Sep', 'Oct (to 20th + forecast)', 'Nov (forecast)'],
           series=[ser(CHN[c], [plan[c]] + [m.loc[c, mo] for mo in months], 'cat', CHC[c]) for c in CHC], yfmt='pct'),
      src='t03_channel_mix.csv')

# ---------------- 4 sell-out
t = r('t04_sell_out'); w = t[t.week_start >= '2026-07-05'].groupby('week_start')[['sales_value_sar', 'predicted_value_sar', 'predicted_low_sar', 'predicted_high_sar']].sum(min_count=1)
a = t[t.basis == 'Actual']; last4 = a[a.week_start >= '2026-09-20']
by = last4.groupby('account_id').agg(v=('sales_value_sar', 'sum'), o=('oos_day_share', 'mean')).reset_index()
pr = t[t.basis == 'Predicted'].groupby('account_id').predicted_value_sar.sum() / 7 * 4
by['p'] = by.account_id.map(pr); by['chg'] = by.p / by.v - 1
panel(4, 'Cluster performance', 'Sell-out (EPOS)', 'Is stock actually leaving the shelves at our biggest customers?',
      [K('Sell-out, last 4 weeks', sar(last4.sales_value_sar.sum()), 'key accounts 01–03 EPOS'),
       K('Out-of-stock days', pct(last4.oos_day_share.mean(), 1), 'share of store-SKU days', 'warn' if last4.oos_day_share.mean() > 0.08 else ''),
       K('Next 4 weeks', sar(pr.sum()), f"{(pr.sum()/last4.sales_value_sar.sum()-1)*100:+.1f}% vs last 4", basis='Predicted')],
      dict(type='line', labels=list(w.index), series=[ser('Actual', w.sales_value_sar, 'actual'), ser('Predicted', w.predicted_value_sar, 'pred'),
           ser('Low', w.predicted_low_sar, 'low'), ser('High', w.predicted_high_sar, 'high')], yfmt='sar'),
      dict(cols=['Account', 'Last 4 wks', 'OOS days', 'Next 4 wks'], rows=table(by, ['account_id', 'v', 'o', 'p'], [lambda v: v.replace('KA', 'Key account '), sar, lambda v: pct(v, 1), sar])),
      't04_sell_out.csv')

# ---------------- 5 POs
t = r('t05_po_tracker'); rs = r('t05b_po_rejection_reasons')
a = t[t.basis == 'Actual']; w = t[t.week_start >= '2026-08-02'].groupby('week_start').agg(po=('po_count', 'sum'), exp=('expected_po_count', 'sum'))
l4 = a[a.week_start >= '2026-09-20']
rr_ = rs.groupby('rejection_reason').rejected_lines.sum().sort_values(ascending=False)
short = l4.groupby('account_name').agg(po=('po_count', 'sum'), exp=('expected_po_count', 'sum'), rej=('rejected_line_share', 'mean'), dl=('delayed_line_share', 'mean')).reset_index()
short['gap'] = short.po - short.exp
panel(5, 'Modern trade', 'POs received vs expected', 'Are the chains ordering as normal, and how many lines get rejected or delayed?',
      [K('POs, last 4 weeks', num(l4.po_count.sum()), f"expected {num(l4.expected_po_count.sum())}"),
       K('Lines rejected', pct(l4.rejected_line_share.mean(), 1), f"top reason: {rr_.index[0]}", 'warn'),
       K('Lines delayed', pct(l4.delayed_line_share.mean(), 1), 'delivered after the requested date')],
      dict(type='bar', labels=list(w.index), series=[ser('Expected', w.exp, 'target'), ser('POs received', w.po.where(w.index <= '2026-10-11'), 'actual'),
           ser('Predicted', w.po.where(w.index > '2026-10-11'), 'pred')], yfmt='num'),
      dict(cols=['Account', 'POs (4 wks)', 'vs expected', 'Rejected', 'Delayed'], rows=table(short.sort_values('gap'), ['account_name', 'po', 'gap', 'rej', 'dl'], [None, num, lambda v: f"{v:+.0f}", lambda v: pct(v, 1), lambda v: pct(v, 1)])),
      't05_po_tracker.csv · t05b_po_rejection_reasons.csv')

# ---------------- 6 merch photo reports
t = r('t06_merch_photo_reports')
hi = t.sort_values('predicted_oos_next_visit', ascending=False).head(8)
panel(6, 'Modern trade', 'Merchandiser photo reports', 'What did the photos show, and which stores will be out of stock next visit?',
      [K('Visits done', pct(t.done_visits.sum() / t.planned_visits.sum()), f"{num(t.done_visits.sum())} of {num(t.planned_visits.sum())} planned, 28 days"),
       K('Photos per visit', f"{t.photos.sum() / t.done_visits.sum():.1f}", 'shelf images recognised'),
       K('High-risk stores', num((t.risk == 'high').sum()), 'predicted OOS above 10% next visit', 'risk', 'Predicted')],
      dict(type='bar', horizontal=True, labels=['On shelf (planogram OK)', 'Out of stock', 'Wrong pack', 'Fair share met'],
           series=[ser('Share of audited SKUs', [t.planogram_ok_rate.mean(), t.oos_rate.mean(), t.wrong_pack.sum() / t.skus_audited.sum(), (t.share_of_shelf >= t.fair_share).mean()], 'actual')], yfmt='pct'),
      dict(cols=['Store', 'Account', 'Visits', 'OOS now', 'OOS next visit'], rows=table(hi, ['outlet_id', 'account_id', 'visit_compliance', 'oos_rate', 'predicted_oos_next_visit'],
           [None, None, pct, lambda v: pct(v, 1), lambda v: pct(v, 1)])), 't06_merch_photo_reports.csv')

# ---------------- 7 promotions live
t = r('t07_promotions_live'); run = t[t.status == 'running']; up = t[t.status == 'upcoming']; ended = t[(t.status == 'ended') & (t.end_date >= '2026-09-01')]
acc_live = t[t.status == 'ended'].groupby('account_id').live_rate.mean().sort_values()
show = pd.concat([run.sort_values('live_rate'), up.sort_values('start_date')]).head(9)
show['lr'] = show.live_rate.fillna(show.predicted_live_rate)
panel(7, 'Modern trade', 'Promotions live in store', 'Are promotions actually running in the stores we pay for?',
      [K('Running now', num(len(run)), f"live in {pct(run.live_rate.mean())} of store checks", 'warn' if run.live_rate.mean() < 0.9 else ''),
       K('Starting by 30 Nov', num(len(up)), f"expected live {pct(up.predicted_live_rate.mean())}", basis='Predicted'),
       K('Price wrong at shelf', pct(1 - ended.price_correct_rate.mean()), 'Sep–Oct ended promotions', 'warn')],
      dict(type='bar', labels=list(acc_live.index), series=[ser('Live rate (ended promos)', acc_live.values, 'actual')], yfmt='pct', target=0.95),
      dict(cols=['Promotion', 'Account', 'Status', 'Dates', 'Live rate'], rows=table(show, ['promotion_id', 'account_id', 'status', 'start_date', 'lr'], [None, None, None, None, pct])),
      't07_promotions_live.csv', 'November calendar = the last four weeks of promotions repeated (no November plan in the data).')

# ---------------- 8 wholesale price levels
t = r('t08_wholesale_price_levels'); t['idx'] = t.avg_price_ea.fillna(t.predicted_price_ea) / t.list_price_ea
w = t.groupby('week_start').agg(idx=('idx', 'mean'), basis=('basis', 'first'))
fl = (t.floor_price_ea / t.list_price_ea).mean()
a = t[(t.basis == 'Actual') & (t.week_start >= '2026-09-20')]
bf = a.groupby(['sku', 'sku_name']).agg(idx=('price_index_vs_list', 'mean'), bl=('below_floor_lines', 'sum'), buyers=('buyers', 'mean')).reset_index().sort_values('idx').head(8)
panel(8, 'Wholesale', 'Wholesale price levels', 'Are wholesalers buying at the right price, or is the price slipping?',
      [K('Avg price vs list', pct(a.price_index_vs_list.mean(), 1), 'top 40 SKUs, last 4 weeks'),
       K('Lines below floor', num(t[t.basis == 'Actual'].below_floor_lines.sum()), 'since March', 'risk'),
       K('Price index, end Nov', pct(w.idx.iloc[-1], 1), f"{(w.idx.iloc[-1]-w.idx[w.basis=='Actual'].iloc[-1])*100:+.1f} pts", basis='Predicted')],
      dict(type='line', labels=list(w.index), series=[ser('Actual', w.idx.where(w.basis == 'Actual'), 'actual'), ser('Predicted', w.idx.where(w.basis == 'Predicted'), 'pred'),
           ser('Floor', [fl] * len(w), 'target')], yfmt='pct'),
      dict(cols=['SKU', 'Name', 'Price vs list', 'Below-floor lines'], rows=table(bf, ['sku', 'sku_name', 'idx', 'bl'], [None, lambda v: str(v)[:26], lambda v: pct(v, 1), num])),
      't08_wholesale_price_levels.csv')

# ---------------- 9 irregular orders
t = r('t09_irregular_orders'); f = t[t.predicted_flag]
prec = f.true_label.notna().mean(); rec = t[t.true_label.notna()].predicted_flag.mean()
by = f.groupby('reason').agg(n=('doc_no', 'size'), v=('value_sar', 'sum'))
panel(9, 'Wholesale', 'Abnormal or irregular orders', 'Which orders look wrong before they ship?',
      [K('Orders flagged', num(len(f)), f"{sar(f.value_sar.sum())} of value", 'risk', 'Predicted'),
       K('Hit rate', pct(prec), 'flags that were real problems (test labels)'),
       K('Caught', pct(rec), 'of the planted problems')],
      dict(type='bar', horizontal=True, labels=list(by.index), series=[ser('Orders flagged', by.n, 'risk')], yfmt='num'),
      dict(cols=['Order', 'Account', 'Date', 'Value', 'Why'], rows=table(f.sort_values(['anomaly_score', 'value_sar'], ascending=False).head(8), ['doc_no', 'account_id', 'doc_date', 'value_sar', 'reason'], [None, None, None, sar, None])),
      't09_irregular_orders.csv', 'Rules: ships outside Riyadh/Jeddah/Dammam, non-promo price under 73% of list, or a line 12× the normal quantity.')

# ---------------- 10 DS vs MT
t = r('t10_ds_vs_mt_price'); w = t.groupby('week_start').agg(g=('gap_pct', 'mean'), p=('predicted_gap_pct', 'mean'), u=('stores_undercutting', 'sum'))
al = t[t.stores_undercutting > 0].groupby('sku').agg(u=('stores_undercutting', 'sum'), g=('gap_pct', 'mean'), last=('week_start', 'max')).reset_index().sort_values('u', ascending=False).head(8)
panel(10, 'Discounters', 'Discounter shelf prices vs MT', 'Are discounters undercutting the chains by more than agreed?',
      [K('Avg DS price gap', pct(t.gap_pct.mean(), 1), 'per unit, vs MT shelf (agreed about −15%)'),
       K('Store undercuts', num(t.stores_undercutting.sum()), 'more than 25% below MT', 'risk'),
       K('Gap next 4 weeks', pct(t.predicted_gap_pct.mean(), 1), 'stable', basis='Predicted')],
      dict(type='line', labels=list(w.index), series=[ser('Actual gap', w.g, 'actual'), ser('Predicted', w.p, 'pred'), ser('Agreed gap', [-0.15] * len(w), 'target')], yfmt='pct'),
      dict(cols=['SKU', 'Undercut checks', 'Avg gap', 'Last seen'], rows=table(al, ['sku', 'u', 'g', 'last'], [None, num, lambda v: pct(v, 1), None])),
      't10_ds_vs_mt_price.csv')

# ---------------- 11 pack compliance
t = r('t11_pack_compliance'); top = t.sort_values('predicted_breach_prob_next_audit', ascending=False).head(8)
ac = t.groupby('account_id').agg(wp=('wrong_pack_lines', 'sum'), n=('audit_lines', 'sum')); ac['r'] = ac.wp / ac.n
panel(11, 'Discounters', 'Pack and SKU compliance', 'Are discounters stocking the discounter packs, not the MT packs?',
      [K('Stores with a wrong pack', num((t.wrong_pack_lines > 0).sum()), f"{num(t.wrong_pack_lines.sum())} MT-pack sightings since March", 'risk'),
       K('Planogram OK', pct(t.planogram_ok_rate.mean()), '120 discounter stores'),
       K('Stores likely to breach', num((t.predicted_breach_prob_next_audit > 0.05).sum()), 'above 5% chance at next audit', 'warn', 'Predicted')],
      dict(type='bar', labels=list(ac.index), series=[ser('Wrong-pack rate', ac.r, 'risk')], yfmt='pct2'),
      dict(cols=['Store', 'Account', 'Breaches', 'Breach chance next audit'], rows=table(top, ['outlet_id', 'account_id', 'wrong_pack_lines', 'predicted_breach_prob_next_audit'], [None, None, num, pct])),
      't11_pack_compliance.csv')

# ---------------- 12 salesman KPIs
t = r('t12_salesman_kpis'); a = t[t.basis == 'Actual']
w = t[t.week_start >= '2026-06-07'].groupby('week_start').agg(s=('sales_sar', 'sum'), lo=('predicted_low_sar', 'sum'), hi=('predicted_high_sar', 'sum'), b=('basis', 'first'))
l4 = a[a.week_start >= '2026-09-20']
v = l4.groupby('van_id').agg(c=('calls_made', 'sum'), p=('productive_calls', 'sum'), s=('sales_sar', 'sum'), d=('working_days', 'sum'), dv=('division', 'first')).reset_index()
v['sr'] = v.p / v.c; v['ds'] = v.s / v.p; v['cpd'] = v.c / v.d
panel(12, 'Grocery', 'Calls, strike rate and drop size', 'How productive is each van rep (by van code)?',
      [K('Strike rate', pct(l4.productive_calls.sum() / l4.calls_made.sum(), 1), 'target 80%, last 4 weeks', 'warn'),
       K('Drop size', sar(l4.sales_sar.sum() / l4.productive_calls.sum()), 'per productive call'),
       K('Calls per van-day', f"{l4.calls_made.sum() / l4.working_days.sum():.1f}", 'planned and unplanned')],
      dict(type='line', labels=list(w.index), series=[ser('Actual', w.s.where(w.b == 'Actual'), 'actual'), ser('Predicted', w.s.where(w.b == 'Predicted'), 'pred'),
           ser('Low', w.lo.where(w.b == 'Predicted'), 'low'), ser('High', w.hi.where(w.b == 'Predicted'), 'high')], yfmt='sar'),
      dict(cols=['Van', 'Division', 'Strike', 'Drop size', 'Calls/day'], rows=table(v[v.c > 40].sort_values('sr').head(8), ['van_id', 'dv', 'sr', 'ds', 'cpd'], [None, None, pct, sar, lambda v: f"{v:.1f}"])),
      't12_salesman_kpis.csv', 'Lowest strike rates shown. Rep names are never stored; vans by code only.')

# ---------------- 13 new / lost outlets
t = r('t13_outlet_movement'); ar = r('t13b_outlets_at_risk')
mm = t.groupby('month')[['new_outlets', 'lost_outlets', 'active_outlets']].sum()
ms = t[t.month == '2026-09-01']; cov = (ms.must_sell_coverage * ms.active_outlets).sum() / ms.active_outlets.sum()
lab = ['Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct*', 'Nov (forecast)']
mm = mm.iloc[1:]
panel(13, 'Grocery', 'New and lost outlets, must-sell coverage', 'Is the outlet base growing, and are shops buying the must-sell range?',
      [K('Active outlets, Sep', num(ms.active_outlets.sum()), 'bought at least once that month'),
       K('Must-sell coverage', pct(cov), 'active outlets buying 3+ must-sell SKUs (target 60%)', 'warn' if cov < 0.6 else 'good'),
       K('Likely lost in Nov', num(mm.lost_outlets.iloc[-1]), f"{num(len(ar))} outlets overdue for an order", 'risk', 'Predicted')],
      dict(type='bar', labels=lab, series=[ser('New', mm.new_outlets, 'good'), ser('Lost', -mm.lost_outlets, 'risk')], yfmt='num', predlast=True),
      dict(cols=['Outlet', 'Van', 'Days silent', 'Usual gap', 'Loss chance'], rows=table(ar.head(8), ['outlet_id', 'van_id', 'days_since_last_order', 'usual_interval_days', 'predicted_loss_prob'], [None, None, num, num, pct])),
      't13_outlet_movement.csv · t13b_outlets_at_risk.csv', 'Lost = 60 days without an order. *October to the 20th.')

# ---------------- 14 van reconciliation
t = r('t14_van_reconciliation'); a = t[t.basis == 'Actual']; p = t[t.basis == 'Predicted'].sort_values('predicted_issue_prob_next_week', ascending=False)
w = a.groupby('week_start').agg(cv=('cash_variance_sar', 'sum'), sv=('stock_variance_sar', 'sum'))
vv = a.groupby('van_id').agg(cv=('cash_variance_sar', 'sum'), sv=('stock_variance_sar', 'sum'), ev=('stock_loss_events', 'sum'), cs=('cash_short_days', 'sum'), cr=('credit_sales_sar', 'sum'))
p = p.join(vv, on='van_id').head(8)
panel(14, 'Grocery', 'Van stock, cash and credit reconciliation', 'Does every van balance at the end of the day?',
      [K('Cash short, 12 weeks', sar(-a.cash_variance_sar.sum()), f"{num(a.cash_short_days.sum())} short days", 'risk'),
       K('Stock unexplained', sar(-a.stock_variance_sar.sum()), f"{num(a.stock_loss_events.sum())} loss lines"),
       K('Credit sales, 12 weeks', sar(a.credit_sales_sar.sum()), f"{pct(a.credit_sales_sar.sum()/(a.cash_sales_sar.sum()+a.card_sales_sar.sum()+a.credit_sales_sar.sum()))} of van sales")],
      dict(type='bar', labels=list(w.index), series=[ser('Cash variance', w.cv, 'risk'), ser('Stock variance (value)', w.sv, 'warn')], yfmt='sar'),
      dict(cols=['Van', 'Division', 'Short days', 'Stock loss lines', 'Issue chance next wk'], rows=table(p, ['van_id', 'division', 'cs', 'ev', 'predicted_issue_prob_next_week'], [None, None, num, num, pct])),
      't14_van_reconciliation.csv')

# ---------------- 15 e-com orders
t = r('t15_ecom_orders'); dn = r('t15b_ecom_listings_down'); dn['platform_id'] = dn.platform_id.map({f'EC0{i}': f'Platform {c}' for i, c in zip(range(1, 5), 'ABCD')})
w = t[t.week_start >= '2026-07-05'].pivot_table(index='week_start', columns='platform_name', values=['orders', 'predicted_orders'], aggfunc='sum')
plats = sorted(t.platform_name.unique()); cols = ['#1F4E8C', '#2F8F6B', '#8E5BB5', '#D9733B']
series = []
for i, p_ in enumerate(plats):
    act = w[('orders', p_)].where(w.index <= '2026-10-11'); pr_ = w[('predicted_orders', p_)].where(w.index > '2026-10-11')
    series.append(ser(p_, act.replace(0, np.nan), 'cat', cols[i])); series.append(ser(p_ + ' forecast', pr_.replace(0, np.nan), 'catpred', cols[i]))
l4 = t[(t.basis == 'Actual') & (t.week_start >= '2026-09-20')]
panel(15, 'E-commerce', 'Orders by platform, stock-outs, inactive listings', 'How are the platforms trading, and what is switched off?',
      [K('Orders, last 4 weeks', num(l4.orders.sum()), sar(l4.gmv_sar.sum()) + ' GMV for our SKUs'),
       K('Listing-days out of stock', pct(l4.oos_days.sum() if 'oos_days' in l4 else l4.oos_listing_share.mean(), 1), 'on the platforms', 'warn'),
       K('Listings down today', num(len(dn)), f"≈{sar(dn.est_lost_gmv_per_day_sar.sum())}/day GMV lost", 'risk')],
      dict(type='line', labels=list(w.index), series=series, yfmt='num'),
      dict(cols=['Platform', 'SKU', 'Status', 'Days down (30)', 'Lost/day'], rows=table(dn.head(8), ['platform_id', 'sku_name', 'listing_status', 'bad_days_last_30', 'est_lost_gmv_per_day_sar'], [None, lambda v: str(v)[:24], None, num, sar])),
      't15_ecom_orders.csv · t15b_ecom_listings_down.csv', 'Platforms are anonymised (Platform A–D). Forecast lift vs a normal week: 11.11 week +27%, White Friday week (22–28 Nov) +44%.')

PLN = {f'EC0{i}': f'Platform {c}' for i, c in zip(range(1, 5), 'ABCD')}
# ---------------- 16 rank, rating, ROAS
t = r('t16_ecom_rank_rating_roas')
t['platform_id'] = t.platform_id.map(PLN)
pl = t.groupby('platform_id').agg(rk=('rank_last_7d', 'median'), rt=('rating', 'mean'), sp=('ad_spend_28d_sar', 'sum'), sa=('ad_sales_28d_sar', 'sum'))
act = t[t.action != ''].copy() if t.action.notna().any() else t.head(0)
act = t[t.action.notna()].sort_values('gmv_28d_sar', ascending=False).head(8)
panel(16, 'E-commerce', 'Search ranking, ratings, ad return', 'Can shoppers find us, do they like us, and do ads pay back?',
      [K('Average rating', f"{t.rating.mean():.2f} ★", f"{num((t.rating < 3.8).sum())} listings under 3.8"),
       K('Ad return (ROAS)', f"{t.ad_sales_28d_sar.sum()/t.ad_spend_28d_sar.sum():.1f}×", 'last 28 days'),
       K('Listings losing rank', num((t.rank_trend_3w > 15).sum()), 'down 15+ places in 3 weeks', 'warn', 'Predicted')],
      dict(type='bar', labels=list(pl.index), series=[ser('ROAS', pl.sa / pl.sp, 'actual')], yfmt='x'),
      dict(cols=['Platform', 'SKU', 'Rank now', 'Next week', 'Action'], rows=table(act, ['platform_id', 'sku_name', 'rank_last_7d', 'predicted_rank_next_week', 'action'], [None, lambda v: str(v)[:22], num, num, None])),
      't16_ecom_rank_rating_roas.csv', 'Table: listings that need an action, biggest sellers first (not the 123 losing rank).')

# ---------------- 17 promo uplift
t = r('t17_promo_uplift'); e = t[t.measured_uplift_pct.notna()]
mech = e.groupby('mechanic').agg(pl=('planned_uplift_pct', 'mean'), me=('measured_uplift_pct', 'median'))
up = t[t.status == 'upcoming'].sort_values('start_date').head(8)
panel(17, 'Pricing and promotions', 'Promotion uplift vs plan', 'Do promotions deliver the extra volume we planned?',
      [K('Measured uplift (median)', pct(e.measured_uplift_pct.median()), f"plan {pct(e.planned_uplift_pct.median())}"),
       K('Below plan', pct((e.measured_uplift_pct < e.planned_uplift_pct).mean()), 'of measured promotions', 'warn'),
       K('Upcoming promos, to 30 Nov', num((t.status == 'upcoming').sum()), f"expected uplift {pct(t[t.status=='upcoming'].predicted_uplift_pct.median())}", basis='Predicted')],
      dict(type='bar', labels=list(mech.index), series=[ser('Planned', mech.pl, 'target'), ser('Measured', mech.me, 'actual')], yfmt='pct'),
      dict(cols=['Promotion', 'Account', 'Starts', 'Mechanic', 'Plan', 'Expected'], rows=table(up, ['promotion_id', 'account_id', 'start_date', 'mechanic', 'planned_uplift_pct', 'predicted_uplift_pct'], [None, None, None, None, pct, pct])),
      't17_promo_uplift.csv', 'Measured = units in promo days vs the 4 weeks before, same account and SKUs.')

# ---------------- 18 trade spend
t = r('t18_trade_spend'); m = t.groupby('month')[['budget_sar', 'accrual_sar', 'claimed_sar', 'approved_sar', 'disputed_claims']].sum(min_count=1)
m = m[m.index >= '2026-04-01']
ac = t[t.basis == 'Actual'].groupby('account_name').agg(b=('budget_sar', 'sum'), a=('accrual_sar', 'sum'), c=('claimed_sar', 'sum'), d=('disputed_claims', 'sum')).reset_index()
ac['u'] = ac.a / ac.b
nov = t[t.basis == 'Predicted']
panel(18, 'Pricing and promotions', 'Trade spend vs budget and claims', 'Are we spending the promo budget well, and are claims fair?',
      [K('Spend vs budget, YTD', pct(ac.a.sum() / ac.b.sum()), f"{sar(ac.a.sum())} of {sar(ac.b.sum())}"),
       K('Disputed claims', num(ac.d.sum()), f"out of {sar(ac.c.sum())} claimed", 'warn'),
       K('November spend', sar(nov.accrual_sar.sum()), f"budget {sar(nov.budget_sar.sum())}", basis='Predicted')],
      dict(type='bar', labels=list(m.index), series=[ser('Budget', m.budget_sar, 'target'), ser('Spend (accrued)', m.accrual_sar, 'actual'), ser('Claimed', m.claimed_sar, 'warn')], yfmt='sar', predlast=True),
      dict(cols=['Account', 'Budget', 'Spent', 'Used', 'Disputes'], rows=table(ac.sort_values('d', ascending=False).head(8), ['account_name', 'b', 'a', 'u', 'd'], [None, sar, sar, pct, num])),
      't18_trade_spend.csv')

# ---------------- 19 competitor
t = r('t19_competitor_activity'); rc = r('t19b_competitor_events_recent')
pc = t[t.event_type == 'price cut'].sort_values('predicted_prob_next_30d', ascending=False).head(8)
ev = rc.groupby('event_type').size()
panel(19, 'Pricing and promotions', 'Competitor launches and price cuts', 'What are competitors doing, and who is likely to cut price next?',
      [K('Events, last 45 days', num(len(rc)), f"{num(ev.get('price cut', 0))} price cuts · {num(ev.get('launch', 0))} launches"),
       K('Avg price-cut depth', pct(rc[rc.event_type == 'price cut'].depth_pct.mean()), 'reported by the field'),
       K('Likely cuts next 30 days', num((t[t.event_type == 'price cut'].predicted_prob_next_30d > 0.5).sum()), 'brand × category above 50%', 'warn', 'Predicted')],
      dict(type='bar', labels=list(ev.index), series=[ser('Events (45 days)', ev.values, 'actual')], yfmt='num'),
      dict(cols=['Competitor', 'Category', 'Cuts so far', 'Chance next 30d'], rows=table(pc, ['brand', 'category', 'events', 'predicted_prob_next_30d'], [None, None, num, pct])),
      't19_competitor_activity.csv · t19b_competitor_events_recent.csv', 'Competitors are anonymised (Competitor A–L).')

# ---------------- 20 stock cover
t = r('t20_stock_cover'); so = t[t.status == 'stock-out risk'].sort_values('cover_incl_transit_days').head(8)
st = t.status.value_counts()
panel(20, 'Supply and stock', 'Warehouse stock cover vs forecast', 'Will we run out, or are we sitting on too much?',
      [K('Stock-out risk', num(st.get('stock-out risk', 0)), 'SKU-locations with cover below lead time', 'risk', 'Predicted'),
       K('Overstock value', sar(t[t.status == 'overstock'].stock_value_sar.sum()), f"{num(st.get('overstock', 0))} SKU-locations over 90 days", 'warn'),
       K('Median cover', f"{t.cover_days.median():.0f} days", 'on-hand vs forecast demand')],
      dict(type='bar', horizontal=True, labels=list(st.index), series=[ser('SKU-locations', st.values, 'actual')], yfmt='num'),
      dict(cols=['SKU', 'Warehouse', 'Cover (days)', 'Lead time', 'Runs out'], rows=table(so, ['sku_name', 'warehouse_id', 'cover_incl_transit_days', 'lead_time_days', 'projected_stockout_date'], [lambda v: str(v)[:24], None, lambda v: f"{v:.0f}", num, None])),
      't20_stock_cover.csv')

# ---------------- 21 fill / OTIF
t = r('t21_fill_otif'); t = t[t.week_start >= '2026-07-05']
cr = [c for c in ['no stock', 'truck capacity', 'late picking', 'quality hold', 'customer refusal'] if c in t]
series = []
for ch in ['MT', 'DS', 'WS', 'EC']:
    x = t[t.channel_id == ch].set_index('week_start')
    series.append(ser(CHN[ch], x.otif.where(x.basis == 'Actual'), 'cat', CHC[ch])); series.append(ser(CHN[ch] + ' forecast', x.otif.where(x.basis == 'Predicted'), 'catpred', CHC[ch]))
a = t[(t.basis == 'Actual') & (t.week_start >= '2026-09-20')]
cut = a[cr].sum().sort_values(ascending=False)
panel(21, 'Supply and stock', 'Fill rate and OTIF with cut reasons', 'Do customers get the full order on time, and why not?',
      [K('OTIF, last 4 weeks', pct((a.otif * a.lines).sum() / a.lines.sum(), 1), 'order lines on time and in full', 'warn'),
       K('Case fill', pct(a.delivered_cases.sum() / a.ordered_cases.sum(), 1), 'cases delivered / ordered'),
       K('Top cut reason', cut.index[0], f"{pct(cut.iloc[0]/cut.sum())} of cut lines", 'risk')],
      dict(type='line', labels=sorted(t.week_start.unique()), series=series, yfmt='pct'),
      dict(cols=['Cut reason', 'Lines (4 wks)', 'Share'], rows=[[k, num(v), pct(v / cut.sum())] for k, v in cut.items()]),
      't21_fill_otif.csv')

# ---------------- 22 customer stock
t = r('t22_customer_stock_expiry'); top = t.sort_values('predicted_returns_next_30d_sar', ascending=False).head(8)
panel(22, 'Supply and stock', 'Customer stock, near-expiry, pending returns', 'What stock in our customers’ stores is about to come back to us?',
      [K('Near-expiry units', num(t.near_expiry_units.sum()), 'in key-account stores now', 'warn'),
       K('Returns pending', num(t.pending_return_units.sum()), 'requested, not yet authorised'),
       K('Returns next 30 days', sar(t.predicted_returns_next_30d_sar.sum()), 'expected value', 'risk', 'Predicted')],
      dict(type='bar', labels=list(t.groupby('division').predicted_returns_next_30d_sar.sum().index), series=[ser('Expected returns (SAR)', t.groupby('division').predicted_returns_next_30d_sar.sum().values, 'risk')], yfmt='sar'),
      dict(cols=['Account', 'SKU', 'Near expiry', 'Pending', 'Expected return'], rows=table(top, ['account_id', 'sku_name', 'near_expiry_units', 'pending_return_units', 'predicted_returns_next_30d_sar'], [None, lambda v: str(v)[:22], num, num, sar])),
      't22_customer_stock_expiry.csv')

# ---------------- 23 demand shifts
t = r('t23_demand_shifts'); f = t[t.share_with_planning]
dv = f.groupby('division').agg(up=('unit_change', lambda s: (s > 0).sum()), dn=('unit_change', lambda s: (s < 0).sum()))
panel(23, 'Supply and stock', 'Demand shifts to share with planning', 'Which November forecasts have moved enough to tell planning?',
      [K('Shifts to share', num(len(f)), f"of {num(len(t))} SKU-locations", 'warn', 'Predicted'),
       K('Up vs plan', num((f.unit_change > 0).sum()), f"+{num(f[f.unit_change > 0].unit_change.sum())} units", basis='Predicted'),
       K('Down vs plan', num((f.unit_change < 0).sum()), f"{num(f[f.unit_change < 0].unit_change.sum())} units", basis='Predicted')],
      dict(type='bar', labels=list(dv.index), series=[ser('Up', dv.up, 'good'), ser('Down', -dv.dn, 'risk')], yfmt='num'),
      dict(cols=['SKU', 'Location', 'Plan', 'New forecast', 'Shift', 'Why'], rows=table(f.reindex(f.unit_change.abs().sort_values(ascending=False).index).head(8),
           ['sku_name', 'location', 'final_plan_units', 'new_forecast_units', 'shift_pct', 'driver'], [lambda v: str(v)[:20], None, num, num, lambda v: f"{v*100:+.0f}%", None])),
      't23_demand_shifts.csv')

# ---------------- 24 receivables
t = r('t24_receivables'); cb = r('t24b_credit_blocks_monthly')
bk = ['bucket_not due', 'bucket_1-30', 'bucket_31-60', 'bucket_61-90', 'bucket_90+']
panel(24, 'Customers and finance', 'Overdue receivables and credit blocks', 'Who owes us, how late, and what will come in this month?',
      [K('Overdue', sar(t.overdue_sar.sum()), f"of {sar(t.open_sar.sum())} open at 20 Oct", 'risk'),
       K('Expected in 30 days', sar(t.expected_collection_30d_sar.sum()), 'from each customer’s payment habit', basis='Predicted'),
       K('Customers on credit block', num(t.credit_blocked.sum()), f"{num(cb.blocks.sum())} blocks since March")],
      dict(type='bar', labels=['Not due', '1–30', '31–60', '61–90', '90+'], series=[ser('Open SAR', [t[b].sum() for b in bk], 'actual')], yfmt='sar'),
      dict(cols=['Customer', 'Channel', 'Overdue', 'Half paid by', '30-day chance'], rows=table(t.head(8), ['customer_name', 'channel_id', 'overdue_sar', 'predicted_pay_date', 'prob_pay_within_30d'], [lambda v: str(v)[:18], None, sar, None, pct])),
      't24_receivables.csv · t24b_credit_blocks_monthly.csv', 'All balances here are simulated, grocery included. Binzagr’s real grocery ageing (SAR 3.82M book, SAR 591K past due) is on the Receivables screen.')

# ---------------- 25 deductions
t = r('t25_deductions_disputes'); op = t[t.status == 'open']
by = t.groupby('reason').deduction_sar.sum().sort_values()
panel(25, 'Customers and finance', 'Claims, deductions and disputes', 'What have customers deducted, and how much can we win back?',
      [K('Open cases', num(len(op)), sar(op.deduction_sar.sum()) + ' held back', 'warn'),
       K('Recoverable', sar(op.predicted_recovery_sar.sum()), 'expected from open cases', 'good', 'Predicted'),
       K('Median days to resolve', f"{t[t.status != 'open'].days_open.median():.0f}", 'closed cases')],
      dict(type='bar', horizontal=True, labels=list(by.index), series=[ser('Deducted (SAR)', by.values, 'actual')], yfmt='sar'),
      dict(cols=['Case', 'Customer', 'Reason', 'Amount', 'Valid chance'], rows=table(op.sort_values('deduction_sar', ascending=False).head(8), ['case_id', 'customer_id', 'reason', 'deduction_sar', 'predicted_valid_prob'], [None, None, lambda v: str(v)[:22], sar, pct])),
      't25_deductions_disputes.csv')

# ---------------- 26 escalations
t = r('t26_escalations'); rk = r('t26b_account_risk')
op = t[t.status.str.startswith('open')]
ty = t.groupby('type').size().sort_values()
panel(26, 'Customers and finance', 'Key account escalations', 'Which customer relationships need the manager this month?',
      [K('Open escalations', num(len(op)), f"{num((op.status == 'open - overdue').sum())} past due date", 'risk'),
       K('Value at risk (open)', sar(op.value_at_risk_sar.sum())),
       K('Accounts likely to escalate', num((rk.predicted_escalation_prob_30d > 0.3).sum()), 'above 30% in next 30 days', 'warn', 'Predicted')],
      dict(type='bar', horizontal=True, labels=list(ty.index), series=[ser('Cases since March', ty.values, 'actual')], yfmt='num'),
      dict(cols=['Account', 'Channel', 'Chance (30d)', 'Main driver'], rows=table(rk.head(8), ['account_name', 'channel_id', 'predicted_escalation_prob_30d', 'main_driver'], [None, None, pct, None])),
      't26_escalations.csv · t26b_account_risk.csv')

# ---------------- 27 coverage
t = r('t27_coverage'); t = t[t.date >= '2026-08-20']
vr = t[t.role == 'Van rep'].set_index('date'); mr = t[t.role == 'Merchandiser'].set_index('date')
labs = sorted(t.date.unique())
a = t[(t.basis == 'Actual') & (t.date >= '2026-09-21')]
panel(27, 'Team', 'Uncovered routes, absent merchandisers', 'Which routes and stores have nobody today?',
      [K('Uncovered routes / day', f"{a[a.role=='Van rep'].uncovered_routes.mean():.1f}", 'last 4 weeks', 'warn'),
       K('Merchandiser gaps / day', f"{a[a.role=='Merchandiser'].uncovered_merch_days.mean():.1f}", f"{int(t[t.role=='Merchandiser'].vacant.max())} posts vacant", 'risk'),
       K('Next 3 weeks', f"{t[(t.basis=='Predicted')].uncovered_routes.sum():.0f}", 'route-days expected uncovered', basis='Predicted')],
      dict(type='line', labels=labs, series=[ser('Uncovered routes', vr.uncovered_routes.reindex(labs).where(vr.basis.reindex(labs) == 'Actual'), 'actual'),
           ser('Forecast', vr.uncovered_routes.reindex(labs).where(vr.basis.reindex(labs) == 'Predicted'), 'pred'),
           ser('Uncovered merchandiser days', mr.uncovered_merch_days.reindex(labs), 'risk')], yfmt='num1'),
      src='t27_coverage.csv', note='Vacancies from 1 Aug (3 merchandiser posts). Fridays excluded.')

# ---------------- 28 meetings
t = r('t28_meetings'); t = t[t.week_start >= '2026-07-05']
w = t.groupby(['week_start', 'role']).hours.sum().unstack().fillna(0)
a = t[(t.basis == 'Actual') & (t.week_start >= '2026-09-20')]
ty = a.groupby('type').agg(n=('meetings', 'sum'), v=('value_at_stake_sar', 'sum'), ag=('agreed', 'sum')).sort_values('n', ascending=False)
rc = {'Key account manager': '#1F4E8C', 'Sales supervisor': '#2F8F6B', 'Channel manager GR': '#8E5BB5', 'Channel manager MT/DS': '#D9733B', 'Channel manager WS/EC': '#2C9AB0'}
panel(28, 'Team', 'KAM and supervisor meetings', 'Are the right meetings happening, and what do they win?',
      [K('Meetings, last 4 weeks', num(a.meetings.sum()), f"{num(a.hours.sum())} hours"),
       K('Value negotiated', sar(a.value_at_stake_sar.sum()), 'promotion, range and plan meetings'),
       K('Agreed', pct(a.agreed.sum() / a[a.value_at_stake_sar > 0].meetings.sum()), 'of negotiations fully or partly agreed', 'good')],
      dict(type='bar', stacked=True, labels=list(w.index), series=[ser(c, w[c], 'cat', rc.get(c)) for c in w.columns], yfmt='num', predafter='2026-10-11'),
      dict(cols=['Type', 'Meetings', 'Value at stake'], rows=table(ty.reset_index().head(8), ['type', 'n', 'v'], [None, num, lambda v: sar(v) if v else '—'])),
      't28_meetings.csv', 'Hours per week by role; weeks after 11 Oct are forecast.')

# ---------------- 29 action log
t = r('t29_action_log_open'); b = r('t29b_action_backlog'); b = b[b.week_start >= '2026-06-07']
panel(29, 'Team', 'Action log and approvals', 'What is waiting on whom, and will it slip?',
      [K('Open actions', num(len(t)), f"{num((t.status == 'overdue').sum())} overdue · raised in last 90 days", 'risk'),
       K('Median wait', f"{t.waiting_days.median():.0f} days", f"{num((t.waiting_days > 30).sum())} items older than 30 days"),
       K('Backlog end Nov', num(b.backlog.iloc[-1]), 'open items, 6 weeks out', 'warn', 'Predicted')],
      dict(type='line', labels=list(b.week_start), series=[ser('Open backlog', b.backlog.where(b.basis == 'Actual'), 'actual'), ser('Forecast', b.backlog.where(b.basis == 'Predicted'), 'pred')], yfmt='num'),
      dict(cols=['Action', 'Type', 'Owner', 'Waiting', 'Miss chance'], rows=table(t.sort_values('waiting_days', ascending=False).head(8), ['action_id', 'source', 'owner_id', 'waiting_days', 'prob_miss_due_date'], [None, lambda v: str(v).replace('recommendation: ', '')[:26], None, num, pct])),
      't29_action_log_open.csv · t29b_action_backlog.csv')

# ---------------- 30 manager time
t = r('t30_manager_time'); a = t[(t.basis == 'Actual') & (t.week_start >= '2026-09-20')]
sh = a.groupby('activity').hours.sum() / a.hours.sum(); tg = t.drop_duplicates('activity').set_index('activity').target_share.reindex(sh.index)
sh = sh.sort_values(); tg = tg.reindex(sh.index)
wk_h = a.groupby(['employee_id', 'week_start']).hours.sum().mean()
panel(30, 'Team', 'Where the manager spends time', 'Is the manager’s week spent on customers and the field, or on reports?',
      [K('Hours per week', f"{wk_h:.0f}", 'per channel manager'),
       K('Customer + field time', pct(sh.get('key account meetings', 0) + sh.get('field visits', 0)), 'suggested 45%', 'warn'),
       K('Reports + admin', pct(sh.get('internal reviews / reporting', 0) + sh.get('admin / email', 0)), 'suggested 30%', 'risk')],
      dict(type='bar', horizontal=True, labels=list(sh.index), series=[ser('Actual share', sh.values, 'actual'), ser('Suggested', tg.values, 'target')], yfmt='pct'),
      src='t30_manager_time.csv', note='From manager calendars (simulated). Suggested mix is our proposal, not Binzagr’s.')

AREAS = ['Cluster performance', 'Modern trade', 'Wholesale', 'Discounters', 'Grocery', 'E-commerce', 'Pricing and promotions', 'Supply and stock', 'Customers and finance', 'Team']
json.dump(dict(as_of='20 Oct 2026', horizon='to 30 Nov 2026', areas=AREAS, panels=P), open('/home/claude/binzagr/sim/_panels.json', 'w'), default=clean)
print(len(P), 'panels')