-- Add an id to LoanAccountSchedule because it is needed for relation to LoanScheduleInstallment
ALTER TABLE `LoanAccountSchedule` ADD COLUMN `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT  FIRST 
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`id`) 
, ADD UNIQUE INDEX `loanaccountschedule_acct_eff_idx` (`account_id` ASC, `effective_date` ASC) ;

-- -----------------------------------------------------
-- Table LoanScheduleInstallment
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanScheduleInstallment;

CREATE TABLE LoanScheduleInstallment (
  schedule_id MEDIUMINT UNSIGNED NOT NULL,
  start_installment_no SMALLINT UNSIGNED NOT NULL,
  end_installment_no SMALLINT UNSIGNED NOT NULL,
  installment_amount DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (schedule_id, start_installment_no),
  FOREIGN KEY (schedule_id)
    REFERENCES LoanAccountSchedule (id) 
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;


-- -----------------------------------------------------
-- Table LoanScheduleARLog
-- -----------------------------------------------------
DROP TABLE IF EXISTS LoanScheduleARLog;

CREATE TABLE LoanScheduleARLog (
  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;


