DROP TABLE IF EXISTS LoanAccountFee;

CREATE TABLE LoanAccountFee (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  account_id MEDIUMINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  type_id TINYINT UNSIGNED NOT NULL,
  category_id TINYINT UNSIGNED NOT NULL,
  freq_id TINYINT UNSIGNED NOT NULL,
  periodic_freq_id TINYINT UNSIGNED DEFAULT NULL,
  calc_type_id TINYINT UNSIGNED NOT NULL,
  charge_time_id TINYINT UNSIGNED DEFAULT NULL,
  amount MEDIUMINT UNSIGNED DEFAULT NULL,
  rate DECIMAL(13,10) DEFAULT NULL,
  rate_type_id TINYINT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (account_id)
    REFERENCES LoanAccount (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (type_id)
    REFERENCES FeeTypeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (category_id)
    REFERENCES FeeCategoryMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (freq_id)
    REFERENCES FeeFrequencyMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (periodic_freq_id)
    REFERENCES FrequencyMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (calc_type_id)
    REFERENCES FeeCalcTypeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (charge_time_id)
    REFERENCES FeeChargeTimeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  FOREIGN KEY (rate_type_id)
    REFERENCES FeeRateTypeMaster (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE UNIQUE INDEX loanaccountfee_acc_id_name_idx ON LoanAccountFee(account_id, name);
