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 fix_django_groups_table():
    """Fix Django groups table structure for CustomUser"""
    try:
        with connection.cursor() as cursor:
            logger.info("Checking Django groups table structure...")
            
            # Check if auth_user_groups table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = 'auth_user_groups'
            """)
            
            if cursor.fetchone()[0] > 0:
                logger.info("Found auth_user_groups table")
                
                # Check if users_groups table exists
                cursor.execute("""
                    SELECT COUNT(*) 
                    FROM information_schema.tables 
                    WHERE table_schema = DATABASE() 
                    AND table_name = 'users_groups'
                """)
                
                if cursor.fetchone()[0] == 0:
                    logger.info("Creating users_groups table...")
                    cursor.execute("""
                        CREATE TABLE users_groups (
                            id INT AUTO_INCREMENT PRIMARY KEY,
                            customuser_id INT NOT NULL,
                            group_id INT NOT NULL,
                            UNIQUE KEY unique_user_group (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
                        )
                    """)
                    logger.info("✅ Created users_groups table")
                else:
                    # Check if customuser_id column exists
                    cursor.execute("""
                        SELECT COUNT(*) 
                        FROM information_schema.columns 
                        WHERE table_schema = DATABASE() 
                        AND table_name = 'users_groups' 
                        AND column_name = 'customuser_id'
                    """)
                    
                    if cursor.fetchone()[0] == 0:
                        logger.info("Adding customuser_id column to users_groups...")
                        cursor.execute("""
                            ALTER TABLE users_groups 
                            ADD COLUMN customuser_id INT NOT NULL
                        """)
                        
                        # Add foreign key constraint
                        cursor.execute("""
                            ALTER TABLE users_groups 
                            ADD CONSTRAINT users_groups_customuser_id_fk 
                            FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE
                        """)
                        logger.info("✅ Added customuser_id column")
                    else:
                        logger.info("customuser_id column already exists")
            
            # Check if users_user_permissions table exists and fix it
            cursor.execute("""
                SELECT COUNT(*) 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE() 
                AND table_name = 'users_user_permissions'
            """)
            
            if cursor.fetchone()[0] == 0:
                logger.info("Creating users_user_permissions table...")
                cursor.execute("""
                    CREATE TABLE users_user_permissions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        customuser_id INT NOT NULL,
                        permission_id INT NOT NULL,
                        UNIQUE KEY unique_user_permission (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
                    )
                """)
                logger.info("✅ Created users_user_permissions table")
            
            logger.info("✅ Django groups table structure fixed")
            
    except Exception as e:
        logger.error(f"❌ Error fixing Django groups table: {e}")
        return False
    
    return True

def run_django_migrations():
    """Run Django migrations to ensure all tables are properly created"""
    try:
        logger.info("Running Django migrations...")
        
        # Make migrations
        execute_from_command_line(['manage.py', 'makemigrations'])
        
        # Apply migrations
        execute_from_command_line(['manage.py', 'migrate'])
        
        logger.info("✅ Django migrations completed")
        return True
        
    except Exception as e:
        logger.error(f"❌ Error running migrations: {e}")
        return False

def test_client_creation_again():
    """Test client creation after fixing Django tables"""
    try:
        from users.models import CustomUser, Branch
        from django.contrib.auth.models import Group
        
        logger.info("Testing client creation after Django fix...")
        
        # 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_django_fix@example.com"
        test_phone = "+1234567890123"
        
        # Delete existing test user if exists
        CustomUser.objects.filter(email=test_email).delete()
        CustomUser.objects.filter(phone_number=test_phone).delete()
        
        # Create test client
        user = CustomUser.objects.create_user(
            username=test_email,
            email=test_email,
            phone_number=test_phone,
            first_name="Test",
            last_name="Django Fix",
            role="client",
            branch=branch
        )
        
        # Test adding to groups (this was failing before)
        client_group, created = Group.objects.get_or_create(name='Clients')
        user.groups.add(client_group)
        user.save()
        
        logger.info(f"✅ Successfully created and assigned groups to test client: {user.get_full_name()}")
        
        # Clean up
        user.delete()
        
        return True
        
    except Exception as e:
        logger.error(f"❌ Client creation test failed: {e}")
        return False

def main():
    """Main function to fix Django groups table issue"""
    logger.info("="*80)
    logger.info("DJANGO GROUPS TABLE FIX SCRIPT")
    logger.info("="*80)
    
    success_count = 0
    total_steps = 3
    
    # Step 1: Fix Django groups table structure
    logger.info("\nStep 1: Fixing Django groups table structure...")
    if fix_django_groups_table():
        success_count += 1
    
    # Step 2: Run Django migrations
    logger.info("\nStep 2: Running Django migrations...")
    if run_django_migrations():
        success_count += 1
    
    # Step 3: Test client creation
    logger.info("\nStep 3: Testing client creation...")
    if test_client_creation_again():
        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 table issues fixed successfully!")
        logger.info("\nYou can now try creating clients again.")
    else:
        logger.info(f"⚠️  {total_steps - success_count} steps failed - manual intervention may be needed")
    
    logger.info("\n" + "="*80)
    logger.info("SCRIPT COMPLETED")
    logger.info("="*80)

if __name__ == "__main__":
    main()