import os
import sys
import django
import logging
from django.db import connection
from django.core.management import execute_from_command_line

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

# 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_users_table_id_type():
    """Get the exact data type of the users.id column"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
                FROM information_schema.COLUMNS 
                WHERE table_schema = DATABASE() 
                AND table_name = 'users' 
                AND column_name = 'id'
            """)
            result = cursor.fetchone()
            if result:
                logger.info(f"Users.id column info: {result}")
                return result[1]  # COLUMN_TYPE
            return None
    except Exception as e:
        logger.error(f"Error getting users.id type: {e}")
        return None

def fix_django_tables_final():
    """Final comprehensive fix for Django custom user tables"""
    try:
        with connection.cursor() as cursor:
            logger.info("Starting final Django tables fix...")
            
            # Get the exact column type for users.id
            id_column_type = get_users_table_id_type()
            if not id_column_type:
                logger.error("Could not determine users.id column type")
                return False
            
            logger.info(f"Using column type: {id_column_type}")
            
            # Drop all existing problematic tables
            tables_to_drop = [
                'users_groups',
                'users_user_permissions', 
                'users_customuser_groups',
                'users_customuser_user_permissions'
            ]
            
            for table in tables_to_drop:
                cursor.execute(f"DROP TABLE IF EXISTS {table}")
                logger.info(f"Dropped table: {table}")
            
            # Create users_groups table (Django expects this exact name)
            logger.info("Creating users_groups table...")
            cursor.execute(f"""
                CREATE TABLE users_groups (
                    id {id_column_type} AUTO_INCREMENT PRIMARY KEY,
                    customuser_id {id_column_type} NOT NULL,
                    group_id INT NOT NULL,
                    UNIQUE KEY users_groups_customuser_id_group_id_uniq (customuser_id, group_id),
                    KEY users_groups_customuser_id (customuser_id),
                    KEY users_groups_group_id (group_id),
                    CONSTRAINT users_groups_customuser_id_fk 
                        FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE,
                    CONSTRAINT users_groups_group_id_fk 
                        FOREIGN KEY (group_id) REFERENCES auth_group (id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # Create users_user_permissions table (Django expects this exact name)
            logger.info("Creating users_user_permissions table...")
            cursor.execute(f"""
                CREATE TABLE users_user_permissions (
                    id {id_column_type} AUTO_INCREMENT PRIMARY KEY,
                    customuser_id {id_column_type} NOT NULL,
                    permission_id INT NOT NULL,
                    UNIQUE KEY users_user_permissions_customuser_id_permission_id_uniq (customuser_id, permission_id),
                    KEY users_user_permissions_customuser_id (customuser_id),
                    KEY users_user_permissions_permission_id (permission_id),
                    CONSTRAINT users_user_permissions_customuser_id_fk 
                        FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE,
                    CONSTRAINT users_user_permissions_permission_id_fk 
                        FOREIGN KEY (permission_id) REFERENCES auth_permission (id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            logger.info("✅ Django tables created successfully")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error in final Django fix: {e}")
        return False

def update_django_migrations():
    """Update Django migrations to reflect the correct table structure"""
    try:
        logger.info("Updating Django migrations...")
        
        # Mark the migration as applied
        with connection.cursor() as cursor:
            # Check if django_migrations table exists
            cursor.execute("""
                SELECT COUNT(*) FROM information_schema.tables 
                WHERE table_schema = DATABASE() AND table_name = 'django_migrations'
            """)
            
            if cursor.fetchone()[0] > 0:
                # Insert fake migration record
                cursor.execute("""
                    INSERT IGNORE INTO django_migrations (app, name, applied) 
                    VALUES ('users', '0014_fix_custom_user_tables', NOW())
                """)
                logger.info("✅ Migration record updated")
            
        return True
        
    except Exception as e:
        logger.error(f"❌ Error updating migrations: {e}")
        return False

def test_client_creation_simple():
    """Simple test of client creation without groups"""
    try:
        from users.models import CustomUser, Branch
        from django.db import transaction
        
        logger.info("Testing simple client creation...")
        
        # Get or create a branch
        branch, created = Branch.objects.get_or_create(
            name="Main Branch",
            defaults={
                'code': 'MAIN',
                'is_main_branch': True,
                'is_active': True
            }
        )
        
        # Try to create a test client
        test_email = "test_simple@example.com"
        test_phone = "+1234567890125"
        
        # Delete existing test user if exists
        CustomUser.objects.filter(email=test_email).delete()
        CustomUser.objects.filter(phone_number=test_phone).delete()
        
        with transaction.atomic():
            # Create test client without groups first
            user = CustomUser.objects.create_user(
                username=test_email,
                email=test_email,
                phone_number=test_phone,
                first_name="Test",
                last_name="Simple",
                role="client",
                branch=branch
            )
            
            logger.info(f"✅ Successfully created simple test client: {user.get_full_name()}")
            logger.info(f"✅ User ID: {user.id}, Branch: {user.branch.name}")
            
            # Now test adding to groups
            from django.contrib.auth.models import Group
            client_group, created = Group.objects.get_or_create(name='Clients')
            
            try:
                user.groups.add(client_group)
                user.save()
                logger.info(f"✅ Successfully added user to group: {client_group.name}")
            except Exception as group_error:
                logger.error(f"❌ Group assignment failed: {group_error}")
                # Continue without groups for now
            
            # Clean up
            user.delete()
        
        return True
        
    except Exception as e:
        logger.error(f"❌ Simple client creation test failed: {e}")
        import traceback
        logger.error(f"Full traceback: {traceback.format_exc()}")
        return False

def verify_database_structure():
    """Verify the final database structure"""
    try:
        with connection.cursor() as cursor:
            logger.info("Verifying database structure...")
            
            # Check all user-related tables
            cursor.execute("""
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name LIKE '%user%'
                ORDER BY table_name
            """)
            
            tables = [row[0] for row in cursor.fetchall()]
            logger.info(f"User-related tables: {tables}")
            
            # Check if required tables exist
            required_tables = ['users', 'users_groups', 'users_user_permissions']
            missing_tables = [t for t in required_tables if t not in tables]
            
            if missing_tables:
                logger.error(f"❌ Missing required tables: {missing_tables}")
                return False
            
            # Check table structures
            for table in required_tables:
                cursor.execute(f"DESCRIBE {table}")
                columns = cursor.fetchall()
                logger.info(f"Table {table} columns: {[col[0] for col in columns]}")
            
            logger.info("✅ Database structure verified")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error verifying database structure: {e}")
        return False

def main():
    """Main function for final Django fix"""
    logger.info("="*80)
    logger.info("FINAL DJANGO CUSTOM USER FIX")
    logger.info("="*80)
    
    success_count = 0
    total_steps = 4
    
    # Step 1: Fix Django tables with correct column types
    logger.info("\nStep 1: Fixing Django tables with correct column types...")
    if fix_django_tables_final():
        success_count += 1
    
    # Step 2: Update Django migrations
    logger.info("\nStep 2: Updating Django migrations...")
    if update_django_migrations():
        success_count += 1
    
    # Step 3: Verify database structure
    logger.info("\nStep 3: Verifying database structure...")
    if verify_database_structure():
        success_count += 1
    
    # Step 4: Test client creation
    logger.info("\nStep 4: Testing client creation...")
    if test_client_creation_simple():
        success_count += 1
    
    # Final summary
    logger.info("\n" + "="*80)
    logger.info("FINAL SUMMARY")
    logger.info("="*80)
    logger.info(f"Completed {success_count}/{total_steps} steps successfully")
    
    if success_count == total_steps:
        logger.info("✅ All Django issues fixed successfully!")
        logger.info("\nYour client creation should now work properly.")
        logger.info("\nNext steps:")
        logger.info("1. Restart your Django application")
        logger.info("2. Try creating a client through the web interface")
        logger.info("3. Monitor for any remaining errors")
    else:
        logger.info(f"⚠️  {total_steps - success_count} steps failed")
        logger.info("\nIf issues persist, you may need to:")
        logger.info("1. Check your Django settings.py for AUTH_USER_MODEL")
        logger.info("2. Verify database user permissions")
        logger.info("3. Consider recreating the database from scratch")
    
    logger.info("\n" + "="*80)
    logger.info("SCRIPT COMPLETED")
    logger.info("="*80)

if __name__ == "__main__":
    main()