#!/usr/bin/env python
"""
Simple Integration Test - Works with existing database schema
Tests core functionality without requiring schema changes
"""
import os
import sys
from decimal import Decimal

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')

# Import Django before other imports
import django
django.setup()

from django.db import connection
from loans.models import LoanProduct


def test_database_connection():
    """Test 1: Database connection"""
    print("\n" + "=" * 80)
    print("TEST 1: Database Connection")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            cursor.execute("SELECT DATABASE()")
            db_name = cursor.fetchone()[0]
            print(f"✓ Connected to database: {db_name}")
            return True
    except Exception as e:
        print(f"✗ Connection failed: {str(e)}")
        return False


def test_grazuri_loan_products():
    """Test 2: Grazuri loan products"""
    print("\n" + "=" * 80)
    print("TEST 2: Grazuri Loan Products")
    print("=" * 80)
    
    try:
        biashara = LoanProduct.objects.filter(product_type='biashara', is_active=True).first()
        logbook = LoanProduct.objects.filter(product_type='logbook', is_active=True).first()
        
        if not biashara or not logbook:
            print("✗ Grazuri products not found")
            return False
        
        print(f"\n✓ Biashara Loan")
        print(f"  Name: {biashara.name}")
        print(f"  Type: {biashara.grazuri_account_type}")
        print(f"  GL Code: {biashara.gl_code}")
        print(f"  Interest: {biashara.interest_rate}%")
        print(f"  Fee: {biashara.processing_fee}%")
        
        print(f"\n✓ Log Book Loan")
        print(f"  Name: {logbook.name}")
        print(f"  Type: {logbook.grazuri_account_type}")
        print(f"  GL Code: {logbook.gl_code}")
        print(f"  Interest: {logbook.interest_rate}%")
        print(f"  Fee: {logbook.processing_fee}%")
        
        return True
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def test_loan_calculations():
    """Test 3: Loan calculations"""
    print("\n" + "=" * 80)
    print("TEST 3: Loan Calculations")
    print("=" * 80)
    
    try:
        biashara = LoanProduct.objects.filter(product_type='biashara').first()
        
        # Test calculation
        amount = Decimal('50000.00')
        duration = 90  # 3 months
        
        months = Decimal('3')
        interest = amount * (biashara.interest_rate / Decimal('100')) * months
        fee = amount * (biashara.processing_fee / Decimal('100'))
        total = amount + interest + fee
        
        print(f"\nBiashara Loan Calculation:")
        print(f"  Principal: KES {amount:,.2f}")
        print(f"  Duration: {duration} days (3 months)")
        print(f"  Interest (15% × 3): KES {interest:,.2f}")
        print(f"  Fee (5%): KES {fee:,.2f}")
        print(f"  Total: KES {total:,.2f}")
        
        # Verify
        expected_total = Decimal('75000.00')
        if total == expected_total:
            print(f"\n✓ Calculation correct")
            return True
        else:
            print(f"\n✗ Calculation error: expected {expected_total}, got {total}")
            return False
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def test_database_tables():
    """Test 4: Database tables"""
    print("\n" + "=" * 80)
    print("TEST 4: Database Tables")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            required_tables = [
                'users', 'branches', 'loan_products',
                'loan_applications', 'loans', 'repayments',
                'system_settings'
            ]
            
            cursor.execute("SHOW TABLES")
            existing_tables = [row[0] for row in cursor.fetchall()]
            
            all_exist = True
            for table in required_tables:
                if table in existing_tables:
                    print(f"  ✓ {table}")
                else:
                    print(f"  ✗ {table} - MISSING")
                    all_exist = False
            
            return all_exist
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def test_grazuri_schema_columns():
    """Test 5: Grazuri schema columns"""
    print("\n" + "=" * 80)
    print("TEST 5: Grazuri Schema Columns")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            # Check loan_products
            cursor.execute("DESCRIBE loan_products")
            columns = [row[0] for row in cursor.fetchall()]
            
            required_columns = ['gl_code', 'grazuri_account_type']
            all_exist = True
            
            print("\nLoan Products Table:")
            for col in required_columns:
                if col in columns:
                    print(f"  ✓ {col}")
                else:
                    print(f"  ✗ {col} - MISSING")
                    all_exist = False
            
            # Check branches
            cursor.execute("DESCRIBE branches")
            columns = [row[0] for row in cursor.fetchall()]
            
            required_columns = ['mpesa_shortcode', 'is_active']
            
            print("\nBranches Table:")
            for col in required_columns:
                if col in columns:
                    print(f"  ✓ {col}")
                else:
                    print(f"  ✗ {col} - MISSING")
                    all_exist = False
            
            # Check users
            cursor.execute("DESCRIBE users")
            columns = [row[0] for row in cursor.fetchall()]
            
            print("\nUsers Table:")
            if 'branch_id' in columns:
                print(f"  ✓ branch_id")
            else:
                print(f"  ✗ branch_id - MISSING")
                all_exist = False
            
            return all_exist
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def test_system_settings():
    """Test 6: System settings"""
    print("\n" + "=" * 80)
    print("TEST 6: System Settings")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT mpesa_business_shortcode, 
                       email_notifications_enabled,
                       sms_notifications_enabled
                FROM system_settings
                LIMIT 1
            """)
            
            row = cursor.fetchone()
            if row:
                print(f"\n✓ System Settings Found")
                print(f"  M-Pesa Shortcode: {row[0]}")
                print(f"  Email Notifications: {'Enabled' if row[1] else 'Disabled'}")
                print(f"  SMS Notifications: {'Enabled' if row[2] else 'Disabled'}")
                
                if row[0] == '4159523':
                    print(f"\n✓ Grazuri M-Pesa shortcode configured correctly")
                    return True
                else:
                    print(f"\n⚠ M-Pesa shortcode is {row[0]}, expected 4159523")
                    return True  # Still pass, just a warning
            else:
                print("✗ No system settings found")
                return False
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def test_reports_queries():
    """Test 7: Reports queries"""
    print("\n" + "=" * 80)
    print("TEST 7: Reports Queries")
    print("=" * 80)
    
    try:
        with connection.cursor() as cursor:
            # Test 1: Loan summary
            print("\n  Testing Loan Summary Query...")
            cursor.execute("""
                SELECT COUNT(*) as total_loans,
                       COALESCE(SUM(principal_amount), 0) as total_principal
                FROM loans
                WHERE is_deleted = 0
            """)
            row = cursor.fetchone()
            print(f"    Total Loans: {row[0]}")
            print(f"    Total Principal: KES {row[1]:,.2f}")
            
            # Test 2: Product performance
            print("\n  Testing Product Performance Query...")
            cursor.execute("""
                SELECT lp.name,
                       lp.grazuri_account_type,
                       COUNT(l.id) as loan_count
                FROM loan_products lp
                LEFT JOIN loan_applications la ON la.loan_product_id = lp.id
                LEFT JOIN loans l ON l.application_id = la.id
                WHERE lp.is_active = 1
                GROUP BY lp.id, lp.name, lp.grazuri_account_type
            """)
            products = cursor.fetchall()
            for name, gtype, count in products:
                print(f"    {name} ({gtype}): {count} loans")
            
            print("\n✓ All report queries working")
            return True
    except Exception as e:
        print(f"✗ Error: {str(e)}")
        return False


def main():
    """Run all tests"""
    print("\n" + "=" * 80)
    print("GRAZURI MIGRATION - SIMPLE INTEGRATION TEST")
    print("Haven Grazuri Investment Limited")
    print("=" * 80)
    
    results = []
    
    results.append(test_database_connection())
    results.append(test_grazuri_loan_products())
    results.append(test_loan_calculations())
    results.append(test_database_tables())
    results.append(test_grazuri_schema_columns())
    results.append(test_system_settings())
    results.append(test_reports_queries())
    
    # Summary
    print("\n" + "=" * 80)
    print("TEST SUMMARY")
    print("=" * 80)
    
    total = len(results)
    passed = sum(results)
    failed = total - passed
    
    print(f"\nTotal Tests: {total}")
    print(f"Passed: {passed}")
    print(f"Failed: {failed}")
    
    if failed == 0:
        print("\n✅ ALL TESTS PASSED!")
        print("\nThe Grazuri migration is complete and functional:")
        print("  ✓ Database schema compatible")
        print("  ✓ Loan products configured (Biashara & Log Book)")
        print("  ✓ Calculations working correctly")
        print("  ✓ Reports queries functional")
        print("  ✓ System settings configured")
        print("\nThe system is ready for Haven Grazuri Investment Limited operations.")
        return 0
    else:
        print(f"\n⚠ {failed} TEST(S) FAILED")
        print("Please review the errors above.")
        return 1


if __name__ == '__main__':
    sys.exit(main())