"""Step 0 — data discovery for the channel-split Polleo bridge.

Goal: map every input the wholesale + B2C + executive bridges will need
to its actual source column + row counts + sample data. We do NOT build
the bridge until this passes inspection.
"""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text
from pathlib import Path

db = SessionLocal()

def section(title):
    print()
    print("═" * 70)
    print(title)
    print("═" * 70)


# ─────────────────────────────────────────────────────────────────────
section("1. Channel column in erp_transactions")
# ─────────────────────────────────────────────────────────────────────
print("erp_transactions.channel_map_id → lookup_channel_map.channel:")
for r in db.execute(text("""
    SELECT cm.id, cm.doc_type, cm.channel, cm.description,
           COUNT(et.id) AS n_transactions
    FROM lookup_channel_map cm
    LEFT JOIN erp_transactions et ON et.channel_map_id = cm.id
    GROUP BY cm.id, cm.doc_type, cm.channel, cm.description
    ORDER BY cm.id
""")).mappings():
    desc = (r["description"] or "")[:50]
    print(f"  id={r['id']}  {r['doc_type']:<4s}  channel={r['channel']:<10s}  "
          f"n={r['n_transactions']:>8,}  {desc}")

# ─────────────────────────────────────────────────────────────────────
section("2. Customer ID in wholesale transactions")
# ─────────────────────────────────────────────────────────────────────
# partner_id is likely the customer for wholesale
print("erp_transactions.partner_id distribution (wholesale only):")
r = db.execute(text("""
    SELECT
        COUNT(*)            AS n_rows,
        COUNT(partner_id)   AS with_partner,
        COUNT(DISTINCT partner_id) AS n_distinct_partners
    FROM erp_transactions et
    JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
    WHERE cm.channel = 'wholesale'
""")).mappings().first()
print(f"  rows: {r['n_rows']:,}   with partner_id: {r['with_partner']:,}   "
      f"distinct partners: {r['n_distinct_partners']}")
print()
print("dim_partners populated?")
r = db.execute(text("""
    SELECT COUNT(*) AS n
    FROM information_schema.tables
    WHERE table_name = 'dim_partners' AND table_schema = 'public'
""")).mappings().first()
if r["n"] > 0:
    cols = db.execute(text("""
        SELECT column_name, data_type
        FROM information_schema.columns
        WHERE table_name = 'dim_partners' ORDER BY ordinal_position
    """)).mappings().all()
    print("  dim_partners columns:", ", ".join(c["column_name"] for c in cols))
    nrows = db.execute(text("SELECT COUNT(*) FROM dim_partners")).scalar()
    print(f"  dim_partners row count: {nrows}")
    samples = db.execute(text("SELECT * FROM dim_partners LIMIT 3")).mappings().all()
    for s in samples:
        print(f"    {dict(s)}")
else:
    print("  (dim_partners NOT found)")

# ─────────────────────────────────────────────────────────────────────
section("3. Loyalty discount field in ERP")
# ─────────────────────────────────────────────────────────────────────
print("approved_discount column distribution:")
r = db.execute(text("""
    SELECT
        COUNT(*) n_total,
        COUNT(approved_discount) n_with_discount,
        COUNT(CASE WHEN approved_discount > 0 THEN 1 END) n_gt0,
        SUM(approved_discount)::float total_discount,
        MIN(approved_discount)::float min_d,
        MAX(approved_discount)::float max_d,
        AVG(approved_discount)::float avg_d
    FROM erp_transactions
""")).mappings().first()
print(f"  total rows:     {r['n_total']:,}")
print(f"  with discount:  {r['n_with_discount']:,}")
print(f"  discount > 0:   {r['n_gt0']:,}")
print(f"  SUM discount:   EUR {r['total_discount']:,.2f}")
print(f"  range:          {r['min_d']} .. {r['max_d']}  (avg {r['avg_d']})")
print()
print("  → Is 'approved_discount' the loyalty discount? "
      "Or is loyalty captured another way?")
print("  → Sample 5 rows with approved_discount > 0:")
for r in db.execute(text("""
    SELECT et.id, p.sku, et.quantity, et.total_value, et.approved_discount,
           cm.channel
    FROM erp_transactions et
    JOIN dim_products p ON p.id = et.product_id
    JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
    WHERE et.approved_discount > 0
    LIMIT 5
""")).mappings():
    print(f"    {dict(r)}")

# ─────────────────────────────────────────────────────────────────────
section("4. VPC per customer — is there a (sku, partner) table?")
# ─────────────────────────────────────────────────────────────────────
# Check if there's a per-customer VPC source other than sku_planning.vpc
print("Tables in public schema that might hold per-customer pricing:")
for r in db.execute(text("""
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND (table_name ILIKE '%vpc%' OR table_name ILIKE '%customer%'
        OR table_name ILIKE '%partner%' OR table_name ILIKE '%agreement%'
        OR table_name ILIKE '%pricing%')
    ORDER BY table_name
""")).mappings():
    print(f"  {r['table_name']}")

# Realized VPC by partner per SKU from transactions:
print()
print("Realized VPC sample (POL09734, top 3 wholesale customers by qty):")
for r in db.execute(text("""
    SELECT et.partner_id,
           dp_part.name AS partner_name,
           SUM(et.quantity)::float    AS qty,
           SUM(et.total_value)::float AS rev,
           CASE WHEN SUM(et.quantity)>0
                THEN SUM(et.total_value)/SUM(et.quantity)
                ELSE NULL END::float  AS realized_vpc
    FROM erp_transactions et
    JOIN dim_products p ON p.id = et.product_id
    JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
    LEFT JOIN dim_partners dp_part ON dp_part.id = et.partner_id
    WHERE p.sku = 'POL09734' AND cm.channel = 'wholesale'
      AND et.transaction_date >= now() - INTERVAL '26 weeks'
    GROUP BY et.partner_id, dp_part.name
    ORDER BY qty DESC LIMIT 5
""")).mappings():
    pn = (r["partner_name"] or "?")[:30]
    print(f"  partner_id={r['partner_id']}  {pn:<30s}  qty={r['qty']:.0f}  "
          f"realized_vpc={r['realized_vpc']:.2f}")

# ─────────────────────────────────────────────────────────────────────
section("5. Promo flag coverage Apr-May 2026")
# ─────────────────────────────────────────────────────────────────────
r = db.execute(text("""
    SELECT
        SUM(CASE WHEN is_erp_promo THEN 1 ELSE 0 END)::int AS n_promo,
        SUM(CASE WHEN NOT is_erp_promo THEN 1 ELSE 0 END)::int AS n_non_promo,
        COUNT(DISTINCT product_id)::int AS n_skus_with_flag,
        MIN(year*100+week) AS min_yw,
        MAX(year*100+week) AS max_yw
    FROM erp_promo_weeks
    WHERE (year, week) BETWEEN (2026, 14) AND (2026, 22)
""")).mappings().first()
print(f"  promo rows:     {r['n_promo']}")
print(f"  non-promo rows: {r['n_non_promo']}")
print(f"  distinct SKUs:  {r['n_skus_with_flag']}")
print(f"  yw range:       {r['min_yw']} .. {r['max_yw']}")

# ─────────────────────────────────────────────────────────────────────
section("6. List prices source")
# ─────────────────────────────────────────────────────────────────────
print("erp_prices — sample for 3 SKUs:")
for r in db.execute(text("""
    SELECT p.sku, ep.avg_sell_price, ep.normal_retail_ppp,
           ep.normal_webshop_ppp, ep.weeks_active, ep.valid_from
    FROM erp_prices ep JOIN dim_products p ON p.id = ep.product_id
    WHERE p.sku IN ('POL09734','POL12848','POL12929')
""")).mappings():
    print(f"  {r['sku']}  avg_sell={r['avg_sell_price']}  retail_ppp={r['normal_retail_ppp']}  "
          f"web_ppp={r['normal_webshop_ppp']}  weeks_active={r['weeks_active']}  valid_from={r['valid_from']}")

# ─────────────────────────────────────────────────────────────────────
section("7. Cost history — NabavneCijene.xlsx")
# ─────────────────────────────────────────────────────────────────────
path = Path("data/NabavneCijene.xlsx")
print(f"  file exists: {path.exists()}  size: {path.stat().st_size if path.exists() else '—'} bytes")
if path.exists():
    import pandas as pd
    df = pd.read_excel(path, nrows=5)
    print(f"  columns: {list(df.columns)}")
    print(f"  sample rows:")
    print(df.head(3).to_string())

# ─────────────────────────────────────────────────────────────────────
section("8. v_sales_weekly_full coverage")
# ─────────────────────────────────────────────────────────────────────
r = db.execute(text("""
    SELECT MIN(year*100+week) min_yw, MAX(year*100+week) max_yw,
           COUNT(DISTINCT (year, week)) n_weeks,
           COUNT(DISTINCT product_id) n_skus
    FROM v_sales_weekly_full
""")).mappings().first()
print(f"  yw range: {r['min_yw']} .. {r['max_yw']}  "
      f"({r['n_weeks']} distinct weeks, {r['n_skus']} SKUs)")

# ─────────────────────────────────────────────────────────────────────
section("9. backtest_results / forecasts coverage")
# ─────────────────────────────────────────────────────────────────────
r = db.execute(text("""
    SELECT MIN(year*100+week) min_yw, MAX(year*100+week) max_yw,
           COUNT(DISTINCT (year, week)) n_weeks,
           COUNT(DISTINCT product_id) n_skus
    FROM backtest_results
""")).mappings().first()
print(f"  backtest_results: yw range {r['min_yw']} .. {r['max_yw']}  "
      f"({r['n_weeks']} weeks, {r['n_skus']} SKUs)")
r = db.execute(text("""
    SELECT MIN(year*100+week) min_yw, MAX(year*100+week) max_yw,
           COUNT(DISTINCT (year, week)) n_weeks,
           COUNT(DISTINCT product_id) n_skus,
           MAX(run_id) max_run_id
    FROM forecasts
""")).mappings().first()
print(f"  forecasts:        yw range {r['min_yw']} .. {r['max_yw']}  "
      f"({r['n_weeks']} weeks, {r['n_skus']} SKUs, latest run_id={r['max_run_id']})")

db.close()
print()
print("DONE.")