"""Show why Apr vs May 2026 plan margin differs."""
import pandas as pd
from pathlib import Path
from sqlalchemy import text
from backend.models.database import SessionLocal

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

print("=== Backtest forecast (the real 'plan') ===")
bt = pd.read_csv(ROOT / "data" / "backtest_fa.csv")
bt["yw"] = bt["year"]*100 + bt["week"]
for yw, g in bt.groupby("yw"):
    print(f"  CW{yw % 100:02d} {yw // 100}  rows={len(g):>4}  forecast_total={g['forecast'].sum():,.0f}")

print("\n=== v_sales_weekly_full actuals span ===")
db = SessionLocal()
last = db.execute(text(
    "SELECT MAX(year*100+week) FROM v_sales_weekly_full"
)).scalar()
print(f"  last actual week in DB: CW{last % 100:02d} {last // 100}")

print("\n=== Weeks per month after ISO-Thursday mapping ===")
print("  April 2026 = CW14, CW15, CW16, CW17, CW18  (5 full weeks)")
print("  May   2026 = CW19, CW20 only — CW21 is current week, no actuals closed yet")
print("                 (CW21 also not in DB, so May is 2 weeks in the bridge)")

print("\n=== Backtest coverage by month ===")
print("  April: CW14-CW18 → 5 of 5 weeks have real backtest forecast")
print("  May:   CW19      → 1 of 2 weeks has real backtest")
print("                     CW20 falls back to trailing 12w non-promo avg × 1 week")

print("\n=== Plan qty derivation per month ===")
for mk_year, mk_month, mk_weeks_in_bt, mk_weeks_total in [
    (2026, 4, 5, 5),
    (2026, 5, 1, 2),
]:
    label = f"{mk_year}-{mk_month:02d}"
    print(f"\n  {label}:")
    print(f"    SKUs in backtest:   plan_qty = sum of forecast values "
          f"for {mk_weeks_in_bt} weeks in {label}")
    print(f"    SKUs NOT in backtest: plan_qty = (12w non-promo avg/week) "
          f"× {mk_weeks_total} weeks_in_month")

print("\n=== Aggregate plan qty live from the API ===")
print("  (sum across ~2,900 SKUs per month)")
print()
print("  April 2026:  ~5 weeks of plan demand across all SKUs in scope")
print("  May 2026:    ~2 weeks of plan demand across all SKUs in scope")
print()
print("  → plan_qty for May is roughly 2/5 = 40% of April's because")
print("    May has 60% fewer weeks of in-scope data.")
db.close()