#!/usr/bin/env python3
"""
Check all tables and views related to permissions to identify the source of the error
"""

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

# 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_all_tables():
    """Check all tables in the database"""
    logger.info("Checking all tables in the database...")
    
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()
        
        logger.info(f"Found {len(tables)} tables:")
        for table in tables:
            table_name = table[0]
            logger.info(f"  - {table_name}")
            
            # Check if it's related to permissions
            if 'permission' in table_name.lower() or 'user' in table_name.lower():
                logger.info(f"    ^ This table might be related to permissions")
                
                # Get table structure
                try:
                    cursor.execute(f"DESCRIBE {table_name}")
                    columns = cursor.fetchall()
                    logger.info(f"    Columns: {[col[0] for col in columns]}")
                    
                    # Check for permission_id column
                    if any('permission_id' in col[0] for col in columns):
                        logger.info(f"    ^ Contains permission_id column")
                        
                        # Check if it has a default value
                        for col in columns:
                            if 'permission_id' in col[0]:
                                logger.info(f"    ^ permission_id: {col[1]} {col[2]} {col[3]} {col[4]} {col[5]}")
                                if col[4] is None and col[2] == 'NO':
                                    logger.warning(f"    ^ WARNING: permission_id has no default value and is NOT NULL")
                
                except Exception as e:
                    logger.error(f"    Error describing table {table_name}: {e}")

def check_views():
    """Check all views in the database"""
    logger.info("Checking all views in the database...")
    
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT TABLE_NAME 
            FROM information_schema.VIEWS 
            WHERE TABLE_SCHEMA = DATABASE()
        """)
        views = cursor.fetchall()
        
        if views:
            logger.info(f"Found {len(views)} views:")
            for view in views:
                logger.info(f"  - {view[0]}")
        else:
            logger.info("No views found")

def check_triggers():
    """Check all triggers in the database"""
    logger.info("Checking all triggers in the database...")
    
    with connection.cursor() as cursor:
        cursor.execute("SHOW TRIGGERS")
        triggers = cursor.fetchall()
        
        if triggers:
            logger.info(f"Found {len(triggers)} triggers:")
            for trigger in triggers:
                logger.info(f"  - {trigger[0]} on {trigger[1]}")
        else:
            logger.info("No triggers found")

def check_constraints():
    """Check all constraints in the database"""
    logger.info("Checking all constraints...")
    
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                COLUMN_NAME,
                CONSTRAINT_NAME,
                REFERENCED_TABLE_NAME,
                REFERENCED_COLUMN_NAME
            FROM information_schema.KEY_COLUMN_USAGE 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND REFERENCED_TABLE_NAME IS NOT NULL
        """)
        constraints = cursor.fetchall()
        
        logger.info(f"Found {len(constraints)} foreign key constraints:")
        for constraint in constraints:
            logger.info(f"  - {constraint[0]}.{constraint[1]} -> {constraint[3]}.{constraint[4]}")

def check_problematic_tables():
    """Check for tables that might be causing the permission error"""
    logger.info("Checking for problematic tables...")
    
    with connection.cursor() as cursor:
        # Check for tables with permission_id column that might not have default values
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                COLUMN_NAME,
                DATA_TYPE,
                IS_NULLABLE,
                COLUMN_DEFAULT,
                EXTRA
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND COLUMN_NAME LIKE '%permission%'
        """)
        permission_columns = cursor.fetchall()
        
        if permission_columns:
            logger.info("Found columns with 'permission' in the name:")
            for col in permission_columns:
                logger.info(f"  - {col[0]}.{col[1]}: {col[2]} {col[3]} {col[4]} {col[5]}")
                
                # Check if this could be causing the error
                if col[1] == 'permission_id' and col[3] == 'NO' and col[4] is None:
                    logger.warning(f"    ^ POTENTIAL PROBLEM: {col[0]}.{col[1]} is NOT NULL with no default value")
        else:
            logger.info("No columns with 'permission' in the name found")

def check_django_content_types():
    """Check Django content types and permissions"""
    logger.info("Checking Django content types and permissions...")
    
    try:
        from django.contrib.contenttypes.models import ContentType
        from django.contrib.auth.models import Permission
        
        # Check content types
        content_types = ContentType.objects.all()
        logger.info(f"Found {content_types.count()} content types")
        
        # Check permissions
        permissions = Permission.objects.all()
        logger.info(f"Found {permissions.count()} permissions")
        
        # Check if there are any issues with the permission model
        try:
            # Try to create a test permission (without saving)
            test_permission = Permission(
                name="Test Permission",
                content_type=ContentType.objects.first(),
                codename="test_permission"
            )
            logger.info("✅ Permission model is working correctly")
        except Exception as e:
            logger.error(f"❌ Permission model has issues: {e}")
        
    except Exception as e:
        logger.error(f"❌ Error checking Django content types: {e}")

def main():
    """Main function"""
    logger.info("=" * 60)
    logger.info("COMPREHENSIVE PERMISSION TABLE CHECK")
    logger.info("=" * 60)
    
    try:
        check_all_tables()
        check_views()
        check_triggers()
        check_constraints()
        check_problematic_tables()
        check_django_content_types()
        
        logger.info("\n" + "=" * 60)
        logger.info("CHECK COMPLETE")
        logger.info("=" * 60)
        logger.info("Review the output above for any tables with permission_id columns")
        logger.info("that don't have default values and are NOT NULL.")
        
    except Exception as e:
        logger.error(f"❌ Error during check: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main()