START TRANSACTION;

# exam_period_options
# 1. создать поле RecordBookID

ALTER TABLE `exam_period_options`
  ADD COLUMN `RecordBookID` int(11),
  ADD KEY (`RecordBookID`);

# discipline_students
# 1. создать поле RecordBookID

ALTER TABLE `disciplines_students`
  ADD COLUMN `RecordBookID` int(11),
  ADD KEY (`RecordBookID`);

COMMIT;

LOCK TABLES exam_period_options WRITE, disciplines_students WRITE, submodules WRITE, modules READ,
disciplines WRITE, students_groups READ, record_books WRITE;

-- --------------------------------------------------------
# exam_period_options
# 1. создать поле RecordBookID - done
# 2. заполнить поле RecordBookID
# 3. удалить все связи поля StudentID и потом само поле
# 4. создать связи для поля RecordBookID

UPDATE exam_period_options
  INNER JOIN submodules on submodules.ID = exam_period_options.SubmoduleID
  INNER JOIN modules on modules.ID = submodules.ModuleID
  INNER JOIN disciplines on disciplines.ID = modules.DisciplineID
  INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
                                AND students_groups.StudentID = exam_period_options.StudentID
SET exam_period_options.RecordBookID = students_groups.RecordBookID;

ALTER TABLE `exam_period_options`
  DROP FOREIGN KEY `exam_period_options_ibfk_2`,
  DROP INDEX `StudentID`,
  DROP INDEX `StudentID_2`,
  DROP INDEX `StudentID_3`,
  DROP FOREIGN KEY `exam_period_options_ibfk_1`,
  DROP INDEX `SubmoduleID`,
  DROP INDEX `SubmoduleID_2`,
  DROP COLUMN `StudentID`;

ALTER TABLE `exam_period_options`
  ADD KEY (`SubmoduleID`),
  ADD CONSTRAINT `Unique` UNIQUE (`RecordBookID`, `SubmoduleID`),
  ADD CONSTRAINT `exam_period_options_ibfk_1` FOREIGN KEY (`SubmoduleID`) REFERENCES `submodules` (`ID`),
  ADD CONSTRAINT `exam_period_options_ibfk_2` FOREIGN KEY (`RecordBookID`) REFERENCES `record_books` (`ID`);


-- --------------------------------------------------------
# discipline_students
# 1. создать поле RecordBookID - done
# 2. заполнить поле RecordBookID
# 3. удалить все связи поля StudentID и потом само поле
# 4. создать связи для поля RecordBookID


UPDATE `disciplines_students`
  INNER JOIN disciplines on disciplines.ID = disciplines_students.DisciplineID
  INNER JOIN students_groups on students_groups.SemesterID = disciplines.SemesterID
                                AND students_groups.StudentID = disciplines_students.StudentID
SET disciplines_students.RecordBookID = students_groups.RecordBookID;

ALTER TABLE `disciplines_students`
  DROP FOREIGN KEY `disciplines_students_ibfk_2`,
  DROP INDEX DisciplineID_2,
  DROP INDEX `StudentID`,
  DROP COLUMN `StudentID`;

ALTER TABLE `disciplines_students`
  ADD CONSTRAINT `Unique` UNIQUE (`RecordBookID`, `DisciplineID`),
  ADD CONSTRAINT `disciplines_students_ibfk_2` FOREIGN KEY (`RecordBookID`) REFERENCES `record_books` (`ID`);

UNLOCK TABLES ;