"""Why is CW21+ forecast 7x past reality?"""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

# 1. Total qty / RUC per week from forecasts table (CW21-33)
print("=== Forecasts table — totals per week (CW21-33) ===")
print(f"{'CW':<6s} {'baseline_qty':>14s} {'on_top_ws_qty':>14s} {'on_top_retail_qty':>18s} {'total_qty':>10s}")
for r in db.execute(text("""
    SELECT week,
           SUM(baseline)::float AS baseline,
           SUM(on_top_wholesale)::float AS ws,
           SUM(on_top_retail)::float AS retail,
           SUM(total)::float AS tot
    FROM forecasts
    WHERE run_id = 5 AND week BETWEEN 21 AND 33
    GROUP BY week ORDER BY week
""")).mappings():
    print(f"CW{r['week']:<4d}  {r['baseline']:>14,.0f} {r['ws']:>14,.0f} {r['retail']:>18,.0f} {r['tot']:>10,.0f}")

# 2. Historical actuals - what was qty per week in past?
print()
print("=== Historical actual units per week (last 8 weeks, all channels) ===")
for r in db.execute(text("""
    SELECT EXTRACT(WEEK FROM transaction_date)::int AS wk,
           SUM(quantity)::float AS qty,
           SUM(ruc_eur)::float AS ruc
    FROM erp_transactions
    WHERE transaction_date >= '2026-03-23'
    GROUP BY wk ORDER BY wk
""")).mappings():
    print(f"  CW{r['wk']}: qty={r['qty']:>10,.0f}  ruc=EUR{r['ruc']:>10,.0f}")

# 3. RUC rate per SKU from last 4 weeks
print()
print("=== retail_ruc_rate & wholesale_ruc_rate from last 4 weeks (sample SKUs) ===")
for r in db.execute(text("""
    WITH recent_yws AS (
        SELECT DISTINCT year, week FROM v_sales_weekly_full
        ORDER BY year DESC, week DESC LIMIT 4
    ),
    ruc_per_sku AS (
        SELECT et.product_id,
               NULLIF(SUM(CASE WHEN cm.channel IN ('retail','webshop') THEN et.quantity ELSE 0 END), 0) AS qty_r,
               SUM(CASE WHEN cm.channel IN ('retail','webshop') THEN et.ruc_eur ELSE 0 END) AS ruc_r,
               NULLIF(SUM(CASE WHEN cm.channel = 'wholesale' THEN et.quantity ELSE 0 END), 0) AS qty_w,
               SUM(CASE WHEN cm.channel = 'wholesale' THEN et.ruc_eur ELSE 0 END) AS ruc_w
        FROM erp_transactions et
        JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
        WHERE (EXTRACT(ISOYEAR FROM et.transaction_date)::int * 100
             + EXTRACT(WEEK FROM et.transaction_date)::int)
              IN (SELECT year*100+week FROM recent_yws)
        GROUP BY et.product_id
    )
    SELECT p.sku, r.ruc_r/r.qty_r AS retail_ruc_rate, r.ruc_w/r.qty_w AS wholesale_ruc_rate,
           ep.avg_sell_price::float as avg_sell,
           sp.vpc::float as vpc,
           ec.cost_price::float as cost
    FROM ruc_per_sku r
    JOIN dim_products p ON p.id = r.product_id
    LEFT JOIN erp_prices ep ON ep.product_id = p.id
    LEFT JOIN sku_planning sp ON sp.product_id = p.id
    LEFT JOIN erp_costs ec ON ec.product_id = p.id
    WHERE p.sku IN ('POL09734','POL12848','POL09753','POL09881','POL04469')
""")).mappings():
    print(f"  {r['sku']}  retail_ruc_rate={r['retail_ruc_rate']}  ws_ruc_rate={r['wholesale_ruc_rate']}  avg_sell={r['avg_sell']}  vpc={r['vpc']}  cost={r['cost']}")

# 4. Compute what CW21 forecast SHOULD be — total qty × realistic per-unit margin
print()
print("=== CW21 expected sanity check ===")
r = db.execute(text("""
    SELECT SUM(baseline)::float as base_qty, SUM(on_top_wholesale)::float as ws_qty,
           SUM(on_top_retail)::float as r_qty, SUM(total)::float as tot
    FROM forecasts WHERE run_id=5 AND week=21
""")).mappings().first()
print(f"  CW21 baseline qty:        {r['base_qty']:,.0f}")
print(f"  CW21 on_top_wholesale qty: {r['ws_qty']:,.0f}")
print(f"  CW21 on_top_retail qty:    {r['r_qty']:,.0f}")
print(f"  CW21 total qty:           {r['tot']:,.0f}")

# Compare past week
r = db.execute(text("""
    SELECT SUM(quantity)::float qty, SUM(ruc_eur)::float ruc
    FROM erp_transactions
    WHERE EXTRACT(WEEK FROM transaction_date) = 20 AND EXTRACT(YEAR FROM transaction_date) = 2026
""")).mappings().first()
print(f"\n  CW20 actual qty (past): {r['qty']:,.0f}  ruc=EUR{r['ruc']:,.0f}")
print(f"  Implied avg RUC/u in CW20: EUR{r['ruc']/r['qty']:.4f}/u")

db.close()