"""Check what historical plan vs actual data we have for the bridge."""
from pathlib import Path
import pandas as pd
from sqlalchemy import text
from backend.models.database import SessionLocal

print("=== backtest_fa.csv coverage ===")
p = Path(__file__).resolve().parents[1] / "data" / "backtest_fa.csv"
if p.exists():
    bt = pd.read_csv(p)
    bt["yw"] = bt["year"] * 100 + bt["week"]
    print(f"  rows: {len(bt):,}, SKUs: {bt['sku'].nunique():,}")
    print(f"  span: {bt['yw'].min()} → {bt['yw'].max()}")
    print(f"  weeks with both forecast+actual:")
    g = bt[bt['forecast'].notna() & bt['actual'].notna()].groupby('yw').size()
    for yw, n in g.items():
        print(f"    {yw}: {n:,} SKU-weeks")
else:
    print("  MISSING")

print("\n=== v_sales_weekly_full month-by-month ===")
db = SessionLocal()
for r in db.execute(text("""
    SELECT year, week, COUNT(*) AS rows, SUM(qty_total)::float AS units
    FROM v_sales_weekly_full
    GROUP BY year, week ORDER BY year, week
""")).mappings():
    yw = r["year"] * 100 + r["week"]
    if yw % 100 in (1, 5, 9, 13, 18, 22, 26, 30, 35, 39, 44, 48):  # month boundaries-ish
        print(f"  {r['year']}W{r['week']:02d}  rows={r['rows']:>4,}  units={r['units']:>9,.0f}")

print("\n=== erp_transactions month-by-month ===")
for r in db.execute(text("""
    SELECT EXTRACT(YEAR FROM transaction_date)::int AS y,
           EXTRACT(MONTH FROM transaction_date)::int AS m,
           COUNT(*)::int AS n, SUM(quantity)::float AS qty,
           SUM(total_value)::float AS rev, SUM(purchase_value)::float AS cost
    FROM erp_transactions
    GROUP BY y, m ORDER BY y, m
""")).mappings():
    print(f"  {r['y']}-{r['m']:02d}  n={r['n']:>6,}  qty={r['qty']:>9,.0f}  "
          f"rev=€{r['rev']:>10,.0f}  cost=€{r['cost']:>10,.0f}")

print("\n=== forecasts table — plan side ===")
for r in db.execute(text("""
    SELECT run_id, MIN(year*100+week)::int AS min_yw,
           MAX(year*100+week)::int AS max_yw,
           COUNT(DISTINCT product_id)::int AS skus,
           SUM(total)::float AS total_qty
    FROM forecasts GROUP BY run_id ORDER BY run_id
""")).mappings():
    print(f"  run_id={r['run_id']}  {r['min_yw']} → {r['max_yw']}  "
          f"skus={r['skus']:,}  total={r['total_qty']:,.0f}")
db.close()