from pathlib import Path
import pandas as pd
from sqlalchemy import text
from backend.models.database import SessionLocal

ROOT = Path(__file__).resolve().parents[1]

# 1) Weeks in the xlsx — non-NaN columns
df = pd.read_excel(ROOT / "data" / "incoming supply po tjednima.xlsx")
sku_col = df.columns[0]
week_cols = list(df.columns[1:])
print("Weekly totals from xlsx:")
for c in week_cols:
    s = pd.to_numeric(df[c], errors="coerce")
    n = s.notna().sum()
    qty = s.fillna(0).sum()
    print(f"  week {c:>2}: {n:>3} rows, qty {qty:,.0f}")

# 2) SKUs from xlsx vs dim_products
xlsx_skus = set(df[sku_col].astype(str).str.strip())
db = SessionLocal()
db_skus = {r["sku"] for r in db.execute(text(
    "SELECT sku FROM dim_products WHERE sku = ANY(:skus)"
), {"skus": list(xlsx_skus)}).mappings()}
missing = sorted(xlsx_skus - db_skus)
print(f"\nXLSX SKUs: {len(xlsx_skus)}  in DB: {len(db_skus)}  missing: {len(missing)}")
print(f"First 20 missing: {missing[:20]}")
db.close()