#!/usr/bin/env python3
"""
Fix multiple database schema issues:
1. Add missing columns to notification_preferences table
2. Create missing users table
3. Fix any other missing tables/columns
"""

import sqlite3
import os
from datetime import datetime

def fix_database_schema():
    """Fix all database schema issues"""
    
    # Database path - you may need to adjust this
    db_path = "test_7.db"  # or the path to your production database
    
    if not os.path.exists(db_path):
        print(f"❌ Database file not found: {db_path}")
        print("Please update the db_path variable to point to your database file")
        return
    
    print(f"🔧 Fixing database schema in: {db_path}")
    print("=" * 60)
    
    try:
        # Connect to the database
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print("✅ Connected to database successfully")
        
        # Check what tables exist
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        existing_tables = [row[0] for row in cursor.fetchall()]
        print(f"📋 Existing tables: {existing_tables}")
        
        # 1. Fix notification_preferences table
        if 'notification_preferences' in existing_tables:
            print("\n🔧 Fixing notification_preferences table...")
            
            # Check current structure
            cursor.execute("PRAGMA table_info(notification_preferences)")
            columns = cursor.fetchall()
            column_names = [col[1] for col in columns]
            
            print(f"   Current columns: {column_names}")
            
            # Add missing columns
            if 'created_at' not in column_names:
                cursor.execute("ALTER TABLE notification_preferences ADD COLUMN created_at DATETIME")
                print("   ✅ Added created_at column")
            
            if 'updated_at' not in column_names:
                cursor.execute("ALTER TABLE notification_preferences ADD COLUMN updated_at DATETIME")
                print("   ✅ Added updated_at column")
            
            # Update existing records
            cursor.execute("""
                UPDATE notification_preferences 
                SET created_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP 
                WHERE created_at IS NULL OR updated_at IS NULL
            """)
            updated_count = cursor.rowcount
            print(f"   ✅ Updated {updated_count} records with timestamps")
        else:
            print("\n❌ notification_preferences table not found")
        
        # 2. Check if users table exists
        if 'users' not in existing_tables:
            print("\n🔧 Creating missing users table...")
            
            cursor.execute("""
                CREATE TABLE users (
                    id VARCHAR(150) PRIMARY KEY,
                    email VARCHAR(120) UNIQUE NOT NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    first_name VARCHAR(100),
                    last_name VARCHAR(100),
                    phone VARCHAR(20),
                    user_type VARCHAR(20) NOT NULL,
                    is_active BOOLEAN DEFAULT 1,
                    last_login DATETIME,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    profile_picture TEXT,
                    gender VARCHAR(20),
                    date_of_birth DATE,
                    nationality VARCHAR(50),
                    two_factor_enabled BOOLEAN DEFAULT 0,
                    two_factor_method VARCHAR(20),
                    two_factor_secret VARCHAR(255),
                    two_factor_phone VARCHAR(20),
                    last_two_factor_attempt DATETIME,
                    two_factor_failed_attempts INTEGER DEFAULT 0,
                    two_factor_backup_codes TEXT,
                    two_factor_verified_at DATETIME,
                    two_factor_recovery_email VARCHAR(120),
                    otp VARCHAR(10),
                    otp_expiry DATETIME,
                    otp_attempts INTEGER DEFAULT 0,
                    failed_login_attempts INTEGER DEFAULT 0,
                    account_locked BOOLEAN DEFAULT 0,
                    account_locked_until DATETIME,
                    last_password_change DATETIME,
                    must_change_password BOOLEAN DEFAULT 0,
                    reset_token VARCHAR(255),
                    reset_token_expiry DATETIME,
                    last_activity DATETIME,
                    current_session_id VARCHAR(255)
                )
            """)
            print("   ✅ Created users table")
            
            # Create indexes
            cursor.execute("CREATE INDEX idx_users_email ON users(email)")
            cursor.execute("CREATE INDEX idx_users_user_type ON users(user_type)")
            cursor.execute("CREATE INDEX idx_users_is_active ON users(is_active)")
            print("   ✅ Created indexes for users table")
        else:
            print("\n✅ users table already exists")
        
        # 3. Check for other common missing tables
        required_tables = [
            'supervisors', 'tutors', 'students', 'specialities', 
            'courses', 'timetable', 'teaching_session', 'academic_sessions'
        ]
        
        for table in required_tables:
            if table not in existing_tables:
                print(f"\n⚠️  Table '{table}' not found - this may cause issues")
        
        # 4. Check for polymorphic identity issues
        if 'users' in existing_tables:
            print("\n🔍 Checking for polymorphic identity issues...")
            
            # Check if there are any users with incorrect polymorphic identity
            try:
                cursor.execute("SELECT user_type, COUNT(*) FROM users GROUP BY user_type")
                user_types = cursor.fetchall()
                print(f"   User types in database: {user_types}")
            except Exception as e:
                print(f"   ⚠️  Could not check user types: {e}")
        
        # Commit all changes
        conn.commit()
        print("\n🎉 Database schema fixes completed successfully!")
        
        # Final verification
        print("\n📋 Final table structure:")
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        final_tables = [row[0] for row in cursor.fetchall()]
        for table in final_tables:
            print(f"   ✅ {table}")
        
    except sqlite3.Error as e:
        print(f"❌ SQLite error: {e}")
        if conn:
            conn.rollback()
    except Exception as e:
        print(f"❌ Error: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()
            print("🔒 Database connection closed")

if __name__ == "__main__":
    print("🚀 Starting database schema fixes...")
    fix_database_schema()