INSERT INTO USER_ROLE (ROLE, NAME) VALUES('ROLE_MANAGER', 'Manager');

ALTER TABLE USERS ENGINE=InnoDB;

ALTER TABLE TIMESHEET_LOCK ENGINE=InnoDB;

CREATE TABLE TIMESHEET_LOCK_EXCLUSION (
  LOCK_ID INT(11) NOT NULL,
  USER_ID INT(11) NOT NULL,
  PRIMARY KEY (LOCK_ID, USER_ID),
  INDEX IDX_LOCK_ID (LOCK_ID ASC),
  INDEX fk_TIMESHEET_LOCK_EXCLUSION_1_idx (USER_ID ASC),
  CONSTRAINT FK_EXCLUSION_USER FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID),
  CONSTRAINT FK_EXCLUSION_LOCK FOREIGN KEY (LOCK_ID) REFERENCES TIMESHEET_LOCK (LOCK_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VALUE) VALUES('reminderEnabled', 'false');
INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VALUE) VALUES('reminderBody', 'Hello $name,\r\n\r\nThis is an automated message.\r\n\r\nOur records show that you have not posted your weekly hours online. Please be sure to post your hours by 5:30PM Friday.\r\n\r\nThank You,\r\n\r\neHour');
INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VALUE) VALUES('reminderTime', '0 30 17 * * FRI');
INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VALUE) VALUES('reminderSubject', 'Missing hours');
INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VALUE) VALUES('reminderMinimalHours', '32');

ALTER TABLE MAIL_LOG DROP FOREIGN KEY MAIL_LOG_fk;
ALTER TABLE MAIL_LOG DROP FOREIGN KEY MAIL_LOG_fk1;

DROP TABLE MAIL_TYPE;
DROP TABLE MAIL_LOG_ASSIGNMENT;

ALTER TABLE MAIL_LOG DROP COLUMN RESULT_MSG;
ALTER TABLE MAIL_LOG DROP COLUMN MAIL_TYPE_ID;
ALTER TABLE MAIL_LOG DROP COLUMN TO_USER_ID;
ALTER TABLE MAIL_LOG ADD COLUMN MAIL_EVENT VARCHAR(64);
ALTER TABLE MAIL_LOG ADD COLUMN MAIL_TO VARCHAR(255);
CREATE INDEX IDX_MAIL ON MAIL_LOG (MAIL_TO, MAIL_EVENT);

ALTER TABLE CONFIGURATION MODIFY CONFIG_VALUE VARCHAR(4096);

UPDATE CONFIGURATION SET CONFIG_VALUE = '1.4' WHERE CONFIG_KEY = 'version';