#!/usr/bin/env python
"""
Fix for production database - Add missing loan_products columns
This script adds missing columns from migration 0005
"""

import os
import sys
import django

# Add the project directory to Python path
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

# Set Django settings
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')

# Setup Django
django.setup()

# Columns that should be added from migration 0005
LOAN_PRODUCT_COLUMNS = [
    ('rollover_fee_percentage', 'DECIMAL(5,2) DEFAULT 5.0'),
    ('max_rollover_count', 'INTEGER DEFAULT 3'),
    ('max_rollover_days', 'INTEGER DEFAULT 30'),
]

def check_column_exists(cursor, table_name, column_name):
    """Check if a column exists in the specified table"""
    cursor.execute("""
        SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = %s 
        AND COLUMN_NAME = %s
    """, [table_name, column_name])
    return cursor.fetchone() is not None

def add_missing_columns():
    """Add missing columns to the loan_products table"""
    print("Checking and adding missing columns to loan_products table...")
    
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            added_count = 0
            
            for column_name, column_type in LOAN_PRODUCT_COLUMNS:
                if not check_column_exists(cursor, 'loan_products', column_name):
                    print(f"Adding column: {column_name}")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE loan_products 
                            ADD COLUMN {column_name} {column_type}
                        """)
                        added_count += 1
                        print(f"✓ Added {column_name}")
                    except Exception as e:
                        print(f"✗ Failed to add {column_name}: {e}")
                else:
                    print(f"✓ {column_name} already exists")
            
            print(f"\nTotal columns added: {added_count}")
            return True
            
    except Exception as e:
        print(f"Error: {e}")
        return False

def mark_migration_applied():
    """Mark the migration as applied in Django's migration history"""
    print("\nMarking migration as applied...")
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check if migration is already recorded
            cursor.execute("""
                SELECT app, name 
                FROM django_migrations 
                WHERE app = 'loans' 
                AND name = '0005_loanproduct_max_rollover_count_and_more'
            """)
            
            if not cursor.fetchone():
                # Add migration record
                cursor.execute("""
                    INSERT INTO django_migrations (app, name, applied) 
                    VALUES ('loans', '0005_loanproduct_max_rollover_count_and_more', NOW())
                """)
                print("✓ Migration marked as applied")
            else:
                print("✓ Migration was already marked as applied")
                
        return True
    except Exception as e:
        print(f"Error marking migration: {e}")
        return False

def check_database_status():
    """Check the current status of the database"""
    print("Checking database status...")
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            cursor.execute("SELECT 1")
            print("Database connection successful!")
            
            # Check all required columns
            missing_columns = []
            for column_name, _ in LOAN_PRODUCT_COLUMNS:
                if not check_column_exists(cursor, 'loan_products', column_name):
                    missing_columns.append(column_name)
            
            if missing_columns:
                print(f"Missing columns in loan_products: {', '.join(missing_columns)}")
                return False
            else:
                print("✓ All required columns exist")
                return True
                
    except Exception as e:
        print(f"Database check failed: {e}")
        return False

if __name__ == '__main__':
    print("Loan Products Database Fix Script")
    print("=" * 40)
    
    # Check current status
    if check_database_status():
        print("\n✓ Database is ready!")
    else:
        print("\n✗ Database needs fixing")
        
        # Try to add missing columns directly
        if add_missing_columns():
            print("\n✓ Database columns added successfully!")
            
            # Mark migration as applied
            if mark_migration_applied():
                print("\n✓ Migration state updated successfully!")
            else:
                print("\n✗ Failed to update migration state")
                sys.exit(1)
        else:
            print("\n✗ Failed to fix database")
            sys.exit(1)
    
    print("\nScript completed successfully!")