"""Show how channel mapping affects POL12887 RUC blend."""
from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()
print("=== POL12887 — per-channel split (last 13w) ===")
for r in db.execute(text("""
    SELECT
        COALESCE(cm.channel, '(unmapped)') AS channel,
        cm.doc_type,
        COUNT(*) AS n_docs,
        SUM(et.quantity)::float AS qty,
        SUM(et.ruc_eur)::float AS ruc,
        (SUM(et.ruc_eur) / NULLIF(SUM(et.quantity), 0))::float AS ruc_per_unit
    FROM erp_transactions et
    JOIN dim_products p ON p.id = et.product_id
    LEFT JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
    WHERE p.sku = 'POL12887'
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    GROUP BY cm.channel, cm.doc_type
    ORDER BY qty DESC
""")).mappings():
    print(f"  channel={r['channel']:<12} doc={r['doc_type']!s:<8} "
          f"n={r['n_docs']:>4}  qty={r['qty']:>6.0f}  "
          f"ruc={r['ruc']:>10.2f}  ruc/unit={r['ruc_per_unit']:.2f}")

print("\n=== lookup_channel_map ===")
for r in db.execute(text("SELECT * FROM lookup_channel_map ORDER BY id")).mappings():
    print(f"  id={r['id']}  doc={r['doc_type']:<5}  channel={r['channel']}  desc={r['description'][:50]}")

print("\n=== POL12887 — channel_map_id histogram ===")
for r in db.execute(text("""
    SELECT et.channel_map_id, COUNT(*) AS n, SUM(et.quantity)::float AS qty
    FROM erp_transactions et
    JOIN dim_products p ON p.id = et.product_id
    WHERE p.sku = 'POL12887'
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    GROUP BY et.channel_map_id ORDER BY n DESC
""")).mappings():
    print(f"  channel_map_id={r['channel_map_id']}  n={r['n']}  qty={r['qty']}")

db.close()