#!/usr/bin/env python
"""
Script to fix the disable_today column type from BIGINT to BOOLEAN
"""

import psycopg2
from urllib.parse import quote_plus
import sys
import os

# Add the backend directory to the path
sys.path.append(os.path.join(os.path.dirname(__file__), '..', '..', 'backend'))

# Import encryption utilities
from app.utils.encryption import decrypt_password

# Load environment variables
from dotenv import load_dotenv
load_dotenv(os.path.join(os.path.dirname(__file__), '..', '..', 'backend', '.env'))

def get_decrypted_env_var(var_name, default=None):
    """Get and decrypt environment variable"""
    encrypted_value = os.getenv(var_name)
    if encrypted_value:
        try:
            return decrypt_password(encrypted_value)
        except:
            return default
    return default

def fix_disable_today_column():
    """Fix the disable_today column type"""
    try:
        # Database configuration - matching admin.py exactly
        database_password = get_decrypted_env_var('DATABASE_PASSWORD')
        if not database_password:
            print("Error: Could not decrypt database password")
            return
            
        database_url = f"postgresql://{os.getenv('DATABASE_USER', 'postgres')}:{quote_plus(database_password)}@{os.getenv('DATABASE_HOST', 'localhost')}:{os.getenv('DATABASE_PORT', '5432')}/{os.getenv('DATABASE_NAME', 'bookbeach')}"
        
        # Connect to database
        conn = psycopg2.connect(database_url)
        cursor = conn.cursor()
        
        print("Connected to database successfully")
        
        # Execute migration steps
        migration_steps = [
            "ALTER TABLE beach_places ADD COLUMN disable_today_new BOOLEAN DEFAULT false;",
            """UPDATE beach_places SET disable_today_new = CASE 
                WHEN disable_today = 0 THEN false 
                ELSE true 
               END;""",
            "ALTER TABLE beach_places DROP COLUMN disable_today;",
            "ALTER TABLE beach_places RENAME COLUMN disable_today_new TO disable_today;",
            "ALTER TABLE beach_places ALTER COLUMN disable_today SET NOT NULL;"
        ]
        
        for i, step in enumerate(migration_steps, 1):
            print(f"Executing step {i}: {step[:50]}...")
            cursor.execute(step)
            print(f"Step {i} completed successfully")
        
        # Commit changes
        conn.commit()
        print("Migration completed successfully!")
        
        # Verify the change
        cursor.execute("SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'beach_places' AND column_name = 'disable_today';")
        result = cursor.fetchone()
        if result:
            print(f"Column 'disable_today' is now type: {result[1]}")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"Error: {str(e)}")
        if 'conn' in locals():
            conn.rollback()
            conn.close()

if __name__ == "__main__":
    fix_disable_today_column()