UPDATE FrequencyMaster SET id = 9 where id = 7;
UPDATE FrequencyMaster SET id = 8 where id = 6;
INSERT INTO FrequencyMaster(id, name) VALUES
(6, 'Quarterly'),
(7, 'Half-Yearly');


ALTER TABLE DepositAccount 
DROP COLUMN `proj_maturity_date`, 
ADD COLUMN `num_payment_periods` SMALLINT UNSIGNED NOT NULL  AFTER `closing_date` ;

ALTER TABLE `DepositScheduleAmountRef`
CHANGE COLUMN `num_payments` `num_payment_periods` SMALLINT(5) UNSIGNED NOT NULL ,
DROP COLUMN `maturity_amount` , 
ADD COLUMN `payment_freq_id` TINYINT UNSIGNED NOT NULL  AFTER `interest_rate` , 
ADD FOREIGN KEY (`payment_freq_id` )
  REFERENCES `FrequencyMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE, 
DROP INDEX `depositscheduleamountref_num_idx` , 
ADD UNIQUE INDEX `depositscheduleamountref_num_idx` (`product_id` ASC, `interest_rate` ASC, `payment_freq_id` ASC, `deposit_amount` ASC, `num_payment_periods` ASC, `deposit_date` ASC) ;

ALTER TABLE `DepositScheduleAmount` 
CHANGE COLUMN `payment_no` `payment_period_no` SMALLINT(5) UNSIGNED NOT NULL,
CHANGE COLUMN `total_deposited_amount` `total_value` DECIMAL(15,6) NOT NULL  ;

ALTER TABLE `DepositScheduleDateRef` CHANGE COLUMN `num_payments` `num_payment_periods` SMALLINT(5) UNSIGNED NOT NULL  ;
ALTER TABLE `DepositScheduleDateRef` ADD COLUMN `payment_freq_id` TINYINT UNSIGNED NOT NULL  AFTER `product_id` , 
ADD FOREIGN KEY (`payment_freq_id` )
  REFERENCES `FrequencyMaster` (`id` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE
, DROP INDEX `depositscheduledateref_prod_date_idx` 
, ADD UNIQUE INDEX `depositscheduledateref_num_idx` (`product_id` ASC, `payment_freq_id` ASC, `deposit_date` ASC, `num_payment_periods` ASC) ;

ALTER TABLE `DepositScheduleDate` 
CHANGE COLUMN `payment_no` `payment_period_no` SMALLINT(5) UNSIGNED NOT NULL  ,
CHANGE COLUMN `payment_date` `payment_period_date` DATE NOT NULL  ;

-- -----------------------------------------------------
-- Table DepositAccountARLog
-- -----------------------------------------------------

CREATE TABLE DepositAccountARLog (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id MEDIUMINT UNSIGNED NOT NULL,
  action_id TINYINT UNSIGNED NOT NULL,
  model VARCHAR(45) NOT NULL,
  id_model INTEGER UNSIGNED DEFAULT NULL,
  field VARCHAR(45) DEFAULT NULL,
  old_value VARCHAR(200) DEFAULT NULL,
  new_value VARCHAR(200) DEFAULT NULL,
  logged_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id)
    REFERENCES User (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (action_id)
    REFERENCES LogActionTypeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
