"""Inspect v_sales_weekly_full and erp_costs for the NPD POL12* SKUs."""
from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()

print("=== v_sales_weekly_full columns ===")
cols = db.execute(text("""
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name='v_sales_weekly_full' ORDER BY ordinal_position
""")).mappings().all()
for c in cols:
    print(f"  {c['column_name']:30s} {c['data_type']}")

print("\n=== sample row joined via dim_products ===")
r = db.execute(text("""
    SELECT p.sku, v.*
    FROM v_sales_weekly_full v
    JOIN dim_products p ON p.id = v.product_id
    WHERE p.sku LIKE 'POL12%' LIMIT 3
""")).mappings().all()
for x in r:
    print(dict(x))

print("\n=== erp_costs columns ===")
cols = db.execute(text("""
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name='erp_costs' ORDER BY ordinal_position
""")).mappings().all()
for c in cols:
    print(f"  {c['column_name']:30s} {c['data_type']}")

print("\n=== check which NPD SKUs exist in dim_products ===")
import pandas as pd
from pathlib import Path
npd_xl = Path(__file__).parent.parent / "data" / "NPD.xlsx"
npd = pd.read_excel(npd_xl)
skus = npd["ŠIFRA"].dropna().astype(str).str.strip().tolist()
print(f"NPD has {len(skus)} SKUs, first 5: {skus[:5]}")
found = db.execute(
    text("SELECT sku FROM dim_products WHERE sku = ANY(:s)"),
    {"s": skus}
).scalars().all()
print(f"In dim_products: {len(found)}/{len(skus)}")
print(f"Missing: {sorted(set(skus) - set(found))[:10]}")

print("\n=== sales rows for NPD SKUs ===")
n = db.execute(text("""
    SELECT COUNT(*) FROM v_sales_weekly_full v
    JOIN dim_products p ON p.id = v.product_id
    WHERE p.sku = ANY(:s)
"""), {"s": skus}).scalar()
print(f"v_sales_weekly_full rows for NPD SKUs: {n}")

db.close()