#!/usr/bin/env python3
"""
Fix script to add missing loan_app_id column to utils_notification table in production.
This addresses the OperationalError: Unknown column 'utils_notification.loan_app_id' in 'field list'
"""

import os
import sys
import django
import pymysql
from django.conf import settings

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

from django.db import connection

def check_column_exists():
    """Check if loan_app_id column exists in utils_notification table"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'utils_notification'
              AND COLUMN_NAME = 'loan_app_id'
        """)
        return cursor.fetchone()[0] > 0

def check_table_exists():
    """Check if utils_notification table exists"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'utils_notification'
        """)
        return cursor.fetchone()[0] > 0

def add_loan_app_column():
    """Add loan_app_id column to utils_notification table"""
    with connection.cursor() as cursor:
        try:
            print("Adding loan_app_id column to utils_notification table...")
            
            # Add the column (using CHAR(32) to match UUID field type)
            cursor.execute("""
                ALTER TABLE utils_notification 
                ADD COLUMN loan_app_id CHAR(32) NULL
            """)
            print("✓ Column loan_app_id added successfully")
            
            # Create index on the column
            cursor.execute("""
                CREATE INDEX utils_notification_loan_app_id_idx 
                ON utils_notification (loan_app_id)
            """)
            print("✓ Index created on loan_app_id column")
            
            # Add foreign key constraint
            cursor.execute("""
                ALTER TABLE utils_notification 
                ADD CONSTRAINT utils_notification_loan_app_id_fk 
                FOREIGN KEY (loan_app_id) REFERENCES loans_loanapplication(id)
            """)
            print("✓ Foreign key constraint added")
            
        except Exception as e:
            print(f"Error adding column: {e}")
            raise

def main():
    print("=== Branch System Notification Table Fix ===")
    print(f"Database: {settings.DATABASES['default']['NAME']}")
    print(f"Host: {settings.DATABASES['default']['HOST']}")
    
    # Check if table exists
    if not check_table_exists():
        print("❌ Error: utils_notification table does not exist!")
        return False
    
    print("✓ utils_notification table exists")
    
    # Check if column already exists
    if check_column_exists():
        print("✓ loan_app_id column already exists - no action needed")
        return True
    
    print("❌ loan_app_id column is missing")
    
    # Auto-apply fix in production environment
    print("Automatically applying fix for missing loan_app_id column...")
    
    try:
        add_loan_app_column()
        print("\n✅ Successfully added loan_app_id column to utils_notification table")
        print("The /utils/notifications/ page should now work correctly")
        return True
        
    except Exception as e:
        print(f"\n❌ Error: {e}")
        print("Please check the database connection and permissions")
        return False

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