"""Inspect erp_prices: avg_sell_price + channel-specific PPPs.
Goal: explain to the user what avg_sell_price *is*."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

print("=== erp_prices columns and meaning (4 sample SKUs) ===")
print(f"  {'sku':<10s} {'avg_sell':>8s} {'retail_ppp':>10s} {'web_ppp':>8s}  weeks_active  valid_from")
for r in db.execute(text("""
    SELECT p.sku, ep.avg_sell_price, ep.normal_retail_ppp,
           ep.normal_webshop_ppp, ep.weeks_active, ep.valid_from
    FROM erp_prices ep JOIN dim_products p ON p.id=ep.product_id
    WHERE p.sku IN ('POL09753','POL12848','POL12929','POL09734')
    ORDER BY p.sku
""")).mappings():
    a = r["avg_sell_price"] or 0
    rp = r["normal_retail_ppp"] or 0
    wp = r["normal_webshop_ppp"] or 0
    print(f"  {r['sku']:<10s} {a:>8.2f} {rp:>10.2f} {wp:>8.2f}  weeks_active={r['weeks_active']:>3}  {r['valid_from']}")

print()
print("=== vpc (wholesale price) lives on sku_planning ===")
for r in db.execute(text("""
    SELECT p.sku, sp.vpc, sp.ws_share_26w
    FROM sku_planning sp JOIN dim_products p ON p.id=sp.product_id
    WHERE p.sku IN ('POL09753','POL12848','POL12929','POL09734')
    ORDER BY p.sku
""")).mappings():
    vpc = r["vpc"] or 0
    wss = r["ws_share_26w"] or 0
    print(f"  {r['sku']:<10s} vpc(wholesale)={vpc:>7.2f}  ws_share_26w={wss:.2%}")

print()
print("=== Check: is avg_sell_price = the blended average of channels? ===")
print("Probably: weighted average of all transaction prices over recent window.")
print("Let me verify against erp_transactions for one SKU.")
print()
for sku in ("POL09734", "POL12848"):
    print(f"--- {sku} ---")
    # Per-channel realized avg
    for r in db.execute(text("""
        SELECT cm.channel,
               SUM(et.quantity)::float qty,
               SUM(et.total_value)::float rev,
               CASE WHEN SUM(et.quantity)>0
                    THEN SUM(et.total_value)/SUM(et.quantity)
                    ELSE 0 END::float AS price_per_unit
        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 rev DESC
    """), {"sku": sku}).mappings():
        print(f"  realized last 26w  {r['channel']:<10s}  qty={r['qty']:>8.0f}  rev={r['rev']:>9.2f}  price/u={r['price_per_unit']:.4f}")
    # Total mix
    t = db.execute(text("""
        SELECT SUM(et.quantity)::float qty,
               SUM(et.total_value)::float rev
        FROM erp_transactions et
        JOIN dim_products p ON p.id = et.product_id
        WHERE p.sku = :sku AND et.transaction_date >= now() - INTERVAL '26 weeks'
    """), {"sku": sku}).mappings().first()
    if t and t["qty"]:
        blended = t["rev"] / t["qty"]
        print(f"  BLENDED last 26w (all channels): {blended:.4f}")
    # Compare to avg_sell_price
    avg_db = db.execute(text("""
        SELECT ep.avg_sell_price::float
        FROM erp_prices ep JOIN dim_products p ON p.id = ep.product_id
        WHERE p.sku = :sku
    """), {"sku": sku}).scalar()
    print(f"  erp_prices.avg_sell_price        : {avg_db}")
    print()

db.close()