"""Why does Executive Stockout return 0 SKUs after Q6?"""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from backend.services import executive_service as svc
from backend.services.coverage_classifier import classify_coverage
from sqlalchemy import text

db = SessionLocal()

# Run the same SQL the endpoint uses, then bucket statuses
cur_y, cur_w = svc._iso_week_now(db)
horizon = [svc._shift_iso_week(cur_y, cur_w, i) for i in range(13)]
horizon_yw = [y * 100 + w for (y, w) in horizon]
cutoff = svc._shift_iso_week(cur_y, cur_w, -13)
avg_cutoff_yw = cutoff[0] * 100 + cutoff[1]

import pandas as pd
rows = pd.read_sql(text("""
    WITH
    wh AS (
        SELECT esc.product_id, SUM(esc.stock_qty)::float AS wh_qty
        FROM erp_stock_current esc
        JOIN dim_stores ds ON ds.id = esc.store_id
        WHERE ds.is_warehouse GROUP BY esc.product_id
    ),
    fc AS (
        SELECT product_id, AVG(COALESCE(total,0))::float AS avg_demand
        FROM forecasts WHERE run_id = (SELECT MAX(run_id) FROM forecasts)
          AND year*100+week = ANY(:yws) GROUP BY product_id
    ),
    incoming_in_lt AS (
        SELECT inc.product_id, SUM(inc.quantity)::float AS qty
        FROM incoming_supply inc
        JOIN supply_master sm ON sm.product_id = inc.product_id
        WHERE inc.quantity > 0
          AND inc.year*100+inc.week >= :cur_yw
          AND inc.year*100+inc.week <  :cur_yw + COALESCE(sm.lead_time_weeks,0)::int + 1
        GROUP BY inc.product_id
    )
    SELECT p.sku, p.name, sp.tier,
           COALESCE(wh.wh_qty,0) AS wh_stock,
           COALESCE(fc.avg_demand,0) AS weekly_demand,
           sm.lead_time_weeks::float AS lead_time_weeks,
           COALESCE(inc_lt.qty,0)::float AS incoming_in_lt
    FROM dim_products p
    JOIN sku_planning sp ON sp.product_id = p.id
    LEFT JOIN supply_master sm  ON sm.product_id = p.id
    LEFT JOIN wh                ON wh.product_id = p.id
    LEFT JOIN fc                ON fc.product_id = p.id
    LEFT JOIN incoming_in_lt inc_lt ON inc_lt.product_id = p.id
    WHERE sp.tier IN ('01 GOLD','02 SILVER','03 BRONZE')
      AND COALESCE(fc.avg_demand,0) > 0
"""), db.bind, params={"yws": horizon_yw, "cur_yw": cur_y*100+cur_w})

print(f"Rows from SQL: {len(rows)}")

# Classify each
status_counts = {}
sample_at_risk = []
for r in rows.itertuples():
    lt = float(r.lead_time_weeks) if pd.notna(r.lead_time_weeks) else None
    cls = classify_coverage(
        stock_now=float(r.wh_stock),
        weekly_demand=float(r.weekly_demand),
        lead_time_weeks=lt,
        incoming_in_lt=float(r.incoming_in_lt),
    )
    status_counts[cls.status] = status_counts.get(cls.status, 0) + 1
    if cls.is_at_risk and len(sample_at_risk) < 5:
        sample_at_risk.append((r.sku, cls.status, r.wh_stock, r.weekly_demand, lt, r.incoming_in_lt, cls.effective_weeks_cover))

print()
print("Status distribution:")
for s, n in sorted(status_counts.items(), key=lambda x: -x[1]):
    print(f"  {s:<14s}: {n}")
print()
print("Sample at-risk:")
for s in sample_at_risk:
    print(f"  {s}")

# Now check: how many rows where wh_stock/demand < 3 weeks (old criterion)?
rows["bare_cover"] = rows["wh_stock"] / rows["weekly_demand"].replace(0, pd.NA)
old_at_risk = rows[rows["bare_cover"] < 3]
print()
print(f"Old criterion (bare_cover < 3w): {len(old_at_risk)} SKUs")
print()
print("Of those, what's their new status?")
old_status = {}
for r in old_at_risk.itertuples():
    lt = float(r.lead_time_weeks) if pd.notna(r.lead_time_weeks) else None
    cls = classify_coverage(
        stock_now=float(r.wh_stock),
        weekly_demand=float(r.weekly_demand),
        lead_time_weeks=lt,
        incoming_in_lt=float(r.incoming_in_lt),
    )
    old_status[cls.status] = old_status.get(cls.status, 0) + 1
for s, n in sorted(old_status.items(), key=lambda x: -x[1]):
    print(f"  {s:<14s}: {n}")

print()
print("Top 5 'old at risk' SKUs and why classifier sees them differently:")
for r in old_at_risk.head(5).itertuples():
    lt = float(r.lead_time_weeks) if pd.notna(r.lead_time_weeks) else None
    cls = classify_coverage(
        stock_now=float(r.wh_stock),
        weekly_demand=float(r.weekly_demand),
        lead_time_weeks=lt,
        incoming_in_lt=float(r.incoming_in_lt),
    )
    print(f"  {r.sku}  stock={r.wh_stock:.0f}u  demand={r.weekly_demand:.1f}/w  bare_cover={r.bare_cover:.1f}w  LT={lt}  inc_in_lt={r.incoming_in_lt:.0f}u  eff={cls.effective_weeks_cover}  status={cls.status}")

db.close()