import os
import django
from django.db import connection

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

def quick_fix_users_groups():
    """
    Quick fix for the users_groups table customuser_id issue
    """
    with connection.cursor() as cursor:
        try:
            # Debug: Print current database name
            cursor.execute("SELECT DATABASE()")
            current_db = cursor.fetchone()[0]
            print(f"Current database: {current_db}")

            # Debug: Show all tables
            cursor.execute("""
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
            """)
            tables = [row[0] for row in cursor.fetchall()]
            print(f"Available tables: {', '.join(tables)}")

            # Check for customuser or customuser_id column
            cursor.execute("""
                SELECT column_name, data_type, is_nullable
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = 'users_groups'
                AND column_name IN ('customuser', 'customuser_id')
            """)
            custom_user_col = cursor.fetchone()

            if custom_user_col:
                col_name, data_type, is_nullable = custom_user_col
                print(f"Found {col_name} column with type {data_type}")
                
                # Drop existing indexes that might conflict
                try:
                    cursor.execute("""
                        DROP INDEX users_groups_customuser_id_group_id_927de924_uniq ON users_groups
                    """)
                except Exception:
                    pass  # Index might not exist

                # Rename and modify the column
                cursor.execute(f"""
                    ALTER TABLE users_groups
                    CHANGE COLUMN {col_name} user_id char(32) NOT NULL
                """)
                print(f"Successfully renamed {col_name} to user_id and set correct type")
            else:
                # Check if we need to create the table
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.tables
                    WHERE table_schema = DATABASE()
                    AND table_name = 'users_groups'
                """)
                table_exists = cursor.fetchone()[0] > 0

                if not table_exists:
                    print("Creating users_groups table...")
                    # Drop the table if it exists but is corrupted
                    cursor.execute("""
                        DROP TABLE IF EXISTS users_groups
                    """)
                    
                    cursor.execute("""
                        CREATE TABLE users_groups (
                            id int(11) NOT NULL AUTO_INCREMENT,
                            user_id char(32) NOT NULL,
                            group_id int(11) NOT NULL,
                            PRIMARY KEY (id),
                            UNIQUE KEY users_groups_user_id_group_id_unique (user_id, group_id),
                            KEY users_groups_group_id_fk (group_id),
                            CONSTRAINT users_groups_group_id_fk FOREIGN KEY (group_id) 
                            REFERENCES auth_group (id)
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                    """)
                    print("Created users_groups table with correct schema")

                    # Copy data from auth_user_groups if it exists
                    cursor.execute("""
                        SELECT COUNT(*)
                        FROM information_schema.tables
                        WHERE table_schema = DATABASE()
                        AND table_name = 'auth_user_groups'
                    """)
                    if cursor.fetchone()[0] > 0:
                        cursor.execute("""
                            INSERT IGNORE INTO users_groups (user_id, group_id)
                            SELECT user_id, group_id FROM auth_user_groups
                        """)
                        print("Copied data from auth_user_groups")

            # Verify the fix and show detailed table structure
            print("\nVerifying table structure...")
            
            cursor.execute("""
                DESCRIBE users_groups
            """)
            columns = cursor.fetchall()
            print("\nCurrent users_groups table structure:")
            for col in columns:
                print(f"Column: {col[0]}, Type: {col[1]}, Null: {col[2]}, Key: {col[3]}, Default: {col[4]}")

            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("\n✓ Verification: users_groups table has correct user_id column")
                # Check if there's any data
                cursor.execute("SELECT COUNT(*) FROM users_groups")
                count = cursor.fetchone()[0]
                print(f"Total records in users_groups: {count}")
            else:
                print("\n✗ Error: users_groups table structure is still incorrect")
                
            # Fix indexes - recreate them with correct names
            try:
                # Drop any existing indexes except PRIMARY
                cursor.execute("""
                    SELECT INDEX_NAME
                    FROM information_schema.statistics
                    WHERE table_schema = DATABASE()
                    AND table_name = 'users_groups'
                    AND INDEX_NAME != 'PRIMARY'
                """)
                indexes = cursor.fetchall()
                for idx in indexes:
                    cursor.execute(f"""
                        DROP INDEX {idx[0]} ON users_groups
                    """)
                
                # Create new indexes
                # First add the indexes
                cursor.execute("""
                    ALTER TABLE users_groups
                    ADD UNIQUE INDEX users_groups_user_id_group_id_unique (user_id, group_id),
                    ADD INDEX users_groups_group_id_fk (group_id)
                """)
                
                # Then try to add the foreign key constraint if it doesn't exist
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.KEY_COLUMN_USAGE
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'users_groups'
                    AND REFERENCED_TABLE_NAME = 'auth_group'
                """)
                has_fk = cursor.fetchone()[0] > 0
                
                if not has_fk:
                    try:
                        cursor.execute("""
                            ALTER TABLE users_groups
                            ADD CONSTRAINT users_groups_group_id_fk 
                            FOREIGN KEY (group_id) REFERENCES auth_group (id)
                        """)
                        print("Added foreign key constraint")
                    except Exception as e:
                        print(f"Warning: Could not add foreign key constraint: {e}")
                
                print("\nFixed indexes and constraints")
            except Exception as e:
                print(f"Warning during index fix: {e}")

            # Show final indexes
            cursor.execute("""
                SHOW INDEX FROM users_groups
            """)
            print("\nFinal table indexes:")
            indexes = cursor.fetchall()
            for idx in indexes:
                print(f"Index: {idx[2]}, Column: {idx[4]}, Type: {idx[10]}")

            # Copy data from auth_user_groups if table is empty
            cursor.execute("SELECT COUNT(*) FROM users_groups")
            if cursor.fetchone()[0] == 0:
                cursor.execute("""
                    SELECT COUNT(*) FROM auth_user_groups
                """)
                if cursor.fetchone()[0] > 0:
                    print("\nCopying data from auth_user_groups...")
                    cursor.execute("""
                        INSERT IGNORE INTO users_groups (user_id, group_id)
                        SELECT user_id, group_id FROM auth_user_groups
                    """)
                    cursor.execute("SELECT COUNT(*) FROM users_groups")
                    print(f"Copied {cursor.fetchone()[0]} records")

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

if __name__ == '__main__':
    print("Starting quick fix for users_groups table...")
    quick_fix_users_groups()
    print("Quick fix completed.")
