#!/usr/bin/env python3
"""
Comprehensive database fix script

Adds all missing columns to match the models.
"""

import os
import sys
from pathlib import Path

# Add project root to path
ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

from dotenv import load_dotenv
load_dotenv()


def fix_database():
    """Add missing columns to all tables"""
    from app import create_app
    from app.extensions import db
    from sqlalchemy import inspect, text

    print("=" * 60)
    print("Comprehensive Database Fix")
    print("=" * 60)

    app = create_app()

    with app.app_context():
        inspector = inspect(db.engine)
        tables = inspector.get_table_names()
        
        print(f"\n📊 Found {len(tables)} tables")
        
        errors = []
        
        # Fix users table
        if 'users' in tables:
            print("\n🔧 Fixing 'users' table...")
            columns = [col['name'] for col in inspector.get_columns('users')]
            expected = ['id', 'name', 'email', 'password_hash', 'role', 
                       'is_active', 'email_verified', 'school_id', 'preferred_language']
            missing = [c for c in expected if c not in columns]
            
            if missing:
                print(f"   Missing: {', '.join(missing)}")
                try:
                    with db.engine.connect() as conn:
                        if 'email_verified' in missing:
                            conn.execute(text("ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE"))
                        if 'school_id' in missing:
                            conn.execute(text("ALTER TABLE users ADD COLUMN school_id INT NULL"))
                        if 'preferred_language' in missing:
                            conn.execute(text("ALTER TABLE users ADD COLUMN preferred_language VARCHAR(10) NULL DEFAULT 'id'"))
                        conn.commit()
                    print("   ✅ Fixed")
                except Exception as e:
                    errors.append(f"users: {e}")
                    print(f"   ❌ Error: {e}")
            else:
                print("   ✅ OK")
        
        # Fix schools table
        if 'schools' in tables:
            print("\n🔧 Fixing 'schools' table...")
            columns = [col['name'] for col in inspector.get_columns('schools')]
            expected = ['id', 'name', 'slug', 'address', 'phone', 'email', 
                       'admin_email', 'status', 'created_at', 'approved_at']
            missing = [c for c in expected if c not in columns]
            
            if missing:
                print(f"   Missing: {', '.join(missing)}")
                try:
                    with db.engine.connect() as conn:
                        if 'approved_at' in missing:
                            conn.execute(text("ALTER TABLE schools ADD COLUMN approved_at DATETIME NULL"))
                        conn.commit()
                    print("   ✅ Fixed")
                except Exception as e:
                    errors.append(f"schools: {e}")
                    print(f"   ❌ Error: {e}")
            else:
                print("   ✅ OK")
        
        # Update existing users
        try:
            with db.engine.connect() as conn:
                conn.execute(text("UPDATE users SET email_verified = TRUE, preferred_language = 'id' WHERE email_verified IS NULL OR preferred_language IS NULL"))
                conn.commit()
            print("\n✅ Updated existing users")
        except Exception as e:
            errors.append(f"update: {e}")
            print(f"❌ Update error: {e}")
        
        print("\n" + "=" * 60)
        if errors:
            print(f"⚠ Completed with {len(errors)} error(s):")
            for err in errors:
                print(f"  - {err}")
            return 1
        else:
            print("✅ All fixes completed successfully!")
            print("\n💡 Database is now ready. Users can login.")
            return 0


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