#!/usr/bin/env python3
"""
Production fix for the "Field 'permission_id' doesn't have a default value" error
on cPanel production server.

This script should be run on your production server to fix the permission system.
"""

import os
import sys
import django
import logging
from django.db import connection, transaction

# Setup Django for production
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')
logger = logging.getLogger(__name__)

def check_table_exists(table_name):
    """Check if a table exists in the database"""
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES LIKE %s", [table_name])
        return cursor.fetchone() is not None

def get_id_column_type():
    """Get the appropriate ID column type based on the users table"""
    with connection.cursor() as cursor:
        cursor.execute("DESCRIBE users")
        columns = cursor.fetchall()
        for col in columns:
            if col[0] == 'id':
                return col[1]
    return 'CHAR(36)'  # Default fallback for UUID

def create_users_user_permissions_table():
    """Create the users_user_permissions table with correct structure"""
    logger.info("Creating users_user_permissions table...")
    
    if check_table_exists('users_user_permissions'):
        logger.info("users_user_permissions table already exists, checking structure...")
        
        # Check if the table has the correct structure
        with connection.cursor() as cursor:
            cursor.execute("DESCRIBE users_user_permissions")
            columns = cursor.fetchall()
            
            # Check if customuser_id is the right size
            customuser_id_col = None
            for col in columns:
                if col[0] == 'customuser_id':
                    customuser_id_col = col
                    break
            
            if customuser_id_col and 'char(36)' in customuser_id_col[1].lower():
                logger.info("Table structure is correct")
                return True
            else:
                logger.info("Table structure needs to be fixed")
                return fix_existing_table()
    
    try:
        with connection.cursor() as cursor:
            # Get the correct ID column type from the users table
            id_column_type = get_id_column_type()
            logger.info(f"Using ID column type: {id_column_type}")
            
            # Create the users_user_permissions table
            create_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 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
            """
            
            cursor.execute(create_sql)
            logger.info("✅ Successfully created users_user_permissions table")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error creating users_user_permissions table: {e}")
        return False

def fix_existing_table():
    """Fix an existing table that has the wrong column size"""
    logger.info("Fixing existing users_user_permissions table...")
    
    try:
        with connection.cursor() as cursor:
            # Drop foreign key constraints first
            logger.info("Dropping foreign key constraints...")
            try:
                cursor.execute("ALTER TABLE users_user_permissions DROP FOREIGN KEY users_user_permissions_customuser_id_fk")
            except:
                pass  # Constraint might not exist
            
            try:
                cursor.execute("ALTER TABLE users_user_permissions DROP FOREIGN KEY users_user_permissions_permission_id_fk")
            except:
                pass  # Constraint might not exist
            
            # Modify the column to be 36 characters
            logger.info("Modifying customuser_id column to CHAR(36)...")
            cursor.execute("ALTER TABLE users_user_permissions MODIFY COLUMN customuser_id CHAR(36) NOT NULL")
            
            # Recreate the foreign key constraints
            logger.info("Recreating foreign key constraints...")
            cursor.execute("""
                ALTER TABLE users_user_permissions 
                ADD CONSTRAINT users_user_permissions_customuser_id_fk 
                FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE
            """)
            cursor.execute("""
                ALTER TABLE users_user_permissions 
                ADD CONSTRAINT users_user_permissions_permission_id_fk 
                FOREIGN KEY (permission_id) REFERENCES auth_permission (id) ON DELETE CASCADE
            """)
            
            logger.info("✅ Successfully fixed users_user_permissions table")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error fixing users_user_permissions table: {e}")
        return False

def create_users_groups_table():
    """Create the users_groups table if it doesn't exist"""
    logger.info("Checking users_groups table...")
    
    if check_table_exists('users_groups'):
        logger.info("users_groups table already exists, checking structure...")
        
        # Check if the table has the correct structure
        with connection.cursor() as cursor:
            cursor.execute("DESCRIBE users_groups")
            columns = cursor.fetchall()
            
            # Check if customuser_id is the right size
            customuser_id_col = None
            for col in columns:
                if col[0] == 'customuser_id':
                    customuser_id_col = col
                    break
            
            if customuser_id_col and 'char(36)' in customuser_id_col[1].lower():
                logger.info("users_groups table structure is correct")
                return True
            else:
                logger.info("users_groups table structure needs to be fixed")
                return fix_users_groups_table()
    
    try:
        with connection.cursor() as cursor:
            id_column_type = get_id_column_type()
            
            create_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 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
            """
            
            cursor.execute(create_sql)
            logger.info("✅ Successfully created users_groups table")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error creating users_groups table: {e}")
        return False

def fix_users_groups_table():
    """Fix an existing users_groups table"""
    logger.info("Fixing existing users_groups table...")
    
    try:
        with connection.cursor() as cursor:
            # Drop foreign key constraints first
            logger.info("Dropping foreign key constraints from users_groups...")
            try:
                cursor.execute("ALTER TABLE users_groups DROP FOREIGN KEY users_groups_customuser_id_fk")
            except:
                pass  # Constraint might not exist
            
            try:
                cursor.execute("ALTER TABLE users_groups DROP FOREIGN KEY users_groups_group_id_fk")
            except:
                pass  # Constraint might not exist
            
            # Modify the column to be 36 characters
            logger.info("Modifying customuser_id column in users_groups to CHAR(36)...")
            cursor.execute("ALTER TABLE users_groups MODIFY COLUMN customuser_id CHAR(36) NOT NULL")
            
            # Recreate the foreign key constraints
            logger.info("Recreating foreign key constraints for users_groups...")
            cursor.execute("""
                ALTER TABLE users_groups 
                ADD CONSTRAINT users_groups_customuser_id_fk 
                FOREIGN KEY (customuser_id) REFERENCES users (id) ON DELETE CASCADE
            """)
            cursor.execute("""
                ALTER TABLE users_groups 
                ADD CONSTRAINT users_groups_group_id_fk 
                FOREIGN KEY (group_id) REFERENCES auth_group (id) ON DELETE CASCADE
            """)
            
            logger.info("✅ Successfully fixed users_groups table")
            return True
            
    except Exception as e:
        logger.error(f"❌ Error fixing users_groups table: {e}")
        return False

def test_permission_system():
    """Test that the permission system works correctly"""
    logger.info("Testing permission system...")
    
    try:
        from django.contrib.auth.models import Permission
        from users.models import CustomUser
        
        # Test basic queries
        permissions = Permission.objects.all()[:3]
        users = CustomUser.objects.all()[:3]
        
        if not users.exists() or not permissions.exists():
            logger.warning("No users or permissions found for testing")
            return True
        
        user = users.first()
        permission = permissions.first()
        
        # Test adding permission
        user.user_permissions.add(permission)
        logger.info("✅ Permission add test successful")
        
        # Test removing permission
        user.user_permissions.remove(permission)
        logger.info("✅ Permission remove test successful")
        
        return True
        
    except Exception as e:
        logger.error(f"❌ Permission system test failed: {e}")
        return False

def main():
    """Main function to fix the production permission issue"""
    logger.info("=" * 60)
    logger.info("FIXING PRODUCTION PERMISSION SYSTEM")
    logger.info("=" * 60)
    
    try:
        # Create or fix the required tables
        success1 = create_users_user_permissions_table()
        success2 = create_users_groups_table()
        
        if not success1 or not success2:
            logger.error("❌ Failed to create/fix required tables")
            return False
        
        # Test the permission system
        success3 = test_permission_system()
        
        if success3:
            logger.info("🎉 SUCCESS: Production permission system is now working correctly!")
            logger.info("The 'Field permission_id doesn't have a default value' error has been resolved.")
            return True
        else:
            logger.error("❌ Permission system test failed")
            return False
            
    except Exception as e:
        logger.error(f"❌ Unexpected error: {e}")
        import traceback
        traceback.print_exc()
        return False

if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)
