"""Find MCI buyer rows for on-top demand in W21 / W22 / W23."""
from sqlalchemy import text
from backend.models.database import SessionLocal

db = SessionLocal()

print("=== Distinct buyers in on_top_inputs containing 'MCI' ===")
for r in db.execute(text("""
    SELECT DISTINCT buyer FROM on_top_inputs
    WHERE buyer ILIKE '%mci%' OR buyer ILIKE '%MCI%'
""")).mappings():
    print(f"  '{r['buyer']}'")

print("\n=== All distinct buyers (top 30 by row count) ===")
for r in db.execute(text("""
    SELECT buyer, COUNT(*) AS n, SUM(quantity)::float AS qty
    FROM on_top_inputs
    GROUP BY buyer ORDER BY n DESC LIMIT 30
""")).mappings():
    print(f"  {(r['buyer'] or '(null)'): <30}  rows={r['n']:>4}  qty={r['qty']:,.0f}")

print("\n=== MCI rows in W21/W22/W23 (year_week LIKE 2026__) ===")
for r in db.execute(text("""
    SELECT ot.id, p.sku, p.name, ot.year_week, ot.quantity, ot.channel,
           ot.buyer, ot.submitted_by_id, ot.submitted_at
    FROM on_top_inputs ot
    JOIN dim_products p ON p.id = ot.product_id
    WHERE (ot.buyer ILIKE '%mci%' OR ot.buyer ILIKE '%MCI%')
      AND ot.year_week IN (202621, 202622, 202623)
    ORDER BY ot.year_week, p.sku LIMIT 60
""")).mappings():
    print(f"  id={r['id']}  yw={r['year_week']}  sku={r['sku']:<10}  "
          f"qty={r['quantity']:>5.0f}  buyer={r['buyer']}  "
          f"name={(r['name'] or '')[:40]}")
db.close()