fineract-provider/src/main/resources/sql/migrations/core_db/V313__multi_rescheduling_script.sql

--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements. See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership. The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
--

ALTER TABLE `m_loan_term_variations`
    ADD COLUMN `is_active` tinyint NOT NULL DEFAULT '1' AFTER `applied_on_loan_status`;

ALTER TABLE `m_loan_term_variations`
    ADD COLUMN `parent_id` BIGINT NULL DEFAULT NULL AFTER `is_active`;

ALTER TABLE `m_loan_term_variations`
    ADD COLUMN `reshedule_request_id` BIGINT NULL DEFAULT NULL AFTER `parent_id`;

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 7 term_type,
 ifnull(mlrr.adjusted_due_date,mlrr.reschedule_from_date) applicable_date,
 mlrr.grace_on_interest decimal_value,
 null date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 null parent_id
from m_loan_reschedule_request mlrr
where mlrr.grace_on_interest is not null);

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 8 term_type,
 ifnull(mlrr.adjusted_due_date,mlrr.reschedule_from_date) applicable_date,
 mlrr.grace_on_principal decimal_value,
 null date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 null parent_id
from m_loan_reschedule_request mlrr
where mlrr.grace_on_principal is not null);

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 4 term_type,
 mlrr.reschedule_from_date applicable_date,
 null decimal_value,
 mlrr.adjusted_due_date date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 null parent_id
from m_loan_reschedule_request mlrr
where mlrr.adjusted_due_date is not null);

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 9 term_type,
 ifnull(mlrr.adjusted_due_date,mlrr.reschedule_from_date) applicable_date,
 mlrr.extra_terms decimal_value,
 null date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 null parent_id
from m_loan_reschedule_request mlrr
where mlrr.extra_terms is not null);

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 2 term_type,
 ifnull(mlrr.adjusted_due_date,mlrr.reschedule_from_date) applicable_date,
 mlrr.interest_rate decimal_value,
 null date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 null parent_id
from m_loan_reschedule_request mlrr
where mlrr.interest_rate is not null);

insert into m_loan_term_variations(`loan_id`, `term_type`, `applicable_date`, `decimal_value`, `date_value` , `is_specific_to_installment`, `applied_on_loan_status`, `reshedule_request_id` , `is_active` , `parent_id`)
(
select
mlrr.loan_id,
 9 term_type,
 mlrr.reschedule_from_date applicable_date,
 if(ifnull(mlrr.grace_on_principal,0) > ifnull(mlrr.grace_on_interest,0),mlrr.grace_on_principal,mlrr.grace_on_interest) decimal_value,
 null date_value,
 1 is_specific_to_installment,
 300 applied_on_loan_status_id,
 mlrr.id reshedule_request_id,
 if(mlrr.status_enum = 200 ,1,0) is_active,
 (select distinct mlt.id
 from m_loan_term_variations mlt
 where mlt.reshedule_request_id is not null
 and (mlt.term_type =8 or mlt.term_type = 7)
 and mlt.reshedule_request_id = mlrr.id
 group by mlt.id
 order by mlt.term_type desc limit 1) parent_id
from m_loan_reschedule_request mlrr
where (mlrr.grace_on_interest is not null or mlrr.grace_on_principal is not null));

ALTER TABLE `m_loan_reschedule_request`
    DROP COLUMN `grace_on_principal`,
    DROP COLUMN `grace_on_interest`,
    DROP COLUMN `extra_terms`,
    DROP COLUMN `interest_rate`,
    DROP COLUMN `adjusted_due_date`;

CREATE TABLE `m_loan_reschedule_request_term_variations_mapping` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `loan_reschedule_request_id` BIGINT NOT NULL,
    `loan_term_variations_id` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK__m_loan_reschedule_request` (`loan_reschedule_request_id`),
    INDEX `FK__m_loan_term_variations` (`loan_term_variations_id`),
    CONSTRAINT `FK__m_loan_reschedule_request` FOREIGN KEY (`loan_reschedule_request_id`) REFERENCES `m_loan_reschedule_request` (`id`),
    CONSTRAINT `FK__m_loan_term_variations` FOREIGN KEY (`loan_term_variations_id`) REFERENCES `m_loan_term_variations` (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

insert ignore into m_loan_reschedule_request_term_variations_mapping (`loan_reschedule_request_id`, `loan_term_variations_id`)
(
select distinct mltv.reshedule_request_id,mltv.id
from m_loan_term_variations mltv
where reshedule_request_id is not null
);

ALTER TABLE `m_loan_term_variations`
    DROP COLUMN `reshedule_request_id`;