"""Find which SKUs / buyers blew up CW21."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

# Top SKUs by on_top_wholesale in CW21
print("=== TOP 15 SKUs by on_top_wholesale in CW21 ===")
print(f"  {'sku':<14s} {'baseline':>10s} {'on_top_ws':>12s} {'on_top_retail':>14s} {'total':>10s}  name")
for r in db.execute(text("""
    SELECT p.sku, p.name,
           f.baseline::float as b, f.on_top_wholesale::float as ws,
           f.on_top_retail::float as r, f.total::float as tot
    FROM forecasts f JOIN dim_products p ON p.id = f.product_id
    WHERE f.run_id = 5 AND f.week = 21
    ORDER BY f.on_top_wholesale DESC NULLS LAST
    LIMIT 15
""")).mappings():
    nm = (r['name'] or '')[:50]
    print(f"  {r['sku']:<14s} {r['b']:>10,.0f} {r['ws']:>12,.0f} {r['r']:>14,.0f} {r['tot']:>10,.0f}  {nm}")

# Compare same SKUs' baseline qty to historical actuals
print()
print("=== Top SKUs: forecast baseline (CW21) vs historical avg (last 4 weeks) ===")
print(f"  {'sku':<14s}  {'fc_baseline':>12s}  {'hist_4w_avg':>12s}  {'ratio':>8s}")
top_skus = [r['sku'] for r in db.execute(text("""
    SELECT p.sku FROM forecasts f JOIN dim_products p ON p.id=f.product_id
    WHERE f.run_id=5 AND f.week=21 ORDER BY f.baseline DESC NULLS LAST LIMIT 15
""")).mappings()]
for sku in top_skus:
    r = db.execute(text("""
        SELECT
            (SELECT f.baseline::float FROM forecasts f
             JOIN dim_products p ON p.id=f.product_id
             WHERE f.run_id=5 AND f.week=21 AND p.sku=:sku LIMIT 1) AS fc,
            (SELECT AVG(qty_total)::float FROM v_sales_weekly_full v
             JOIN dim_products p ON p.id=v.product_id
             WHERE p.sku=:sku AND v.year*100+v.week >= 202617) AS hist
    """), {"sku": sku}).mappings().first()
    if r["fc"] and r["hist"]:
        ratio = r["fc"] / r["hist"]
        marker = " ← inflated" if ratio > 2 else ""
        print(f"  {sku:<14s}  {r['fc']:>12,.0f}  {r['hist']:>12,.1f}  {ratio:>7.1f}x{marker}")
    elif r["fc"]:
        print(f"  {sku:<14s}  {r['fc']:>12,.0f}  {'(no hist)':>12s}")

# Are baseline forecasts per SKU realistic in general?
print()
print("=== Distribution: CW21 baseline vs avg of last 4 weeks (all SKUs) ===")
r = db.execute(text("""
    WITH hist AS (
        SELECT v.product_id, AVG(v.qty_total)::float AS hist_qty
        FROM v_sales_weekly_full v
        WHERE v.year*100+v.week >= 202617
        GROUP BY v.product_id
    )
    SELECT
        COUNT(*) AS n,
        COUNT(CASE WHEN f.baseline > 2*h.hist_qty THEN 1 END) AS n_inflated_2x,
        COUNT(CASE WHEN f.baseline > 5*h.hist_qty THEN 1 END) AS n_inflated_5x,
        SUM(f.baseline)::float AS sum_fc,
        SUM(h.hist_qty)::float AS sum_hist
    FROM forecasts f
    JOIN hist h ON h.product_id = f.product_id
    WHERE f.run_id = 5 AND f.week = 21
""")).mappings().first()
print(f"  SKUs with both fc + hist: {r['n']}")
print(f"  ...where CW21 baseline > 2x hist avg: {r['n_inflated_2x']}")
print(f"  ...where CW21 baseline > 5x hist avg: {r['n_inflated_5x']}")
print(f"  Sum CW21 baseline qty:    {r['sum_fc']:,.0f}")
print(f"  Sum hist avg qty (1 week): {r['sum_hist']:,.0f}")
print(f"  Inflation ratio (total):  {r['sum_fc']/r['sum_hist']:.2f}x")

db.close()