"""Compare three possible COGS sources for a few SKUs.
Goal: figure out which is the right basis for the bridge's 'actual COGS'."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

# 1. Does erp_transactions.purchase_value actually have data?
print("=== Population check ===")
r = db.execute(text("""
    SELECT COUNT(*) total_rows,
           COUNT(purchase_value) with_pv,
           COUNT(CASE WHEN purchase_value > 0 THEN 1 END) with_pv_gt0,
           SUM(purchase_value)::float total_pv
    FROM erp_transactions
""")).mappings().first()
print(f"  Total rows:          {r['total_rows']:>12,}")
print(f"  ...with purchase_value populated: {r['with_pv']:>12,}")
print(f"  ...with purchase_value > 0:       {r['with_pv_gt0']:>12,}")
print(f"  SUM(purchase_value):              EUR {(r['total_pv'] or 0):,.0f}")
print()

# 2. Per-SKU: compare three COGS proxies for the last 13 weeks
print("=== Per-SKU COGS comparison — last 13 weeks ===")
print(f"  {'sku':<10s} {'qty':>8s} {'A) erp_trans pv/u':>18s} "
      f"{'B) erp_costs.cost':>18s} {'C) NabavneCijene avg':>22s}")
print("  " + "─" * 80)
for sku in ("POL09734", "POL12848", "POL12929", "POL09753"):
    # A) realized COGS from erp_transactions
    a = db.execute(text("""
        SELECT SUM(et.quantity)::float qty,
               SUM(et.purchase_value)::float pv
        FROM erp_transactions et
        JOIN dim_products p ON p.id = et.product_id
        WHERE p.sku = :sku
          AND et.transaction_date >= now() - INTERVAL '13 weeks'
    """), {"sku": sku}).mappings().first()
    qty = a["qty"] or 0
    a_pvu = (a["pv"] / qty) if qty > 0 else None

    # B) current erp_costs.cost_price (latest snapshot)
    b = db.execute(text("""
        SELECT ec.cost_price::float
        FROM erp_costs ec JOIN dim_products p ON p.id = ec.product_id
        WHERE p.sku = :sku ORDER BY ec.valid_from DESC LIMIT 1
    """), {"sku": sku}).scalar()

    # C) NabavneCijene table — we don't load that into Postgres, but Finance Bridge
    #    reads it directly from xlsx via cost_history. We can estimate by averaging
    #    purchase_value over recent receipts in incoming_supply (if any).
    c = db.execute(text("""
        SELECT AVG(ec.cost_price)::float
        FROM erp_costs ec JOIN dim_products p ON p.id = ec.product_id
        WHERE p.sku = :sku
          AND ec.valid_from >= now() - INTERVAL '90 days'
    """), {"sku": sku}).scalar()

    a_str = f"{a_pvu:.4f}" if a_pvu is not None else "  —  "
    b_str = f"{b:.4f}" if b is not None else "  —  "
    c_str = f"{c:.4f}" if c is not None else "  —  "
    print(f"  {sku:<10s} {qty:>8.0f} {a_str:>18s} {b_str:>18s} {c_str:>22s}")

# 3. Aggregate "true COGS" for one full month — what would the P&L line show?
print()
print("=== May 2026 — what does the P&L COGS line look like? ===")
r = db.execute(text("""
    SELECT SUM(et.quantity)::float qty_sold,
           SUM(et.purchase_value)::float cogs_eur,
           SUM(et.total_value)::float revenue_eur,
           SUM(et.tax_base)::float tax_base_eur,
           SUM(et.ruc_eur)::float margin_eur
    FROM erp_transactions et
    WHERE et.transaction_date >= '2026-05-01'
      AND et.transaction_date < '2026-06-01'
""")).mappings().first()
qty = r["qty_sold"] or 0
cogs = r["cogs_eur"] or 0
rev = r["revenue_eur"] or 0
tb = r["tax_base_eur"] or 0
mar = r["margin_eur"] or 0
print(f"  units sold:           {qty:>14,.0f}")
print(f"  revenue (total_value): EUR {rev:>14,.0f}")
print(f"  net revenue (tax_base):EUR {tb:>14,.0f}")
print(f"  COGS (purchase_value): EUR {cogs:>14,.0f}")
print(f"  margin (ruc_eur):      EUR {mar:>14,.0f}")
print(f"  derived margin (net rev - COGS): EUR {tb - cogs:>10,.0f}")
print(f"  COGS / unit (avg):    EUR {cogs/qty if qty else 0:.4f}")
print(f"  margin %:             {(mar/tb*100 if tb else 0):.1f}%")

db.close()