-- altering LoanScheduleAmountRef
set foreign_key_checks = 0;
ALTER TABLE `LoanScheduleAmountRef` ADD COLUMN `repay_freq_id` TINYINT UNSIGNED NOT NULL  AFTER `interest_rate` , 
  ADD FOREIGN KEY (`repay_freq_id` )
  REFERENCES `FrequencyMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, DROP INDEX `loanscheduleamountref_proj_num_idx` 
, ADD UNIQUE INDEX `loanscheduleamountref_proj_num_idx` (`product_id` ASC, `interest_rate` ASC, `repay_freq_id` ASC, `loan_amount` ASC, `installment_amount` ASC, `proj_num_installments` ASC, `first_repay_moratorium_days` ASC, `disbursement_date` ASC) 
, DROP INDEX `loanscheduleamountref_num_idx` 
, ADD UNIQUE INDEX `loanscheduleamountref_num_idx` (`product_id` ASC, `interest_rate` ASC, `repay_freq_id` ASC, `loan_amount` ASC, `installment_amount` ASC, `num_installments` ASC, `first_repay_moratorium_days` ASC, `disbursement_date` ASC) ;
set foreign_key_checks = 1;

-- data
select distinct schedule_amount_ref_id , repay_freq_id into outfile '/tmp/schdamount.txt' from LoanAccountSchedule;
-- awk '{printf "UPDATE LoanScheduleAmountRef SET repay_freq_id=%s WHERE id=%s;\n", $2, $1}' /tmp/schdamount.txt > updateLSAR.sql



-- altering LoanScheduleDateRef
set foreign_key_checks = 0;
ALTER TABLE `LoanScheduleDateRef` ADD COLUMN `repay_freq_id` TINYINT UNSIGNED NOT NULL  AFTER `product_id` , 
  ADD FOREIGN KEY (`repay_freq_id` )
  REFERENCES `FrequencyMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, DROP INDEX `loanscheduledateref_prod_date_num1_idx` 
, ADD UNIQUE INDEX `loanscheduledateref_proj_num_idx` (`product_id` ASC, `repay_freq_id` ASC, `disbursement_date` ASC, `first_repay_date` ASC, `proj_num_installments` ASC) 
, DROP INDEX `loanscheduledateref_prod_date_num2_idx` 
, ADD UNIQUE INDEX `loanscheduledateref_num_idx` (`product_id` ASC, `repay_freq_id` ASC, `disbursement_date` ASC, `first_repay_date` ASC, `num_installments` ASC) ;
set foreign_key_checks = 1;

select distinct schedule_date_ref_id , repay_freq_id into outfile '/tmp/schddate.txt' from LoanAccountSchedule;
-- awk '{printf "UPDATE LoanScheduleDateRef SET repay_freq_id=%s WHERE id=%s;\n", $2, $1}' /tmp/schddate.txt > updateLSDR.sql
