"""Build the base sales / returns facts used by every prediction step (written to WORK as parquet)."""
import numpy as np, pandas as pd
from pcommon import *

s = pd.read_parquet(f'{CLEAN}/riyadh_sales_lines.parquet')
r = pd.read_parquet(f'{CLEAN}/riyadh_return_lines.parquet')
u = pd.read_parquet(f'{CLEAN}/dim_uom_conversion.parquet')
p = pd.read_parquet(f'{CLEAN}/dim_product.parquet')

# case factor = units (EA) per selling unit (UOS) for each product
uos = u[u.uom_cd == 'UOS'].groupby('prd_cd').conv_factor.max()
obs = s[s.uom_cd == 'UOS'].groupby('prd_cd').conv_factor.max()          # factor seen on the sales lines themselves
case_factor = uos.combine_first(obs)

def prep(df, kind):
    d = df.copy()
    d['date'] = pd.to_datetime(d.doc_date)
    d['week_end'] = week_end(d['date'])
    d['outlet_id'] = outlet_id(d.dist_cd, d.cust_cd)
    d['van'] = d.slsman_cd.astype(str)
    d['sku'] = d.prd_cd.astype(str)
    d['case_factor'] = d.sku.map(case_factor).fillna(1).clip(lower=1)
    d['qty_cases'] = d.qty_base_ea / d.case_factor
    d['sellable'] = ~d.is_foc
    d['kind'] = kind
    return d[['kind', 'dist_cd', 'cust_cd', 'outlet_id', 'van', 'sku', 'prd_desc', 'doc_no', 'date', 'week_end', 'month',
              'uom_cd', 'qty', 'qty_base_ea', 'case_factor', 'qty_cases', 'net_amt', 'is_foc', 'sellable']]

S = prep(s, 'sale'); R = prep(r, 'return')
S.to_parquet(f'{WORK}/sales.parquet', index=False); R.to_parquet(f'{WORK}/returns.parquet', index=False)

prod = p[['prd_cd', 'prd_desc_en', 'prd_desc_ar', 'l1_code', 'l1_desc', 'l2_desc', 'l4_desc', 'status']].rename(columns={'prd_cd': 'sku'})
prod['sku'] = prod.sku.astype(str)
prod['case_factor'] = prod.sku.map(case_factor)
prod.to_parquet(f'{WORK}/products.parquet', index=False)
print('sales', S.shape, 'returns', R.shape, 'case factor coverage of sales lines', round(S.sku.isin(case_factor.index).mean(), 4))
print('weeks', S.week_end.min().date(), '->', S.week_end.max().date(), S.week_end.nunique())
print('qty_cases total', round(S.qty_cases.sum()), 'net', round(S.net_amt.sum(), 2))