"""
Dashboard Analytics Helper Functions
Provides data aggregation functions for the dashboard analytics API
"""

from django.db.models import Count, Sum, Q, F
from django.utils import timezone
from datetime import datetime, timedelta
from customers.models import Customer
from savings.models import SavingAccount, SavingAccountTransactions
from shares.models import SharesTransaction, ShareHolders
from loans.models import LoanApplication, LoanApplicationApproval, LoanApplicationDisbursement, LoanRepaymentview
from ledgers.models import OrganisationSubAccount, Currencies
from organisations.models import Organisation
from django.utils.timezone import make_aware


def get_customers_by_type(dashboard_summary, filters):
    """
    Get customer statistics by type
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    start = filters.get('start')
    end = filters.get('end')
    
    # Base query for customers
    customer_query = Customer.objects.filter(
        branch_customer_type__organisation=organisation_id,
        is_deleted=False
    )
    
    # Apply branch filter if specified
    if branch_id and branch_id != '0' and branch_id != 0:
        customer_query = customer_query.filter(customer_branch_id=branch_id)
    
    # Apply date filter if specified
    if start and end:
        customer_query = customer_query.filter(
            date_added__date__gte=start,
            date_added__date__lte=end
        )
    
    # Get customer types with counts
    customer_types = customer_query.values(
        'branch_customer_type__customer_type'
    ).annotate(
        total=Count('id')
    ).order_by('-total')
    
    # Format customer data
    customers = []
    total_customers = 0
    
    for customer_type in customer_types:
        customers.append({
            'customer_type': customer_type['branch_customer_type__customer_type'],
            'total': customer_type['total']
        })
        total_customers += customer_type['total']
    
    dashboard_summary['customers'] = customers
    dashboard_summary['customer_totals'] = total_customers
    dashboard_summary['customer_types'] = [ct['branch_customer_type__customer_type'] for ct in customer_types]
    
    return dashboard_summary


def get_customers_by_gender(dashboard_summary, filters):
    """
    Get customer statistics by gender
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    
    # Base query for customers
    customer_query = Customer.objects.filter(
        branch_customer_type__organisation=organisation_id,
        is_deleted=False
    )
    
    # Apply branch filter if specified
    if branch_id and branch_id != '0' and branch_id != 0:
        customer_query = customer_query.filter(customer_branch_id=branch_id)
    
    # Get gender statistics
    male_count = customer_query.filter(gender='M').count()
    female_count = customer_query.filter(gender='F').count()
    other_count = customer_query.filter(gender='O').count()
    
    total_gender = male_count + female_count + other_count
    
    dashboard_summary['male_count'] = male_count
    dashboard_summary['female_count'] = female_count
    dashboard_summary['other_count'] = other_count
    dashboard_summary['male_total'] = male_count
    dashboard_summary['female_total'] = female_count
    dashboard_summary['other_total'] = other_count
    dashboard_summary['all_gender_totals'] = total_gender
    
    return dashboard_summary


def get_customers_savings_summary(dashboard_summary, filters):
    """
    Get savings account statistics
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    start = filters.get('start')
    end = filters.get('end')
    
    # Base query for savings accounts
    savings_query = SavingAccount.objects.filter(
        customer_branch__branch_organisation_id=organisation_id,
        deleted=False
    )
    
    # Apply branch filter if specified
    if branch_id and branch_id != '0' and branch_id != 0:
        savings_query = savings_query.filter(customer_branch_id=branch_id)
    
    # Account status counts (all-time, not period-filtered — mirrors BMC dashboard)
    dashboard_summary['savings_active']   = savings_query.filter(status='active').count()
    dashboard_summary['savings_dormant']  = savings_query.filter(status='dormant').count()
    dashboard_summary['savings_inactive'] = savings_query.filter(status='inactive').count()
    dashboard_summary['savings_total_count'] = savings_query.count()

    # Transactions — filter via transaction__branch (SystemTransactions.branch FK),
    # which is the path used by savings/views.py SavingDepositsView
    base_txn = SavingAccountTransactions.objects.filter(
        transaction__branch__branch_organisation_id=organisation_id,
        deleted=False
    )
    if branch_id and branch_id != '0' and branch_id != 0:
        base_txn = base_txn.filter(transaction__branch_id=branch_id)
    if start and end:
        base_txn = base_txn.filter(
            transaction__record_date__date__gte=start,
            transaction__record_date__date__lte=end
        )

    deposits_qs    = base_txn.filter(transaction_type='deposit')
    withdrawals_qs = base_txn.filter(transaction_type='withdrawal')

    deposits_count    = deposits_qs.count()
    deposits_amount   = deposits_qs.aggregate(total=Sum('transaction__amount'))['total'] or 0
    withdrawals_count = withdrawals_qs.count()
    withdrawals_amount = withdrawals_qs.aggregate(total=Sum('transaction__amount'))['total'] or 0

    dashboard_summary['deposits_count']   = deposits_count
    dashboard_summary['deposits']         = deposits_amount
    dashboard_summary['withdrwals_count'] = withdrawals_count
    dashboard_summary['withdrwals']       = withdrawals_amount
    dashboard_summary['savings_total']    = deposits_amount + withdrawals_amount

    return dashboard_summary


def get_customers_shares_summary(dashboard_summary, filters):
    """
    Get shares transaction statistics
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    start = filters.get('start')
    end = filters.get('end')
    
    # Filter by org via organisation_branch__branch_organisation_id (confirmed in shares/views.py)
    shares_query = SharesTransaction.objects.filter(
        organisation_branch__branch_organisation_id=organisation_id,
        deleted=False
    )
    if branch_id and branch_id != '0' and branch_id != 0:
        shares_query = shares_query.filter(organisation_branch_id=branch_id)
    # date_added is the actual DateTimeField on SharesTransaction (no transaction_date field)
    if start and end:
        shares_query = shares_query.filter(
            date_added__date__gte=start,
            date_added__date__lte=end
        )

    purchases_qs   = shares_query.filter(transaction_type='purchase')
    # withdrawal + transfer-out are both outflows (mirrors BMC)
    withdrawals_qs = shares_query.filter(transaction_type__in=['withdrawal', 'transfer-out'])

    # SharesTransaction has no direct amount field — money is on system_transaction__amount
    share_purchases_count    = purchases_qs.count()
    share_purchases_amount   = purchases_qs.aggregate(total=Sum('system_transaction__amount'))['total'] or 0
    share_withdrawals_count  = withdrawals_qs.count()
    share_withdrawals_amount = withdrawals_qs.aggregate(total=Sum('system_transaction__amount'))['total'] or 0

    dashboard_summary['shares_total_count']     = share_purchases_count + share_withdrawals_count
    dashboard_summary['share_purchases_count']  = share_purchases_count
    dashboard_summary['share_purchases']        = share_purchases_amount
    dashboard_summary['share_withdrwals_count'] = share_withdrawals_count
    dashboard_summary['share_withdrwals']       = share_withdrawals_amount
    dashboard_summary['shares_total']           = share_purchases_amount + share_withdrawals_amount

    return dashboard_summary


def get_customers_loan_summary(dashboard_summary, filters):
    """
    Get loan application statistics
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    start = filters.get('start')
    end = filters.get('end')
    
    # Base query for loan applications
    loans_query = LoanApplication.objects.filter(
        customer__branch_customer_type__organisation_id=organisation_id,
        is_deleted=False
    )
    
    # Apply branch filter if specified
    if branch_id and branch_id != '0' and branch_id != 0:
        loans_query = loans_query.filter(organisation_branch_id=branch_id)
    
    # Apply date filter
    if start and end:
        loans_query = loans_query.filter(
            date_added__date__gte=start,
            date_added__date__lte=end
        )
    
    # Get loan applications awaiting approval
    loan_applications_query = loans_query.filter(status='pending')
    loan_applications_count = loan_applications_query.count()
    loan_applications_amount = loan_applications_query.aggregate(total=Sum('loan_amount'))['total'] or 0
    
    # Get approved loans awaiting disbursement
    loans_approved_query = loans_query.filter(status='approved')
    loans_approved_count = loans_approved_query.count()
    loans_approved_amount = loans_approved_query.aggregate(total=Sum('loan_amount'))['total'] or 0
    
    # Get disbursed loans
    loans_disbursed_query = loans_query.filter(status='disbursed')
    loans_disbursed_count = loans_disbursed_query.count()
    loans_disbursed_amount = loans_disbursed_query.aggregate(total=Sum('loan_amount'))['total'] or 0
    
    # Get overdue loans (simplified - you may need to implement proper overdue logic)
    loans_due_query = loans_query.filter(status='disbursed')  # This is simplified
    loans_due_count = loans_due_query.count()
    loans_due_amount = loans_due_query.aggregate(total=Sum('loan_amount'))['total'] or 0
    
    dashboard_summary['loan_applications_count'] = loan_applications_count
    dashboard_summary['loan_applications'] = loan_applications_amount
    dashboard_summary['loans_approved_count'] = loans_approved_count
    dashboard_summary['loans_approved'] = loans_approved_amount
    dashboard_summary['loans_disbursed_count'] = loans_disbursed_count
    dashboard_summary['loans_disbursed'] = loans_disbursed_amount
    dashboard_summary['loans_due_count'] = loans_due_count
    dashboard_summary['loans_due'] = loans_due_amount
    
    return dashboard_summary


def get_income_statement(dashboard_summary, filters):
    """
    Get income statement data for the dashboard
    """
    organisation_id = filters['organisation_id']
    branch_id = filters.get('branch_id')
    start = filters.get('start')
    end = filters.get('end')
    
    # This is a simplified implementation
    # You may need to implement proper income statement logic based on your chart of accounts
    
    income_data = {
        'income': [
            {
                'transactions': [
                    {
                        'sub_accounts': [
                            {
                                'balance_btn': {
                                    'balance_raw': 0  # Placeholder - implement actual income calculation
                                }
                            }
                        ]
                    }
                ]
            }
        ],
        'expenses': [
            {
                'transactions': [
                    {
                        'sub_accounts': [
                            {
                                'balance_btn': {
                                    'balance_raw': 0  # Placeholder - implement actual expense calculation
                                }
                            }
                        ]
                    }
                ]
            }
        ]
    }
    
    dashboard_summary['income_statement'] = income_data
    
    return dashboard_summary


def get_currency_code(organisation_id):
    """
    Get the currency code for the organisation
    """
    try:
        organisation = Organisation.objects.get(pk=organisation_id)
        currency = Currencies.objects.get(pk=organisation.base_currency)
        return currency.currency_code
    except (Organisation.DoesNotExist, Currencies.DoesNotExist):
        return 'UGX'  # Default currency