#!/usr/bin/env python
"""
Comprehensive Report Validation Script
Validates that all financial numbers are being calculated correctly
"""

import os
import sys
import django
from datetime import date, datetime
from decimal import Decimal

# Setup Django
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from loans.models import Loan, Repayment
from expenses.models import Expense
from django.db.models import Sum, Count

def print_header(title):
    print("\n" + "="*70)
    print(f"  {title}")
    print("="*70)

def format_kes(amount):
    """Format amount as KES"""
    if amount is None:
        return "KES 0.00"
    return f"KES {amount:,.2f}"

def validate_loans():
    """Validate loan portfolio numbers"""
    print_header("LOAN PORTFOLIO VALIDATION")
    
    # Active loans
    active_loans = Loan.objects.filter(is_deleted=False, status='active')
    active_count = active_loans.count()
    
    # Portfolio value (principal)
    portfolio_value = active_loans.aggregate(total=Sum('principal_amount'))['total'] or Decimal('0.00')
    
    # Outstanding balance
    outstanding = Decimal('0.00')
    for loan in active_loans:
        outstanding += loan.outstanding_amount
    
    # Collection rate
    total_expected = Decimal('0.00')
    total_collected = Decimal('0.00')
    for loan in active_loans:
        total_expected += loan.principal_amount + loan.interest_amount
        total_collected += loan.amount_paid
    
    collection_rate = (total_collected / total_expected * 100) if total_expected > 0 else Decimal('0.00')
    
    print(f"\n[OK] Active Loans: {active_count}")
    print(f"[OK] Portfolio Value: {format_kes(portfolio_value)}")
    print(f"[OK] Outstanding Balance: {format_kes(outstanding)}")
    print(f"[OK] Collection Rate: {collection_rate:.1f}%")
    
    return {
        'active_count': active_count,
        'portfolio_value': portfolio_value,
        'outstanding': outstanding,
        'collection_rate': collection_rate
    }

def validate_expenses():
    """Validate expense numbers"""
    print_header("EXPENSE VALIDATION")
    
    # All approved expenses
    all_expenses = Expense.objects.filter(status='approved')
    total_all_time = all_expenses.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    
    # This month
    today = date.today()
    month_start = today.replace(day=1)
    this_month = Expense.objects.filter(
        status='approved',
        expense_date__gte=month_start,
        expense_date__lte=today
    )
    total_this_month = this_month.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    count_this_month = this_month.count()
    
    # This year
    year_start = today.replace(month=1, day=1)
    this_year = Expense.objects.filter(
        status='approved',
        expense_date__gte=year_start,
        expense_date__lte=today
    )
    total_this_year = this_year.aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    count_this_year = this_year.count()
    
    # By category
    by_category = Expense.objects.filter(status='approved').values('category').annotate(
        total=Sum('amount'),
        count=Count('id')
    ).order_by('-total')
    
    print(f"\n[OK] Total All-Time: {format_kes(total_all_time)}")
    print(f"[OK] This Month ({month_start} to {today}): {format_kes(total_this_month)} ({count_this_month} expenses)")
    print(f"[OK] This Year ({year_start} to {today}): {format_kes(total_this_year)} ({count_this_year} expenses)")
    
    print(f"\n[OK][OK] By Category:")
    for cat in by_category[:5]:
        print(f"   • {cat['category']}: {format_kes(cat['total'])} ({cat['count']} expenses)")
    
    return {
        'total_all_time': total_all_time,
        'total_this_month': total_this_month,
        'total_this_year': total_this_year,
        'by_category': list(by_category)
    }

def validate_income():
    """Validate income numbers"""
    print_header("INCOME VALIDATION")
    
    today = date.today()
    month_start = today.replace(day=1)
    year_start = today.replace(month=1, day=1)
    
    # This month
    loans_this_month = Loan.objects.filter(
        is_deleted=False,
        disbursement_date__date__gte=month_start,
        disbursement_date__date__lte=today
    )
    
    month_agg = loans_this_month.aggregate(
        interest=Sum('interest_amount'),
        processing=Sum('processing_fee'),
        registration=Sum('registration_fee'),
        count=Count('id')
    )
    
    interest_this_month = month_agg['interest'] or Decimal('0.00')
    processing_this_month = month_agg['processing'] or Decimal('0.00')
    registration_this_month = month_agg['registration'] or Decimal('0.00')
    loans_count_this_month = month_agg['count']
    
    # This year
    loans_this_year = Loan.objects.filter(
        is_deleted=False,
        disbursement_date__date__gte=year_start,
        disbursement_date__date__lte=today
    )
    
    year_agg = loans_this_year.aggregate(
        interest=Sum('interest_amount'),
        processing=Sum('processing_fee'),
        registration=Sum('registration_fee'),
        count=Count('id')
    )
    
    interest_this_year = year_agg['interest'] or Decimal('0.00')
    processing_this_year = year_agg['processing'] or Decimal('0.00')
    registration_this_year = year_agg['registration'] or Decimal('0.00')
    loans_count_this_year = year_agg['count']
    
    print(f"\n[OK][OK] THIS MONTH ({month_start} to {today}):")
    print(f"   [OK] Interest Income: {format_kes(interest_this_month)}")
    print(f"   [OK] Processing Fees: {format_kes(processing_this_month)}")
    print(f"   [OK] Registration Fees: {format_kes(registration_this_month)}")
    print(f"   [OK] Total Income: {format_kes(interest_this_month + processing_this_month + registration_this_month)}")
    print(f"   [OK] Loans Processed: {loans_count_this_month}")
    
    print(f"\n[OK][OK] THIS YEAR ({year_start} to {today}):")
    print(f"   [OK] Interest Income: {format_kes(interest_this_year)}")
    print(f"   [OK] Processing Fees: {format_kes(processing_this_year)}")
    print(f"   [OK] Registration Fees: {format_kes(registration_this_year)}")
    print(f"   [OK] Total Income: {format_kes(interest_this_year + processing_this_year + registration_this_year)}")
    print(f"   [OK] Loans Processed: {loans_count_this_year}")
    
    return {
        'this_month': {
            'interest': interest_this_month,
            'processing': processing_this_month,
            'registration': registration_this_month,
            'count': loans_count_this_month
        },
        'this_year': {
            'interest': interest_this_year,
            'processing': processing_this_year,
            'registration': registration_this_year,
            'count': loans_count_this_year
        }
    }

def validate_accounting_system():
    """Validate Chart of Accounts balances"""
    print_header("CHART OF ACCOUNTS VALIDATION")
    
    try:
        from accounting.models import Account
        from accounting.services.accounting_service import AccountingService
        
        accounting_service = AccountingService()
        as_of_date = date.today()
        
        # Count accounts
        total_accounts = Account.objects.count()
        active_accounts = Account.objects.filter(is_active=True).count()
        
        print(f"\n[OK] Total Accounts: {total_accounts}")
        print(f"[OK] Active Accounts: {active_accounts}")
        
        # Check balances by type
        for account_type, label in [
            ('asset', 'Assets'),
            ('liability', 'Liabilities'),
            ('equity', 'Equity'),
            ('income', 'Income'),
            ('expense', 'Expenses')
        ]:
            accounts = Account.objects.filter(account_type=account_type, is_active=True)
            count = accounts.count()
            total_balance = Decimal('0.00')
            
            for account in accounts:
                try:
                    balance = accounting_service.calculate_account_balance(account, as_of_date, None)
                    total_balance += abs(balance)
                except Exception as e:
                    print(f"   [OK][OK] Error calculating balance for {account.code}: {e}")
            
            print(f"\n[OK][OK] {label}:")
            print(f"   • Count: {count} accounts")
            print(f"   • Total Balance: {format_kes(total_balance)}")
            
            # Show top 3 accounts
            for account in accounts[:3]:
                try:
                    balance = accounting_service.calculate_account_balance(account, as_of_date, None)
                    print(f"   • {account.code} - {account.name}: {format_kes(balance)}")
                except:
                    pass
        
        return True
        
    except ImportError:
        print("\n[OK][OK] Accounting system not available")
        return False
    except Exception as e:
        print(f"\n[OK] Error validating accounting system: {e}")
        return False

def validate_profit_loss_report():
    """Validate P&L report calculation"""
    print_header("PROFIT & LOSS REPORT VALIDATION")
    
    try:
        from reports.financial_reports_service import get_profit_and_loss
        
        today = date.today()
        month_start = today.replace(day=1)
        
        print(f"\n[OK][OK] Testing P&L for period: {month_start} to {today}")
        
        pl_data = get_profit_and_loss(month_start, today, None)
        
        print(f"\n[OK] Data Source: {pl_data.get('source', 'unknown')}")
        print(f"\n[OK][OK] INCOME:")
        income = pl_data.get('income', {})
        print(f"   • Interest: {format_kes(income.get('interest_income', 0))}")
        print(f"   • Processing Fees: {format_kes(income.get('processing_fee_income', 0))}")
        print(f"   • Registration Fees: {format_kes(income.get('registration_fee_income', 0))}")
        print(f"   • Penalties: {format_kes(income.get('penalty_income', 0))}")
        print(f"   • TOTAL: {format_kes(income.get('total', 0))}")
        
        print(f"\n[OK][OK] EXPENSES:")
        expenses = pl_data.get('expenses', {})
        print(f"   • Total Operational: {format_kes(expenses.get('total_operational', 0))}")
        if expenses.get('by_category'):
            print(f"   • By Category:")
            for cat in expenses['by_category'][:5]:
                print(f"     - {cat.get('category', 'Unknown')}: {format_kes(cat.get('total', 0))}")
        
        print(f"\n[OK][OK] NET PROFIT/LOSS: {format_kes(pl_data.get('net_profit', 0))}")
        
        return pl_data
        
    except Exception as e:
        print(f"\n[OK] Error validating P&L report: {e}")
        import traceback
        traceback.print_exc()
        return None

def main():
    """Run all validations"""
    print("\n" + "="*70)
    print("  COMPREHENSIVE FINANCIAL REPORTS VALIDATION")
    print("  Haven Grazuri Investment Limited")
    print("="*70)
    print(f"\nValidation Date: {datetime.now().strftime('%B %d, %Y at %I:%M %p')}")
    
    # Run validations
    loan_data = validate_loans()
    expense_data = validate_expenses()
    income_data = validate_income()
    accounting_ok = validate_accounting_system()
    pl_data = validate_profit_loss_report()
    
    # Summary
    print_header("VALIDATION SUMMARY")
    
    print(f"\n[OK] Portfolio: {loan_data['active_count']} loans, {format_kes(loan_data['portfolio_value'])}")
    print(f"[OK] Expenses This Month: {format_kes(expense_data['total_this_month'])}")
    print(f"[OK] Expenses This Year: {format_kes(expense_data['total_this_year'])}")
    print(f"[OK] Income This Month: {format_kes(income_data['this_month']['interest'] + income_data['this_month']['processing'] + income_data['this_month']['registration'])}")
    print(f"[OK] Accounting System: {'Available' if accounting_ok else 'Not Available'}")
    
    if pl_data:
        print(f"[OK] P&L Report: Working (source: {pl_data.get('source')})")
        
        # Check if numbers match
        expected_expenses = expense_data['total_this_month']
        pl_expenses = pl_data.get('expenses', {}).get('total_operational', Decimal('0.00'))
        
        if expected_expenses != pl_expenses:
            print(f"\n[OK][OK] WARNING: Expense mismatch!")
            print(f"   Expected: {format_kes(expected_expenses)}")
            print(f"   P&L Shows: {format_kes(pl_expenses)}")
            print(f"   Difference: {format_kes(abs(expected_expenses - pl_expenses))}")
    else:
        print(f"[OK] P&L Report: Error")
    
    print("\n" + "="*70 + "\n")
    
    return 0

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