"""
Script to check and create the utils_auditlog table if it doesn't exist
"""
import os
import django
import sys

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

from django.db import connection
from django.core.management import call_command

def check_table_exists(table_name):
    """Check if a table exists in the database"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.tables 
            WHERE table_schema = DATABASE()
            AND table_name = %s
        """, [table_name])
        result = cursor.fetchone()
        return result[0] > 0

def main():
    table_name = 'utils_auditlog'
    
    print(f"Checking if table '{table_name}' exists...")
    
    if check_table_exists(table_name):
        print(f"✓ Table '{table_name}' exists in the database.")
    else:
        print(f"✗ Table '{table_name}' does NOT exist in the database.")
        print("\nAttempting to create the table...")
        
        try:
            # Get the SQL for creating the table
            from django.core.management.sql import sql_create
            from utils.models import AuditLog
            
            # Create the table using raw SQL
            with connection.cursor() as cursor:
                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_customuser` (`id`) 
                        ON DELETE SET NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
                """
                cursor.execute(create_sql)
                print(f"✓ Table '{table_name}' created successfully!")
                
        except Exception as e:
            print(f"✗ Error creating table: {e}")
            print("\nTrying alternative method: fake migration and re-run...")
            
            try:
                # Fake the migration to reset state
                call_command('migrate', 'utils', '0001_initial', fake=True)
                # Re-run the migration
                call_command('migrate', 'utils', '0002_auditlog')
                print("✓ Migration re-applied successfully!")
            except Exception as e2:
                print(f"✗ Error with migration: {e2}")
                return False
    
    # Verify the table exists now
    if check_table_exists(table_name):
        print(f"\n✓ SUCCESS: Table '{table_name}' is now available in the database.")
        return True
    else:
        print(f"\n✗ FAILED: Table '{table_name}' still does not exist.")
        return False

if __name__ == '__main__':
    success = main()
    sys.exit(0 if success else 1)