"""Explain what each price column IS, with real data + lineage."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

# Pick a few well-known SKUs and show all the relevant prices side by side
print("═" * 90)
print("WHAT EACH PRICE COLUMN MEANS — 6 sample SKUs")
print("═" * 90)
print()
print("Columns explained AFTER the table below.")
print()

skus = ('POL09734', 'POL12848', 'POL09753', 'POL09807', 'POL12793', 'POL09881')

rows = db.execute(text("""
    SELECT p.sku, p.name,
           ep.avg_sell_price::float    AS avg_sell,
           ep.normal_retail_ppp::float AS retail_ppp,
           ep.normal_webshop_ppp::float AS web_ppp,
           sp.vpc::float               AS vpc,
           ec.cost_price::float        AS cost
    FROM dim_products p
    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 = ANY(:s)
    ORDER BY p.sku
"""), {"s": list(skus)}).mappings().all()

print(f"  {'SKU':<10s} {'avg_sell':>10s} {'retail_ppp':>11s} {'web_ppp':>10s} {'vpc':>8s} {'cost':>8s}  {'name (truncated)':<30s}")
print(f"  {'─'*100}")
for r in rows:
    avg = r["avg_sell"] or 0
    rt  = r["retail_ppp"] or 0
    wb  = r["web_ppp"] or 0
    vp  = r["vpc"] or 0
    co  = r["cost"] or 0
    nm = (r["name"] or "")[:28]
    print(f"  {r['sku']:<10s} {avg:>10.2f} {rt:>11.2f} {wb:>10.2f} {vp:>8.2f} {co:>8.2f}  {nm}")

# Now show REALIZED per-channel prices for last 26 weeks for these SKUs
print()
print("═" * 90)
print("REALIZED — what these SKUs ACTUALLY sold for in the last 26 weeks per channel")
print("═" * 90)
print()
for sku in skus:
    print(f"  --- {sku} ---")
    r = db.execute(text("""
        SELECT cm.channel,
               SUM(et.quantity)::float    AS qty,
               SUM(et.total_value)::float AS rev_gross,
               SUM(et.tax_base)::float    AS rev_net,
               CASE WHEN SUM(et.quantity)>0
                    THEN SUM(et.total_value)/SUM(et.quantity)
                    ELSE 0 END::float     AS price_gross,
               CASE WHEN SUM(et.quantity)>0
                    THEN SUM(et.tax_base)/SUM(et.quantity)
                    ELSE 0 END::float     AS price_net
        FROM erp_transactions et
        JOIN dim_products p          ON p.id = et.product_id
        JOIN lookup_channel_map cm   ON cm.id = et.channel_map_id
        WHERE p.sku = :sku
          AND et.transaction_date >= now() - INTERVAL '26 weeks'
        GROUP BY cm.channel
        ORDER BY qty DESC
    """), {"sku": sku}).mappings().all()
    if not r:
        print(f"      (no transactions in last 26w)")
        continue
    for c in r:
        print(f"      {c['channel']:<10s}  qty={c['qty']:>7.0f}  gross_price=EUR{c['price_gross']:>7.2f}/u   net_price=EUR{c['price_net']:>6.2f}/u")
    print()

db.close()