"""Find every cost/price source that mentions POL12887."""
from sqlalchemy import text
from backend.models.database import SessionLocal

SKU = "POL12887"
db = SessionLocal()

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

# erp_costs
print("=== erp_costs ===")
rows = db.execute(text("""
    SELECT * FROM erp_costs WHERE product_id=:p ORDER BY valid_from DESC
"""), {"p": pid}).mappings().all()
print(f"  rows: {len(rows)}")
for r in rows[:5]:
    print(f"  {dict(r)}")

# npd_products
print("\n=== npd_products ===")
r = db.execute(text("""
    SELECT sku, cost_price, msrp_hr, msrp_si, launch_month, arrived
    FROM npd_products WHERE sku=:s
"""), {"s": SKU}).mappings().first()
print(f"  {dict(r) if r else None}")

# erp_transactions implied cost
print("\n=== erp_transactions (implied cost from purchase_value) ===")
rows = db.execute(text("""
    SELECT
        SUM(quantity)::float                              AS units,
        SUM(purchase_value)::float                        AS purchase_value,
        SUM(purchase_value)::float / NULLIF(SUM(quantity), 0) AS implied_cost
    FROM erp_transactions WHERE product_id=:p
"""), {"p": pid}).mappings().first()
print(f"  {dict(rows) if rows else None}")

# erp_stock_current
print("\n=== erp_stock_current ===")
rows = db.execute(text("""
    SELECT
        SUM(stock_qty)::float                              AS units,
        SUM(purchase_value)::float                         AS purchase_value,
        SUM(purchase_value)::float / NULLIF(SUM(stock_qty), 0) AS implied_cost
    FROM erp_stock_current WHERE product_id=:p
"""), {"p": pid}).mappings().first()
print(f"  {dict(rows) if rows else None}")

# Other tables that might have cost
print("\n=== columns containing 'cost' or 'nabav' across all tables ===")
for c in db.execute(text("""
    SELECT table_name, column_name
    FROM information_schema.columns
    WHERE table_schema='public'
      AND (column_name ILIKE '%cost%' OR column_name ILIKE '%nabav%' OR column_name ILIKE '%nc%')
    ORDER BY table_name
""")).mappings():
    print(f"  {c['table_name']}.{c['column_name']}")

# supply_master
print("\n=== supply_master ===")
r = db.execute(text("""
    SELECT * FROM supply_master WHERE product_id=:p
"""), {"p": pid}).mappings().first()
print(f"  {dict(r) if r else None}")

# erp_nc30
print("\n=== erp_nc30 ===")
r = db.execute(text("""
    SELECT * FROM erp_nc30 WHERE product_id=:p
"""), {"p": pid}).mappings().first()
print(f"  {dict(r) if r else None}")

db.close()