"""Inspect NabavneCijene.xlsx to confirm it has historical cost per SKU per date."""
from pathlib import Path
import pandas as pd

p = Path(__file__).resolve().parents[1] / "data" / "NabavneCijene.xlsx"
df = pd.read_excel(p)
df = df.rename(columns={
    "Šifra": "sku",
    "Datum": "date",
    "Količina": "qty",
    "Nabavna vrijednost €": "cost_eur",
    "RUC €": "ruc_eur",
    "Marža %": "margin_pct",
})
df["sku"] = df["sku"].astype(str).str.strip()
df["date"] = pd.to_datetime(df["date"], errors="coerce")
df["unit_cost"] = df["cost_eur"] / df["qty"].replace(0, pd.NA)

print(f"rows: {len(df):,}  unique SKUs: {df['sku'].nunique():,}")
print(f"date range: {df['date'].min()} → {df['date'].max()}")

print("\n=== rows per year-month ===")
df["yearmonth"] = df["date"].dt.to_period("M")
for ym, n in df.groupby("yearmonth").size().items():
    print(f"  {ym}: {n:>5,} rows")

print("\n=== unit_cost variation per SKU over time (top 10 with most receipts) ===")
multi = df.groupby("sku").agg(n=("date","count"),
                                first=("date","min"),
                                last=("date","max"),
                                avg_cost=("unit_cost","mean"),
                                std_cost=("unit_cost","std")).reset_index()
multi = multi[multi["n"] >= 3].sort_values("n", ascending=False).head(15)
for r in multi.itertuples():
    print(f"  {r.sku:<12} n={r.n:>3}  span={r.first.strftime('%Y-%m-%d')}→{r.last.strftime('%Y-%m-%d')}  "
          f"avg=€{r.avg_cost:.2f}  σ=€{(r.std_cost or 0):.2f}")

print("\n=== sample receipts showing cost drift ===")
for sku in ["POL09740", "POL09753"]:
    sub = df[df["sku"] == sku].sort_values("date")[["date","qty","unit_cost"]].head(8)
    if not sub.empty:
        print(f"\n  {sku}:")
        for r in sub.itertuples():
            print(f"    {r.date.strftime('%Y-%m-%d')}  qty={r.qty:>5}  unit_cost=€{r.unit_cost:.4f}")