#!/usr/bin/env python3
"""
Production fix for receipts.repayment_id column issue
Run this on your production server to fix the OperationalError
"""

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_production_receipts():
    """Fix all missing columns in production receipts table"""
    log_message("Fixing production receipts table...")
    
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            # First, check if receipts table exists
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts'
            """)
            
            table_exists = cursor.fetchone()
            
            if not table_exists:
                log_message("Creating receipts table...")
                cursor.execute("""
                    CREATE TABLE receipts (
                        id CHAR(32) NOT NULL PRIMARY KEY,
                        repayment_id CHAR(32) NULL,
                        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)
                    )
                """)
                log_message("SUCCESS: Created receipts table")
                return True
            
            log_message("SUCCESS: Receipts table exists, checking for missing columns...")
            
            # Required columns for receipts table
            required_columns = [
                ('repayment_id', 'CHAR(32) NULL'),
                ('loan_id', 'CHAR(32) NOT NULL'),
                ('borrower_id', 'CHAR(32) NOT NULL'),
                ('receipt_number', 'VARCHAR(20) NOT NULL'),
                ('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)'),
            ]
            
            # Check and add missing columns
            for column_name, column_definition in required_columns:
                cursor.execute("""
                    SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = DATABASE() 
                    AND TABLE_NAME = 'receipts' 
                    AND COLUMN_NAME = %s
                """, [column_name])
                
                if not cursor.fetchone():
                    log_message(f"Adding missing {column_name} column...")
                    try:
                        cursor.execute(f"""
                            ALTER TABLE receipts 
                            ADD COLUMN {column_name} {column_definition}
                        """)
                        log_message(f"SUCCESS: Added {column_name} column")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {column_name}: {e}", 'ERROR')
                        return False
                else:
                    log_message(f"SUCCESS: {column_name} column exists")
            
            # Add unique constraint on receipt_number if it doesn't exist
            try:
                cursor.execute("""
                    ALTER TABLE receipts 
                    ADD CONSTRAINT uk_receipts_receipt_number UNIQUE (receipt_number)
                """)
                log_message("SUCCESS: Added unique constraint on receipt_number")
            except Exception as e:
                if "Duplicate key name" in str(e) or "already exists" in str(e):
                    log_message("SUCCESS: Unique constraint on receipt_number already exists")
                else:
                    log_message(f"Note: Could not add unique constraint: {e}", 'WARNING')
            
            # Also ensure 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 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_method VARCHAR(20) NOT NULL DEFAULT 'mpesa',
                        mpesa_transaction_id VARCHAR(50) NULL,
                        mpesa_phone_number VARCHAR(17) NULL,
                        receipt_number VARCHAR(20) NOT NULL UNIQUE,
                        payment_date DATETIME(6) NOT NULL,
                        created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
                    )
                """)
                log_message("SUCCESS: Created repayments table")
            else:
                log_message("SUCCESS: Repayments table exists")
            
            # Add essential indexes if they don't exist
            essential_indexes = [
                ('idx_receipts_loan_id', 'receipts', 'loan_id'),
                ('idx_receipts_borrower_id', 'receipts', 'borrower_id'),
                ('idx_receipts_receipt_number', 'receipts', 'receipt_number'),
                ('idx_repayments_loan_id', 'repayments', 'loan_id'),
                ('idx_repayments_receipt_number', 'repayments', 'receipt_number'),
            ]
            
            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) or "already exists" 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')
        
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error fixing receipts table: {e}", 'ERROR')
        import traceback
        log_message(f"Traceback: {traceback.format_exc()}", 'ERROR')
        return False

def main():
    """Main execution"""
    log_message("HAVEN GRAZURI INVESTMENT LIMITED- Production Receipts Fix")
    log_message("=" * 55)
    
    try:
        # Setup Django with production settings
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        log_message("Django setup completed")
        
        if fix_production_receipts():
            log_message("\n" + "=" * 55)
            log_message("SUCCESS: PRODUCTION FIX COMPLETED SUCCESSFULLY!")
            log_message("The receipts.repayment_id column issue has been resolved.")
            log_message("Your loan repayment functionality should now work.")
            log_message("You can now process loan repayments without errors.")
            log_message("=" * 55)
            return True
        else:
            log_message("\n" + "=" * 55)
            log_message("ERROR: PRODUCTION FIX FAILED!")
            log_message("Please check the error messages above.")
            log_message("Contact support if the issue persists.")
            log_message("=" * 55)
            return False
            
    except Exception as e:
        log_message(f"\nERROR: Critical error during setup: {e}", 'ERROR')
        import traceback
        log_message(f"Traceback: {traceback.format_exc()}", 'ERROR')
        return False

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