-- These tables need to be emptied first in order to run the below milestone stuff
DELETE FROM milestone;
DELETE FROM milestone_activity;
DELETE FROM milestone_activity_project_class;

-- Phase table
CREATE TABLE `milestone_phase` (
  `id`           BIGINT(20)   NOT NULL AUTO_INCREMENT,
  `dateCreated`  DATETIME     NOT NULL,
  `lastModified` DATETIME     NOT NULL,
  `version`      INT(11)      NOT NULL,
  `deleted`      TINYINT(1)   NOT NULL,
  `description`  VARCHAR(255) DEFAULT NULL,
  `title`        VARCHAR(255) NOT NULL,
  `sortOrder`    INT(11)      NOT NULL,
  PRIMARY KEY (`id`),
  KEY `deleted_index` (`deleted`)
)
  ENGINE =InnoDB
  DEFAULT CHARSET =latin1;

ALTER TABLE `milestone_activity` ADD `sortOrder` INT(11) DEFAULT NULL;
ALTER TABLE `milestone_activity` ADD `phase` BIGINT(20) NOT NULL;
ALTER TABLE `milestone_activity` ADD CONSTRAINT `FK42DAA8FE233E1A72` FOREIGN KEY (`phase`) REFERENCES `milestone_phase` (`id`);

DELETE FROM board_message_board_message;
DELETE FROM message_board_board_message;
DELETE FROM board_message;
DELETE FROM message_board;

DROP TABLE board_message_board_message;
DROP TABLE message_board_board_message;
DROP TABLE board_message;
DROP TABLE message_board;

DELETE FROM supervisor_board_message_supervisor_board_message;
DELETE FROM supervisor_message_board_supervisor_board_message;
DELETE FROM supervisor_board_message;
DELETE FROM supervisor_message_board;

DROP TABLE supervisor_board_message_supervisor_board_message;
DROP TABLE supervisor_message_board_supervisor_board_message;
DROP TABLE supervisor_board_message;
DROP TABLE supervisor_message_board;

DELETE FROM Student_Exemption;
DELETE FROM Exemption;

DROP TABLE Student_Exemption;
DROP TABLE Exemption;

ALTER TABLE `TurnitinSettings` ADD `lastFailedDate` DATETIME DEFAULT NULL;

DELETE FROM web_notification;
DROP TABLE web_notification;

DROP TABLE peer_queue;

ALTER TABLE checklist DROP FOREIGN KEY FK17CCD1A65563395;
ALTER TABLE checklist_upload DROP FOREIGN KEY FK6BCD32BA1F327355;

ALTER TABLE checklist DROP checkListUpload_id;
ALTER TABLE checklist_upload DROP checklist_id;

DROP TABLE checklist_upload;

DROP TABLE string_resource;

DROP TABLE weights;

ALTER TABLE `comment` CHANGE `id`  `id` BIGINT(20) NOT NULL AUTO_INCREMENT;

CREATE TABLE `Program` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime NOT NULL,
  `lastModified` datetime NOT NULL,
  `version` int(11) NOT NULL,
  `externalId` bigint(20) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `role_Program` (
  `role_id` bigint(20) NOT NULL,
  `programs_id` bigint(20) NOT NULL,
  PRIMARY KEY (`role_id`,`programs_id`),
  KEY `FK_i1i38rcos28p2hu4xgel3ftcw` (`programs_id`),
  KEY `FK_90cvbm5wx89wvlqnkq3vusner` (`role_id`),
  CONSTRAINT `FK_90cvbm5wx89wvlqnkq3vusner` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`),
  CONSTRAINT `FK_i1i38rcos28p2hu4xgel3ftcw` FOREIGN KEY (`programs_id`) REFERENCES `Program` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `newidea_student` ADD `program_id` bigint(20) DEFAULT NULL, ADD KEY `FK_c5py593l4g261jdkuvwdmcmgj` (`program_id`);

# select * from final_seminar_active_participation
# where finalSeminar_id not in
#       (select id from final_seminar);

DELETE FROM `scipro`.`final_seminar_active_participation` WHERE `final_seminar_active_participation`.`id` = 2958;
DELETE FROM `scipro`.`final_seminar_active_participation` WHERE `final_seminar_active_participation`.`id` = 2959;

# select * from final_seminar_opposition
# where finalSeminar_id not in
#       (select id from final_seminar);

DELETE FROM `scipro`.`final_seminar_opposition` WHERE `final_seminar_opposition`.`id` = 1628;
DELETE FROM `scipro`.`final_seminar_opposition` WHERE `final_seminar_opposition`.`id` = 1853;

# select * from projectPartner
# where projectClass_id not in
#       (select id from project_class);

DELETE FROM `scipro`.`projectPartner` WHERE `projectPartner`.`id` = 1;
DELETE FROM `scipro`.`projectPartner` WHERE `projectPartner`.`id` = 2;

# select * from project_student
# where project_id not in
#       (select id from project);

DELETE FROM `scipro`.`project_student` WHERE `project_student`.`project_id` = 1121 AND `project_student`.`projectParticipants_id` = 4006;

alter table final_seminar_active_participation add index FK_mk920fce29yhjgv33wr69fe8a (finalSeminar_id), add constraint FK_mk920fce29yhjgv33wr69fe8a foreign key (finalSeminar_id) references final_seminar (id);

alter table final_seminar_opposition add index FK_62i59u7j6x5ma0iydx9no6m4i (finalSeminar_id), add constraint FK_62i59u7j6x5ma0iydx9no6m4i foreign key (finalSeminar_id) references final_seminar (id);

alter table projectPartner add index FK_2ar5my1wm4p3uevf1xrrv4cgd (projectClass_id), add constraint FK_2ar5my1wm4p3uevf1xrrv4cgd foreign key (projectClass_id) references project_class (id);

alter table project_student add index FK_o3axkuf5be8j9tvi46b5uovdd (project_id), add constraint FK_o3axkuf5be8j9tvi46b5uovdd foreign key (project_id) references project (id);