from django.utils.timezone import make_aware
from rest_framework.pagination import PageNumberPagination
from rest_framework.response import Response
from django.core.cache import cache
from django.db import transaction
from loans.serializers import *
from .general_helper import filter_loan_savings, filter_loans_balances, fetch_loan_savings_bulk
from questbanker_api.utils import get_current_user
import time
import logging

logger = logging.getLogger(__name__)

def batch_filter_loan_savings(filter_combinations, extra_sql_filters, as_at, extra_report_filter):
    """Batch process multiple filter combinations to reduce database calls"""
    results = {}
    for combo_key, loan_filter in filter_combinations.items():
        results[combo_key] = filter_loan_savings(loan_filter, extra_sql_filters, None, as_at, extra_report_filter)
    return results


def filter_loan_savings_reports(report_filters):
    start_time = time.time()

    # Create cache key from report parameters
    cache_key = f"loan_savings_{hash(str(sorted((k, str(v)) for k, v in report_filters.items() if k != 'request')))}"
    cached_data = cache.get(cache_key)
    if cached_data:
        logger.info(f"Loan savings report served from cache in {time.time() - start_time:.2f}s")
        return Response(cached_data)

    results       = []
    filter_1  = report_filters['filter_1']
    filter_2  = report_filters['filter_2']

    page_size     = report_filters['page_size']
    report_filter = report_filters['report_filter']
    search        = report_filters['search']
    organisation_branch_id = get_current_user(report_filters['request'], 'organisation_branch_id', None)
    as_at = report_filters['as_at']
    extra_sql_filters = " AND arrear_days > 0"

    if search:
        extra_sql_filters = ' AND arrear_days > 0 AND (name LIKE %' + search + '% OR member_number LIKE ' + search + '% OR old_member_number  LIKE ' + search + '% )'

    loan_filter   = {"is_deleted": False, "status": ["disbursed", "cleared_off"], "organisation_id": report_filters["organisation_id"]}
    extra_report_filter = {"outstanding_principal_bal": 0, "report_type": "loan_savings_report"}

    organisation_branch = OrganisationBranch.objects.select_related('branch_organisation').filter(id=organisation_branch_id, can_transact=True).first()
    if organisation_branch and report_filter in ['officer_client_type', 'officer_gender', 'product_client_type', 'product_gender', 'product_officer', 'product_sector']:
        loan_filter["branch_id"] = organisation_branch_id

    paginator = PageNumberPagination()
    paginator.page_size = page_size

    if report_filter == 'officer_client_type':
        results = filter_loan_savings_officer_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'officer_gender':
        results = filter_loan_savings_officer_gender(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'officer_branch':
        results = filter_loan_savings_officer_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'product_client_type':
        results = filter_loan_savings_product_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'product_gender':
        results = filter_loan_savings_product_gender(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'product_branch':
        results = filter_loan_savings_product_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'product_officer':
        results = filter_loan_savings_product_officer(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'product_sector':
        results = filter_loan_savings_product_sector(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'gender_branch':
        results = filter_loan_savings_gender_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    elif report_filter == 'group_branch':
        results = filter_loan_savings_group_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter)

    response_data = {"results": results, "count": len(results)}

    # Cache for 5 minutes — report data doesn't change second-to-second
    cache.set(cache_key, response_data, 300)

    execution_time = time.time() - start_time
    logger.info(f"Loan savings report generated in {execution_time:.2f}s for filter: {report_filters.get('report_filter')}")

    if execution_time > 10:
        logger.warning(f"Slow loan savings report query: {execution_time:.2f}s for {report_filters}")

    return Response(response_data)


def filter_loan_savings_officer_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    staffs = Staff.objects.select_related('staff_organisation').filter(staff_organisation__id=organisation_id, id__in=filter_1)
    customer_types = CustomerType.objects.filter(id__in=filter_2)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_officer_id'] = [int(i) for i in filter_1]
    bulk_filter['customer_type_id'] = [int(ct.id) for ct in customer_types]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_officer_id', 'customer_type_id'])

    for staff in staffs:
        customer_types_list = []
        for customer_type in customer_types:
            loans = grouped.get((staff.id, customer_type.id), [])
            customer_types_list.append({"id": customer_type.id, "name": customer_type.customer_type, "loans": loans})
        response.append({"id": staff.id, "name": staff.name, "data": customer_types_list})
    return response


def filter_loan_savings_officer_gender(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    staffs = Staff.objects.select_related('staff_organisation').filter(staff_organisation__id=organisation_id, id__in=filter_1)
    gender_map = {'M': 'Male', 'F': 'Female', 'O': 'Other'}

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_officer_id'] = [int(i) for i in filter_1]
    bulk_filter['gender'] = list(filter_2)
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_officer_id', 'gender'])

    for staff in staffs:
        gender_list = []
        for gender in filter_2:
            loans = grouped.get((staff.id, gender), [])
            gender_list.append({"id": gender, "name": gender_map.get(gender, gender), "loans": loans})
        response.append({"id": staff.id, "name": staff.name, "data": gender_list})
    return response


def filter_loan_savings_officer_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    branches = OrganisationBranch.objects.select_related('branch_organisation').filter(branch_organisation__id=organisation_id, id__in=filter_2)
    staffs = Staff.objects.select_related('staff_organisation').filter(staff_organisation__id=organisation_id, id__in=filter_1)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_officer_id'] = [int(i) for i in filter_1]
    bulk_filter['branch_id'] = [int(i) for i in filter_2]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['branch_id', 'loan_officer_id'])

    for branch in branches:
        staff_list = []
        for staff in staffs:
            loans = grouped.get((branch.id, staff.id), [])
            staff_list.append({"id": staff.id, "name": staff.name, "loans": loans})
        response.append({"id": branch.id, "name": branch.name, "data": staff_list})
    return response


def filter_loan_savings_product_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    loan_products = LoanProduct.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_1)
    customer_types = CustomerType.objects.filter(id__in=filter_2)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_product_id'] = [int(i) for i in filter_1]
    bulk_filter['customer_type_id'] = [int(ct.id) for ct in customer_types]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_product_id', 'customer_type_id'])

    for loan_product in loan_products:
        customer_types_list = []
        for customer_type in customer_types:
            loans = grouped.get((loan_product.id, customer_type.id), [])
            customer_types_list.append({"id": customer_type.id, "name": customer_type.customer_type, "loans": loans})
        response.append({"id": loan_product.id, "name": loan_product.product_name, "data": customer_types_list})
    return response


def filter_loan_savings_product_gender(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    loan_products = LoanProduct.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_1)
    gender_map = {'M': 'Male', 'F': 'Female', 'O': 'Other'}

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_product_id'] = [int(i) for i in filter_1]
    bulk_filter['gender'] = list(filter_2)
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_product_id', 'gender'])

    for loan_product in loan_products:
        gender_list = []
        for gender in filter_2:
            loans = grouped.get((loan_product.id, gender), [])
            gender_list.append({"id": gender, "name": gender_map.get(gender, gender), "loans": loans})
        response.append({"id": loan_product.id, "name": loan_product.product_name, "data": gender_list})
    return response


def filter_loan_savings_product_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    branches = OrganisationBranch.objects.select_related('branch_organisation').filter(branch_organisation__id=organisation_id, id__in=filter_2)
    loan_products = LoanProduct.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_1)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_product_id'] = [int(i) for i in filter_1]
    bulk_filter['branch_id'] = [int(i) for i in filter_2]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['branch_id', 'loan_product_id'])

    for branch in branches:
        loan_products_list = []
        for loan_product in loan_products:
            loans = grouped.get((branch.id, loan_product.id), [])
            loan_products_list.append({"id": loan_product.id, "name": loan_product.product_name, "loans": loans})
        response.append({"id": branch.id, "name": branch.name, "data": loan_products_list})
    return response


def filter_loan_savings_product_officer(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    staffs = Staff.objects.select_related('staff_organisation').filter(staff_organisation__id=organisation_id, id__in=filter_2)
    loan_products = LoanProduct.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_1)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_product_id'] = [int(i) for i in filter_1]
    bulk_filter['loan_officer_id'] = [int(i) for i in filter_2]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_officer_id', 'loan_product_id'])

    for staff in staffs:
        loan_products_list = []
        for loan_product in loan_products:
            loans = grouped.get((staff.id, loan_product.id), [])
            loan_products_list.append({"id": loan_product.id, "name": loan_product.product_name, "loans": loans})
        response.append({"id": staff.id, "name": staff.name, "data": loan_products_list})
    return response


def filter_loan_savings_product_sector(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    sectors = LoanSectors.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_2)
    loan_products = LoanProduct.objects.select_related('organisation').filter(organisation__id=organisation_id, id__in=filter_1)

    bulk_filter = loan_filter.copy()
    bulk_filter['loan_product_id'] = [int(i) for i in filter_1]
    bulk_filter['loan_sector_id'] = [int(s.id) for s in sectors]
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['loan_sector_id', 'loan_product_id'])

    for sector in sectors:
        loan_products_list = []
        for loan_product in loan_products:
            loans = grouped.get((sector.id, loan_product.id), [])
            loan_products_list.append({"id": loan_product.id, "name": loan_product.product_name, "loans": loans})
        response.append({"id": sector.id, "name": sector.name, "data": loan_products_list})
    return response


def filter_loan_savings_gender_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    branches = OrganisationBranch.objects.select_related('branch_organisation').filter(branch_organisation__id=organisation_id, id__in=filter_1)
    gender_map = {'M': 'Male', 'F': 'Female', 'O': 'Other'}

    bulk_filter = loan_filter.copy()
    bulk_filter['branch_id'] = [int(i) for i in filter_1]
    bulk_filter['gender'] = list(filter_2)
    grouped = fetch_loan_savings_bulk(bulk_filter, extra_sql_filters, as_at, extra_report_filter, ['branch_id', 'gender'])

    for branch in branches:
        gender_list = []
        for gender in filter_2:
            loans = grouped.get((branch.id, gender), [])
            gender_list.append({"id": gender, "name": gender_map.get(gender, gender), "loans": loans})
        response.append({"id": branch.id, "name": branch.name, "data": gender_list})
    return response


def filter_loan_savings_group_branch(filter_1, filter_2, loan_filter, extra_sql_filters, as_at, extra_report_filter):
    response = []
    organisation_id = loan_filter['organisation_id']
    branches = OrganisationBranch.objects.select_related('branch_organisation').filter(branch_organisation__id=organisation_id, id__in=filter_1)
    for branch in branches:
        groups = Customer.objects.select_related('customer_branch').filter(customer_branch=branch, id__in=filter_2)
        groups_list = []
        for group in groups:
            group_members = list(GroupMembership.objects.filter(group=group, active=True).values_list('member__id', flat=True))
            if len(group_members) > 0:
                disbursed_loans = list(LoanApplicationDisbursement.objects.filter(loan_application__loan_group__id=group.id, loan_application__customer__id__in=group_members).values_list('loan_application', flat=True))
                temp_filter = loan_filter.copy()
                if len(disbursed_loans) > 0:
                    temp_filter['id'] = disbursed_loans
                temp_filter['customer_id'] = group_members
                loans_response = filter_loan_savings(temp_filter, extra_sql_filters, None, as_at, extra_report_filter)
                groups_list.append({"id": group.id, "name": group.name, "loans": loans_response})

        response.append({"id": branch.id, "name": branch.name, "data": groups_list})
    return response