import os
import django
from django.db import connection
from django.conf import settings

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

def fix_django_model_settings():
    """
    Fix Django model settings to use user_id instead of customuser_id
    """
    try:
        from django.contrib.auth import get_user_model
        from django.contrib.auth.models import Group
        from django.db.models import Q
        User = get_user_model()
        
        # Force Django to use 'user_id' for foreign keys to the User model
        User._meta.pk.db_column = 'user_id'
        for field in User._meta.get_fields():
            if hasattr(field, 'column') and field.column == 'customuser_id':
                field.column = 'user_id'
        
        print(f"\nCurrent User model configuration:")
        print(f"Model: {User._meta.model_name}")
        print(f"DB Table: {User._meta.db_table}")
        print(f"App Label: {User._meta.app_label}")

        # Force Django to use 'user_id' as the column name
        with connection.cursor() as cursor:
            # Get detailed column information from users table
            cursor.execute("""
                SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users'
                AND COLUMN_NAME = 'id'
            """)
            col_info = cursor.fetchone()
            users_id_type = col_info[0]
            char_set = col_info[1] or 'utf8mb4'
            collation = col_info[2] or 'utf8mb4_unicode_ci'
            print(f"\nUsers table id column details:")
            print(f"Type: {users_id_type}")
            print(f"Character Set: {char_set}")
            print(f"Collation: {collation}")
            
            # Check if users_groups exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND table_name = 'users_groups'
            """)
            table_exists = cursor.fetchone()[0] > 0
            
            if table_exists:
                # Backup existing data
                print("\nBacking up existing data...")
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS users_groups_backup AS
                    SELECT * FROM users_groups
                """)
            else:
                print("\nNo existing users_groups table found, will create new one...")
            
            if table_exists:
                # Drop foreign key constraints first
                print("Checking for existing foreign keys...")
                cursor.execute("""
                    SELECT CONSTRAINT_NAME
                    FROM information_schema.KEY_COLUMN_USAGE
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'users_groups'
                    AND REFERENCED_TABLE_NAME IS NOT NULL
                """)
                constraints = cursor.fetchall()
                for constraint in constraints:
                    print(f"Dropping foreign key constraint: {constraint[0]}")
                    cursor.execute(f"""
                        ALTER TABLE users_groups
                        DROP FOREIGN KEY {constraint[0]}
                    """)
            
            # Drop the existing table if it exists
            print("Dropping existing table if it exists...")
            cursor.execute("""
                DROP TABLE IF EXISTS users_groups
            """)
            
            # Get users table character set and collation
            cursor.execute("""
                SELECT TABLE_COLLATION
                FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users'
            """)
            table_collation = cursor.fetchone()[0]
            print(f"Users table collation: {table_collation}")

            # Create the table with the correct structure
            print("Creating new table with correct structure...")
            cursor.execute("""
                CREATE TABLE users_groups (
                    id int(11) NOT NULL AUTO_INCREMENT,
                    user_id """ + users_id_type + """ CHARACTER SET """ + char_set + """ COLLATE """ + collation + """ NOT NULL,
                    group_id int(11) NOT NULL,
                    PRIMARY KEY (id),
                    UNIQUE KEY users_groups_user_group_unique (user_id, group_id),
                    KEY users_groups_group_id_idx (group_id),
                    CONSTRAINT users_groups_user_id_fk FOREIGN KEY (user_id) REFERENCES users (id),
                    CONSTRAINT users_groups_group_id_fk FOREIGN KEY (group_id) REFERENCES auth_group (id)
                ) ENGINE=InnoDB DEFAULT CHARSET=""" + char_set + """ COLLATE=""" + collation + """
            """)
            
            if table_exists:
                # Restore the data
                print("Restoring data...")
                cursor.execute("""
                    INSERT INTO users_groups (user_id, group_id)
                    SELECT user_id, group_id FROM users_groups_backup
                """)
                
                # Drop the backup table
                print("Cleaning up backup...")
                cursor.execute("""
                    DROP TABLE IF EXISTS users_groups_backup
                """)
            
            # Verify the structure
            print("\nVerifying final structure...")
            cursor.execute("""
                DESCRIBE users_groups
            """)
            columns = cursor.fetchall()
            print("\nTable structure:")
            for col in columns:
                print(f"Column: {col[0]}, Type: {col[1]}, Null: {col[2]}, Key: {col[3]}")
            
            # Check indexes
            cursor.execute("""
                SHOW INDEX FROM users_groups
            """)
            print("\nIndexes:")
            for idx in cursor.fetchall():
                print(f"Index: {idx[2]}, Column: {idx[4]}")

    except Exception as e:
        print(f"Error fixing model settings: {e}")
        raise

def fix_django_query():
    """
    Fix any remaining customuser_id references in queries and update Django's internal cache
    """
    from django.core.cache import cache
    from django.apps import apps
    
    # Clear Django's model cache to force reload of field names
    apps.clear_cache()
    cache.clear()
    
    with connection.cursor() as cursor:
        try:
            print("\nFixing query-related tables...")
            
            # Check current foreign key settings
            cursor.execute("""
                SELECT 
                    CONSTRAINT_NAME,
                    COLUMN_NAME,
                    REFERENCED_TABLE_NAME,
                    REFERENCED_COLUMN_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users_groups'
                AND REFERENCED_TABLE_NAME IS NOT NULL
            """)
            constraints = cursor.fetchall()
            print("\nCurrent foreign key constraints:")
            for constraint in constraints:
                print(f"Constraint: {constraint[0]}, Column: {constraint[1]}, References: {constraint[2]}({constraint[3]})")
            
            # Verify users table structure
            cursor.execute("""
                SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users'
            """)
            print("\nUsers table structure:")
            for col in cursor.fetchall():
                print(f"Column: {col[0]}, Type: {col[1]}, Nullable: {col[2]}")
            
            # Try to find any query using customuser_id
            cursor.execute("""
                SELECT DISTINCT TABLE_NAME, COLUMN_NAME
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND COLUMN_NAME LIKE '%customuser%'
            """)
            print("\nColumns containing 'customuser':")
            for table, column in cursor.fetchall():
                print(f"Table: {table}, Column: {column}")
            
            # Add user_id column if missing
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users_groups'
                AND COLUMN_NAME = 'user_id'
            """)
            if cursor.fetchone()[0] == 0:
                print("\nAdding user_id column...")
                cursor.execute("""
                    ALTER TABLE users_groups
                    ADD COLUMN IF NOT EXISTS user_id char(32) NOT NULL,
                    ADD UNIQUE INDEX users_groups_user_id_group_id_unique (user_id, group_id)
                """)
                print("Added user_id column")
            
            # Update foreign key if needed
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users_groups'
                AND COLUMN_NAME = 'user_id'
                AND REFERENCED_TABLE_NAME = 'users'
            """)
            if cursor.fetchone()[0] == 0:
                try:
                    print("\nUpdating foreign key...")
                    cursor.execute("""
                        ALTER TABLE users_groups
                        ADD CONSTRAINT users_groups_user_id_fk
                        FOREIGN KEY (user_id) REFERENCES users(id)
                    """)
                    print("Updated foreign key")
                except Exception as e:
                    print(f"Warning: Could not update foreign key: {e}")

        except Exception as e:
            print(f"Error during fix: {e}")
            raise

if __name__ == '__main__':
    print("Starting Django model settings fix...")
    fix_django_model_settings()
    fix_django_query()
    print("\nRestart your Django application after this fix!")
    print("\nIMPORTANT: You may need to clear Django's cache and restart the application")