#!/usr/bin/env python3
"""
Emergency fix for receipts.repayment_id column issue
This script specifically addresses the OperationalError about missing repayment_id column
"""

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_receipts_repayment_id():
    """Fix the missing repayment_id column in receipts table"""
    log_message("Emergency fix for receipts.repayment_id column...")
    
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check if receipts table exists
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts'
            """)
            
            if not cursor.fetchone():
                log_message("Receipts table doesn't exist. Creating it...")
                cursor.execute("""
                    CREATE TABLE receipts (
                        id CHAR(32) NOT NULL PRIMARY KEY,
                        repayment_id CHAR(32) NOT NULL UNIQUE,
                        loan_id CHAR(32) NOT NULL,
                        borrower_id CHAR(32) NOT NULL,
                        receipt_number VARCHAR(20) NOT NULL UNIQUE,
                        amount_paid DECIMAL(12,2) NOT NULL,
                        payment_method VARCHAR(20) NOT NULL,
                        payment_date DATETIME(6) NOT NULL,
                        previous_balance DECIMAL(12,2) NOT NULL,
                        new_balance DECIMAL(12,2) NOT NULL,
                        pdf_file VARCHAR(100) NULL,
                        created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                        INDEX idx_receipts_repayment_id (repayment_id),
                        INDEX idx_receipts_loan_id (loan_id),
                        INDEX idx_receipts_borrower_id (borrower_id)
                    )
                """)
                log_message("SUCCESS: Created receipts table with repayment_id column")
                return True
            
            # Check if repayment_id column exists
            cursor.execute("""
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts' 
                AND COLUMN_NAME = 'repayment_id'
            """)
            
            if not cursor.fetchone():
                log_message("Adding missing repayment_id column...")
                
                # Add the column
                cursor.execute("""
                    ALTER TABLE receipts 
                    ADD COLUMN repayment_id CHAR(32) NULL
                """)
                
                # Add unique constraint (after adding data if needed)
                try:
                    cursor.execute("""
                        ALTER TABLE receipts 
                        ADD CONSTRAINT uk_receipts_repayment_id UNIQUE (repayment_id)
                    """)
                except Exception as e:
                    log_message(f"Note: Could not add unique constraint: {e}", 'WARNING')
                
                # Add index
                try:
                    cursor.execute("""
                        CREATE INDEX idx_receipts_repayment_id ON receipts (repayment_id)
                    """)
                except Exception as e:
                    log_message(f"Note: Could not create index: {e}", 'WARNING')
                
                log_message("SUCCESS: Added repayment_id column to receipts table")
            else:
                log_message("SUCCESS: repayment_id column already exists")
            
            # Also check if repayments table exists
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'repayments'
            """)
            
            if not cursor.fetchone():
                log_message("Creating missing repayments table...")
                cursor.execute("""
                    CREATE TABLE repayments (
                        id CHAR(32) NOT NULL PRIMARY KEY,
                        loan_id CHAR(32) NOT NULL,
                        amount DECIMAL(12,2) NOT NULL,
                        payment_date DATETIME(6) NOT NULL,
                        payment_method VARCHAR(20) NOT NULL DEFAULT 'mpesa',
                        receipt_number VARCHAR(20) NOT NULL UNIQUE,
                        mpesa_transaction_id VARCHAR(50) NULL,
                        notes TEXT NULL,
                        created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
                        updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
                        INDEX idx_repayments_loan_id (loan_id),
                        INDEX idx_repayments_payment_date (payment_date)
                    )
                """)
                log_message("SUCCESS: Created repayments table")
            
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing receipts table: {e}", 'ERROR')
        return False

def main():
    """Main execution"""
    log_message("HAVEN GRAZURI INVESTMENT LIMITED- Emergency Receipts Fix")
    log_message("=" * 50)
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        log_message("Django setup completed")
        
        if fix_receipts_repayment_id():
            log_message("\n" + "=" * 50)
            log_message("SUCCESS: EMERGENCY FIX COMPLETED!")
            log_message("The receipts.repayment_id column issue has been resolved.")
            log_message("Your loan repayment functionality should now work.")
            log_message("=" * 50)
            return True
        else:
            log_message("\n" + "=" * 50)
            log_message("ERROR: EMERGENCY FIX FAILED!")
            log_message("Please check the error messages above.")
            log_message("=" * 50)
            return False
            
    except Exception as e:
        log_message(f"\nERROR: Critical error: {e}", 'ERROR')
        return False

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