"""How much sales history do we have? span + volume + density."""
from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()

print("=== v_sales_weekly_full (aggregated, the demand-side view) ===")
r = db.execute(text("""
    SELECT
        MIN(year * 100 + week)::int     AS min_yw,
        MAX(year * 100 + week)::int     AS max_yw,
        COUNT(*)::int                    AS rows_total,
        COUNT(DISTINCT (year * 100 + week))::int AS distinct_weeks,
        COUNT(DISTINCT product_id)::int  AS distinct_skus,
        SUM(qty_total)::float            AS units_total,
        SUM(qty_retail)::float           AS units_retail,
        SUM(qty_webshop)::float          AS units_webshop,
        SUM(qty_wholesale)::float        AS units_wholesale
    FROM v_sales_weekly_full
""")).mappings().first()
print(f"  span:    {r['min_yw']} → {r['max_yw']}")
print(f"  weeks:   {r['distinct_weeks']}")
print(f"  rows:    {r['rows_total']:,}")
print(f"  SKUs:    {r['distinct_skus']:,}")
print(f"  units (total/retail/webshop/wholesale):")
print(f"    {r['units_total']:>12,.0f} / {r['units_retail']:>12,.0f} / "
      f"{r['units_webshop']:>12,.0f} / {r['units_wholesale']:>12,.0f}")

print("\n=== erp_transactions (document-level — the source) ===")
r = db.execute(text("""
    SELECT
        MIN(transaction_date) AS first_date,
        MAX(transaction_date) AS last_date,
        COUNT(*)::int          AS n_docs,
        COUNT(DISTINCT product_id)::int AS skus,
        SUM(quantity)::float            AS units,
        SUM(total_value)::float         AS revenue_eur,
        SUM(ruc_eur)::float             AS ruc_eur
    FROM erp_transactions
""")).mappings().first()
print(f"  span:       {r['first_date']} → {r['last_date']}")
print(f"  documents:  {r['n_docs']:,}")
print(f"  SKUs:       {r['skus']:,}")
print(f"  units:      {r['units']:,.0f}")
print(f"  revenue:    €{r['revenue_eur']:,.0f}")
print(f"  ruc:        €{r['ruc_eur']:,.0f}")

print("\n=== Weekly density (rows + units per year) ===")
for r in db.execute(text("""
    SELECT year::int                AS yr,
           COUNT(DISTINCT week)::int AS weeks,
           COUNT(*)::int             AS rows,
           SUM(qty_total)::float     AS units
    FROM v_sales_weekly_full
    GROUP BY year ORDER BY year
""")).mappings():
    print(f"  {r['yr']}: {r['weeks']:>2}w · {r['rows']:>7,} rows · "
          f"{r['units']:>11,.0f} units")

print("\n=== Last 6 weeks of sales (sanity) ===")
for r in db.execute(text("""
    SELECT year, week,
           COUNT(*)::int AS rows,
           COUNT(DISTINCT product_id)::int AS skus,
           SUM(qty_total)::float AS units
    FROM v_sales_weekly_full
    WHERE year * 100 + week >= (
        SELECT MAX(year * 100 + week) - 5 FROM v_sales_weekly_full
    )
    GROUP BY year, week ORDER BY year, week
""")).mappings():
    print(f"  {r['year']}W{r['week']:02d}  rows={r['rows']:>5,}  "
          f"skus={r['skus']:>4,}  units={r['units']:>9,.0f}")
db.close()