"""
Polleo Demand Forecasting Engine v3.6
======================================
Changes in v3.6:
  - Fixed cannibalization: proportional scaling by on-top/forecast ratio
    (small on-tops no longer trigger full cannibalization reduction)
  - Fixed Demand Planning filter: Artikl column filled on all rows per SKU block
    (enables filtering by product name to show all detail rows)
  - Added Review column: flags SKUs where avg FC (4wk) deviates ±25% from
    avg run rate (last 4wk). Value written on all 11 rows for filtering.

Changes in v3.5:
  - Added SeasonIdx model: 26-week avg with weekly seasonal indices + trend
    (Kaufland UDF-style approach: base × seasonal_index × trend_factor)
  - SeasonIdx competes in the backtest alongside all other models
  - CW_START = current ISO week + 1 (calendar-anchored, run on Monday)
  - Run Rate built from sales_clean.csv (last 26 weeks ending at current CW)
  - Latest price per SKU for revenue calculation (most recent week's price)
  - Preserve planner corrections on re-run: planner factors, VP/MP on-tops
    are read from existing Polleo_Demand_Plan.xlsx and carried forward for
    overlapping weeks. Only new weeks get defaults.
  - Actuals row uses real last 26 weeks from sales_clean.csv

Requires: sales_clean.csv, sku_uplift.csv, cat_uplift.csv,
          sku_prices.csv, sku_category_map.csv, sku_subcat_map.csv
          + Polleo_Demand_Planning_Book.xlsx (for SKU list, first run)
          + Polleo_Demand_Plan.xlsx (optional, for preserving corrections)
Output:   Polleo_Demand_Plan.xlsx
"""

import numpy as np, pandas as pd, warnings, sys, os, glob
from scipy.optimize import minimize_scalar
from scipy.stats import trim_mean
from sklearn.ensemble import HistGradientBoostingRegressor
from sklearn.preprocessing import LabelEncoder
from sklearn.model_selection import ParameterGrid
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter as CL
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import SeriesLabel
from datetime import datetime, timedelta
warnings.filterwarnings('ignore')

# v3.7-db: Postgres-backed data loaders. Each loader is CSV-first when a local
# CSV exists (preserves bit-exact behaviour) and falls back to Postgres via
# forecast_db.py. The math/model/guardrail code is untouched.
try:
    import forecast_db as _fdb
    HAS_DB_LOADERS = True
except Exception as _exc:
    print(f'  [WARN] forecast_db.py import failed: {_exc} — using direct CSV reads only')
    _fdb = None
    HAS_DB_LOADERS = False

# StatsForecast (v3.5)
try:
    from statsforecast import StatsForecast as SF
    from statsforecast.models import (
        AutoARIMA, AutoCES, AutoTheta, Naive,
        CrostonOptimized, ADIDA, IMAPA, TSB
    )
    HAS_SF = True
except ImportError:
    HAS_SF = False
    print('  [WARN] statsforecast not installed — using engine models only')

# ============ MODELS (v3.4.1: recency-weighted, focused on last ~13 weeks) ============
ALPHA_LO, ALPHA_HI = .15, .75  # recency bounds (0.75 = responsive but not hyper-reactive)

def ses_forecast(y,h=13):
    n=len(y)
    if n<3:return np.full(h,np.mean(y) if n>0 else 0)
    tw=np.array([0.95**max(0,n-1-t) for t in range(n)])
    def sse(a):
        a=max(ALPHA_LO,min(ALPHA_HI,a));l=y[0];e=0
        for t in range(1,n):e+=tw[t]*(y[t]-l)**2;l=a*y[t]+(1-a)*l
        return e
    al=minimize_scalar(sse,bounds=(ALPHA_LO,ALPHA_HI),method='bounded').x;l=y[0]
    for t in range(1,n):l=al*y[t]+(1-al)*l
    # Floor: don't drop below 50% of recent 8-week median
    nz=y[-min(8,n):];nz=nz[nz>0]
    floor=np.median(nz)*0.5 if len(nz)>=3 else 0
    return np.full(h,max(floor,l))

def croston_forecast(y,h=13):
    d,iv,li=[],[],-1
    for t in range(len(y)):
        if y[t]>0:d.append(y[t]);(iv.append(t-li) if li>=0 else None);li=t
    if len(d)<2:return np.full(h,(np.mean(y[y>0]) if np.sum(y>0)>0 else 0)*.5)
    a=.25;z,p=d[0],(iv[0] if iv else 1)  # raised from 0.15 to 0.25
    for i in range(1,len(d)):z=a*d[i]+(1-a)*z;p=(a*iv[i]+(1-a)*p) if i<len(iv) else p
    return np.full(h,max(0,z/max(p,1)))

def wma_capped_forecast(y,h=13):
    n=len(y)
    if n<4:return np.full(h,np.mean(y))
    nz=y[y>0];med=np.median(nz) if len(nz)>0 else np.mean(y)
    # Use last 6 weeks (was 4), heavier weights on most recent
    k=min(6,n)
    lk=np.clip(y[-k:],.5*med,2*med) if med>0 else y[-k:]
    w=np.arange(1,k+1,dtype=float)  # [1,2,3,4,5,6]
    return np.full(h,max(0,np.average(lk,weights=w)))

def hybrid_fc(y,h=13):
    if len(y)<4:return np.full(h,np.mean(y) if len(y)>0 else 0)
    # Use last 13 weeks for trimmed mean (was full history)
    recent=y[-min(13,len(y)):]
    cr=trim_mean(recent,.075);nz=y[y>0];med=float(np.median(nz)) if len(nz)>0 else float(np.mean(y))
    k=min(6,len(y))
    lk=np.clip(y[-k:].astype(float),.5*med,2*med) if med>0 else y[-k:].astype(float)
    w=np.arange(1,k+1,dtype=float)
    return np.full(h,max(0,.75*np.average(lk,weights=w)+.25*cr))  # was 0.6/0.4


def seasonal_indexed_forecast(y, dates, forecast_start_cw, h=13):
    """26-week avg with weekly seasonal indices and trend correction.
    Kaufland-style UDF approach: base × seasonal_index × trend."""
    n = len(y)
    if n < 4:
        return np.full(h, np.mean(y) if n > 0 else 0)

    # Extract calendar week per historical data point
    hist_weeks = []
    for d in dates[-n:]:
        if hasattr(d, 'isocalendar'):
            hist_weeks.append(d.isocalendar()[1])
        else:
            hist_weeks.append(0)

    # Base: mean of last 26 non-zero weeks
    tail_26 = y[-min(26, n):]
    nz = tail_26[tail_26 > 0]
    base = float(np.mean(nz)) if len(nz) > 0 else float(np.mean(tail_26))
    if base <= 0:
        return np.zeros(h)

    # Seasonal indices: average per calendar week / overall average
    overall_avg = float(np.mean(y[y > 0])) if np.sum(y > 0) > 0 else base
    week_sums = {}
    week_counts = {}
    for i in range(n):
        if y[i] > 0 and i < len(hist_weeks) and hist_weeks[i] > 0:
            wk = hist_weeks[i]
            week_sums[wk] = week_sums.get(wk, 0) + y[i]
            week_counts[wk] = week_counts.get(wk, 0) + 1

    indices = {}
    for wk in week_sums:
        wk_avg = week_sums[wk] / week_counts[wk]
        raw_idx = wk_avg / overall_avg if overall_avg > 0 else 1.0
        # Damping: blend toward 1.0 (less data = more damping)
        n_pts = week_counts[wk]
        damp = min(n_pts / 3.0, 1.0)
        indices[wk] = 1.0 + (raw_idx - 1.0) * damp

    # Trend: last 8 weeks vs last 26 weeks (captures growth/decline)
    tail_8 = y[-min(8, n):]
    nz_8 = tail_8[tail_8 > 0]
    avg_8 = float(np.mean(nz_8)) if len(nz_8) > 0 else base
    trend = avg_8 / base if base > 0 else 1.0
    trend = max(0.75, min(trend, 1.4))  # cap between 0.75x and 1.4x

    # Generate forecast per week
    fc = np.zeros(h)
    for i in range(h):
        fc_cw = ((forecast_start_cw + i - 1) % 52) + 1  # calendar week 1-52
        idx = indices.get(fc_cw, 1.0)
        fc[i] = max(0, base * idx * trend)

    # Floor: 50% of 8-week median (same as other models)
    nz_floor = y[-min(8, n):]
    nz_floor = nz_floor[nz_floor > 0]
    if len(nz_floor) >= 3:
        fc = np.maximum(fc, np.median(nz_floor) * 0.5)

    return fc

# ============ HELPERS ============
def classify(y):
    if len(y)==0 or np.sum(y)==0:return 'dead'
    nz=y[y>0];zp=np.sum(y==0)/len(y)
    cv=np.std(nz)/np.mean(nz) if len(nz)>1 and np.mean(nz)>0 else 0
    adi=len(y)/np.sum(y>0) if np.sum(y>0)>0 else len(y)
    if zp>.5 or adi>2:return 'intermittent'
    corr=abs(np.corrcoef(np.arange(len(y)),y)[0,1]) if np.std(y)>0 else 0
    if corr>.4:return 'trending'
    if cv>.8:return 'volatile'
    return 'stable'

def impute_oos(y):
    y=y.copy();nz=y[y>0]
    if len(nz)<2:return y
    thr=.05*np.median(nz)
    for t in range(len(y)):
        if y[t]<thr:
            nb=[]
            for l in range(t-1,-1,-1):
                if y[l]>=thr:nb.append(y[l])
                if len(nb)==2:break
            for r in range(t+1,len(y)):
                if y[r]>=thr:nb.append(y[r])
                if len(nb)==4:break
            if nb:y[t]=np.mean(nb)
    return y

def remove_spikes(y):
    y=y.copy().astype(float);nz=y[y>0]
    if len(nz)<2:return y
    med=np.median(nz)
    if med>0:y[y>3*med]=np.nan
    return y

def bt_metrics(act,fc):
    ta,tf=np.sum(act),np.sum(fc);te=np.sum(np.abs(fc-act))
    return {'fa':(1-te/ta)*100 if ta>0 else 0,'bias':((tf-ta)/ta)*100 if ta>0 else 0,
            'in_range':.7<=(tf/ta if ta>0 else 0)<=1.3,'total_act':ta,'total_fc':tf}

def clean_series(y,pf,uplift):
    yc=y.copy()
    if uplift<=1.0:return yc

    # v3.7: ws_spike no longer used here — wholesale spikes are cleaned upstream
    # in run() before clean_series runs, so this function now only deflates
    # true retail promo weeks by the per-SKU promo uplift.
    consecutive_end = 0
    for t in range(len(pf)-1, -1, -1):
        if t < len(pf) and pf[t].get('is_promo',0):
            consecutive_end += 1
        else:
            break

    total_promo = sum(1 for t in range(len(pf)) if pf[t].get('is_promo',0))
    promo_pct = total_promo / max(len(pf), 1)

    # If >40% of history is "promo" or last 4+ weeks are all promo → skip cleaning
    if promo_pct > 0.40 or consecutive_end >= 4:
        return yc

    # Only deflate isolated promo weeks (not part of a 4+ week streak)
    streak = 0
    for t in range(len(yc)):
        is_promo = t < len(pf) and pf[t].get('is_promo',0)
        if is_promo:
            streak += 1
        else:
            # If the streak that just ended was short (1-3 weeks), deflate those weeks
            if 0 < streak <= 3:
                for s in range(t - streak, t):
                    if yc[s] > 0:
                        yc[s] /= uplift
            streak = 0

    # Handle streak at the end of series (only deflate if short)
    if 0 < streak <= 3:
        for s in range(len(yc) - streak, len(yc)):
            if yc[s] > 0:
                yc[s] /= uplift

    return yc

# ============ NEW ARTICLE PROXY — sub-category matching ============
def detect_new_article(y):
    for i in range(len(y)):
        if y[i]>0:return i
    return -1

def find_proxy_sales(sku, cat, name, all_skus, subcat_map):
    """Match by sub-category first, then keyword fallback."""
    # Try sub-category match
    my_subcat = subcat_map.get(sku)
    if my_subcat:
        candidates = []
        for s in all_skus:
            if s['sku'] == sku or s['cat'] != cat: continue
            if subcat_map.get(s['sku']) == my_subcat:
                ys = np.array(s['full'], dtype=float); nz = ys[ys > 0]
                if len(nz) >= 8:
                    candidates.append(np.median(nz))
        if candidates:
            return np.median(candidates)  # median of sub-category peers

    # Fallback: keyword matching from name
    nl = (name or '').lower()
    kws = [kw for kw in ['bar','creatine','whey','cookie','brownie','caps','softgel',
        'protein','bcaa','gainer','collagen','shake','drink','gel','oats',
        'pancake','syrup','sauce','peanut','iso','vitamin','multivitamin',
        'preworkout','pre-workout','pump','omega','zma','tribulus','glutamine',
        'carnitine','creatine','magnesium','melatonin'] if kw in nl]
    candidates = []
    for s in all_skus:
        if s['sku'] == sku or s['cat'] != cat: continue
        sn = (s['name'] or '').lower()
        shared = sum(1 for k in kws if k in sn)
        if shared == 0: continue
        ys = np.array(s['full'], dtype=float); nz = ys[ys > 0]
        if len(nz) < 8: continue
        candidates.append((shared, np.median(nz)))
    if not candidates:
        # Last resort: category median
        cat_vals = []
        for s in all_skus:
            if s['cat'] == cat and s['sku'] != sku:
                ys = np.array(s['full'], dtype=float); nz = ys[ys > 0]
                if len(nz) >= 8: cat_vals.append(np.median(nz))
        return np.median(cat_vals) if cat_vals else None
    candidates.sort(key=lambda x: (-x[0], x[1]))
    return candidates[0][1]

# ============ FORECAST LOG (append-only — feeds Live FA in the app) ============
# Each run appends ~SKU_COUNT * N_FC rows. The log never gets truncated — rows
# persist so the app can pick the FIRST forecast made for any (sku, year, week)
# once the week closes with real actuals. forecast_total mirrors the Supply
# bridge formula: baseline × planner_factor + VP + MP (baseline = model output
# post cap/floor + MP-replaces-retail adjustment). Back-compat columns
# (target_year, target_week, forecast) keep the existing Global FA loader
# working without schema changes.
FORECAST_LOG_COLS = [
    'run_id', 'run_date', 'sku', 'year', 'week',
    'target_year', 'target_week', 'forecast',
    'forecast_total', 'forecast_retail', 'forecast_wholesale',
    'baseline', 'on_top_vp', 'on_top_mp',
    'promo_uplift', 'planner_factor', 'model_used', 'channel_mode',
]

def append_forecast_log(bd, results, ontop_vp, ontop_mp, planner_factor_map,
                         cy, CW_START, N_FC):
    """Append one row per (sku, week) to data/forecast_log.csv. Returns row count."""
    run_ts = datetime.now()
    run_id = run_ts.strftime('%Y-%m-%dT%H:%M:%S')
    run_date_lbl = run_ts.strftime('%Y-%m-%d %H:%M')
    log_rows = []
    for idx, r in enumerate(results):
        sku = r.get('sku')
        if sku is None:
            continue
        fc_arr = r.get('fc')
        if fc_arr is None or len(fc_arr) == 0:
            continue
        fc_r = r.get('fc_retail', np.zeros_like(fc_arr))
        fc_w = r.get('fc_wholesale', np.zeros_like(fc_arr))
        vp_arr = ontop_vp.get(idx, [0.0] * N_FC)
        mp_arr = ontop_mp.get(idx, [0.0] * N_FC)
        f_arr = planner_factor_map.get(idx, [1.0] * N_FC)
        for j in range(N_FC):
            cwn = CW_START + j
            ty = cy + (1 if cwn > 52 else 0)
            tw = cwn if cwn <= 52 else cwn - 52
            baseline = float(fc_arr[j])
            vp_v = float(vp_arr[j]) if j < len(vp_arr) else 0.0
            mp_v = float(mp_arr[j]) if j < len(mp_arr) else 0.0
            pf_v = float(f_arr[j]) if j < len(f_arr) else 1.0
            total = baseline * pf_v + vp_v + mp_v
            log_rows.append({
                'run_id': run_id,
                'run_date': run_date_lbl,
                'sku': sku,
                'year': ty,
                'week': tw,
                'target_year': ty,
                'target_week': tw,
                'forecast': int(round(total)),
                'forecast_total': round(total, 2),
                'forecast_retail': round(float(fc_r[j]) if j < len(fc_r) else 0.0, 2),
                'forecast_wholesale': round(float(fc_w[j]) if j < len(fc_w) else 0.0, 2),
                'baseline': round(baseline, 2),
                'on_top_vp': round(vp_v, 2),
                'on_top_mp': round(mp_v, 2),
                'promo_uplift': r.get('uplift', 1.0),
                'planner_factor': round(pf_v, 4),
                'model_used': r.get('model', ''),
                'channel_mode': r.get('channel_mode', ''),
            })
    log_path = os.path.join(bd, 'forecast_log.csv')
    log_df = pd.DataFrame(log_rows, columns=FORECAST_LOG_COLS)
    if os.path.exists(log_path):
        log_df.to_csv(log_path, mode='a', header=False, index=False)
    else:
        log_df.to_csv(log_path, index=False)
    return len(log_rows)


# ============ PROMO + UPLIFT ============
def _load_erp_promo(sales_csv_path):
    """Load erp_promo_calendar.csv (ground truth) sitting next to sales_clean.csv.
    Returns (erp_set, erp_min_yw, erp_max_yw) or (None, None, None) if missing.
    erp_set = {(sku, year, week): 1} for rows with is_erp_promo == 1.
    The min/max yw define the global coverage window — inside the window we
    trust ERP (0 = no promo, 1 = promo); outside we fall back to statistical."""
    erp_path = os.path.join(os.path.dirname(sales_csv_path), 'erp_promo_calendar.csv')
    if not os.path.exists(erp_path):
        return None, None, None
    edf = _fdb.load_erp_promo_calendar(erp_path) if HAS_DB_LOADERS else pd.read_csv(erp_path)
    if 'is_erp_promo' in edf.columns:
        edf = edf[edf['is_erp_promo'] == 1]
    yw_all = edf['year'].astype(int) * 100 + edf['week'].astype(int)
    if len(yw_all) == 0:
        return None, None, None
    erp_set = set(zip(edf['sku'].astype(str), edf['year'].astype(int), edf['week'].astype(int)))
    return erp_set, int(yw_all.min()), int(yw_all.max())


def load_promo_data(csv_path, skus_dates):
    if not os.path.exists(csv_path): return None
    sc = _fdb.load_sales_clean(csv_path) if HAS_DB_LOADERS else pd.read_csv(csv_path); lk = {}
    for _,r in sc.iterrows():lk[(r['sku'],int(r['year']),int(r['week']))]=r
    # ERP-aware promo flag: ground truth inside ERP window, statistical fallback outside.
    erp_set, erp_min_yw, erp_max_yw = _load_erp_promo(csv_path)
    n_erp = n_fallback = 0
    res={}
    for sku,dates in skus_dates.items():
        pl=[]
        for dt in dates:
            if dt and hasattr(dt,'isocalendar'):
                iso=dt.isocalendar();r=lk.get((sku,iso[0],iso[1]))
                if r is not None:
                    qt=float(r.get('qty_total',0));qr=float(r.get('qty_retail',0))
                    yw = iso[0]*100 + iso[1]
                    if erp_set is not None and erp_min_yw <= yw <= erp_max_yw:
                        is_promo = 1 if (str(sku), int(iso[0]), int(iso[1])) in erp_set else 0
                        n_erp += 1
                    else:
                        is_promo = int(r.get('is_any_promo', 0))
                        n_fallback += 1
                    pl.append({'is_promo':is_promo,'disc':float(r.get('retail_discount_pct',0)),'ws_spike':int(r.get('is_wholesale_spike',0)),'pct_retail':qr/qt if qt>0 else 0})
                else:pl.append({})
            else:pl.append({})
        res[sku]=pl
    if erp_set is not None:
        def _yw_lbl(yw): return f'CW{yw%100:02d}/{yw//100}'
        print(f'  Promo flags: {n_erp:,} from ERP ({_yw_lbl(erp_min_yw)}-{_yw_lbl(erp_max_yw)}), {n_fallback:,} from statistical fallback')
    else:
        print(f'  Promo flags: ERP calendar missing, {n_fallback:,} from statistical fallback only')
    print(f'  Promo: {len(sc):,} rows, {sum(1 for v in res.values() if any(d.get("is_promo",0) for d in v))} SKUs')
    return res

def load_uplift(p1,p2):
    su,cu={},{}
    df1 = _fdb.load_sku_uplift(p1) if HAS_DB_LOADERS else (pd.read_csv(p1) if os.path.exists(p1) else pd.DataFrame())
    df2 = _fdb.load_cat_uplift(p2) if HAS_DB_LOADERS else (pd.read_csv(p2) if os.path.exists(p2) else pd.DataFrame())
    if not df1.empty and 'promo_uplift' in df1.columns:
        for _, r in df1.iterrows():
            u = float(r['promo_uplift'])
            if u > 1 and r.get('promo_weeks', 0) >= 2 and r.get('normal_weeks', 0) >= 3:
                su[r['sku']] = u
    if not df2.empty and 'cat_promo_uplift' in df2.columns:
        for _, r in df2.iterrows():
            u = float(r['cat_promo_uplift'])
            if u > 1: cu[r['grupacija']] = u
    print(f'  Uplift: {len(su)} SKUs, {len(cu)} categories')
    return su,cu

def get_uplift(sku,cat,su,cu):
    if sku in su:return su[sku]
    if cat in cu:return cu[cat]
    return 1.35

def get_cann_rate(u):
    if u<=1:return .10
    return max(.10,min(.50,1-1/u))

def load_subcat_map(path):
    df = _fdb.load_sku_subcat_map(path) if HAS_DB_LOADERS else (pd.read_csv(path) if os.path.exists(path) else pd.DataFrame())
    if df.empty or 'sku' not in df.columns or 'sub_cat' not in df.columns:
        return {}
    return dict(zip(df['sku'], df['sub_cat']))

# ============ GBR ============
FC_COLS=['lag1','lag2','lag3','lag4','rm4','rm8','rs4','slope','level','woy','month','grup','ozn','vpc','cv','zc','med','is_promo','disc','pct_retail','ws_spike']

def build_features(y,dates,meta,pi):
    yi=impute_oos(y);yc=remove_spikes(yi);n=len(y);nz=y[y>0]
    cv=float(np.std(nz)/np.mean(nz)) if len(nz)>1 and np.mean(nz)>0 else 0
    zc=int(np.sum(y==0));ms=float(np.median(nz)) if len(nz)>0 else 0
    ema=np.zeros(n);ema[0]=yi[0]
    for t in range(1,n):ema[t]=.3*yi[t]+.7*ema[t-1]
    rows=[]
    for t in range(8,n):
        if np.isnan(yc[t]):continue
        seg=yi[t-8:t];dt=dates[t] if t<len(dates) else dates[-1]
        p=pi[t] if t<len(pi) else {}
        rows.append({'target':yi[t],'lag1':yi[t-1],'lag2':yi[t-2],'lag3':yi[t-3],'lag4':yi[t-4],
            'rm4':np.mean(yi[t-4:t]),'rm8':np.mean(seg),'rs4':np.std(yi[t-4:t]),
            'slope':np.polyfit(np.arange(8),seg,1)[0] if np.std(seg)>0 else 0,'level':ema[t-1],
            'woy':dt.isocalendar()[1] if hasattr(dt,'isocalendar') else 1,
            'month':dt.month if hasattr(dt,'month') else 1,
            'grup':meta['cat'],'ozn':meta['ozn'],'vpc':meta.get('vpc',0),
            'cv':cv,'zc':zc,'med':ms,'is_promo':p.get('is_promo',0),'disc':p.get('disc',0),
            'pct_retail':p.get('pct_retail',0),'ws_spike':p.get('ws_spike',0),'sku':meta['sku']})
    return rows

def train_gbr(rows):
    df=pd.DataFrame(rows);lg=LabelEncoder();lo=LabelEncoder()
    df['grup']=lg.fit_transform(df['grup'].fillna('UNK'));df['ozn']=lo.fit_transform(df['ozn'].fillna('UNK'))
    tr,te=[],[]
    for s in df['sku'].unique():
        idx=df[df['sku']==s].index
        if len(idx)<=4:tr.extend(idx);continue
        tr.extend(idx[:-4]);te.extend(idx[-4:])
    Xtr,ytr=df.loc[tr,FC_COLS].values.astype(float),df.loc[tr,'target'].values
    Xte,yte=df.loc[te,FC_COLS].values.astype(float),df.loc[te,'target'].values
    # v3.4.1: Recency sample weights — recent samples get ~3x weight vs oldest
    # Per-SKU: last sample = 1.0, decays by 0.92 per step back
    sw=np.ones(len(tr))
    tr_df=df.loc[tr]
    for s in tr_df['sku'].unique():
        mask=tr_df['sku']==s
        idxs=np.where(mask.values)[0]
        n=len(idxs)
        for i,ix in enumerate(idxs):
            sw[ix]=0.92**(n-1-i)  # oldest ~0.12 at 26wk, newest = 1.0
    bm,bmae,bp=None,np.inf,{}
    for p in ParameterGrid({'max_depth':[3,5,7],'max_iter':[100,200],'learning_rate':[.05,.1]}):
        m=HistGradientBoostingRegressor(loss='absolute_error',**p,random_state=42);m.fit(Xtr,ytr,sample_weight=sw)
        mae=np.mean(np.abs(m.predict(Xte)-yte))
        if mae<bmae:bmae,bm,bp=mae,m,p
    print(f'  GBR MAE: {bmae:.1f} | {bp}');return bm,lg,lo

def gbr_predict(m,lg,lo,y,dates,meta,h=13):
    yi=impute_oos(y);n=len(yi);nz=y[y>0]
    med=float(np.median(nz)) if len(nz)>0 else 0;cap=3*med if med>0 else np.inf
    cv=float(np.std(nz)/np.mean(nz)) if len(nz)>1 and np.mean(nz)>0 else 0;zc=int(np.sum(y==0))
    try:ge=lg.transform([meta['cat'] or 'UNK'])[0]
    except:ge=0
    try:oe=lo.transform([meta['ozn'] or 'UNK'])[0]
    except:oe=0
    ema=yi[0]
    for t in range(1,n):ema=.3*yi[t]+.7*ema
    ext=list(yi);preds=[];ld=dates[-1] if dates else None
    for i in range(h):
        rm4=np.mean(ext[-4:]);rm8=np.mean(ext[-8:]) if len(ext)>=8 else rm4
        seg=ext[-8:] if len(ext)>=8 else ext[-4:]
        sl=np.polyfit(np.arange(len(seg)),seg,1)[0] if np.std(seg)>0 else 0
        if ld and hasattr(ld,'isocalendar'):dt=ld+timedelta(weeks=i+1);woy,mo=dt.isocalendar()[1],dt.month
        else:woy,mo=1,1
        feat=np.array([[ext[-1],ext[-2],ext[-3],ext[-4],rm4,rm8,np.std(ext[-4:]),sl,ema,woy,mo,ge,oe,meta.get('vpc',0),cv,zc,med,0,0,.5,0]])
        p=max(0,min(m.predict(feat)[0],cap));ext.append(p);ema=.3*p+.7*ema;preds.append(p)
    return np.array(preds)

# ============ WORKBOOK STYLES ============
C_NAVY='1B2A4A';C_BLUE='2F5496';C_DKBLUE='1F3864';C_GREEN='548235'
C_LGREEN='E2EFDA';C_LBLUE='D6DCE4';C_YELLOW='FFF2CC';C_LYELLOW='FFFDE7'
C_LGRAY='F2F2F2';C_WHITE='FFFFFF';C_DGRAY='404040'
F_TITLE=Font(name='Aptos',bold=True,size=16,color=C_NAVY)
F_SUB=Font(name='Aptos',size=10,color='808080')
F_HDR=Font(name='Aptos',bold=True,size=10,color=C_WHITE)
F_SKU=Font(name='Aptos',bold=True,size=11,color=C_NAVY)
F_NAME=Font(name='Aptos',size=10,color=C_DGRAY)
F_LBL=Font(name='Aptos',size=9,color='808080')
F_LBL_B=Font(name='Aptos',bold=True,size=9,color=C_DGRAY)
F_VAL=Font(name='Aptos',size=10)
F_FC=Font(name='Aptos',bold=True,size=10,color=C_GREEN)
F_TOTAL=Font(name='Aptos',bold=True,size=11,color=C_NAVY)
F_FACTOR=Font(name='Aptos',bold=True,size=10,color='C00000')
F_RR=Font(name='Aptos',size=9,color='999999')
FILL_HDR=PatternFill('solid',fgColor=C_BLUE);FILL_DKHDR=PatternFill('solid',fgColor=C_DKBLUE)
FILL_SKU=PatternFill('solid',fgColor=C_LBLUE);FILL_FC=PatternFill('solid',fgColor=C_LGREEN)
FILL_RR=PatternFill('solid',fgColor='EDF2F9');FILL_ONTOP=PatternFill('solid',fgColor=C_YELLOW)
FILL_TOTAL=PatternFill('solid',fgColor=C_LGRAY);FILL_FACTOR=PatternFill('solid',fgColor='FFF0F0')
FILL_INPUT=PatternFill('solid',fgColor=C_LYELLOW)
ALIGN_C=Alignment(horizontal='center',vertical='center');ALIGN_R=Alignment(horizontal='right')
BORDER_BOT=Border(bottom=Side(style='medium',color=C_BLUE))
VP_SH='Demand Input VP';MP_SH='Demand Input MP';DP_SH='Demand Planning'

# ============ SHEET BUILDERS ============
def build_input_sheet(ws, results, existing_ontop, title_text, N_FC, CW_START):
    ws.cell(1,1,title_text).font=F_TITLE
    ws.cell(2,1,'Enter on-top demand and regular increases. Use filters to find SKUs.').font=F_SUB
    hr=4
    headers=['SKU','Artikl','Grupacija','OZNAKA','Type']+[f'CW{CW_START+j}' for j in range(N_FC)]
    for c,h in enumerate(headers,1):
        cell=ws.cell(hr,c,h);cell.font=F_HDR;cell.fill=FILL_HDR if c<=5 else FILL_DKHDR;cell.alignment=ALIGN_C
    ws.column_dimensions['A'].width=14;ws.column_dimensions['B'].width=42
    ws.column_dimensions['C'].width=22;ws.column_dimensions['D'].width=12;ws.column_dimensions['E'].width=18
    for j in range(N_FC):ws.column_dimensions[CL(6+j)].width=9
    row=5
    for idx,r in enumerate(results):
        for rtype in ['on-top demand','regular increase']:
            ws.cell(row,1,r['sku']).font=F_VAL;ws.cell(row,2,r['name']).font=F_NAME
            ws.cell(row,3,r['cat']).font=F_VAL;ws.cell(row,4,r['ozn']).font=F_VAL
            ws.cell(row,5,rtype).font=F_LBL_B
            ws.cell(row,5).fill=FILL_ONTOP if rtype=='on-top demand' else FILL_INPUT
            if rtype=='on-top demand' and idx in existing_ontop:
                for j,v in enumerate(existing_ontop[idx]):
                    if v and v>0:ws.cell(row,6+j,v)
            for j in range(N_FC):ws.cell(row,6+j).fill=FILL_INPUT;ws.cell(row,6+j).alignment=ALIGN_R
            row+=1
    ws.auto_filter.ref=f'A{hr}:{CL(5+N_FC)}{row-1}'

def build_dp_sheet(ws, results, rr_current, N_FC, CW_START, planner_factor_map=None,
                    rr_vp_hist=None, rr_mp_hist=None):
    ws.cell(1,1,'DEMAND PLANNING').font=F_TITLE
    ws.cell(2,1,f'Generated {datetime.now().strftime("%Y-%m-%d %H:%M")} | Engine v3.6').font=F_SUB
    ws.cell(3,1,'Planner Factor: 1.1 = +10%, 0.9 = -10%').font=Font(name='Aptos',size=9,italic=True,color='C00000')
    hr=5
    # Headers: A=SKU, B=Artikl, C=Grupacija, D=Oznaka, E=Row Label
    for c,h in enumerate(['SKU','Artikl','Grupacija','Oznaka','Label'],1):
        cell=ws.cell(hr,c,h);cell.font=F_HDR;cell.fill=FILL_HDR;cell.alignment=ALIGN_C
    for j in range(26):
        c=ws.cell(hr,6+j,f'RR-{25-j}');c.font=Font(name='Aptos',size=7,color='AAAAAA');c.fill=FILL_RR;c.alignment=ALIGN_C
    FC_COL=32
    for j in range(N_FC):
        c=ws.cell(hr,FC_COL+j,f'CW{CW_START+j}');c.font=F_HDR;c.fill=FILL_DKHDR;c.alignment=ALIGN_C
    ws.column_dimensions['A'].width=14;ws.column_dimensions['B'].width=38
    ws.column_dimensions['C'].width=20;ws.column_dimensions['D'].width=10;ws.column_dimensions['E'].width=16
    for j in range(26):ws.column_dimensions[CL(6+j)].width=7
    for j in range(N_FC):ws.column_dimensions[CL(FC_COL+j)].width=9
    BLOCK=11;BASE=6
    FILL_PROMO_HIST = PatternFill('solid', fgColor='FFC7CE')  # past RR week flagged by MP
    F_PROMO_HIST = Font(name='Aptos', size=7, color='9C0006', bold=True)
    for idx,r in enumerate(results):
        br=BASE+idx*BLOCK;rr=rr_current.get(idx,[0]*26);fc=r['fc']
        vp_ot=5+idx*2;vp_rg=5+idx*2+1;mp_ot=vp_ot;mp_rg=vp_rg
        vp_h = (rr_vp_hist or {}).get(idx, [0.0]*26)
        mp_h = (rr_mp_hist or {}).get(idx, [0.0]*26)
        # ALL rows get SKU/Grup/Ozn for filtering
        labels=['','Baseline FC','Planner Factor','Adjusted FC','VP on-top','VP regular','MP on-top','MP regular','On-top Total','TOTAL DEMAND','']
        for off in range(BLOCK):
            ws.cell(br+off,1,r['sku'] if off==0 else r['sku']).font=F_SKU if off==0 else Font(name='Aptos',size=1,color=C_LBLUE)
            ws.cell(br+off,2,r['name']).font=Font(name='Aptos',bold=True,size=10,color=C_DGRAY) if off==0 else Font(name='Aptos',size=1,color=C_LBLUE)
            ws.cell(br+off,3,r['cat'])
            ws.cell(br+off,4,r['ozn'])
            ws.cell(br+off,5,labels[off]).font=F_LBL_B
            if off==0:
                ws.cell(br+off,3).font=F_NAME;ws.cell(br+off,4).font=F_NAME
                for col in range(1,FC_COL+N_FC):ws.cell(br,col).fill=FILL_SKU
            else:
                ws.cell(br+off,3).font=Font(name='Aptos',size=1,color=C_LBLUE)
                ws.cell(br+off,4).font=Font(name='Aptos',size=1,color=C_LBLUE)
        # +1 Baseline (RR history + forecast). Past RR weeks where MP on-top
        # was entered are highlighted red so planners can spot historical promos.
        ws.cell(br+1,5,'Baseline FC').font=F_LBL_B
        for j in range(26):
            c=ws.cell(br+1,6+j,round(rr[j]))
            c.alignment=ALIGN_R
            if j < len(mp_h) and mp_h[j] > 0:
                c.font=F_PROMO_HIST; c.fill=FILL_PROMO_HIST
            else:
                c.font=F_RR; c.fill=FILL_RR
        for j in range(N_FC):c=ws.cell(br+1,FC_COL+j,round(float(fc[j])));c.font=F_FC;c.fill=FILL_FC;c.alignment=ALIGN_R
        # +2 Planner Factor (v3.4: preserve corrections from previous run)
        ws.cell(br+2,5,'Planner Factor').font=F_FACTOR
        pf_vals = planner_factor_map.get(idx, []) if planner_factor_map else []
        for j in range(N_FC):
            fval = pf_vals[j] if j < len(pf_vals) else 1.0
            c=ws.cell(br+2,FC_COL+j,fval);c.font=F_FACTOR;c.fill=FILL_FACTOR;c.alignment=ALIGN_C;c.number_format='0.00'
        # +3 Adjusted FC
        ws.cell(br+3,5,'Adjusted FC').font=F_LBL_B
        for j in range(N_FC):
            col=CL(FC_COL+j);ws.cell(br+3,FC_COL+j,f'=ROUND({col}{br+1}*{col}{br+2},0)')
            ws.cell(br+3,FC_COL+j).font=Font(name='Aptos',bold=True,size=10,color=C_GREEN);ws.cell(br+3,FC_COL+j).fill=FILL_FC
        # +4-7 VP/MP
        for off,label,sheet,src_row in [(4,'VP on-top',VP_SH,vp_ot),(5,'VP regular',VP_SH,vp_rg),(6,'MP on-top',MP_SH,mp_ot),(7,'MP regular',MP_SH,mp_rg)]:
            ws.cell(br+off,5,label).font=F_LBL
            # Historical past-26-weeks display for VP/MP on-top rows
            # (regular rows stay empty because we don't track on-top vs regular
            # split in the combined CSVs — only the per-SKU total survives).
            hist_vals = None
            if label == 'VP on-top': hist_vals = vp_h
            elif label == 'MP on-top': hist_vals = mp_h
            if hist_vals is not None:
                for j in range(26):
                    v = hist_vals[j] if j < len(hist_vals) else 0
                    if v > 0:
                        c = ws.cell(br+off, 6+j, round(float(v)))
                        c.font = F_RR; c.fill = FILL_ONTOP; c.alignment = ALIGN_R
            for j in range(N_FC):
                vc=CL(6+j);ws.cell(br+off,FC_COL+j,f"=IF('{sheet}'!{vc}{src_row}=\"\",0,'{sheet}'!{vc}{src_row})")
                if 'on-top' in label:ws.cell(br+off,FC_COL+j).fill=FILL_ONTOP
        # +8 On-top Total
        ws.cell(br+8,5,'On-top Total').font=F_LBL_B
        for j in range(N_FC):
            col=CL(FC_COL+j);ws.cell(br+8,FC_COL+j,f'=SUM({col}{br+4}:{col}{br+7})')
            ws.cell(br+8,FC_COL+j).fill=FILL_ONTOP;ws.cell(br+8,FC_COL+j).font=F_LBL_B
        # +9 TOTAL
        ws.cell(br+9,5,'TOTAL DEMAND').font=F_TOTAL
        for ci in range(1,FC_COL+N_FC):ws.cell(br+9,ci).border=BORDER_BOT
        for j in range(N_FC):
            col=CL(FC_COL+j);ws.cell(br+9,FC_COL+j,f'={col}{br+3}+{col}{br+8}')
            ws.cell(br+9,FC_COL+j).fill=FILL_TOTAL;ws.cell(br+9,FC_COL+j).font=F_TOTAL
    # ---- Review column: avg FC(4wk) vs avg RR(last 4wk), ±25% threshold ----
    REV_COL=FC_COL+N_FC
    c=ws.cell(hr,REV_COL,'Review');c.font=F_HDR;c.fill=FILL_DKHDR;c.alignment=ALIGN_C
    ws.column_dimensions[CL(REV_COL)].width=10
    FILL_OK=PatternFill('solid',fgColor='C6EFCE');FILL_CHK=PatternFill('solid',fgColor='FFC7CE')
    F_OK=Font(name='Aptos',bold=True,size=9,color='006100')
    F_CHK=Font(name='Aptos',bold=True,size=9,color='9C0006')
    for idx,r in enumerate(results):
        br=BASE+idx*BLOCK;rr=rr_current.get(idx,[0]*26);fc=r['fc']
        # Last 4 weeks of run rate (RR-4..RR-1, indices 21..24)
        rr4=np.array([rr[j] for j in range(21,25)],dtype=float)
        rr4_nz=rr4[rr4>0]
        avg_rr=float(np.mean(rr4_nz)) if len(rr4_nz)>0 else 0
        # First 4 weeks of forecast
        fc4=np.array(fc[:min(4,len(fc))],dtype=float)
        avg_fc=float(np.mean(fc4))
        # Check ±25%
        if avg_rr>0:
            status='OK' if 0.75*avg_rr<=avg_fc<=1.25*avg_rr else 'CHECK'
        else:
            status='CHECK' if avg_fc>0 else 'OK'
        fill=FILL_OK if status=='OK' else FILL_CHK
        font=F_OK if status=='OK' else F_CHK
        for off in range(BLOCK):
            c=ws.cell(br+off,REV_COL,status);c.fill=fill
            c.font=font if off==0 else Font(name='Aptos',size=1,color='C6EFCE' if status=='OK' else 'FFC7CE')
            c.alignment=ALIGN_C
    # ---- Promo column: PROMO if any MP on-top > 0 anywhere in forecast horizon ----
    # Filter on this to jump straight to SKUs with upcoming marketing activity.
    PROMO_COL = REV_COL + 1
    c = ws.cell(hr, PROMO_COL, 'Promo'); c.font = F_HDR; c.fill = FILL_DKHDR; c.alignment = ALIGN_C
    ws.column_dimensions[CL(PROMO_COL)].width = 9
    FILL_PROMO = PatternFill('solid', fgColor='FFD966')
    F_PROMO = Font(name='Aptos', bold=True, size=9, color='7F6000')
    for idx, r in enumerate(results):
        br = BASE + idx * BLOCK
        mp_row_ref = 5 + idx * 2  # MP on-top row in MP_SH (same as vp_ot layout)
        # Flag if any MP on-top cell in forecast horizon > 0.
        cws = [CL(6 + j) + str(mp_row_ref) for j in range(N_FC)]
        sum_expr = "+".join([f"IFERROR(VALUE('{MP_SH}'!{cw}),0)" for cw in cws])
        label_formula = f'=IF(({sum_expr})>0,"PROMO","")'
        for off in range(BLOCK):
            c = ws.cell(br + off, PROMO_COL, label_formula if off == 0 else "")
            if off == 0:
                c.font = F_PROMO; c.fill = FILL_PROMO; c.alignment = ALIGN_C
    last_row=BASE+len(results)*BLOCK-1
    ws.auto_filter.ref=f'A{hr}:{CL(PROMO_COL)}{last_row}'

def build_revenue_sheet(ws, results, portfolio_fc, sku_sell_prices, N_FC, CW_START):
    ws.cell(1,1,'REVENUE FORECAST DASHBOARD').font=F_TITLE
    ws.cell(2,1,'Forecasted revenue (€) = forecast units × avg selling price').font=F_SUB
    all_rev={}
    for r in results:
        cat=r.get('cat','UNKNOWN') or 'UNKNOWN';price=sku_sell_prices.get(r['sku'],0)
        if cat not in all_rev:all_rev[cat]=np.zeros(N_FC)
        for j in range(N_FC):all_rev[cat][j]+=float(r['fc'][j])*price
    for sku,data in portfolio_fc.items():
        cat=data.get('cat','OTHER') or 'OTHER';price=sku_sell_prices.get(sku,0)
        if cat not in all_rev:all_rev[cat]=np.zeros(N_FC)
        for j in range(N_FC):all_rev[cat][j]+=float(data['fc'][j])*price
    cats=sorted(all_rev.keys())
    # Planned section
    ws.cell(4,1,'PLANNED PORTFOLIO (496 SKUs)').font=Font(name='Aptos',bold=True,size=11,color=C_NAVY)
    hr=5
    ws.cell(hr,1,'Grupacija').font=F_HDR;ws.cell(hr,1).fill=FILL_HDR
    for j in range(N_FC):c=ws.cell(hr,2+j,f'CW{CW_START+j}');c.font=F_HDR;c.fill=FILL_DKHDR;c.alignment=ALIGN_C
    ws.cell(hr,2+N_FC,'TOTAL 13wk').font=F_HDR;ws.cell(hr,2+N_FC).fill=FILL_HDR;ws.column_dimensions['A'].width=28
    for j in range(N_FC):ws.column_dimensions[CL(2+j)].width=12
    dp_cats=sorted(set(r['cat'] for r in results if r['cat']))
    for ci,cat in enumerate(dp_cats):
        row=hr+1+ci;ws.cell(row,1,cat).font=Font(name='Aptos',bold=True,size=10)
        cat_skus=[r for r in results if r['cat']==cat]
        for j in range(N_FC):
            rev=sum(float(r['fc'][j])*sku_sell_prices.get(r['sku'],0) for r in cat_skus)
            ws.cell(row,2+j,round(rev,2)).number_format='€#,##0'
        ws.cell(row,2+N_FC,f'=SUM({CL(2)}{row}:{CL(1+N_FC)}{row})').number_format='€#,##0'
    dp_tr=hr+1+len(dp_cats)
    ws.cell(dp_tr,1,'PLANNED TOTAL').font=F_TOTAL
    for j in range(N_FC+1):
        col=CL(2+j);ws.cell(dp_tr,2+j,f'=SUM({col}{hr+1}:{col}{dp_tr-1})').number_format='€#,##0'
        ws.cell(dp_tr,2+j).font=F_TOTAL;ws.cell(dp_tr,2+j).border=Border(top=Side(style='medium',color=C_NAVY))
    # Full portfolio
    gap=dp_tr+2
    ws.cell(gap,1,f'FULL PORTFOLIO ({len(results)+len(portfolio_fc)} SKUs)').font=Font(name='Aptos',bold=True,size=11,color=C_NAVY)
    hr2=gap+1
    ws.cell(hr2,1,'Grupacija').font=F_HDR;ws.cell(hr2,1).fill=FILL_HDR
    for j in range(N_FC):c=ws.cell(hr2,2+j,f'CW{CW_START+j}');c.font=F_HDR;c.fill=FILL_DKHDR;c.alignment=ALIGN_C
    ws.cell(hr2,2+N_FC,'TOTAL 13wk').font=F_HDR;ws.cell(hr2,2+N_FC).fill=FILL_HDR
    for ci,cat in enumerate(cats):
        row=hr2+1+ci;ws.cell(row,1,cat).font=Font(name='Aptos',bold=True,size=10)
        for j in range(N_FC):ws.cell(row,2+j,round(float(all_rev[cat][j]),2)).number_format='€#,##0'
        ws.cell(row,2+N_FC,f'=SUM({CL(2)}{row}:{CL(1+N_FC)}{row})').number_format='€#,##0'
    gr=hr2+1+len(cats)
    ws.cell(gr,1,'GRAND TOTAL').font=F_TOTAL
    for j in range(N_FC+1):
        col=CL(2+j);ws.cell(gr,2+j,f'=SUM({col}{hr2+1}:{col}{gr-1})').number_format='€#,##0'
        ws.cell(gr,2+j).font=F_TOTAL;ws.cell(gr,2+j).border=Border(top=Side(style='double',color=C_NAVY))
    chart=BarChart();chart.type='col';chart.grouping='stacked';chart.title='Full Portfolio Revenue (€)'
    chart.y_axis.title='Revenue €';chart.width=32;chart.height=16
    cats_ref=Reference(ws,min_col=2,max_col=1+N_FC,min_row=hr2)
    for ci in range(len(cats)):
        vals=Reference(ws,min_col=2,max_col=1+N_FC,min_row=hr2+1+ci)
        chart.add_data(vals,from_rows=True,titles_from_data=False);chart.series[-1].tx=SeriesLabel(v=cats[ci])
    chart.set_categories(cats_ref);ws.add_chart(chart,'A'+str(gr+3))

def build_detail_sheet(ws, results, N_FC, CW_START):
    hdrs=['SKU','Name','Grupacija','Oznaka','Model','Pattern','Uplift','Cann%']+[f'CW{CW_START+j}' for j in range(N_FC)]
    for c,h in enumerate(hdrs,1):cell=ws.cell(1,c,h);cell.font=F_HDR;cell.fill=FILL_HDR;cell.alignment=ALIGN_C
    for i,r in enumerate(results):
        row=i+2;ws.cell(row,1,r['sku']);ws.cell(row,2,r['name']);ws.cell(row,3,r['cat']);ws.cell(row,4,r['ozn'])
        ws.cell(row,5,r['model']);ws.cell(row,6,r['pattern']);ws.cell(row,7,r['uplift']);ws.cell(row,8,round(r['cann_rate']*100,1))
        for j in range(N_FC):ws.cell(row,9+j,round(float(r['fc'][j]),0))
    ws.column_dimensions['A'].width=14;ws.column_dimensions['B'].width=40
    ws.auto_filter.ref=f'A1:{CL(8+N_FC)}{len(results)+1}'

def build_output_sheet(ws, results, title, row_offset, N_FC, CW_START, fill=None):
    """Generic output sheet: Total or On-top."""
    ws.cell(1,1,title).font=F_TITLE
    hr=4
    hdrs=['SKU','Artikl','Grupacija','OZNAKA']+[f'CW{CW_START+j}' for j in range(N_FC)]
    for c,h in enumerate(hdrs,1):cell=ws.cell(hr,c,h);cell.font=F_HDR;cell.fill=FILL_HDR if c<=4 else FILL_DKHDR;cell.alignment=ALIGN_C
    BLOCK=11;BASE=6
    for idx,r in enumerate(results):
        row=hr+1+idx;src=BASE+idx*BLOCK+row_offset
        ws.cell(row,1,r['sku']).font=F_VAL;ws.cell(row,2,r['name']).font=F_NAME;ws.cell(row,3,r['cat']);ws.cell(row,4,r['ozn'])
        for j in range(N_FC):
            ws.cell(row,5+j,f"='{DP_SH}'!{CL(32+j)}{src}").number_format='#,##0'
            if fill:ws.cell(row,5+j).fill=fill
    ws.column_dimensions['A'].width=14;ws.column_dimensions['B'].width=40;ws.column_dimensions['C'].width=22;ws.column_dimensions['D'].width=12
    ws.auto_filter.ref=f'A{hr}:{CL(4+N_FC)}{hr+len(results)}'

def build_price_sheet(ws, prices_df):
    ws.cell(1,1,'PRICE REFERENCE').font=F_TITLE
    ws.cell(2,1,'Avg selling prices from 52 weeks of transactions.').font=F_SUB
    hdrs=['SKU','Avg Sell Price €','Retail PPP €','Webshop PPP €','Retail Qty','Webshop Qty','Wholesale Qty','Weeks Active']
    for c,h in enumerate(hdrs,1):cell=ws.cell(4,c,h);cell.font=F_HDR;cell.fill=FILL_HDR;cell.alignment=ALIGN_C
    for i,(_,r) in enumerate(prices_df.iterrows()):
        row=5+i;ws.cell(row,1,r['sku']);ws.cell(row,2,r['avg_sell_price']).number_format='€#,##0.00'
        ws.cell(row,3,r['normal_retail_ppp']).number_format='€#,##0.00';ws.cell(row,4,r['normal_webshop_ppp']).number_format='€#,##0.00'
        ws.cell(row,5,int(r['qty_retail']));ws.cell(row,6,int(r['qty_webshop']));ws.cell(row,7,int(r['qty_wholesale']));ws.cell(row,8,int(r['weeks_active']))
    ws.column_dimensions['A'].width=18
    for c in range(2,9):ws.column_dimensions[CL(c)].width=16
    ws.auto_filter.ref=f'A4:H{4+len(prices_df)}'

def forecast_portfolio(sales_csv, dp_skus_set, N_FC):
    sc = _fdb.load_sales_clean(sales_csv) if HAS_DB_LOADERS else pd.read_csv(sales_csv)
    extra = sc[~sc['sku'].isin(dp_skus_set)]
    all_weeks = sc[['year','week']].drop_duplicates().sort_values(['year','week'])
    last_12=set(zip(all_weeks.tail(12)['year'],all_weeks.tail(12)['week']))
    portfolio={};sk_stale=0;sk_low=0
    for sku,grp in extra.groupby('sku'):
        grp=grp.sort_values(['year','week'])
        recent=grp[grp.apply(lambda r:(int(r['year']),int(r['week'])) in last_12,axis=1)]
        if len(recent)==0 or recent['qty_total'].sum()==0:sk_stale+=1;continue
        if recent['qty_total'].sum()<3:sk_low+=1;continue
        qty=grp['qty_total'].values;last=qty[-min(8,len(qty)):]
        nz=last[last>0]
        if len(nz)==0:continue
        w=np.arange(1,len(last)+1,dtype=float);fc_val=max(0,np.average(last,weights=w))
        portfolio[sku]={'fc':np.full(N_FC,fc_val),'cat':None}
    print(f'    Skipped stale: {sk_stale}, low vol: {sk_low}');return portfolio

# ============ v3.4: CALENDAR-ANCHORED WEEKS ============
def get_current_cw():
    """Return (year, week) for the current ISO week."""
    today = datetime.now()
    iso = today.isocalendar()
    return iso[0], iso[1]

def week_to_date(year, week):
    """Return Monday date for a given ISO year/week."""
    return datetime.strptime(f'{year}-W{week:02d}-1', '%G-W%V-%u')

def build_run_rate_from_sales(sales_csv, sku_list, n_weeks=None):
    """Build actuals per SKU from sales_clean.csv, ending at current CW.
    If n_weeks is None, uses ALL available weeks."""
    sc = _fdb.load_sales_clean(sales_csv) if HAS_DB_LOADERS else pd.read_csv(sales_csv)
    cy, cw = get_current_cw()

    # Build ordered list of all year-week pairs up to current CW
    all_yw = sc[['year','week']].drop_duplicates().sort_values(['year','week'])
    all_yw['yw_key'] = all_yw['year']*100 + all_yw['week']
    current_key = cy*100 + cw
    past = all_yw[all_yw['yw_key'] <= current_key]
    if n_weeks:
        past = past.tail(n_weeks)
    target_weeks = list(zip(past['year'].astype(int), past['week'].astype(int)))

    # Build dates for headers
    rr_dates = [week_to_date(y, w) for y, w in target_weeks]

    # Pivot actuals per SKU (total + retail + wholesale + ws_spike flag)
    sc_indexed = sc.set_index(['sku','year','week'])
    rr_data = {}
    rr_retail = {}
    rr_wholesale = {}
    rr_ws_spike = {}
    for sku in sku_list:
        vals = []
        r_vals = []
        w_vals = []
        sp_vals = []
        for y, w in target_weeks:
            try:
                row = sc_indexed.loc[(sku, y, w)]
                if isinstance(row, pd.DataFrame): row = row.iloc[0]
                vals.append(float(row['qty_total']))
                r_vals.append(float(row.get('qty_retail', 0) or 0))
                w_vals.append(float(row.get('qty_wholesale', 0) or 0))
                sp_vals.append(int(row.get('is_wholesale_spike', 0) or 0))
            except KeyError:
                vals.append(0.0)
                r_vals.append(0.0)
                w_vals.append(0.0)
                sp_vals.append(0)
        rr_data[sku] = vals
        rr_retail[sku] = r_vals
        rr_wholesale[sku] = w_vals
        rr_ws_spike[sku] = sp_vals

    return rr_data, rr_retail, rr_wholesale, rr_dates, target_weeks, rr_ws_spike

def get_latest_prices(sales_csv):
    """Get the most recent week's selling price per SKU for revenue calculation."""
    sc = _fdb.load_sales_clean(sales_csv) if HAS_DB_LOADERS else pd.read_csv(sales_csv)
    # Sort and take last occurrence per SKU
    sc = sc.sort_values(['year','week'])

    latest = {}
    for sku, grp in sc.groupby('sku'):
        last = grp.iloc[-1]
        qr = float(last.get('qty_retail', 0) or 0)
        qw = float(last.get('qty_webshop', 0) or 0)
        ppr = float(last.get('avg_ppp_retail', 0) or 0)
        ppw = float(last.get('avg_ppp_webshop', 0) or 0)
        total_q = qr + qw
        if total_q > 0:
            latest[sku] = (qr * ppr + qw * ppw) / total_q
        elif ppr > 0:
            latest[sku] = ppr
        elif ppw > 0:
            latest[sku] = ppw
        else:
            latest[sku] = 0
    return latest

def read_existing_corrections(existing_file):
    """Read planner factors, VP/MP on-tops from existing Polleo_Demand_Plan.xlsx.
    Returns dicts keyed by SKU -> {CW_number: value}."""
    if not os.path.exists(existing_file):
        return {}, {}, {}

    wb = load_workbook(existing_file, data_only=True)

    # Read CW headers from Demand Planning sheet
    ws_dp = wb['Demand Planning']
    old_cws = []
    for c in range(32, 32+20):  # scan up to 20 columns
        v = ws_dp.cell(5, c).value
        if v and str(v).startswith('CW'):
            old_cws.append((c, int(str(v).replace('CW',''))))
        elif v is None:
            break

    BLOCK = 11; BASE = 6
    planner_factors = {}  # sku -> {cw: factor}
    # Count SKUs
    max_idx = (ws_dp.max_row - BASE) // BLOCK + 1

    for idx in range(max_idx):
        br = BASE + idx * BLOCK
        sku = ws_dp.cell(br, 1).value
        if not sku: continue

        # Planner Factor is at br+2
        pf = {}
        for col, cw_num in old_cws:
            val = ws_dp.cell(br+2, col).value
            if val is not None and val != '' and float(val) != 1.0:
                pf[cw_num] = float(val)
        if pf:
            planner_factors[sku] = pf

    # Read VP/MP on-tops
    vp_ontops = {}  # sku -> {cw: value}
    mp_ontops = {}
    for sheet_name, target in [('Demand Input VP', vp_ontops), ('Demand Input MP', mp_ontops)]:
        if sheet_name not in wb.sheetnames: continue
        ws = wb[sheet_name]
        # Find CW columns
        inp_cws = []
        for c in range(6, 6+20):
            v = ws.cell(4, c).value
            if v and str(v).startswith('CW'):
                inp_cws.append((c, int(str(v).replace('CW',''))))
            elif v is None:
                break
        # Read rows (2 rows per SKU: on-top, regular)
        row = 5
        while row <= ws.max_row:
            sku = ws.cell(row, 1).value
            rtype = ws.cell(row, 5).value
            if not sku:
                row += 1; continue
            vals = {}
            for col, cw_num in inp_cws:
                v = ws.cell(row, col).value
                if v and float(v) > 0:
                    key = f'{rtype}_{cw_num}'
                    if sku not in target: target[sku] = {}
                    target[sku][key] = float(v)
            row += 1

    wb.close()
    print(f'  Preserved: {len(planner_factors)} planner factors, {len(vp_ontops)} VP, {len(mp_ontops)} MP corrections')
    return planner_factors, vp_ontops, mp_ontops

# ============ MAIN ============
def run(input_file=None, *, generate_xlsx=True, run_type='baseline', run_by_id=None):
    """Main forecast pipeline.

    Args:
      input_file: Optional path to Polleo_Demand_Planning_Book*.xlsx. When None,
        the engine globs the current working directory. DB-only mode also works
        (no workbook needed) if `sku_planning` is populated.
      generate_xlsx: When True (default) writes Polleo_Demand_Plan.xlsx with the
        full 7-sheet Monika layout. When False, skips Excel — much faster, and
        used by FastAPI which generates the xlsx on demand via
        `generate_demand_plan_xlsx(run_id)`.
      run_type: 'baseline' or 'with_ontop' — stored on the forecast_runs row
        when DB is available.
      run_by_id: optional user_id for the forecast_runs row.

    Returns:
      dict with keys: run_id (None if DB unavailable), sku_count, rows_inserted,
      duration, output_file (if xlsx written), n_csv_log_rows.
    """
    _t_run_start = datetime.now()
    bd = '.'
    # Find input workbook (Planning Book for SKU list). In DB-only mode, when
    # `sku_planning` has rows the engine can run without a workbook — but we
    # still keep the file-discovery branch for legacy CLI flows.
    if not input_file:
        candidates = [f for f in glob.glob('Polleo_Demand_Planning_Book*.xlsx')
                      if not f.startswith('~')]
        if not candidates:
            candidates = [f for f in glob.glob('Polleo_Demand_Plan*.xlsx')
                          if not f.startswith('~') and 'backup' not in f.lower()]
        input_file = candidates[0] if candidates else None
        if not input_file:
            # DB-only mode: if sku_planning is populated we can proceed without a workbook
            if HAS_DB_LOADERS:
                test_plan = _fdb.load_sku_plan_list(None)
                if not test_plan.empty:
                    input_file = None  # signal "DB-only"
                    bd = 'data' if os.path.isdir('data') else '.'
                else:
                    print('\n  ERROR: No workbook found and DB sku_planning is empty.')
                    if sys.stdin.isatty(): input('\n  Press Enter...'); sys.exit(1)
            else:
                print('\n  ERROR: No workbook found.')
                if sys.stdin.isatty(): input('\n  Press Enter...'); sys.exit(1)
    if input_file:
        bd = os.path.dirname(input_file) or '.'
    output_file = os.path.join(bd, 'Polleo_Demand_Plan.xlsx')
    sales_csv = os.path.join(bd, 'sales_clean.csv')

    print(f'\n{"="*60}')
    print(f'  POLLEO FORECAST ENGINE v3.6')
    print(f'  {datetime.now().strftime("%Y-%m-%d %H:%M")}')
    print(f'{"="*60}')

    # ---- v3.4: Calendar-anchored CW_START ----
    cy, cw = get_current_cw()
    CW_START = cw + 1
    N_FC = 13
    print(f'  Current week: CW{cw} ({cy})')
    print(f'  Forecast starts: CW{CW_START} ({N_FC} weeks -> CW{CW_START+N_FC-1})')

    # ---- Read SKU list: sku_plan_list.csv (preferred) or Planning Book (fallback) ----
    plan_csv = os.path.join(bd, 'sku_plan_list.csv')
    # DB-first when CSV not present, CSV-first when present (bit-exact behaviour)
    if HAS_DB_LOADERS:
        plan_df = _fdb.load_sku_plan_list(plan_csv if os.path.exists(plan_csv) else None)
    elif os.path.exists(plan_csv):
        plan_df = pd.read_csv(plan_csv)
    else:
        plan_df = pd.DataFrame()
    if not plan_df.empty:
        sku_list = plan_df['sku'].tolist()
        sku_meta = {}
        for _, r in plan_df.iterrows():
            sku_meta[r['sku']] = {
                'name': r.get('name', ''),
                'cat': r.get('cat', ''),
                'ozn': r.get('oznaka', ''),
                'vpc': float(r.get('vpc', 0) or 0)
            }
        print(f'  SKUs from sku_plan_list.csv: {len(sku_list)}')
        ozn_counts = plan_df['oznaka'].value_counts().to_dict()
        print(f'  OZNAKA: {ozn_counts}')
    else:
        wb = load_workbook(input_file, data_only=True)
        ws_rr = wb['Run Rate']

        pr = {}
        if 'cijena po artiklu' in wb.sheetnames:
            ws_p = wb['cijena po artiklu']
            for r in range(2, ws_p.max_row+1):
                s = ws_p.cell(r, 1).value
                if s: pr[s] = float(ws_p.cell(r, 5).value or 0)

        sku_list = []
        sku_meta = {}
        for r in range(4, ws_rr.max_row+1):
            sku = ws_rr.cell(r, 1).value
            if not sku: continue
            sku_list.append(sku)
            sku_meta[sku] = {
                'name': ws_rr.cell(r, 2).value,
                'cat': ws_rr.cell(r, 3).value,
                'ozn': ws_rr.cell(r, 4).value,
                'vpc': pr.get(sku, 0)
            }
        wb.close()
        print(f'  SKUs from Planning Book: {len(sku_list)} (no sku_plan_list.csv found)')

    # ---- v3.5: Build Run Rate from sales_clean.csv (ALL available weeks) ----
    print(f'\n  Building Run Rate from sales_clean.csv...')
    rr_data, rr_retail, rr_wholesale, rr_dates, target_weeks, rr_ws_spike = build_run_rate_from_sales(sales_csv, sku_list)
    N_HIST = len(target_weeks)
    print(f'  Run Rate: {N_HIST} weeks, {rr_dates[0].strftime("%Y-%m-%d")} to {rr_dates[-1].strftime("%Y-%m-%d")}')

    # ---- v3.4: Preserve planner corrections from existing output ----
    # DB-first: pull from the most recent forecast_runs (planner_factor +
    # on_top_* columns). Falls back to reading Polleo_Demand_Plan.xlsx when
    # DB is unavailable. Same `(old_factors, old_vp, old_mp)` shape.
    print(f'\n  Reading existing corrections...')
    old_factors, old_vp, old_mp = read_existing_corrections(output_file)
    if HAS_DB_LOADERS and not old_factors:
        db_factors = _fdb.load_latest_planner_factors()
        if db_factors:
            old_factors = db_factors
            print(f'  Planner factors loaded from DB (latest forecast_run): {len(old_factors)} SKUs')
    if HAS_DB_LOADERS and (not old_vp or not old_mp):
        db_vp, db_mp = _fdb.load_latest_ontops()
        if not old_vp and db_vp:
            old_vp = db_vp
            print(f'  VP on-tops loaded from DB: {len(old_vp)} SKUs')
        if not old_mp and db_mp:
            old_mp = db_mp
            print(f'  MP on-tops loaded from DB: {len(old_mp)} SKUs')

    # ---- Build SKU data structures ----
    skus = []
    rr_current = {}
    for idx, sku in enumerate(sku_list):
        m = sku_meta[sku]
        curr = rr_data.get(sku, [0.0]*N_HIST)
        rr_current[idx] = curr[-26:] if len(curr) > 26 else curr  # last 26 for display in DP sheet
        skus.append({
            'sku': sku, 'name': m['name'], 'cat': m['cat'],
            'ozn': m['ozn'], 'full': curr, 'dates': rr_dates, 'vpc': m['vpc'],
            'retail': rr_retail.get(sku, [0.0]*N_HIST),
            'wholesale': rr_wholesale.get(sku, [0.0]*N_HIST),
            'ws_spike_flags': rr_ws_spike.get(sku, [0]*N_HIST),
        })
    print(f'  SKUs: {len(skus)}, Weeks: {N_HIST}')

    # ---- Historical VP/MP on-top lookups for DP sheet display ----
    # Read past CW columns from vp_input.csv / mp_input.csv and align to the
    # last 26 target_weeks (RR-25..RR-0 display window). Relies on combine_collected
    # preserving past CWs — otherwise past columns simply won't be there yet.
    def _load_hist_ontop(csv_path, channel):
        # DB-first via forecast_db.load_vp_input/load_mp_input (pivots
        # on_top_inputs to wide format with CW{n} columns). CSV fallback when
        # DB unavailable.
        df = pd.DataFrame()
        if HAS_DB_LOADERS:
            loader = _fdb.load_vp_input if channel == 'wholesale' else _fdb.load_mp_input
            df = loader(csv_path if os.path.exists(csv_path) else None)
        elif os.path.exists(csv_path):
            try:
                df = pd.read_csv(csv_path)
            except Exception:
                df = pd.DataFrame()
        if df.empty:
            return {}
        out = {}
        cw_cols = [c for c in df.columns if c.startswith('CW')]
        for _, row in df.iterrows():
            per = {}
            for c in cw_cols:
                try:
                    per[int(c[2:])] = float(row.get(c, 0) or 0)
                except Exception:
                    continue
            out[row['sku']] = per
        return out

    hist_vp = _load_hist_ontop(os.path.join(bd, 'vp_input.csv'), 'wholesale')
    hist_mp = _load_hist_ontop(os.path.join(bd, 'mp_input.csv'), 'retail')
    rr_vp_hist = {}
    rr_mp_hist = {}
    display_weeks = target_weeks[-26:] if len(target_weeks) > 26 else target_weeks
    for idx, sku in enumerate(sku_list):
        vp_per = hist_vp.get(sku, {})
        mp_per = hist_mp.get(sku, {})
        rr_vp_hist[idx] = [vp_per.get(w, 0.0) for (_y, w) in display_weeks]
        rr_mp_hist[idx] = [mp_per.get(w, 0.0) for (_y, w) in display_weeks]

    # ---- v3.7: Clean wholesale spikes out of training history ----
    # Wholesale spikes are one-shot sell-in events (VP-driven). If left in the
    # baseline, the model learns a phantom recurring surge — and because VP
    # on-tops are added forward separately, spikes get double-counted.
    # For training-only series ('full' used by clean_series / SF / GBR), replace
    # spike weeks with the SKU's non-spike wholesale median + that week's
    # retail+webshop (so retail dynamics are preserved). Actuals for FA display
    # and run-rate in the UI stay untouched.
    ws_cleaned = 0
    for s in skus:
        flags = s.get('ws_spike_flags', [])
        if not flags or not any(flags):
            continue
        wh = np.array(s['wholesale'], dtype=float)
        rt = np.array(s['retail'], dtype=float)
        full = np.array(s['full'], dtype=float)
        nonspike_wh = wh[(np.array(flags) == 0) & (wh > 0)]
        median_wh = float(np.median(nonspike_wh)) if len(nonspike_wh) >= 4 else 0.0
        cleaned_full = full.copy()
        cleaned_wh = wh.copy()
        hits = 0
        for t, f in enumerate(flags):
            if f:
                # Replace wholesale portion with median; retail/webshop (full - wh) preserved.
                retail_webshop_part = max(full[t] - wh[t], 0.0)
                cleaned_wh[t] = median_wh
                cleaned_full[t] = retail_webshop_part + median_wh
                hits += 1
        s['full'] = cleaned_full.tolist()
        s['wholesale'] = cleaned_wh.tolist()
        ws_cleaned += hits
    print(f'  Wholesale spike weeks cleaned (training-only): {ws_cleaned}')

    # ---- Load promo/uplift data ----
    print(f'\n  Loading data...')
    promo_data = load_promo_data(sales_csv, {s['sku']: s['dates'] for s in skus})
    su, cu = load_uplift(os.path.join(bd, 'sku_uplift.csv'), os.path.join(bd, 'cat_uplift.csv'))
    subcat_map = load_subcat_map(os.path.join(bd, 'sku_subcat_map.csv'))
    print(f'  Sub-categories: {len(subcat_map)} SKUs')

    # ---- Clean & forecast ----
    print(f'\n  Cleaning & forecasting...')
    new_cnt = 0
    for s in skus:
        y = np.array(s['full'], dtype=float)
        pi = promo_data.get(s['sku'], [{}]*len(y)) if promo_data else [{}]*len(y)
        u = get_uplift(s['sku'], s['cat'], su, cu)
        yc = clean_series(y, pi, u)
        fn = detect_new_article(yc)
        if fn > len(yc) * .5:
            px = find_proxy_sales(s['sku'], s['cat'], s['name'], skus, subcat_map)
            if px:
                for t in range(max(0, fn-8), fn): yc[t] = px * .5
                new_cnt += 1
        s['y_clean'] = yc; s['pi'] = pi; s['uplift'] = u
    print(f'  New articles proxied: {new_cnt}')


    feat_rows = []
    for s in skus:
        yc = s['y_clean']
        if classify(yc) in ('dead', 'intermittent') or len(yc) < 12: continue
        feat_rows.extend(build_features(yc, s['dates'],
            {'sku': s['sku'], 'cat': s['cat'], 'ozn': s['ozn'], 'vpc': s['vpc']}, s['pi']))
    print(f'  Samples: {len(feat_rows)}')
    gbr, le_g, le_o = train_gbr(feat_rows)

    # ---- v3.5: Run StatsForecast on all SKUs in batch ----
    # v3.6: Keep retail/wholesale channel forecasts separate so wholesale-
    # dominant SKUs can use channel-split baselines (avoids double-counting
    # historical wholesale activity when VP on-top is added). Also runs a
    # seasonal SF batch on wholesale series (season_length=4, monthly) to
    # catch regular monthly buyer patterns.
    sf_fc_total = {}      # sku -> {model_name: np.array}  (total demand fc)
    sf_fc_retail = {}     # sku -> {model_name: np.array}  (retail+webshop fc)
    sf_fc_wholesale = {}  # sku -> {model_name: np.array}  (wholesale fc, inc. seasonal)
    sf_fc_split = {}      # sku -> {model_name: np.array}  (retail+wholesale summed; kept for backward compat in model competition)
    sf_model_names = []

    if HAS_SF:
        print(f'\n  Running StatsForecast ({N_HIST} weeks history)...')
        import time as _time; _t0 = _time.time()

        sf_rows = []
        sf_skus = []
        for s in skus:
            yc = s['y_clean']
            if np.sum(yc > 0) < 4: continue
            sf_skus.append(s['sku'])
            for t, v in enumerate(yc):
                sf_rows.append({'unique_id': s['sku'], 'ds': t, 'y': max(float(v), 0.0)})
            for t, v in enumerate(s['retail']):
                sf_rows.append({'unique_id': f"{s['sku']}_R", 'ds': t, 'y': max(float(v), 0.0)})
            for t, v in enumerate(s['wholesale']):
                sf_rows.append({'unique_id': f"{s['sku']}_W", 'ds': t, 'y': max(float(v), 0.0)})

        sf_df = pd.DataFrame(sf_rows)
        sf_models_list = [
            AutoARIMA(season_length=1),
            AutoCES(season_length=1),
            AutoTheta(season_length=1),
            CrostonOptimized(),
            ADIDA(),
            IMAPA(),
            TSB(alpha_d=0.2, alpha_p=0.2),
        ]
        sf_engine = SF(models=sf_models_list, freq=1, n_jobs=4, fallback_model=Naive())
        sf_engine.fit(sf_df)
        sf_pred = sf_engine.predict(h=N_FC)
        sf_model_names = [c for c in sf_pred.columns if c not in ('ds', 'unique_id')]

        for sku in sf_skus:
            total_rows = sf_pred[sf_pred['unique_id'] == sku]
            r_rows = sf_pred[sf_pred['unique_id'] == f'{sku}_R']
            w_rows = sf_pred[sf_pred['unique_id'] == f'{sku}_W']

            sf_fc_total[sku] = {}
            sf_fc_retail[sku] = {}
            sf_fc_wholesale[sku] = {}
            sf_fc_split[sku] = {}
            for mn in sf_model_names:
                if len(total_rows) > 0:
                    sf_fc_total[sku][mn] = np.maximum(total_rows[mn].values.astype(float), 0)
                if len(r_rows) > 0:
                    sf_fc_retail[sku][mn] = np.maximum(r_rows[mn].values.astype(float), 0)
                if len(w_rows) > 0:
                    sf_fc_wholesale[sku][mn] = np.maximum(w_rows[mn].values.astype(float), 0)
                # Keep _split forecasts as candidates for model competition
                if len(r_rows) > 0 and len(w_rows) > 0:
                    sf_fc_split[sku][f'{mn}_split'] = (
                        sf_fc_retail[sku][mn] + sf_fc_wholesale[sku][mn]
                    )

        # ---- v3.6: Seasonal wholesale batch (monthly pattern) ----
        # Catches regular monthly buyer orders that the non-seasonal models miss.
        # Only runs on _W series and only adds AutoARIMA/Theta variants that
        # accept seasonality. Adds ~30-50% runtime but only to the wholesale side.
        print(f'  Running seasonal wholesale batch (season_length=4)...')
        sf_df_w = sf_df[sf_df['unique_id'].str.endswith('_W')].copy()
        if len(sf_df_w) > 0:
            sf_models_seasonal = [
                AutoARIMA(season_length=4),
                AutoTheta(season_length=4),
            ]
            sf_engine_seasonal = SF(
                models=sf_models_seasonal, freq=1, n_jobs=4, fallback_model=Naive()
            )
            try:
                sf_engine_seasonal.fit(sf_df_w)
                sf_pred_seasonal = sf_engine_seasonal.predict(h=N_FC)
                seasonal_model_names = [
                    c for c in sf_pred_seasonal.columns
                    if c not in ('ds', 'unique_id')
                ]
                for sku in sf_skus:
                    w_rows = sf_pred_seasonal[sf_pred_seasonal['unique_id'] == f'{sku}_W']
                    if len(w_rows) == 0:
                        continue
                    for mn in seasonal_model_names:
                        # Rename to avoid clash with non-seasonal AutoARIMA/Theta
                        label = f'{mn}_s4'
                        vals = np.maximum(w_rows[mn].values.astype(float), 0)
                        sf_fc_wholesale[sku][label] = vals
            except Exception as _e:
                print(f'    Seasonal batch failed ({type(_e).__name__}: {_e}) — using non-seasonal models only')

        _sf_time = _time.time() - _t0
        print(f'  StatsForecast: {len(sf_skus)} SKUs x {len(sf_model_names)} models x 3 channels + seasonal wholesale in {_sf_time:.1f}s')
    else:
        print(f'  StatsForecast not available, using engine models only')

    # ---- Load VP/MP ----
    new_cws = [CW_START + j for j in range(N_FC)]

    vp_csv = os.path.join(bd, 'vp_input.csv')
    mp_csv = os.path.join(bd, 'mp_input.csv')

    def load_input_csv(csv_path, channel, sku_list, new_cws):
        df = pd.DataFrame()
        if HAS_DB_LOADERS:
            loader = _fdb.load_vp_input if channel == 'wholesale' else _fdb.load_mp_input
            df = loader(csv_path if os.path.exists(csv_path) else None)
        elif os.path.exists(csv_path):
            df = pd.read_csv(csv_path)
        if df.empty:
            return {}
        sku_to_idx = {s: i for i, s in enumerate(sku_list)}
        ontop = {}
        cw_cols = [f'CW{cw}' for cw in new_cws]
        for _, row in df.iterrows():
            sku = row['sku']
            if sku not in sku_to_idx: continue
            idx = sku_to_idx[sku]
            vals = [float(row.get(c, 0) or 0) for c in cw_cols]
            if any(v > 0 for v in vals):
                if idx in ontop:
                    ontop[idx] = [ontop[idx][j] + vals[j] for j in range(len(vals))]
                else:
                    ontop[idx] = vals
        return ontop

    # DB-first when on_top_inputs has any rows; CSV fallback when DB empty/unavailable
    use_csv = HAS_DB_LOADERS or os.path.exists(vp_csv) or os.path.exists(mp_csv)
    if use_csv:
        ontop_vp = load_input_csv(vp_csv, 'wholesale', sku_list, new_cws)
        ontop_mp = load_input_csv(mp_csv, 'retail',    sku_list, new_cws)
        print(f'  VP/MP from CSVs/DB: {len(ontop_vp)} VP, {len(ontop_mp)} MP SKUs')
    else:
        print(f'  VP/MP: will use xlsx corrections')

    ontop_map = {}

    # ---- v3.5: Model competition — engine + SF, no Holt ----
    TEST = 4; results = []; mw = {}; ah = []; ap = []; ch_ = []; cp_ = []
    for idx, s in enumerate(skus):
        yc = s['y_clean']; yr = np.array(s['full'], dtype=float); pat = classify(yc)
        sku = s['sku']

        # Ontop per channel: VP (wholesale, truly additive to baseline) and
        # MP (physical retail — REPLACES retail baseline for the week when set,
        # since a promo week's retail sell-through IS the MP number, not on
        # top of the non-promo baseline).
        if use_csv:
            vp_vals = list(ontop_vp.get(idx, [0]*N_FC))
            mp_vals = list(ontop_mp.get(idx, [0]*N_FC))
        else:
            vp_vals = [0] * N_FC
            mp_vals = [0] * N_FC
            for j, cwn in enumerate(new_cws):
                vp_ot = old_vp.get(sku, {}).get(f'on-top demand_{cwn}', 0)
                vp_rg = old_vp.get(sku, {}).get(f'regular increase_{cwn}', 0)
                mp_ot = old_mp.get(sku, {}).get(f'on-top demand_{cwn}', 0)
                mp_rg = old_mp.get(sku, {}).get(f'regular increase_{cwn}', 0)
                vp_vals[j] = vp_ot + vp_rg
                mp_vals[j] = mp_ot + mp_rg
        ontop = [vp_vals[j] + mp_vals[j] for j in range(N_FC)]
        ontop_map[idx] = ontop

        u = s['uplift']; cr = get_cann_rate(u)
        if pat == 'dead' or len(yc) <= TEST + 4:
            results.append({
                'fc': np.zeros(N_FC), 'model': 'Dead', 'pattern': pat,
                'ontop': ontop, 'uplift': round(u, 2), 'cann_rate': round(cr, 3),
                'fc_retail': np.zeros(N_FC),
                'fc_wholesale': np.zeros(N_FC),
                'channel_mode': 'dead',
                'ws_share': 0.0,
                'model_retail': '',
                'model_wholesale': '',
                **s,
            })
            mw['Dead'] = mw.get('Dead', 0) + 1; continue
        tr = yc[:-TEST]; act = yc[-TEST:]
        nzt = tr[tr > 0]; med = np.median(nzt) if len(nzt) > 0 else 0
        sp = bool(any(a > 3*med for a in yr[-TEST:])) if med > 0 else False

        # ---- Build all candidates for backtest ----
        # Engine models (no Holt)
        cands = {'SES': ses_forecast(tr, TEST), 'Croston': croston_forecast(tr, TEST), 'Hybrid': hybrid_fc(tr, TEST)}
        if len(tr) >= 4: cands['WMA'] = wma_capped_forecast(tr, TEST)
        if len(tr) >= 8: cands['SeasonIdx'] = seasonal_indexed_forecast(tr, s['dates'][:len(tr)], CW_START, TEST)
        if pat != 'intermittent' and len(tr) >= 12:
            try: cands['GBR'] = gbr_predict(gbr, le_g, le_o, tr, s['dates'][:len(tr)], s, h=TEST)
            except: pass

        # SF models — use batch-run total forecasts (last TEST weeks as pseudo-backtest)
        # Since SF was fit on full data, we compare its forecast shape to actuals
        if sku in sf_fc_total:
            for mn, fc_full in sf_fc_total[sku].items():
                cands[mn] = fc_full[:TEST]
        if sku in sf_fc_split:
            for mn, fc_full in sf_fc_split[sku].items():
                cands[mn] = fc_full[:TEST]

        # Penalty: only GBR gets penalized
        PENALTY = {'GBR': 1.15}
        best = min(cands, key=lambda m: np.mean(np.abs(cands[m] - act)) * PENALTY.get(m, 1.0))

        # ---- Generate final N_FC forecast ----
        if best == 'SES': fc = ses_forecast(yc, N_FC)
        elif best == 'Croston': fc = croston_forecast(yc, N_FC)
        elif best == 'WMA': fc = wma_capped_forecast(yc, N_FC)
        elif best == 'GBR':
            try: fc = gbr_predict(gbr, le_g, le_o, yc, s['dates'], s, h=N_FC)
            except: fc = ses_forecast(yc, N_FC)
        elif best == 'Hybrid': fc = hybrid_fc(yc, N_FC)
        elif best == 'SeasonIdx': fc = seasonal_indexed_forecast(yc, s['dates'], CW_START, N_FC)
        elif sku in sf_fc_total and best in sf_fc_total[sku]:
            fc = sf_fc_total[sku][best]
        elif sku in sf_fc_split and best in sf_fc_split[sku]:
            fc = sf_fc_split[sku][best]
        else:
            fc = ses_forecast(yc, N_FC)

        fc = np.maximum(fc, 0)
        # Sanity cap: no week > 2x recent 13-week average
        nz_recent = yc[-min(13, len(yc)):]; nz_pos = nz_recent[nz_recent > 0]
        if len(nz_pos) >= 3 and pat not in ('dead', 'intermittent'):
            fc = np.minimum(fc, np.mean(nz_pos) * 2.0)
        # Floor: no week below 50% of 8-week median
        nz_floor = yc[-min(8, len(yc)):]; nz_floor = nz_floor[nz_floor > 0]
        if len(nz_floor) >= 3 and pat not in ('dead', 'intermittent'):
            fc = np.maximum(fc, np.median(nz_floor) * 0.5)

        # ---- v3.6: Channel-split branch for wholesale-dominant SKUs ----
        # Compute historical wholesale share over the full history window.
        ws_hist = np.array(s['wholesale'], dtype=float)
        r_hist  = np.array(s['retail'], dtype=float)
        tot_hist = ws_hist.sum() + r_hist.sum()
        ws_share = (ws_hist.sum() / tot_hist) if tot_hist > 0 else 0.0

        fc_retail = None
        fc_wholesale = None
        channel_mode = 'total'
        model_retail = ''
        model_wholesale = ''

        # Only split if (a) SKU is wholesale-dominant, (b) both channel
        # forecasts are available, (c) both channels have some history
        split_available = (
            ws_share >= 0.5
            and sku in sf_fc_retail and sku in sf_fc_wholesale
            and len(sf_fc_retail[sku]) > 0 and len(sf_fc_wholesale[sku]) > 0
            and ws_hist.sum() > 0
        )

        if split_available:
            # Pick best model per channel using the in-sample backtest window
            r_series = r_hist[:len(yc)]
            w_series = ws_hist[:len(yc)]
            r_act = r_series[-TEST:] if len(r_series) >= TEST else r_series
            w_act = w_series[-TEST:] if len(w_series) >= TEST else w_series

            def _pick_best_channel(fc_candidates, actuals):
                if len(actuals) == 0 or len(fc_candidates) == 0:
                    return None
                scores = {}
                for mn, vals in fc_candidates.items():
                    if vals is None or len(vals) == 0:
                        continue
                    pred = vals[:len(actuals)]
                    if len(pred) != len(actuals):
                        continue
                    # Seasonal models need a clear win over non-seasonal to be
                    # picked — protects against overfitting on short histories.
                    pen = 1.10 if mn.endswith('_s4') else 1.0
                    scores[mn] = np.mean(np.abs(pred - actuals)) * pen
                return min(scores, key=scores.get) if scores else None

            best_r = _pick_best_channel(sf_fc_retail[sku], r_act)
            best_w = _pick_best_channel(sf_fc_wholesale[sku], w_act)

            if best_r is not None and best_w is not None:
                fc_retail = np.maximum(sf_fc_retail[sku][best_r], 0)
                fc_wholesale = np.maximum(sf_fc_wholesale[sku][best_w], 0)

                # Per-channel sanity caps.
                # RETAIL is relatively stable → mean*2 cap is fine.
                r_nz = r_hist[-min(13, len(r_hist)):]; r_nz = r_nz[r_nz > 0]
                if len(r_nz) >= 3:
                    fc_retail = np.minimum(fc_retail, np.mean(r_nz) * 2.0)
                # WHOLESALE is lumpy → capping at mean*2 would clip seasonal
                # peaks. Use max*1.5 instead to preserve monthly-buyer spikes
                # while still protecting against runaway forecasts.
                w_nz = ws_hist[-min(26, len(ws_hist)):]; w_nz = w_nz[w_nz > 0]
                if len(w_nz) >= 3:
                    fc_wholesale = np.minimum(fc_wholesale, np.max(w_nz) * 1.5)
                # Wholesale floor: 0 is valid (off-month) so no lower floor

                # For wholesale-dominant SKUs, VP on-top is TRULY additive to
                # the wholesale baseline (which already captures normal recurring
                # wholesale activity). So no cannibalization: just replace fc.
                # MP on-top, however, REPLACES physical retail baseline: a
                # promo week's retail sell-through equals the MP number, not
                # MP + non-promo baseline. Zero fc_retail for weeks where
                # MP on-top is present; MP value itself is added downstream
                # in the Demand Plan sheet (Adjusted FC + On-top Total).
                for j in range(N_FC):
                    if mp_vals[j] > 0:
                        fc_retail[j] = 0
                fc = fc_retail + fc_wholesale
                channel_mode = 'split'
                model_retail = best_r
                model_wholesale = best_w
            # else: fall through to total-mode cannibalization branch

        if channel_mode == 'total':
            # Total-mode baseline = qty_total (retail + webshop + wholesale).
            # When MP on-top is set for a week, zero out the physical retail
            # portion of baseline (MP replaces it). VP stays truly additive —
            # wholesale baseline already captures recurring wholesale, so VP
            # adds genuine new orders on top.
            full_sum = float(np.array(s['full'], dtype=float).sum())
            retail_phys_share = (r_hist.sum() / full_sum) if full_sum > 0 else 0.0
            for j in range(N_FC):
                if mp_vals[j] > 0 and fc[j] > 0:
                    fc[j] *= (1 - retail_phys_share)
            # Approximate channel decomposition for display purposes only
            if ws_share > 0 and tot_hist > 0:
                fc_wholesale = fc * ws_share
                fc_retail = fc * (1 - ws_share)
            else:
                fc_wholesale = np.zeros_like(fc)
                fc_retail = fc.copy()

        results.append({
            'fc': fc, 'model': best, 'pattern': pat, 'ontop': ontop,
            'uplift': round(u, 2), 'cann_rate': round(cr, 3),
            # v3.6: channel-split metadata
            'fc_retail': fc_retail,
            'fc_wholesale': fc_wholesale,
            'channel_mode': channel_mode,
            'ws_share': round(float(ws_share), 3),
            'model_retail': model_retail,
            'model_wholesale': model_wholesale,
            **s,
        })
        mw[best] = mw.get(best, 0) + 1
        bp = bt_metrics(act, cands[best]); bh = bt_metrics(act, cands.get('Hybrid', cands['SES']))
        ah.append(bh); ap.append(bp)
        if not sp: ch_.append(bh); cp_.append(bp)

    def agg(b):
        ta = sum(x['total_act'] for x in b); tf = sum(x['total_fc'] for x in b)
        te = sum(abs(x['total_fc'] - x['total_act']) for x in b)
        return (1 - te/ta)*100 if ta > 0 else 0, ((tf - ta)/ta)*100 if ta > 0 else 0, sum(1 for x in b if x['in_range'])/len(b)*100
    print(f'\n  BACKTEST:')
    for l, bh, bp in [('ALL', ah, ap), ('CLEAN', ch_, cp_)]:
        if not bh: continue
        hf, hb, hh = agg(bh); pf, pb, ph = agg(bp)
        print(f'  {l} ({len(bh)}): Engine FA={pf:.1f}% Bias={pb:+.1f}% HR={ph:.1f}%')
    print(f'  Models: {dict(sorted(mw.items(), key=lambda x: -x[1]))}')

    # ---- v3.4: Latest price for revenue ----
    print(f'\n  Building workbook...')
    sku_sell_prices = get_latest_prices(sales_csv)
    # Fallback to sku_prices.csv for SKUs without recent transactions
    prices_path = os.path.join(bd, 'sku_prices.csv')
    prices_df = pd.DataFrame()
    if HAS_DB_LOADERS:
        prices_df = _fdb.load_sku_prices(prices_path if os.path.exists(prices_path) else None)
    elif os.path.exists(prices_path):
        prices_df = pd.read_csv(prices_path)
    if not prices_df.empty:
        fallback_prices = dict(zip(prices_df['sku'], prices_df['avg_sell_price']))
        for sku in sku_list:
            if sku not in sku_sell_prices or sku_sell_prices.get(sku, 0) == 0:
                sku_sell_prices[sku] = fallback_prices.get(sku, 0)
    else:
        prices_df = pd.DataFrame({'sku': list(sku_sell_prices.keys()),
            'avg_sell_price': list(sku_sell_prices.values()),
            'normal_retail_ppp': [0]*len(sku_sell_prices),
            'normal_webshop_ppp': [0]*len(sku_sell_prices),
            'qty_retail': [0]*len(sku_sell_prices),
            'qty_webshop': [0]*len(sku_sell_prices),
            'qty_wholesale': [0]*len(sku_sell_prices),
            'weeks_active': [0]*len(sku_sell_prices)})
    print(f'  Prices (latest week): {sum(1 for v in sku_sell_prices.values() if v > 0)} SKUs with price')

    # ---- Build VP/MP on-tops for output workbook ----
    if not use_csv:
        # Fall back to preserved corrections from xlsx
        ontop_vp = {}
        ontop_mp = {}
        for idx, sku in enumerate(sku_list):
            vp_vals = []
            mp_vals = []
            for j, cwn in enumerate(new_cws):
                vp_ot = old_vp.get(sku, {}).get(f'on-top demand_{cwn}', 0)
                vp_rg = old_vp.get(sku, {}).get(f'regular increase_{cwn}', 0)
                mp_ot = old_mp.get(sku, {}).get(f'on-top demand_{cwn}', 0)
                mp_rg = old_mp.get(sku, {}).get(f'regular increase_{cwn}', 0)
                vp_vals.append(vp_ot + vp_rg)
                mp_vals.append(mp_ot + mp_rg)
            if any(v > 0 for v in vp_vals): ontop_vp[idx] = vp_vals
            if any(v > 0 for v in mp_vals): ontop_mp[idx] = mp_vals
        print(f'  VP/MP from xlsx corrections: {len(ontop_vp)} VP, {len(ontop_mp)} MP SKUs')

    # ---- v3.4: Build planner factor map preserving corrections by CW ----
    planner_factor_map = {}  # idx -> [factor per FC week]
    for idx, sku in enumerate(sku_list):
        if sku in old_factors:
            factors = []
            for j, cwn in enumerate(new_cws):
                factors.append(old_factors[sku].get(cwn, 1.0))
            planner_factor_map[idx] = factors

    # ---- Portfolio forecast ----
    dp_skus_set = set(s['sku'] for s in skus); portfolio_fc = {}
    if os.path.exists(sales_csv) or HAS_DB_LOADERS:
        portfolio_fc = forecast_portfolio(sales_csv, dp_skus_set, N_FC)
        cat_map_path = os.path.join(bd, 'sku_category_map.csv')
        sku_to_cat = {}
        cat_df = pd.DataFrame()
        if HAS_DB_LOADERS:
            cat_df = _fdb.load_sku_category_map(cat_map_path if os.path.exists(cat_map_path) else None)
        elif os.path.exists(cat_map_path):
            cat_df = pd.read_csv(cat_map_path)
        if not cat_df.empty:
            sku_to_cat = dict(zip(cat_df['sku'], cat_df['cat']))
        for sku in portfolio_fc: portfolio_fc[sku]['cat'] = sku_to_cat.get(sku, 'OTHER')
        print(f'  Portfolio: {len(portfolio_fc)} extra SKUs')

    # ---- v3.7-db: ALWAYS write to Postgres first (forecasts table is the
    # canonical source of truth now). Excel is on-demand only when
    # `generate_xlsx=True`.
    db_run_id = None
    db_rows_inserted = 0
    if HAS_DB_LOADERS:
        try:
            db_run_id = _fdb.write_forecast_run(
                run_type=run_type, run_by_id=run_by_id,
                year_week=cy * 100 + cw, n_skus=len(results),
            )
            if db_run_id is not None:
                # Build per-(sku, week) rows in the shape write_forecasts expects
                _db_rows = []
                for idx, r in enumerate(results):
                    sku = r.get('sku')
                    fc_arr = r.get('fc')
                    if not sku or fc_arr is None:
                        continue
                    fc_r = r.get('fc_retail', np.zeros_like(fc_arr))
                    fc_w = r.get('fc_wholesale', np.zeros_like(fc_arr))
                    vp_arr = ontop_vp.get(idx, [0.0] * N_FC)
                    mp_arr = ontop_mp.get(idx, [0.0] * N_FC)
                    f_arr  = planner_factor_map.get(idx, [1.0] * N_FC)
                    for j in range(N_FC):
                        cwn = CW_START + j
                        ty = cy + (1 if cwn > 52 else 0)
                        tw = cwn if cwn <= 52 else cwn - 52
                        baseline = float(fc_arr[j])
                        baseline_r = float(fc_r[j]) if j < len(fc_r) else 0.0
                        baseline_w = float(fc_w[j]) if j < len(fc_w) else 0.0
                        vp_v = float(vp_arr[j]) if j < len(vp_arr) else 0.0
                        mp_v = float(mp_arr[j]) if j < len(mp_arr) else 0.0
                        pf_v = float(f_arr[j])  if j < len(f_arr)  else 1.0
                        total = baseline * pf_v + vp_v + mp_v
                        # Per-channel split — engine produces fc_r / fc_w from
                        # the channel-split forecast. Include on-tops in the
                        # corresponding channel so forecast_retail +
                        # forecast_wholesale = total. Downstream consumers
                        # (monthly plan loader, Margin Bridge) can use these
                        # directly instead of falling back to ws_share_26w.
                        fc_retail_total    = baseline_r * pf_v + mp_v
                        fc_wholesale_total = baseline_w * pf_v + vp_v
                        _db_rows.append({
                            'sku':            sku,
                            'year':           ty, 'week': tw,
                            'baseline':       baseline,
                            'total':          total,
                            'on_top_vp':      vp_v,
                            'on_top_mp':      mp_v,
                            'forecast_retail':    fc_retail_total,
                            'forecast_wholesale': fc_wholesale_total,
                            'promo_uplift':   r.get('uplift', 1.0),
                            'planner_factor': pf_v,
                            'model_used':     r.get('model', ''),
                            'channel_mode':   r.get('channel_mode', ''),
                        })
                db_rows_inserted = _fdb.write_forecasts(run_id=db_run_id, rows=_db_rows)
                print(f'  DB forecasts: run_id={db_run_id}, {db_rows_inserted:,} rows inserted')
        except Exception as _e:
            print(f'  WARN: DB write failed: {_e}')

    # ---- Excel workbook — only when generate_xlsx=True ----
    if generate_xlsx:
        print(f'\n  Building workbook…')
        wb_out = Workbook()
        ws_dp = wb_out.active; ws_dp.title = DP_SH
        build_dp_sheet(ws_dp, results, rr_current, N_FC, CW_START, planner_factor_map,
                        rr_vp_hist=rr_vp_hist, rr_mp_hist=rr_mp_hist)
        ws_vp = wb_out.create_sheet(VP_SH)
        build_input_sheet(ws_vp, results, ontop_vp, 'DEMAND INPUT - VP (WHOLESALE)', N_FC, CW_START)
        ws_mp = wb_out.create_sheet(MP_SH)
        build_input_sheet(ws_mp, results, ontop_mp, 'DEMAND INPUT - MP (MARKETING / RETAIL)', N_FC, CW_START)
        ws_rev = wb_out.create_sheet('Revenue Dashboard')
        build_revenue_sheet(ws_rev, results, portfolio_fc, sku_sell_prices, N_FC, CW_START)
        ws_det = wb_out.create_sheet('Forecast Detail')
        build_detail_sheet(ws_det, results, N_FC, CW_START)
        ws_ot = wb_out.create_sheet('Demand Output - Total')
        build_output_sheet(ws_ot, results, 'DEMAND OUTPUT - TOTAL', 9, N_FC, CW_START)
        ws_oo = wb_out.create_sheet('Demand Output - On Top')
        build_output_sheet(ws_oo, results, 'DEMAND OUTPUT - ON TOP', 8, N_FC, CW_START, FILL_ONTOP)
        ws_pr = wb_out.create_sheet('Price Reference')
        build_price_sheet(ws_pr, prices_df)
        wb_out.save(output_file)
        print(f'  Saved: {os.path.basename(output_file)}')
    else:
        print('  Skipping Excel (generate_xlsx=False) — download on demand via /api/demand/download-plan')

    # ---- Forecast log (append-only CSV) — kept for FA dashboards that still
    # read the CSV. The DB forecasts table is the canonical source.
    n_csv_log_rows = 0
    try:
        n_csv_log_rows = append_forecast_log(bd, results, ontop_vp, ontop_mp,
                                              planner_factor_map, cy, CW_START, N_FC)
        print(f'  Forecast log (CSV): appended {n_csv_log_rows:,} rows')
    except Exception as _e:
        print(f'  WARN: forecast log append failed: {_e}')

    recalc = '/mnt/skills/public/xlsx/scripts/recalc.py'
    if os.path.exists(recalc):
        print(f'  Recalculating...'); import subprocess
        r = subprocess.run(['python3', recalc, output_file, '60'], capture_output=True, text=True)
        if 'success' in r.stdout.lower(): print(f'  Formulas OK')

    # ---- v3.7: Snapshot Planner Factors per (sku, target_year, target_week) ----
    # Persists what factor was applied for each future week so FA dashboard
    # can later show "with vs without planner factor" once the week is closed.
    # Idempotent on (run_year, run_week, target_year, target_week, sku).
    try:
        from datetime import datetime as _dt_now
        run_date = _dt_now.now().strftime('%Y-%m-%d %H:%M')
        run_year, run_week = cy, cw
        target_year_for_cw = lambda cwn: cy + (1 if cwn > 52 else 0)
        new_rows = []
        for idx, sku in enumerate(sku_list):
            factors = planner_factor_map.get(idx, [1.0] * N_FC)
            for j, cwn in enumerate(new_cws):
                f = factors[j] if j < len(factors) else 1.0
                # Normalise CW for year-rollover (week 53 → next year week 1).
                ty = target_year_for_cw(cwn)
                tw = cwn if cwn <= 52 else cwn - 52
                new_rows.append({
                    'run_date': run_date,
                    'run_year': run_year,
                    'run_week': run_week,
                    'target_year': ty,
                    'target_week': tw,
                    'sku': sku,
                    'factor': float(f),
                })
        if new_rows:
            new_df = pd.DataFrame(new_rows)
            hist_path = os.path.join(bd, 'factor_history.csv')
            # CSV-first; DB factor_history is also maintained
            if os.path.exists(hist_path):
                old = pd.read_csv(hist_path)
                # Drop overlapping rows for this run before append (idempotent re-runs).
                old = old[~((old['run_year'] == run_year) & (old['run_week'] == run_week))]
                merged = pd.concat([old, new_df], ignore_index=True)
            else:
                merged = new_df
            merged.to_csv(hist_path, index=False)
            print(f'  Factor history: appended {len(new_df)} rows -> factor_history.csv')
            # Mirror to DB
            if HAS_DB_LOADERS:
                try:
                    n_db_fh = _fdb.write_factor_history(
                        run_year=run_year, run_week=run_week,
                        rows=new_rows, run_date=run_date,
                    )
                    print(f'  Factor history (DB): inserted {n_db_fh:,} rows')
                except Exception as _e:
                    print(f'  WARN: DB factor_history write failed: {_e}')
    except Exception as _e:
        print(f'  WARN: factor history snapshot failed: {_e}')

    _t_run_end = datetime.now()
    _duration_s = int((_t_run_end - _t_run_start).total_seconds())

    print(f'\n{"="*60}')
    print(f'  DONE — {_duration_s}s')
    if generate_xlsx:
        print(f'  Excel: {os.path.basename(output_file)}')
    if db_run_id is not None:
        print(f'  DB:    forecast_runs.id={db_run_id}, {db_rows_inserted:,} forecast rows')
    print(f'  Run Rate: {N_HIST} weeks ending CW{cw}')
    print(f'  Forecast: CW{CW_START} to CW{CW_START+N_FC-1}')
    print(f'  Corrections preserved: {len(old_factors)} factors, {len(old_vp)} VP, {len(old_mp)} MP')
    print(f'{"="*60}\n')

    return {
        "run_id":         db_run_id,
        "sku_count":      len(results),
        "rows_inserted":  db_rows_inserted,
        "duration":       _duration_s,
        "output_file":    output_file if generate_xlsx else None,
        "n_csv_log_rows": n_csv_log_rows,
        "n_history_csv":  N_HIST,
        "cw_start":       CW_START,
        "cy":             cy,
        "cw":             cw,
    }

if __name__ == '__main__':
    inp = sys.argv[1] if len(sys.argv) > 1 else None
    exit_code = 0
    try:
        run(inp)
    except Exception as e:
        print(f'\n  ERROR: {e}')
        import traceback; traceback.print_exc()
        # v3.6: propagate non-zero exit so the app can detect the failure.
        # Previously this returned 0 and the app reported false success when
        # the save step hit a Windows Excel file-lock.
        exit_code = 1
    try: input('\n  Press Enter to close...')
    except (EOFError, OSError): pass
    sys.exit(exit_code)