#!/usr/bin/env python3
"""
Script to apply the database migration for adding new columns to markets and adventures tables.
"""

import psycopg2
import os
from pathlib import Path

def get_db_params():
    """Get database connection parameters by trying common passwords."""
    # Try common PostgreSQL passwords
    passwords = ["password", "f@k0s!!", "postgres", "admin", ""]
    
    for pwd in passwords:
        try:
            # Test connection first
            import psycopg2
            test_params = {
                'host': 'localhost',
                'port': 5432,
                'database': 'bookbeach',
                'user': 'postgres',
                'password': pwd
            }
            
            conn = psycopg2.connect(**test_params)
            conn.close()
            print(f"✅ Connected with password: '{pwd}'")
            return test_params
            
        except Exception:
            continue
    
    raise Exception("Could not connect to database with any common password")

def run_migration():
    """Run the migration script to add new columns to markets and adventures tables."""
    
    # Get database connection parameters
    db_params = get_db_params()
    
    # Path to the migration script
    script_path = Path(__file__).parent / 'add_new_columns_to_markets_adventures.sql'
    
    try:
        # Connect to the database
        print("Connecting to the database...")
        conn = psycopg2.connect(**db_params)
        conn.autocommit = True
        cursor = conn.cursor()
        
        # Read and execute the migration script
        print(f"Reading migration script from: {script_path}")
        with open(script_path, 'r', encoding='utf-8') as file:
            migration_sql = file.read()
        
        print("Executing migration script...")
        cursor.execute(migration_sql)
        
        # Verify the columns were added successfully
        print("Verifying new columns in markets table...")
        cursor.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'markets' 
            AND column_name IN ('address', 'city', 'country_id', 'latitude', 'longitude', 'phone', 'email', 'website', 'updated_at')
            ORDER BY column_name;
        """)
        
        markets_columns = cursor.fetchall()
        print("Markets table new columns:")
        for col in markets_columns:
            print(f"  - {col[0]}: {col[1]} ({'NULL' if col[2] == 'YES' else 'NOT NULL'})")
        
        print("\nVerifying new columns in adventures table...")
        cursor.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns 
            WHERE table_name = 'adventures' 
            AND column_name IN ('address', 'city', 'country_id', 'latitude', 'longitude', 'phone', 'email', 'website', 'updated_at')
            ORDER BY column_name;
        """)
        
        adventures_columns = cursor.fetchall()
        print("Adventures table new columns:")
        for col in adventures_columns:
            print(f"  - {col[0]}: {col[1]} ({'NULL' if col[2] == 'YES' else 'NOT NULL'})")
        
        # Check if triggers were created
        print("\nVerifying triggers...")
        cursor.execute("""
            SELECT trigger_name, event_manipulation, event_object_table
            FROM information_schema.triggers 
            WHERE trigger_name LIKE '%updated_at%'
            AND event_object_table IN ('markets', 'adventures')
            ORDER BY event_object_table, trigger_name;
        """)
        
        triggers = cursor.fetchall()
        print("Created triggers:")
        for trigger in triggers:
            print(f"  - {trigger[0]} on {trigger[2]} for {trigger[1]}")
        
        print("\n✅ Migration completed successfully!")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"❌ Migration failed: {str(e)}")
        raise

if __name__ == "__main__":
    run_migration()