"""Apply db/migrations/bridge_loyalty_and_promo_policies.sql to the live DB."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from pathlib import Path
from backend.models.database import SessionLocal
from sqlalchemy import text

sql_path = Path("db/migrations/bridge_loyalty_and_promo_policies.sql")
sql = sql_path.read_text(encoding="utf-8")

db = SessionLocal()
try:
    # Execute as a single multi-statement script via the raw psycopg2 cursor —
    # avoids fragile semicolon-splitting that breaks on comments.
    raw_conn = db.connection().connection
    with raw_conn.cursor() as cur:
        cur.execute(sql)
    db.commit()
    print("✓ migration applied")
except Exception as e:
    db.rollback()
    print(f"✗ migration FAILED: {e}")
    raise
finally:
    db.close()

# Verify
db = SessionLocal()
print()
print("=== Verification ===")
r = db.execute(text("""
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = 'erp_transactions' AND column_name = 'has_loyalty'
""")).mappings().first()
print(f"  erp_transactions.has_loyalty: {dict(r) if r else 'MISSING'}")
r = db.execute(text("""
    SELECT COUNT(*) AS n FROM promo_policies
""")).mappings().first()
print(f"  promo_policies row count: {r['n']}")
r = db.execute(text("""
    SELECT COUNT(*) AS n FROM promo_policy_items
""")).mappings().first()
print(f"  promo_policy_items row count: {r['n']}")
db.close()