"""Why does the bridge see 0 promo transactions?"""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from backend.models.database import SessionLocal
from sqlalchemy import text

db = SessionLocal()

# Check promo_policy_items state
print("=== promo_policy_items state ===")
r = db.execute(text("""
    SELECT COUNT(*) AS n_items,
           COUNT(product_id) AS n_with_product_id,
           MIN(valid_from) AS min_from,
           MAX(valid_to) AS max_to
    FROM promo_policy_items
""")).mappings().first()
print(f"  total items: {r['n_items']}")
print(f"  with product_id: {r['n_with_product_id']}")
print(f"  date range: {r['min_from']} .. {r['max_to']}")

# Sample
print()
print("=== Sample items ===")
for r in db.execute(text("""
    SELECT ppi.sku, ppi.product_id, ppi.promo_price, ppi.valid_from, ppi.valid_to,
           pp.policy_name
    FROM promo_policy_items ppi
    JOIN promo_policies pp ON pp.id = ppi.policy_id
    ORDER BY ppi.valid_from LIMIT 5
""")).mappings():
    print(f"  {r['sku']:<14s}  pid={r['product_id']}  promo_price={r['promo_price']}  "
          f"{r['valid_from']}..{r['valid_to']}  policy={r['policy_name']}")

# Now check actual matches for May 2026
print()
print("=== Transactions × promo policies — May 2026 ===")
r = db.execute(text("""
    SELECT COUNT(*) AS n_tx_in_may,
           COUNT(DISTINCT et.product_id) AS n_skus_in_may
    FROM erp_transactions et
    WHERE et.transaction_date BETWEEN '2026-05-04' AND '2026-05-31'
""")).mappings().first()
print(f"  May transactions: {r['n_tx_in_may']}, SKUs: {r['n_skus_in_may']}")

r = db.execute(text("""
    SELECT COUNT(*) AS n_matches
    FROM erp_transactions et
    JOIN promo_policy_items ppi ON ppi.product_id = et.product_id
                              AND et.transaction_date BETWEEN ppi.valid_from
                                                          AND ppi.valid_to
""")).mappings().first()
print(f"  Direct join matches: {r['n_matches']}")

# Maybe product_id is NULL on items
r = db.execute(text("""
    SELECT COUNT(*) FROM promo_policy_items WHERE product_id IS NULL
""")).scalar()
print(f"  promo_policy_items with NULL product_id: {r}")

# Sample raw match
r = db.execute(text("""
    SELECT ppi.sku AS ppi_sku, et.product_id, p.sku AS et_sku,
           ppi.product_id AS ppi_pid,
           et.transaction_date, ppi.valid_from, ppi.valid_to,
           ppi.promo_price, et.total_value, et.quantity
    FROM erp_transactions et
    JOIN dim_products p ON p.id = et.product_id
    JOIN promo_policy_items ppi ON ppi.sku = p.sku
    WHERE et.transaction_date BETWEEN ppi.valid_from AND ppi.valid_to
    LIMIT 5
""")).mappings().all()
print(f"  Match via sku join: {len(r)}")
for x in r:
    print(f"    {dict(x)}")

db.close()