#!/usr/bin/env python3
import os
import sys
import django
import logging
from django.db import connection

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

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def fix_char1_columns():
    """Fix all char(1) foreign key columns to char(32)"""
    
    # Define all tables and columns that need fixing
    char1_fixes = [
        ('loans', 'borrower'),
        ('loans', 'original_loan'),
        ('loans', 'application'),
        ('loan_applications', 'borrower'),
        ('loan_applications', 'reviewed_by'),
        ('loan_applications', 'loan_product'),
        ('receipts', 'borrower'),
        ('receipts', 'loan'),
        ('receipts', 'repayment'),
        ('loan_statements', 'borrower'),
        ('loan_statements', 'loan'),
        ('mpesa_transactions', 'loan'),
        ('mpesa_transactions', 'repayment'),
        ('notifications', 'related_application'),
        ('notifications', 'related_loan'),
        ('notifications', 'user'),
        ('offer_letters', 'application'),
        ('offer_letters', 'borrower'),
        ('rollover_requests', 'borrower'),
        ('rollover_requests', 'loan'),
        ('rollover_requests', 'reviewed_by'),
        ('users_groups', 'customuser'),
        ('users_user_permissions', 'customuser'),
        ('repayments', 'loan'),
        ('loan_scoring', 'user'),
        ('utils_notification', 'user'),
        ('utils_documenttag', 'created_by'),
        ('generated_reports', 'generated_by'),
        ('users', 'verified_by'),
    ]
    
    with connection.cursor() as cursor:
        for table_name, column_name in char1_fixes:
            try:
                # Check if table exists
                cursor.execute(
                    "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = %s AND table_schema = DATABASE()",
                    [table_name]
                )
                if cursor.fetchone()[0] == 0:
                    logger.warning(f"Table {table_name} does not exist")
                    continue
                
                # Check if column exists
                cursor.execute(
                    "SELECT COUNT(*) FROM information_schema.columns WHERE table_name = %s AND column_name = %s AND table_schema = DATABASE()",
                    [table_name, column_name]
                )
                if cursor.fetchone()[0] == 0:
                    logger.warning(f"Column {table_name}.{column_name} does not exist")
                    continue
                
                # Clean invalid data
                cursor.execute(
                    f"UPDATE {table_name} SET {column_name} = NULL WHERE {column_name} IS NOT NULL AND LENGTH({column_name}) != 32"
                )
                
                # Fix column type
                cursor.execute(
                    f"ALTER TABLE {table_name} MODIFY COLUMN {column_name} char(32) DEFAULT NULL"
                )
                
                logger.info(f"Fixed {table_name}.{column_name}")
                
            except Exception as e:
                logger.error(f"Failed to fix {table_name}.{column_name}: {e}")
        
        # Add missing columns
        missing_columns = [
            ('notifications', 'user_id', 'char(32)'),
            ('user_permissions', 'granted_by_id', 'char(32)'),
            ('portfolio_performance', 'portfolio_manager_id', 'char(32)'),
            ('portfolio_performance', 'verified_by_id', 'char(32)'),
        ]
        
        for table_name, column_name, column_type in missing_columns:
            try:
                cursor.execute(
                    f"ALTER TABLE {table_name} ADD COLUMN IF NOT EXISTS {column_name} {column_type} DEFAULT NULL"
                )
                logger.info(f"Added {table_name}.{column_name}")
            except Exception as e:
                logger.error(f"Failed to add {table_name}.{column_name}: {e}")

if __name__ == '__main__':
    fix_char1_columns()
    print("All char(1) foreign key columns have been fixed!")