"""Top 10 selling SKUs on Austrian web (WSA/WSB) - last 90 days."""
import pandas as pd, glob, sys, io
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')

today = pd.Timestamp('2026-05-13')
cutoff = today - pd.Timedelta(days=90)
print(f"Window: {cutoff.date()} to {today.date()}\n")

files = sorted(glob.glob('data/upload_*at*.xlsx') + glob.glob('data/upload_*At*.xlsx'))

def read_at_file(f):
    """Read Austrian file, auto-detecting header row."""
    for skip in range(0, 6):
        try:
            df = pd.read_excel(f, sheet_name=0, header=skip)
            cols_lower = [str(c).lower() for c in df.columns]
            if any('datum' in c for c in cols_lower) and any('artikal' in c for c in cols_lower):
                return df, skip
        except Exception:
            continue
    return None, None

print("=== Date range per file ===")
all_frames = []
for f in files:
    df, skip = read_at_file(f)
    if df is None:
        print(f"  {f.split(chr(92))[-1]}: COULD NOT PARSE")
        continue
    df['Datum'] = pd.to_datetime(df['Datum'], errors='coerce')
    df = df.dropna(subset=['Datum'])
    dmin, dmax = df['Datum'].min().date(), df['Datum'].max().date()
    print(f"  {f.split(chr(92))[-1]}: rows={len(df):,}, "
          f"header_skip={skip}, date={dmin} to {dmax}")
    df['_src_file'] = f.split(chr(92))[-1]
    all_frames.append(df)

all_at = pd.concat(all_frames, ignore_index=True)
print(f"\n  Raw combined rows: {len(all_at):,}")

# Dedupe on Dokument + Artikal + Datum + Količina + Vrijednost EUR
dedup_cols = ['Dokument', 'Artikal', 'Datum', 'Količina', 'Vrijednost EUR']
all_at = all_at.drop_duplicates(subset=dedup_cols)
print(f"  After dedupe: {len(all_at):,}")
print(f"  Overall date range: {all_at['Datum'].min().date()} to {all_at['Datum'].max().date()}")
print(f"  Doc types overall: {all_at['Tip dok.'].value_counts().to_dict()}")

# Filter: WSA/WSB only, in last 90 days
mask = (all_at['Tip dok.'].isin(['WSA','WSB'])) & \
       (all_at['Datum'] >= cutoff) & (all_at['Datum'] <= today) & \
       (all_at['Količina'] > 0) & \
       (~all_at['Artikal'].astype(str).str.startswith('OST'))  # exclude postage/coupons
filt = all_at[mask].copy()
print(f"\n  After WSA/WSB filter + 90-day window + qty>0: {len(filt):,}")
if len(filt) == 0:
    print("  No rows left — bailing.")
    sys.exit(0)
print(f"  Doc types in filtered: {filt['Tip dok.'].value_counts().to_dict()}")
print(f"  Date span (filtered): {filt['Datum'].min().date()} to {filt['Datum'].max().date()}")

# Aggregate by SKU
agg = filt.groupby(['Artikal','Naziv'], dropna=False).agg(
    qty=('Količina','sum'),
    value_eur=('Vrijednost EUR','sum'),
    orders=('Dokument','nunique'),
).reset_index().sort_values('qty', ascending=False)

print("\n" + "="*80)
print("TOP 10 SKUs by QUANTITY  -  Austrian web (WSA+WSB), last 90 days")
print("="*80)
top10 = agg.head(10).copy()
top10['qty'] = top10['qty'].astype(int)
top10['value_eur'] = top10['value_eur'].round(0).astype(int)
print(top10.to_string(index=False))

print("\n" + "="*80)
print("TOP 10 SKUs by REVENUE (€)  -  Austrian web (WSA+WSB), last 90 days")
print("="*80)
top10_rev = agg.sort_values('value_eur', ascending=False).head(10).copy()
top10_rev['qty'] = top10_rev['qty'].astype(int)
top10_rev['value_eur'] = top10_rev['value_eur'].round(0).astype(int)
print(top10_rev.to_string(index=False))