"""Prove: per-channel RUC is preserved at the transaction level.

For POL12885 (where wholesale RUC differs sharply from retail), show:
  - per-transaction granularity preserved in erp_transactions
  - aggregated per-channel split derived live
  - blended figure stored in erp_costs.ruc for fallback
"""
from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()

print("=== Sample raw transactions for POL12885 ===")
for r in db.execute(text("""
    SELECT et.transaction_date, cm.channel, cm.doc_type,
           et.quantity::float AS qty,
           et.purchase_value::float AS cost_total,
           et.ruc_eur::float AS ruc_total,
           (et.ruc_eur/NULLIF(et.quantity,0))::float AS ruc_per_unit
    FROM erp_transactions et
    JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
    JOIN dim_products p ON p.id = et.product_id
    WHERE p.sku = 'POL12885'
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    ORDER BY et.transaction_date DESC
    LIMIT 8
""")).mappings():
    print(f"  {r['transaction_date']}  ch={r['channel']:<10} doc={r['doc_type']:<4} "
          f"qty={(r['qty'] or 0):>4.0f}  cost={(r['cost_total'] or 0):>7.2f}  "
          f"ruc={(r['ruc_total'] or 0):>7.2f}  ruc/unit={(r['ruc_per_unit'] or 0):.2f}")

print("\n=== Per-channel aggregation (last 13w) — what the live API computes ===")
for r in db.execute(text("""
    SELECT cm.channel,
           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 lookup_channel_map cm ON cm.id = et.channel_map_id
    JOIN dim_products p ON p.id = et.product_id
    WHERE p.sku = 'POL12885'
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    GROUP BY cm.channel
    ORDER BY cm.channel
""")).mappings():
    print(f"  {r['channel']:<12} {r['n_docs']:>5} docs  "
          f"qty={r['qty']:>5.0f}  ruc=€{r['ruc']:>9.2f}  ruc/unit=€{r['ruc_per_unit']:.2f}")

print("\n=== Blended figure stored in erp_costs.ruc (fallback) ===")
r = db.execute(text("""
    SELECT cost_price::float AS cost, ruc::float AS ruc
    FROM erp_costs ec JOIN dim_products p ON p.id=ec.product_id
    WHERE p.sku = 'POL12885'
""")).mappings().first()
print(f"  cost_price = €{r['cost']:.2f}  (from Sifrarnik NabCj)")
print(f"  ruc        = €{r['ruc']:.2f}  (volume-weighted blend across channels)")

db.close()