-- test

-- Make watson fields in the NewIdea-entity optional (Supervisor ideas doesnt fill in Watsons when submitting
ALTER TABLE  `newidea` CHANGE  `practicalHow`  `practicalHow` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ,
CHANGE  `theoryHow`  `theoryHow` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ,
CHANGE  `what`  `what` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL ,
CHANGE  `why`  `why` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;


-- Changes to NewFirstMeeting entity
ALTER TABLE `newidea_first_meeting` ADD `room` LONGTEXT NOT NULL;
ALTER TABLE  `newidea_first_meeting` CHANGE  `description`  `description` LONGTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

-- Add scipro url to general system settings entity
alter table general_system_settings add column `sciproURL` varchar(255) NOT NULL;

-- New notification data table
CREATE TABLE `NotificationData` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `DTYPE` varchar(31) NOT NULL,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `event` varchar(255) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `seminar_id` bigint(20) DEFAULT NULL,
  `idea_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK2DCAC3554D07E0A9` (`seminar_id`),
  KEY `FK2DCAC355FCDADF61` (`idea_id`),
  KEY `FK2DCAC355C1813915` (`project_id`),
  CONSTRAINT `FK2DCAC355C1813915` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
  CONSTRAINT `FK2DCAC3554D07E0A9` FOREIGN KEY (`seminar_id`) REFERENCES `final_seminar` (`id`),
  CONSTRAINT `FK2DCAC355FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

-- New notification table
CREATE TABLE `Notification` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `absoluteURL` varchar(255) NOT NULL,
  `unread` bit(1) NOT NULL,
  `notificationData_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `mailed` bit(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK2D45DD0B895349BF` (`user_id`),
  KEY `FK2D45DD0B599425F6` (`notificationData_id`),
  CONSTRAINT `FK2D45DD0B599425F6` FOREIGN KEY (`notificationData_id`) REFERENCES `NotificationData` (`id`),
  CONSTRAINT `FK2D45DD0B895349BF` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1;

-- New notification settings table
CREATE TABLE `NotificationSettings` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `mailCompilation` bit(1) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKE466C24E895349BF` (`user_id`),
  CONSTRAINT `FKE466C24E895349BF` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

-- New notification settings_mail table
CREATE TABLE `NotificationSettings_mailType` (
  `NotificationSettings_id` bigint(20) NOT NULL,
  `mailType` bit(1) DEFAULT NULL,
  `mailType_KEY` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`NotificationSettings_id`,`mailType_KEY`),
  KEY `FKC93C7C62CBB50956` (`NotificationSettings_id`),
  CONSTRAINT `FKC93C7C62CBB50956` FOREIGN KEY (`NotificationSettings_id`) REFERENCES `NotificationSettings` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- New boolean to see if a NewIdea object is published or not
ALTER TABLE `newidea` ADD `published` bit(1) NOT NULL DEFAULT b'1';

-- New password table to store passwords for users without a kerberos login
CREATE TABLE IF NOT EXISTS `Password` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `deleted` bit(1) NOT NULL,
  `hash` tinyblob,
  `salt` tinyblob,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `FK4C641EBB895349BF` (`user_id`),
  KEY `deleted_index` (`deleted`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

ALTER TABLE `user` ADD `password_id` bigint(20) DEFAULT NULL;

-- added later to prod, should've followed with this file.
ALTER TABLE `NotificationData` ADD COLUMN `source` VARCHAR(255) DEFAULT NULL;

ALTER TABLE `NotificationData` ADD COLUMN `review_id` bigint(20) DEFAULT NULL;
ALTER TABLE `NotificationData` ADD INDEX `FK2DCAC35542E9AC7B` (`review_id`);
ALTER TABLE `NotificationData` ADD CONSTRAINT `FK2DCAC35542E9AC7B` FOREIGN KEY (`review_id`) REFERENCES `peer_review` (`id`);