ALTER TABLE `LoanInstallmentTypeMaster` 
DROP INDEX `loaninstallmenttype_name_idx` 
, ADD UNIQUE INDEX `loaninstallmentcalctype_name_idx` (`name` ASC) 
, DROP INDEX `loaninstallmenttype_abbr_idx` 
, ADD UNIQUE INDEX `loaninstallmentcalctype_abbr_idx` (`abbr` ASC) , RENAME TO  `LoanInstallmentCalcTypeMaster` ;


ALTER TABLE `LoanProduct` DROP FOREIGN KEY `LoanProduct_ibfk_11` ;
ALTER TABLE `LoanProduct` CHANGE COLUMN `installment_type_id` `installment_calc_type_id` TINYINT(3) UNSIGNED NOT NULL  , 
  ADD CONSTRAINT `LoanProduct_ibfk_11`
  FOREIGN KEY (`installment_calc_type_id` )
  REFERENCES `LoanInstallmentCalcTypeMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, DROP INDEX `installment_type_id` 
, ADD INDEX `installment_calc_type_id` (`installment_calc_type_id` ASC) ;


-- -----------------------------------------------------
-- Table LoanInstallmentTypeMaster
-- -----------------------------------------------------

CREATE TABLE LoanInstallmentTypeMaster (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loaninstallmenttype_name_idx ON LoanInstallmentTypeMaster(name);


--
-- Master table values for LoanInstallmentTypeMaster
--
DELETE FROM LoanInstallmentTypeMaster;
INSERT INTO LoanInstallmentTypeMaster(id, name) VALUES
(1, 'Advance'),
(2, 'Regular');


SET foreign_key_checks = 0;
ALTER TABLE `LoanProductInstallment` ADD COLUMN `installment_type_id` TINYINT UNSIGNED NOT NULL  AFTER `product_id` , 
  ADD CONSTRAINT `LoanProductInstallment_ibfk_2`
  FOREIGN KEY (`installment_type_id` )
  REFERENCES `LoanInstallmentTypeMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, ADD INDEX `LoanProductInstallment_ibfk_2` (`installment_type_id` ASC) ;

UPDATE LoanProductInstallment SET installment_type_id = 2;
SET foreign_key_checks = 1;
