"""Investigate every RUC / price source for one SKU.

Goal: show why the SKU detail shows RUC=4.95 even though
  wholesale margin = VPC - cost = 9.59 - 2.90 = 6.69.
"""
from sqlalchemy import text
from backend.models.database import SessionLocal

SKU = "POL12885"
db = SessionLocal()

# Resolve product_id
pid = db.execute(text(
    "SELECT id FROM dim_products WHERE sku = :sku"
), {"sku": SKU}).scalar()
print(f"SKU={SKU} product_id={pid}\n")

# ---------- erp_costs (static RUC the SkuDetail card uses) ----------
print("=== erp_costs (latest by valid_from) ===")
for r in db.execute(text("""
    SELECT cost_price, ruc, valid_from
    FROM erp_costs WHERE product_id = :pid
    ORDER BY valid_from DESC, id DESC LIMIT 5
"""), {"pid": pid}).mappings():
    print(f"  valid_from={r['valid_from']}  cost={r['cost_price']}  ruc={r['ruc']}")

# ---------- erp_prices ----------
print("\n=== erp_prices (latest) ===")
for r in db.execute(text("""
    SELECT avg_sell_price, normal_retail_ppp, normal_webshop_ppp, valid_from
    FROM erp_prices WHERE product_id = :pid
    ORDER BY valid_from DESC, id DESC LIMIT 3
"""), {"pid": pid}).mappings():
    print(f"  valid_from={r['valid_from']}  avg_sell={r['avg_sell_price']}  "
          f"retail_ppp={r['normal_retail_ppp']}  webshop_ppp={r['normal_webshop_ppp']}")

# ---------- sku_planning.vpc ----------
print("\n=== sku_planning ===")
sp = db.execute(text("""
    SELECT vpc, ws_share_26w
    FROM sku_planning WHERE product_id = :pid
"""), {"pid": pid}).mappings().first()
print(f"  vpc={sp['vpc'] if sp else None}  ws_share_26w={sp['ws_share_26w'] if sp else None}")

# ---------- erp_transactions: implied per-unit RUC by channel ----------
print("\n=== erp_transactions (last 13 weeks, by channel) ===")
for r in db.execute(text("""
    SELECT cm.channel,
           SUM(et.quantity)::float                          AS units,
           SUM(et.ruc_eur)::float                           AS ruc_total,
           (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
    WHERE et.product_id = :pid
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    GROUP BY cm.channel
    ORDER BY cm.channel
"""), {"pid": pid}).mappings():
    print(f"  channel={r['channel']:10s}  units={r['units']:8.0f}  "
          f"ruc_total={r['ruc_total']:10.2f}  ruc/unit={r['ruc_per_unit']:.2f}")

# Same, but all-time
print("\n=== erp_transactions (all-time, by channel) ===")
for r in db.execute(text("""
    SELECT cm.channel,
           SUM(et.quantity)::float                          AS units,
           SUM(et.ruc_eur)::float                           AS ruc_total,
           (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
    WHERE et.product_id = :pid
    GROUP BY cm.channel
    ORDER BY cm.channel
"""), {"pid": pid}).mappings():
    print(f"  channel={r['channel']:10s}  units={r['units']:8.0f}  "
          f"ruc_total={r['ruc_total']:10.2f}  ruc/unit={r['ruc_per_unit']:.2f}")

# ---------- erp_costs columns: is there channel info? ----------
print("\n=== erp_costs columns ===")
for c in db.execute(text("""
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name='erp_costs' ORDER BY ordinal_position
""")).mappings():
    print(f"  {c['column_name']}: {c['data_type']}")

# ---------- erp_transactions columns ----------
print("\n=== erp_transactions columns ===")
for c in db.execute(text("""
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name='erp_transactions' ORDER BY ordinal_position
""")).mappings():
    print(f"  {c['column_name']}: {c['data_type']}")

db.close()