#!/usr/bin/env python
"""
Fix Database Migration Script for Aldudu Academy

This script fixes the deprecated/fragmented migration chain by:
1. Checking the current alembic version
2. Fixing the alembic_version table if needed
3. Running the migration upgrade

Usage:
    python scripts/fix_database_migration.py

For production:
    Run with appropriate DATABASE_URL environment variable
"""

import os
import sys

# Add the project root to the path
project_root = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, project_root)

from dotenv import load_dotenv
load_dotenv(override=True)

def get_database_connection():
    """Get database connection based on DATABASE_URL"""
    from sqlalchemy import create_engine, text
    from sqlalchemy.exc import SQLAlchemyError
    
    database_url = os.environ.get('DATABASE_URL')
    if not database_url:
        print("ERROR: DATABASE_URL not set in environment")
        return None, None
    
    try:
        engine = create_engine(database_url)
        conn = engine.connect()
        return engine, conn
    except SQLAlchemyError as e:
        print(f"ERROR: Failed to connect to database: {e}")
        return None, None


def check_alembic_version(conn):
    """Check current alembic version"""
    from sqlalchemy import text
    from sqlalchemy.exc import SQLAlchemyError
    
    try:
        result = conn.execute(text("SELECT version_num FROM alembic_version"))
        versions = [row[0] for row in result.fetchall()]
        return versions
    except SQLAlchemyError as e:
        print(f"ERROR: Failed to check alembic version: {e}")
        return None


def fix_alembic_version(conn, target_version='e1f2g3h4i5j7'):
    """Fix alembic version to point to the correct head"""
    from sqlalchemy import text
    from sqlalchemy.exc import SQLAlchemyError
    
    try:
        # Delete all existing versions
        conn.execute(text("DELETE FROM alembic_version"))
        
        # Insert the target version
        conn.execute(text("INSERT INTO alembic_version (version_num) VALUES (:version)"),
                    {"version": target_version})
        conn.commit()
        
        print(f"✓ Fixed alembic_version to: {target_version}")
        return True
    except SQLAlchemyError as e:
        print(f"ERROR: Failed to fix alembic version: {e}")
        conn.rollback()
        return False


def run_migration_upgrade():
    """Run flask db upgrade using the virtualenv Python"""
    import subprocess
    
    # Path to virtualenv Python
    venv_python = '/home/smpb5875/virtualenv/public_html/aldudu-academy.smpia22.com/3.11/bin/python'
    
    if not os.path.exists(venv_python):
        print("WARNING: Virtualenv Python not found, trying system python...")
        venv_python = 'python'
    
    try:
        result = subprocess.run(
            [venv_python, '-m', 'flask', 'db', 'upgrade'],
            cwd=project_root,
            capture_output=True,
            text=True,
            timeout=60
        )
        
        if result.returncode == 0:
            print("✓ Migration upgrade completed successfully")
            if result.stdout:
                print(result.stdout)
            return True
        else:
            print(f"ERROR: Migration upgrade failed")
            if result.stderr:
                print(f"STDERR: {result.stderr}")
            if result.stdout:
                print(f"STDOUT: {result.stdout}")
            return False
    except subprocess.TimeoutExpired:
        print("ERROR: Migration upgrade timed out")
        return False
    except Exception as e:
        print(f"ERROR: Failed to run migration upgrade: {e}")
        return False


def check_current_head():
    """Check current migration head"""
    import subprocess
    
    venv_python = '/home/smpb5875/virtualenv/public_html/aldudu-academy.smpia22.com/3.11/bin/python'
    
    if not os.path.exists(venv_python):
        venv_python = 'python'
    
    try:
        result = subprocess.run(
            [venv_python, '-m', 'flask', 'db', 'current'],
            cwd=project_root,
            capture_output=True,
            text=True,
            timeout=30
        )
        
        if result.returncode == 0:
            print(f"Current migration: {result.stdout.strip()}")
            return result.stdout.strip()
        else:
            print(f"Could not determine current migration")
            return None
    except Exception as e:
        print(f"ERROR: Failed to check current head: {e}")
        return None


def main():
    print("=" * 60)
    print("Aldudu Academy - Database Migration Fix Script")
    print("=" * 60)
    print()
    
    # Step 1: Check current version
    print("Step 1: Checking current alembic version...")
    engine, conn = get_database_connection()
    
    if not conn:
        print("\n✗ Failed to connect to database")
        print("Please ensure DATABASE_URL is set correctly in your .env file")
        return 1
    
    current_versions = check_alembic_version(conn)
    
    if current_versions is None:
        print("\n✗ Could not check alembic_version table")
        print("The table might not exist. Running fresh migration...")
    elif len(current_versions) == 0:
        print("\n⚠ alembic_version table is empty")
    elif len(current_versions) > 1:
        print(f"\n✗ Multiple heads detected: {current_versions}")
        print("This is the problem we need to fix!")
    else:
        print(f"\n✓ Current version: {current_versions[0]}")
    
    # Step 2: Fix alembic version if needed
    if current_versions is None or len(current_versions) != 1 or current_versions[0] not in ['e1f2g3h4i5j7', 'z999_fix_migration_chain']:
        print("\nStep 2: Fixing alembic version...")
        if fix_alembic_version(conn, 'e1f2g3h4i5j7'):
            print("✓ Alembic version fixed")
        else:
            print("✗ Failed to fix alembic version")
            conn.close()
            return 1
    else:
        print("\n✓ Alembic version is already correct")
    
    conn.close()
    
    # Step 3: Run migration upgrade
    print("\nStep 3: Running migration upgrade...")
    if run_migration_upgrade():
        print("\nStep 4: Verifying fix...")
        check_current_head()
        print("\n" + "=" * 60)
        print("✓ Database migration fix completed successfully!")
        print("=" * 60)
        return 0
    else:
        print("\n✗ Migration upgrade failed")
        print("\nManual fix required. See FIX_DATABASE_MIGRATION.md for details.")
        return 1


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