#!/usr/bin/env python3
"""
Comprehensive Database Schema Fix for HAVEN GRAZURI Advance
Fixes all missing columns in production database
Run this script to fix immediate production issues
"""

import os
import sys
import django
from datetime import datetime

def log_message(message, level='INFO'):
    """Log messages to console"""
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    print(f"[{timestamp}] {level}: {message}")

def fix_loan_products_table():
    """Fix missing columns in loan_products table"""
    log_message("Fixing loan_products table...")
    
    try:
        from django.db import connection
        
        # Required fields for loan_products table
        required_fields = [
            ('available_durations', 'JSON NULL'),
            ('available_repayment_methods', 'JSON NULL'),
            ('late_payment_penalty', 'DECIMAL(5,2) DEFAULT 5.0'),
            ('duration_months', 'INT DEFAULT 1'),
            ('min_duration', 'INT DEFAULT 7'),
            ('max_duration', 'INT DEFAULT 30'),
            ('is_active', 'BOOLEAN DEFAULT TRUE'),
            ('created_at', 'DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)'),
            ('updated_at', 'DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)'),
        ]
        
        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 = 'loan_products' 
                    AND COLUMN_NAME = %s
                """, [field_name])
                
                if not cursor.fetchone():
                    log_message(f"Adding missing field '{field_name}' to loan_products table...")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE loan_products 
                            ADD COLUMN {field_name} {field_definition}
                        """)
                        log_message(f"SUCCESS: Successfully added {field_name} field")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {field_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {field_name} field already exists")
        
        log_message("SUCCESS: Loan products table fix completed")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing loan_products table: {e}", 'ERROR')
        return False

def fix_loans_table():
    """Fix missing columns in loans table"""
    log_message("Starting loans table fix...")
    
    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:
            log_message("Checking loans table structure...")
            
            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: Successfully added {field_name} field")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {field_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {field_name} field already exists")
            
            # Add essential indexes
            essential_indexes = [
                ('idx_loans_is_deleted', 'loans', 'is_deleted'),
                ('idx_loans_status', 'loans', 'status'),
                ('idx_loans_borrower', 'loans', 'borrower_id'),
            ]
            
            log_message("Adding essential indexes...")
            for index_name, table_name, column_name in essential_indexes:
                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 fix completed")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing loans table: {e}", 'ERROR')
        return False

def fix_loan_applications_table():
    """Fix missing columns in loan_applications table"""
    log_message("Fixing loan_applications table...")
    
    try:
        from django.db import connection
        
        # Required fields for loan_applications table
        required_fields = [
            ('is_deleted', 'BOOLEAN DEFAULT FALSE'),
            ('deleted_at', 'DATETIME(6) NULL'),
            ('deleted_by_id', 'CHAR(32) NULL'),
            ('credit_score', 'INT NULL'),
            ('risk_assessment', 'TEXT NULL'),
            ('collateral_value', 'DECIMAL(12,2) NULL'),
            ('guarantor_id', 'CHAR(32) NULL'),
            ('employment_verification', 'BOOLEAN DEFAULT FALSE'),
            ('income_verification', 'BOOLEAN DEFAULT FALSE'),
        ]
        
        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 = 'loan_applications' 
                    AND COLUMN_NAME = %s
                """, [field_name])
                
                if not cursor.fetchone():
                    log_message(f"Adding missing field '{field_name}' to loan_applications table...")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE loan_applications 
                            ADD COLUMN {field_name} {field_definition}
                        """)
                        log_message(f"SUCCESS: Successfully added {field_name} field")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {field_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {field_name} field already exists")
        
        log_message("SUCCESS: Loan applications table fix completed")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing loan_applications table: {e}", 'ERROR')
        return False

def fix_users_table():
    """Fix missing columns in users table"""
    log_message("Fixing users table...")
    
    try:
        from django.db import connection
        
        # Required fields for users table
        required_fields = [
            ('monthly_income', 'DECIMAL(12,2) NULL'),
            ('employer', 'VARCHAR(200) NULL'),
            ('logbook', 'VARCHAR(255) NULL'),
            ('title_deed', 'VARCHAR(255) NULL'),
            ('signature', 'VARCHAR(255) NULL'),
            ('last_login_at', 'DATETIME(6) NULL'),
            ('is_phone_verified', 'BOOLEAN DEFAULT FALSE'),
            ('is_email_verified', 'BOOLEAN DEFAULT FALSE'),
            ('address', 'TEXT NULL'),
            ('county', 'VARCHAR(100) 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])
                
                if not cursor.fetchone():
                    log_message(f"Adding missing field '{field_name}' to users table...")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE users 
                            ADD COLUMN {field_name} {field_definition}
                        """)
                        log_message(f"SUCCESS: Successfully added {field_name} field")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {field_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {field_name} field already exists")
        
        log_message("SUCCESS: Users table fix completed")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing users table: {e}", 'ERROR')
        return False

def fix_all_missing_tables():
    """Create any missing tables"""
    log_message("Checking for missing tables...")
    
    try:
        from django.db import connection
        
        # Tables that should exist
        required_tables = [
            ('loan_scoring', '''
                CREATE TABLE loan_scoring (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id CHAR(32) NOT NULL,
                    score INT DEFAULT 0,
                    factors JSON NULL,
                    calculated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                    INDEX idx_loan_scoring_user_id (user_id)
                )
            '''),
            ('utils_document', '''
                CREATE TABLE utils_document (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(200) NOT NULL,
                    file VARCHAR(100) NOT NULL,
                    uploaded_by_id CHAR(32) NULL,
                    uploaded_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                    INDEX idx_utils_document_uploaded_by (uploaded_by_id)
                )
            '''),
            ('utils_notification', '''
                CREATE TABLE utils_notification (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id CHAR(32) NOT NULL,
                    title VARCHAR(200) NOT NULL,
                    message TEXT NOT NULL,
                    is_read BOOLEAN DEFAULT FALSE,
                    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                    INDEX idx_utils_notification_user_id (user_id)
                )
            '''),
            ('utils_documentshare', '''
                CREATE TABLE utils_documentshare (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    document_id INT NOT NULL,
                    shared_with_id CHAR(32) NOT NULL,
                    shared_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                    INDEX idx_utils_documentshare_document (document_id),
                    INDEX idx_utils_documentshare_shared_with (shared_with_id)
                )
            '''),
        ]
        
        with connection.cursor() as cursor:
            for table_name, create_sql in required_tables:
                # Check if table exists
                cursor.execute("""
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = %s
                """, [table_name])
                
                if not cursor.fetchone():
                    log_message(f"Creating missing table '{table_name}'...")
                    try:
                        cursor.execute(create_sql)
                        log_message(f"SUCCESS: Successfully created {table_name} table")
                    except Exception as e:
                        log_message(f"ERROR: Failed to create {table_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {table_name} table already exists")
        
        log_message("SUCCESS: Missing tables check completed")
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error checking missing tables: {e}", 'ERROR')
        return False

def main():
    """Main execution"""
    log_message("HAVEN GRAZURI VESTMENT LIMITED- Comprehensive Database Fix")
    log_message("=" * 60)
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        log_message("Django setup completed")
        
        # Run all fixes
        fixes = [
            ("Missing Tables", fix_all_missing_tables),
            ("Loan Products Table", fix_loan_products_table),
            ("Loans Table", fix_loans_table),
            ("Loan Applications Table", fix_loan_applications_table),
            ("Users Table", fix_users_table),
        ]
        
        failed_fixes = []
        
        for fix_name, fix_function in fixes:
            log_message(f"\n--- {fix_name} ---")
            if not fix_function():
                failed_fixes.append(fix_name)
        
        if not failed_fixes:
            log_message("\n" + "=" * 60)
            log_message("SUCCESS: ALL FIXES COMPLETED SUCCESSFULLY!")
            log_message("Your application should now work without database errors.")
            log_message("You can safely delete this script after confirming everything works.")
            log_message("=" * 60)
            return True
        else:
            log_message("\n" + "=" * 60)
            log_message("ERROR: SOME FIXES FAILED!")
            log_message(f"Failed fixes: {', '.join(failed_fixes)}")
            log_message("Please check the error messages above and contact support if needed.")
            log_message("=" * 60)
            return False
            
    except Exception as e:
        log_message(f"\nERROR: Critical error during setup: {e}", 'ERROR')
        return False

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