#!/usr/bin/env python3
"""
Complete fix for all receipts table issues - ASCII version
This script fixes all missing columns in the receipts table without Unicode characters
"""

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_table_complete():
    """Complete fix for receipts table - recreate if necessary"""
    log_message("Complete fix for receipts table...")
    
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            # Check current table structure
            cursor.execute("""
                SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 'receipts'
                ORDER BY ORDINAL_POSITION
            """)
            
            existing_columns = cursor.fetchall()
            log_message(f"Found {len(existing_columns)} existing columns in receipts table")
            
            if existing_columns:
                for col in existing_columns:
                    log_message(f"  - {col[0]} ({col[1]}, nullable: {col[2]})")
            
            # Required columns with their definitions
            required_columns = {
                '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',
                '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)'
            }
            
            existing_column_names = [col[0] for col in existing_columns] if existing_columns else []
            missing_columns = []
            
            for col_name in required_columns:
                if col_name not in existing_column_names:
                    missing_columns.append(col_name)
            
            if not existing_columns:
                # Table doesn't exist, create it
                log_message("Creating receipts table from scratch...")
                create_sql = f"""
                    CREATE TABLE receipts (
                        {', '.join([f'{name} {definition}' for name, definition in required_columns.items()])}
                    )
                """
                cursor.execute(create_sql)
                log_message("SUCCESS: Created receipts table")
                
            elif missing_columns:
                # Add missing columns
                log_message(f"Adding {len(missing_columns)} missing columns...")
                for col_name in missing_columns:
                    log_message(f"Adding {col_name}...")
                    try:
                        # Skip PRIMARY KEY constraint when adding to existing table
                        definition = required_columns[col_name].replace(' PRIMARY KEY', '')
                        cursor.execute(f"""
                            ALTER TABLE receipts 
                            ADD COLUMN {col_name} {definition}
                        """)
                        log_message(f"SUCCESS: Added {col_name}")
                    except Exception as e:
                        log_message(f"ERROR: Failed to add {col_name}: {e}", 'ERROR')
                        return False
            else:
                log_message("SUCCESS: All required columns exist")
            
            # Add essential indexes
            indexes = [
                ('idx_receipts_repayment_id', 'repayment_id'),
                ('idx_receipts_loan_id', 'loan_id'),
                ('idx_receipts_borrower_id', 'borrower_id'),
                ('idx_receipts_receipt_number', 'receipt_number'),
                ('idx_receipts_payment_date', 'payment_date'),
            ]
            
            for index_name, column_name in indexes:
                try:
                    cursor.execute(f"""
                        CREATE INDEX {index_name} ON receipts ({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')
            
            # Add unique constraint on receipt_number
            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 already exists")
                else:
                    log_message(f"WARNING: Could not add unique constraint: {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 fix_repayments_table():
    """Ensure repayments table exists with correct structure"""
    log_message("Checking repayments table...")
    
    try:
        from django.db import connection
        
        with connection.cursor() as cursor:
            # 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 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),
                        INDEX idx_repayments_loan_id (loan_id),
                        INDEX idx_repayments_receipt_number (receipt_number),
                        INDEX idx_repayments_payment_date (payment_date)
                    )
                """)
                log_message("SUCCESS: Created repayments table")
            else:
                log_message("SUCCESS: Repayments table exists")
        
        return True
        
    except Exception as e:
        log_message(f"ERROR: Error with repayments table: {e}", 'ERROR')
        return False

def main():
    """Main execution"""
    log_message("HAVEN GRAZURI INVESTMENT LIMITED- Complete Receipts Fix")
    log_message("=" * 60)
    
    try:
        # Setup Django
        os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings_production')
        django.setup()
        
        log_message("Django setup completed")
        
        # Fix both tables
        repayments_ok = fix_repayments_table()
        receipts_ok = fix_receipts_table_complete()
        
        if repayments_ok and receipts_ok:
            log_message("\n" + "=" * 60)
            log_message("SUCCESS: COMPLETE FIX SUCCESSFUL!")
            log_message("All receipts table issues have been resolved.")
            log_message("Your loan repayment functionality should now work perfectly.")
            log_message("=" * 60)
            return True
        else:
            log_message("\n" + "=" * 60)
            log_message("ERROR: SOME FIXES FAILED!")
            log_message("Please check the error messages above.")
            log_message("=" * 60)
            return False
            
    except Exception as e:
        log_message(f"\nERROR: Critical error: {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)