from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()
print("=== POL12887 in dim_products ===")
for r in db.execute(text(
    "SELECT id, sku, name FROM dim_products WHERE sku ILIKE 'POL12887%'"
)).mappings():
    print(f"  id={r['id']}  sku={r['sku']}  name={r['name']}")

print("\n=== aggregate by product_id (last 13w) ===")
for r in db.execute(text("""
    SELECT et.product_id, p.sku, 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
    WHERE p.sku ILIKE 'POL12887%'
      AND et.transaction_date >= (CURRENT_DATE - INTERVAL '13 weeks')
    GROUP BY et.product_id, p.sku
""")).mappings():
    print(f"  pid={r['product_id']}  sku={r['sku']}  n_docs={r['n_docs']}  "
          f"qty={r['qty']}  ruc={r['ruc']}  ruc/unit={r['ruc_per_unit']:.4f}")

print("\n=== stored value in erp_costs ===")
for r in db.execute(text("""
    SELECT ec.product_id, p.sku, ec.cost_price, ec.ruc
    FROM erp_costs ec JOIN dim_products p ON p.id=ec.product_id
    WHERE p.sku ILIKE 'POL12887%'
""")).mappings():
    print(f"  {r['sku']}  cost={r['cost_price']}  ruc={r['ruc']}")

db.close()