import os
import sys
import logging
from datetime import datetime
import pymysql

# Setup Django FIRST before any Django imports
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')

# Import Django and setup
import django
django.setup()

# NOW we can import Django components
from django.db import connection, transaction
from django.contrib.auth.models import Group
from django.contrib.contenttypes.models import ContentType
from users.models import CustomUser, Branch

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format='[%(asctime)s] %(levelname)s: %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S'
)
logger = logging.getLogger(__name__)

def get_db_connection():
    """Get direct MySQL connection"""
    from django.conf import settings
    db_config = settings.DATABASES['default']
    return pymysql.connect(
        host=db_config['HOST'],
        user=db_config['USER'],
        password=db_config['PASSWORD'],
        database=db_config['NAME'],
        charset='utf8mb4'
    )

def fix_django_auth_tables():
    """Fix Django auth tables with proper MySQL syntax"""
    logger.info("Step 1: Creating Django auth tables with proper MySQL syntax...")
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Get the users table ID column type
        cursor.execute("DESCRIBE users")
        columns = cursor.fetchall()
        id_column_type = None
        for col in columns:
            if col[0] == 'id':
                id_column_type = col[1]
                break
        
        logger.info(f"Users table ID column type: {id_column_type}")
        
        # Drop existing problematic tables
        tables_to_drop = [
            'users_groups',
            'users_user_permissions', 
            'users_customuser_groups',
            'users_customuser_user_permissions'
        ]
        
        for table in tables_to_drop:
            try:
                cursor.execute(f"DROP TABLE IF EXISTS {table}")
                logger.info(f"Dropped table: {table}")
            except Exception as e:
                logger.warning(f"Could not drop {table}: {e}")
        
        # Create users_groups table with proper MySQL syntax
        users_groups_sql = f"""
        CREATE TABLE users_groups (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customuser_id {id_column_type} NOT NULL,
            group_id INT NOT NULL,
            UNIQUE KEY unique_user_group (customuser_id, group_id),
            KEY idx_customuser_id (customuser_id),
            KEY idx_group_id (group_id),
            CONSTRAINT fk_users_groups_user 
                FOREIGN KEY (customuser_id) REFERENCES users(id) ON DELETE CASCADE,
            CONSTRAINT fk_users_groups_group 
                FOREIGN KEY (group_id) REFERENCES auth_group(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
        
        cursor.execute(users_groups_sql)
        logger.info("✅ Created users_groups table")
        
        # Create users_user_permissions table
        users_permissions_sql = f"""
        CREATE TABLE users_user_permissions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customuser_id {id_column_type} NOT NULL,
            permission_id INT NOT NULL,
            UNIQUE KEY unique_user_permission (customuser_id, permission_id),
            KEY idx_customuser_id (customuser_id),
            KEY idx_permission_id (permission_id),
            CONSTRAINT fk_users_permissions_user 
                FOREIGN KEY (customuser_id) REFERENCES users(id) ON DELETE CASCADE,
            CONSTRAINT fk_users_permissions_permission 
                FOREIGN KEY (permission_id) REFERENCES auth_permission(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
        
        cursor.execute(users_permissions_sql)
        logger.info("✅ Created users_user_permissions table")
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return True
        
    except Exception as e:
        logger.error(f"❌ Error creating Django auth tables: {e}")
        return False

def update_django_migrations():
    """Update Django migrations to reflect the changes"""
    logger.info("\nStep 2: Updating Django migrations...")
    
    try:
        with connection.cursor() as cursor:
            # Update migration record
            cursor.execute("""
                INSERT INTO django_migrations (app, name, applied) 
                VALUES ('users', '9999_fix_auth_tables', NOW())
                ON DUPLICATE KEY UPDATE applied = NOW()
            """)
            
        logger.info("✅ Migration record updated")
        return True
        
    except Exception as e:
        logger.error(f"❌ Error updating migrations: {e}")
        return False

def verify_database_structure():
    """Verify the database structure is correct"""
    logger.info("\nStep 3: Verifying database structure...")
    
    try:
        with connection.cursor() as cursor:
            # Check for required tables
            cursor.execute("SHOW TABLES LIKE 'users%'")
            tables = [row[0] for row in cursor.fetchall()]
            
            required_tables = ['users_groups', 'users_user_permissions']
            missing_tables = [table for table in required_tables if table not in tables]
            
            if missing_tables:
                logger.error(f"❌ Missing required tables: {missing_tables}")
                return False
            else:
                logger.info("✅ All required tables exist")
                
            # Verify table structures
            for table in required_tables:
                cursor.execute(f"DESCRIBE {table}")
                columns = cursor.fetchall()
                logger.info(f"✅ {table} structure: {len(columns)} columns")
                
        return True
        
    except Exception as e:
        logger.error(f"❌ Error verifying database: {e}")
        return False

def create_default_groups():
    """Create default groups if they don't exist"""
    logger.info("\nStep 4: Creating default groups...")
    
    try:
        default_groups = ['Clients', 'Staff', 'Managers']
        
        for group_name in default_groups:
            group, created = Group.objects.get_or_create(name=group_name)
            if created:
                logger.info(f"✅ Created group: {group_name}")
            else:
                logger.info(f"✅ Group exists: {group_name}")
                
        return True
        
    except Exception as e:
        logger.error(f"❌ Error creating groups: {e}")
        return False

def test_client_creation_safe():
    """Test client creation with proper error handling"""
    logger.info("\nStep 5: Testing client creation (safe mode)...")
    
    try:
        # Get or create main branch
        main_branch, created = Branch.objects.get_or_create(
            name='Main Branch',
            defaults={'code': 'MAIN', 'is_main_branch': True, 'is_active': True}
        )
        
        if created:
            logger.info("✅ Created main branch")
        
        # Test user creation with username parameter
        test_email = f"test_safe_{datetime.now().strftime('%Y%m%d_%H%M%S')}@example.com"
        test_phone = f"+1234567{datetime.now().strftime('%H%M%S')}"
        
        with transaction.atomic():
            user = CustomUser.objects.create_user(
                username=test_phone,  # Add username parameter
                email=test_email,
                first_name='Test',
                last_name='Safe',
                phone_number=test_phone,
                branch=main_branch,
                role='borrower',  # Use 'borrower' instead of 'client'
                status='active'
            )
            
            logger.info(f"✅ Created test user: {user.get_full_name()}")
            logger.info(f"✅ User ID: {user.id}, Branch: {user.branch.name}")
            
            # Test group assignment
            try:
                client_group = Group.objects.get(name='Clients')
                user.groups.add(client_group)
                logger.info("✅ Group assignment successful")
            except Exception as group_error:
                logger.warning(f"⚠️ Group assignment failed: {group_error}")
            
            # Clean up test user
            user.delete()
            logger.info("✅ Test user cleaned up")
            
        return True
        
    except Exception as e:
        logger.error(f"❌ Client creation test failed: {e}")
        return False

def main():
    """Main execution function"""
    logger.info("=" * 80)
    logger.info("ULTIMATE DJANGO FIX - MySQL Compatible")
    logger.info("=" * 80)
    
    steps = [
        ("Fix Django auth tables", fix_django_auth_tables),
        ("Update Django migrations", update_django_migrations),
        ("Verify database structure", verify_database_structure),
        ("Create default groups", create_default_groups),
        ("Test client creation", test_client_creation_safe)
    ]
    
    completed_steps = 0
    failed_steps = 0
    
    for step_name, step_function in steps:
        logger.info(f"\n{step_name}...")
        try:
            if step_function():
                completed_steps += 1
                logger.info(f"✅ {step_name} completed")
            else:
                failed_steps += 1
                logger.error(f"❌ {step_name} failed")
        except Exception as e:
            failed_steps += 1
            logger.error(f"❌ {step_name} failed with exception: {e}")
    
    # Final summary
    logger.info("\n" + "=" * 80)
    logger.info("FINAL SUMMARY")
    logger.info("=" * 80)
    logger.info(f"Completed {completed_steps}/{len(steps)} steps successfully")
    
    if failed_steps > 0:
        logger.info(f"⚠️  {failed_steps} steps failed")
    else:
        logger.info("🎉 All steps completed successfully!")
    
    logger.info("\nNext steps:")
    logger.info("1. Test client creation in your Django admin")
    logger.info("2. Verify that groups are working properly")
    logger.info("3. Check that the 404 notifications error is resolved")
    
    logger.info("\n" + "=" * 80)
    logger.info("SCRIPT COMPLETED")
    logger.info("=" * 80)

if __name__ == '__main__':
    main()