"""
One-time migration: fix tradie_leads table schema to match tradie_lead_scraper_v2.py
- Renames trade_type -> trade
- Adds missing columns: lead_hash, state, area_code, listing_url, etc.
"""
import sys
sys.path.insert(0, '/mnt/e/genesis-system/data/genesis-memory')
from elestio_config import PostgresConfig
import psycopg2

conn = psycopg2.connect(**PostgresConfig.get_connection_params())
conn.autocommit = False
cur = conn.cursor()

print('Migrating tradie_leads table...')

# Rename trade_type -> trade
try:
    cur.execute('ALTER TABLE tradie_leads RENAME COLUMN trade_type TO trade')
    print('  Renamed trade_type -> trade')
except Exception as e:
    print('  trade_type rename skipped: {}'.format(e))
    conn.rollback()
    conn.autocommit = False

# Add missing columns
missing_cols = [
    ('lead_hash', 'VARCHAR(64)'),
    ('state', 'VARCHAR(10)'),
    ('area_code', 'VARCHAR(5)'),
    ('listing_url', 'TEXT'),
    ('scrape_source', 'VARCHAR(100)'),
    ('outreach_sent_at', 'TIMESTAMP'),
    ('outreach_reply_at', 'TIMESTAMP'),
    ('outreach_channel', 'VARCHAR(50)'),
    ('notes', 'TEXT'),
]

for col, dtype in missing_cols:
    try:
        cur.execute('ALTER TABLE tradie_leads ADD COLUMN IF NOT EXISTS {} {}'.format(col, dtype))
        print('  Added column: {}'.format(col))
    except Exception as e:
        conn.rollback()
        print('  {} skipped: {}'.format(col, e))

# Add UNIQUE constraint on lead_hash
try:
    cur.execute('ALTER TABLE tradie_leads ADD CONSTRAINT tradie_leads_lead_hash_key UNIQUE (lead_hash)')
    print('  Added UNIQUE constraint on lead_hash')
except Exception as e:
    conn.rollback()
    print('  UNIQUE constraint skipped: {}'.format(e))

# Create missing indexes
for idx_sql in [
    'CREATE INDEX IF NOT EXISTS idx_tradie_leads_trade ON tradie_leads(trade)',
    'CREATE INDEX IF NOT EXISTS idx_tradie_leads_hash ON tradie_leads(lead_hash)',
    'CREATE INDEX IF NOT EXISTS idx_tradie_leads_score ON tradie_leads(priority_score DESC)',
]:
    try:
        cur.execute(idx_sql)
    except Exception as e:
        conn.rollback()
        print('  Index skipped: {}'.format(e))

conn.commit()
print('\nMigration COMPLETE')

# Verify final schema
cur.execute("""
    SELECT column_name, data_type FROM information_schema.columns
    WHERE table_name = 'tradie_leads' ORDER BY ordinal_position
""")
print('\nFINAL SCHEMA:')
for c in cur.fetchall():
    print('  {}: {}'.format(c[0], c[1]))

cur.execute("SELECT COUNT(*) FROM tradie_leads")
count = cur.fetchone()[0]
print('\nExisting rows in table: {}'.format(count))

conn.close()