# Get emi wise emi_amount, bounce & penal - settlement_reconcile.py
emi_payments_query = """
    select application_id,emi_date,pre_emi_amount,emi_amount,bounce_charges,delay_penalty,charges_waived
    from emi_payments where application_id = :application_id
    order by 1,2
"""


# Get total amount paid for EMI & Pre-EMI - settlement_reconcile.py
total_amount_query = """
    select ifnull(sum(amount),0) total_amount_paid
    from payment_transactions
    where (deleted_at is null and refunded_at is null and is_deleted_at is null) and (payment_for in ('Pre-EMI','EMI')) 
    and application_id = :application_id
"""


# Check if any app_id is closed, cancelled, foreclosed, settled, prepartpayment done - settlement_reconcile.py
closed_cancelled_settled_check = """
    select consumer_application.id application_id, state app_status,foreclosed,settled,pre_part_payment
    from consumer_application
    left join (
        select application_id,
        max(case when payment_for in ('Forclouser','Foreclosed','Foreclosure','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment') then 1 else 0 end) foreclosed,
        max(case when (payment_for in ('Write-off','settled_principal','settled_interest','settled_penal','settled_bounce',
            'written_off_principal','written_off_interest','written_off_bounce','written_off_penal')) then 1 else 0 end) settled,
        max(case when payment_for = 'pre_part_payment' then 1 else 0 end) pre_part_payment
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (application_id in :application_ids)
        group by 1
    )as payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
    where consumer_application.id in :application_ids
"""


# Get rps of single app_id - payment_allocation_st.py
allocation_query = """
with rps_logs_cte as (
    select application_id,date_format(due_date,'%d-%m-%Y') due_date,round(bpi_interest,0) bpi_interest,morat_interest,interest_comp,principal_comp,round(emi_amount) emi_amount,
    principal_outstanding
    from rps_logs
    where (deleted_at is null) and (application_id = :application_id)
)

            
,payment_transactions_cte as (select * from payment_transactions where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (application_id = :application_id))

            
,emi_payments_cte as (
select emi_payments.id emi_payment_id,emi_payments.application_id,date_format(emi_date,'%d-%m-%Y') emi_date,date_format(due_date,'%d-%m-%Y') due_date,bounce_charges,
delay_penalty,charges_waived,delay_days,coalesce(sum(payment_transactions_cte.amount),0) total_paid,
coalesce(sum(case when payment_transactions_cte.payment_for = 'Pre-EMI' then payment_transactions_cte.amount end),0) total_paid_for_bpi,
group_concat(case when (payment_transactions_cte.payment_for = 'EMI' and payment_transactions_cte.payment_date < current_date()) then 
    concat('{"',date_format(payment_transactions_cte.payment_date,'%d-%m-%Y'),'"',':',payment_transactions_cte.amount,'}') end 
    order by payment_transactions_cte.payment_date separator ',') payment_list,date(max(payment_transactions_cte.payment_date)) recent_payment_date
from emi_payments
left join payment_transactions_cte on payment_transactions_cte.emi_payment_id = emi_payments.id
where emi_payments.application_id = :application_id
group by emi_payments.application_id,emi_payments.due_date
)


,base as(
select emi_payment_id,emi_payments_cte.application_id,emi_date,rps_logs_cte.due_date,bpi_interest,morat_interest,emi_amount,bounce_charges,delay_penalty,charges_waived,
bpi_interest + morat_interest + emi_amount + bounce_charges + delay_penalty - charges_waived total_payable,total_paid,total_paid_for_bpi,delay_days,
concat('[',ifnull(concat(payment_list, ',{"', date_format(current_date(), '%d-%m-%Y'), '":', 0, '}'),concat('{"', date_format(current_date(), '%d-%m-%Y'), '":', 0, '}')),']') payment_list,
principal_outstanding,interest_comp,principal_comp,recent_payment_date
from rps_logs_cte
join emi_payments_cte on emi_payments_cte.application_id = rps_logs_cte.application_id and emi_payments_cte.due_date = rps_logs_cte.due_date
)


select emi_payment_id,application_id,emi_date,due_date,bpi_interest,morat_interest,emi_amount,bounce_charges,delay_penalty,charges_waived,total_payable,total_paid,
greatest(0,total_payable - total_paid) amount_due,total_paid_for_bpi,
least(round(emi_amount),greatest(0,total_paid - total_paid_for_bpi - morat_interest)) total_paid_for_emi,
greatest(0,round(emi_amount) - least(round(emi_amount),greatest(0,total_paid - total_paid_for_bpi - morat_interest))) pending_for_emi,delay_days,payment_list,principal_outstanding,
interest_comp,principal_comp,recent_payment_date,consumer_application.lender_id
from base
left join consumer_application on consumer_application.id = base.application_id
order by date(emi_date)
"""


# Vivriti CAM generation query
vivriti_cam_query = """
    with emi_calc_cte as (
        select *
        from(
            select application_id,tenure,interest_rate,emi,row_number() over(partition by application_id order by created_at desc) row_num
            from emi_calculation where application_id = :application_id
        )yy where row_num = 1
    )


    select consumer_application.id application_id,consumer_application.name consumer_name,consumer_application.approved_amount loan_value,emi_calc_cte.tenure,emi_calc_cte.interest_rate roi,emi_calc_cte.emi,
    merchant.merchant_name,
    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,coalesce(consumer_application_element.inverter_KW_installation,0) inverter_KW_installation,
    foir.id tradeline_id,accounttype type_of_loan,date(dateopened) dateopened,date(date(dateopened) + interval 1 month) firstemidate,date(coalesce(dateclosed,lastpaymentdate,current_date())) dateclosed,lastpaymentdate,ownershiptype,if(isOpened = 1,'Active','Closed') account_status,
    approvedamount loan_amount,InstallmentAmountNEW emi_amount,PastDueAmount overdue_amount,dpd,
    case when ownershiptype = 'Individual' and accounttype in ('Auto Loan', 'Business Loan', 'Business Loan - Priority Sector- Small Business', 'Business Loan - Secured', 'Business Loan - Unsecured', 
    'Business Loan – General', 'Commercial Vehicle Loan', 'Construction Equipment Loan', 'Consumer Loan', 'Education Loan', 'Educational Loan', 'GECL Loan Secured', 'GECL Loan Unsecured', 'Gecl Secured', 
    'Gecl Unsecured', 'Home Loan', 'Housing Loan', 'Loan on Credit Card', 'Loan to Professional', 'Microfinance – Business Loan', 'Microfinance – Housing Loan', 'Microfinance – Others', 'Microfinance – Personal Loan', 
    'MicroFinance Business Loan', 'MicroFinance Housing Loan', 'MicroFinance Others', 'MicroFinance Personal Loan', 'Mortgages Loan', 'P2p Auto Loan', 'P2P Personal Loan', 
    'Personal Loan', 'Pradhan Mantri Awas Yojana - Credit Link Subsidy Scheme MAY CLSS', 'Prime Minister Jaan Dhan Yojana - Overdraft', 'Property Loan', 'Short Term Personal Loan [Unsecured]', 
    'Tractor Loan', 'Two-wheeler Loan', 'Used Car Loan') and (date(coalesce(dateclosed,lastpaymentdate,current_date())) >= current_date() - interval 36 month) then 'eligible' else 'not eligible' end as eligibility,
    case when (date(coalesce(dateclosed,lastpaymentdate,current_date())) <= current_date() - interval 36 month) then 'loan not active in last 36 months'
    when ownershiptype <> 'Individual' then 'not Individual loan' 
    when accounttype not in ('Auto Loan', 'Business Loan', 'Business Loan - Priority Sector- Small Business', 'Business Loan - Secured', 'Business Loan - Unsecured', 
    'Business Loan – General', 'Commercial Vehicle Loan', 'Construction Equipment Loan', 'Consumer Loan', 'Education Loan', 'Educational Loan', 'GECL Loan Secured', 'GECL Loan Unsecured', 'Gecl Secured', 
    'Gecl Unsecured', 'Home Loan', 'Housing Loan', 'Loan on Credit Card', 'Loan to Professional', 'Microfinance – Business Loan', 'Microfinance – Housing Loan', 'Microfinance – Others', 'Microfinance – Personal Loan', 
    'MicroFinance Business Loan', 'MicroFinance Housing Loan', 'MicroFinance Others', 'MicroFinance Personal Loan', 'Mortgages Loan', 'P2p Auto Loan', 'P2P Personal Loan', 
    'Personal Loan', 'Pradhan Mantri Awas Yojana - Credit Link Subsidy Scheme MAY CLSS', 'Prime Minister Jaan Dhan Yojana - Overdraft', 'Property Loan', 'Short Term Personal Loan [Unsecured]', 
    'Tractor Loan', 'Two-wheeler Loan', 'Used Car Loan') then concat(accounttype,' not eligible') end not_eligible_reason
    from foir 
    join consumer_application on consumer_application.id = foir.application_id
    left join consumer_application_element on consumer_application_element.application_id = foir.application_id
    left join merchant on merchant.id = consumer_application.merchant_id
    left join emi_calc_cte on emi_calc_cte.application_id = foir.application_id
    where (foir.deleted_at is null and foir.deleted_by is null and applicant_type = 1 and accounttype != 'Credit Fair EMI')
    and foir.application_id = :application_id
"""


# Utkarsh CAM generation query
utkarsh_cam_query = """
    with emi_calc_cte as (
        select *
        from(
            select application_id,tenure,interest_rate,emi,row_number() over(partition by application_id order by created_at desc) row_num
            from emi_calculation where application_id = :application_id
        )yy where row_num = 1
    )


    select consumer_application.id application_id,consumer_application.name consumer_name,consumer_application.approved_amount loan_value,emi_calc_cte.tenure,emi_calc_cte.interest_rate roi,emi_calc_cte.emi,
    merchant.merchant_name,
    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,coalesce(consumer_application_element.inverter_KW_installation,0) inverter_KW_installation,
    foir.id tradeline_id,accounttype type_of_loan,date(dateopened) dateopened,date(date(dateopened) + interval 1 month) firstemidate,date(coalesce(dateclosed,lastpaymentdate,current_date())) dateclosed,lastpaymentdate,ownershiptype,if(isOpened = 1,'Active','Closed') account_status,
    approvedamount loan_amount,round(if(ownershiptype = 'Joint',InstallmentAmountNEW * 0.5, InstallmentAmountNEW)) emi_amount,PastDueAmount overdue_amount,dpd,
    case when accounttype in ('Auto Loan', 'Business Loan', 'Business Loan - Priority Sector- Small Business', 'Business Loan - Secured', 'Business Loan - Unsecured', 
    'Business Loan – General', 'Commercial Vehicle Loan', 'Construction Equipment Loan', 'Consumer Loan', 'Education Loan', 'Educational Loan', 'GECL Loan Secured', 'GECL Loan Unsecured', 'Gecl Secured', 
    'Gecl Unsecured', 'Home Loan', 'Housing Loan', 'Loan on Credit Card', 'Loan to Professional', 'Microfinance – Business Loan', 'Microfinance – Housing Loan', 'Microfinance – Others', 'Microfinance – Personal Loan', 
    'MicroFinance Business Loan', 'MicroFinance Housing Loan', 'MicroFinance Others', 'MicroFinance Personal Loan', 'Mortgages Loan', 'P2p Auto Loan', 'P2P Personal Loan', 
    'Personal Loan', 'Pradhan Mantri Awas Yojana - Credit Link Subsidy Scheme MAY CLSS', 'Prime Minister Jaan Dhan Yojana - Overdraft', 'Property Loan', 'Short Term Personal Loan [Unsecured]', 
    'Tractor Loan', 'Two-wheeler Loan', 'Used Car Loan') and (date(coalesce(dateclosed,lastpaymentdate,current_date())) >= current_date() - interval 36 month) then 'eligible' else 'not eligible' end as eligibility,
    case when (date(coalesce(dateclosed,lastpaymentdate,current_date())) <= current_date() - interval 36 month) then 'loan not active in last 36 months'
        when accounttype not in ('Auto Loan', 'Business Loan', 'Business Loan - Priority Sector- Small Business', 'Business Loan - Secured', 'Business Loan - Unsecured', 
        'Business Loan – General', 'Commercial Vehicle Loan', 'Construction Equipment Loan', 'Consumer Loan', 'Education Loan', 'Educational Loan', 'GECL Loan Secured', 'GECL Loan Unsecured', 'Gecl Secured', 
        'Gecl Unsecured', 'Home Loan', 'Housing Loan', 'Loan on Credit Card', 'Loan to Professional', 'Microfinance – Business Loan', 'Microfinance – Housing Loan', 'Microfinance – Others', 'Microfinance – Personal Loan', 
        'MicroFinance Business Loan', 'MicroFinance Housing Loan', 'MicroFinance Others', 'MicroFinance Personal Loan', 'Mortgages Loan', 'P2p Auto Loan', 'P2P Personal Loan', 
        'Personal Loan', 'Pradhan Mantri Awas Yojana - Credit Link Subsidy Scheme MAY CLSS', 'Prime Minister Jaan Dhan Yojana - Overdraft', 'Property Loan', 'Short Term Personal Loan [Unsecured]', 
        'Tractor Loan', 'Two-wheeler Loan', 'Used Car Loan') then concat(accounttype,' not eligible') end not_eligible_reason
    from foir 
    join consumer_application on consumer_application.id = foir.application_id
    left join consumer_application_element on consumer_application_element.application_id = foir.application_id
    left join merchant on merchant.id = consumer_application.merchant_id
    left join emi_calc_cte on emi_calc_cte.application_id = foir.application_id
    where (foir.deleted_at is null and foir.deleted_by is null and applicant_type = 1 and accounttype != 'Credit Fair EMI')
    and foir.application_id = :application_id
"""


# Pre Part Payment Queries
existing_ep_query = """
    select ep.id,ep.application_id,ep.emi_date,ep.due_date,ep.emi_amount,ep.total_payable,ep.amount_due,ep.payment_status,coalesce(ne.rn, 0) AS installment_number,
    ep.updated_at,ep.created_at
    from emi_payments ep
    left join (
        select 
            id,row_number() over (partition by application_id order by emi_date) as rn from emi_payments where emi_amount > 0
    ) ne on ep.id = ne.id
    where ep.application_id = :application_id
"""


existing_rps_query = """
    select rps_logs.id,rps_logs.application_id,rps_logs.emi_date,rps_logs.due_date,rps_logs.interest_comp,rps_logs.principal_comp, rps_logs.emi_amount,rps_logs.total_payable,
    rps_logs.principal_outstanding,rps_logs.foreclosure_amount,emi_payments.payment_status,coalesce(numbered_emis.rn, 0) AS installment_number
    from rps_logs
    join emi_payments on emi_payments.application_id = rps_logs.application_id and date(emi_payments.emi_date) = date(rps_logs.emi_date)
    left join (
        select id, row_number() over (partition by application_id order by emi_date) as rn
        from rps_logs
        where deleted_at is null and emi_amount > 0
    ) as numbered_emis on rps_logs.id = numbered_emis.id
    where rps_logs.deleted_at is null
    and rps_logs.application_id = :application_id
    and rps_logs.morat_interest = 0
    order by installment_number
""" 


old_rps_query = """
    select emi_payments.application_id, emi_payments.emi_date, emi_payments.due_date, 
    row_number() over(partition by emi_payments.application_id order by emi_payments.emi_date) as installment_number,
    ifnull(greatest(pre_emi_amount,bpi_interest),0) as bpi_interest, coalesce(interest_comp,0) as interest_comp, 
    coalesce(principal_comp,emi_payments.emi_amount) as principal_comp, 
    emi_payments.emi_amount, emi_payments.bounce_charges, emi_payments.delay_penalty, emi_payments.charges_waived,
    emi_payments.total_payable, emi_payments.amount_due, emi_payments.payment_status
    from emi_payments
    left join rps_logs on rps_logs.application_id = emi_payments.application_id and date(emi_payments.emi_date) = date(rps_logs.emi_date) 
    and (rps_logs.deleted_at is null)
    where true and (emi_payments.application_id = :application_id)
"""


# TOS, Overdue, DPD calculation query - overdue_update.py
overdue_calc_query = """
    with payment_transactions_cte as (
    select application_id,emi_payment_id,payment_for,payment_date,amount from payment_transactions
    where (deleted_at is null and is_deleted_at is null and refunded_at is null)
    and (payment_for in ('EMI','Forclouser','Foreclosed','Foreclosure','Write-off','Pre-EMI','settled_principal','written_off_principal','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment'))
    and application_id in :app_ids
    )


    ,foreclosure_cte as (
        select application_id
        from payment_transactions_cte 
        where payment_for in ('Forclouser','Foreclosed','Foreclosure','Write-off','settled_principal','written_off_principal','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment')
    )


    ,emi_payments_cte as (
        select id,application_id,emi_date,payment_status,
        least(emi_amount,(emi_amount + bounce_charges + delay_penalty) - charges_waived) emi_amount_expected,
        round((pre_emi_amount + emi_amount + bounce_charges + delay_penalty) - charges_waived) total_amount_expected
        from emi_payments
        where true -- and (deleted_at is null)
        and application_id in :app_ids
    )


    ,emi_cte1 as (
        select *,if(emi_amount_expected > emi_amount_received and emi_date <= current_date,emi_date,null) first_unpaid_emi_date
        from(
            select emi_payments_cte.application_id,
            emi_payments_cte.emi_date,emi_payments_cte.payment_status,
            emi_payments_cte.emi_amount_expected,
            if(
                ifnull(sum(payment_transactions_cte.amount),0) > emi_payments_cte.emi_amount_expected,
                    emi_payments_cte.emi_amount_expected,
                    ifnull(sum(payment_transactions_cte.amount),0)
            ) emi_amount_received, -- if more amount received then expected then show expected amount, else show received amount
            emi_payments_cte.total_amount_expected,ifnull(sum(payment_transactions_cte.amount),0) total_amount_received,
            max(payment_transactions_cte.payment_date) latest_payment_date
            from emi_payments_cte
            left join payment_transactions_cte on payment_transactions_cte.application_id = emi_payments_cte.application_id and payment_transactions_cte.emi_payment_id = emi_payments_cte.id
            group by 1,2,3,4
        )yy
    )


    ,emi_cte2 as (
        select application_id,min(emi_date) first_emi_date,sum(emi_amount_expected) total_emi_amount_expected,
        sum(case when emi_date <= current_date then emi_amount_expected else 0 end) total_emi_amount_expected_today,
        sum(emi_amount_received) total_emi_amount_received,sum(total_amount_expected) total_amount_expected,
        sum(case when emi_date <= current_date then total_amount_expected else 0 end) total_amount_expected_today,
        sum(total_amount_received) total_amount_received,
        min(first_unpaid_emi_date) first_unpaid_emi_date,
        date(max(latest_payment_date)) latest_payment_date,sum(case when payment_status <> 'success' then 1 else 0 end) emi_pending,
        sum(case when payment_status = 'success' then 1 else 0 end) emi_paid,count(*) total_tenure_count
        from emi_cte1 group by 1
    )


    ,final as (
    select application_id,
    -- first_emi_date,
    -- total_emi_amount_expected,
    -- total_emi_amount_expected_today,
    -- total_emi_amount_received,
    greatest(0,total_emi_amount_expected_today - total_emi_amount_received) principal_due, -- emi_overdue
    greatest(0,total_emi_amount_expected - total_emi_amount_received) total_principal_due, -- principal_outstanding
    -- total_amount_expected,
    -- total_amount_expected_today,
    -- total_amount_received,
    greatest(0,total_amount_expected_today - total_amount_received) amount_over_due, -- total_overdue
    greatest(0,emi_cte2.total_amount_expected - emi_cte2.total_amount_received) total_amount_due, -- total_loan_outstanding
    -- first_unpaid_emi_date,
    ifnull(timestampdiff(day,first_unpaid_emi_date,current_date()),0) dpd, emi_paid, emi_pending, total_tenure_count
    -- case when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 0 or timestampdiff(day,first_unpaid_emi_date,current_date()) is null then '1) No DPD'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 30 then '2) 1-30'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 60 then '3) 31-60'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 90 then '4) 61-90'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 120 then '5) 91-120'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 150 then '6) 121-150'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 180 then '7) 151-180'
    --     when timestampdiff(day,first_unpaid_emi_date,current_date()) <= 364 then '8) 181-364' else '9) >=365' end dpd_bucket,
    -- latest_payment_date
    from emi_cte2
    )


    select final.application_id,if(foreclosure_cte.application_id is not null,0,principal_due) principal_due,
    if(foreclosure_cte.application_id is not null,0,total_principal_due) total_principal_due,
    if(foreclosure_cte.application_id is not null,0,amount_over_due) amount_over_due,
    if(foreclosure_cte.application_id is not null,0,total_amount_due) total_amount_due,
    if(foreclosure_cte.application_id is not null,0,dpd) dpd,
    if(foreclosure_cte.application_id is not null,total_tenure_count,emi_paid) emi_paid,
    if(foreclosure_cte.application_id is not null,0,emi_pending) emi_pending, total_tenure_count
    from final
    left join foreclosure_cte on foreclosure_cte.application_id = final.application_id
"""

# Bounce penal update query - bounce_penal_update.py
bounce_penal_query = """
    with payment_transactions_cte as (select * from payment_transactions where (deleted_at is null and is_deleted_at is null and refunded_at is null) and application_id in :application_ids)


    ,emi_payments_cte as (
    select emi_payments.id emi_payment_id,emi_payments.application_id,date_format(emi_date,'%d-%m-%Y') emi_date,date_format(due_date,'%d-%m-%Y') due_date,
    greatest(0,pre_emi_amount) pre_emi_amount,emi_amount,bounce_charges,delay_penalty,charges_waived,delay_days,coalesce(sum(payment_transactions_cte.amount),0) total_paid,
    coalesce(sum(case when payment_transactions_cte.payment_for = 'Pre-EMI' then payment_transactions_cte.amount end),0) total_paid_for_bpi,
    group_concat(case when (payment_transactions_cte.payment_for = 'EMI' and payment_transactions_cte.payment_date < current_date()) then 
        concat('{"',date_format(payment_transactions_cte.payment_date,'%d-%m-%Y'),'"',':',payment_transactions_cte.amount,'}') end 
        order by payment_transactions_cte.payment_date separator ',') payment_list,date(max(payment_transactions_cte.payment_date)) recent_payment_date,emi_payments.updated_at
    from emi_payments
    left join payment_transactions_cte on payment_transactions_cte.emi_payment_id = emi_payments.id
    where emi_payments.application_id in :application_ids
    group by emi_payments.application_id,emi_payments.due_date
    )


    select emi_payment_id,emi_payments_cte.application_id,emi_date,due_date,pre_emi_amount,emi_amount,bounce_charges,delay_penalty,charges_waived,
    pre_emi_amount + emi_amount + bounce_charges + delay_penalty - charges_waived total_payable,total_paid,total_paid_for_bpi,delay_days,
    concat('[',ifnull(concat(payment_list, ',{"', date_format(current_date(), '%d-%m-%Y'), '":', 0, '}'),concat('{"', date_format(current_date(), '%d-%m-%Y'), '":', 0, '}')),']') payment_list,
    recent_payment_date, consumer_application.lender_id,emi_payments_cte.updated_at
    from emi_payments_cte
    left join consumer_application on consumer_application.id = emi_payments_cte.application_id
    where application_id in :application_ids
    order by emi_payments_cte.application_id, emi_date
"""


# Bureau report - filter application id query - bureau_report_script.py
""" 
Query selects distinct consumer_application IDs with these conditions:
1. Excludes applications from specific merchants (merchant IDs listed).
2. Application state must be either:
   - Active ('disbursed', 'nach registered', 'cancellation in-process'), or
   - Closed/cancelled with a status log entry in the last 6 months 
   (to avoid reporting for old closed cases - not needed).
3. Only includes applications where EMI payments have started 
   (emi_amount > 0 and earliest emi_date <= current date).
"""
bureau_report_cases_query = """
    select distinct consumer_application.id, consumer_application.lender_id
    from consumer_application
    left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
    left join (
        select distinct application_id
        from status_logs
        where (deleted_at is null) and (is_reversed = 0) and (status in ('closed', 'cancelled')) and (date(created_at) >= current_date() - interval 6 month)
        -- only consider closed/cancelled cases if their closed/cancelled date is in last 6 months, to reduce continuous reporting for older closed cases
    ) recent_closed_cancelled on recent_closed_cancelled.application_id = consumer_application.id
    -- join (
    --     select application_id from emi_payments where emi_amount > 0 group by 1 having(min(emi_date) <= current_date)
    -- ) current_emi on current_emi.application_id = consumer_application.id -- consider only those cases where emis have started
    where (consumer_application.deleted_at is null and consumer_application.is_deleted is null and 
        consumer_application.merchant_id not in (73, 9421) -- reporting not needed for these merchants, confirmed with collections
        and consumer_application.lender_id in (2, 5, 7, 10, 11, 20)
    )
    and (
        (consumer_application.state in ('disbursed', 'nach registered', 'cancellation in-process'))
        or 
        (consumer_application.state in ('closed', 'cancelled') and recent_closed_cancelled.application_id is not null) 
    ) -- and (consumer_application_element.type_of_entity = 'Proprietorships' or consumer_application_element.type_of_entity is null)
"""


# Bureau report data query - bureau_report_script.py
bureau_report_data_query = """ 
    with pro_forma_cte as (
        select *
        from(
            select application_id,loan_application_no,row_number() over(partition by application_id order by created_at desc) row_num
            from pro_forma_invoice
            where (deleted_at is null) and (application_id in :app_ids)
        )yy where row_num = 1
    )


    ,emi_calc_cte as (
        select *
        from(
            select application_id,upfront_from_customer,interest_rate,remaining_emi,emi,row_number() over(partition by application_id order by created_at desc) row_num
            from emi_calculation where application_id in :app_ids
        )yy where row_num = 1
    )


    ,loan_hypothecation_cases as (
        select distinct application_id
        from loan_hypothecation_model
        where (deleted_at is null) and (application_id in :app_ids)
    )


    ,status_logs_cte as (
        select application_id,min(case when status in ('disbursed','nach registered') then created_at end) disbursed_date,
        max(case when status in ('closed') then created_at end) closed_date,
        max(case when status in ('cancelled') then created_at end) cancelled_date
        from status_logs
        where (deleted_at is null) and (status in ('disbursed','nach registered','closed','cancelled')) and (application_id in :app_ids)
        group by 1
    )


    ,payment_transactions_cte as (
        select application_id, max(case when payment_for in ('EMI','Foreclosure','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment') then payment_date end) last_payment_date,
        sum(case when (payment_for in ('Write-off','written_off_principal','written_off_interest')) then amount else null end) written_off_amount,
        max(case when (payment_for in ('Write-off','written_off_principal','written_off_interest') and (reason = '' or reason is null)) then 1 else 0 end) settled_flag,
        min(case when (payment_for in ('Write-off','written_off_principal','written_off_interest') and (reason = '' or reason is null)) then payment_date else null end) settled_date
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) 
        and (payment_for in ('EMI','Foreclosure','Write-off','written_off_principal','written_off_interest','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment'))
        and (application_id in :app_ids)
        group by 1
    )


    ,payments_reason_cte as (
        select application_id, any_value(reason) as reason, min(case when reason = 'fraud_by_merchant' then payment_date else null end) merchant_fraud_date
        from payment_transactions
        where (is_deleted_at is null and deleted_at is null and refunded_at is null)
        and reason in ('365_plus_dpd','fraud_by_merchant','fraud_by_customer','non_recoverable')
        group by application_id
    )



    select consumer_application.id application_id, consumer_application.name "Consumer Name", date(consumer_application.dob) "Date of Birth",
    case when consumer_application.gender = 'MALE' then 2 when consumer_application.gender = 'FEMALE' then 1 else null end as "Gender",
    consumer_application.pan_card "Income Tax ID Number", consumer_application_additional.ckyc_number "CKYC", null "Issue Date", null "Expiration Date", null "Passport Number", null "Passport Issue Date", null "Passport Expiry Date", null "Voter ID Number",
    null "Driving License Number", null "Driving License Issue Date", null "Driving License Expiry Date", null "Ration Card Number",
    consumer_application.aadhaar_card "Universal ID Number", null "Additional ID #1",
    null "Additional ID #2", consumer_application.mobile_number "Telephone No.Mobile", null "Telephone No.Residence", null "Telephone No.Office", null "Extension Office",
    null "Telephone No.Other", null "Extension Other", consumer_application.email "Email ID 1", null "Email ID 2", 
    case when consumer_application.permanent_address_line_1 is null 
        then concat(consumer_application.current_address_line_1,', ',consumer_application.current_address_landmark) 
        else concat(consumer_application.permanent_address_line_1,', ',consumer_application.permanent_address_landmark) end "Address Line 1",
    state.code_number "State Code 1",
    if(consumer_application.permanent_address_line_1 is null,consumer_application.current_address_pincode,consumer_application.permanent_address_pincode) "PIN Code 1",
    if(consumer_application.permanent_address_line_1 is null,'02','01') as "Address Category 1",
    null "Residence Code 1", null "Address 2", null "State Code 2", null "PIN Code 2", null "Address Category 2", null "Residence Code 2",
    null "Current/New Member Code", null "Current/New Member Short Name", pro_forma_cte.loan_application_no "Curr/New Account No",
    case when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 and consumer_application_element.type_of_customer_application = 'Non-Individual'
        and consumer_application_element.type_of_customer not in (1,4) then '54'
        when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 then '00' 
        when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 then '00'
        when consumer_application.lender_id = 2 and (merchant.id = 24 or merchant.category_id = 3) then '05'
        when consumer_application.lender_id = 2 and merchant.category_id in (4, 33, 34, 35, 36, 37, 38) then '08'
        when consumer_application.lender_id = 11 and (merchant.id = 24 or merchant.category_id = 3) then '45'
        when consumer_application.lender_id = 11 and merchant.category_id in (4, 33, 34, 35, 36, 37, 38) then '47'
        else '05' end as "Account Type", 1 "Ownership Indicator",
    date(status_logs_cte.disbursed_date) "Date Opened/Disbursed", date(payment_transactions_cte.last_payment_date) "Date of Last Payment", 
    case when consumer_application.state = "closed" then date(status_logs_cte.closed_date)
        when consumer_application.state = "cancelled" then date(status_logs_cte.cancelled_date) 
        when merchant_fraud_date is not null then date(merchant_fraud_date)
        when settled_date is not null then date(settled_date) else null end "Date Closed", date(current_date()) "Date Reported",
    consumer_application.approved_amount - emi_calc_cte.upfront_from_customer "High Credit/Sanctioned Amt", 
    -- consumer_application_element.total_amount_due + consumer_application_additional.processing_fee_due as "Current Balance", 
    consumer_application_additional.principal_outstanding as "Current Balance",
    consumer_application_element.amount_over_due + consumer_application_additional.processing_fee_due as "Amt Overdue",
    consumer_application_additional.dpd as "No of Days Past Due", null "Old Mbr Code", null "Old Mbr Short Name", null "Old Acc No", null "Old Acc Type", null "Old Ownership Indicator", 
    if(suit_file.application_id is not null, '01', null) "Suit Filed / Wilful Default", if(settled_flag > 0, '03', null) "Credit Facility Status", null as "Asset Classification", null "Value of Collateral", null "Type of Collateral", null "Credit Limit",
    null "Cash Limit", emi_calc_cte.interest_rate "Rate of Interest", emi_calc_cte.remaining_emi "RepaymentTenure", emi_calc_cte.emi "EMI Amount", written_off_amount "Written - off Amount (Total)",
    null "Written - off Principal Amount", null "Settlement Amt", '03' as "Payment Frequency", null "Actual Payment Amt", null "Occupation Code", 
    consumer_application.monthly_income "Income", null "Net/Gross Income Indicator", null "Monthly/Annual Income Indicator", consumer_application.lender_id, settled_flag, payments_reason_cte.reason
    from consumer_application
    left join state on lower(state.name) = lower(coalesce(consumer_application.permanent_address_state, consumer_application.current_address_state))
    left join pro_forma_cte on pro_forma_cte.application_id = consumer_application.id
    left join loan_hypothecation_cases on loan_hypothecation_cases.application_id = consumer_application.id
    left join merchant on merchant.id = consumer_application.merchant_id
    left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
    left join status_logs_cte on status_logs_cte.application_id = consumer_application.id
    left join payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
    left join emi_calc_cte on emi_calc_cte.application_id = consumer_application.id
    left join consumer_application_additional on consumer_application_additional.application_id = consumer_application.id
    left join suit_file on suit_file.id = 
    (select min(s.id) from suit_file s where s.application_id = consumer_application.id and s.deleted_at is null)
    left join payments_reason_cte on payments_reason_cte.application_id = consumer_application.id
    where (consumer_application.id in :app_ids)


    union all


    select consumer_application.id application_id, consumer_application.co_applicant_name "Consumer Name", date(consumer_application.co_applicant_dob) "Date of Birth",
    case when consumer_application.co_applicant_gender = 'MALE' then 2 when consumer_application.co_applicant_gender = 'FEMALE' then 1 else null end as "Gender",
    consumer_application.co_applicant_pan_card "Income Tax ID Number", consumer_application_additional.ckyc_number "CKYC", null "Issue Date", null "Expiration Date", null "Passport Number", null "Passport Issue Date", null "Passport Expiry Date", null "Voter ID Number",
    null "Driving License Number", null "Driving License Issue Date", null "Driving License Expiry Date", null "Ration Card Number",
    consumer_application.co_applicant_aadhaar_card "Universal ID Number", null "Additional ID #1",
    null "Additional ID #2", consumer_application.co_applicant_mobile_number "Telephone No.Mobile", null "Telephone No.Residence", null "Telephone No.Office", null "Extension Office",
    null "Telephone No.Other", null "Extension Other", consumer_application.co_applicant_email "Email ID 1", null "Email ID 2", 
    concat(consumer_application.co_applicant_address_line_1,', ',consumer_application.co_applicant_address_landmark) "Address Line 1",
    state.code_number "State Code 1", consumer_application.co_applicant_address_pincode "PIN Code 1", '01' as "Address Category 1",
    null "Residence Code 1", null "Address 2", null "State Code 2", null "PIN Code 2", null "Address Category 2", null "Residence Code 2",
    null "Current/New Member Code", null "Current/New Member Short Name", pro_forma_cte.loan_application_no "Curr/New Account No",
    case when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 and consumer_application_element.type_of_customer_application = 'Non-Individual'
        and consumer_application_element.type_of_customer not in (1,4) then '54'
        when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 then '00' 
        when loan_hypothecation_cases.application_id is not null and merchant.category_id = 24 then '00'
        when consumer_application.lender_id = 2 and (merchant.id = 24 or merchant.category_id = 3) then '05'
        when consumer_application.lender_id = 2 and merchant.category_id in (4, 33, 34, 35, 36, 37, 38) then '08'
        when consumer_application.lender_id = 11 and (merchant.id = 24 or merchant.category_id = 3) then '45'
        when consumer_application.lender_id = 11 and merchant.category_id in (4, 33, 34, 35, 36, 37, 38) then '47'
        else '05' end as "Account Type", 4 "Ownership Indicator",
    date(status_logs_cte.disbursed_date) "Date Opened/Disbursed", date(payment_transactions_cte.last_payment_date) "Date of Last Payment", 
    case when consumer_application.state = "closed" then date(status_logs_cte.closed_date)
        when consumer_application.state = "cancelled" then date(status_logs_cte.cancelled_date)
        when merchant_fraud_date is not null then date(merchant_fraud_date)
        when settled_date is not null then date(settled_date) else null end "Date Closed", date(current_date()) "Date Reported",
    consumer_application.approved_amount - emi_calc_cte.upfront_from_customer "High Credit/Sanctioned Amt", 
    -- consumer_application_element.total_amount_due + consumer_application_additional.processing_fee_due as "Current Balance", 
    consumer_application_additional.principal_outstanding as "Current Balance",
    consumer_application_element.amount_over_due + consumer_application_additional.processing_fee_due as "Amt Overdue",
    consumer_application_additional.dpd as "No of Days Past Due", null "Old Mbr Code", null "Old Mbr Short Name", null "Old Acc No", null "Old Acc Type", null "Old Ownership Indicator", 
    if(suit_file.application_id is not null, '01', null) "Suit Filed / Wilful Default", if(settled_flag > 0, '03', null) "Credit Facility Status", null as "Asset Classification", null "Value of Collateral", null "Type of Collateral", null "Credit Limit",
    null "Cash Limit", emi_calc_cte.interest_rate "Rate of Interest", emi_calc_cte.remaining_emi "RepaymentTenure", emi_calc_cte.emi "EMI Amount", written_off_amount "Written - off Amount (Total)",
    null "Written - off Principal Amount", null "Settlement Amt", '03' as "Payment Frequency", null "Actual Payment Amt", null "Occupation Code", 
    consumer_application.co_applicant_monthly_income "Income", null "Net/Gross Income Indicator", null "Monthly/Annual Income Indicator", consumer_application.lender_id, settled_flag, payments_reason_cte.reason
    from consumer_application
    left join state on lower(state.name) = lower(consumer_application.co_applicant_address_state)
    left join pro_forma_cte on pro_forma_cte.application_id = consumer_application.id
    left join loan_hypothecation_cases on loan_hypothecation_cases.application_id = consumer_application.id
    left join merchant on merchant.id = consumer_application.merchant_id
    left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
    left join status_logs_cte on status_logs_cte.application_id = consumer_application.id
    left join payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
    left join emi_calc_cte on emi_calc_cte.application_id = consumer_application.id
    left join consumer_application_additional on consumer_application_additional.application_id = consumer_application.id
    left join suit_file on suit_file.id = 
    (select min(s.id) from suit_file s where s.application_id = consumer_application.id and s.deleted_at is null)
    left join payments_reason_cte on payments_reason_cte.application_id = consumer_application.id
    where (consumer_application.id in :app_ids) and (consumer_application.co_applicant_name is not null and consumer_application.co_applicant_name <> '')
"""


# EMI wise payment query - foreclosure_breakup.py, foreclosure_bulk_update.py
emi_wise_payments_query = """
select *
from(
    select id emi_payment_id,application_id,date_format(emi_date,'%d-%m-%Y') emi_date, date_format(due_date,'%d-%m-%Y') due_date, pre_emi_amount,
    cast(coalesce(moratorium_amount, 0) as unsigned) moratorium_amount,
    greatest(emi_amount - coalesce(moratorium_amount, 0), 0) emi_amount,
    bounce_charges,delay_penalty,payment_status,
    charges_waived,ifnull(sum(case when payment_for = 'EMI' then amount end),0) total_paid, 
    ifnull(sum(case when payment_for = 'Pre-EMI' then amount end),0) total_paid_for_bpi,
    group_concat(concat(date_format(payment_transactions_cte.payment_date, '%Y-%m-%d'), ' - ','₹',amount) order by payment_transactions_cte.payment_date) AS payment_details,
    date_format(max(payment_transactions_cte.payment_date), '%d-%m-%Y') latest_payment_date,
    date(emi_date) order_date
    from emi_payments 
    left join (
        select emi_payment_id,payment_date,amount,payment_for
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (payment_for in ('EMI','Pre-EMI'))
    )payment_transactions_cte on payment_transactions_cte.emi_payment_id = emi_payments.id
    where application_id in :application_ids
    group by 1,2,3,4,5,6,7,8,9
    
    union

    select pt.emi_payment_id, pt.application_id, date_format(pt.payment_date,'%d-%m-%Y') emi_date, date_format(pt.payment_date,'%d-%m-%Y') due_date,
    0 pre_emi_amount, 0 moratorium_amount, sum(amount) emi_amount, 0 bounce_charges, 
    0 delay_penalty, 'success' payment_status, 0 charges_waived, sum(amount) total_paid, 0 total_paid_for_bpi, 
    concat(date_format(pt.payment_date, '%Y-%m-%d'), ' - ','₹',sum(pt.amount)) as payment_details, 
    date_format(pt.payment_date, '%d-%m-%Y') latest_payment_date,
    date(payment_date) order_date
    from payment_transactions pt
    where (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null) and (pt.payment_for = 'pre_part_payment') and (pt.amount > 0)
    and pt.application_id in :application_ids
    group by pt.application_id, pt.payment_date
)yy order by application_id, date(order_date), emi_payment_id
"""

# Get net loan amount and monthly_irr_rate which will be used to reconcile payments - foreclosure_breakup.py, foreclosure_bulk_update.py
net_loan_irr_query = """
    select consumer_application.id application_id,
    coalesce(
    if(
        pro_forma_invoice.insurance_premium_by_merchant = 2,
        (consumer_application.approved_amount - emi_calculation.upfront_from_customer) + ifnull(pro_forma_invoice.insurance_premium_amount, 0), 
        (consumer_application.approved_amount - emi_calculation.upfront_from_customer)
    ),
    consumer_application.approved_amount) as net_loan_amount,
    if(if(tc.application_id is not null and tc.pre_part_payment_amount=0, 0, emi_calculation.rate) = 0,'subvention','interest') loan_type,
    ep.emis_list, if(tc.application_id is not null and tc.pre_part_payment_amount>0, 1, 0) pre_part_flag,
    emi_calculation.emi emi_value, emi_calculation.remaining_emi tenure, ep.last_emi_date
    from consumer_application
    left join emi_calculation on emi_calculation.id = 
        (select max(e.id) from emi_calculation e where e.application_id = consumer_application.id and e.offer_id = consumer_application.offer_id)
    left join pro_forma_invoice on pro_forma_invoice.id = (select max(pfi.id) from pro_forma_invoice pfi where pfi.application_id = consumer_application.id)
    left join (
        select application_id,sum(amount) pre_part_payment_amount
        from payment_transactions where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (payment_for = 'pre_part_payment')
        and (application_id in :application_ids)
        group by application_id
    ) as tc on tc.application_id = consumer_application.id -- identify pre part payment/ tenure changed cases
    left join (
        select emi_payments.application_id,group_concat(greatest(emi_amount - coalesce(moratorium_amount, 0), 0)) emis_list,date(max(emi_date)) last_emi_date 
        from emi_payments 
        where emi_amount > 0 and application_id in :application_ids group by emi_payments.application_id
    )ep on ep.application_id = consumer_application.id -- get list of all emi_amounts
    where consumer_application.id in :application_ids
"""


# Find Processing fee due & Down payment due - foreclosure_breakup.py, foreclosure_bulk_update.py
pf_dp_due_query = """
    select application_id,
    if(down_payment_collected_by = 'Collected by Creditfair',greatest(down_payment - dp_paid_amount,0),0) down_payment_due,
    if(processing_fee_type in ('Collected by Creditfair in 1st EMI','Collected by Creditfair Upfront'),greatest(processing_fee - pf_paid_amount,0),0) pf_due
    from(
        select consumer_application.id application_id,emi_calculation.upfront_from_customer down_payment,
        if(emi_calculation.upfront_from_customer > 0,
                if(pro_forma_invoice.merchant_collected_advance_amount=0,
                    'Collected by Merchant', 
                    'Collected by Creditfair'),
                'No Down Payment') down_payment_collected_by,
        ifnull(payment_transactions_cte.dp_paid_amount,0) dp_paid_amount,
        emi_calculation.processing_fee,
        if(emi_calculation.processing_fee > 0,
            if(pro_forma_invoice.processing_fee_by_merchant=1,
                'Collected by Merchant',
                if(merchant_offers.processing_fee_first_emi=1,
                    "Collected by Creditfair in 1st EMI",
                    "Collected by Creditfair Upfront"
                    )
                ),
            "No Processing Fee"
        ) processing_fee_type,
        ifnull(payment_transactions_cte.pf_paid_amount,0) pf_paid_amount
        from consumer_application
        join merchant on merchant.id = consumer_application.merchant_id
        left join emi_calculation on emi_calculation.application_id = consumer_application.id and emi_calculation.offer_id = consumer_application.offer_id
            and emi_calculation.created_at = (select max(e.created_at) from emi_calculation e where e.application_id = consumer_application.id and e.offer_id = consumer_application.offer_id)
        left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id and pro_forma_invoice.deleted_at is null
            and pro_forma_invoice.id = (select max(pfi.id) from pro_forma_invoice pfi where pfi.application_id = consumer_application.id and pfi.deleted_at is null)
        left join(
            select application_id,
            sum(case when payment_for in ('processing_fee','Processing Fee') then amount end) pf_paid_amount,
            sum(case when payment_for = 'advance_amount' then amount end) dp_paid_amount
            from payment_transactions
            where (deleted_at is null and is_deleted_at is null and refunded_at is null) and payment_for in ('processing_fee','Processing Fee','advance_amount')
            and application_id in :application_ids
            group by 1
        )payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
        left join merchant_offers on merchant_offers.id = consumer_application.offer_id
        where consumer_application.id in :application_ids
    )yy
"""


# DPD report cases query - dpd_report_script.py
""" 
Query selects distinct consumer_application IDs with these conditions:
1. Excludes applications from specific merchants (merchant IDs listed).
2. Excludes applications which are foreclosed, settled.
3. Application state must be either:
   - Active ('disbursed', 'nach registered')
4. Only includes applications where EMI payments have started 
   (emi_amount > 0 and earliest emi_date <= current date).
"""
dpd_report_cases_query = """ 
    select consumer_application.id application_id
    from consumer_application
    left join merchant on merchant.id = consumer_application.merchant_id
    left join lenders on lenders.id = consumer_application.lender_id
    left join merchant anchor on anchor.id = merchant.anchor_merchant_id
    left join merchant_category on merchant_category.id = merchant.category_id
    left join users on users.id = consumer_application.created_by -- advisor
    left join emi_payments on emi_payments.application_id = consumer_application.id and emi_payments.emi_amount > 0
        and emi_payments.emi_date = (select date(min(ep.emi_date)) from emi_payments ep where ep.application_id = consumer_application.id and ep.emi_amount > 0)
    left join status_logs on status_logs.application_id = consumer_application.id and status_logs.deleted_at is null and status_logs.status = 'disbursed'
        and status_logs.id = (select sl.id from status_logs sl where sl.application_id = consumer_application.id and
        sl.deleted_at is null and sl.status = 'disbursed' order by sl.id desc limit 1)
    where (consumer_application.deleted_at is null and consumer_application.is_deleted is null 
        and consumer_application.merchant_id not in (6865, 6600, 6517, 6598, 1806, 6478, 6746, 6754, 6418, 73, 9421, 8378)
        and consumer_application.lender_id not in (12,16,18,21))
    and (consumer_application.state in ('disbursed', 'nach registered'))
    and not exists (
    select 1 from payment_transactions pt
    where pt.application_id = consumer_application.id and (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null)
    and pt.payment_for in ('Forclouser', 'Foreclosed', 'Foreclosure', 'Write-off', 'written_off_principal', 'written_off_interest', 'written_off_bounce', 'written_off_penal', 'foreclosure_bpi', 'foreclosure_interest', 'foreclosure_principal', 'foreclosure_bounce', 'foreclosure_penal', 'foreclosure_over_payment')
    ) -- excl. foreclosed, settled, written off cases
    and not exists (
    select 1 from status_logs sls
    where sls.application_id = consumer_application.id and (sls.deleted_at is null)
    and sls.secondary_status in ('settlement-done', 'settled')
    ) -- excl. settlement-done, settled cases
    and (emi_payments.emi_date <= current_date) -- excl. cases where emi hasn't started
    and not exists (
    select 1 from consumer_application_follow_ups cafu
    where cafu.application_id = consumer_application.id
    and cafu.id = (
        select max(cafu2.id) from consumer_application_follow_ups cafu2
        where cafu2.application_id = consumer_application.id
        and cafu2.deleted_at is null
    )
    and cafu.follow_up_status = 'stopped'
    and cafu.deleted_at is null
    ) -- excl. cases where latest follow-up is stopped
"""


# DPD report data query - dpd_report_script.py
dpd_report_data_query = """ 
    with pro_forma_cte as (
        select * 
        from (
            select application_id,loan_application_no,row_number () over (partition by application_id order by id desc) as row_no 
            from pro_forma_invoice where (deleted_at is null) and (application_id in :application_ids) 
        ) a where row_no = 1
    )


    ,last_payment_cte as (
        select application_id, date(max(payment_date)) as last_payment_date
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (application_id in :application_ids)
        group by 1
    )


    ,last_payment_cte2 as (
        select ld.application_id, date_format(ld.last_payment_date, '%d-%m-%y') as last_payment_date, sum(pt.amount) as last_payment_amount
        from last_payment_cte ld
        join payment_transactions pt on pt.application_id = ld.application_id and date(pt.payment_date) = date(ld.last_payment_date)
        where (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null)
        group by ld.application_id, ld.last_payment_date
    )


    ,emi_calc_cte as (
        select *
        from(
            select application_id,upfront_from_customer,emi,row_number() over(partition by application_id order by created_at desc) row_num
            from emi_calculation where (application_id in :application_ids)
        )yy where row_num = 1
    )
    
    ,emi_payments_cte as (
        select application_id,min(emi_date) first_emi_date, max(emi_date) last_emi_date
        from emi_payments where(emi_amount > 0) and (application_id in :application_ids)
        group by 1
    )

    select consumer_application.id "Application ID",pro_forma_cte.loan_application_no "Agreement Number", consumer_application.name "Consumer Name", 
    merchant.merchant_name "Merchant Name", merchant_category.name "Segment", 
    if(merchant_location.name is null,null,concat(merchant.merchant_name, " - ", merchant_location.name)) "Sub-Dealer", 
    coalesce(products.name,consumer_application_element.product_name) "Product Name", lenders.name "Lender Name", consumer_application.mobile_number "Contact Number",
    null "Manager", null "After FLDG DPD", consumer_application.approved_amount - emi_calc_cte.upfront_from_customer "Loan Amount",
    consumer_application_additional.dpd_bucket "DPD Bucket", consumer_application_additional.dpd "DPD days", emi_calc_cte.emi "EMI Amount", 
    consumer_application_additional.emi_overdue_count "No of EMI Overdue", consumer_application_element.amount_over_due "Overdue Amount",
    consumer_application_additional.principal_outstanding "Principal Outstanding", consumer_application_element.total_amount_due "Total Outstanding",
    consumer_application.employee_status "Employment Status", company.company "Company Name", 
    concat(ifnull(consumer_application.current_address_line_1, ''), ' ', ifnull(consumer_application.current_address_landmark, '')) as "Residential Address",
    concat(ifnull(consumer_application.work_address_line_1,''), ' ', ifnull(work_address_landmark,'')) "Office Address",
    consumer_application.current_address_city "City", consumer_application.current_address_state "State", consumer_application.current_address_pincode "Pincode",
    consumer_application_element.reference_name_1 "Reference Name 1", consumer_application_element.reference_contact_1 "Reference Number 1", 
    consumer_application_element.reference_name_2 "Reference Name 2", consumer_application_element.reference_contact_2 "Reference Number 2",
    last_payment_cte2.last_payment_date "Last Payment Date", last_payment_cte2.last_payment_amount "Last Payment Amount", 
    consumer_application.monthly_income "Monthly Income", consumer_application.email "Email",  date_format(consumer_application.dob,'%d-%m-%Y') "DOB", 
    consumer_application.pan_card "PAN", emi_payments_cte.first_emi_date, emi_payments_cte.last_emi_date
    from consumer_application
    left join merchant on merchant.id = consumer_application.merchant_id
    left join merchant_category on merchant_category.id = merchant.category_id
    left join lenders on lenders.id = consumer_application.lender_id
    left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
    left join emi_calc_cte on emi_calc_cte.application_id = consumer_application.id
    left join company on company.id = consumer_application.company_name
    left join pro_forma_cte on pro_forma_cte.application_id = consumer_application.id
    left join last_payment_cte2 on last_payment_cte2.application_id = consumer_application.id
    left join merchant_location on merchant_location.id  = consumer_application_element.center_id
    left join products on products.id = coalesce(consumer_application_element.product_id,consumer_application_element.product_name)
    left join consumer_application_additional on consumer_application_additional.application_id = consumer_application.id
    left join emi_payments_cte on emi_payments_cte.application_id = consumer_application.id
    where consumer_application.id in :application_ids
"""

# loan ammortization/schedule query - loan_schedule_script.py
loan_schedule_query_prepart = """
select *
from(
    select id emi_payment_id,application_id,date_format(emi_date,'%d-%m-%Y') emi_date, date_format(due_date,'%d-%m-%Y') due_date, pre_emi_amount,
    greatest(emi_amount - coalesce(moratorium_amount, 0), 0) emi_amount,
    bounce_charges,delay_penalty,charges_waived,date(emi_date) order_date
    from emi_payments
    where application_id in :application_ids
    
    union

    select pt.emi_payment_id, pt.application_id, date_format(pt.payment_date,'%d-%m-%Y') emi_date, date_format(pt.payment_date,'%d-%m-%Y') due_date,
    0 pre_emi_amount, sum(amount) emi_amount, 0 bounce_charges, 
    0 delay_penalty, 0 charges_waived,date(payment_date) order_date
    from payment_transactions pt
    where (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null) and (pt.payment_for = 'pre_part_payment')
    and pt.application_id in :application_ids
    group by pt.application_id, pt.payment_date
)yy order by application_id, date(order_date)
"""

# emi payment id wise payment received query - loan_schedule_script.py
emi_id_wise_payments = """
    select ep.id emi_payment_id, ep.emi_date, ep.application_id, ep.bounce_charges, ep.delay_penalty, ep.charges_waived,
    ifnull(sum(case when payment_for = 'EMI' then amount end),0) total_paid, 
    ifnull(sum(case when payment_for = 'Pre-EMI' then amount end),0) total_paid_for_bpi,
    date_format(max(payment_transactions_cte.payment_date), '%d-%m-%Y') latest_payment_date
    from emi_payments ep
    left join (
            select emi_payment_id,payment_date,amount,payment_for
            from payment_transactions
            where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (payment_for in ('EMI','Pre-EMI'))
        )payment_transactions_cte on payment_transactions_cte.emi_payment_id = ep.id
    where application_id in :application_ids
    group by ep.application_id, ep.id
    order by ep.application_id, date(ep.emi_date)
"""

email_list_of_collection_and_user_types = """
    SELECT email, id
    FROM users
    WHERE user_type IN (22, 39, 14, 13, 34)
    AND deleted_at IS NULL;
"""

get_user_name_by_id_query = """
    SELECT name 
    FROM users 
    WHERE id = %s 
    LIMIT 1;
"""
insert_collection_action_query = """
    INSERT INTO collection_actions (
        application_id, 
        user_id, 
        action_date, 
        dispo_code, 
        remarks, 
        next_action_date, 
        actual_amount_to_be_collected, 
        amount_to_be_received, 
        ptp_date, 
        on_field_visit,
        created_at,
        updated_at
    ) VALUES (
        :application_id, 
        :user_id, 
        :action_date, 
        :dispo_code, 
        :remarks, 
        :next_action_date, 
        :actual_amount_to_be_collected, 
        :amount_to_be_received, 
        :ptp_date, 
        :on_field_visit,
        :created_at,
        :updated_at
    );
"""

insert_file_upload_record_query = """
    INSERT INTO collections_file_upload (
        file_name, 
        uploaded_by, 
        upload_date, 
        total_rows,
        created_at
    ) VALUES (
        :file_name, 
        :uploaded_by, 
        :upload_date, 
        :total_rows,
        :created_at
    );
"""

# fetch settled amounts for given application_id - settlement_reconcile_v2.py
settlement_reconcile_v2_query = """
    select consumer_application.id application_id,
    ifnull(sum(case when payment_for = 'settled_principal' then amount else 0 end), 0) settled_principal_amount,
    ifnull(sum(case when payment_for = 'settled_interest' then amount else 0 end), 0) settled_interest_amount,
    ifnull(sum(case when payment_for = 'settled_bounce' then amount else 0 end), 0) settled_bounce_amount,
    ifnull(sum(case when payment_for = 'settled_penal' then amount else 0 end), 0) settled_penal_amount,
    date(min(sl_settled_done.created_at)) settlement_done_date
    from consumer_application
    left join payment_transactions pt on pt.application_id = consumer_application.id
    left join status_logs sl_settled_done on sl_settled_done.id = ( -- check for given application_id if status_logs has status as settlement done
    select min(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.secondary_status = 'settlement-done' and sl.deleted_at is null
    )
    where consumer_application.id in :application_ids
    and (pt.is_deleted_at is null and pt.deleted_at is null and pt.refunded_at is null)
    group by consumer_application.id
"""

# validation query for settlement - settlement_reconcile_v2.py
settlement_validation_qry = """
    select consumer_application.id application_id, state app_status, foreclosed, pre_part_payment,
    if(isnull(sl_settled.id), settled, 1) settled, date(sl_settle_done.created_at) settled_done_date
    from consumer_application
    left join (
        select application_id,
        max(case when payment_for in ('Forclouser','Foreclosed','Foreclosure','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment') then 1 else 0 end) foreclosed,
        max(case when (payment_for in ('Write-off','written_off_principal','written_off_interest','written_off_bounce','written_off_penal')
        and (reason = '' or reason is null)) then 1 else 0 end) settled,
        max(case when payment_for = 'pre_part_payment' then 1 else 0 end) pre_part_payment
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (application_id in :application_ids)
        group by 1
    )as payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
    left join secondary_status_logs sl_settle_done on sl_settle_done.id = ( -- check for given application_id if secondary_status_logs has status as settlement_done
    select min(sl.id) from secondary_status_logs sl where sl.application_id = consumer_application.id and sl.status = 'settlement-done' and sl.deleted_at is null
    )
    left join secondary_status_logs sl_settled on sl_settled.id = ( -- check for given application_id if secondary_status_logs has status as settled
    select min(sl.id) from secondary_status_logs sl where sl.application_id = consumer_application.id and sl.status = 'settled' and sl.deleted_at is null
    )
    where consumer_application.id in :application_ids
"""


# BlackSoil RPS Queries
blacksoil_soft_delete_query = """
    UPDATE blacksoil_emi_payments 
    SET deleted_at = :deleted_at 
    WHERE loan_id = :loan_id AND deleted_at IS NULL
"""

blacksoil_insert_query = """
    INSERT INTO blacksoil_emi_payments 
    (cust_id, loan_id, emi_date, interest_due, principal_due, emi_amount, opening_pos, closing_pos, created_at, updated_at)
    VALUES 
    (:cust_id, :loan_id, :emi_date, :interest_due, :principal_due, :emi_amount, :opening_pos, :closing_pos, :created_at, :updated_at)
"""


# BlackSoil Payment Queries
blacksoil_insert_payment_query = """
    INSERT INTO blacksoil_payment_transactions 
    (emi_payment_id, amount, cust_id, loan_id, payment_for, payment_date, status, created_at, updated_at)
    VALUES 
    (:emi_payment_id, :amount, :cust_id, :loan_id, :payment_for, :payment_date, :status, :created_at, :updated_at)
"""

blacksoil_check_attributes_query = """
    SELECT loan_id FROM blacksoil_attributes WHERE loan_id IN :loan_ids
"""

blacksoil_insert_attributes_query = """
    INSERT INTO blacksoil_attributes (loan_id, dpd, created_at, updated_at) 
    VALUES (:loan_id, :dpd, :created_at, :updated_at)
"""

blacksoil_update_attributes_query = """
    UPDATE blacksoil_attributes 
    SET dpd = :dpd, updated_at = :updated_at
    WHERE loan_id = :loan_id
"""


# Merchant Collection Report Query
merchant_collection_report_query = """
    SELECT 
        ca.id AS application_id,
        pfi.loan_application_no,
        ca.mobile_number,
        ec.emi,
        (
            SELECT MAX(pt.payment_date) 
            FROM payment_transactions pt 
            WHERE pt.application_id = ca.id AND pt.status = 'success'
        ) AS last_payment_date,
        ca.name AS customer_name,
        cae.amount_over_due AS overdue_amount,
        COALESCE(ep_agg.total_delay_days, 0) AS total_delay_days,
        ca.merchant_id,
        m.merchant_name,
        m.email AS merchant_email,
        m.contact_number,
        m.rcp_mobile,
        m.sales_head_mobile,
        m.finance_head_mobile,
        ca.lender_id,
        l.name AS lender_name
    FROM consumer_application ca
    JOIN consumer_application_element cae ON ca.id = cae.application_id
    JOIN merchant m ON ca.merchant_id = m.id
    JOIN lenders l ON ca.lender_id = l.id
    LEFT JOIN emi_calculation ec ON ec.id = (
        SELECT MAX(id)
        FROM emi_calculation
        WHERE application_id = ca.id
    )
    LEFT JOIN pro_forma_invoice pfi ON pfi.id = (
        SELECT MAX(id) 
        FROM pro_forma_invoice 
        WHERE application_id = ca.id AND deleted_at IS NULL
    )
    LEFT JOIN (
        SELECT application_id, SUM(delay_days) AS total_delay_days
        FROM emi_payments
        WHERE payment_status != 'success'
        GROUP BY application_id
    ) ep_agg ON ca.id = ep_agg.application_id
    WHERE ca.state IN ('disbursed', 'nach registered')
    AND ca.is_deleted IS NULL
    AND ca.lender_id NOT IN (12, 16) 
    AND NOT EXISTS (
        SELECT 1 
        FROM payment_transactions pt_check 
        WHERE pt_check.application_id = ca.id 
          AND pt_check.payment_for = 'settled_principal'
    )
    AND m.pending_collection_report = 1
    AND m.id NOT IN (6418, 6517, 6598, 6865, 6600, 6746, 6754) 
    AND cae.amount_over_due >= 100
    AND COALESCE(ep_agg.total_delay_days, 0) > 5 
    AND ca.id NOT IN (
        SELECT application_id 
        FROM consumer_application_follow_ups
        WHERE id IN (
            SELECT MAX(id) 
            FROM consumer_application_follow_ups
            WHERE deleted_at IS NULL 
            GROUP BY application_id
        )
        AND follow_up_status = 'stopped'
    )
    ORDER BY m.merchant_name, l.name, ca.id
"""

CO_LENDING_BOUNCE_PENAL_QUERY = """
    WITH payment_transactions_cte AS (
        SELECT pt.*, pt.co_lending_emi_payments_id as emi_payment_id
        FROM co_lending_payment_transactions pt 
        WHERE pt.deleted_at IS NULL
    ),
    emi_payments_cte AS (
        SELECT 
            ep.id AS emi_payment_id,
            ep.loan_id,
            ep.rps_type,
            cll.product_id,
            cla.bounce_type,
            DATE_FORMAT(ep.emi_date,'%d-%m-%Y') AS emi_date,
            DATE_FORMAT(ep.emi_date,'%d-%m-%Y') AS due_date,
            ep.emi_amount_due AS emi_amount,
            ep.bounce_charges,
            ep.delay_penalty,
            ep.charges_waived,
            ep.bpi_due,
            ep.delay_days,
            COALESCE(SUM(ptc.amount), 0) AS total_paid,
            GROUP_CONCAT(
                CASE WHEN ptc.payment_for IN ('principal', 'interest') AND ptc.payment_date < CURRENT_DATE() THEN 
                    CONCAT('{"', DATE_FORMAT(ptc.payment_date,'%d-%m-%Y'), '"', CHAR(58), ptc.amount, '}') 
                END ORDER BY ptc.payment_date SEPARATOR ','
            ) AS payment_list,
            DATE(MAX(ptc.payment_date)) AS recent_payment_date
        FROM co_lending_emi_payments ep
        JOIN co_lending_loans cll ON cll.loan_id = ep.loan_id AND cll.deleted_at IS NULL
        LEFT JOIN co_lending_additional cla ON cla.loan_id = ep.loan_id
        LEFT JOIN payment_transactions_cte ptc ON ptc.emi_payment_id = ep.id
        WHERE ep.deleted_at IS NULL
        GROUP BY ep.id, ep.loan_id, ep.rps_type, cll.product_id, cla.bounce_type, ep.emi_date
    )
    SELECT 
        emi_payment_id,
        loan_id,
        rps_type,
        product_id,
        bounce_type,
        emi_date,
        due_date,
        emi_amount,
        bounce_charges,
        delay_penalty,
        charges_waived,
        bpi_due,
        delay_days,
        total_paid,
        CONCAT('[', IFNULL(CONCAT(payment_list, ',{"', DATE_FORMAT(CURRENT_DATE(), '%d-%m-%Y'), '"', CHAR(58), '0}'), CONCAT('{"', DATE_FORMAT(CURRENT_DATE(), '%d-%m-%Y'), '"', CHAR(58), '0}')), ']') AS payment_list,
        recent_payment_date
    FROM emi_payments_cte
    ORDER BY loan_id, emi_date
"""

UPDATE_LOANS_DATA_QUERY = """
    WITH payment_transactions_cte AS (
        SELECT co_lending_emi_payments_id as emi_payment_id, amount, payment_for
        FROM co_lending_payment_transactions 
        WHERE deleted_at IS NULL
    ),
    emi_summaries AS (
        SELECT 
            ep.loan_id,
            ep.rps_type,
            ep.id,
            ep.emi_date,
            ep.due_date,
            ep.principal_due,
            ep.interest_due,
            ep.bounce_charges,
            ep.delay_penalty,
            ep.charges_waived,
            ep.bpi_due,
            (ep.emi_amount_due + COALESCE(ep.bpi_due, 0) + COALESCE(ep.bounce_charges, 0) + COALESCE(ep.delay_penalty, 0) - COALESCE(ep.charges_waived, 0) - COALESCE(SUM(ptc.amount), 0)) AS amount_due,
            GREATEST(ep.emi_amount_due - COALESCE(SUM(ptc.amount), 0), 0) AS core_amount_due,
            ep.delay_days,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'principal' THEN ptc.amount ELSE 0 END), 0) AS principal_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'interest' THEN ptc.amount ELSE 0 END), 0) AS interest_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'bpi' THEN ptc.amount ELSE 0 END), 0) AS bpi_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'bounce' THEN ptc.amount ELSE 0 END), 0) AS bounce_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'penal' THEN ptc.amount ELSE 0 END), 0) AS penal_paid,
            COALESCE(SUM(ptc.amount), 0) AS total_paid
        FROM co_lending_emi_payments ep
        LEFT JOIN payment_transactions_cte ptc ON ptc.emi_payment_id = ep.id
        WHERE ep.deleted_at IS NULL
        GROUP BY ep.id, ep.loan_id, ep.rps_type
    )
    SELECT 
        loan_id,
        rps_type,
        GREATEST(0, SUM(bpi_due) - SUM(bpi_paid)) AS pre_emi_due,
        GREATEST(0, SUM(principal_due) - SUM(principal_paid)) AS principal_outstanding,
        GREATEST(0, SUM(interest_due) - SUM(interest_paid)) AS interest_outstanding,
        GREATEST(0, SUM(CASE WHEN emi_date <= CURRENT_DATE() THEN interest_due - interest_paid ELSE 0 END)) AS interest_due,
        GREATEST(0, SUM(bounce_charges) - SUM(bounce_paid)) AS total_bounce_due,
        GREATEST(0, SUM(delay_penalty) - SUM(penal_paid)) AS total_penal_due,
        GREATEST(0, SUM(CASE WHEN emi_date < CURRENT_DATE() THEN amount_due ELSE 0 END)) AS amount_overdue,
        GREATEST(0, SUM(principal_due - principal_paid) + SUM(interest_due - interest_paid) + SUM(bounce_charges - bounce_paid) + SUM(delay_penalty - penal_paid)) AS total_outstanding,
        MIN(CASE WHEN core_amount_due > 0 AND due_date < CURRENT_DATE() THEN due_date END) AS first_unpaid_emi_date,
        MAX(CASE WHEN core_amount_due > 0 AND due_date < CURRENT_DATE() THEN delay_days ELSE 0 END) AS dpd,
        SUM(CASE WHEN core_amount_due <= 0 THEN 1 ELSE 0 END) AS emi_paid_count,
        SUM(CASE WHEN core_amount_due > 0 THEN 1 ELSE 0 END) AS emi_unpaid_count
    FROM emi_summaries
    GROUP BY loan_id, rps_type
"""

HISTORICAL_LOANS_DATA_QUERY = """
    WITH payment_transactions_cte AS (
        SELECT co_lending_emi_payments_id as emi_payment_id, amount, payment_for
        FROM co_lending_payment_transactions 
        WHERE deleted_at IS NULL AND DATE(payment_date) <= :as_on_date
    ),
    emi_summaries AS (
        SELECT 
            ep.loan_id,
            ep.rps_type,
            ep.id,
            ep.emi_date,
            ep.due_date,
            ep.principal_due,
            ep.interest_due,
            ep.bounce_charges,
            ep.delay_penalty,
            ep.charges_waived,
            ep.bpi_due,
            (ep.emi_amount_due + COALESCE(ep.bpi_due, 0) + COALESCE(ep.bounce_charges, 0) + COALESCE(ep.delay_penalty, 0) - COALESCE(ep.charges_waived, 0) - COALESCE(SUM(ptc.amount), 0)) AS amount_due,
            GREATEST(ep.emi_amount_due - COALESCE(SUM(ptc.amount), 0), 0) AS core_amount_due,
            GREATEST(0, DATEDIFF(:as_on_date, ep.due_date)) AS delay_days,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'principal' THEN ptc.amount ELSE 0 END), 0) AS principal_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'interest' THEN ptc.amount ELSE 0 END), 0) AS interest_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'bpi' THEN ptc.amount ELSE 0 END), 0) AS bpi_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'bounce' THEN ptc.amount ELSE 0 END), 0) AS bounce_paid,
            COALESCE(SUM(CASE WHEN ptc.payment_for = 'penal' THEN ptc.amount ELSE 0 END), 0) AS penal_paid,
            COALESCE(SUM(ptc.amount), 0) AS total_paid
        FROM co_lending_emi_payments ep
        LEFT JOIN payment_transactions_cte ptc ON ptc.emi_payment_id = ep.id
        WHERE ep.deleted_at IS NULL
        GROUP BY ep.id, ep.loan_id, ep.rps_type
    )
    SELECT 
        loan_id,
        rps_type,
        GREATEST(0, SUM(bpi_due) - SUM(bpi_paid)) AS pre_emi_due,
        GREATEST(0, SUM(principal_due) - SUM(principal_paid)) AS principal_outstanding,
        GREATEST(0, SUM(interest_due) - SUM(interest_paid)) AS interest_outstanding,
        GREATEST(0, SUM(CASE WHEN emi_date <= :as_on_date THEN interest_due - interest_paid ELSE 0 END)) AS interest_due,
        GREATEST(0, SUM(CASE WHEN emi_date <= :as_on_date THEN bounce_charges ELSE 0 END) - SUM(bounce_paid)) AS total_bounce_due,
        GREATEST(0, SUM(CASE WHEN emi_date <= :as_on_date THEN delay_penalty ELSE 0 END) - SUM(penal_paid)) AS total_penal_due,
        GREATEST(0, SUM(CASE WHEN emi_date < :as_on_date THEN amount_due ELSE 0 END)) AS amount_overdue,
        GREATEST(0, SUM(principal_due - principal_paid) + SUM(interest_due - interest_paid) + SUM(CASE WHEN emi_date <= :as_on_date THEN bounce_charges ELSE 0 END - bounce_paid) + SUM(CASE WHEN emi_date <= :as_on_date THEN delay_penalty ELSE 0 END - penal_paid)) AS total_outstanding,
        MIN(CASE WHEN core_amount_due > 0 AND due_date < :as_on_date THEN due_date END) AS first_unpaid_emi_date,
        MAX(CASE WHEN core_amount_due > 0 AND due_date < :as_on_date THEN delay_days ELSE 0 END) AS dpd,
        SUM(CASE WHEN core_amount_due <= 0 THEN 1 ELSE 0 END) AS emi_paid_count,
        SUM(CASE WHEN core_amount_due > 0 THEN 1 ELSE 0 END) AS emi_unpaid_count
    FROM emi_summaries
    GROUP BY loan_id, rps_type
"""


# Pincode Master processing queries
insert_pincode_query = """
    INSERT INTO pincode_classifications 
    (upload_batch_id, pincode, type, state, city, area, location, comment, level, agency_name, user_id, created_at, updated_at) 
    VALUES 
    (:upload_batch_id, :pincode, :type, :state, :city, :area, :location, :comment, :level, :agency_name, :user_id, :created_at, :updated_at)
"""

soft_delete_pincode_query = """
    UPDATE pincode_classifications 
    SET deleted_at = NOW() 
    WHERE deleted_at IS NULL
"""

cleanup_old_pincode_batches_query = """
    DELETE FROM pincode_classifications 
    WHERE upload_batch_id IS NOT NULL 
    AND upload_batch_id NOT IN (
        SELECT batch_id FROM (
            SELECT upload_batch_id as batch_id
            FROM pincode_classifications 
            WHERE upload_batch_id IS NOT NULL 
            GROUP BY upload_batch_id 
            ORDER BY MAX(created_at) DESC 
            LIMIT 3
        ) as tmp
    )
"""

# Blacksoil DA accrued revenue queries
def get_loan_info_query(loans_str: str = None) -> str:
    loan_filter = f"and da_system_data.loan_no in ({loans_str})" if loans_str else ""
    return f"""
    with pos_cte as (
        select blacksoil_emi_payments.loan_id, 
        greatest(sum(principal_due) - ifnull(max(bpt.principal_received), 0), 0) current_principal_outstanding,
        ifnull(max(bpt.has_foreclosure), 0) as has_foreclosure
        from blacksoil_emi_payments
        left join (
            select loan_id, 
                   sum(amount) as principal_received,
                   max(case when lower(payment_for) in ('foreclosure', 'foreclosure_principal') then 1 else 0 end) as has_foreclosure
            from blacksoil_payment_transactions 
            where deleted_at is null 
            and payment_for in ('principal','foreclosure_principal','overdue_principal','prepayment_principal', 'foreclosure', 'Foreclosure')
            group by loan_id
        ) bpt on bpt.loan_id = blacksoil_emi_payments.loan_id
        where blacksoil_emi_payments.deleted_at is null
        group by blacksoil_emi_payments.loan_id
    )
    select da_system_data.loan_no, da_system_data.member_name, da_system_data.partner_id, 
           da_system_data.int_rate, ifnull(blacksoil_attributes.dpd, 0) as dpd
    from da_system_data
    left join blacksoil_attributes on blacksoil_attributes.loan_id = da_system_data.loan_no
    join pos_cte on pos_cte.loan_id = da_system_data.loan_no
    where da_system_data.deleted_at is null 
    {loan_filter}
    and pos_cte.current_principal_outstanding >= 1
    and pos_cte.has_foreclosure = 0
    """

def get_emi_schedules_query(active_loans_str: str) -> str:
    return f"""
    select loan_id, emi_date, principal_due, interest_due
    from blacksoil_emi_payments
    where deleted_at is null and loan_id in ({active_loans_str})
    order by loan_id, emi_date asc
    """

def get_payment_transactions_query(active_loans_str: str) -> str:
    return f"""
    select loan_id, amount as principal_received, payment_date, payment_for
    from blacksoil_payment_transactions
    where deleted_at is null and is_deleted_at is null and refunded_at is null
    and payment_for in ('principal','foreclosure_principal','overdue_principal','prepayment_principal', 'foreclosure', 'Foreclosure')
    and loan_id in ({active_loans_str})
    """

# CF Accounts accrued revenue queries
accrued_revenue_loans_query = """
    with base as (
        select ca.id application_id, ca.approved_amount,
        ifnull(ec.processing_fee, ec2.processing_fee) processing_fee, 
        ifnull(ec.subvention, ec2.subvention) subvention,
        ifnull(ec.interest_rate, ec2.interest_rate) interest_rate,
        ifnull(ec.upfront_from_customer, ec2.upfront_from_customer) upfront_from_customer,
        ifnull(ec.emi, ec2.emi) emi_value,
        ifnull(ec.remaining_emi, ec2.remaining_emi) remaining_emi,
        if(pfi.insurance_premium_applicable = 1, pfi.insurance_premium_amount, 0) insurance_premium_amount, 
        pfi.insurance_premium_by_merchant, l.name lender_name, ca.name customer_name, sl_disbursed.created_at disbursed_date,
        if(pt_prepartpayment.id is not null, 1, 0) pre_part_flag
        
        from consumer_application ca
        left join emi_calculation ec on ec.id = 
        (select max(e.id) from emi_calculation e where e.application_id = ca.id and e.offer_id = ca.offer_id)
        left join emi_calculation ec2 on ec2.id = 
        (select max(e.id) from emi_calculation e where e.application_id = ca.id) -- useful where offer_id does not match in ca & ec (eg: bnpl cases)
        left join pro_forma_invoice pfi on pfi.id = 
        (select max(pfi.id) from pro_forma_invoice pfi where pfi.application_id = ca.id and pfi.deleted_at is null)
        join status_logs sl_disbursed on sl_disbursed.id = 
        (select min(sl.id) from status_logs sl where sl.application_id = ca.id and sl.status in ('disbursed', 'nach registered') and sl.deleted_at is null)
        left join lenders l on l.id = ca.lender_id
        left join payment_transactions pt_prepartpayment on pt_prepartpayment.id =
        (select max(p.id) from payment_transactions p where p.application_id = ca.id and p.deleted_at is null and p.is_deleted_at is null and p.refunded_at is null 
            and p.payment_for = 'pre_part_payment')
        where ca.deleted_at is null 
        and ca.is_deleted is null 
        and ca.merchant_id not in (73, 9421)
        and ca.lender_id not in (18, 21) 
        and ca.state in ('disbursed','nach registered')
        and not exists (
            select 1 from payment_transactions pt
            where pt.deleted_at is null
                and pt.is_deleted_at is null
                and pt.refunded_at is null
                and pt.payment_for in ('Foreclosure','foreclosure_bpi','foreclosure_interest','foreclosure_principal','foreclosure_bounce','foreclosure_penal','foreclosure_over_payment','Settled','settled_bounce','settled_penal','settled_principal','Write-off','written_off_bounce','written_off_interest',
                'written_off_penal','written_off_principal')
                and pt.application_id = ca.id
            )
    )

    ,base2 as (
        select *,
        ifnull(
            if(
                insurance_premium_by_merchant = 2,
                (approved_amount - upfront_from_customer) + ifnull(insurance_premium_amount, 0), 
                (approved_amount - upfront_from_customer)
            ),
        approved_amount) as net_loan_amount,
        processing_fee - round(processing_fee - (processing_fee * (100 / 118)), 2) pf_excl_gst 
        from base
    )

    select application_id, customer_name, lender_name, disbursed_date, approved_amount, subvention, interest_rate, emi_value, remaining_emi, pf_excl_gst, net_loan_amount,
    net_loan_amount * (subvention/100) total_subvention_pool,
    net_loan_amount - (net_loan_amount * (subvention/100)) net_principal_outlay,
    net_loan_amount - pf_excl_gst - insurance_premium_amount - (net_loan_amount * (subvention/100)) outstanding_as_per_eir,
    pre_part_flag
    from base2
"""

accrued_revenue_emi_payments_query = """
    select *, pre_emi_amount + emi_amount cashflows
    from (
        select application_id, ep.id as emi_payment_id, emi_date, ifnull(pre_emi_amount, 0) as pre_emi_amount, 
        ifnull(emi_amount, 0) - ifnull(moratorium_amount, 0) as emi_amount,
        greatest(ifnull(bounce_charges, 0) - greatest(ifnull(charges_waived, 0) - ifnull(delay_penalty, 0), 0), 0) as bounce_charges,
        greatest(ifnull(delay_penalty, 0) - ifnull(charges_waived, 0), 0) as delay_penalty,
        payment_status
        from emi_payments ep
        where ep.application_id in :application_ids
        
        union all
        
        select pt.application_id, 0 as emi_payment_id, pt.payment_date as emi_date, 0 as pre_emi_amount,
        sum(pt.amount) as emi_amount, 0 as bounce_charges, 0 as delay_penalty, 'success' as payment_status
        from payment_transactions pt
        where (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null) 
        and (pt.payment_for = 'pre_part_payment') and (pt.amount > 0)
        and pt.application_id in :application_ids
        group by pt.application_id, pt.payment_date
    ) t
    order by application_id, date(emi_date), emi_payment_id
"""

upsert_accrued_revenue_query = """
INSERT INTO accrued_revenue_rps_data (
    application_id, emi_date, net_loan_amount, customer_interest_rate, company_irr, eir, opening_outstanding, 
    opening_net_principal_outlay, opening_subvention_balance, principal_payable, emi_amount, closing_outstanding, 
    revenue, interest, subvention, processing_fees, bounce_charges, penal_interest, subvention_balance, closing_net_principal_outlay, 
    opening_outstanding_as_per_eir, interest_as_per_eir, outflows, closing_outstanding_as_per_eir,
    created_at, updated_at
) VALUES (
    :application_id, :emi_date, :net_loan_amount, :customer_interest_rate, :company_irr, :eir, :opening_outstanding, :opening_net_principal_outlay, 
    :opening_subvention_balance, :principal_payable, :emi_amount, :closing_outstanding, :revenue, :interest, 
    :subvention, :processing_fees, :bounce_charges, :penal_interest, :subvention_balance, :closing_net_principal_outlay, 
    :opening_outstanding_as_per_eir, :interest_as_per_eir, :outflows, :closing_outstanding_as_per_eir,
    :now, :now
) ON DUPLICATE KEY UPDATE
    net_loan_amount = VALUES(net_loan_amount),
    customer_interest_rate = VALUES(customer_interest_rate),
    company_irr = VALUES(company_irr),
    eir = VALUES(eir),
    opening_outstanding = VALUES(opening_outstanding),
    opening_net_principal_outlay = VALUES(opening_net_principal_outlay),
    opening_subvention_balance = VALUES(opening_subvention_balance),
    principal_payable = VALUES(principal_payable),
    emi_amount = VALUES(emi_amount),
    closing_outstanding = VALUES(closing_outstanding),
    revenue = VALUES(revenue),
    interest = VALUES(interest),
    subvention = VALUES(subvention),
    processing_fees = VALUES(processing_fees),
    bounce_charges = VALUES(bounce_charges),
    penal_interest = VALUES(penal_interest),
    subvention_balance = VALUES(subvention_balance),
    closing_net_principal_outlay = VALUES(closing_net_principal_outlay),
    opening_outstanding_as_per_eir = VALUES(opening_outstanding_as_per_eir),
    interest_as_per_eir = VALUES(interest_as_per_eir),
    outflows = VALUES(outflows),
    closing_outstanding_as_per_eir = VALUES(closing_outstanding_as_per_eir),
    updated_at = VALUES(updated_at),
    deleted_at = NULL
"""

cleanup_accrued_revenue_query = """
UPDATE accrued_revenue_rps_data
SET deleted_at = :now
WHERE application_id = :application_id
AND emi_date NOT IN :valid_dates
AND deleted_at IS NULL
"""

collection_report_emi_payments_query = """
select *
from(
    select id emi_payment_id,application_id,date_format(emi_date,'%d-%m-%Y') emi_date, date_format(due_date,'%d-%m-%Y') due_date, pre_emi_amount,
    cast(coalesce(moratorium_amount, 0) as unsigned) moratorium_amount,
    greatest(emi_amount - coalesce(moratorium_amount, 0), 0) emi_amount,
    bounce_charges,delay_penalty,payment_status,
    charges_waived,ifnull(sum(case when payment_for = 'EMI' then amount end),0) total_paid, 
    ifnull(sum(case when payment_for = 'Pre-EMI' then amount end),0) total_paid_for_bpi,
    group_concat(concat(date_format(payment_transactions_cte.payment_date, '%Y-%m-%d'), ' - ','₹',amount) order by payment_transactions_cte.payment_date) AS payment_details,
    date_format(max(payment_transactions_cte.payment_date), '%d-%m-%Y') latest_payment_date,
    date(emi_date) order_date
    from emi_payments 
    left join (
        select emi_payment_id,payment_date,amount,payment_for
        from payment_transactions
        where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (payment_for in ('EMI','Pre-EMI'))
        and payment_date <= :up_to_date
    )payment_transactions_cte on payment_transactions_cte.emi_payment_id = emi_payments.id
    where application_id in :application_ids
    group by 1,2,3,4,5,6,7,8,9
    
    union

    select pt.emi_payment_id, pt.application_id, date_format(pt.payment_date,'%d-%m-%Y') emi_date, date_format(pt.payment_date,'%d-%m-%Y') due_date,
    0 pre_emi_amount, 0 moratorium_amount, sum(amount) emi_amount, 0 bounce_charges, 
    0 delay_penalty, 'success' payment_status, 0 charges_waived, sum(amount) total_paid, 0 total_paid_for_bpi, 
    concat(date_format(pt.payment_date, '%Y-%m-%d'), ' - ','₹',sum(pt.amount)) as payment_details, 
    date_format(pt.payment_date, '%d-%m-%Y') latest_payment_date,
    date(payment_date) order_date
    from payment_transactions pt
    where (pt.deleted_at is null and pt.is_deleted_at is null and pt.refunded_at is null) 
    and (pt.payment_for = 'pre_part_payment') and (pt.amount > 0)
    and pt.application_id in :application_ids
    and pt.payment_date <= :up_to_date
    group by pt.application_id, pt.payment_date
)yy order by application_id, date(order_date), emi_payment_id
"""

collection_report_additional_info_query = """
select consumer_application.id application_id, merchant.merchant_name, lenders.name lender_name, 
greatest(consumer_application_additional.dpd - 1, 0) dpd 
from consumer_application
left join merchant on merchant.id = consumer_application.merchant_id
left join lenders on lenders.id = consumer_application.lender_id
left join consumer_application_additional on consumer_application_additional.application_id = consumer_application.id
where (consumer_application.deleted_at is null and consumer_application.is_deleted is null and consumer_application.merchant_id not in (73, 9421)
and consumer_application.lender_id not in (18, 21))
and consumer_application.id in :application_ids
"""