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_balances
from questbanker_api.utils import get_current_user

def get_list_to_writeoff(loan_filter,as_at,extra_sql_filters):
      extra_report_filter = {"outstanding_principal_bal":0, "report_type":"writeoff_report"}
      loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, extra_report_filter)
      return loans_response

def filter_loan_writeoff_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)
    as_at = report_filters['as_at']
    extra_sql_filters = " AND write_off_days > 0"
    if search:
        extra_sql_filters = ' AND write_off_days > 0 AND (name LIKE %' + search + '% OR member_number LIKE ' + search + '% OR old_member_number  LIKE ' + search + '% )'
    loan_filter   = { "is_deleted":False,"status":'written_off',"organisation_id":report_filters["organisation_id"]}
    extra_report_filter = {"outstanding_principal_bal":0, "report_type":"writeoff_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["branch_id"] = organisation_branch_id

    paginator = PageNumberPagination()
    paginator.page_size = page_size
    if report_filter == 'officer_client_type':
        results = filter_writeoff_officer_client_type(filter_1, filter_2, loan_filter,extra_sql_filters, as_at, extra_report_filter)
        
    elif report_filter == 'officer_gender':
        results = filter_writeoff_officer_gender(filter_1, filter_2, loan_filter,extra_sql_filters, as_at, extra_report_filter)

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

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

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

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

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

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

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

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

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

def filter_writeoff_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.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_officer_id'] = staff.id
            loan_filter['customer_type_id'] = customer_type.id

            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_officer_id'] = staff.id
            loan_filter['gender'] = gender
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_officer_id'] = staff.id
            loan_filter['branch_id'] = branch.id
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_product_id'] = loan_product.id
            loan_filter['customer_type_id'] = customer_type.id

            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_product_id'] = loan_product.id
            loan_filter['gender'] = gender
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_product_id'] = loan_product.id
            loan_filter['branch_id'] = branch.id

            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_product_id'] = loan_product.id
            loan_filter['loan_officer_id'] = staff.id
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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_product_id'] = loan_product.id
            loan_filter['loan_sector_id'] = sector.id
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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['branch_id'] = branch.id
            loan_filter['gender'] = gender
            loans_response = filter_loans_balances(loan_filter, extra_sql_filters, None, as_at, 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_writeoff_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.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 = list(GroupMembership.objects.filter(group=group, active=True).values_list('member__id', flat=True))
            if group_members:
                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))
                    if len(disbursed_loans) > 0:
                        loan_filter['id'] = disbursed_loans
                    loan_filter['customer_id'] = group_members
                    loans_response = filter_loans_balances(loan_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