import os
import sys
import django
from datetime import datetime

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection, transaction
from users.models import CustomUser, Branch
from utils.models import SystemSetting

def log_message(message, level='INFO'):
    """Log messages with timestamp"""
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    print(f"[{timestamp}] {level}: {message}")

def check_table_exists(table_name):
    """Check if a table exists in the database"""
    try:
        with connection.cursor() as cursor:
            cursor.execute(f"""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}'
            """)
            return cursor.fetchone()[0] > 0
    except Exception as e:
        log_message(f"Error checking table {table_name}: {e}", 'ERROR')
        return False

def check_column_exists(table_name, column_name):
    """Check if a column exists in a table"""
    try:
        with connection.cursor() as cursor:
            cursor.execute(f"""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table_name}' 
                AND COLUMN_NAME = '{column_name}'
            """)
            return cursor.fetchone()[0] > 0
    except Exception as e:
        log_message(f"Error checking column {table_name}.{column_name}: {e}", 'ERROR')
        return False

def fix_users_table_structure():
    """Ensure users table has all required columns and indexes"""
    try:
        log_message("Fixing users table structure...")
        
        with connection.cursor() as cursor:
            # Check and add missing columns
            required_columns = {
                'branch_id': "ADD COLUMN `branch_id` char(32) NULL",
                'phone_number': "ADD COLUMN `phone_number` varchar(20) NULL",
                'email': "MODIFY COLUMN `email` varchar(254) NULL",
                'id_number': "ADD COLUMN `id_number` varchar(50) NULL",
                'business_name': "ADD COLUMN `business_name` varchar(200) NULL",
                'business_type': "ADD COLUMN `business_type` varchar(50) NULL",
                'business_address': "ADD COLUMN `business_address` longtext NULL",
                'physical_address': "ADD COLUMN `physical_address` longtext NULL",
                'city': "ADD COLUMN `city` varchar(100) NULL",
                'postal_code': "ADD COLUMN `postal_code` varchar(20) NULL",
                'nationality': "ADD COLUMN `nationality` varchar(50) NULL DEFAULT 'Kenyan'",
                'marital_status': "ADD COLUMN `marital_status` varchar(20) NULL",
                'gender': "ADD COLUMN `gender` varchar(10) NULL",
                'date_of_birth': "ADD COLUMN `date_of_birth` date NULL",
                'status': "ADD COLUMN `status` varchar(20) NULL DEFAULT 'active'",
                'role': "ADD COLUMN `role` varchar(20) NULL DEFAULT 'borrower'"
            }
            
            for column, alter_sql in required_columns.items():
                if not check_column_exists('users', column):
                    try:
                        cursor.execute(f"ALTER TABLE `users` {alter_sql}")
                        log_message(f"✅ Added/Modified column: {column}")
                    except Exception as e:
                        if "Duplicate column name" not in str(e):
                            log_message(f"Warning adding column {column}: {e}", 'WARNING')
                else:
                    log_message(f"Column {column} already exists")
            
            # Add indexes for better performance
            indexes_to_add = [
                ("idx_users_phone", "CREATE INDEX `idx_users_phone` ON `users` (`phone_number`)"),
                ("idx_users_email", "CREATE INDEX `idx_users_email` ON `users` (`email`)"),
                ("idx_users_id_number", "CREATE INDEX `idx_users_id_number` ON `users` (`id_number`)"),
                ("idx_users_branch", "CREATE INDEX `idx_users_branch` ON `users` (`branch_id`)"),
                ("idx_users_role", "CREATE INDEX `idx_users_role` ON `users` (`role`)"),
                ("idx_users_status", "CREATE INDEX `idx_users_status` ON `users` (`status`)")
            ]
            
            for index_name, create_sql in indexes_to_add:
                try:
                    cursor.execute(create_sql)
                    log_message(f"✅ Created index: {index_name}")
                except Exception as e:
                    if "Duplicate key name" not in str(e):
                        log_message(f"Warning creating index {index_name}: {e}", 'WARNING')
            
            # Add foreign key constraint for branch_id if it doesn't exist
            if check_table_exists('users_branch'):
                try:
                    cursor.execute("""
                        ALTER TABLE `users` 
                        ADD CONSTRAINT `fk_users_branch` 
                        FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) 
                        ON DELETE SET NULL
                    """)
                    log_message("✅ Added foreign key constraint for branch_id")
                except Exception as e:
                    if "Duplicate foreign key constraint name" not in str(e):
                        log_message(f"Warning adding foreign key: {e}", 'WARNING')
        
        return True
        
    except Exception as e:
        log_message(f"Error fixing users table structure: {e}", 'ERROR')
        return False

def clean_duplicate_data():
    """Clean up duplicate and inconsistent data"""
    try:
        log_message("Cleaning duplicate data...")
        
        with connection.cursor() as cursor:
            # Find and fix duplicate phone numbers
            cursor.execute("""
                SELECT phone_number, COUNT(*) as count 
                FROM users 
                WHERE phone_number IS NOT NULL AND phone_number != '' 
                GROUP BY phone_number 
                HAVING count > 1
            """)
            
            duplicates = cursor.fetchall()
            if duplicates:
                log_message(f"Found {len(duplicates)} duplicate phone numbers")
                
                for phone, count in duplicates:
                    log_message(f"Fixing duplicate phone number: {phone} ({count} records)")
                    
                    # Get all users with this phone number
                    cursor.execute(
                        "SELECT id, username, email, first_name, last_name, date_joined FROM users WHERE phone_number = %s ORDER BY date_joined",
                        [phone]
                    )
                    users = cursor.fetchall()
                    
                    # Keep the first user, modify others
                    for i, user in enumerate(users[1:], 1):
                        user_id, username, email, first_name, last_name, date_joined = user
                        new_phone = f"{phone}_{i}"
                        new_username = f"{username}_{i}" if username else f"user_{user_id}_{i}"
                        
                        cursor.execute(
                            "UPDATE users SET phone_number = %s, username = %s WHERE id = %s",
                            [new_phone, new_username, user_id]
                        )
                        log_message(f"  Updated user {user_id}: phone {phone} -> {new_phone}")
            
            # Find and fix duplicate emails
            cursor.execute("""
                SELECT email, COUNT(*) as count 
                FROM users 
                WHERE email IS NOT NULL AND email != '' 
                GROUP BY email 
                HAVING count > 1
            """)
            
            email_duplicates = cursor.fetchall()
            if email_duplicates:
                log_message(f"Found {len(email_duplicates)} duplicate emails")
                
                for email, count in email_duplicates:
                    log_message(f"Fixing duplicate email: {email} ({count} records)")
                    
                    cursor.execute(
                        "SELECT id, username, phone_number, first_name, last_name, date_joined FROM users WHERE email = %s ORDER BY date_joined",
                        [email]
                    )
                    users = cursor.fetchall()
                    
                    # Keep the first user, modify others
                    for i, user in enumerate(users[1:], 1):
                        user_id, username, phone_number, first_name, last_name, date_joined = user
                        email_parts = email.split('@')
                        new_email = f"{email_parts[0]}_{i}@{email_parts[1]}"
                        
                        cursor.execute(
                            "UPDATE users SET email = %s WHERE id = %s",
                            [new_email, user_id]
                        )
                        log_message(f"  Updated user {user_id}: email {email} -> {new_email}")
            
            # Find and fix duplicate ID numbers
            cursor.execute("""
                SELECT id_number, COUNT(*) as count 
                FROM users 
                WHERE id_number IS NOT NULL AND id_number != '' 
                GROUP BY id_number 
                HAVING count > 1
            """)
            
            id_duplicates = cursor.fetchall()
            if id_duplicates:
                log_message(f"Found {len(id_duplicates)} duplicate ID numbers")
                
                for id_number, count in id_duplicates:
                    log_message(f"Fixing duplicate ID number: {id_number} ({count} records)")
                    
                    cursor.execute(
                        "SELECT id, username, phone_number, email, first_name, last_name, date_joined FROM users WHERE id_number = %s ORDER BY date_joined",
                        [id_number]
                    )
                    users = cursor.fetchall()
                    
                    # Keep the first user, modify others
                    for i, user in enumerate(users[1:], 1):
                        user_id = user[0]
                        new_id_number = f"{id_number}_{i}"
                        
                        cursor.execute(
                            "UPDATE users SET id_number = %s WHERE id = %s",
                            [new_id_number, user_id]
                        )
                        log_message(f"  Updated user {user_id}: ID {id_number} -> {new_id_number}")
        
        return True
        
    except Exception as e:
        log_message(f"Error cleaning duplicate data: {e}", 'ERROR')
        return False

def ensure_branch_system():
    """Ensure branch system is properly set up"""
    try:
        log_message("Setting up branch system...")
        
        # Create branch table if it doesn't exist
        if not check_table_exists('users_branch'):
            log_message("Creating branch table...")
            with connection.cursor() as cursor:
                cursor.execute("""
                    CREATE TABLE `users_branch` (
                        `id` char(32) NOT NULL,
                        `name` varchar(100) NOT NULL,
                        `code` varchar(20) NOT NULL UNIQUE,
                        `address` longtext,
                        `phone_number` varchar(20),
                        `email` varchar(254),
                        `is_main_branch` tinyint(1) NOT NULL DEFAULT 0,
                        `is_active` tinyint(1) NOT NULL DEFAULT 1,
                        `created_at` datetime(6) NOT NULL,
                        `updated_at` datetime(6) NOT NULL,
                        PRIMARY KEY (`id`),
                        KEY `idx_branch_active` (`is_active`),
                        KEY `idx_branch_main` (`is_main_branch`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
            log_message("✅ Branch table created")
        
        # Create accessible_branches table if it doesn't exist
        if not check_table_exists('users_customuser_accessible_branches'):
            log_message("Creating accessible_branches table...")
            with connection.cursor() as cursor:
                cursor.execute("""
                    CREATE TABLE `users_customuser_accessible_branches` (
                        `id` bigint(20) NOT NULL AUTO_INCREMENT,
                        `customuser_id` char(32) NOT NULL,
                        `branch_id` char(32) NOT NULL,
                        PRIMARY KEY (`id`),
                        UNIQUE KEY `users_customuser_accessible_customuser_id_branch_id_unique` (`customuser_id`,`branch_id`),
                        KEY `users_customuser_accessible_branch_id_fk` (`branch_id`),
                        CONSTRAINT `users_customuser_accessible_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
                        CONSTRAINT `users_customuser_accessible_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) ON DELETE CASCADE
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
            log_message("✅ Accessible branches table created")
        
        # Create default branch if none exists
        from users.models import Branch
        if not Branch.objects.filter(is_active=True).exists():
            log_message("Creating default branch...")
            branch = Branch.objects.create(
                name="Main Branch",
                code="MAIN",
                address="Head Office",
                phone_number="+254700000000",
                email="info@branchbusinessadvance.co.ke",
                is_main_branch=True,
                is_active=True
            )
            log_message(f"✅ Created default branch: {branch.name}")
            
            # Assign users without branch to default branch
            users_updated = CustomUser.objects.filter(branch__isnull=True).update(branch=branch)
            log_message(f"✅ Assigned {users_updated} users to default branch")
        
        return True
        
    except Exception as e:
        log_message(f"Error setting up branch system: {e}", 'ERROR')
        return False

def fix_user_validation():
    """Fix user validation issues"""
    try:
        log_message("Fixing user validation issues...")
        
        with connection.cursor() as cursor:
            # Fix NULL usernames
            cursor.execute("""
                UPDATE users 
                SET username = CONCAT('user_', id) 
                WHERE username IS NULL OR username = ''
            """)
            affected = cursor.rowcount
            if affected > 0:
                log_message(f"✅ Fixed {affected} NULL usernames")
            
            # Fix users without proper role
            cursor.execute("""
                UPDATE users 
                SET role = 'borrower' 
                WHERE role IS NULL OR role = ''
            """)
            affected = cursor.rowcount
            if affected > 0:
                log_message(f"✅ Fixed {affected} users without role")
            
            # Fix users without proper status
            cursor.execute("""
                UPDATE users 
                SET status = 'active' 
                WHERE status IS NULL OR status = ''
            """)
            affected = cursor.rowcount
            if affected > 0:
                log_message(f"✅ Fixed {affected} users without status")
            
            # Ensure all users have first_name
            cursor.execute("""
                UPDATE users 
                SET first_name = 'User' 
                WHERE (first_name IS NULL OR first_name = '') AND role = 'borrower'
            """)
            affected = cursor.rowcount
            if affected > 0:
                log_message(f"✅ Fixed {affected} users without first_name")
        
        return True
        
    except Exception as e:
        log_message(f"Error fixing user validation: {e}", 'ERROR')
        return False

def test_client_creation():
    """Test client creation functionality"""
    try:
        log_message("Testing client creation...")
        
        # Test data
        test_phone = "+254700999888"
        test_email = "testclient@example.com"
        test_id = "99999999"
        
        # Clean up any existing test data
        CustomUser.objects.filter(phone_number=test_phone).delete()
        CustomUser.objects.filter(email=test_email).delete()
        CustomUser.objects.filter(id_number=test_id).delete()
        
        # Get a branch
        branch = Branch.objects.filter(is_active=True).first()
        if not branch:
            log_message("❌ No active branch found for testing", 'ERROR')
            return False
        
        # Create test user
        user = CustomUser.objects.create_user(
            username=test_phone,
            email=test_email,
            phone_number=test_phone,
            first_name="Test",
            last_name="Client",
            date_of_birth="1990-01-01",
            gender="male",
            id_number=test_id,
            nationality="Kenyan",
            physical_address="Test Address",
            city="Nairobi",
            business_name="Test Business",
            business_type="retail",
            business_address="Test Business Address",
            role='borrower',
            status='active',
            branch=branch
        )
        
        log_message(f"✅ Successfully created test client: {user.get_full_name()}")
        
        # Test duplicate detection
        try:
            duplicate_user = CustomUser.objects.create_user(
                username=f"{test_phone}_dup",
                email=f"dup_{test_email}",
                phone_number=test_phone,  # Same phone number
                first_name="Duplicate",
                last_name="Test",
                role='borrower'
            )
            log_message("❌ Duplicate detection failed - created user with same phone", 'ERROR')
            duplicate_user.delete()
        except Exception as e:
            log_message("✅ Duplicate detection working correctly")
        
        # Clean up test data
        user.delete()
        log_message("✅ Test client cleaned up")
        
        return True
        
    except Exception as e:
        log_message(f"❌ Client creation test failed: {e}", 'ERROR')
        return False

def optimize_database():
    """Optimize database performance"""
    try:
        log_message("Optimizing database...")
        
        with connection.cursor() as cursor:
            # Analyze and optimize tables
            tables_to_optimize = ['users', 'users_branch', 'users_customuser_accessible_branches']
            
            for table in tables_to_optimize:
                if check_table_exists(table):
                    try:
                        cursor.execute(f"ANALYZE TABLE `{table}`")
                        cursor.execute(f"OPTIMIZE TABLE `{table}`")
                        log_message(f"✅ Optimized table: {table}")
                    except Exception as e:
                        log_message(f"Warning optimizing {table}: {e}", 'WARNING')
        
        return True
        
    except Exception as e:
        log_message(f"Error optimizing database: {e}", 'ERROR')
        return False

def main():
    """Main execution function"""
    log_message("=" * 80)
    log_message("COMPREHENSIVE CLIENT CREATION FIX SCRIPT")
    log_message("=" * 80)
    
    success_count = 0
    total_steps = 6
    
    # Step 1: Fix users table structure
    log_message("\n" + "=" * 50)
    log_message("STEP 1: FIXING USERS TABLE STRUCTURE")
    log_message("=" * 50)
    if fix_users_table_structure():
        success_count += 1
        log_message("✅ Users table structure fixed")
    else:
        log_message("❌ Failed to fix users table structure")
    
    # Step 2: Clean duplicate data
    log_message("\n" + "=" * 50)
    log_message("STEP 2: CLEANING DUPLICATE DATA")
    log_message("=" * 50)
    if clean_duplicate_data():
        success_count += 1
        log_message("✅ Duplicate data cleaned")
    else:
        log_message("❌ Failed to clean duplicate data")
    
    # Step 3: Ensure branch system
    log_message("\n" + "=" * 50)
    log_message("STEP 3: SETTING UP BRANCH SYSTEM")
    log_message("=" * 50)
    if ensure_branch_system():
        success_count += 1
        log_message("✅ Branch system set up")
    else:
        log_message("❌ Failed to set up branch system")
    
    # Step 4: Fix user validation
    log_message("\n" + "=" * 50)
    log_message("STEP 4: FIXING USER VALIDATION")
    log_message("=" * 50)
    if fix_user_validation():
        success_count += 1
        log_message("✅ User validation fixed")
    else:
        log_message("❌ Failed to fix user validation")
    
    # Step 5: Test client creation
    log_message("\n" + "=" * 50)
    log_message("STEP 5: TESTING CLIENT CREATION")
    log_message("=" * 50)
    if test_client_creation():
        success_count += 1
        log_message("✅ Client creation test passed")
    else:
        log_message("❌ Client creation test failed")
    
    # Step 6: Optimize database
    log_message("\n" + "=" * 50)
    log_message("STEP 6: OPTIMIZING DATABASE")
    log_message("=" * 50)
    if optimize_database():
        success_count += 1
        log_message("✅ Database optimized")
    else:
        log_message("❌ Failed to optimize database")
    
    # Final summary
    log_message("\n" + "=" * 80)
    log_message("FINAL SUMMARY")
    log_message("=" * 80)
    log_message(f"Completed {success_count}/{total_steps} steps successfully")
    
    if success_count == total_steps:
        log_message("🎉 ALL FIXES COMPLETED SUCCESSFULLY!")
        log_message("\nNext steps:")
        log_message("1. Restart your Django development server")
        log_message("2. Clear browser cache and cookies")
        log_message("3. Try creating a client again")
        log_message("4. The duplicate phone number error should be resolved")
    else:
        log_message(f"⚠️  {total_steps - success_count} steps failed - manual intervention may be needed")
    
    log_message("\n" + "=" * 80)
    log_message("SCRIPT COMPLETED")
    log_message("=" * 80)

if __name__ == '__main__':
    main()