CREATE TABLE `supervisoridea` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateCreated` datetime NOT NULL,
 `lastModified` datetime NOT NULL,
 `version` int(11) NOT NULL,
 `title` varchar(255) NOT NULL,
 `practicalHow` varchar(4000) NOT NULL,
 `theoryHow` varchar(4000) NOT NULL,
 `what` varchar(4000) NOT NULL,
 `why` varchar(4000) NOT NULL,
 `creator_id` bigint(20) NOT NULL,
 `project_id` bigint(20) DEFAULT NULL,
 `projectClass_id` bigint(20) NOT NULL,
 `applicationPeriod_id` bigint(20) DEFAULT NULL,
 `description` varchar(4000) NOT NULL,
 `requirements` varchar(1024) NOT NULL,
 `ideaStatus` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `IDEAPROJECTKEY` (`project_id`),
 KEY `IDEAPROJECTCLASSKEY` (`projectClass_id`),
 KEY `IDEAPERIODKEY` (`applicationPeriod_id`),
 KEY `IDEACREATORKEY` (`creator_id`), 
 CONSTRAINT `IDEAPROJECTCLASSKEY` FOREIGN KEY (`projectClass_id`) REFERENCES `project_class` (`id`),
 CONSTRAINT `IDEAPERIODKEY` FOREIGN KEY (`applicationPeriod_id`) REFERENCES `ApplicationPeriod` (`id`),
 CONSTRAINT `IDEAPROJECTKEY` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
 CONSTRAINT `IDEACREATORKEY` FOREIGN KEY (`creator_id`) REFERENCES `role` (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- CREATE TABLE `supervisoridea_Keyword` (
--  `supervisoridea_id` bigint(20) NOT NULL,
--  `keywords_id` bigint(20) NOT NULL,
--  PRIMARY KEY (`supervisoridea_id`,`keywords_id`),
--  KEY `supervisoridea_keyword_key` (`supervisoridea_id`),
--  KEY `supervisoridea_keyword_key2` (`keywords_id`),
--  CONSTRAINT `supervisoridea_keyword_key` FOREIGN KEY (`supervisoridea_id`) REFERENCES `supervisoridea` (`id`),
--  CONSTRAINT `supervisoridea_keyword_key2` FOREIGN KEY (`keywords_id`) REFERENCES `Keyword` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `supervisoridea_Keyword` (
  `supervisoridea_id` bigint(20) NOT NULL,
  `keywords_id` bigint(20) NOT NULL,
  PRIMARY KEY (`supervisoridea_id`,`keywords_id`),
  KEY `FK9842BE9AE316F00` (`keywords_id`),
  KEY `FK9842BE98DE85053` (`supervisoridea_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `idea_student` (
`role_id` bigint(20) NOT NULL,
 `supervisoridea_id` bigint(20) NOT NULL,
 `confirmed` bit(1) NOT NULL,
 PRIMARY KEY (`role_id`,`supervisoridea_id`),
 KEY `fk_role_id` (`role_id`),
 KEY `fk_supervisoridea_id` (`supervisoridea_id`),
 CONSTRAINT `fk_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
 CONSTRAINT `fk_supervisoridea_id` FOREIGN KEY (`supervisoridea_id`) REFERENCES `supervisoridea` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

 ALTER TABLE `ApplicationPeriod` ADD  `courseStartDate` datetime NOT NULL;

 CREATE TABLE `researcharea` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dateCreated` datetime NOT NULL,
 `lastModified` datetime NOT NULL,
 `identifier` bigint(20) DEFAULT NULL,
 `title` varchar(255) NOT NULL,
 `version` int(4) NOT NULL DEFAULT '0',
 `deleted` bit(1) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `identifier` (`identifier`),
 KEY `deleted_index` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `employee_researcharea` (
 `role_id` bigint(20) NOT NULL,
 `researchAreas_id` bigint(20) NOT NULL,
 PRIMARY KEY (`role_id`,`researchAreas_id`),
 KEY `employee_researcharea_key` (`role_id`),
 KEY `employee_researcharea_key2` (`researchAreas_id`),
 CONSTRAINT `employee_researcharea_key` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
 CONSTRAINT `employee_researcharea_key2` FOREIGN KEY (`researchAreas_id`) REFERENCES `researcharea` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;