#!/usr/bin/env python3
"""
Fix missing columns in users table

This script adds missing columns: email_verified, school_id, preferred_language
"""

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_users_table():
    """Add missing columns to users table"""
    from app import create_app
    from app.extensions import db
    from sqlalchemy import inspect, text

    print("=" * 60)
    print("Fix users table - Add missing columns")
    print("=" * 60)

    app = create_app()

    with app.app_context():
        # Check if column exists
        inspector = inspect(db.engine)
        columns = [col['name'] for col in inspector.get_columns('users')]

        print(f"\n📊 Current columns in 'users' table:")
        for col in columns:
            print(f"  - {col}")

        # Expected columns from model
        expected_columns = ['id', 'name', 'email', 'password_hash', 'role', 
                          'is_active', 'email_verified', 'school_id', 'preferred_language']
        
        missing_columns = [col for col in expected_columns if col not in columns]

        if not missing_columns:
            print("\n✅ All expected columns exist!")
        else:
            print(f"\n⚠ Missing columns: {', '.join(missing_columns)}")
            print("\nAdding missing columns...")

        try:
            with db.engine.connect() as conn:
                # Add missing columns
                if 'email_verified' not in columns:
                    conn.execute(text(
                        "ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE"
                    ))
                    print("  ✅ Added 'email_verified'")

                if 'school_id' not in columns:
                    conn.execute(text(
                        "ALTER TABLE users ADD COLUMN school_id INT NULL"
                    ))
                    print("  ✅ Added 'school_id'")

                if 'preferred_language' not in columns:
                    conn.execute(text(
                        "ALTER TABLE users ADD COLUMN preferred_language VARCHAR(10) NULL DEFAULT 'id'"
                    ))
                    print("  ✅ Added 'preferred_language'")

                conn.commit()

            # Verify all columns were added
            columns = [col['name'] for col in inspector.get_columns('users')]
            missing_columns = [col for col in expected_columns if col not in columns]
            
            if not missing_columns:
                print("\n✅ All columns successfully added!")
            else:
                print(f"\n⚠ Still missing: {', '.join(missing_columns)}")

            # Set all existing users as verified and with default language
            with db.engine.connect() as conn:
                result = 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(f"✅ Updated {result.rowcount} existing users")

            print("\n" + "=" * 60)
            print("✅ Fix completed successfully!")
            print("=" * 60)
            print("\n💡 All existing users can now login.")
            return 0

        except Exception as e:
            print(f"\n❌ Error: {str(e)}")
            print("\nTroubleshooting:")
            print("  1. Check database connection")
            print("  2. Check if you have ALTER TABLE permissions")
            print("  3. Try running: flask db upgrade")
            return 1


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