"""Migration script for deals.db schema changes (Phase 3B1 fixes).

Wipes the deals table and recreates with new schema:
- address: nullable (vacant lots have only parcel_id)
- listing_price: nullable (foreclosure pre-auction bids hidden)
- NEW columns: parcel_id, final_judgment_amount

Phase 3 is still in dev, no production data lost.
"""
from __future__ import annotations
import io, sys
from pathlib import Path

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8", errors="replace")
ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(ROOT))


def main() -> int:
    from deals_db import _get_conn, init_db, _DB_PATH
    conn = _get_conn()

    # Show current state
    pre_count = conn.execute("SELECT COUNT(*) AS n FROM deals").fetchone()[0]
    pre_runs = conn.execute("SELECT COUNT(*) AS n FROM scraper_runs").fetchone()[0]
    pre_fcr = conn.execute("SELECT COUNT(*) AS n FROM firecrawl_usage").fetchone()[0]
    print(f"Pre-migration state:")
    print(f"  deals: {pre_count}")
    print(f"  scraper_runs: {pre_runs}")
    print(f"  firecrawl_usage: {pre_fcr}")

    # Wipe + recreate deals table only (keep history of runs + firecrawl usage)
    conn.execute("DROP TABLE IF EXISTS deals")
    print()
    print("Dropped deals table.")

    init_db()
    print("Recreated schema with new columns (address nullable, listing_price nullable, +parcel_id, +final_judgment_amount).")

    # Verify
    cols = [r["name"] for r in conn.execute("PRAGMA table_info(deals)").fetchall()]
    expected_new = ["parcel_id", "final_judgment_amount"]
    for c in expected_new:
        if c in cols:
            print(f"  ✅ column '{c}' present")
        else:
            print(f"  ❌ column '{c}' MISSING")
            return 1

    print()
    print(f"deals.db migrated OK at {_DB_PATH}")
    return 0


if __name__ == "__main__":
    sys.exit(main())