from rest_framework.pagination import PageNumberPagination
from rest_framework.response import Response
from loans.serializers import *
from questbanker_api.utils import get_current_user
from reports.models import RescheduledLoansReport
from reports.serializers import RescheduledLoansReportSerializer

def filter_loan_rescheduled_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']
    organisation_id = report_filters["organisation_id"]
    organisation_branch_id = get_current_user(report_filters['request'], 'organisation_branch_id', None)

    start = report_filters['start']
    end   = report_filters['end']
    organisation_branch = OrganisationBranch.objects.filter(id=organisation_branch_id, can_transact=True).first()
    if not organisation_branch and report_filter in ['officer_client_type','officer_gender','product_client_type','product_gender','product_officer','product_sector']:
        organisation_branch_id = None

    paginator = PageNumberPagination()
    paginator.page_size = page_size
    if report_filter == 'officer_client_type':
        results = filter_rescheduled_officer_client_type(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)
        
    elif report_filter == 'officer_gender':
        results = filter_rescheduled_officer_gender(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)

    elif report_filter == 'officer_branch':
        results = filter_rescheduled_officer_branch(filter_1, filter_2,start,end ,organisation_id)

    elif report_filter == 'product_client_type':
        results = filter_rescheduled_product_client_type(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)

    elif report_filter == 'product_gender':
        results = filter_rescheduled_product_gender(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)

    elif report_filter == 'product_branch':
        results = filter_rescheduled_product_branch(filter_1, filter_2,start,end ,organisation_id)

    elif report_filter == 'product_officer':
        results = filter_rescheduled_product_officer(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)

    elif report_filter == 'product_sector':
        results = filter_rescheduled_product_sector(filter_1, filter_2,start,end ,organisation_id,organisation_branch_id)

    elif report_filter == 'gender_branch':
        results = filter_rescheduled_gender_branch(filter_1, filter_2,start,end ,organisation_id)

    elif report_filter == 'group_branch':
        results = filter_rescheduled_group_branch(filter_1, filter_2,start,end ,organisation_id)

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

def get_rescheduled_loans_list(fileds_sting,where_sting):
    raw_query    = f'SELECT {fileds_sting} FROM rescheduled_loans AS reschduled' +\
        ' LEFT JOIN loan_application_disbursement AS loan ON reschduled.loan_application_id = loan.loan_application_id' +\
        ' LEFT JOIN loanview AS lv ON lv.id = loan.loan_application_id '
    raw_query = f'{raw_query} {where_sting}'
    
    queryset     = RescheduledLoansReport.objects.raw(raw_query)
    if queryset:
        return RescheduledLoansReportSerializer(queryset, many=True).data
    return []

def filter_rescheduled_officer_client_type(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.customer_type_id = {customer_type.id} AND lv.loan_officer_id = {staff.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
             
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
            
            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'
            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
             
            if len(query_data) > 0:
                customer_types_list.append({"id":customer_type.id,"name":customer_type.customer_type, "sub_section_data":query_data})
        if len(customer_types_list) > 0:
            response.append({
                    "id":staff.id,
                    "name":staff.name ,
                    "section_data":customer_types_list
                })
    return response

def filter_rescheduled_officer_gender(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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'
            
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.gender = '{gender}' AND lv.loan_officer_id = {staff.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"

            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'

            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            if len(query_data):
                gender_list.append({"id":gender,"name":gender_name, "sub_section_data":query_data})
        
        if len(gender_list):
            response.append({
                "id":staff.id,
                "name":staff.name,
                "section_data":gender_list
            })
    return response

def filter_rescheduled_officer_branch(filter_1, filter_2,start,end ,organisation_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.branch_id = {branch.id} AND lv.loan_officer_id = {staff.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                staff_list.append({"id":staff.id,"name":staff.name , "sub_section_data":query_data })
        
        if len(staff_list):
            response.append({
                "id":branch.id,
                "name":branch.name,
                "section_data":staff_list
            })
    return response

def filter_rescheduled_product_client_type(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.loan_product_id = {loan_product.id} AND lv.customer_type_id = {customer_type.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"

            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'

            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
           
            if len(query_data):
                customer_types_list.append({"id":customer_type.id,"name":customer_type.customer_type, "sub_section_data":query_data})
        
        if len(customer_types_list):
            response.append({
                "id":loan_product.id,
                "name":loan_product.product_name,
                "section_data":customer_types_list
            })  
    return response

def filter_rescheduled_product_gender(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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'
            
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.loan_product_id = {loan_product.id} AND lv.gender = '{gender}' AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"

            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'

            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                gender_list.append({"id":gender,"name":gender_name, "sub_section_data":query_data})
        
        if len(gender_list):
            response.append({
                "id":loan_product.id,
                "name":loan_product.product_name,
                "section_data":gender_list
            })
    return response

def filter_rescheduled_product_branch(filter_1, filter_2,start,end ,organisation_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.loan_product_id = {loan_product.id} AND lv.branch_id = {branch.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "sub_section_data":query_data})
        
        if len(loan_products_list):
            response.append({
                "id":branch.id,
                "name":branch.name,
                "section_data":loan_products_list
            })
    return response

def filter_rescheduled_product_officer(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.loan_product_id = {loan_product.id} AND lv.loan_officer_id = {staff.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'

            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "sub_section_data":query_data})
       
        if len(loan_products_list):
            response.append({
                "id":staff.id,
                "name":staff.name ,
                "section_data":loan_products_list
            })
    return response

def filter_rescheduled_product_sector(filter_1, filter_2,start,end ,organisation_id,branch_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.loan_product_id = {loan_product.id} AND lv.loan_sector_id = {sector.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            if branch_id:
                where_sting = f'{where_sting} AND lv.branch_id = {branch_id}'

            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data) > 0:
                loan_products_list.append({"id":loan_product.id,"name":loan_product.product_name, "sub_section_data":query_data})
        
        if len(loan_products_list) > 0:
            response.append({
                "id":sector.id,
                "name":sector.name,
                "section_data":loan_products_list
            })
    return response

def filter_rescheduled_gender_branch(filter_1, filter_2,start,end ,organisation_id):
    response = []
    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'
            
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.branch_id = {branch.id} AND lv.gender = '{gender}' AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                gender_list.append({"id":gender,"name":gender_name, "sub_section_data":query_data})
        
        if len(gender_list):
            response.append({
                "id":branch.id,
                "name":branch.name,
                "section_data":gender_list
            })
    return response

def filter_rescheduled_group_branch(filter_1, filter_2,start,end ,organisation_id):
    response = []
    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:
            rescheled_fileds = 'reschduled.principal_amount AS reschedule_principal,reschduled.interest_expected AS reschedule_interest,reschduled.loan_period,reschduled.period_type,reschduled.frequency_type,reschduled.frequency,reschduled.grace_period,reschduled.grace_period_type,reschduled.int_rate,reschduled.int_method,reschduled.reschedule_date,reschduled.comment'
            fileds_sting = f'lv.id,lv.name,lv.member_number,lv.old_member_number,lv.loan_officer_full_name,loan.total_interest_expected, loan.total_principal_expected,loan.loan_start_date,{rescheled_fileds} '
            where_sting  = f" where reschduled.status = 'active' AND lv.organisation_id = {organisation_id} AND lv.branch_id = {branch.id} AND lv.loan_group_id = {group.id} AND DATE(reschduled.reschedule_date) <= '{end}'"
            
            if start:
                where_sting = f"{where_sting} AND DATE(reschduled.reschedule_date) >= '{start}'"
                
            query_data   = get_rescheduled_loans_list(fileds_sting,where_sting)
            
            if len(query_data):
                groups_list.append({"id":group.id,"name":group.name, "sub_section_data":query_data})

        if len(groups_list):
            response.append({
                "id":branch.id,
                "name":branch.name,
                "section_data":groups_list
            })
    return response