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 fix_users_groups_table():
    """
    Fix the users_groups table by ensuring proper column names and constraints
    """
    with connection.cursor() as cursor:
        print("Checking users_groups table...")
        try:
            # Check if the table exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                AND table_name = 'auth_user_groups'
            """)
            auth_user_groups_exists = cursor.fetchone()[0] > 0

            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                AND table_name = 'users_groups'
            """)
            users_groups_exists = cursor.fetchone()[0] > 0

            if users_groups_exists:
                # Check if customuser_id exists
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.columns
                    WHERE table_schema = DATABASE()
                    AND table_name = 'users_groups'
                    AND column_name = 'customuser_id'
                """)
                has_customuser_id = cursor.fetchone()[0] > 0

                if has_customuser_id:
                    print("Renaming customuser_id to user_id in users_groups table...")
                    cursor.execute("""
                        ALTER TABLE users_groups
                        CHANGE COLUMN customuser_id user_id char(32) NOT NULL
                    """)
                    print("Successfully renamed customuser_id to user_id")

            elif auth_user_groups_exists:
                print("Creating users_groups table from auth_user_groups...")
                # Create users_groups table with correct schema
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS 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)
                    )
                """)
                
                # Copy 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
                """)
                print("Successfully created and populated users_groups table")

        except Exception as e:
            print(f"Error fixing users_groups table: {e}")

def fix_notification_models():
    """
    Fix the notification models conflict by adding related_name
    """
    with connection.cursor() as cursor:
        try:
            # Check if reports_notification table exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                AND table_name = 'reports_notification'
            """)
            if cursor.fetchone()[0] > 0:
                # Check if column exists
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.columns
                    WHERE table_schema = DATABASE()
                    AND table_name = 'reports_notification'
                    AND column_name = 'related_name'
                """)
                if cursor.fetchone()[0] == 0:
                    cursor.execute("""
                        ALTER TABLE reports_notification
                        ADD COLUMN related_name VARCHAR(100) DEFAULT 'reports_notifications'
                    """)
                    print("Added related_name to reports_notification table")
            else:
                print("reports_notification table does not exist, skipping...")
            
            # Check if utils_notification table exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                AND table_name = 'utils_notification'
            """)
            if cursor.fetchone()[0] > 0:
                # Check if column exists
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM information_schema.columns
                    WHERE table_schema = DATABASE()
                    AND table_name = 'utils_notification'
                    AND column_name = 'related_name'
                """)
                if cursor.fetchone()[0] == 0:
                    cursor.execute("""
                        ALTER TABLE utils_notification
                        ADD COLUMN related_name VARCHAR(100) DEFAULT 'utils_notifications'
                    """)
                    print("Added related_name to utils_notification table")
            else:
                print("utils_notification table does not exist, skipping...")

        except Exception as e:
            print(f"Error fixing notification models: {e}")

def verify_fixes():
    """
    Verify that the fixes were applied correctly
    """
    with connection.cursor() as cursor:
        try:
            # Check users_groups table
            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("✓ users_groups table has correct user_id column")
            else:
                print("✗ users_groups table structure is not correct")

            # Check notification tables if they exist
            for table in ['reports_notification', 'utils_notification']:
                cursor.execute(f"""
                    SELECT COUNT(*)
                    FROM information_schema.tables 
                    WHERE table_schema = DATABASE()
                    AND table_name = '{table}'
                """)
                if cursor.fetchone()[0] > 0:
                    cursor.execute(f"""
                        SELECT COUNT(*)
                        FROM information_schema.columns
                        WHERE table_schema = DATABASE()
                        AND table_name = '{table}'
                        AND column_name = 'related_name'
                    """)
                    if cursor.fetchone()[0] > 0:
                        print(f"✓ {table} has related_name column")
                    else:
                        print(f"✗ {table} is missing related_name column")

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

if __name__ == '__main__':
    print("Starting database fixes...")
    fix_users_groups_table()
    fix_notification_models()
    print("\nVerifying fixes...")
    verify_fixes()
    print("\nDatabase fixes completed.")