def master_query_function(as_on_date):

    master_data_query = f'''

        with status_logs_cte as (
            select *
            from status_logs
            where deleted_at is null and date(created_at) < date('{as_on_date}')
            and is_reversed = 0
        )

        ,app_status_cte as(
            select application_id,min(case when status in ('disbursed','nach registered') then created_at end) disbursement_date,
            min(case when status in ('closed','cancelled') then created_at end) loan_close_date
            from status_logs_cte
            group by 1
        )

        ,latest_status_cte as(
            select *
            from(
                select application_id,status,row_number() over(partition by application_id order by id desc) row_num 
                from status_logs_cte
                where status not like 'disbursement phase%%'
            )yy where row_num = 1
        )

        ,partial_phase_data_cte as (
            select application_id,phase,created_at,per_disbursement_amount
            from disbursement_log
            where deleted_at is null and date(created_at) < date('{as_on_date}')
        )

        ,first_phase_disbursement_cte as ( -- get cases with only first phase disbursment done
            select application_id,max(phase) disbursment_phase,sum(per_disbursement_amount/100) disbursment_percent
            from partial_phase_data_cte
            group by 1
            having max(phase) = 1
        )

        ,credit_report_cte as (
            select *
            from(
                select application_id,score bureau_score,row_number() over(partition by application_id order by created_at desc) row_num
                from credit_report
                where credit_report.deleted_at is null and applicant_type = 1
            )yy where row_num = 1
        )

        ,emi_calculation_cte as (
            select *
            from(
                select * ,row_number() over(partition by application_id order by coalesce(updated_at,created_at) desc) row_num
                from emi_calculation
            )yy where row_num = 1
        )

        ,emi_payments_cte as (
            select *
            from emi_payments
            where true 
            -- and deleted_at is null
        )

        ,payment_transactions_cte as ( -- get data from payment_transactions table according to 'as on date'
            select *,if(payment_transactions.payment_for in ('Forclouser','Foreclosed','Foreclosure'),payment_date,null) foreclosed_date
            from payment_transactions
            where (payment_transactions.deleted_at is null and payment_transactions.is_deleted_at is null and refunded_at is null) 
            and payment_transactions.payment_for in ('EMI','Forclouser','Foreclosed','Foreclosure')
            and payment_transactions.status = 'success' 
            and date(payment_transactions.payment_date) < date('{as_on_date}') -- change analysis_date here, update to first day of current month
        )

        ,total_payment as ( -- get total_amount_paid_by_customer (incl. bounce penal) & foreclosed_date
            select application_id,sum(amount) total_amount_paid_by_customer,max(foreclosed_date) foreclosed_date
            from payment_transactions_cte
            group by 1
        )

        ,emi_wise_amount_paid_cte as ( -- get amount_paid by customer for each month emi
            select application_id,emi_id,emi_date,least(emi_amount,((emi_amount + bounce_charges + delay_penalty) - charges_waived)) emi_amount,bounce_charges,delay_penalty,charges_waived,payment_status,amount_paid, -- charges_waived on principal
            actual_amount_paid_for_emi
            from(
                select emi_payments_cte.application_id,emi_payments_cte.id emi_id,emi_payments_cte.emi_date,emi_payments_cte.emi_amount,bounce_charges,delay_penalty,charges_waived,payment_status,
                sum(coalesce(payment_transactions_cte.amount,0)) amount_paid,
                case when sum(coalesce(payment_transactions_cte.amount,0)) > emi_payments_cte.emi_amount then emi_payments_cte.emi_amount else sum(coalesce(payment_transactions_cte.amount,0)) end actual_amount_paid_for_emi
                from emi_payments_cte
                left join payment_transactions_cte on payment_transactions_cte.emi_payment_id = emi_payments_cte.id
                left join emi_calculation_cte on emi_calculation_cte.application_id = emi_payments_cte.application_id 
                group by 1,2,3,4,5,6,7,8
            )yy
        )

        ,emis_list as ( -- get emi_date wise emi amount in a dictionary
            select application_id,json_objectagg(date_format(emi_date,'%%d-%%m-%%Y'), ifnull(emi_amount,0)) emi_date_wise_amount_list
            from(
                select emi_payments.application_id,date(emi_payments.emi_date) emi_date,
                if(emi_payments.application_id in (select distinct application_id from moratorium where deleted_at is null),
                    emi_calculation_cte.emi,
                    least(emi_amount,((emi_amount + bounce_charges + delay_penalty) - charges_waived))) emi_amount -- if case has morat, then consider emi_calculation emi amount else consider emi_payments emi amount as emi_payments emi amount is incorrect for morat cases
                from emi_payments
                left join emi_calculation_cte on emi_calculation_cte.application_id = emi_payments.application_id
                where true and (emi_payments.emi_amount > 0) -- exclude extra pre emi entry
            
                union all 
            
                select application_id,date(emi_date) emi_date,0 from moratorium where (deleted_at is null) and (stopped_date is null)
            )qq
            group by 1
        )

        ,emi_info_consolidated as (
            select emi_wise_amount_paid_cte.application_id,emis_list.emi_date_wise_amount_list,
            sum(case when emi_date < date('{as_on_date}') then emi_amount end ) emi_amount_expected, -- emi amount expected upto analysis_date
            sum(actual_amount_paid_for_emi) amount_paid_for_emi,min(case when emi_amount >0 then emi_date end) first_emi_date,max(emi_date) last_emi_date
            from emi_wise_amount_paid_cte
            left join emis_list on emis_list.application_id = emi_wise_amount_paid_cte.application_id
            group by 1
        )

        ,files as (
        select consumer_application_id application_id,max(case when file_type like '%%gst_certificate%%' then created_at end) gst_certificate_date
        from consumer_application_files
        where deleted_at is null
        group by 1
        )


        ,base as (
        select date('{as_on_date}') - interval 1 day analysis_date,consumer_application.id application_id,consumer_application.name applicant_name,current_address_state,
        if(length(credit_report_cte.bureau_score) > 3,0,credit_report_cte.bureau_score) bureau_score,consumer_application.employee_status,cast(consumer_application.monthly_income as unsigned) monthly_income,
        merchant.merchant_name,anchor.merchant_name anchor_merchant,merchant_category.name merchant_category,
        case when consumer_application_element.type_of_customer = 1 then 'Residential'
            when consumer_application_element.type_of_customer = 2 then 'Rural C&I MSME(Attachakki, Oil Expeller etc.)'
            when consumer_application_element.type_of_customer = 3 then 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)'
            when consumer_application_element.type_of_customer = 4 then 'Housing Society'
            when consumer_application_element.type_of_customer = 5 then 'Schools & Universities'
            else null end as type_of_customer,
        gst_certificate_date,lenders.name lender_name,
        if(consumer_application.state in ('closed','cancelled'),latest_status_cte.status,consumer_application.state) application_status,date_format(disbursement_date,'%%d-%%m-%%Y') disbursement_date,
        if(consumer_application.id in (select application_id from partial_phase_data_cte),'partial','full') disbursement_type,
        case when consumer_application.id in (select application_id from partial_phase_data_cte) and first_phase_disbursement_cte.application_id is not null then 'only phase 1 disbursed'
            when consumer_application.id in (select application_id from partial_phase_data_cte) and first_phase_disbursement_cte.application_id is null then 'both phase disbursed'
            else null end partial_disbursement_status,
        date_format(first_emi_date,'%%d-%%m-%%Y') first_emi_date,date_format(coalesce(foreclosed_date,loan_close_date),'%%d-%%m-%%Y') loan_close_date,date_format(emi_info_consolidated.last_emi_date,'%%d-%%m-%%Y') last_emi_date,emi emi_amount,interest_rate,
        emi_calculation_cte.processing_fee,cast(consumer_application.loan_amount as unsigned) loan_amount,approved_amount,upfront_from_customer,coalesce(approved_amount - upfront_from_customer,0) loan_principal,remaining_emi,subvention,
        if(merchant.charge_subvention_remaining_amount=1,'true','false') charge_subvention_on_net_loan_amount,
        if(remaining_emi = 1 or emi_calculation_cte.rate is null or emi_calculation_cte.rate = 0 or emi_calculation_cte.interest_rate = 0,0.000000001,emi_calculation_cte.rate) irr_rate,consumer_application_element.hold_amount,
        round(consumer_application.approved_amount - emi_calculation_cte.upfront_from_customer - coalesce(hold_amount,0) 
        - if(merchant.charge_subvention_remaining_amount = 1,(approved_amount - upfront_from_customer) * subvention/100,approved_amount * subvention/100),0) net_disbursement_amount,
        coalesce(emi_amount_expected,0) emi_amount_expected,coalesce(total_amount_paid_by_customer,0) total_amount_paid_by_customer,coalesce(amount_paid_for_emi,0) amount_paid_for_emi,
        emi_date_wise_amount_list
        from consumer_application
        left join app_status_cte on app_status_cte.application_id = consumer_application.id
        left join credit_report_cte on credit_report_cte.application_id = consumer_application.id
        left join merchant on merchant.id = consumer_application.merchant_id
        left join merchant anchor on anchor.id = merchant.anchor_merchant_id
        left join lenders on lenders.id = consumer_application.lender_id
        left join merchant_category on merchant_category.id = merchant.category_id
        left join emi_calculation_cte on emi_calculation_cte.application_id = consumer_application.id
        left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
        left join latest_status_cte on latest_status_cte.application_id = consumer_application.id
        left join first_phase_disbursement_cte on first_phase_disbursement_cte.application_id = consumer_application.id
        left join total_payment on total_payment.application_id = consumer_application.id
        left join emi_info_consolidated on emi_info_consolidated.application_id = consumer_application.id
        left join files on files.application_id = consumer_application.id
        where (consumer_application.is_deleted is null and consumer_application.deleted_at is null and consumer_application.merchant_id not in (73,9421))
        and (consumer_application.state IN ('disbursed','nach registered','closed','cancelled', 'cancellation in-process'))
        and (disbursement_date < date('{as_on_date}'))
        )


        ,final as( -- added some logic here, refer comments
        select date_format(analysis_date,'%%d-%%m-%%Y') analysis_date,application_id,applicant_name,current_address_state,bureau_score,employee_status,monthly_income,merchant_name,merchant_category,type_of_customer,
        -- new type_of_customer column for lender_reporting
        case when merchant_category = 'Solar and Rooftop Solutions' then 
                case when interest_rate > 12 and type_of_customer is null then 'Rural C&I MSME(Attachakki, Oil Expeller etc.)'
                    when type_of_customer is null and gst_certificate_date is not null then 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)'
                    when type_of_customer in ('Schools & Universities', 'Housing Society') then 'Urban C&I MSME(Factories, Hospitals, Hotels, Offices, etc.)'
                    when type_of_customer is null and gst_certificate_date is null then 'Residential'
                    else type_of_customer end
            else null
        end as revised_type_of_customer,lender_name,
        case when application_id in (select distinct application_id from payment_transactions_cte where payment_for like '%%for%%') then 'closed' else application_status end as application_status,
        disbursement_date,disbursement_type,partial_disbursement_status,first_emi_date,loan_close_date,last_emi_date,emi_amount,interest_rate,processing_fee,loan_amount,approved_amount,upfront_from_customer,
        coalesce(remaining_emi,1) remaining_emi,subvention,charge_subvention_on_net_loan_amount,irr_rate,hold_amount,net_disbursement_amount,loan_principal,emi_amount_expected,total_amount_paid_by_customer,
        case when amount_paid_for_emi = 0 and total_amount_paid_by_customer > 0 then total_amount_paid_by_customer else amount_paid_for_emi end amount_paid_for_emi, -- bnpl cases will not have amount_paid_for_emi
        emi_date_wise_amount_list
        from base
        )


        select * from final -- order by application_id desc limit 5

        '''

    return master_data_query