"""Stage 3: route visits (F13) and van stock & cash (F14) for the Grocery van channel, built around the real invoices."""
import numpy as np, pandas as pd
from common import *
import warnings; warnings.filterwarnings('ignore')

r = rng(3)
S = real_sales(); Rt = real_returns(); outl = load('dim_outlet'); acc = load('dim_account'); prod = load('dim_product_ext')
gro = outl[outl.channel_id == 'GR']
code2main = dict(gro.assign(c=gro.source_codes.str.split('|')).explode('c')[['c', 'outlet_id']].values)
S['outlet_id'] = S.outlet_id.map(code2main).fillna(S.outlet_id); Rt['outlet_id'] = Rt.outlet_id.map(code2main).fillna(Rt.outlet_id)
S['van'] = S.slsman_cd.astype(str); Rt['van'] = Rt.slsman_cd.astype(str)
xy = gro.set_index('outlet_id')[['latitude', 'longitude']].astype(float)
DEPOT = (24.6440, 46.7700)   # assumed Riyadh depot location (south-east industrial area)


def km(a, b):
    la1, lo1, la2, lo2 = map(np.radians, [a[..., 0], a[..., 1], b[..., 0], b[..., 1]])
    h = np.sin((la2 - la1) / 2) ** 2 + np.cos(la1) * np.cos(la2) * np.sin((lo2 - lo1) / 2) ** 2
    return 6371 * 2 * np.arcsin(np.sqrt(h))

# ---------------- F13 route visits
inv = S.groupby(['van', 'doc_date', 'outlet_id']).agg(drop_value_sar=('net_amt', 'sum'), doc_no=('doc_no', 'first'), docs=('doc_no', 'nunique')).reset_index()
freq = S.groupby(['van', 'outlet_id']).doc_date.nunique().rename('visits').reset_index()
rows = []
for van, d in inv.groupby('van'):
    strike = r.uniform(*P['strike_rate_target'])
    pool = freq[freq.van == van]
    pw = (pool.visits / pool.visits.sum()).values; po = pool.outlet_id.values
    for day, dd in d.groupby('doc_date'):
        prod_o = dd.outlet_id.tolist()
        n_np = r.binomial(len(prod_o) * 3, (1 / strike - 1) / 3) if len(prod_o) else 0
        cand = np.setdiff1d(po, prod_o)
        if len(cand) and n_np:
            cw = pool.set_index('outlet_id').visits.reindex(cand).values.astype(float); cw /= cw.sum()
            np_o = list(r.choice(cand, min(n_np, len(cand)), replace=False, p=cw))
        else:
            np_o = []
        missed = list(r.choice(np.setdiff1d(cand, np_o), min(len(cand) - len(np_o), r.binomial(len(prod_o) + len(np_o), 0.05)), replace=False)) if len(cand) > len(np_o) else []
        stops = prod_o + np_o
        # tour: nearest neighbour from the depot
        pts = np.array([xy.loc[o].values if o in xy.index and not np.isnan(xy.loc[o].values).any() else DEPOT for o in stops])
        order, cur, left = [], np.array(DEPOT), list(range(len(stops)))
        while left:
            dist = km(np.repeat(cur[None], len(left), 0), pts[left]); j = left[int(np.argmin(dist))]
            order.append(j); cur = pts[j]; left.remove(j)
        t = pd.Timestamp(day) + pd.Timedelta(minutes=int(r.integers(450, 510)))
        prev = np.array(DEPOT)
        dv = dd.set_index('outlet_id')
        for seq, j in enumerate(order, 1):
            o = stops[j]; p = pts[j]
            dkm = float(km(prev[None], p[None])[0]) * 1.35
            t += pd.Timedelta(minutes=dkm / 22 * 60 + r.uniform(2, 6))
            sale = o in dv.index
            svc = r.uniform(12, 28) if sale else r.uniform(4, 9)
            outcome = 'sale' if sale else r.choice(['no order', 'owner absent', 'stock sufficient', 'closed'], p=[0.55, 0.18, 0.2, 0.07])
            gps = p + r.normal(0, 0.00018, 2)
            rows.append((f'RT-{van}', van, van, o, day.date(), True, seq, t.strftime('%H:%M'), (t + pd.Timedelta(minutes=svc)).strftime('%H:%M'),
                         round(gps[0], 6), round(gps[1], 6), outcome, dv.doc_no.get(o) if sale else None,
                         round(float(dv.drop_value_sar.get(o)), 2) if sale else 0.0, round(dkm, 2), 'Actual' if sale else 'Simulated'))
            t += pd.Timedelta(minutes=svc); prev = p
        for o in missed:
            rows.append((f'RT-{van}', van, van, o, day.date(), True, None, None, None, None, None, 'not visited', None, 0.0, 0.0, 'Simulated'))
RV = pd.DataFrame(rows, columns=['route_id', 'van_id', 'rep_employee_id', 'outlet_id', 'visit_date', 'planned_flag', 'stop_seq', 'arrival_time',
                                 'departure_time', 'gps_lat', 'gps_lon', 'outcome', 'doc_no', 'drop_value_sar', 'km_from_previous', 'basis'])
RV['route_id'] = 'R-' + RV.van_id
# a few unplanned calls (outlets outside the van's usual list are rare) and fake-GPS check-ins for anomaly tests
fake = r.choice(RV.index[RV.outcome.isin(['no order', 'owner absent'])], 120, replace=False)
RV.loc[fake, 'gps_lat'] = RV.loc[fake, 'gps_lat'] + r.choice([-1, 1], 120) * r.uniform(0.004, 0.012, 120)
RV['anomaly_label'] = None; RV.loc[fake, 'anomaly_label'] = 'gps_far_from_outlet'
RV.insert(0, 'visit_id', [f'V{i+1:07d}' for i in range(len(RV))])
save(RV, 'fact_route_visit')
vis = RV[RV.outcome != 'not visited']
print('  strike rate', round((vis.outcome == 'sale').mean(), 3), '| calls/van-day', round(vis.groupby(['van_id', 'visit_date']).size().mean(), 1))

# ---------------- F14 van stock (van × SKU × day) and cash (van × day)
sold = S[~S.is_foc].groupby(['van', 'prd_cd', 'doc_date']).qty_base_ea.sum()
foc = S[S.is_foc].groupby(['van', 'prd_cd', 'doc_date']).qty_base_ea.sum()
ret = Rt.groupby(['van', 'prd_cd', 'doc_date']).qty_base_ea.sum()
cs = prod.set_index('sku').case_size
srows, crows = [], []
terms = acc.set_index('account_id').payment_terms_days
for van, vs in S.groupby('van'):
    days = np.sort(vs.doc_date.unique()); skus = vs.prd_cd.unique()
    idx = pd.MultiIndex.from_product([skus, days])
    so = sold.loc[van].reindex(idx, fill_value=0).values.reshape(len(skus), len(days)) if van in sold.index.get_level_values(0) else np.zeros((len(skus), len(days)))
    fo = foc.loc[van].reindex(idx, fill_value=0).values.reshape(len(skus), len(days)) if van in foc.index.get_level_values(0) else np.zeros_like(so)
    rt = ret.loc[van].reindex(idx, fill_value=0).values.reshape(len(skus), len(days)) if van in ret.index.get_level_values(0) else np.zeros_like(so)
    out = so + fo
    case = cs.reindex(skus).fillna(12).values
    target = np.ceil(np.quantile(np.where(out > 0, out, np.nan), 0.9, axis=1, method='linear') / case) * case
    target = np.nan_to_num(target, nan=0)
    opening = np.zeros(len(skus))
    for k, d in enumerate(days):
        need = np.maximum(target, out[:, k])
        loaded = np.ceil(np.maximum(0, need - opening) / case) * case
        damaged = np.where(r.random(len(skus)) < 0.004, np.minimum(case, opening + loaded - out[:, k]), 0)
        closing = opening + loaded - out[:, k] + rt[:, k] - damaged
        keep = (opening + loaded + out[:, k] + rt[:, k]) > 0
        for j in np.where(keep)[0]:
            srows.append((van, d, skus[j], opening[j], loaded[j], so[j, k], fo[j, k], rt[j, k], damaged[j], closing[j]))
        opening = closing
    # cash per van-day
    vd = vs[~vs.is_foc].copy()
    vd['terms'] = ('GR-' + vd.outlet_id).map(terms).fillna(0)
    for d, x in vd.groupby('doc_date'):
        credit = x.loc[x.terms > 0, 'net_amt'].sum(); cashlike = x.loc[x.terms <= 0, 'net_amt'].sum()
        card_share = 0.25 + 0.15 * (pd.Timestamp(d) - START).days / (END - START).days
        crows.append((van, d, round(cashlike * (1 - card_share), 2), round(cashlike * card_share, 2), round(credit, 2)))
ST = pd.DataFrame(srows, columns=['van_id', 'stock_date', 'sku', 'opening_qty', 'loaded_qty', 'sold_qty', 'free_goods_qty', 'returned_qty', 'damaged_qty', 'closing_qty'])
# unexplained stock losses on ~1.5% of van-days (labelled)
vday = ST[['van_id', 'stock_date']].drop_duplicates().sample(frac=0.015, random_state=SEED)
mm = ST.merge(vday.assign(f=1), on=['van_id', 'stock_date'], how='left').f.eq(1).values & (ST.closing_qty.values > 0) & (r.random(len(ST)) < 0.25)
ST['stock_variance_qty'] = 0.0
ST.loc[mm, 'stock_variance_qty'] = -np.ceil(ST.loc[mm, 'closing_qty'] * r.uniform(0.1, 0.5, mm.sum()))
ST['anomaly_label'] = np.where(mm, 'stock_unexplained_loss', None)
ST['stock_date'] = pd.to_datetime(ST.stock_date).dt.date; ST['basis'] = 'Simulated'
ST['note'] = 'sold, free goods and returned quantities are Binzagr actuals; loads and stock are simulated'
save(ST.drop(columns='note'), 'fact_van_stock')
C = pd.DataFrame(crows, columns=['van_id', 'cash_date', 'cash_sales_sar', 'card_sales_sar', 'credit_sales_sar'])
C['cash_deposited_sar'] = C.cash_sales_sar
sh = r.random(len(C)) < 0.02
C.loc[sh, 'cash_deposited_sar'] = (C.loc[sh, 'cash_sales_sar'] * (1 - r.uniform(0.03, 0.25, sh.sum()))).round(2)
C['cash_variance_sar'] = (C.cash_deposited_sar - C.cash_sales_sar).round(2)
C['anomaly_label'] = np.where(sh, 'cash_short', None)
C['cash_date'] = pd.to_datetime(C.cash_date).dt.date
save(C, 'fact_van_cash')