#!/usr/bin/env python3
"""
Quick Database Fix for User Permissions

This script provides a quick fix for the user_permissions table database issue.
It can be run directly or as a Django management command.

Usage:
    python fix_permissions_db.py
    or
    python manage.py shell < fix_permissions_db.py
"""

import os
import sys
import django
from pathlib import Path

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

from django.db import connection, transaction
from django.core.management import execute_from_command_line

def fix_user_permissions_table():
    """Fix the user_permissions table structure"""
    print("Fixing user_permissions table...")
    
    try:
        with connection.cursor() as cursor:
            # Check if table exists
            cursor.execute("SHOW TABLES LIKE 'user_permissions'")
            table_exists = cursor.fetchone()
            
            if not table_exists:
                print("Creating user_permissions table...")
                create_table_sql = """
                CREATE TABLE user_permissions (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    user_id BIGINT NOT NULL,
                    module VARCHAR(50) NOT NULL,
                    action VARCHAR(30) NOT NULL,
                    is_allowed BOOLEAN NOT NULL DEFAULT FALSE,
                    granted_by_id BIGINT NULL,
                    reason TEXT NULL,
                    expires_at DATETIME NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    UNIQUE KEY unique_user_permission (user_id, module, action),
                    KEY idx_user_expires (user_id, expires_at),
                    KEY idx_module_action (module, action),
                    CONSTRAINT fk_user_permissions_user 
                        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                    CONSTRAINT fk_user_permissions_granted_by 
                        FOREIGN KEY (granted_by_id) REFERENCES users(id) ON DELETE SET NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """
                cursor.execute(create_table_sql)
                print("[SUCCESS] Created user_permissions table")
            else:
                print("user_permissions table exists, checking structure...")
                
                # Check table structure
                cursor.execute("DESCRIBE user_permissions")
                columns = cursor.fetchall()
                
                # Check if module column exists
                module_column = None
                for column in columns:
                    if column[0] == 'module':
                        module_column = column
                        break
                
                if not module_column:
                    print("Adding missing columns to user_permissions table...")
                    # Add missing columns
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN module VARCHAR(50) NOT NULL AFTER user_id")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN action VARCHAR(30) NOT NULL AFTER module")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN is_allowed BOOLEAN NOT NULL DEFAULT FALSE AFTER action")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN granted_by_id BIGINT NULL AFTER is_allowed")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN reason TEXT NULL AFTER granted_by_id")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN expires_at DATETIME NULL AFTER reason")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER expires_at")
                    cursor.execute("ALTER TABLE user_permissions ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at")
                    
                    # Add constraints
                    try:
                        cursor.execute("ALTER TABLE user_permissions ADD UNIQUE KEY unique_user_permission (user_id, module, action)")
                    except:
                        pass  # Constraint might already exist
                    
                    print("[SUCCESS] Added missing columns")
                else:
                    # Check if module column has correct length
                    if 'varchar(20)' in module_column[1].lower():
                        print("Updating module column length from 20 to 50...")
                        cursor.execute("ALTER TABLE user_permissions MODIFY COLUMN module VARCHAR(50) NOT NULL")
                        print("[SUCCESS] Updated module column length")
                    else:
                        print("[SUCCESS] user_permissions table structure is correct")
        
        # Run migrations to ensure everything is in sync
        print("Running Django migrations...")
        execute_from_command_line(['manage.py', 'migrate', 'users', '--verbosity=0'])
        print("[SUCCESS] Django migrations completed")
        
        print("\n[COMPLETE] Database fix completed successfully!")
        print("The user_permissions table should now work correctly.")
        
        return True
        
    except Exception as e:
        print(f"[ERROR] Error fixing database: {str(e)}")
        return False

def main():
    """Main function"""
    print("=" * 60)
    print("QUICK DATABASE FIX: USER PERMISSIONS TABLE")
    print("=" * 60)
    
    success = fix_user_permissions_table()
    
    if success:
        print("\n[SUCCESS] Fix completed successfully!")
        print("You can now access the user permissions page without errors.")
    else:
        print("\n[ERROR] Fix failed!")
        print("Please check the error messages above and try again.")
    
    return success

if __name__ == '__main__':
    main()