-- altering LoanProduct

ALTER TABLE `LoanProduct` DROP FOREIGN KEY `LoanProduct_ibfk_4` ;
ALTER TABLE `LoanProduct` CHANGE COLUMN `installment_freq_id` `compounding_freq_id` TINYINT(3) UNSIGNED NOT NULL  , 
  ADD CONSTRAINT `LoanProduct_ibfk_4`
  FOREIGN KEY (`compounding_freq_id` )
  REFERENCES `FrequencyMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE;

-- -----------------------------------------------------
-- Table LoanProductInfo
-- -----------------------------------------------------

CREATE TABLE LoanProductInfo (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id SMALLINT UNSIGNED NOT NULL,
  repay_freq_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (product_id)
    REFERENCES LoanProduct (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (repay_freq_id)
    REFERENCES FrequencyMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loanproductinfo_prod_repay_idx ON LoanProductInfo(product_id, repay_freq_id);

-- data
INSERT INTO LoanProductInfo(product_id, repay_freq_id) SELECT id, compounding_freq_id FROM LoanProduct;


-- altering LoanAccountSchedule
set foreign_key_checks = 0;
ALTER TABLE `LoanAccountSchedule` 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;
set foreign_key_checks = 1;

-- data
select id, compounding_freq_id into outfile '/tmp/lp.txt' from LoanProduct;
-- awk '{printf "UPDATE LoanAccountSchedule SET repay_freq_id=%s WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=%s);\n", $2, $1}' /tmp/lp.txt
-- UPDATE LoanAccountSchedule SET repay_freq_id=2 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=1);
-- UPDATE LoanAccountSchedule SET repay_freq_id=2 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=2);
-- UPDATE LoanAccountSchedule SET repay_freq_id=2 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=5);
-- UPDATE LoanAccountSchedule SET repay_freq_id=2 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=6);
-- UPDATE LoanAccountSchedule SET repay_freq_id=3 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=4);
-- UPDATE LoanAccountSchedule SET repay_freq_id=3 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=7);
-- UPDATE LoanAccountSchedule SET repay_freq_id=5 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=3);
-- UPDATE LoanAccountSchedule SET repay_freq_id=5 WHERE account_id IN (SELECT id FROM LoanAccount WHERE product_id=8);


-- altering LoanAccountAtom

ALTER TABLE `LoanAccountAtom` CHANGE COLUMN `installment_freq_id` `compounding_freq_id` TINYINT(3) UNSIGNED NOT NULL,
ADD COLUMN `repay_freq_id` TINYINT(3) UNSIGNED NOT NULL after `compounding_freq_id`;

-- data
UPDATE LoanAccountAtom SET repay_freq_id = compounding_freq_id;
