"""Compare three different revenue calculations for the same SKU.
Reproduces what Sales Weekly, Executive Revenue Pulse, and Finance Bridge each show."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

for sku in ("POL09734", "POL12848"):
    print(f"=== {sku} — three revenue calculations for last 26 weeks ===")
    # Method 1: Sales Weekly (qty × channel list price)
    r = db.execute(text("""
        SELECT SUM(v.qty_retail   * COALESCE(p.normal_retail_ppp,  p.avg_sell_price, 0))::float r_retail,
               SUM(v.qty_webshop  * COALESCE(p.normal_webshop_ppp, p.avg_sell_price, 0))::float r_web,
               SUM(v.qty_wholesale* COALESCE(sp.vpc,                p.avg_sell_price, 0))::float r_ws,
               SUM(v.qty_retail + v.qty_webshop + v.qty_wholesale)::float total_qty
        FROM v_sales_weekly_full v
        JOIN dim_products dp ON dp.id = v.product_id
        LEFT JOIN erp_prices  p  ON p.product_id  = v.product_id
        LEFT JOIN sku_planning sp ON sp.product_id = v.product_id
        WHERE dp.sku = :sku
          AND v.year*100 + v.week >= 202548
    """), {"sku": sku}).mappings().first()
    if r:
        rt = r["r_retail"] or 0
        rw = r["r_web"] or 0
        rs = r["r_ws"] or 0
        method1_total = rt + rw + rs
        print(f"  [1] Sales Weekly (qty × list price):")
        print(f"        retail={rt:>9.0f}  webshop={rw:>9.0f}  wholesale={rs:>9.0f}  TOTAL={method1_total:>10.0f}")

    # Method 2: Executive (sum of total_value)
    r = db.execute(text("""
        SELECT SUM(et.total_value)::float total
        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()
    method2 = (r["total"] if r else 0) or 0
    print(f"  [2] Executive (SUM erp_transactions.total_value):     TOTAL={method2:>10.0f}")

    # Method 3: Finance Bridge (sum of tax_base)
    r = db.execute(text("""
        SELECT SUM(et.tax_base)::float total
        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()
    method3 = (r["total"] if r else 0) or 0
    print(f"  [3] Finance Bridge (SUM erp_transactions.tax_base):   TOTAL={method3:>10.0f}")

    # Delta
    print(f"  [1] vs [2]: {method1_total - method2:+.0f}    [2] vs [3]: {method2 - method3:+.0f}")
    print()

db.close()