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 verify_users_groups_setup():
    """
    Verify that the users_groups table is set up correctly
    """
    with connection.cursor() as cursor:
        try:
            print("\nVerifying table structure...")
            
            # Check table exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND table_name = 'users_groups'
            """)
            if cursor.fetchone()[0] == 0:
                print("❌ Error: users_groups table does not exist!")
                return
            
            # Check columns
            cursor.execute("""
                SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'users_groups'
                ORDER BY ORDINAL_POSITION
            """)
            print("\nColumn structure:")
            columns = cursor.fetchall()
            for col in columns:
                print(f"Column: {col[0]}, Type: {col[1]}, Nullable: {col[2]}, Key: {col[3]}")
                
            # Check foreign keys
            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
            """)
            print("\nForeign key constraints:")
            constraints = cursor.fetchall()
            for constraint in constraints:
                print(f"Constraint: {constraint[0]}")
                print(f"  Column: {constraint[1]} -> {constraint[2]}.{constraint[3]}")
            
            # Check indexes
            cursor.execute("""
                SHOW INDEX FROM users_groups
            """)
            print("\nIndexes:")
            indexes = cursor.fetchall()
            for idx in indexes:
                print(f"Index: {idx[2]}, Column: {idx[4]}, Type: {idx[10]}")
            
            # Check data integrity
            cursor.execute("SELECT COUNT(*) FROM users_groups")
            total_rows = cursor.fetchone()[0]
            
            # Check for any invalid foreign keys
            cursor.execute("""
                SELECT COUNT(*)
                FROM users_groups ug
                LEFT JOIN users u ON ug.user_id = u.id
                LEFT JOIN auth_group g ON ug.group_id = g.id
                WHERE u.id IS NULL OR g.id IS NULL
            """)
            invalid_rows = cursor.fetchone()[0]
            
            print(f"\nData integrity:")
            print(f"Total rows: {total_rows}")
            if invalid_rows > 0:
                print(f"❌ Warning: Found {invalid_rows} rows with invalid foreign keys!")
            else:
                print("✓ All foreign key relationships are valid")
            
            # Test a sample query that would be used in deletion
            if total_rows > 0:
                cursor.execute("""
                    SELECT user_id, group_id 
                    FROM users_groups 
                    LIMIT 1
                """)
                sample = cursor.fetchone()
                if sample:
                    user_id = sample[0]
                    print(f"\nTesting sample query for user_id: {user_id}")
                    cursor.execute(f"""
                        SELECT COUNT(*) 
                        FROM users_groups 
                        WHERE user_id = %s
                    """, [user_id])
                    count = cursor.fetchone()[0]
                    print(f"✓ Query test successful - found {count} groups for test user")
            
        except Exception as e:
            print(f"Error during verification: {e}")
            raise

if __name__ == '__main__':
    print("Starting verification of users_groups table...")
    verify_users_groups_setup()
    print("\nVerification complete.")