"""Compare erp_costs.cost_price vs erp_stock_current.purchase_value."""
import sys
sys.stdout.reconfigure(encoding="utf-8")

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

db = SessionLocal()

print("=== erp_costs (latest by product, sample 5 SKUs) ===")
for r in db.execute(text("""
    SELECT p.sku, p.name, ec.cost_price, ec.ruc, ec.valid_from
    FROM erp_costs ec JOIN dim_products p ON p.id=ec.product_id
    WHERE p.sku IN ('POL09753','POL12848','POL12929','POL09734')
    ORDER BY p.sku, ec.valid_from DESC
""")).mappings():
    name_short = (r["name"] or "")[:40]
    print(f"  {r['sku']:<10s} cost_price={r['cost_price']:<8} ruc={r['ruc']:<6} "
          f"valid_from={r['valid_from']}  ({name_short})")

print()
print("=== erp_stock_current.purchase_value per SKU ===")
print("  (pv = SUM(purchase_value), pv/u = SUM(pv)/SUM(qty) — implied unit cost)")
for r in db.execute(text("""
    SELECT p.sku, SUM(esc.stock_qty)::float qty,
           SUM(esc.purchase_value)::float pv,
           CASE WHEN SUM(esc.stock_qty) > 0
                THEN SUM(esc.purchase_value)/SUM(esc.stock_qty)
                ELSE 0 END::float AS pv_per_unit
    FROM erp_stock_current esc JOIN dim_products p ON p.id=esc.product_id
    WHERE p.sku IN ('POL09753','POL12848','POL12929','POL09734')
    GROUP BY p.sku ORDER BY p.sku
""")).mappings():
    qty = r["qty"] or 0
    pv = r["pv"] or 0
    pvu = r["pv_per_unit"] or 0
    print(f"  {r['sku']:<10s} qty={qty:<8.0f} pv=EUR{pv:<11.2f} pv/u={pvu:.4f}")

print()
print("=== Company-wide: total inventory value by each method ===")
r = db.execute(text("""
    WITH stock_pv AS (
        SELECT esc.product_id,
               SUM(esc.stock_qty)::float qty,
               SUM(esc.purchase_value)::float pv
        FROM erp_stock_current esc
        GROUP BY esc.product_id HAVING SUM(esc.stock_qty) > 0
    )
    SELECT
        COUNT(*) total_with_stock,
        COUNT(ec.cost_price) with_cost_price,
        COUNT(CASE WHEN sp.pv > 0 THEN 1 END) with_pv_gt0,
        SUM(sp.pv)::float pv_sum,
        SUM(sp.qty * COALESCE(ec.cost_price, 0))::float cost_sum
    FROM stock_pv sp
    LEFT JOIN (
        SELECT DISTINCT ON (product_id) product_id, cost_price
        FROM erp_costs ORDER BY product_id, valid_from DESC, id DESC
    ) ec ON ec.product_id = sp.product_id
""")).mappings().first()
print(f"  SKUs with stock:                   {r['total_with_stock']}")
print(f"  ...with cost_price in erp_costs:   {r['with_cost_price']}")
print(f"  ...with purchase_value > 0:        {r['with_pv_gt0']}")
pv_sum = r["pv_sum"] or 0
cost_sum = r["cost_sum"] or 0
print(f"  Method A: SUM(purchase_value)               = EUR {pv_sum:>15,.0f}")
print(f"  Method B: SUM(qty * latest cost_price)      = EUR {cost_sum:>15,.0f}")
print(f"  Delta (B - A) = EUR {cost_sum - pv_sum:,.0f}")

print()
print("=== Top 5 SKUs where the two methods disagree most ===")
for r in db.execute(text("""
    WITH stock_pv AS (
        SELECT esc.product_id,
               SUM(esc.stock_qty)::float qty,
               SUM(esc.purchase_value)::float pv,
               CASE WHEN SUM(esc.stock_qty) > 0
                    THEN SUM(esc.purchase_value)/SUM(esc.stock_qty) ELSE NULL END pv_per_u
        FROM erp_stock_current esc
        GROUP BY esc.product_id HAVING SUM(esc.stock_qty) > 0
    )
    SELECT p.sku, sp.qty, sp.pv_per_u, ec.cost_price,
           ABS(sp.pv_per_u - ec.cost_price) AS diff_per_unit,
           sp.qty * ABS(sp.pv_per_u - ec.cost_price) AS total_eur_diff
    FROM stock_pv sp
    JOIN dim_products p ON p.id=sp.product_id
    LEFT JOIN (
        SELECT DISTINCT ON (product_id) product_id, cost_price
        FROM erp_costs ORDER BY product_id, valid_from DESC, id DESC
    ) ec ON ec.product_id = sp.product_id
    WHERE sp.pv_per_u IS NOT NULL AND ec.cost_price IS NOT NULL
      AND ABS(sp.pv_per_u - ec.cost_price) > 0.001
    ORDER BY total_eur_diff DESC LIMIT 5
""")).mappings():
    print(f"  {r['sku']:<10s} qty={r['qty']:<6.0f} "
          f"pv/u={r['pv_per_u']:.4f}  cost_price={r['cost_price']:.4f}  "
          f"diff/u={r['diff_per_unit']:.4f}  total_diff=EUR{r['total_eur_diff']:>8.0f}")

db.close()