"""
Create the utils_auditlog table with correct foreign key to users table
"""
import os
import django

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

from django.db import connection

def create_auditlog_table():
    """Create the utils_auditlog table"""
    create_sql = """
    CREATE TABLE IF NOT EXISTS `utils_auditlog` (
        `id` char(32) NOT NULL,
        `action` varchar(50) NOT NULL,
        `model_name` varchar(100) NOT NULL,
        `object_id` varchar(50) NOT NULL,
        `description` longtext NOT NULL,
        `ip_address` char(39) DEFAULT NULL,
        `user_agent` varchar(255) DEFAULT NULL,
        `created_at` datetime(6) NOT NULL,
        `user_id` char(32) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `utils_auditlog_user_id_idx` (`user_id`),
        KEY `utils_auditlog_created_at_idx` (`created_at`),
        CONSTRAINT `utils_auditlog_user_id_fk` 
            FOREIGN KEY (`user_id`) 
            REFERENCES `users` (`id`) 
            ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    
    try:
        with connection.cursor() as cursor:
            print("Creating utils_auditlog table...")
            cursor.execute(create_sql)
            print("✓ Table created successfully!")
            
            # Verify it exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
                AND table_name = 'utils_auditlog'
            """)
            result = cursor.fetchone()
            
            if result[0] > 0:
                print("✓ Verified: Table 'utils_auditlog' exists in database")
                
                # Show table structure
                cursor.execute("DESCRIBE utils_auditlog")
                columns = cursor.fetchall()
                
                print("\nTable structure:")
                print("-" * 60)
                for col in columns:
                    print(f"  {col[0]:<20} {col[1]:<20} {col[2]}")
                
                return True
            else:
                print("✗ Warning: Table creation may have failed")
                return False
                
    except Exception as e:
        print(f"✗ Error: {e}")
        return False

if __name__ == '__main__':
    success = create_auditlog_table()
    if success:
        print("\n" + "=" * 60)
        print("SUCCESS! You can now try logging in again.")
        print("=" * 60)
