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,
        sum(i.principal_expected + i.interest_expected) AS old_loan_enr
    FROM loan_app_installment i
    JOIN rescheduled r
      ON r.old_loan_id = i.loan_id
    join new_lms_installmentextension ie on ie.installment_ptr_id = i.id
    WHERE  i.day <= r.date_of_reschedule  and ie.status_id = 16 
    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.status_id = 16
    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
    	ie.status_id = 16  
--        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;

CldJVEggcmVzY2hlZHVsZWQgQVMgKAogICAgU0VMRUNUCiAgICAgICAgY2FwLmN1c3RvbWVyX2lkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyBjdXN0b21lcl9pZCwKICAgICAgICBjYXAuY3JlYXRlZF9hdDo6ZGF0ZSAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTIGRhdGVfb2ZfcmVzY2hlZHVsZSwKICAgICAgICBvbGRfbG9hbi5pZCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTIG9sZF9sb2FuX2lkLAogICAgICAgIG5ld19sb2FuLmlkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgbmV3X2xvYW5faWQKICAgIEZST00gbG9hbl9hcHBfbG9hbiBBUyBuZXdfbG9hbgogICAgSU5ORVIgSk9JTiBjcm1fYXBwX3B1cmNoYXNlIGNhcAogICAgICAgIE9OIG5ld19sb2FuLmlkID0gY2FwLmxvYW5faWQKICAgIElOTkVSIEpPSU4gY3JtX2FwcF9wdXJjaGFzZSBjYXAyCiAgICAgICAgT04gY2FwMi5pZCA9IGNhcC5wYXJlbnRfcHVyY2hhc2VfaWQKICAgIElOTkVSIEpPSU4gbG9hbl9hcHBfbG9hbiBBUyBvbGRfbG9hbgogICAgICAgIE9OIG9sZF9sb2FuLmlkID0gY2FwMi5sb2FuX2lkCiAgICBXSEVSRQogICAgICAgIGNhcDIuaXNfcmVzY2hlZHVsZWQgPSBUUlVFCiAgICAgICAgQU5EIGNhcC5jcmVhdGVkX2F0OjpkYXRlIEJFVFdFRU4geydkYXRlJzonc3RhcnRfZGF0ZSd9IEFORCB7J2RhdGUnOidlbmRfZGF0ZSd9CiksCgoKb2xkX2VuciBBUyAoCiAgICBTRUxFQ1QKICAgICAgICBpLmxvYW5faWQsCiAgICAgICAgc3VtKGkucHJpbmNpcGFsX2V4cGVjdGVkICsgaS5pbnRlcmVzdF9leHBlY3RlZCkgQVMgb2xkX2xvYW5fZW5yCiAgICBGUk9NIGxvYW5fYXBwX2luc3RhbGxtZW50IGkKICAgIEpPSU4gcmVzY2hlZHVsZWQgcgogICAgICBPTiByLm9sZF9sb2FuX2lkID0gaS5sb2FuX2lkCiAgICBqb2luIG5ld19sbXNfaW5zdGFsbG1lbnRleHRlbnNpb24gaWUgb24gaWUuaW5zdGFsbG1lbnRfcHRyX2lkID0gaS5pZAogICAgV0hFUkUgIGkuZGF5IDw9IHIuZGF0ZV9vZl9yZXNjaGVkdWxlICBhbmQgaWUuc3RhdHVzX2lkID0gMTYgCiAgICBHUk9VUCBCWSBpLmxvYW5faWQKKSwKCgoKb2xkX2ludGVyZXN0IEFTICgKICAgIFNFTEVDVAogICAgICAgIGkubG9hbl9pZCwKICAgICAgICBTVU0oaS5pbnRlcmVzdF9leHBlY3RlZCk6Om51bWVyaWMgQVMgb2xkX2xvYW5faW50ZXJlc3QKICAgIEZST00gbmV3X2xtc19pbnN0YWxsbWVudGV4dGVuc2lvbiBpZQogICAgam9pbiBsb2FuX2FwcF9pbnN0YWxsbWVudCBpIG9uIGkuaWQgPSBpZS5pbnN0YWxsbWVudF9wdHJfaWQKICAgIEpPSU4gcmVzY2hlZHVsZWQgcgogICAgICBPTiByLm9sZF9sb2FuX2lkID0gaS5sb2FuX2lkCiAgICBXSEVSRQogICAgICAgIGkuZGF5IDw9IHIuZGF0ZV9vZl9yZXNjaGVkdWxlICAgICAKICAgICAgICBBTkQgaWUuc3RhdHVzX2lkID0gMTYKICAgIEdST1VQIEJZIGkubG9hbl9pZAopLAoKCm9sZF9sYXRlX2ZlZXMgQVMgKAogICAgU0VMRUNUCiAgICAgICAgaS5sb2FuX2lkLAogICAgICAgIFNVTShsZi5hbW91bnQpOjpudW1lcmljIEFTIG9sZF9sb2FuX2xhdGVfZmVlcwogICAgRlJPTSBuZXdfbG1zX2luc3RhbGxtZW50bGF0ZWZlZXMgbGYKICAgIEpPSU4gbmV3X2xtc19pbnN0YWxsbWVudGV4dGVuc2lvbiBpZQogICAgICBPTiBsZi5pbnN0YWxsbWVudF9leHRlbnNpb25faWQgPSBpZS5pbnN0YWxsbWVudF9wdHJfaWQKICAgICAgCiAgICBqb2luIGxvYW5fYXBwX2luc3RhbGxtZW50IGkgb24gaS5pZCA9IGxmLmluc3RhbGxtZW50X2V4dGVuc2lvbl9pZAogICAgSk9JTiByZXNjaGVkdWxlZCByCiAgICAgIE9OIHIub2xkX2xvYW5faWQgPSBpLmxvYW5faWQKICAgIHdoZXJlCiAgICAJaWUuc3RhdHVzX2lkID0gMTYgIAotLSAgICAgICAgbGYuaXNfcGFpZCA9IEZBTFNFCi0tICAgICAgICBBTkQgbGYuaXNfY2FuY2VsbGVkID0gRkFMU0UKLS0gICAgICAgIEFORCBsZi5pc19kaXNhYmxlZCA9IEZBTFNFCiAgICAgICAgQU5EIGxmLmRheSA8PSByLmRhdGVfb2ZfcmVzY2hlZHVsZQogICAgR1JPVVAgQlkgaS5sb2FuX2lkCikKClNFTEVDVAogICAgci5jdXN0b21lcl9pZCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIkN1c3RvbWVyIElEIiwKICAgIHIuZGF0ZV9vZl9yZXNjaGVkdWxlICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJEYXRlIG9mIFJlc2NoZWR1bGUiLAoKICAKICAgIG9sZF9sb2FuLmlkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBJRCIsCiAgICBDT0FMRVNDRShvZS5vbGRfbG9hbl9lbnIsIG9sZF9sb2FuLnByaW5jaXBsZSkgICBBUyAiT2xkIExvYW4gRU5SIiwKICAgIENPQUxFU0NFKG9pLm9sZF9sb2FuX2ludGVyZXN0LCAwKSAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBJbnRlcmVzdCIsCiAgICBDT0FMRVNDRShvbC5vbGRfbG9hbl9sYXRlX2ZlZXMsIDApICAgICAgICAgICAgICBBUyAiT2xkIExvYW4gTGF0ZSBGZWVzIiwKICAgIG9sZF9sb2FuLnByaW5jaXBsZSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBQcmluY2lwYWwiLAogICAgb2xkX2xvYW4ucGVyaW9kX2luc3RhbGxtZW50ICAgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBJbnN0YWxsbWVudCIsCiAgICBvbGRfbG9hbi5udW1fbW9udGhzICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyAiT2xkIExvYW4gVGVub3IiLAogICAgb2xkX2xvYW4ucGVyaW9kX2ludGVyZXN0X3JhdGUgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBMb2FuIFBlcmlvZCBJbnRlcmVzdCBSYXRlIiwKICAgIG9sZF9sb2FuLnllYXJseV9pbnRlcmVzdCAgICAgICAgICAgICAgICAgICAgICAgIEFTICJPbGQgTG9hbiBZZWFybHkgSW50ZXJlc3QiLAogICAgb2xkX3N0YXR1cy5uYW1lICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk9sZCBMb2FuIFN0YXR1cyIsCgogIAogICAgbmV3X2xvYW4uaWQgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIElEIiwKICAgIG5ld19sb2FuLnByaW5jaXBsZSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEFTICJOZXcgTG9hbiBQcmluY2lwYWwiLAogICAgbmV3X2xvYW4ucGVyaW9kX2luc3RhbGxtZW50ICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBJbnN0YWxsbWVudCIsCiAgICBuZXdfbG9hbi5udW1fbW9udGhzICAgICAgICAgICAgICAgICAgICAgICAgICAgICBBUyAiTmV3IExvYW4gVGVub3IiLAogICAgbmV3X2xvYW4ucGVyaW9kX2ludGVyZXN0X3JhdGUgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIFBlcmlvZCBJbnRlcmVzdCBSYXRlIiwKICAgIG5ld19sb2FuLnllYXJseV9pbnRlcmVzdCAgICAgICAgICAgICAgICAgICAgICAgIEFTICJOZXcgTG9hbiBZZWFybHkgSW50ZXJlc3QiLAogICAgbmV3X3N0YXR1cy5uYW1lICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgIk5ldyBMb2FuIFN0YXR1cyIKCkZST00gcmVzY2hlZHVsZWQgcgpKT0lOIGxvYW5fYXBwX2xvYW4gb2xkX2xvYW4KICAgIE9OIG9sZF9sb2FuLmlkID0gci5vbGRfbG9hbl9pZApKT0lOIGxvYW5fYXBwX2xvYW4gbmV3X2xvYW4KICAgIE9OIG5ld19sb2FuLmlkID0gci5uZXdfbG9hbl9pZApKT0lOIGxvYW5fYXBwX2xvYW5zdGF0dXMgb2xkX3N0YXR1cwogICAgT04gb2xkX3N0YXR1cy5pZCA9IG9sZF9sb2FuLnN0YXR1c19pZApKT0lOIGxvYW5fYXBwX2xvYW5zdGF0dXMgbmV3X3N0YXR1cwogICAgT04gbmV3X3N0YXR1cy5pZCA9IG5ld19sb2FuLnN0YXR1c19pZApMRUZUIEpPSU4gb2xkX2VuciBvZQogICAgT04gb2UubG9hbl9pZCA9IHIub2xkX2xvYW5faWQKTEVGVCBKT0lOIG9sZF9pbnRlcmVzdCBvaQogICAgT04gb2kubG9hbl9pZCA9IHIub2xkX2xvYW5faWQKTEVGVCBKT0lOIG9sZF9sYXRlX2ZlZXMgb2wKICAgIE9OIG9sLmxvYW5faWQgPSByLm9sZF9sb2FuX2lkCk9SREVSIEJZCiAgICByLmRhdGVfb2ZfcmVzY2hlZHVsZSwKICAgIHIuY3VzdG9tZXJfaWQsCiAgICByLm9sZF9sb2FuX2lkOw==