from django.utils.timezone import make_aware
from rest_framework.pagination import PageNumberPagination
from rest_framework.response import Response
from loans.serializers import *
from .general_helper import filter_loans_repayment
from questbanker_api.utils import get_current_user

def filter_loan_repayment_reports(report_filters):
    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)
    start_date = report_filters['start_date']
    end_date = report_filters['end_date']

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

    loan_filter   = { "loan_application__is_deleted":False,"loan_application__organisation_branch__branch_organisation__id":report_filters["organisation_id"]}
    extra_report_filter = {"outstanding_principal_bal":0, "report_type":"loan_repayment_report"}
    
    organisation_branch = OrganisationBranch.objects.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["loan_application__organisation_branch__id"] = organisation_branch_id
        
    paginator = PageNumberPagination()
    paginator.page_size = page_size
    if report_filter == 'officer_client_type':
        results = filter_loan_repayment_officer_client_type(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)
        
    elif report_filter == 'officer_gender':
        results = filter_loan_repayment_officer_gender(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'officer_branch':
        print("*********************** filter_loan_repayment_officer_branch ********************************")
        results = filter_loan_repayment_officer_branch(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'product_client_type':
        results = filter_loan_repayment_product_client_type(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'product_gender':
        results = filter_loan_repayment_product_gender(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'product_branch':
        results = filter_loan_repayment_product_branch(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'product_officer':
        results = filter_loan_repayment_product_officer(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'product_sector':
        results = filter_loan_repayment_product_sector(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'gender_branch':
        results = filter_loan_repayment_gender_branch(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    elif report_filter == 'group_branch':
        results = filter_loan_repayment_group_branch(filter_1, filter_2, loan_filter,extra_sql_filters, start_date, end_date, extra_report_filter)

    return Response({"results":results,"count":len(results)})

def filter_loan_repayment_officer_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    staffs = Staff.objects.filter(staff_organisation__id=organisation_id, id__in=filter_1).all()
    for staff in staffs:
        customer_types = CustomerType.objects.filter(id__in=filter_2).all()
        customer_types_list = []
        for customer_type in customer_types:
            loan_filter['loan_application__loan_officer__id'] = staff.id
            loan_filter['loan_application__customer__branch_customer_type__id'] = customer_type.id

            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            customer_types_list.append({"id":customer_type.id,"name":customer_type.customer_type, "loans":loans_response })

        response.append({
                "id":staff.id,
                "name":staff.name,
                "data":customer_types_list
            })
    return response

def filter_loan_repayment_officer_gender(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    staffs = Staff.objects.filter(staff_organisation__id=organisation_id, id__in=filter_1).all()
    for staff in staffs:
        gender_list = []
        for gender in filter_2:
            gender_name = 'Male'
            if gender == 'F':
                gender_name = 'Female'
            elif gender == 'O':
                gender_name = 'Other'

            loan_filter['loan_application__loan_officer__id'] = staff.id
            loan_filter['loan_application__customer__gender'] = gender
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            gender_list.append({"id":gender,"name":gender_name, "loans":loans_response })

        response.append({
            "id":staff.id,
            "name":staff.name,
            "data":gender_list
        })
    return response

def filter_loan_repayment_officer_branch(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    branches = OrganisationBranch.objects.filter(branch_organisation__id=organisation_id, id__in=filter_2)
    for branch in branches:
        staff_list = []
        staffs = Staff.objects.filter(staff_organisation__id=organisation_id, id__in=filter_1).all()
        for staff in staffs:

            loan_filter['loan_application__loan_officer__id'] = staff.id
            loan_filter['loan_application__organisation_branch__id'] = branch.id
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            staff_list.append({"id":staff.id,"name":staff.name , "loans":loans_response })
        
        response.append({
            "id":branch.id,
            "name":branch.name,
            "data":staff_list
        })
    return response

def filter_loan_repayment_product_client_type(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id__in=filter_1)
    for loan_product in loan_products:
        customer_types = CustomerType.objects.filter(id__in=filter_2).all()
        customer_types_list = []
        for customer_type in customer_types:
            loan_filter['loan_application__loan_application_product__id'] = loan_product.id
            loan_filter['loan_application__customer__branch_customer_type__id'] = customer_type.id

            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            customer_types_list.append({"id":customer_type.id,"name":customer_type.customer_type, "loans":loans_response })
        
        response.append({
            "id":loan_product.id,
            "name":loan_product.product_name,
            "data":customer_types_list
        })  
    return response

def filter_loan_repayment_product_gender(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id__in=filter_1)
    for loan_product in loan_products:
        gender_list = []
        for gender in filter_2:
            gender_name = 'Male'
            if gender == 'F':
                gender_name = 'Female'
            elif gender == 'O':
                gender_name = 'Other'

            loan_filter['loan_application__loan_application_product__id'] = loan_product.id
            loan_filter['loan_application__customer__gender'] = gender
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            gender_list.append({"id":gender,"name":gender_name, "loans":loans_response })

        response.append({
            "id":loan_product.id,
            "name":loan_product.product_name,
            "data":gender_list
        })
    return response

def filter_loan_repayment_product_branch(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    branches = OrganisationBranch.objects.filter(branch_organisation__id=organisation_id, id__in=filter_2)
    for branch in branches:
        loan_products_list = []
        loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id__in=filter_1)
        for loan_product in loan_products:
            loan_filter['loan_application__loan_application_product__id'] = loan_product.id
            loan_filter['loan_application__organisation_branch__id'] = branch.id

            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "loans":loans_response })
        
        response.append({
            "id":branch.id,
            "name":branch.name,
            "data":loan_products_list
        })
    return response

def filter_loan_repayment_product_officer(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    staffs = Staff.objects.filter(staff_organisation__id=organisation_id, id__in=filter_2).all()
    for staff in staffs:
        loan_products_list = []
        loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id__in=filter_1)
        for loan_product in loan_products:

            loan_filter['loan_application__loan_application_product__id'] = loan_product.id
            loan_filter['loan_application__loan_officer__id'] = staff.id
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "loans":loans_response })
        
        response.append({
            "id":staff.id,
            "name":staff.name ,
            "data":loan_products_list
        })

    return response

def filter_loan_repayment_product_sector(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    sectors = LoanSectors.objects.filter(organisation__id=organisation_id, id__in=filter_2)
    for sector in sectors:
        loan_products_list = []
        loan_products = LoanProduct.objects.filter(organisation__id=organisation_id, id__in=filter_1)
        for loan_product in loan_products:

            loan_filter['loan_application__loan_application_product__id'] = loan_product.id
            loan_filter['loan_application__loan_sector__id'] = sector.id
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "loans":loans_response })
        
        response.append({
            "id":sector.id,
            "name":sector.name,
            "data":loan_products_list
        })
    return response

def filter_loan_repayment_gender_branch(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    branches = OrganisationBranch.objects.filter(branch_organisation__id=organisation_id, id__in=filter_1)
    for branch in branches:
        gender_list = []
        for gender in filter_2:
            gender_name = 'Male'
            if gender == 'F':
                gender_name = 'Female'
            elif gender == 'O':
                gender_name = 'Other'

            loan_filter['loan_application__organisation_branch__id'] = branch.id
            loan_filter['loan_application__customer__gender'] = gender
            loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
            gender_list.append({"id":gender,"name":gender_name, "loans":loans_response })
        
        response.append({
            "id":branch.id,
            "name":branch.name,
            "data":gender_list
        })
    return response

def filter_loan_repayment_group_branch(filter_1, filter_2, loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter):
    response = []
    organisation_id = loan_filter['loan_application__organisation_branch__branch_organisation__id']
    branches = OrganisationBranch.objects.filter(branch_organisation__id=organisation_id, id__in=filter_1)
    for branch in branches:
        groups = Customer.objects.filter(customer_branch__id=branch.id,id__in=filter_2)
        groups_list = []
        for group in groups:
            group_members = GroupMembership.objects.filter(group=group, active=True).values_list('member__id', flat=True)
            if len(group_members) > 0:
                disbursed_loans = LoanApplicationDisbursement.objects.filter(loan_application__loan_group=group,loan_application__customer__id__in=group_members).values_list('loan_application', flat=True)
                if len(disbursed_loans) > 0:
                    loan_filter['loan_application__id__in'] = disbursed_loans
                loans_response = filter_loans_repayment(loan_filter, extra_sql_filters, start_date, end_date, 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