WITH rescheduled AS (
    SELECT
        cap.customer_id                                AS customer_id,
        cap.created_at::date                           AS date_of_reschedule,
        old_loan.id                                    AS old_loan_id,
        new_loan.id                                    AS new_loan_id
    FROM loan_app_loan AS new_loan
    INNER JOIN crm_app_purchase cap
        ON new_loan.id = cap.loan_id
    INNER JOIN crm_app_purchase cap2
        ON cap2.id = cap.parent_purchase_id
    INNER JOIN loan_app_loan AS old_loan
        ON old_loan.id = cap2.loan_id
    WHERE
        cap2.is_rescheduled = TRUE
        AND cap.created_at::date BETWEEN {'date':'start_date'} AND {'date':'end_date'}
),
 
 
old_enr AS (
    SELECT
        i.loan_id,
        MIN(i.remaining_principal_expected) AS old_loan_enr
    FROM loan_app_installment i
    JOIN rescheduled r
      ON r.old_loan_id = i.loan_id
    WHERE i.day >= r.date_of_reschedule
    GROUP BY i.loan_id
),
 
 
old_interest AS (
    SELECT
        i.loan_id,
        SUM(i.interest_expected)::numeric AS old_loan_interest
    FROM new_lms_installmentextension ie
    join loan_app_installment i on i.id = ie.installment_ptr_id
    JOIN rescheduled r
      ON r.old_loan_id = i.loan_id
    WHERE
        i.day <= r.date_of_reschedule     
        AND ie.is_interest_paid = FALSE    
    GROUP BY i.loan_id
),
 
 
old_late_fees AS (
    SELECT
        i.loan_id,
        SUM(lf.amount)::numeric AS old_loan_late_fees
    FROM new_lms_installmentlatefees lf
    JOIN new_lms_installmentextension ie
      ON lf.installment_extension_id = ie.installment_ptr_id
 
    join loan_app_installment i on i.id = lf.installment_extension_id
    JOIN rescheduled r
      ON r.old_loan_id = i.loan_id
    WHERE
        lf.is_paid = FALSE
        AND lf.is_cancelled = FALSE
        AND lf.is_disabled = FALSE
        AND lf.day <= r.date_of_reschedule
    GROUP BY i.loan_id
)
 
SELECT
    r.customer_id                                   AS "Customer ID",
    r.date_of_reschedule                            AS "Date of Reschedule",
 
 
    old_loan.id                                     AS "Old Loan ID",
    COALESCE(oe.old_loan_enr, old_loan.principle)   AS "Old Loan ENR",
    COALESCE(oi.old_loan_interest, 0)               AS "Old Loan Interest",
    COALESCE(ol.old_loan_late_fees, 0)              AS "Old Loan Late Fees",
    old_loan.principle                              AS "Old Loan Principal",
    old_loan.period_installment                     AS "Old Installment",
    old_loan.num_months                             AS "Old Loan Tenor",
    old_loan.period_interest_rate                   AS "Old Loan Period Interest Rate",
    old_loan.yearly_interest                        AS "Old Loan Yearly Interest",
    old_status.name                                 AS "Old Loan Status",
 
 
    new_loan.id                                     AS "New Loan ID",
    new_loan.principle                              AS "New Loan Principal",
    new_loan.period_installment                     AS "New Installment",
    new_loan.num_months                             AS "New Loan Tenor",
    new_loan.period_interest_rate                   AS "New Loan Period Interest Rate",
    new_loan.yearly_interest                        AS "New Loan Yearly Interest",
    new_status.name                                 AS "New Loan Status"
 
FROM rescheduled r
JOIN loan_app_loan old_loan
    ON old_loan.id = r.old_loan_id
JOIN loan_app_loan new_loan
    ON new_loan.id = r.new_loan_id
JOIN loan_app_loanstatus old_status
    ON old_status.id = old_loan.status_id
JOIN loan_app_loanstatus new_status
    ON new_status.id = new_loan.status_id
LEFT JOIN old_enr oe
    ON oe.loan_id = r.old_loan_id
LEFT JOIN old_interest oi
    ON oi.loan_id = r.old_loan_id
LEFT JOIN old_late_fees ol
    ON ol.loan_id = r.old_loan_id
ORDER BY
    r.date_of_reschedule,
    r.customer_id,
    r.old_loan_id;

V0lUSCByZXNjaGVkdWxlZCBBUyAoCiAgICBTRUxFQ1QKICAgICAgICBjYXAuY3VzdG9tZXJfaWQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTIGN1c3RvbWVyX2lkLAogICAgICAgIGNhcC5jcmVhdGVkX2F0OjpkYXRlICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgZGF0ZV9vZl9yZXNjaGVkdWxlLAogICAgICAgIG9sZF9sb2FuLmlkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgb2xkX2xvYW5faWQsCiAgICAgICAgbmV3X2xvYW4uaWQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyBuZXdfbG9hbl9pZAogICAgRlJPTSBsb2FuX2FwcF9sb2FuIEFTIG5ld19sb2FuCiAgICBJTk5FUiBKT0lOIGNybV9hcHBfcHVyY2hhc2UgY2FwCiAgICAgICAgT04gbmV3X2xvYW4uaWQgPSBjYXAubG9hbl9pZAogICAgSU5ORVIgSk9JTiBjcm1fYXBwX3B1cmNoYXNlIGNhcDIKICAgICAgICBPTiBjYXAyLmlkID0gY2FwLnBhcmVudF9wdXJjaGFzZV9pZAogICAgSU5ORVIgSk9JTiBsb2FuX2FwcF9sb2FuIEFTIG9sZF9sb2FuCiAgICAgICAgT04gb2xkX2xvYW4uaWQgPSBjYXAyLmxvYW5faWQKICAgIFdIRVJFCiAgICAgICAgY2FwMi5pc19yZXNjaGVkdWxlZCA9IFRSVUUKICAgICAgICBBTkQgY2FwLmNyZWF0ZWRfYXQ6OmRhdGUgQkVUV0VFTiB7J2RhdGUnOidzdGFydF9kYXRlJ30gQU5EIHsnZGF0ZSc6J2VuZF9kYXRlJ30KKSwKCgpvbGRfZW5yIEFTICgKICAgIFNFTEVDVAogICAgICAgIGkubG9hbl9pZCwKICAgICAgICBNSU4oaS5yZW1haW5pbmdfcHJpbmNpcGFsX2V4cGVjdGVkKSBBUyBvbGRfbG9hbl9lbnIKICAgIEZST00gbG9hbl9hcHBfaW5zdGFsbG1lbnQgaQogICAgSk9JTiByZXNjaGVkdWxlZCByCiAgICAgIE9OIHIub2xkX2xvYW5faWQgPSBpLmxvYW5faWQKICAgIFdIRVJFIGkuZGF5ID49IHIuZGF0ZV9vZl9yZXNjaGVkdWxlCiAgICBHUk9VUCBCWSBpLmxvYW5faWQKKSwKCgpvbGRfaW50ZXJlc3QgQVMgKAogICAgU0VMRUNUCiAgICAgICAgaS5sb2FuX2lkLAogICAgICAgIFNVTShpLmludGVyZXN0X2V4cGVjdGVkKTo6bnVtZXJpYyBBUyBvbGRfbG9hbl9pbnRlcmVzdAogICAgRlJPTSBuZXdfbG1zX2luc3RhbGxtZW50ZXh0ZW5zaW9uIGllCiAgICBqb2luIGxvYW5fYXBwX2luc3RhbGxtZW50IGkgb24gaS5pZCA9IGllLmluc3RhbGxtZW50X3B0cl9pZAogICAgSk9JTiByZXNjaGVkdWxlZCByCiAgICAgIE9OIHIub2xkX2xvYW5faWQgPSBpLmxvYW5faWQKICAgIFdIRVJFCiAgICAgICAgaS5kYXkgPD0gci5kYXRlX29mX3Jlc2NoZWR1bGUgICAgIAogICAgICAgIEFORCBpZS5pc19pbnRlcmVzdF9wYWlkID0gRkFMU0UgICAgCiAgICBHUk9VUCBCWSBpLmxvYW5faWQKKSwKCgpvbGRfbGF0ZV9mZWVzIEFTICgKICAgIFNFTEVDVAogICAgICAgIGkubG9hbl9pZCwKICAgICAgICBTVU0obGYuYW1vdW50KTo6bnVtZXJpYyBBUyBvbGRfbG9hbl9sYXRlX2ZlZXMKICAgIEZST00gbmV3X2xtc19pbnN0YWxsbWVudGxhdGVmZWVzIGxmCiAgICBKT0lOIG5ld19sbXNfaW5zdGFsbG1lbnRleHRlbnNpb24gaWUKICAgICAgT04gbGYuaW5zdGFsbG1lbnRfZXh0ZW5zaW9uX2lkID0gaWUuaW5zdGFsbG1lbnRfcHRyX2lkCiAgICAgIAogICAgam9pbiBsb2FuX2FwcF9pbnN0YWxsbWVudCBpIG9uIGkuaWQgPSBsZi5pbnN0YWxsbWVudF9leHRlbnNpb25faWQKICAgIEpPSU4gcmVzY2hlZHVsZWQgcgogICAgICBPTiByLm9sZF9sb2FuX2lkID0gaS5sb2FuX2lkCiAgICBXSEVSRQogICAgICAgIGxmLmlzX3BhaWQgPSBGQUxTRQogICAgICAgIEFORCBsZi5pc19jYW5jZWxsZWQgPSBGQUxTRQogICAgICAgIEFORCBsZi5pc19kaXNhYmxlZCA9IEZBTFNFCiAgICAgICAgQU5EIGxmLmRheSA8PSByLmRhdGVfb2ZfcmVzY2hlZHVsZQogICAgR1JPVVAgQlkgaS5sb2FuX2lkCikKClNFTEVDVAogICAgci5jdXN0b21lcl9pZCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIkN1c3RvbWVyIElEIiwKICAgIHIuZGF0ZV9vZl9yZXNjaGVkdWxlICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJEYXRlIG9mIFJlc2NoZWR1bGUiLAoKICAKICAgIG9sZF9sb2FuLmlkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBJRCIsCiAgICBDT0FMRVNDRShvZS5vbGRfbG9hbl9lbnIsIG9sZF9sb2FuLnByaW5jaXBsZSkgICBBUyAiT2xkIExvYW4gRU5SIiwKICAgIENPQUxFU0NFKG9pLm9sZF9sb2FuX2ludGVyZXN0LCAwKSAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBJbnRlcmVzdCIsCiAgICBDT0FMRVNDRShvbC5vbGRfbG9hbl9sYXRlX2ZlZXMsIDApICAgICAgICAgICAgICBBUyAiT2xkIExvYW4gTGF0ZSBGZWVzIiwKICAgIG9sZF9sb2FuLnByaW5jaXBsZSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBQcmluY2lwYWwiLAogICAgb2xkX2xvYW4ucGVyaW9kX2luc3RhbGxtZW50ICAgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBJbnN0YWxsbWVudCIsCiAgICBvbGRfbG9hbi5udW1fbW9udGhzICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyAiT2xkIExvYW4gVGVub3IiLAogICAgb2xkX2xvYW4ucGVyaW9kX2ludGVyZXN0X3JhdGUgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBMb2FuIFBlcmlvZCBJbnRlcmVzdCBSYXRlIiwKICAgIG9sZF9sb2FuLnllYXJseV9pbnRlcmVzdCAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBZZWFybHkgSW50ZXJlc3QiLAogICAgb2xkX3N0YXR1cy5uYW1lICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBMb2FuIFN0YXR1cyIsCgogIAogICAgbmV3X2xvYW4uaWQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIElEIiwKICAgIG5ld19sb2FuLnByaW5jaXBsZSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJOZXcgTG9hbiBQcmluY2lwYWwiLAogICAgbmV3X2xvYW4ucGVyaW9kX2luc3RhbGxtZW50ICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBJbnN0YWxsbWVudCIsCiAgICBuZXdfbG9hbi5udW1fbW9udGhzICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyAiTmV3IExvYW4gVGVub3IiLAogICAgbmV3X2xvYW4ucGVyaW9kX2ludGVyZXN0X3JhdGUgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIFBlcmlvZCBJbnRlcmVzdCBSYXRlIiwKICAgIG5ld19sb2FuLnllYXJseV9pbnRlcmVzdCAgICAgICAgICAgICAgICAgICAgICAgIEFTICJOZXcgTG9hbiBZZWFybHkgSW50ZXJlc3QiLAogICAgbmV3X3N0YXR1cy5uYW1lICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIFN0YXR1cyIKCkZST00gcmVzY2hlZHVsZWQgcgpKT0lOIGxvYW5fYXBwX2xvYW4gb2xkX2xvYW4KICAgIE9OIG9sZF9sb2FuLmlkID0gci5vbGRfbG9hbl9pZApKT0lOIGxvYW5fYXBwX2xvYW4gbmV3X2xvYW4KICAgIE9OIG5ld19sb2FuLmlkID0gci5uZXdfbG9hbl9pZApKT0lOIGxvYW5fYXBwX2xvYW5zdGF0dXMgb2xkX3N0YXR1cwogICAgT04gb2xkX3N0YXR1cy5pZCA9IG9sZF9sb2FuLnN0YXR1c19pZApKT0lOIGxvYW5fYXBwX2xvYW5zdGF0dXMgbmV3X3N0YXR1cwogICAgT04gbmV3X3N0YXR1cy5pZCA9IG5ld19sb2FuLnN0YXR1c19pZApMRUZUIEpPSU4gb2xkX2VuciBvZQogICAgT04gb2UubG9hbl9pZCA9IHIub2xkX2xvYW5faWQKTEVGVCBKT0lOIG9sZF9pbnRlcmVzdCBvaQogICAgT04gb2kubG9hbl9pZCA9IHIub2xkX2xvYW5faWQKTEVGVCBKT0lOIG9sZF9sYXRlX2ZlZXMgb2wKICAgIE9OIG9sLmxvYW5faWQgPSByLm9sZF9sb2FuX2lkCk9SREVSIEJZCiAgICByLmRhdGVfb2ZfcmVzY2hlZHVsZSwKICAgIHIuY3VzdG9tZXJfaWQsCiAgICByLm9sZF9sb2FuX2lkOw==