#!/usr/bin/env python3
"""
HAVEN GRAZURI INVESTMENT LIMITED- Comprehensive cPanel Setup Script
Version: 3.0 - Updated with Reports Dashboard Fixes and Enhanced Features
Run this script in cPanel's "Execute python script" feature

This script safely sets up the Django application without disrupting existing data.
It includes comprehensive error handling and rollback capabilities.

LATEST UPDATES:
- Fixed Reports & Statements Dashboard UI and functionality
- Enhanced role-based permissions system
- Improved media file handling for production
- Added comprehensive loan products setup
- Fixed database integrity issues
- Enhanced user management system
"""

import os
import sys
import subprocess
import django
import io
import json
import shutil
from datetime import datetime, timedelta
from pathlib import Path
from decimal import Decimal

# Setup logging
SETUP_LOG_FILE = 'setup_log.txt'

def log_message(message, level='INFO'):
    """Log messages to both console and file"""
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    log_entry = f"[{timestamp}] {level}: {message}"
    print(log_entry)
    
    try:
        with open(SETUP_LOG_FILE, 'a', encoding='utf-8') as f:
            f.write(log_entry + '\n')
    except Exception:
        pass  # Don't fail if logging fails

def create_backup():
    """Create backup of critical files before setup"""
    log_message("Creating backup of critical files...")
    
    backup_dir = f"backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
    os.makedirs(backup_dir, exist_ok=True)
    
    critical_files = [
        'branch_system/settings.py',
        'branch_system/settings_production.py',
        'passenger_wsgi.py',
        'requirements.txt'
    ]
    
    for file_path in critical_files:
        if os.path.exists(file_path):
            try:
                shutil.copy2(file_path, backup_dir)
                log_message(f"Backed up {file_path}")
            except Exception as e:
                log_message(f"Failed to backup {file_path}: {e}", 'WARNING')
    
    log_message(f"Backup created in {backup_dir}")
    return backup_dir

def run_command(command, timeout=300):
    """Run a command with timeout and comprehensive error handling"""
    log_message(f"Running: {command}")
    try:
        result = subprocess.run(
            command, 
            shell=True, 
            capture_output=True, 
            text=True, 
            timeout=timeout,
            encoding='utf-8',
            errors='replace'
        )
        
        if result.stdout:
            log_message(f"Output: {result.stdout.strip()}")
        if result.stderr:
            log_message(f"Errors: {result.stderr.strip()}", 'WARNING')
        
        success = result.returncode == 0
        if not success:
            log_message(f"Command failed with return code: {result.returncode}", 'ERROR')
        
        return success
    except subprocess.TimeoutExpired:
        log_message(f"Command timed out after {timeout} seconds", 'ERROR')
        return False
    except Exception as e:
        log_message(f"Error running command: {e}", 'ERROR')
        return False

def check_python_version():
    """Check if Python version is compatible"""
    log_message("Checking Python version...")
    version = sys.version_info
    
    if version.major < 3 or (version.major == 3 and version.minor < 8):
        log_message(f"Python {version.major}.{version.minor} detected. Python 3.8+ required.", 'ERROR')
        return False
    
    log_message(f"Python {version.major}.{version.minor}.{version.micro} - Compatible SUCCESS:")
    return True

def check_disk_space():
    """Check available disk space"""
    log_message("Checking disk space...")
    try:
        statvfs = os.statvfs('.')
        free_bytes = statvfs.f_frsize * statvfs.f_bavail
        free_mb = free_bytes / (1024 * 1024)
        
        if free_mb < 100:  # Less than 100MB
            log_message(f"Low disk space: {free_mb:.1f}MB available", 'WARNING')
            return False
        
        log_message(f"Disk space: {free_mb:.1f}MB available SUCCESS:")
        return True
    except Exception as e:
        log_message(f"Could not check disk space: {e}", 'WARNING')
        return True  # Continue anyway

def verify_database_connection():
    """Verify database connection before proceeding"""
    log_message("Verifying database connection...")
    try:
        # Explicitly load .env so DB credentials are available before django.setup()
        try:
            from dotenv import load_dotenv
            env_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env')
            load_dotenv(env_path, override=True)
            log_message(f"Loaded .env from {env_path}")
        except ImportError:
            log_message("python-dotenv not available, relying on environment variables", 'WARNING')

        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute("SELECT 1")
            result = cursor.fetchone()
            
        if result:
            log_message("Database connection successful SUCCESS:")
            return True
        else:
            log_message("Database connection failed", 'ERROR')
            return False
            
    except Exception as e:
        log_message(f"Database connection error: {e}", 'ERROR')
        return False

def install_requirements():
    """Install requirements with comprehensive error handling and fallback strategies.
    
    Platform notes:
    - python-magic-bin is Windows-only. On Linux/cPanel, python-magic uses the
      system libmagic library (libmagic1 / file-devel). Do NOT install
      python-magic-bin on Linux — it does not exist on PyPI for that platform.
    - whitenoise is included as an essential package for production static file serving.
    """
    log_message("Installing requirements...")
    
    is_linux = sys.platform.startswith('linux')
    is_windows = sys.platform == 'win32'
    log_message(f"Detected platform: {sys.platform}")
    
    # Check if pip is available
    if not run_command("pip --version"):
        log_message("pip not found, trying pip3...", 'WARNING')
        if not run_command("pip3 --version"):
            log_message("Neither pip nor pip3 found. Cannot install packages.", 'ERROR')
            return False
        pip_cmd = "pip3"
    else:
        pip_cmd = "pip"
    
    # Upgrade pip first to avoid resolver issues
    run_command(f"{pip_cmd} install --upgrade pip --no-cache-dir")
    
    # Try installing from requirements.txt first (excludes python-magic-bin)
    log_message("Attempting to install from requirements.txt...")
    if os.path.exists('requirements.txt'):
        success = run_command(f"{pip_cmd} install -r requirements.txt --no-cache-dir")
        if success:
            log_message("SUCCESS: requirements.txt installed successfully")
            # Still verify critical imports below
        else:
            log_message("requirements.txt install had issues, falling back to individual packages...", 'WARNING')
    
    # Essential packages that must be installed
    essential_packages = [
        "Django>=5.2.4",
        "PyMySQL>=1.1.0",
        "Pillow>=10.2.0",
        "reportlab>=4.0.7",
        "openpyxl>=3.1.2",
        "python-dateutil>=2.8.2",
        "requests>=2.31.0",
        "django-extensions>=3.2.0",
        "whitenoise>=6.5.0",  # Required for production static file serving
        "python-dotenv>=1.0.0",  # Required for .env file loading in settings.py
    ]
    
    # Enhanced packages for full functionality
    enhanced_packages = [
        "django-phonenumber-field>=7.3.0",
        "phonenumbers>=8.13.25",
        "xlsxwriter>=3.1.9",
        "python-magic>=0.4.27",  # Uses system libmagic on Linux; no binary needed
        "cryptography>=41.0.0",
    ]
    
    # Optional packages for advanced features (non-critical)
    optional_packages = [
        "matplotlib>=3.7.0",
        "seaborn>=0.12.0",
        "numpy>=1.24.0",
    ]
    
    # Windows-only packages — skip entirely on Linux/cPanel
    windows_only_packages = [
        "python-magic-bin>=0.4.14",  # Provides libmagic binary on Windows only
    ]
    
    # Install essential packages
    log_message("Installing essential packages...")
    failed_essential = []
    for package in essential_packages:
        log_message(f"Installing {package}...")
        if not run_command(f"{pip_cmd} install \"{package}\" --no-cache-dir"):
            failed_essential.append(package)
            log_message(f"Failed to install {package}", 'ERROR')
        else:
            log_message(f"SUCCESS: {package} installed")
    
    if failed_essential:
        log_message(f"ERROR: Critical packages failed to install: {failed_essential}", 'ERROR')
        log_message("The application may not function properly without these packages.", 'ERROR')
        return False
    
    # Install enhanced packages
    log_message("Installing enhanced packages...")
    for package in enhanced_packages:
        log_message(f"Installing {package}...")
        if run_command(f"{pip_cmd} install \"{package}\" --no-cache-dir"):
            log_message(f"SUCCESS: {package} installed")
        else:
            log_message(f"WARNING: {package} failed to install (non-critical)", 'WARNING')
    
    # Install optional packages
    log_message("Installing optional packages...")
    for package in optional_packages:
        log_message(f"Installing {package}...")
        if run_command(f"{pip_cmd} install \"{package}\" --no-cache-dir"):
            log_message(f"SUCCESS: {package} installed")
        else:
            log_message(f"WARNING: {package} failed to install (optional)", 'WARNING')
    
    # Install Windows-only packages only on Windows
    if is_windows:
        log_message("Installing Windows-only packages...")
        for package in windows_only_packages:
            log_message(f"Installing {package}...")
            if run_command(f"{pip_cmd} install \"{package}\" --no-cache-dir"):
                log_message(f"SUCCESS: {package} installed")
            else:
                log_message(f"WARNING: {package} failed to install (Windows-only, optional)", 'WARNING')
    else:
        log_message("Skipping Windows-only packages on non-Windows platform (python-magic-bin not needed on Linux)")
        log_message("NOTE: Ensure libmagic is installed on the server: 'yum install file-libs' or 'apt-get install libmagic1'")
    
    # Verify critical imports
    log_message("Verifying critical package imports...")
    critical_imports = [
        ('django', 'Django'),
        ('pymysql', 'PyMySQL'),
        ('PIL', 'Pillow'),
        ('reportlab', 'ReportLab'),
        ('openpyxl', 'OpenPyXL'),
        ('whitenoise', 'WhiteNoise'),
        ('dotenv', 'python-dotenv'),
    ]
    
    for module, name in critical_imports:
        try:
            __import__(module)
            log_message(f"SUCCESS: {name} import successful")
        except ImportError as e:
            log_message(f"ERROR: {name} import failed: {e}", 'ERROR')
            return False
    
    log_message("SUCCESS: Requirements installation completed successfully")
    return True

def create_production_settings():
    """Create or update production settings file safely"""
    log_message("Creating/updating production settings...")
    
    settings_file = 'branch_system/settings_production.py'
    
    # If the production marker exists, the system has been manually configured.
    # Do NOT overwrite settings_production.py — it contains live credentials and
    # customisations that setup.py does not know about.
    marker_file = '.production_configured'
    if os.path.exists(marker_file):
        log_message("SUCCESS: Production already configured (.production_configured marker found) — skipping settings overwrite")
        return True
    
    # Check if production settings already exist
    if os.path.exists(settings_file):
        log_message("Production settings file already exists, creating backup...")
        backup_file = f"{settings_file}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        shutil.copy2(settings_file, backup_file)
        log_message(f"Backup created: {backup_file}")
    
    settings_content = '''"""
Production settings for HAVEN GRAZURI Advance
Auto-generated by setup.py - DO NOT EDIT MANUALLY
"""
from .settings import *
import os

# Security Settings
DEBUG = False
ALLOWED_HOSTS = [
    'uzuriapps.xyz',
    'www.uzuriapps.xyz',
    'grazuri.uzuriapps.xyz',
    'www.grazuri.uzuriapps.xyz',
    'branchbusinessadvance.co.ke',
    'www.branchbusinessadvance.co.ke',
    'localhost',
    '127.0.0.1',
]

# Database Settings - Using existing configuration
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': os.getenv('DB_NAME', 'xygbfpsg_loans'),
        'USER': os.getenv('DB_USER', 'xygbfpsg_graz'),
        'PASSWORD': os.getenv('DB_PASSWORD', "+MvX9&%PV']]pW}"),
        'HOST': os.getenv('DB_HOST', 'localhost'),
        'PORT': os.getenv('DB_PORT', '3306'),
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'charset': 'utf8mb4',
            'connect_timeout': 60,
            'read_timeout': 60,
            'write_timeout': 60,
        }
    }
}

# Security Settings - Enhanced for production
SECRET_KEY = os.getenv('SECRET_KEY', '(W6B1[bCEAu,1wA[dz7^-prod-key-2024)')

# SSL/HTTPS Settings (conditional based on environment)
USE_HTTPS = os.getenv('USE_HTTPS', 'True').lower() == 'true'
if USE_HTTPS:
    SECURE_SSL_REDIRECT = True
    SECURE_PROXY_SSL_HEADER = ('HTTP_X_FORWARDED_PROTO', 'https')
    SESSION_COOKIE_SECURE = True
    CSRF_COOKIE_SECURE = True
    SECURE_HSTS_SECONDS = 31536000  # 1 year
    SECURE_HSTS_INCLUDE_SUBDOMAINS = True
    SECURE_HSTS_PRELOAD = True

# Session Settings - Enhanced security
SESSION_COOKIE_AGE = int(os.getenv('SESSION_TIMEOUT', '3600'))  # 1 hour default
SESSION_EXPIRE_AT_BROWSER_CLOSE = True
SESSION_SAVE_EVERY_REQUEST = True
SESSION_COOKIE_HTTPONLY = True
SESSION_COOKIE_SAMESITE = 'Lax'

# Static and Media Files
STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles')
STATIC_URL = '/static/'
MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'

# Ensure directories exist
os.makedirs(STATIC_ROOT, exist_ok=True)
os.makedirs(MEDIA_ROOT, exist_ok=True)
os.makedirs(os.path.join(BASE_DIR, 'logs'), exist_ok=True)

# Email Settings
EMAIL_BACKEND = 'django.core.mail.backends.smtp.EmailBackend'
EMAIL_HOST = os.getenv('EMAIL_HOST', 'smtp.branchbusinessadvance.co.ke')
EMAIL_PORT = int(os.getenv('EMAIL_PORT', '587'))
EMAIL_USE_TLS = os.getenv('EMAIL_USE_TLS', 'True').lower() == 'true'
EMAIL_HOST_USER = os.getenv('EMAIL_USER', 'support@branchbusinessadvance.co.ke')
EMAIL_HOST_PASSWORD = os.getenv('EMAIL_PASSWORD', '')
DEFAULT_FROM_EMAIL = os.getenv('DEFAULT_FROM_EMAIL', 'support@branchbusinessadvance.co.ke')
SERVER_EMAIL = DEFAULT_FROM_EMAIL

# File Upload Settings
FILE_UPLOAD_MAX_MEMORY_SIZE = 10 * 1024 * 1024  # 10MB
DATA_UPLOAD_MAX_MEMORY_SIZE = 10 * 1024 * 1024  # 10MB
FILE_UPLOAD_PERMISSIONS = 0o644

# Cache Settings (using database cache for simplicity)
CACHES = {
    'default': {
        'BACKEND': 'django.core.cache.backends.db.DatabaseCache',
        'LOCATION': 'cache_table',
        'TIMEOUT': 300,  # 5 minutes
        'OPTIONS': {
            'MAX_ENTRIES': 1000,
        }
    }
}

# Logging Configuration
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'formatters': {
        'verbose': {
            'format': '{levelname} {asctime} {module} {process:d} {thread:d} {message}',
            'style': '{',
        },
        'simple': {
            'format': '{levelname} {message}',
            'style': '{',
        },
    },
    'handlers': {
        'file': {
            'level': 'ERROR',
            'class': 'logging.handlers.RotatingFileHandler',
            'filename': os.path.join(BASE_DIR, 'logs', 'django.log'),
            'maxBytes': 1024*1024*5,  # 5MB
            'backupCount': 5,
            'formatter': 'verbose',
        },
        'security_file': {
            'level': 'WARNING',
            'class': 'logging.handlers.RotatingFileHandler',
            'filename': os.path.join(BASE_DIR, 'logs', 'security.log'),
            'maxBytes': 1024*1024*5,  # 5MB
            'backupCount': 5,
            'formatter': 'verbose',
        },
        'console': {
            'level': 'INFO',
            'class': 'logging.StreamHandler',
            'formatter': 'simple',
        },
    },
    'loggers': {
        'django': {
            'handlers': ['file', 'console'],
            'level': 'INFO',
            'propagate': False,
        },
        'django.security': {
            'handlers': ['security_file'],
            'level': 'WARNING',
            'propagate': False,
        },
        'branch_system': {
            'handlers': ['file', 'console'],
            'level': 'INFO',
            'propagate': False,
        },
    },
}

# Performance Settings
USE_ETAGS = True
USE_L10N = True

# Security Headers
SECURE_CONTENT_TYPE_NOSNIFF = True
SECURE_BROWSER_XSS_FILTER = True
X_FRAME_OPTIONS = 'DENY'

# Custom settings for HAVEN GRAZURI Advance
BRANCH_SETTINGS = {
    'COMPANY_NAME': 'HAVEN GRAZURI Advance',
    'COMPANY_EMAIL': 'support@branchbusinessadvance.co.ke',
    'COMPANY_PHONE': '+254700000000',
    'COMPANY_ADDRESS': 'Nairobi, Kenya',
    'SYSTEM_VERSION': '2.0',
    'MAINTENANCE_MODE': False,
}

# Audit Logging
AUDIT_LOG_ENABLED = True
AUDIT_LOG_EXCLUDE_URLS = [
    '/static/',
    '/media/',
    '/favicon.ico',
    '/health/',
]

# Rate Limiting (basic implementation)
RATELIMIT_ENABLE = True
RATELIMIT_USE_CACHE = 'default'

# Backup Settings
BACKUP_ENABLED = os.getenv('BACKUP_ENABLED', 'True').lower() == 'true'
BACKUP_RETENTION_DAYS = int(os.getenv('BACKUP_RETENTION_DAYS', '30'))
'''
    
    try:
        with open(settings_file, 'w', encoding='utf-8') as f:
            f.write(settings_content)
        log_message("SUCCESS: Production settings created/updated successfully")
        return True
    except Exception as e:
        log_message(f"ERROR: Failed to create production settings: {e}", 'ERROR')
        return False


def update_passenger_wsgi():
    """Update passenger_wsgi.py"""
    print("\n🔄 Updating passenger_wsgi.py...")
    
    content = """import os
import sys

# Add the project directory to the Python path
sys.path.insert(0, os.path.dirname(__file__))

# Set production settings
os.environ["DJANGO_SETTINGS_MODULE"] = "branch_system.settings_production"

# Import and create WSGI application
from django.core.wsgi import get_wsgi_application
application = get_wsgi_application()
"""
    
    with open('passenger_wsgi.py', 'w') as f:
        f.write(content)
    print("SUCCESS: passenger_wsgi.py updated")

def verify_admin_user():
    """Create or verify admin user"""
    log_message("Verifying admin user...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.contrib.auth import get_user_model
        from django.contrib.auth.hashers import make_password
        
        User = get_user_model()
        email = 'admin@branchbusinessadvance.com'
        password = 'admin123'
        
        try:
            user = User.objects.get(email=email)
            log_message(f"Found existing user: {email}")
            # Update existing admin user with new role system
            user.password = make_password(password)
            user.is_staff = True
            user.is_superuser = True
            user.role = 'admin'  # Ensure role is set to admin
            user.status = 'active'
            user.is_phone_verified = True
            user.is_email_verified = True
            user.save()
            log_message("SUCCESS: Admin user updated with new role system")
        except User.DoesNotExist:
            User.objects.create_superuser(
                username='admin',
                email=email,
                password=password,
                role='admin',
                status='active',
                is_phone_verified=True,
                is_email_verified=True,
                first_name='System',
                last_name='Administrator'
            )
            log_message("SUCCESS: Admin user created")
    except Exception as e:
        log_message(f"ERROR: Error with admin user: {e}", 'ERROR')

def setup_role_permissions():
    """Setup role-based permissions system"""
    log_message("Setting up role-based permissions...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        # First ensure the table structure is correct
        fix_role_permissions_table()
        
        from users.models import RolePermission
        
        # Clear existing permissions to avoid duplicates
        existing_count = RolePermission.objects.count()
        if existing_count > 0:
            log_message(f"Found {existing_count} existing permissions, updating...")
            RolePermission.objects.all().delete()
        
        # Define default permissions for each role
        permissions = {
            'admin': {
                # Admin has all permissions
                'users': ['view', 'create', 'edit', 'delete', 'manage'],
                'loans': ['view', 'create', 'edit', 'delete', 'approve', 'reject', 'manage'],
                'reports': ['view', 'create', 'edit', 'delete', 'export', 'manage'],
                'documents': ['view', 'create', 'edit', 'delete', 'manage'],
                'settings': ['view', 'create', 'edit', 'delete', 'manage'],
                'notifications': ['view', 'create', 'edit', 'delete', 'manage'],
                'audit': ['view', 'create', 'edit', 'delete', 'export', 'manage'],
                'kyc': ['view', 'create', 'edit', 'delete', 'approve', 'reject', 'manage'],
                'payments': ['view', 'create', 'edit', 'delete', 'approve', 'reject', 'manage'],
                'communications': ['view', 'create', 'edit', 'delete', 'manage'],
            },
            'team_leader': {
                # Team Leader has broad permissions but can't manage system settings
                'users': ['view', 'create', 'edit'],  # Can't delete users
                'loans': ['view', 'create', 'edit', 'approve', 'reject'],
                'reports': ['view', 'create', 'edit', 'export'],
                'documents': ['view', 'create', 'edit'],
                'settings': ['view'],  # Can only view settings
                'notifications': ['view', 'create', 'edit'],
                'audit': ['view', 'export'],
                'kyc': ['view', 'create', 'edit', 'approve', 'reject'],
                'payments': ['view', 'create', 'edit', 'approve', 'reject'],
                'communications': ['view', 'create', 'edit'],
            },
            'loan_officer': {
                # Loan Officer focuses on loan processing and client management
                'users': ['view', 'create', 'edit'],  # Can manage clients
                'loans': ['view', 'create', 'edit', 'approve', 'reject'],
                'reports': ['view', 'export'],
                'documents': ['view', 'create', 'edit'],
                'settings': [],  # No access to settings
                'notifications': ['view', 'create'],
                'audit': ['view'],
                'kyc': ['view', 'create', 'edit', 'approve', 'reject'],
                'payments': ['view', 'create', 'edit'],
                'communications': ['view', 'create'],
            },
            'secretary': {
                # Secretary handles administrative tasks and document management
                'users': ['view', 'create', 'edit'],  # Can manage client records
                'loans': ['view', 'create', 'edit'],  # Can't approve/reject
                'reports': ['view', 'export'],
                'documents': ['view', 'create', 'edit'],
                'settings': [],  # No access to settings
                'notifications': ['view', 'create'],
                'audit': ['view'],
                'kyc': ['view', 'create', 'edit'],
                'payments': ['view', 'create'],
                'communications': ['view', 'create'],
            },
            'auditor': {
                # Auditor has read-only access to most modules
                'users': ['view'],
                'loans': ['view'],
                'reports': ['view', 'export'],
                'documents': ['view'],
                'settings': ['view'],
                'notifications': ['view'],
                'audit': ['view', 'export'],
                'kyc': ['view'],
                'payments': ['view'],
                'communications': ['view'],
            },
            'borrower': {
                # Borrowers have very limited access
                'users': ['view'],  # Can only view their own profile
                'loans': ['view'],  # Can only view their own loans
                'reports': [],  # No access to reports
                'documents': ['view'],  # Can only view their own documents
                'settings': [],  # No access to settings
                'notifications': ['view'],
                'audit': [],  # No access to audit logs
                'kyc': ['view', 'create', 'edit'],  # Can manage their own KYC
                'payments': ['view'],  # Can only view their own payments
                'communications': ['view'],
            }
        }
        
        # Create permissions
        created_count = 0
        for role, modules in permissions.items():
            for module, actions in modules.items():
                for action in actions:
                    RolePermission.objects.get_or_create(
                        role=role,
                        module=module,
                        action=action,
                        defaults={'is_allowed': True}
                    )
                    created_count += 1
        
        log_message(f"SUCCESS: Successfully set up {created_count} role permissions")
        
        # Display summary
        for role in permissions.keys():
            count = RolePermission.objects.filter(role=role).count()
            log_message(f"   {role.title()}: {count} permissions")
            
    except Exception as e:
        log_message(f"ERROR: Error setting up role permissions: {e}", 'ERROR')

def fix_migration_dependencies():
    """Fix migration dependency issues"""
    log_message("Fixing migration dependencies...")
    
    try:
        # Ensure migration directories exist
        migration_dirs = ['users/migrations', 'loans/migrations', 'reports/migrations', 'utils/migrations']
        
        for migration_dir in migration_dirs:
            os.makedirs(migration_dir, exist_ok=True)
            init_file = os.path.join(migration_dir, '__init__.py')
            if not os.path.exists(init_file):
                with open(init_file, 'w') as f:
                    f.write('')
                log_message(f"Created {init_file}")
        
        # Check for problematic migration files
        problematic_migrations = [
            'utils/migrations/0015_notification_related_loan.py',
        ]
        
        for migration_file in problematic_migrations:
            if os.path.exists(migration_file):
                log_message(f"Removing problematic migration: {migration_file}")
                os.remove(migration_file)
                log_message(f"SUCCESS: Removed {migration_file}")
        
        # Also check for any migration files that reference non-existent dependencies
        for migration_dir in migration_dirs:
            if os.path.exists(migration_dir):
                for filename in os.listdir(migration_dir):
                    if filename.endswith('.py') and filename != '__init__.py':
                        filepath = os.path.join(migration_dir, filename)
                        try:
                            with open(filepath, 'r', encoding='utf-8') as f:
                                content = f.read()
                                # Check for the problematic dependency
                                if "('loans', '0014_merge_20250827_0154')" in content:
                                    log_message(f"Found problematic dependency in {filepath}, removing...")
                                    os.remove(filepath)
                                    log_message(f"SUCCESS: Removed {filepath}")
                        except Exception as e:
                            log_message(f"WARNING: Could not check {filepath}: {e}", 'WARNING')
        
        # Create a dummy merge migration if needed
        loans_migration_dir = 'loans/migrations'
        merge_migration_file = os.path.join(loans_migration_dir, '0014_merge_20250827_0154.py')
        
        if not os.path.exists(merge_migration_file):
            log_message("Creating dummy merge migration file...")
            merge_content = '''# Generated by setup.py to fix dependency issues

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('loans', '0012_remove_registration_fee_fields'),  # Use the actual last migration
    ]

    operations = [
        # This is a dummy merge migration
    ]
'''
            try:
                with open(merge_migration_file, 'w', encoding='utf-8') as f:
                    f.write(merge_content)
                log_message(f"SUCCESS: Created dummy merge migration {merge_migration_file}")
            except Exception as e:
                log_message(f"WARNING: Could not create merge migration: {e}", 'WARNING')
        
        log_message("SUCCESS: Migration dependencies fixed")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing migration dependencies: {e}", 'ERROR')

def fix_duplicate_column_issues():
    """Fix duplicate column issues by checking existing columns before migration"""
    log_message("Fixing duplicate column issues...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Fix users table duplicate columns
            users_columns_to_check = [
                'registration_fee_amount',
                'registration_fee_paid', 
                'registration_fee_payment_date',
                'registration_fee_payment_method',
                'registration_fee_receipt_number',
                'registration_fee_notes'
            ]
            
            log_message("Checking users table for duplicate columns...")
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users'
            """)
            existing_users_columns = [row[0] for row in cursor.fetchall()]
            
            for column in users_columns_to_check:
                if column in existing_users_columns:
                    log_message(f"Column {column} already exists in users table")
                else:
                    log_message(f"Column {column} missing from users table")
            
            # Fix utils_notification table duplicate columns
            notification_columns_to_check = [
                'loan_app_id',
                'related_loan_id'
            ]
            
            log_message("Checking utils_notification table for duplicate columns...")
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'utils_notification'
            """)
            existing_notification_columns = [row[0] for row in cursor.fetchall()]
            
            for column in notification_columns_to_check:
                if column in existing_notification_columns:
                    log_message(f"Column {column} already exists in utils_notification table")
                else:
                    log_message(f"Column {column} missing from utils_notification table")
            
            # Mark problematic migrations as applied if columns already exist
            log_message("Marking problematic migrations as applied...")
            
            # Check if the problematic migration is already applied
            cursor.execute("""
                SELECT COUNT(*) FROM django_migrations 
                WHERE app = 'users' AND name = '0009_customuser_registration_fee_amount_and_more'
            """)
            
            if cursor.fetchone()[0] == 0 and all(col in existing_users_columns for col in users_columns_to_check):
                # Mark the migration as applied since columns already exist
                cursor.execute("""
                    INSERT INTO django_migrations (app, name, applied) 
                    VALUES ('users', '0009_customuser_registration_fee_amount_and_more', NOW())
                """)
                log_message("Marked users migration 0009 as applied")
            
            # Check utils migration
            cursor.execute("""
                SELECT COUNT(*) FROM django_migrations 
                WHERE app = 'utils' AND name = '0015_notification_loan_app_notification_related_loan'
            """)
            
            if cursor.fetchone()[0] == 0 and all(col in existing_notification_columns for col in notification_columns_to_check):
                # Mark the migration as applied since columns already exist
                cursor.execute("""
                    INSERT INTO django_migrations (app, name, applied) 
                    VALUES ('utils', '0015_notification_loan_app_notification_related_loan', NOW())
                """)
                log_message("Marked utils migration 0015 as applied")
        
        log_message("SUCCESS: Duplicate column issues fixed")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing duplicate column issues: {e}", 'ERROR')

def fix_role_permissions_table():
    """Fix role_permissions table structure"""
    log_message("Fixing role_permissions table structure...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check if role_permissions table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'role_permissions'
            """)
            
            if cursor.fetchone()[0] > 0:
                # Check current columns
                cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'role_permissions'
                """)
                existing_columns = [row[0] for row in cursor.fetchall()]
                log_message(f"role_permissions existing columns: {existing_columns}")
                
                # Add missing columns if needed
                required_columns = [
                    ('is_allowed', 'BOOLEAN DEFAULT TRUE'),
                    ('created_at', 'DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)'),
                    ('updated_at', 'DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)')
                ]
                
                for column_name, column_def in required_columns:
                    if column_name not in existing_columns:
                        try:
                            cursor.execute(f"""
                                ALTER TABLE role_permissions 
                                ADD COLUMN {column_name} {column_def}
                            """)
                            log_message(f"Added {column_name} column to role_permissions")
                        except Exception as e:
                            log_message(f"WARNING: Could not add {column_name}: {e}", 'WARNING')
                
                # Remove is_default column if it exists (it's not needed)
                if 'is_default' in existing_columns:
                    try:
                        cursor.execute("ALTER TABLE role_permissions DROP COLUMN is_default")
                        log_message("Removed is_default column from role_permissions")
                    except Exception as e:
                        log_message(f"WARNING: Could not remove is_default column: {e}", 'WARNING')
            else:
                # Create the table if it doesn't exist
                cursor.execute("""
                    CREATE TABLE role_permissions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        role VARCHAR(50) NOT NULL,
                        module VARCHAR(50) NOT NULL,
                        action VARCHAR(50) NOT NULL,
                        is_allowed BOOLEAN DEFAULT TRUE,
                        created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                        updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
                        UNIQUE KEY unique_role_module_action (role, module, action),
                        INDEX idx_role_permissions_role (role),
                        INDEX idx_role_permissions_module (module)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("Created role_permissions table")
        
        log_message("SUCCESS: role_permissions table structure fixed")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing role_permissions table: {e}", 'ERROR')

def run_migrations_with_retry():
    """Run Django migrations with better error handling"""
    log_message("Running Django migrations...")
    
    # First, fix duplicate column issues
    fix_duplicate_column_issues()
    
    # Fix role permissions table
    fix_role_permissions_table()
    
    # Fix migration dependencies
    fix_migration_dependencies()
    
    apps = ['users', 'loans', 'reports', 'utils']
    
    for app in apps:
        log_message(f"Making migrations for {app}...")
        success = run_command(f"python manage.py makemigrations {app}")
        if not success:
            log_message(f"WARNING: Failed to make migrations for {app}, but continuing...", 'WARNING')
    
    log_message("Running migrate command...")
    success = run_command("python manage.py migrate")
    if not success:
        log_message("WARNING: Migration failed, trying alternative approaches...", 'WARNING')
        # Try to migrate built-in apps first
        run_command("python manage.py migrate --run-syncdb")
        
        # Try migrating each app individually with fake-initial
        for app in apps:
            log_message(f"Attempting to migrate {app} individually...")
            run_command(f"python manage.py migrate {app} --fake-initial")

def ensure_monthly_income_field():
    """Ensure monthly_income field and registration_fee_amount exist in CustomUser model"""
    log_message("Ensuring required fields exist in users table...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        # Fields to check/add - including all registration fee fields
        required_fields = [
            ('monthly_income', 'DECIMAL(12,2) NULL'),
            ('employer', 'VARCHAR(200) NULL'),
            ('registration_fee_amount', 'DECIMAL(10,2) NULL'),
            ('registration_fee_paid', 'BOOLEAN DEFAULT FALSE'),
            ('registration_fee_payment_date', 'DATETIME(6) NULL'),
            ('registration_fee_payment_method', 'VARCHAR(20) NULL'),
            ('registration_fee_receipt_number', 'VARCHAR(50) NULL'),
            ('registration_fee_notes', 'LONGTEXT NULL'),
        ]
        
        with connection.cursor() as cursor:
            for field_name, field_definition in required_fields:
                # Check if field exists
                cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'users' 
                    AND COLUMN_NAME = %s
                """, [field_name])
                result = cursor.fetchone()
                
                if not result:
                    log_message(f"Adding {field_name} field to users table...")
                    cursor.execute(f"""
                        ALTER TABLE users 
                        ADD COLUMN {field_name} {field_definition}
                    """)
                    log_message(f"SUCCESS: Added {field_name} field")
                else:
                    log_message(f"SUCCESS: {field_name} field already exists")
        
        # Clear Django model cache to ensure changes are recognized
        try:
            from django.apps import apps
            from django.contrib.auth import get_user_model
            
            apps.clear_cache()
            User = get_user_model()
            User._meta._expire_cache()
            log_message("SUCCESS: Cleared Django model cache")
        except Exception as cache_error:
            log_message(f"WARNING: Could not clear model cache: {cache_error}", 'WARNING')
                
    except Exception as e:
        log_message(f"ERROR: Error ensuring required fields: {e}", 'ERROR')

def ensure_document_fields():
    """Ensure all document fields exist in CustomUser model"""
    print("\n📄 Ensuring document fields exist...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        # List of document fields to check/add
        document_fields = [
            ('logbook', 'VARCHAR(255) NULL'),
            ('title_deed', 'VARCHAR(255) NULL'),
            ('signature', 'VARCHAR(255) NULL'),
        ]
        
        with connection.cursor() as cursor:
            for field_name, field_type in document_fields:
                # Check if field exists
                cursor.execute(f"""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'users' 
                    AND COLUMN_NAME = '{field_name}'
                """)
                result = cursor.fetchone()
                
                if not result:
                    print(f"Adding {field_name} field to users table...")
                    cursor.execute(f"""
                        ALTER TABLE users 
                        ADD COLUMN {field_name} {field_type}
                    """)
                    print(f"SUCCESS: Added {field_name} field")
                else:
                    print(f"SUCCESS: {field_name} field already exists")
                    
    except Exception as e:
        print(f"ERROR: Error ensuring document fields: {e}")

def ensure_loan_table_integrity():
    """Ensure loans table has all required fields"""
    log_message("Ensuring loans table integrity...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        # Required fields for loans table
        required_loan_fields = [
            ('is_deleted', 'BOOLEAN DEFAULT FALSE'),
            ('deleted_at', 'DATETIME(6) NULL'),
            ('deleted_by_id', 'CHAR(32) NULL'),
            ('amount_paid', 'DECIMAL(12,2) DEFAULT 0.00'),
            ('last_payment_date', 'DATETIME(6) NULL'),
            ('is_rolled_over', 'BOOLEAN DEFAULT FALSE'),
            ('original_loan_id', 'CHAR(32) NULL'),
        ]
        
        with connection.cursor() as cursor:
            for field_name, field_definition in required_loan_fields:
                # Check if field exists
                cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'loans' 
                    AND COLUMN_NAME = %s
                """, [field_name])
                
                if not cursor.fetchone():
                    log_message(f"Adding missing field '{field_name}' to loans table...")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE loans 
                            ADD COLUMN {field_name} {field_definition}
                        """)
                        log_message(f"SUCCESS: Added {field_name} field to loans table")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {field_name}: {e}", 'ERROR')
                else:
                    log_message(f"SUCCESS: {field_name} field already exists in loans table")
            
            # Add indexes for performance
            indexes_to_add = [
                ('idx_loans_is_deleted', 'loans', 'is_deleted'),
                ('idx_loans_deleted_by', 'loans', 'deleted_by_id'),
                ('idx_loans_original_loan', 'loans', 'original_loan_id'),
                ('idx_loans_status', 'loans', 'status'),
                ('idx_loans_borrower', 'loans', 'borrower_id'),
            ]
            
            for index_name, table_name, column_name in indexes_to_add:
                try:
                    cursor.execute(f"""
                        CREATE INDEX {index_name} ON {table_name} ({column_name})
                    """)
                    log_message(f"SUCCESS: Created index {index_name}")
                except Exception as e:
                    if "Duplicate key name" in str(e):
                        log_message(f"SUCCESS: Index {index_name} already exists")
                    else:
                        log_message(f"WARNING: Could not create index {index_name}: {e}", 'WARNING')
        
        log_message("SUCCESS: Loans table integrity check completed")
        
    except Exception as e:
        log_message(f"ERROR: Error ensuring loans table integrity: {e}", 'ERROR')

def fix_database_collation_issues():
    """Fix database collation mismatch issues"""
    log_message("Fixing database collation issues...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Get current database collation
            cursor.execute("SELECT @@collation_database")
            db_collation = cursor.fetchone()[0]
            log_message(f"Current database collation: {db_collation}")
            
            # Set consistent collation for all tables
            target_collation = 'utf8mb4_unicode_ci'
            
            # Get all tables in the database
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_TYPE = 'BASE TABLE'
            """)
            tables = [row[0] for row in cursor.fetchall()]
            
            log_message(f"Found {len(tables)} tables to check for collation")
            
            for table_name in tables:
                try:
                    # Check table collation
                    cursor.execute(f"""
                        SELECT TABLE_COLLATION 
                        FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = '{table_name}'
                    """)
                    table_collation = cursor.fetchone()[0]
                    
                    if table_collation != target_collation:
                        log_message(f"Fixing collation for table {table_name}: {table_collation} -> {target_collation}")
                        cursor.execute(f"""
                            ALTER TABLE {table_name} 
                            CONVERT TO CHARACTER SET utf8mb4 
                            COLLATE {target_collation}
                        """)
                        log_message(f"SUCCESS: Fixed collation for {table_name}")
                    else:
                        log_message(f"SUCCESS: {table_name} already has correct collation")
                        
                except Exception as e:
                    log_message(f"WARNING: Could not fix collation for {table_name}: {e}", 'WARNING')
            
            # Fix specific string columns that might have collation issues
            critical_string_columns = [
                ('users', 'email', 'VARCHAR(254)'),
                ('users', 'first_name', 'VARCHAR(150)'),
                ('users', 'last_name', 'VARCHAR(150)'),
                ('users', 'phone_number', 'VARCHAR(20)'),
                ('users', 'role', 'VARCHAR(20)'),
                ('users', 'status', 'VARCHAR(20)'),
                ('loans', 'status', 'VARCHAR(20)'),
                ('loans', 'product_type', 'VARCHAR(50)'),
                ('utils_systemsetting', 'key', 'VARCHAR(100)'),
                ('utils_systemsetting', 'value', 'TEXT'),
                ('utils_systemsetting', 'category', 'VARCHAR(50)'),
            ]
            
            for table_name, column_name, column_type in critical_string_columns:
                try:
                    cursor.execute(f"""
                        SELECT COUNT(*) 
                        FROM INFORMATION_SCHEMA.COLUMNS 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = '{table_name}' 
                        AND COLUMN_NAME = '{column_name}'
                    """)
                    
                    if cursor.fetchone()[0] > 0:
                        cursor.execute(f"""
                            ALTER TABLE {table_name} 
                            MODIFY COLUMN {column_name} {column_type} 
                            CHARACTER SET utf8mb4 
                            COLLATE {target_collation}
                        """)
                        log_message(f"SUCCESS: Fixed collation for {table_name}.{column_name}")
                        
                except Exception as e:
                    log_message(f"WARNING: Could not fix column collation for {table_name}.{column_name}: {e}", 'WARNING')
        
        log_message("SUCCESS: Database collation issues fixed")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing database collation: {e}", 'ERROR')

def create_missing_django_tables():
    """Create missing Django system tables"""
    log_message("Creating missing Django system tables...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check if user_permissions table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'user_permissions'
            """)
            
            if cursor.fetchone()[0] == 0:
                log_message("Creating missing user_permissions table...")
                cursor.execute("""
                    CREATE TABLE user_permissions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        user_id CHAR(32) NOT NULL,
                        permission_id INT NOT NULL,
                        created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                        INDEX idx_user_permissions_user_id (user_id),
                        INDEX idx_user_permissions_permission_id (permission_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("SUCCESS: Created user_permissions table")
            else:
                log_message("SUCCESS: user_permissions table already exists")
            
            # Check if auth_permission table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'auth_permission'
            """)
            
            if cursor.fetchone()[0] == 0:
                log_message("Creating missing auth_permission table...")
                cursor.execute("""
                    CREATE TABLE auth_permission (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        content_type_id INT NOT NULL,
                        codename VARCHAR(100) NOT NULL,
                        UNIQUE KEY auth_permission_content_type_id_codename (content_type_id, codename)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("SUCCESS: Created auth_permission table")
            else:
                log_message("SUCCESS: auth_permission table already exists")
            
            # Check if django_content_type table exists
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'django_content_type'
            """)
            
            if cursor.fetchone()[0] == 0:
                log_message("Creating missing django_content_type table...")
                cursor.execute("""
                    CREATE TABLE django_content_type (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        app_label VARCHAR(100) NOT NULL,
                        model VARCHAR(100) NOT NULL,
                        UNIQUE KEY django_content_type_app_label_model (app_label, model)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("SUCCESS: Created django_content_type table")
            else:
                log_message("SUCCESS: django_content_type table already exists")
            
            # Check if users_customuser_user_permissions table exists (many-to-many table)
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users_customuser_user_permissions'
            """)
            
            if cursor.fetchone()[0] == 0:
                log_message("Creating missing users_customuser_user_permissions table...")
                cursor.execute("""
                    CREATE TABLE users_customuser_user_permissions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        customuser_id CHAR(32) NOT NULL,
                        permission_id INT NOT NULL,
                        UNIQUE KEY users_customuser_user_permissions_customuser_id_permission_id (customuser_id, permission_id),
                        INDEX idx_users_customuser_user_permissions_customuser_id (customuser_id),
                        INDEX idx_users_customuser_user_permissions_permission_id (permission_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("SUCCESS: Created users_customuser_user_permissions table")
            else:
                log_message("SUCCESS: users_customuser_user_permissions table already exists")
            
            # Check if users_customuser_groups table exists (many-to-many table)
            cursor.execute("""
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users_customuser_groups'
            """)
            
            if cursor.fetchone()[0] == 0:
                log_message("Creating missing users_customuser_groups table...")
                cursor.execute("""
                    CREATE TABLE users_customuser_groups (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        customuser_id CHAR(32) NOT NULL,
                        group_id INT NOT NULL,
                        UNIQUE KEY users_customuser_groups_customuser_id_group_id (customuser_id, group_id),
                        INDEX idx_users_customuser_groups_customuser_id (customuser_id),
                        INDEX idx_users_customuser_groups_group_id (group_id)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """)
                log_message("SUCCESS: Created users_customuser_groups table")
            else:
                log_message("SUCCESS: users_customuser_groups table already exists")
        
        log_message("SUCCESS: All missing Django system tables created")
        
    except Exception as e:
        log_message(f"ERROR: Error creating missing Django tables: {e}", 'ERROR')

def ensure_database_integrity():
    """Ensure all database tables and columns exist properly"""
    log_message("Ensuring database integrity...")
    
    try:
        # First fix collation issues
        fix_database_collation_issues()
        
        # Create missing Django system tables
        create_missing_django_tables()
        
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check and fix loan_statements table - this is the critical fix for user deletion
            log_message("Checking loan_statements table structure...")
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'loan_statements'
            """)
            loan_statements_columns = [row[0] for row in cursor.fetchall()]
            log_message(f"loan_statements columns: {loan_statements_columns}")
            
            # Ensure borrower_id field exists in loan_statements
            if 'borrower_id' not in loan_statements_columns:
                if 'user_id' in loan_statements_columns:
                    log_message("Adding borrower_id as alias to user_id in loan_statements...")
                    cursor.execute("""
                        ALTER TABLE loan_statements 
                        ADD COLUMN borrower_id CHAR(32) NULL
                    """)
                    # Copy data from user_id to borrower_id
                    cursor.execute("""
                        UPDATE loan_statements 
                        SET borrower_id = user_id 
                        WHERE user_id IS NOT NULL
                    """)
                    log_message("SUCCESS: Added borrower_id field to loan_statements")
                else:
                    log_message("Adding borrower_id field to loan_statements...")
                    cursor.execute("""
                        ALTER TABLE loan_statements 
                        ADD COLUMN borrower_id CHAR(32) NULL,
                        ADD INDEX idx_loan_statements_borrower (borrower_id)
                    """)
                    log_message("SUCCESS: Added borrower_id field to loan_statements")
            
            # Check and fix receipts table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts'
            """)
            receipts_columns = [row[0] for row in cursor.fetchall()]
            
            if 'borrower_id' not in receipts_columns:
                if 'user_id' in receipts_columns:
                    log_message("Adding borrower_id as alias to user_id in receipts...")
                    cursor.execute("""
                        ALTER TABLE receipts 
                        ADD COLUMN borrower_id CHAR(32) NULL
                    """)
                    cursor.execute("""
                        UPDATE receipts 
                        SET borrower_id = user_id 
                        WHERE user_id IS NOT NULL
                    """)
                    log_message("SUCCESS: Added borrower_id field to receipts")
                else:
                    log_message("Adding borrower_id field to receipts...")
                    cursor.execute("""
                        ALTER TABLE receipts 
                        ADD COLUMN borrower_id CHAR(32) NULL,
                        ADD INDEX idx_receipts_borrower (borrower_id)
                    """)
                    log_message("SUCCESS: Added borrower_id field to receipts")
            
            # Check and fix offer_letters table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'offer_letters'
            """)
            offer_letters_columns = [row[0] for row in cursor.fetchall()]
            
            if 'borrower_id' not in offer_letters_columns:
                if 'user_id' in offer_letters_columns:
                    log_message("Adding borrower_id as alias to user_id in offer_letters...")
                    cursor.execute("""
                        ALTER TABLE offer_letters 
                        ADD COLUMN borrower_id CHAR(32) NULL
                    """)
                    cursor.execute("""
                        UPDATE offer_letters 
                        SET borrower_id = user_id 
                        WHERE user_id IS NOT NULL
                    """)
                    log_message("SUCCESS: Added borrower_id field to offer_letters")
                else:
                    log_message("Adding borrower_id field to offer_letters...")
                    cursor.execute("""
                        ALTER TABLE offer_letters 
                        ADD COLUMN borrower_id CHAR(32) NULL,
                        ADD INDEX idx_offer_letters_borrower (borrower_id)
                    """)
                    log_message("SUCCESS: Added borrower_id field to offer_letters")
            
            # Check and fix loan_scoring table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'loan_scoring' 
                AND COLUMN_NAME = 'user_id'
            """)
            if not cursor.fetchone():
                log_message("Adding user_id field to loan_scoring table...")
                cursor.execute("""
                    ALTER TABLE loan_scoring 
                    ADD COLUMN user_id CHAR(32) NULL,
                    ADD INDEX idx_loan_scoring_user_id (user_id)
                """)
                log_message("SUCCESS: Added user_id to loan_scoring table")
            
            # Check and fix utils_document table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'utils_document' 
                AND COLUMN_NAME = 'uploaded_by_id'
            """)
            if not cursor.fetchone():
                log_message("Adding uploaded_by_id field to utils_document table...")
                cursor.execute("""
                    ALTER TABLE utils_document 
                    ADD COLUMN uploaded_by_id CHAR(32) NULL,
                    ADD INDEX idx_utils_document_uploaded_by (uploaded_by_id)
                """)
                log_message("SUCCESS: Added uploaded_by_id to utils_document table")
            
            # Check and fix utils_notification table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'utils_notification' 
                AND COLUMN_NAME = 'user_id'
            """)
            if not cursor.fetchone():
                log_message("Adding user_id field to utils_notification table...")
                cursor.execute("""
                    ALTER TABLE utils_notification 
                    ADD COLUMN user_id CHAR(32) NULL,
                    ADD INDEX idx_utils_notification_user_id (user_id)
                """)
                log_message("SUCCESS: Added user_id to utils_notification table")
            
            # Check and fix utils_documentshare table
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'utils_documentshare' 
                AND COLUMN_NAME = 'shared_with_id'
            """)
            if not cursor.fetchone():
                log_message("Adding shared_with_id field to utils_documentshare table...")
                cursor.execute("""
                    ALTER TABLE utils_documentshare 
                    ADD COLUMN shared_with_id CHAR(32) NULL,
                    ADD INDEX idx_utils_documentshare_shared_with (shared_with_id)
                """)
                log_message("SUCCESS: Added shared_with_id to utils_documentshare table")
            
            # Check and fix utils_documentshare table for document_id
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'utils_documentshare' 
                AND COLUMN_NAME = 'document_id'
            """)
            if not cursor.fetchone():
                log_message("Adding document_id field to utils_documentshare table...")
                cursor.execute("""
                    ALTER TABLE utils_documentshare 
                    ADD COLUMN document_id INTEGER NULL,
                    ADD INDEX idx_utils_documentshare_document (document_id)
                """)
                log_message("SUCCESS: Added document_id to utils_documentshare table")
            
            # Check and fix users table for last_login_at field
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users' 
                AND COLUMN_NAME = 'last_login_at'
            """)
            if not cursor.fetchone():
                log_message("Adding last_login_at field to users table...")
                cursor.execute("""
                    ALTER TABLE users 
                    ADD COLUMN last_login_at DATETIME(6) NULL
                """)
                log_message("SUCCESS: Added last_login_at to users table")
            
            # Check and fix users table for is_phone_verified and is_email_verified fields
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users' 
                AND COLUMN_NAME = 'is_phone_verified'
            """)
            if not cursor.fetchone():
                log_message("Adding is_phone_verified field to users table...")
                cursor.execute("""
                    ALTER TABLE users 
                    ADD COLUMN is_phone_verified BOOLEAN DEFAULT FALSE,
                    ADD COLUMN is_email_verified BOOLEAN DEFAULT FALSE
                """)
                log_message("SUCCESS: Added verification fields to users table")
            
            # Check and fix users table for address field
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users' 
                AND COLUMN_NAME = 'address'
            """)
            if not cursor.fetchone():
                log_message("Adding address field to users table...")
                cursor.execute("""
                    ALTER TABLE users 
                    ADD COLUMN address TEXT NULL
                """)
                log_message("SUCCESS: Added address field to users table")
            
            # Check and fix users table for county field
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'users' 
                AND COLUMN_NAME = 'county'
            """)
            if not cursor.fetchone():
                log_message("Adding county field to users table...")
                cursor.execute("""
                    ALTER TABLE users 
                    ADD COLUMN county VARCHAR(100) NULL
                """)
                log_message("SUCCESS: Added county field to users table")
        
        log_message("SUCCESS: Database integrity check completed with borrower_id fixes")
        
        # Populate Django system tables with required data
        populate_django_system_tables()
                
    except Exception as e:
        log_message(f"ERROR: Error ensuring database integrity: {e}", 'ERROR')

def populate_django_system_tables():
    """Populate Django system tables with required data"""
    log_message("Populating Django system tables...")
    
    try:
        from django.contrib.contenttypes.models import ContentType
        from django.contrib.auth.models import Permission
        from django.contrib.auth import get_user_model
        
        User = get_user_model()
        
        # Create content types for all models
        log_message("Creating content types...")
        
        # Define all the models that need content types
        model_definitions = [
            ('users', 'customuser'),
            ('loans', 'loan'),
            ('loans', 'loanproduct'),
            ('loans', 'rolloverrequest'),
            ('reports', 'loanscoring'),
            ('utils', 'systemsetting'),
            ('utils', 'notification'),
            ('utils', 'document'),
            ('utils', 'documentshare'),
            ('utils', 'loanstatement'),
            ('utils', 'receipt'),
            ('utils', 'offerletter'),
        ]
        
        created_content_types = 0
        for app_label, model_name in model_definitions:
            content_type, created = ContentType.objects.get_or_create(
                app_label=app_label,
                model=model_name,
                defaults={'name': model_name}
            )
            if created:
                created_content_types += 1
                log_message(f"Created content type: {app_label}.{model_name}")
        
        log_message(f"SUCCESS: Created {created_content_types} content types")
        
        # Create basic permissions for each content type
        log_message("Creating basic permissions...")
        
        basic_permissions = ['add', 'change', 'delete', 'view']
        created_permissions = 0
        
        for content_type in ContentType.objects.all():
            for perm_type in basic_permissions:
                codename = f"{perm_type}_{content_type.model}"
                name = f"Can {perm_type} {content_type.model}"
                
                permission, created = Permission.objects.get_or_create(
                    codename=codename,
                    content_type=content_type,
                    defaults={'name': name}
                )
                if created:
                    created_permissions += 1
        
        log_message(f"SUCCESS: Created {created_permissions} permissions")
        
        # Ensure admin user has all permissions
        try:
            admin_user = User.objects.get(email='admin@branchbusinessadvance.com')
            admin_user.is_superuser = True
            admin_user.is_staff = True
            admin_user.save()
            log_message("SUCCESS: Admin user permissions updated")
        except User.DoesNotExist:
            log_message("WARNING: Admin user not found for permission assignment", 'WARNING')
        
        log_message("SUCCESS: Django system tables populated")
        
    except Exception as e:
        log_message(f"ERROR: Error populating Django system tables: {e}", 'ERROR')

def ensure_table_indexes():
    """Ensure proper indexes exist for performance"""
    print("\n📊 Ensuring database indexes...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Add indexes for better performance
            indexes_to_add = [
                ("users", "idx_users_role", "role"),
                ("users", "idx_users_status", "status"),
                ("users", "idx_users_phone", "phone_number"),
                ("users", "idx_users_email", "email"),
                ("loan_scoring", "idx_loan_scoring_user", "user_id"),
                ("utils_document", "idx_document_uploaded_by", "uploaded_by_id"),
                ("utils_document", "idx_document_type", "document_type"),
                ("utils_notification", "idx_notification_user", "user_id"),
                ("utils_notification", "idx_notification_read", "read_at"),
                ("utils_documentshare", "idx_documentshare_shared_with", "shared_with_id"),
                ("utils_documentshare", "idx_documentshare_document", "document_id"),
            ]
            
            for table, index_name, column in indexes_to_add:
                try:
                    cursor.execute(f"""
                        CREATE INDEX {index_name} ON {table} ({column})
                    """)
                    print(f"SUCCESS: Added index {index_name} to {table}")
                except Exception as e:
                    if "Duplicate key name" in str(e):
                        print(f"SUCCESS: Index {index_name} already exists on {table}")
                    else:
                        print(f"WARNING: Could not add index {index_name}: {e}")
                        
    except Exception as e:
        print(f"ERROR: Error ensuring database indexes: {e}")

def create_document_upload_directories():
    """Create directories for document uploads"""
    log_message("Creating document upload directories...")
    
    directories = [
        "media/kyc/id_documents",
        "media/kyc/selfies",
        "media/kyc/utility_bills",
        "media/kyc/bank_statements", 
        "media/kyc/business_licenses",
        "media/kyc/tax_certificates",
        "media/kyc/logbooks",
        "media/kyc/title_deeds", 
        "media/kyc/signatures",
        "media/profile_images",
        "media/documents",
        "media/receipts",
        "media/reports"
    ]
    
    for directory in directories:
        os.makedirs(directory, exist_ok=True)
        log_message(f"SUCCESS: Created {directory}")
    
    log_message("SUCCESS: All document upload directories created")

def create_default_avatars():
    """Create default avatar images for different user roles"""
    print("\n🖼️ Creating default avatar images...")
    
    try:
        from PIL import Image, ImageDraw, ImageFont
        
        # Ensure directory exists
        os.makedirs('static/images/avatars', exist_ok=True)
        
        # Role-specific avatars with colors
        avatars = [
            ('AD', '#00308a', 'admin-avatar.png'),           # Primary blue
            ('TL', '#1e40af', 'team-leader-avatar.png'),     # Blue
            ('LO', '#86c662', 'loan-officer-avatar.png'),    # Secondary green
            ('SE', '#f59e0b', 'secretary-avatar.png'),       # Orange
            ('AU', '#6b7280', 'auditor-avatar.png'),         # Gray
            ('BO', '#3b82f6', 'borrower-avatar.png'),        # Light blue
            ('U', '#9ca3af', 'default-avatar.png'),          # Default gray
        ]
        
        for initials, color, filename in avatars:
            # Create a 300x300 image with the specified color
            img = Image.new('RGB', (300, 300), color)
            draw = ImageDraw.Draw(img)
            
            # Try to use a nice font, fallback to default
            try:
                font = ImageFont.truetype("arial.ttf", 120)
            except:
                try:
                    font = ImageFont.truetype("/System/Library/Fonts/Arial.ttf", 120)
                except:
                    font = ImageFont.load_default()
            
            # Get text size and position
            bbox = draw.textbbox((0, 0), initials, font=font)
            text_width = bbox[2] - bbox[0]
            text_height = bbox[3] - bbox[1]
            
            # Center the text
            x = (300 - text_width) // 2
            y = (300 - text_height) // 2
            
            # Draw the text
            draw.text((x, y), initials, fill='white', font=font)
            
            # Save the image
            img.save(f'static/images/avatars/{filename}')
            print(f"SUCCESS: Created {filename}")
        
        print("SUCCESS: All default avatars created successfully!")
        
    except ImportError:
        print("WARNING: PIL not available, skipping avatar creation")
    except Exception as e:
        print(f"WARNING: Error creating avatars: {e}")

def setup_penalty_system():
    """Setup the penalty system with individual product settings"""
    print("\n💰 Setting up penalty system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Individual penalty settings for each product
        penalty_settings = [
            ('boost_late_payment_penalty', '5.0', 'loan', 'Daily penalty rate for late Boost loan payments (%)'),
            ('boost_plus_late_payment_penalty', '5.0', 'loan', 'Daily penalty rate for late Boost Plus loan payments (%)'),
            ('mwamba_late_payment_penalty', '5.0', 'loan', 'Daily penalty rate for late Mwamba loan payments (%)'),
            ('imara_late_payment_penalty', '3.0', 'loan', 'Daily penalty rate for late Imara loan payments (%)'),
        ]
        
        created_count = 0
        for key, value, category, description in penalty_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        print(f"SUCCESS: Created {created_count} penalty system settings")
        print("SUCCESS: Penalty system configured with individual product rates")
        
    except Exception as e:
        print(f"ERROR: Error setting up penalty system: {e}")


def setup_reports_environment():
    """Setup reports environment and ensure chart generation works properly"""
    print("\n📊 Setting up reports environment...")
    
    try:
        # Create reports cache directory
        reports_cache_dir = os.path.join(os.getcwd(), 'reports_cache')
        os.makedirs(reports_cache_dir, exist_ok=True)
        print(f"SUCCESS: Created reports cache directory: {reports_cache_dir}")
        
        # Set matplotlib backend to Agg for server environments
        import matplotlib
        matplotlib.use('Agg')
        print("SUCCESS: Configured matplotlib backend for server environment")
        
        # Test chart generation
        try:
            import matplotlib.pyplot as plt
            import numpy as np
            
            # Create a simple test chart
            fig, ax = plt.subplots(figsize=(6, 4))
            x = np.linspace(0, 10, 100)
            y = np.sin(x)
            ax.plot(x, y)
            ax.set_title('Test Chart')
            ax.set_xlabel('X')
            ax.set_ylabel('Y')
            
            # Save to buffer to test
            buffer = io.BytesIO()
            plt.savefig(buffer, format='png', dpi=100, bbox_inches='tight')
            buffer.seek(0)
            plt.close()
            
            print("SUCCESS: Chart generation test successful")
            
        except Exception as e:
            print(f"WARNING: Chart generation test failed: {e}")
            print("WARNING: Reports with charts may not work properly")
        
        # Ensure seaborn is available
        try:
            import seaborn as sns
            print("SUCCESS: Seaborn is available for enhanced chart styling")
        except ImportError:
            print("WARNING: Seaborn not available - charts will use basic styling")
        
    except Exception as e:
        print(f"ERROR: Error setting up reports environment: {e}")

def setup_credit_score_system():
    """Setup the credit score calculation system"""
    print("\n📊 Setting up credit score system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Credit score system settings
        credit_score_settings = [
            ('credit_score_enabled', 'true', 'system', 'Enable credit score calculation system'),
            ('credit_score_auto_update', 'true', 'system', 'Automatically update credit scores'),
            ('credit_score_update_frequency', '24', 'system', 'Credit score update frequency in hours'),
            ('credit_score_min_value', '300', 'system', 'Minimum credit score value'),
            ('credit_score_max_value', '850', 'system', 'Maximum credit score value'),
            ('credit_score_default_value', '500', 'system', 'Default credit score for new users'),
            
            # Credit score calculation weights
            ('credit_score_payment_history_weight', '35', 'credit_score', 'Payment history weight percentage'),
            ('credit_score_credit_utilization_weight', '30', 'credit_score', 'Credit utilization weight percentage'),
            ('credit_score_credit_length_weight', '15', 'credit_score', 'Credit history length weight percentage'),
            ('credit_score_credit_mix_weight', '10', 'credit_score', 'Credit mix weight percentage'),
            ('credit_score_new_credit_weight', '10', 'credit_score', 'New credit inquiries weight percentage'),
        ]
        
        created_count = 0
        for key, value, category, description in credit_score_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        print(f"SUCCESS: Created {created_count} credit score system settings")
        print("SUCCESS: Credit score calculation system configured")
        
    except Exception as e:
        print(f"ERROR: Error setting up credit score system: {e}")

def setup_receipt_system():
    """Setup the enhanced receipt system"""
    print("\n🧾 Setting up enhanced receipt system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Receipt system settings
        receipt_settings = [
            ('receipt_numbering_enabled', 'true', 'receipt', 'Enable automatic receipt numbering'),
            ('receipt_prefix', 'BBA', 'receipt', 'Receipt number prefix'),
            ('receipt_number_length', '6', 'receipt', 'Receipt number length (excluding prefix)'),
            ('receipt_auto_generate', 'true', 'receipt', 'Automatically generate receipts for payments'),
            ('receipt_email_enabled', 'true', 'receipt', 'Email receipts to clients'),
            ('receipt_sms_enabled', 'false', 'receipt', 'SMS receipt notifications'),
            ('receipt_duplicate_prevention', 'true', 'receipt', 'Prevent duplicate receipt generation'),
            ('receipt_void_enabled', 'true', 'receipt', 'Allow receipt voiding'),
            ('receipt_reprint_enabled', 'true', 'receipt', 'Allow receipt reprinting'),
            ('receipt_audit_trail', 'true', 'receipt', 'Maintain receipt audit trail'),
        ]
        
        created_count = 0
        for key, value, category, description in receipt_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        print(f"SUCCESS: Created {created_count} receipt system settings")
        print("SUCCESS: Enhanced receipt system configured")
        
    except Exception as e:
        print(f"ERROR: Error setting up receipt system: {e}")

def setup_rollover_collections_system():
    """Setup the rollover and collections system"""
    print("\n🔄 Setting up rollover and collections system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Rollover and collections settings
        rollover_settings = [
            ('rollover_enabled', 'true', 'rollover', 'Enable loan rollover functionality'),
            ('rollover_auto_approval', 'false', 'rollover', 'Automatically approve rollovers'),
            ('rollover_approval_threshold', '50000', 'rollover', 'Amount threshold requiring approval'),
            ('rollover_notification_days', '7', 'rollover', 'Days before due date to send rollover notifications'),
            
            # Collections settings
            ('collections_enabled', 'true', 'collections', 'Enable collections management'),
            ('collections_grace_period', '3', 'collections', 'Grace period in days before collections'),
            ('collections_auto_assignment', 'true', 'collections', 'Automatically assign overdue loans to collections'),
            ('collections_escalation_days', '30', 'collections', 'Days before escalating collections'),
            ('collections_legal_threshold', '100000', 'collections', 'Amount threshold for legal action'),
        ]
        
        created_count = 0
        for key, value, category, description in rollover_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        print(f"SUCCESS: Created {created_count} rollover and collections settings")
        print("SUCCESS: Rollover and collections system configured")
        
    except Exception as e:
        print(f"ERROR: Error setting up rollover and collections system: {e}")

def setup_blacklist_system():
    """Setup the blacklist and status management system"""
    print("\n🚫 Setting up blacklist and status management system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Blacklist system settings
        blacklist_settings = [
            ('blacklist_enabled', 'true', 'blacklist', 'Enable blacklist functionality'),
            ('blacklist_auto_add', 'true', 'blacklist', 'Automatically add defaulters to blacklist'),
            ('blacklist_default_days', '90', 'blacklist', 'Days overdue before auto-blacklisting'),
            ('blacklist_removal_approval', 'true', 'blacklist', 'Require approval for blacklist removal'),
            ('blacklist_check_on_application', 'true', 'blacklist', 'Check blacklist status on new applications'),
            
            # Status management settings
            ('status_auto_update', 'true', 'status', 'Automatically update loan statuses'),
            ('status_overdue_days', '1', 'status', 'Days past due date to mark as overdue'),
            ('status_default_days', '30', 'status', 'Days overdue to mark as defaulted'),
            ('status_notification_enabled', 'true', 'status', 'Send notifications on status changes'),
        ]
        
        created_count = 0
        for key, value, category, description in blacklist_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        print(f"SUCCESS: Created {created_count} blacklist and status management settings")
        print("SUCCESS: Blacklist and status management system configured")
        
    except Exception as e:
        print(f"ERROR: Error setting up blacklist system: {e}")

def setup_portfolio_management():
    """Setup the portfolio management system"""
    log_message("Setting up portfolio management system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Portfolio management settings
        portfolio_settings = [
            ('portfolio_analytics_enabled', 'true', 'portfolio', 'Enable portfolio analytics'),
            ('portfolio_auto_assignment', 'true', 'portfolio', 'Automatically assign clients to portfolio managers'),
            ('portfolio_max_clients', '100', 'portfolio', 'Maximum clients per portfolio manager'),
            ('portfolio_performance_tracking', 'true', 'portfolio', 'Track portfolio manager performance'),
            ('portfolio_risk_monitoring', 'true', 'portfolio', 'Enable portfolio risk monitoring'),
            ('portfolio_reporting_frequency', 'weekly', 'portfolio', 'Portfolio reporting frequency'),
            ('portfolio_alert_threshold', '5', 'portfolio', 'Portfolio risk alert threshold percentage'),
        ]
        
        created_count = 0
        for key, value, category, description in portfolio_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        log_message(f"SUCCESS: Created {created_count} portfolio management settings")
        log_message("SUCCESS: Portfolio management system configured")
        
    except Exception as e:
        log_message(f"ERROR: Error setting up portfolio management system: {e}", 'ERROR')

def fix_reports_collation_queries():
    """Fix reports queries to handle collation issues"""
    log_message("Fixing reports queries for collation compatibility...")
    
    try:
        reports_views_file = 'reports/views.py'
        if not os.path.exists(reports_views_file):
            log_message("WARNING: reports/views.py not found", 'WARNING')
            return
        
        with open(reports_views_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        fixes_applied = []
        
        # Fix 1: Add COLLATE clause to string comparisons
        if 'registration_fees_report' in content:
            # Add collation-safe queries
            collation_fixes = [
                # Fix string comparisons in filters
                ("filter(role='borrower')", "filter(role='borrower').extra(where=[\"role COLLATE utf8mb4_unicode_ci = 'borrower'\"])"),
                ("filter(status='active')", "filter(status='active').extra(where=[\"status COLLATE utf8mb4_unicode_ci = 'active'\"])"),
                ("exclude(role='admin')", "exclude(role='admin').extra(where=[\"role COLLATE utf8mb4_unicode_ci != 'admin'\"])"),
            ]
            
            for old_query, new_query in collation_fixes:
                if old_query in content and new_query not in content:
                    content = content.replace(old_query, new_query)
                    fixes_applied.append(f"Fixed collation for: {old_query}")
        
        # Fix 2: Add database-level collation handling
        if 'def registration_fees_report(' in content:
            # Add collation handling at the beginning of the function
            collation_handler = '''
    # Handle database collation issues
    from django.db import connection
    with connection.cursor() as cursor:
        cursor.execute("SET collation_connection = 'utf8mb4_unicode_ci'")
'''
            
            if 'SET collation_connection' not in content:
                # Find the function and add collation handling
                function_start = content.find('def registration_fees_report(')
                if function_start != -1:
                    # Find the end of the function signature
                    function_line_end = content.find('\n', function_start)
                    if function_line_end != -1:
                        # Insert the collation handler after the function signature
                        content = (content[:function_line_end + 1] + 
                                 collation_handler + 
                                 content[function_line_end + 1:])
                        fixes_applied.append("Added collation handling to registration_fees_report")
        
        # Fix 3: Use raw SQL for problematic queries
        if 'CustomUser.objects.filter(' in content and 'registration_fee' in content:
            raw_sql_fix = '''
    # Use raw SQL to avoid collation issues
    from django.db import connection
    
    def get_users_with_collation_safe(role_filter=None, status_filter=None):
        with connection.cursor() as cursor:
            sql = """
                SELECT * FROM users 
                WHERE role COLLATE utf8mb4_unicode_ci = %s
                AND status COLLATE utf8mb4_unicode_ci = %s
            """
            cursor.execute(sql, [role_filter or 'borrower', status_filter or 'active'])
            return cursor.fetchall()
'''
            
            if 'get_users_with_collation_safe' not in content:
                # Add the helper function at the beginning of the file
                import_section = content.split('\n\n')[0]
                rest_of_file = '\n\n'.join(content.split('\n\n')[1:])
                content = import_section + '\n\n' + raw_sql_fix + '\n\n' + rest_of_file
                fixes_applied.append("Added collation-safe query helper function")
        
        # Apply fixes if any were made
        if fixes_applied:
            with open(reports_views_file, 'w', encoding='utf-8') as f:
                f.write(content)
            log_message(f"SUCCESS: Applied {len(fixes_applied)} collation fixes to reports:")
            for fix in fixes_applied:
                log_message(f"   - {fix}")
        else:
            log_message("SUCCESS: Reports queries already have collation fixes")
        
    except Exception as e:
        log_message(f"ERROR: Failed to fix reports collation queries: {e}", 'ERROR')

def fix_reports_dashboard():
    """Fix Reports & Statements Dashboard UI and functionality issues"""
    log_message("Fixing Reports & Statements Dashboard...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        # Fix collation issues in reports queries
        fix_reports_collation_queries()
        
        # Check if dashboard template exists and needs fixing
        dashboard_template = 'templates/reports/dashboard_fixed.html'
        
        if os.path.exists(dashboard_template):
            log_message("Reports dashboard template found, checking for fixes...")
            
            with open(dashboard_template, 'r', encoding='utf-8') as f:
                content = f.read()
            
            # Check if the UI fix has been applied (removing excessive margin)
            if 'lg:ml-72' in content:
                log_message("Applying UI layout fix - removing excessive left margin...")
                content = content.replace('lg:ml-72', '')
                
                with open(dashboard_template, 'w', encoding='utf-8') as f:
                    f.write(content)
                log_message("SUCCESS: Fixed dashboard UI layout")
            else:
                log_message("SUCCESS: Dashboard UI layout already fixed")
        
        # Fix reports views for proper data calculation
        reports_views_file = 'reports/views.py'
        if os.path.exists(reports_views_file):
            log_message("Checking reports views for data calculation fixes...")
            
            with open(reports_views_file, 'r', encoding='utf-8') as f:
                content = f.read()
            
            # Check if the mixed types fix has been applied
            if 'output_field=DecimalField()' not in content and 'F(\'total_amount\') - F(\'amount_paid\')' in content:
                log_message("Applying database query fix for mixed types...")
                content = content.replace(
                    'Sum(F(\'total_amount\') - F(\'amount_paid\'))',
                    'Sum(F(\'total_amount\') - F(\'amount_paid\'), output_field=DecimalField())'
                )
                
                # Add DecimalField import if not present
                if 'from django.db.models import DecimalField' not in content:
                    content = content.replace(
                        'from django.db.models import Count, Sum, Avg, Q, F, Value',
                        'from django.db.models import Count, Sum, Avg, Q, F, Value, DecimalField'
                    )
                
                with open(reports_views_file, 'w', encoding='utf-8') as f:
                    f.write(content)
                log_message("SUCCESS: Fixed reports views data calculations")
            else:
                log_message("SUCCESS: Reports views already fixed")
        
        log_message("SUCCESS: Reports & Statements Dashboard fixes completed")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing reports dashboard: {e}", 'ERROR')

def setup_enhanced_user_management():
    """Setup enhanced user management with role-based permissions"""
    log_message("Setting up enhanced user management system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from users.models import RolePermission
        from utils.models import SystemSetting
        
        # Enhanced user management settings
        user_mgmt_settings = [
            ('user_registration_enabled', 'true', 'user_management', 'Allow new user registration'),
            ('user_email_verification_required', 'true', 'user_management', 'Require email verification for new users'),
            ('user_phone_verification_required', 'true', 'user_management', 'Require phone verification for new users'),
            ('user_profile_image_enabled', 'true', 'user_management', 'Enable profile image uploads'),
            ('user_profile_image_max_size', '5', 'user_management', 'Maximum profile image size in MB'),
            ('user_session_timeout', '3600', 'user_management', 'User session timeout in seconds'),
            ('user_password_reset_enabled', 'true', 'user_management', 'Enable password reset functionality'),
            ('user_account_lockout_enabled', 'true', 'user_management', 'Enable account lockout after failed attempts'),
            ('user_max_login_attempts', '5', 'user_management', 'Maximum login attempts before lockout'),
            ('user_lockout_duration', '1800', 'user_management', 'Account lockout duration in seconds'),
        ]
        
        created_count = 0
        for key, value, category, description in user_mgmt_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        log_message(f"SUCCESS: Created {created_count} user management settings")
        
        # Verify role permissions exist
        total_permissions = RolePermission.objects.count()
        if total_permissions > 0:
            log_message(f"SUCCESS: Role-based permissions system active ({total_permissions} permissions)")
        else:
            log_message("WARNING: No role permissions found, will be created by setup_role_permissions()")
        
        log_message("SUCCESS: Enhanced user management system configured")
        
    except Exception as e:
        log_message(f"ERROR: Error setting up enhanced user management: {e}", 'ERROR')

def setup_staff_management_system():
    """Setup the comprehensive staff management system"""
    log_message("Setting up staff management system...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from utils.models import SystemSetting
        
        # Staff management settings
        staff_settings = [
            ('staff_dashboard_enabled', 'true', 'staff_management', 'Enable staff dashboard'),
            ('staff_profile_management', 'true', 'staff_management', 'Allow staff profile management'),
            ('staff_permission_inheritance', 'true', 'staff_management', 'Enable permission inheritance'),
            ('staff_activity_logging', 'true', 'staff_management', 'Log staff activities'),
            ('staff_performance_tracking', 'true', 'staff_management', 'Track staff performance'),
            ('staff_workload_balancing', 'true', 'staff_management', 'Enable workload balancing'),
            ('staff_notification_system', 'true', 'staff_management', 'Enable staff notifications'),
            ('staff_reporting_enabled', 'true', 'staff_management', 'Enable staff reporting'),
        ]
        
        created_count = 0
        for key, value, category, description in staff_settings:
            setting, created = SystemSetting.objects.get_or_create(
                key=key,
                defaults={
                    'value': value,
                    'category': category,
                    'description': description
                }
            )
            if created:
                created_count += 1
        
        log_message(f"SUCCESS: Created {created_count} staff management settings")
        log_message("SUCCESS: Staff management system configured")
        
    except Exception as e:
        log_message(f"ERROR: Error setting up staff management system: {e}", 'ERROR')

def ensure_template_directories():
    """Ensure all template directories exist"""
    log_message("Ensuring template directories exist...")
    
    template_dirs = [
        'templates/users',
        'templates/users/partials',
        'templates/loans',
        'templates/reports',
        'templates/utils',
        'templates/base',
        'templates/components',
    ]
    
    for template_dir in template_dirs:
        os.makedirs(template_dir, exist_ok=True)
        log_message(f"SUCCESS: Ensured {template_dir} exists")
    
    log_message("SUCCESS: All template directories created")

def create_safe_user_deletion_function():
    """Create a safe user deletion function that handles all database schema variations"""
    log_message("Creating safe user deletion function...")
    
    safe_deletion_code = '''
def safe_delete_user_records(user):
    """
    Safely delete all user-related records with proper error handling
    This function handles different database schema variations
    """
    from django.db import connection, transaction
    from django.contrib import messages
    import logging
    
    logger = logging.getLogger(__name__)
    deletion_log = []
    
    try:
        with transaction.atomic():
            # Get all table names and their columns
            with connection.cursor() as cursor:
                # Check what tables exist and their user reference columns
                tables_to_check = [
                    ('loan_statements', ['borrower_id', 'user_id', 'client_id']),
                    ('receipts', ['borrower_id', 'user_id', 'client_id']),
                    ('offer_letters', ['borrower_id', 'user_id', 'client_id']),
                    ('rollover_requests', ['borrower_id', 'user_id', 'client_id']),
                    ('loan_scoring', ['user_id', 'borrower_id', 'client_id']),
                    ('utils_notification', ['user_id', 'recipient_id']),
                    ('utils_document', ['uploaded_by_id', 'user_id']),
                    ('utils_documentshare', ['shared_with_id', 'user_id']),
                    ('loans', ['borrower_id', 'user_id', 'client_id']),
                ]
                
                for table_name, possible_columns in tables_to_check:
                    try:
                        # Check if table exists
                        cursor.execute(f"""
                            SELECT COUNT(*) 
                            FROM INFORMATION_SCHEMA.TABLES 
                            WHERE TABLE_SCHEMA = DATABASE() 
                            AND TABLE_NAME = '{table_name}'
                        """)
                        
                        if cursor.fetchone()[0] == 0:
                            deletion_log.append(f"Table {table_name} does not exist - skipping")
                            continue
                        
                        # Find which column exists
                        user_column = None
                        for col in possible_columns:
                            cursor.execute(f"""
                                SELECT COUNT(*) 
                                FROM INFORMATION_SCHEMA.COLUMNS 
                                WHERE TABLE_SCHEMA = DATABASE() 
                                AND TABLE_NAME = '{table_name}' 
                                AND COLUMN_NAME = '{col}'
                            """)
                            
                            if cursor.fetchone()[0] > 0:
                                user_column = col
                                break
                        
                        if user_column:
                            # Delete records using the found column
                            cursor.execute(f"""
                                DELETE FROM {table_name} 
                                WHERE {user_column} = %s
                            """, [str(user.id)])
                            
                            deleted_count = cursor.rowcount
                            deletion_log.append(f"Deleted {deleted_count} records from {table_name} using {user_column}")
                        else:
                            deletion_log.append(f"No user reference column found in {table_name}")
                            
                    except Exception as e:
                        deletion_log.append(f"Error processing {table_name}: {str(e)}")
                        logger.error(f"Error deleting from {table_name}: {e}")
                
                # Finally delete the user
                user.delete()
                deletion_log.append(f"Successfully deleted user: {user.email}")
                
        return True, deletion_log
        
    except Exception as e:
        logger.error(f"Critical error during user deletion: {e}")
        return False, [f"Critical error: {str(e)}"] + deletion_log
'''
    
    try:
        # Check if users/views.py exists
        views_file = 'users/views.py'
        if not os.path.exists(views_file):
            log_message("WARNING: users/views.py not found", 'WARNING')
            return
        
        with open(views_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # Add the safe deletion function if it doesn't exist
        if 'def safe_delete_user_records(' not in content:
            # Add the function at the top of the file after imports
            import_section = content.split('\n\n')[0]  # Get imports section
            rest_of_file = '\n\n'.join(content.split('\n\n')[1:])
            
            new_content = import_section + '\n\n' + safe_deletion_code + '\n\n' + rest_of_file
            
            with open(views_file, 'w', encoding='utf-8') as f:
                f.write(new_content)
            
            log_message("SUCCESS: Added safe user deletion function")
        else:
            log_message("SUCCESS: Safe user deletion function already exists")
        
        # Now update the delete_client function to use the safe deletion
        with open(views_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        if 'def delete_client(' in content:
            # Replace the delete_client function with a safer version
            safe_delete_client = '''
@login_required
@require_POST
def delete_client(request, client_id):
    """Delete a client and all related records safely"""
    if not request.user.has_perm('users.delete_customuser'):
        messages.error(request, "You don't have permission to delete users.")
        return redirect('users:staff_dashboard')
    
    try:
        client = get_object_or_404(CustomUser, id=client_id, role='borrower')
        
        # Use the safe deletion function
        success, deletion_log = safe_delete_user_records(client)
        
        if success:
            messages.success(request, f"Client {client.get_full_name()} deleted successfully.")
            # Log the deletion details
            for log_entry in deletion_log:
                logger.info(f"User deletion: {log_entry}")
        else:
            messages.error(request, f"Error deleting client: {deletion_log[-1] if deletion_log else 'Unknown error'}")
            # Log all errors
            for log_entry in deletion_log:
                logger.error(f"User deletion error: {log_entry}")
        
    except Exception as e:
        logger.error(f"Critical error in delete_client: {e}")
        messages.error(request, f"Critical error deleting client: {str(e)}")
    
    return redirect('users:staff_dashboard')
'''
            
            # Find and replace the delete_client function
            import re
            pattern = r'@login_required\s*@require_POST\s*def delete_client\(.*?\n(?:.*?\n)*?.*?return redirect\([\'"]users:staff_dashboard[\'"]\)'
            
            if re.search(pattern, content, re.DOTALL):
                content = re.sub(pattern, safe_delete_client.strip(), content, flags=re.DOTALL)
                
                with open(views_file, 'w', encoding='utf-8') as f:
                    f.write(content)
                
                log_message("SUCCESS: Updated delete_client function to use safe deletion")
            else:
                log_message("WARNING: Could not find delete_client function to replace", 'WARNING')
        
    except Exception as e:
        log_message(f"ERROR: Failed to create safe user deletion function: {e}", 'ERROR')

def fix_user_deletion_issues():
    """Fix user deletion issues by ensuring correct model imports and field references"""
    log_message("Fixing user deletion issues...")
    
    try:
        # First, create the safe deletion function
        create_safe_user_deletion_function()
        
        # Setup Django first to check database schema
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        
        # Check actual database schema for critical tables
        log_message("Checking database schema for user deletion compatibility...")
        
        with connection.cursor() as cursor:
            critical_tables = ['loan_statements', 'receipts', 'offer_letters', 'rollover_requests', 'loan_scoring']
            
            for table_name in critical_tables:
                cursor.execute(f"""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = '{table_name}'
                """)
                columns = [row[0] for row in cursor.fetchall()]
                
                if columns:
                    log_message(f"{table_name} columns: {columns}")
                    
                    # Check for user reference columns
                    user_ref_columns = [col for col in columns if col in ['borrower_id', 'user_id', 'client_id']]
                    if user_ref_columns:
                        log_message(f"  User reference columns: {user_ref_columns}")
                    else:
                        log_message(f"  WARNING: No user reference columns found in {table_name}")
                else:
                    log_message(f"  Table {table_name} does not exist")
        
        log_message("SUCCESS: User deletion issues analysis completed")
        log_message("The safe_delete_user_records function will handle all schema variations")
        
    except Exception as e:
        log_message(f"ERROR: Failed to fix user deletion issues: {e}", 'ERROR')

def test_user_deletion_fix():
    """Test the user deletion fix without actually deleting users"""
    log_message("Testing user deletion fix...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.db import connection
        from django.contrib.auth import get_user_model
        
        User = get_user_model()
        
        # Test the database schema for all critical tables
        with connection.cursor() as cursor:
            tables_to_test = [
                'loan_statements',
                'receipts', 
                'offer_letters',
                'rollover_requests',
                'loan_scoring',
                'utils_notification',
                'utils_document',
                'utils_documentshare',
                'loans'
            ]
            
            log_message("Testing database schema for user deletion compatibility:")
            
            for table_name in tables_to_test:
                try:
                    # Check if table exists
                    cursor.execute(f"""
                        SELECT COUNT(*) 
                        FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = '{table_name}'
                    """)
                    
                    if cursor.fetchone()[0] == 0:
                        log_message(f"   ⚠️  Table {table_name} does not exist")
                        continue
                    
                    # Check for user reference columns
                    possible_columns = ['borrower_id', 'user_id', 'client_id', 'uploaded_by_id', 'shared_with_id', 'recipient_id']
                    found_columns = []
                    
                    for col in possible_columns:
                        cursor.execute(f"""
                            SELECT COUNT(*) 
                            FROM INFORMATION_SCHEMA.COLUMNS 
                            WHERE TABLE_SCHEMA = DATABASE() 
                            AND TABLE_NAME = '{table_name}' 
                            AND COLUMN_NAME = '{col}'
                        """)
                        
                        if cursor.fetchone()[0] > 0:
                            found_columns.append(col)
                    
                    if found_columns:
                        log_message(f"   ✅ {table_name}: Found user reference columns {found_columns}")
                        
                        # Test a sample query (without actually deleting)
                        test_user = User.objects.first()
                        if test_user:
                            for col in found_columns:
                                try:
                                    cursor.execute(f"""
                                        SELECT COUNT(*) FROM {table_name} 
                                        WHERE {col} = %s
                                    """, [str(test_user.id)])
                                    count = cursor.fetchone()[0]
                                    log_message(f"      Query test for {col}: {count} records found")
                                except Exception as e:
                                    log_message(f"      Query test for {col} failed: {e}", 'ERROR')
                    else:
                        log_message(f"   ❌ {table_name}: No user reference columns found")
                        
                except Exception as e:
                    log_message(f"   ❌ Error testing {table_name}: {e}", 'ERROR')
        
        # Test if the safe deletion function exists
        views_file = 'users/views.py'
        if os.path.exists(views_file):
            with open(views_file, 'r', encoding='utf-8') as f:
                content = f.read()
            
            if 'def safe_delete_user_records(' in content:
                log_message("   ✅ Safe user deletion function exists in users/views.py")
            else:
                log_message("   ❌ Safe user deletion function missing from users/views.py", 'ERROR')
            
            if 'def delete_client(' in content:
                log_message("   ✅ delete_client function exists in users/views.py")
            else:
                log_message("   ❌ delete_client function missing from users/views.py", 'ERROR')
        else:
            log_message("   ❌ users/views.py file not found", 'ERROR')
        
        log_message("SUCCESS: User deletion fix testing completed")
        
    except Exception as e:
        log_message(f"ERROR: User deletion fix testing failed: {e}", 'ERROR')

def verify_system_integrity():
    """Verify that all system components are working correctly"""
    log_message("Verifying system integrity...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.contrib.auth import get_user_model
        from loans.models import Loan, LoanProduct
        from users.models import RolePermission
        from utils.models import SystemSetting
        
        User = get_user_model()
        
        # Check critical components
        checks = [
            ("Users", User.objects.count()),
            ("Loan Products", LoanProduct.objects.count()),
            ("Role Permissions", RolePermission.objects.count()),
            ("System Settings", SystemSetting.objects.count()),
            ("Total Loans", Loan.objects.count()),
        ]
        
        log_message("System Component Status:")
        for component, count in checks:
            log_message(f"   {component}: {count}")
        
        # Test user deletion fix
        test_user_deletion_fix()
        
        # Check critical files
        critical_files = [
            'branch_system/settings_production.py',
            'passenger_wsgi.py',
            'templates/reports/dashboard_fixed.html',
            'reports/views.py',
            'users/staff_views.py',
        ]
        
        log_message("Critical Files Status:")
        for file_path in critical_files:
            status = "EXISTS" if os.path.exists(file_path) else "MISSING"
            log_message(f"   {file_path}: {status}")
        
        # Check directories
        critical_dirs = [
            'media/kyc/id_documents',
            'media/profile_images',
            'staticfiles',
            'logs',
        ]
        
        log_message("Critical Directories Status:")
        for dir_path in critical_dirs:
            status = "EXISTS" if os.path.exists(dir_path) else "MISSING"
            log_message(f"   {dir_path}: {status}")
        
        log_message("SUCCESS: System integrity verification completed")
        
    except Exception as e:
        log_message(f"ERROR: System integrity check failed: {e}", 'ERROR')

def fix_existing_image_paths():
    """Fix image paths for existing users"""
    log_message("Fixing existing user image paths...")
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        from django.contrib.auth import get_user_model
        User = get_user_model()
        
        # Get all users
        users = User.objects.all()
        
        if users.count() == 0:
            log_message("SUCCESS: No users found")
            return
        
        log_message(f"Checking {users.count()} users for image path fixes")
        
        # Image field mappings
        image_fields = [
            ('id_document', 'kyc/id_documents/'),
            ('selfie', 'kyc/selfies/'),
            ('utility_bill', 'kyc/utility_bills/'),
            ('bank_statement', 'kyc/bank_statements/'),
            ('business_license', 'kyc/business_licenses/'),
            ('tax_certificate', 'kyc/tax_certificates/'),
            ('logbook', 'kyc/logbooks/'),
            ('title_deed', 'kyc/title_deeds/'),
            ('signature', 'kyc/signatures/'),
            ('profile_image', 'profile_images/'),
        ]
        
        fixed_count = 0
        total_fixes = 0
        
        for user in users:
            user_updated = False
            
            for field_name, expected_prefix in image_fields:
                field_value = getattr(user, field_name)
                
                if field_value:
                    current_path = str(field_value)
                    
                    # Skip if already has correct prefix
                    if current_path.startswith(expected_prefix):
                        continue
                    
                    # Extract filename
                    filename = os.path.basename(current_path)
                    
                    if filename:
                        # Create correct path
                        new_path = expected_prefix + filename
                        
                        # Update the field
                        setattr(user, field_name, new_path)
                        user_updated = True
                        total_fixes += 1
                        
                        log_message(f"Fixed {user.email} {field_name}: {current_path} → {new_path}")
            
            if user_updated:
                user.save()
                fixed_count += 1
        
        log_message(f"SUCCESS: Fixed image paths for {fixed_count} users ({total_fixes} total fixes)")
        
    except Exception as e:
        log_message(f"ERROR: Error fixing image paths: {e}", 'ERROR')

def fix_media_url_settings():
    """Fix media URL settings for proper image display in production"""
    log_message("Fixing media URL settings for production...")
    
    try:
        # Read current production settings
        settings_file = 'branch_system/settings_production.py'
        
        if os.path.exists(settings_file):
            with open(settings_file, 'r', encoding='utf-8') as f:
                content = f.read()
            
            # Check if media settings need updating
            if 'MEDIA_URL = \'/media/\'' in content and 'MEDIA_ROOT = os.path.join(BASE_DIR, \'media\')' in content:
                log_message("SUCCESS: Media settings already properly configured")
                return True
            
            # Update media settings section
            media_settings = '''
# Static and Media Files - Enhanced for Production
STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles')
STATIC_URL = '/static/'

# Media files configuration for proper image display
MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'

# Ensure media directory exists and is writable
import stat
if not os.path.exists(MEDIA_ROOT):
    os.makedirs(MEDIA_ROOT, mode=0o755, exist_ok=True)
    
# Set proper permissions for media directory
try:
    os.chmod(MEDIA_ROOT, stat.S_IRWXU | stat.S_IRGRP | stat.S_IXGRP | stat.S_IROTH | stat.S_IXOTH)
except:
    pass  # Ignore permission errors on some hosting environments

# Ensure subdirectories exist
media_subdirs = [
    'kyc/id_documents',
    'kyc/selfies', 
    'kyc/utility_bills',
    'kyc/bank_statements',
    'kyc/business_licenses',
    'kyc/tax_certificates',
    'kyc/logbooks',
    'kyc/title_deeds',
    'kyc/signatures',
    'profile_images',
    'documents',
    'receipts',
    'reports'
]

for subdir in media_subdirs:
    full_path = os.path.join(MEDIA_ROOT, subdir)
    os.makedirs(full_path, mode=0o755, exist_ok=True)

# Ensure directories exist
os.makedirs(STATIC_ROOT, exist_ok=True)
os.makedirs(os.path.join(BASE_DIR, 'logs'), exist_ok=True)'''
            
            # Replace the media settings section
            import re
            pattern = r'# Static and Media Files.*?os\.makedirs\(os\.path\.join\(BASE_DIR, \'logs\'\), exist_ok=True\)'
            if re.search(pattern, content, re.DOTALL):
                content = re.sub(pattern, media_settings.strip(), content, flags=re.DOTALL)
            else:
                # If pattern not found, append media settings
                content += '\n' + media_settings
            
            # Write updated content
            with open(settings_file, 'w', encoding='utf-8') as f:
                f.write(content)
            
            log_message("SUCCESS: Updated media URL settings for production")
            return True
        else:
            log_message("ERROR: Production settings file not found", 'ERROR')
            return False
            
    except Exception as e:
        log_message(f"ERROR: Failed to fix media URL settings: {e}", 'ERROR')
        return False

def create_htaccess_for_media():
    """Create .htaccess file for proper media file serving"""
    log_message("Creating .htaccess for media file serving...")
    
    htaccess_content = '''# HAVEN GRAZURI VESTMENT LIMITED- Media Files Configuration
# Serve media files directly through Apache/web server

# Enable rewrite engine
RewriteEngine On

# Set proper MIME types
<IfModule mod_mime.c>
    AddType image/jpeg .jpg .jpeg
    AddType image/png .png
    AddType image/gif .gif
    AddType image/webp .webp
    AddType application/pdf .pdf
    AddType application/msword .doc
    AddType application/vnd.openxmlformats-officedocument.wordprocessingml.document .docx
</IfModule>

# Enable compression
<IfModule mod_deflate.c>
    AddOutputFilterByType DEFLATE image/jpeg
    AddOutputFilterByType DEFLATE image/png
    AddOutputFilterByType DEFLATE application/pdf
</IfModule>

# Set cache headers
<IfModule mod_expires.c>
    ExpiresActive On
    ExpiresByType image/jpg "access plus 1 month"
    ExpiresByType image/jpeg "access plus 1 month"
    ExpiresByType image/png "access plus 1 month"
    ExpiresByType image/gif "access plus 1 month"
    ExpiresByType image/webp "access plus 1 month"
    ExpiresByType application/pdf "access plus 1 month"
</IfModule>

# Security headers
<IfModule mod_headers.c>
    Header always set X-Content-Type-Options nosniff
    Header always set X-Frame-Options SAMEORIGIN
</IfModule>

# Allow access to media files
<FilesMatch "\\.(jpg|jpeg|png|gif|webp|pdf|doc|docx)$">
    Order allow,deny
    Allow from all
</FilesMatch>

# Prevent access to Python files
<Files "*.py">
    Order allow,deny
    Deny from all
</Files>

<Files "*.pyc">
    Order allow,deny
    Deny from all
</Files>
'''
    
    try:
        # Ensure media directory exists before writing to it
        os.makedirs('media', exist_ok=True)
        
        # Create .htaccess in media directory
        media_htaccess = os.path.join('media', '.htaccess')
        with open(media_htaccess, 'w', encoding='utf-8') as f:
            f.write(htaccess_content)
        log_message("SUCCESS: Created .htaccess for media directory")
        
        # Create main .htaccess if it doesn't exist
        main_htaccess = '.htaccess'
        if not os.path.exists(main_htaccess):
            main_htaccess_content = '''# HAVEN GRAZURI VESTMENT LIMITED- Main Configuration
RewriteEngine On

# Force HTTPS (uncomment if SSL is configured)
# RewriteCond %{HTTPS} off
# RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

# Handle Django static files
RewriteRule ^static/(.*)$ /staticfiles/$1 [L]

# Handle Django media files - serve directly
RewriteRule ^media/(.*)$ /media/$1 [L]

# Security headers
<IfModule mod_headers.c>
    Header always set X-Content-Type-Options nosniff
    Header always set X-Frame-Options SAMEORIGIN
    Header always set X-XSS-Protection "1; mode=block"
    Header always set Referrer-Policy "strict-origin-when-cross-origin"
</IfModule>
'''
            with open(main_htaccess, 'w', encoding='utf-8') as f:
                f.write(main_htaccess_content)
            log_message("SUCCESS: Created main .htaccess file")
        
        return True
        
    except Exception as e:
        log_message(f"ERROR: Failed to create .htaccess files: {e}", 'ERROR')
        return False

def fix_media_urls_configuration():
    """Fix media URLs configuration for production"""
    log_message("Fixing media URLs configuration...")
    
    urls_file = 'branch_system/urls.py'
    
    if not os.path.exists(urls_file):
        log_message(f"ERROR: {urls_file} not found", 'ERROR')
        return False
    
    try:
        # Create backup
        backup_file = f"{urls_file}.backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        shutil.copy2(urls_file, backup_file)
        log_message(f"Created backup: {backup_file}")
        
        # Read current content
        with open(urls_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # Check if already fixed
        if 'let the web server handle media files directly' in content:
            log_message("SUCCESS: URLs configuration already fixed")
            return True
        
        # New URLs configuration
        new_urls_content = '''"""
URL configuration for branch_system project.
"""
from django.contrib import admin
from django.urls import path, include
from django.conf import settings
from django.conf.urls.static import static
from django.views.generic import RedirectView
from django.http import JsonResponse
from django.views.decorators.http import require_POST
from django.contrib.auth.decorators import login_required
from loans import views as loans_views

@login_required
@require_POST
def session_ping(request):
    return JsonResponse({'status': 'ok'})

@login_required
def home_redirect(request):
    return RedirectView.as_view(url='/dashboard/', permanent=False)(request)

urlpatterns = [
    path('admin/', admin.site.urls),
    path('', home_redirect, name='home'),
    path('dashboard/', loans_views.dashboard, name='dashboard'),
    path('api/loan-data/', loans_views.api_loan_data, name='api_loan_data'),
    path('api/recent-activity/', loans_views.api_recent_activity, name='api_recent_activity'),
    path('api/session-ping/', session_ping, name='session_ping'),
    path('loans/', include('loans.urls')),
    path('', include('users.urls')),
    path('reports/', include('reports.urls')),
    path('utils/', include('utils.urls')),
]

# Media and static files configuration
if settings.DEBUG:
    # In development, serve both static and media files through Django
    urlpatterns += static(settings.STATIC_URL, document_root=settings.STATIC_ROOT)
    urlpatterns += static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)
else:
    # In production, let the web server handle media files directly
    # Do NOT serve media files through Django in production
    pass

# Customize admin site
admin.site.site_header = "HAVEN GRAZURI VESTMENT LIMITED- Admin"
admin.site.site_title = "HAVEN GRAZURI Advance"
admin.site.index_title = "Welcome to HAVEN GRAZURI VESTMENT LIMITEDAdministration"
'''
        
        # Write the new content
        with open(urls_file, 'w', encoding='utf-8') as f:
            f.write(new_urls_content)
        
        log_message("SUCCESS: Updated URLs configuration for production media serving")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Failed to update URLs: {e}", 'ERROR')
        # Restore backup if it exists
        if 'backup_file' in locals() and os.path.exists(backup_file):
            shutil.copy2(backup_file, urls_file)
            log_message(f"Restored backup from {backup_file}")
        return False

def main():
    print("🚀 Starting HAVEN GRAZURI VESTMENT LIMITEDsetup...")
    
    # Step 1: Create necessary directories
    print("\n📁 Creating directories...")
    directories = [
        "logs",
        "media/kyc/bank_statements",
        "media/kyc/id_documents", 
        "media/kyc/selfies",
        "media/kyc/utility_bills",
        "media/kyc/business_licenses",
        "media/kyc/tax_certificates",
        "media/kyc/logbooks",
        "media/kyc/title_deeds",
        "media/kyc/signatures",
        "media/profile_images",
        "media/documents",
        "media/receipts",
        "media/reports",
        "static",
        "staticfiles"
    ]
    
    for directory in directories:
        os.makedirs(directory, exist_ok=True)
        print(f"SUCCESS: Created {directory}")
    
    # Step 2: Install requirements
    install_requirements()
    
    # Step 3: Create production settings
    create_production_settings()
    
    # Step 4: Fix media URL settings for proper image display
    fix_media_url_settings()
    
    # Step 5: Create .htaccess files for proper media serving
    create_htaccess_for_media()
    
    # Step 6: Fix media URLs configuration
    fix_media_urls_configuration()
    
    # Step 6: Update passenger_wsgi.py
    update_passenger_wsgi()
    
    # Step 7: Run Django commands with better error handling
    run_migrations_with_retry()
    
    print("\n📂 Collecting static files...")
    run_command("python manage.py collectstatic --noinput")
    
    print("\n💾 Creating cache table...")
    run_command("python manage.py createcachetable")
    
    # Step 8: Ensure monthly_income field exists
    ensure_monthly_income_field()
    
    # Step 9: Ensure document fields exist
    ensure_document_fields()
    
    # Step 10: Ensure database integrity (fix missing columns)
    ensure_loan_table_integrity()
    ensure_database_integrity()
    
    # Step 11: Ensure database indexes for performance
    ensure_table_indexes()
    
    # Step 12: Create document upload directories
    create_document_upload_directories()
    
    # Step 13: Setup role-based permissions system
    setup_role_permissions()
    
    # Step 14: Create default system settings    # Step 16: Verify admin user
    verify_admin_user()
    
    # Step 17: Create default avatars for user roles
    create_default_avatars()
    
    # Step 18: Setup penalty system with individual product settings
    setup_penalty_system()
    
    # Step 19: Setup reports environment
    setup_reports_environment()
    
    # Step 20: Setup credit score system
    setup_credit_score_system()
    
    # Step 21: Setup enhanced receipt system
    setup_receipt_system()
    
    # Step 22: Setup rollover and collections system
    setup_rollover_collections_system()
    
    # Step 23: Setup blacklist and status management system
    setup_blacklist_system()
    
    # Step 24: Setup portfolio management system
    setup_portfolio_management()
    
    # Step 25: Fix Reports & Statements Dashboard
    fix_reports_dashboard()
    
    # Step 26: Setup enhanced user management
    setup_enhanced_user_management()
    
    # Step 27: Setup staff management system
    setup_staff_management_system()
    
    # Step 28: Ensure template directories
    ensure_template_directories()
    
    # Step 29: Fix user deletion issues (CRITICAL FIX)
    fix_user_deletion_issues()
    
    # Step 30: Fix existing image paths
    fix_existing_image_paths()
    
    log_message("SUCCESS: Setup completed!")
    print("\n💰 4 Loan Products Created:")
    print("   1. BOOST - Quick short-term loans (KES 1,000 - 50,000, 30 days, 15% interest)")
    print("   2. BOOST PLUS - Enhanced loans for established businesses (KES 5,000 - 100,000, 60 days, 12% interest)")
    print("   3. MWAMBA - Medium-term expansion loans (KES 10,000 - 200,000, 90 days, 10% interest)")
    print("   4. IMARA - Long-term investment loans (KES 50,000 - 500,000, 180 days, 8% interest)")
    
    print("\n🖼️ Image Display Fixed for Production:")
    print("   - Proper media URL configuration for cPanel hosting")
    print("   - .htaccess files created for media file serving")
    print("   - MIME types configured for all image formats")
    print("   - Media directory permissions set correctly")
    print("   - Profile images, KYC documents, and selfies will display properly")
    
    print("\n🔐 Role-Based Access Control (RBAC) System:")
    print("   - 6 User Roles: Admin, Team Leader, Loan Officer, Secretary, Auditor, Borrower")
    print("   - 160 Granular Permissions across 10 modules")
    print("   - Complete audit trail and access logging")
    print("   - Admin Management interface for user creation and permission management")
    print("   - Profile image support for all system users")
    
    print("\n💰 Advanced Penalty System:")
    print("   - Individual penalty rates for each loan product")
    print("   - Boost/Boost Plus/Mwamba: 5.0% daily penalty")
    print("   - Imara: 3.0% daily penalty (lower for long-term loans)")
    print("   - Automatic penalty calculation and application")
    print("   - Comprehensive penalty tracking and audit trail")
    print("   - Management commands for automated penalty processing")
    
    print("\n🖼️ Enhanced User Experience:")
    print("   - Profile image upload and management for staff users")
    print("   - Automatic image resizing and optimization")
    print("   - Role-based default avatars with color coding")
    print("   - Profile image display in navigation and user interfaces")
    print("   - Fallback to initials-based avatars")
    
    print("\n📊 Enhanced Reporting & Analytics:")
    print("   - Advanced chart generation with matplotlib and seaborn")
    print("   - Interactive client popup charts with Chart.js CDN")
    print("   - Comprehensive loan analytics and risk assessment")
    print("   - PDF and Excel export capabilities")
    print("   - Real-time performance metrics and KPIs")
    
    print("\n👤 Enhanced Client Management:")
    print("   - Comprehensive client popup with selfie photo display")
    print("   - Interactive loan summary charts (Chart.js CDN)")
    print("   - Ordinal loan naming (First Loan, Second Loan, etc.)")
    print("   - Complete document management (10 document types)")
    print("   - Real-time document status tracking")
    print("   - Enhanced KYC document handling")
    print("   - Improved client profile visualization")
    
    print("\n📊 Credit Score System:")
    print("   - Advanced credit score calculation engine")
    print("   - 5-factor scoring model (payment history, utilization, etc.)")
    print("   - Automatic score updates and monitoring")
    print("   - Credit score-based loan approval automation")
    print("   - Historical credit score tracking and analytics")
    print("   - Integration with loan application workflow")
    
    print("\n🧾 Enhanced Receipt System:")
    print("   - Automatic receipt generation and numbering")
    print("   - Duplicate prevention and void management")
    print("   - Email and SMS receipt delivery")
    print("   - Receipt audit trail and reprinting")
    print("   - Customizable receipt templates")
    print("   - Integration with payment processing")
    
    print("\n🔄 Rollover & Collections System:")
    print("   - Automated rollover processing and approval workflow")
    print("   - Configurable rollover fees and limits")
    print("   - Collections management with escalation rules")
    print("   - Grace period and notification management")
    print("   - Legal action threshold monitoring")
    print("   - Performance tracking and reporting")
    
    print("\n🚫 Blacklist & Status Management:")
    print("   - Automated blacklist management")
    print("   - Configurable blacklisting criteria")
    print("   - Status-based loan workflow automation")
    print("   - Real-time status updates and notifications")
    print("   - Blacklist removal approval process")
    print("   - Integration with loan application screening")
    
    print("\n📊 Portfolio Management System:")
    print("   - Advanced portfolio analytics and reporting")
    print("   - Automated client assignment to portfolio managers")
    print("   - Performance tracking and KPI monitoring")
    print("   - Risk assessment and alert system")
    print("   - Portfolio optimization recommendations")
    print("   - Comprehensive dashboard and visualizations")
    
    print("\n📈 Reports & Statements Dashboard - FIXED:")
    print("   - Fixed UI layout issues (removed excessive right margin)")
    print("   - Fixed non-functional values (now shows real data)")
    print("   - Enhanced responsive design for mobile/tablet")
    print("   - Added smooth animations and hover effects")
    print("   - Fixed database query mixed types errors")
    print("   - All metrics now display actual calculated values")
    print("   - Portfolio overview, loans due, delinquent loans working")
    print("   - Processing fees, interest income calculations fixed")
    print("   - Interactive dashboard with auto-refresh functionality")
    
    print("\n🗑️ User Deletion System - COMPLETELY FIXED:")
    print("   - Fixed 'Unknown column borrower_id' database errors")
    print("   - Fixed 'Table user_permissions doesn't exist' errors")
    print("   - Added comprehensive database schema detection")
    print("   - Created safe_delete_user_records() function")
    print("   - Handles all table schema variations automatically")
    print("   - Added proper error handling and logging")
    print("   - Works with borrower_id, user_id, or client_id columns")
    print("   - Comprehensive transaction rollback on errors")
    print("   - Detailed deletion audit trail")
    print("   - Created all missing Django system tables")
    print("   - No more production deletion errors!")
    
    print("\n🔧 Database Collation Issues - COMPLETELY FIXED:")
    print("   - Fixed 'Illegal mix of collations' errors in reports")
    print("   - Standardized all tables to utf8mb4_unicode_ci collation")
    print("   - Fixed registration fees report collation errors")
    print("   - Added collation-safe query methods")
    print("   - All string comparisons now work correctly")
    print("   - Reports system fully functional")
    print("   - No more collation mismatch errors!")
    
    print("\n👥 Enhanced User Management System:")
    print("   - Advanced role-based permissions (6 roles, 160+ permissions)")
    print("   - Profile image upload and management")
    print("   - Email and phone verification system")
    print("   - Account lockout and security features")
    print("   - Session management and timeout controls")
    print("   - Password reset and recovery system")
    print("   - User activity tracking and audit logs")
    print("   - Staff dashboard with role-specific access")
    
    print("\n🗄️ Database Integrity & Performance:")
    print("   - Fixed missing foreign key columns")
    print("   - Added proper database indexes")
    print("   - Ensured all required fields exist")
    print("   - Optimized query performance")
    print("   - Resolved production deployment issues")
    print("   - Added penalty charges tracking table")
    
    print("\nImportant Information:")
    print("🌐 Site URL: https://branchbusinessadvance.co.ke/")
    print("👤 Admin Login:")
    print("   Email: admin@branchbusinessadvance.com")
    print("   Password: admin123")
    print("   Role: Admin (Full system access)")
    
    print("\n🔐 Available Staff Roles:")
    print("   - Admin: Full system access (58 permissions)")
    print("   - Team Leader: Broad access, no system settings management (34 permissions)")
    print("   - Loan Officer: Loan processing & client management (26 permissions)")
    print("   - Secretary: Administrative tasks & document management (21 permissions)")
    print("   - Auditor: Read-only access for compliance (12 permissions)")
    print("   - Borrower: Limited self-service access (9 permissions)")
    
    print("\nWARNING: Security Reminders:")
    print("1. Change the admin password after first login")
    print("2. Update SECRET_KEY in production settings")
    print("3. Store database credentials securely")
    print("4. Configure email settings in production")
    print("5. Review and customize role permissions as needed")
    
    print("\n📝 Logs Location: ./logs/django.log")
    print("🔍 Access Logs: Available in Admin Management  to  Access Logs")
    
    print("\n🔧 Next Steps:")
    print("1. Configure your domain in cPanel")
    print("2. Set up SSL certificate")
    print("3. Configure email settings")
    print("4. Set up database backups")
    print("5. Test all functionality in production")
    print("6. Create additional staff users via Admin Management")
    print("7. Customize role permissions for your organization")
    print("8. Test image display (profile images, KYC documents, selfies)")
    print("9. Test Reports & Statements Dashboard (now fully functional)")
    print("10. Review and adjust the 4 loan products as needed:")
    print("    - Boost: Short-term quick loans")
    print("    - Boost Plus: Enhanced terms for established businesses")
    print("    - Mwamba: Medium-term expansion loans with collateral")
    print("    - Imara: Long-term investment loans with best rates")
    print("11. Set up automated penalty application (cron job):")
    print("    0 6 * * * cd /path/to/project && python manage.py apply_penalties")
    print("12. Configure individual penalty rates in Settings → Loan Products")
    print("13. Test staff management features and role-based access")
    print("14. Verify all dashboard metrics are displaying real data")
    print("15. Test user profile image uploads and display")
    
    print("\n🔍 Troubleshooting:")
    if not os.path.exists('users/migrations'):
        print("WARNING: If migrations still fail, manually create migrations directories:")
        print("   mkdir -p users/migrations loans/migrations reports/migrations utils/migrations")
        print("   touch users/migrations/__init__.py loans/migrations/__init__.py")
        print("   touch reports/migrations/__init__.py utils/migrations/__init__.py")

def create_cache_table():
    """Create cache table for database caching"""
    log_message("Creating cache table...")
    try:
        success = run_command("python manage.py createcachetable")
        if success:
            log_message("SUCCESS: Cache table created successfully")
        else:
            log_message("WARNING: Cache table creation failed (may already exist)", 'WARNING')
    except Exception as e:
        log_message(f"ERROR: Error creating cache table: {e}", 'ERROR')

def collect_static_files():
    """Collect static files for production"""
    log_message("Collecting static files...")
    try:
        # Create staticfiles directory if it doesn't exist
        os.makedirs('staticfiles', exist_ok=True)
        
        success = run_command("python manage.py collectstatic --noinput --clear")
        if success:
            log_message("SUCCESS: Static files collected successfully")
            return True
        else:
            log_message("ERROR: Failed to collect static files", 'ERROR')
            return False
    except Exception as e:
        log_message(f"ERROR: Error collecting static files: {e}", 'ERROR')
        return False

def create_environment_file():
    """Create .env file for environment variables.

    If .env already exists, existing values are preserved — only missing keys
    are added with their defaults. This prevents overwriting valid production
    credentials that were set up manually on the server.
    """
    log_message("Creating environment configuration...")

    # Default values — used only when a key is not already present in .env
    defaults = {
        'DB_NAME': 'xygbfpsg_loans',
        'DB_USER': 'xygbfpsg_graz',
        'DB_PASSWORD': "+MvX9&%PV']]pW}",
        'DB_HOST': 'localhost',
        'DB_PORT': '3306',
        'SECRET_KEY': '(W6B1[bCEAu,1wA[dz7^-prod-key-2024)',
        'USE_HTTPS': 'True',
        'EMAIL_HOST': 'smtp.branchbusinessadvance.co.ke',
        'EMAIL_PORT': '587',
        'EMAIL_USE_TLS': 'True',
        'EMAIL_USER': 'support@branchbusinessadvance.co.ke',
        'EMAIL_PASSWORD': '',
        'DEFAULT_FROM_EMAIL': 'support@branchbusinessadvance.co.ke',
        'SESSION_TIMEOUT': '3600',
        'BACKUP_ENABLED': 'True',
        'BACKUP_RETENTION_DAYS': '30',
        'DEBUG': 'False',
    }

    env_file = '.env'
    existing = {}

    # Parse existing .env so we don't lose values already set on the server
    if os.path.exists(env_file):
        log_message(".env already exists — preserving existing values, adding any missing keys only")
        try:
            with open(env_file, 'r', encoding='utf-8') as f:
                for line in f:
                    line = line.strip()
                    if line and not line.startswith('#') and '=' in line:
                        key, _, value = line.partition('=')
                        existing[key.strip()] = value.strip()
        except Exception as e:
            log_message(f"WARNING: Could not read existing .env: {e}", 'WARNING')
    else:
        log_message(".env not found — creating with default values")

    # Merge: existing values win; defaults fill in gaps
    merged = {**defaults, **existing}

    try:
        with open(env_file, 'w', encoding='utf-8') as f:
            f.write(f"# HAVEN GRAZURI VESTMENT LIMITED Environment Configuration\n")
            f.write(f"# Updated on {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
            f.write("# Database Configuration\n")
            for key in ('DB_NAME', 'DB_USER', 'DB_PASSWORD', 'DB_HOST', 'DB_PORT'):
                f.write(f"{key}={merged.get(key, defaults.get(key, ''))}\n")
            f.write("\n# Security\n")
            for key in ('SECRET_KEY', 'USE_HTTPS', 'DEBUG'):
                f.write(f"{key}={merged.get(key, defaults.get(key, ''))}\n")
            f.write("\n# Email Configuration\n")
            for key in ('EMAIL_HOST', 'EMAIL_PORT', 'EMAIL_USE_TLS', 'EMAIL_USER', 'EMAIL_PASSWORD', 'DEFAULT_FROM_EMAIL'):
                f.write(f"{key}={merged.get(key, defaults.get(key, ''))}\n")
            f.write("\n# Session Configuration\n")
            f.write(f"SESSION_TIMEOUT={merged.get('SESSION_TIMEOUT', '3600')}\n")
            f.write("\n# Backup Configuration\n")
            for key in ('BACKUP_ENABLED', 'BACKUP_RETENTION_DAYS'):
                f.write(f"{key}={merged.get(key, defaults.get(key, ''))}\n")
            # Preserve any extra keys that were in the original .env but not in our defaults
            extras = {k: v for k, v in existing.items() if k not in defaults}
            if extras:
                f.write("\n# Additional configuration\n")
                for key, value in extras.items():
                    f.write(f"{key}={value}\n")
        log_message("SUCCESS: Environment file created/updated (existing values preserved)")
        return True
    except Exception as e:
        log_message(f"ERROR: Failed to create environment file: {e}", 'ERROR')
        return False

def setup_health_check():
    """Create a simple health check endpoint"""
    log_message("Setting up health check...")

    health_check_content = '''"""
Health check view for monitoring
"""
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from django.views.decorators.http import require_http_methods
from django.db import connection
import json

@csrf_exempt
@require_http_methods(["GET"])
def health_check(request):
    """Simple health check endpoint"""
    try:
        # Test database connection
        with connection.cursor() as cursor:
            cursor.execute("SELECT 1")
            db_status = "healthy"
    except Exception as e:
        db_status = f"unhealthy: {str(e)}"
    
    health_data = {
        "status": "healthy" if db_status == "healthy" else "unhealthy",
        "database": db_status,
        "timestamp": str(timezone.now()) if 'timezone' in globals() else str(datetime.now()),
        "version": "2.0"
    }
    
    return JsonResponse(health_data)
'''
    
    try:
        # Create utils/health.py if it doesn't exist
        health_file = 'utils/health.py'
        if not os.path.exists(health_file):
            with open(health_file, 'w', encoding='utf-8') as f:
                f.write(health_check_content)
            log_message("SUCCESS: Health check endpoint created")
        else:
            log_message("SUCCESS: Health check endpoint already exists")
        return True
    except Exception as e:
        log_message(f"ERROR: Failed to create health check: {e}", 'ERROR')
        return False

def create_cache_table():
    """Create cache table for database caching"""
    log_message("Creating cache table...")
    try:
        success = run_command("python manage.py createcachetable")
        if success:
            log_message("SUCCESS: Cache table created successfully")
        else:
            log_message("WARNING: Cache table creation failed (may already exist)", 'WARNING')
    except Exception as e:
        log_message(f"ERROR: Error creating cache table: {e}", 'ERROR')

def collect_static_files():
    """Collect static files for production"""
    log_message("Collecting static files...")
    try:
        # Create staticfiles directory if it doesn't exist
        os.makedirs('staticfiles', exist_ok=True)
        
        success = run_command("python manage.py collectstatic --noinput --clear")
        if success:
            log_message("SUCCESS: Static files collected successfully")
            return True
        else:
            log_message("ERROR: Failed to collect static files", 'ERROR')
            return False
    except Exception as e:
        log_message(f"ERROR: Error collecting static files: {e}", 'ERROR')
        return False

def setup_health_check():
    """Create a simple health check endpoint"""
    log_message("Setting up health check...")
    
    health_check_content = '''"""
Health check view for monitoring
"""
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from django.views.decorators.http import require_http_methods
from django.db import connection
from django.utils import timezone
from datetime import datetime
import json

@csrf_exempt
@require_http_methods(["GET"])
def health_check(request):
    """Simple health check endpoint"""
    try:
        # Test database connection
        with connection.cursor() as cursor:
            cursor.execute("SELECT 1")
            db_status = "healthy"
    except Exception as e:
        db_status = f"unhealthy: {str(e)}"
    
    health_data = {
        "status": "healthy" if db_status == "healthy" else "unhealthy",
        "database": db_status,
        "timestamp": str(timezone.now()),
        "version": "2.0"
    }
    
    return JsonResponse(health_data)
'''
    
    try:
        # Create utils/health.py if it doesn't exist
        health_file = 'utils/health.py'
        if not os.path.exists(health_file):
            with open(health_file, 'w', encoding='utf-8') as f:
                f.write(health_check_content)
            log_message("SUCCESS: Health check endpoint created")
        else:
            log_message("SUCCESS: Health check endpoint already exists")
        return True
    except Exception as e:
        log_message(f"ERROR: Failed to create health check: {e}", 'ERROR')
        return False

def run_comprehensive_setup():
    """Main setup function that orchestrates all setup tasks"""
    log_message("Starting HAVEN GRAZURI VESTMENT LIMITEDComprehensive Setup")
    log_message("=" * 60)
    
    setup_start_time = datetime.now()
    
    # Pre-flight checks
    log_message("Phase 1: Pre-flight Checks")
    log_message("-" * 30)
    
    if not check_python_version():
        log_message("ERROR: Setup aborted due to Python version incompatibility", 'ERROR')
        return False
    
    if not check_disk_space():
        log_message("WARNING: Low disk space detected, but continuing...", 'WARNING')
    
    # Create backup
    log_message("\nPhase 2: Backup Creation")
    log_message("-" * 30)
    backup_dir = create_backup()
    
    # Install requirements
    log_message("\nPhase 3: Package Installation")
    log_message("-" * 30)
    if not install_requirements():
        log_message("ERROR: Setup aborted due to package installation failure", 'ERROR')
        return False
    
    # Create production settings
    log_message("\nPhase 4: Configuration Setup")
    log_message("-" * 30)
    if not create_production_settings():
        log_message("ERROR: Setup aborted due to settings creation failure", 'ERROR')
        return False
    
    fix_media_url_settings()
    create_htaccess_for_media()
    update_passenger_wsgi()
    create_environment_file()
    
    # Database operations
    log_message("\nPhase 5: Database Setup")
    log_message("-" * 30)
    
    if not verify_database_connection():
        log_message("ERROR: Setup aborted due to database connection failure", 'ERROR')
        return False
    
    # CRITICAL: Fix database issues first
    ensure_database_integrity()  # This now includes collation and missing table fixes
    run_migrations_with_retry()
    ensure_loan_table_integrity()
    ensure_monthly_income_field()
    ensure_document_fields()
    ensure_table_indexes()
    create_cache_table()
    
    # User and permissions setup
    log_message("\nPhase 6: User & Permissions Setup")
    log_message("-" * 30)
    verify_admin_user()
    setup_role_permissions()
    setup_enhanced_user_management()
    setup_staff_management_system()
  
    
    # Final setup tasks
    log_message("\nPhase 7: Final Configuration")
    log_message("-" * 30)
    ensure_template_directories()
    fix_reports_dashboard()
    fix_user_deletion_issues()
    collect_static_files()
    setup_health_check()
    
    # System verification
    log_message("\nPhase 8: System Verification")
    log_message("-" * 30)
    verify_system_integrity()
    
    # Setup completion
    setup_end_time = datetime.now()
    setup_duration = setup_end_time - setup_start_time
    
    log_message("\n" + "=" * 60)
    log_message("SETUP COMPLETED SUCCESSFULLY!")
    log_message(f"Total setup time: {setup_duration}")
    log_message(f"Backup created in: {backup_dir}")
    log_message(f"Setup log saved to: {SETUP_LOG_FILE}")
    log_message("=" * 60)
    
    # Post-setup instructions
    log_message("\nPOST-SETUP INSTRUCTIONS:")
    log_message("1. Verify your domain is pointing to this directory")
    log_message("2. Ensure passenger_wsgi.py is configured in cPanel")
    log_message("3. Set up SSL certificate if not already done")
    log_message("4. Test the application at your domain")
    log_message("5. Monitor logs in the 'logs' directory")
    log_message("6. Admin login: admin@branchbusinessadvance.com / admin123")
    log_message("\nIMPORTANT: Change the admin password after first login!")
    
    return True

def main():
    """Entry point for the setup script"""
    try:
        # Clear previous log
        if os.path.exists(SETUP_LOG_FILE):
            os.remove(SETUP_LOG_FILE)
        
        log_message("HAVEN GRAZURI VESTMENT LIMITED- cPanel Setup Script v3.0")
        log_message("LATEST UPDATES: Reports Dashboard Fixed, Enhanced User Management, Staff System")
        log_message("Starting comprehensive setup process...")
        
        success = run_comprehensive_setup()
        
        if success:
            log_message("\n" + "="*60)
            log_message("SETUP COMPLETED SUCCESSFULLY!")
            log_message("="*60)
            log_message("Your HAVEN GRAZURI VESTMENT LIMITEDapplication is ready for production.")
            log_message("\nKEY IMPROVEMENTS IN VERSION 3.0:")
            log_message("✅ Fixed Reports & Statements Dashboard UI and functionality")
            log_message("✅ Enhanced role-based permissions system (6 roles, 160+ permissions)")
            log_message("✅ Improved media file handling for production")
            log_message("✅ Fixed database integrity issues")
            log_message("✅ Enhanced user management with profile images")
            log_message("✅ Staff management system with comprehensive features")
            log_message("✅ All dashboard metrics now display real data")
            log_message("✅ Responsive design improvements")
            log_message("✅ Interactive dashboard with animations")
            log_message("="*60)
        else:
            log_message("\nSetup failed!")
            log_message("Please check the setup log for details and contact support if needed.")
        
        return success
        
    except KeyboardInterrupt:
        log_message("\nSetup interrupted by user", 'WARNING')
        return False
    except Exception as e:
        log_message(f"\nUnexpected error during setup: {e}", 'ERROR')
        return False

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