-- new reorganized autogenerated sql
-- create statements first

CREATE TABLE IF NOT EXISTS `newidea` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `description` longtext,
  `externalSupervisorInfo` longtext,
  `prerequisites` longtext,
  `title` longtext NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `practicalHow` longtext NOT NULL,
  `theoryHow` longtext NOT NULL,
  `what` longtext NOT NULL,
  `why` longtext NOT NULL,
  `applicationPeriod_id` bigint(20) DEFAULT NULL,
  `creator_id` bigint(20) NOT NULL,
  `language_id` bigint(20) NOT NULL,
  `match_id` bigint(20) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `projectClass_id` bigint(20) NOT NULL,
  `researchArea_id` bigint(20) NOT NULL,
  `suggestedReviewer_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK6E051897C1813915` (`project_id`),
  KEY `FK6E051897B9431B73` (`match_id`),
  KEY `FK6E051897BEC322C1` (`applicationPeriod_id`),
  KEY `FK6E051897E20156A5` (`suggestedReviewer_id`),
  KEY `FK6E051897B2B6081F` (`projectClass_id`),
  KEY `FK6E0518974E257FBF` (`researchArea_id`),
  KEY `FK6E051897E44F4DBE` (`creator_id`),
  KEY `FK6E0518973BE9881F` (`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `newidea_first_meeting` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `description` longtext NOT NULL,
  `firstMeetingDate` datetime NOT NULL,
  `idea_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idea_id` (`idea_id`),
  KEY `FK9393AA04FCDADF61` (`idea_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `newidea_Keyword` (
  `newidea_id` bigint(20) NOT NULL,
  `keywords_id` bigint(20) NOT NULL,
  PRIMARY KEY (`newidea_id`,`keywords_id`),
  KEY `FK3707EE21BD1521C1` (`newidea_id`),
  KEY `FK3707EE21AE316F00` (`keywords_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `newidea_match` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `status` varchar(255) DEFAULT NULL,
  `changedBy_id` bigint(20) DEFAULT NULL,
  `idea_id` bigint(20) NOT NULL,
  `supervisor_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK87EA481DFCDADF61` (`idea_id`),
  KEY `FK87EA481DBCA56165` (`supervisor_id`),
  KEY `FK87EA481DA89FFB7F` (`changedBy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `newidea_student` (
  `confirmed` bit(1) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `role_id` bigint(20) NOT NULL DEFAULT '0',
  `idea_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idea_id`,`role_id`),
  KEY `FK9458BA93FCDADF61` (`idea_id`),
  KEY `FK9458BA932B6C61BA` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- then alter statements to add foreign constraints
ALTER TABLE `newidea`
  ADD CONSTRAINT `FK6E0518973BE9881F` FOREIGN KEY (`language_id`) REFERENCES `Language` (`id`),
  ADD CONSTRAINT `FK6E0518974E257FBF` FOREIGN KEY (`researchArea_id`) REFERENCES `researcharea` (`id`),
  ADD CONSTRAINT `FK6E051897B2B6081F` FOREIGN KEY (`projectClass_id`) REFERENCES `project_class` (`id`),
  ADD CONSTRAINT `FK6E051897B9431B73` FOREIGN KEY (`match_id`) REFERENCES `newidea_match` (`id`),
  ADD CONSTRAINT `FK6E051897BEC322C1` FOREIGN KEY (`applicationPeriod_id`) REFERENCES `ApplicationPeriod` (`id`),
  ADD CONSTRAINT `FK6E051897C1813915` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
  ADD CONSTRAINT `FK6E051897E20156A5` FOREIGN KEY (`suggestedReviewer_id`) REFERENCES `role` (`id`),
  ADD CONSTRAINT `FK6E051897E44F4DBE` FOREIGN KEY (`creator_id`) REFERENCES `user` (`id`);

  ALTER TABLE `newidea_first_meeting`
  ADD CONSTRAINT `FK9393AA04FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);

  ALTER TABLE `newidea_Keyword`
  ADD CONSTRAINT `FK3707EE21AE316F00` FOREIGN KEY (`keywords_id`) REFERENCES `Keyword` (`id`),
  ADD CONSTRAINT `FK3707EE21BD1521C1` FOREIGN KEY (`newidea_id`) REFERENCES `newidea` (`id`);

ALTER TABLE `newidea_match`
  ADD CONSTRAINT `FK87EA481DA89FFB7F` FOREIGN KEY (`changedBy_id`) REFERENCES `user` (`id`),
  ADD CONSTRAINT `FK87EA481DBCA56165` FOREIGN KEY (`supervisor_id`) REFERENCES `role` (`id`),
  ADD CONSTRAINT `FK87EA481DFCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);

ALTER TABLE `newidea_student`
  ADD CONSTRAINT `FK9458BA932B6C61BA` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  ADD CONSTRAINT `FK9458BA93FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`);

-- the sql below is impossible to run as it requires creating tables that have mutual restraints
-- CREATE TABLE `newidea` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `dateCreated` datetime NOT NULL,
--  `lastModified` datetime NOT NULL,
--  `version` int(11) NOT NULL,
--  `description` varchar(4000) DEFAULT NULL,
--  `externalSupervisorInfo` varchar(255) DEFAULT NULL,
--  `title` varchar(255) NOT NULL,
--  `type` varchar(255) DEFAULT NULL,
--  `practicalHow` varchar(4000) NOT NULL,
--  `theoryHow` varchar(4000) NOT NULL,
--  `what` varchar(4000) NOT NULL,
--  `why` varchar(4000) NOT NULL,
--  `applicationPeriod_id` bigint(20) DEFAULT NULL,
--  `creator_id` bigint(20) NOT NULL,
--  `language_id` bigint(20) NOT NULL,
--  `match_id` bigint(20) DEFAULT NULL,
--  `project_id` bigint(20) DEFAULT NULL,
--  `projectClass_id` bigint(20) NOT NULL,
--  `researchArea_id` bigint(20) NOT NULL,
--  `suggestedReviewer_id` bigint(20) DEFAULT NULL,
--  `prerequisites` varchar(4000) DEFAULT NULL,
--  PRIMARY KEY (`id`),
--  KEY `FK6E051897C1813915` (`project_id`),
--  KEY `FK6E051897B9431B73` (`match_id`),
--  KEY `FK6E051897BEC322C1` (`applicationPeriod_id`),
--  KEY `FK6E051897E20156A5` (`suggestedReviewer_id`),
--  KEY `FK6E051897B2B6081F` (`projectClass_id`),
--  KEY `FK6E0518974E257FBF` (`researchArea_id`),
--  KEY `FK6E051897E44F4DBE` (`creator_id`),
--  KEY `FK6E0518973BE9881F` (`language_id`),
--  CONSTRAINT `FK6E0518973BE9881F` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`),
--  CONSTRAINT `FK6E0518974E257FBF` FOREIGN KEY (`researchArea_id`) REFERENCES `researcharea` (`id`),
--  CONSTRAINT `FK6E051897B2B6081F` FOREIGN KEY (`projectClass_id`) REFERENCES `project_class` (`id`),
--  CONSTRAINT `FK6E051897B9431B73` FOREIGN KEY (`match_id`) REFERENCES `newidea_match` (`id`),
--  CONSTRAINT `FK6E051897BEC322C1` FOREIGN KEY (`applicationPeriod_id`) REFERENCES `ApplicationPeriod` (`id`),
--  CONSTRAINT `FK6E051897C1813915` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`),
--  CONSTRAINT `FK6E051897E20156A5` FOREIGN KEY (`suggestedReviewer_id`) REFERENCES `role` (`id`),
--  CONSTRAINT `FK6E051897E44F4DBE` FOREIGN KEY (`creator_id`) REFERENCES `user` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
-- CREATE TABLE `newidea_first_meeting` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `description` longtext NOT NULL,
--  `firstMeetingDate` datetime NOT NULL,
--  `idea_id` bigint(20) NOT NULL,
--  PRIMARY KEY (`id`),
--  UNIQUE KEY `idea_id` (`idea_id`),
--  KEY `FK9393AA04FCDADF61` (`idea_id`),
--  CONSTRAINT `FK9393AA04FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
-- CREATE TABLE `newidea_keyword` (
--  `newidea_id` bigint(20) NOT NULL,
--  `keywords_id` bigint(20) NOT NULL,
--  PRIMARY KEY (`newidea_id`,`keywords_id`),
--  KEY `FK3707EE21BD1521C1` (`newidea_id`),
--  KEY `FK3707EE21AE316F00` (`keywords_id`),
--  CONSTRAINT `FK3707EE21AE316F00` FOREIGN KEY (`keywords_id`) REFERENCES `Keyword` (`id`),
--  CONSTRAINT `FK3707EE21BD1521C1` FOREIGN KEY (`newidea_id`) REFERENCES `newidea` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
-- CREATE TABLE `newidea_match` (
--  `id` bigint(20) NOT NULL AUTO_INCREMENT,
--  `dateCreated` datetime NOT NULL,
--  `lastModified` datetime NOT NULL,
--  `version` int(11) NOT NULL,
--  `status` varchar(255) DEFAULT NULL,
--  `changedBy_id` bigint(20) DEFAULT NULL,
--  `idea_id` bigint(20) NOT NULL,
--  `supervisor_id` bigint(20) DEFAULT NULL,
--  `comment` longtext,
--  PRIMARY KEY (`id`),
--  KEY `FK87EA481DFCDADF61` (`idea_id`),
--  KEY `FK87EA481DBCA56165` (`supervisor_id`),
--  KEY `FK87EA481DA89FFB7F` (`changedBy_id`),
--  CONSTRAINT `FK87EA481DA89FFB7F` FOREIGN KEY (`changedBy_id`) REFERENCES `user` (`id`),
--  CONSTRAINT `FK87EA481DBCA56165` FOREIGN KEY (`supervisor_id`) REFERENCES `role` (`id`),
--  CONSTRAINT `FK87EA481DFCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
-- CREATE TABLE `newidea_student` (
--  `confirmed` bit(1) DEFAULT NULL,
--  `dateCreated` datetime NOT NULL,
--  `role_id` bigint(20) NOT NULL DEFAULT '0',
--  `idea_id` bigint(20) NOT NULL DEFAULT '0',
--  PRIMARY KEY (`idea_id`,`role_id`),
--  KEY `FK9458BA93FCDADF61` (`idea_id`),
--  KEY `FK9458BA932B6C61BA` (`role_id`),
--  CONSTRAINT `FK9458BA932B6C61BA` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
--  CONSTRAINT `FK9458BA93FCDADF61` FOREIGN KEY (`idea_id`) REFERENCES `newidea` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1