ALTER TABLE `subject_category` 
ADD COLUMN `code` VARCHAR(45) NULL AFTER `subjectcatCode`;


CREATE TABLE `moderation_max_mark_each_sub` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `moderation_rule_id` INT(11) NULL,
  `examId` INT(11) NULL,
  `max_mark` INT(11) NULL,
  `batchId` INT(11) NULL,
  `exam_registration_id` INT(11) NULL,
  `exam_supplementary_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

ALTER TABLE exam_supplementary ADD COLUMN isSpecialExam INT DEFAULT 0;

CREATE TABLE special_exam_assigned_students(
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  studentaccount_id INT(8) DEFAULT NULL,
  batches_id INT(8) DEFAULT NULL,
  exam_registration_id INT(8) DEFAULT NULL,
  exam_supplementary_id INT(8) DEFAULT NULL,
  exam_id INT(8) DEFAULT NULL,
  createdBy INT(8) NOT NULL,
  updatedBy INT(8) NOT NULL,
  createdDate date NOT NULL,
  updatedDate date NOT NULL
) ENGINE=INNODB;

ALTER TABLE special_exam_assigned_students ADD CONSTRAINT unique_ID UNIQUE (studentaccount_id, exam_supplementary_id,batches_id,exam_id);

ALTER TABLE exam_supplementary ADD COLUMN considerFlag INT(8) DEFAULT NULL;

ALTER TABLE gracemarks_initiate_mark_entry ADD CONSTRAINT unique_ID UNIQUE (gracemarks_event_category_id,gracemarks_appln_initiate_id);

DROP INDEX examIDs ON supply_improve_subject_fees;

ALTER TABLE supply_improve_subject_fees ADD CONSTRAINT unique_ID UNIQUE (exam_supplementary_id,examID,examfeesID);





alter table examcontroller_privileges add column menuActions varchar(750);
alter table examcontroller_privileges add column nodeAnalyser varchar(50);
alter table examcontroller_privileges add column permissions tinyint(4);


update examcontroller_privileges set menuActions="p_list,p_edit,pdel_cnf,p_delete", nodeAnalyser="1-1-2" where menuItems="admin_accounts";




/*************************kishan quiz ************************/


CREATE TABLE `quiz` (
  `quizID` int(11) NOT NULL AUTO_INCREMENT,
  `quizName` varchar(150) DEFAULT NULL,
  `quizDesc` varchar(300) DEFAULT NULL,
  `deptID` int(11) DEFAULT NULL,
  `subjectID` int(11) DEFAULT NULL,
  `sbsID` int(11) DEFAULT NULL,
  `passPercentage` float(6,3) DEFAULT NULL,
  `timeLImit` int(11) DEFAULT NULL,
  `immediate_result` int(11) DEFAULT '0',
  `show_correctans` int(11) DEFAULT '0',
  `unlocked` int(11) DEFAULT '0',
  `subbatchIDs` varchar(50) DEFAULT '0',
  `startDate` date DEFAULT NULL,
  `startTime` time DEFAULT NULL,
  `endDate` date DEFAULT NULL,
  `endTime` time DEFAULT NULL,
  `noOfQuestionsPerPage` int(11) DEFAULT NULL,
  `is_question_shuffle` tinyint(1) DEFAULT '0',
  `batchID` int(11) DEFAULT NULL,
  PRIMARY KEY (`quizID`),
  UNIQUE KEY `uc_quiz` (`quizName`,`batchID`,`subjectID`),
  KEY `deptsubid` (`deptID`,`subjectID`)
) ENGINE=InnoDB;


CREATE TABLE `quiz_student_answer_starttime` (
  `studentID` int(11) DEFAULT NULL,
  `quizID` int(11) DEFAULT NULL,
  `timeStart` int(11) DEFAULT '0',
  `timeEnd` int(11) DEFAULT '0',
  `is_attended` tinyint(1) DEFAULT '0',
  UNIQUE KEY `studentID` (`studentID`,`quizID`),
  KEY `quizID` (`quizID`),
  CONSTRAINT `quiz_student_answer_starttime_ibfk_1` FOREIGN KEY (`quizID`) REFERENCES `quiz` (`quizID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `student_quiz_result` (
  `studentID` int(11) NOT NULL,
  `quizID` int(8) NOT NULL,
  `points` int(8) DEFAULT NULL,
  `maxPoints` int(8) DEFAULT NULL,
  `submitTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeTaken` int(8) DEFAULT NULL,
  `incomplete` int(11) DEFAULT '0'
) ENGINE=InnoDB;


CREATE TABLE `quiz_subject_questions` (
  `questionID` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(767) DEFAULT NULL,
  `deptID` int(11) DEFAULT NULL,
  `subjectID` int(11) DEFAULT NULL,
  `sbsID` int(11) DEFAULT NULL,
  `public_question` int(11) DEFAULT '1',
  PRIMARY KEY (`questionID`),
  UNIQUE KEY `question` (`question`,`deptID`,`subjectID`,`sbsID`),
  KEY `deptsubid` (`deptID`,`subjectID`)
) ENGINE=InnoDB;


CREATE TABLE `quiz_quiz_questions` (
  `quizquestID` int(11) NOT NULL AUTO_INCREMENT,
  `quizID` int(11) DEFAULT NULL,
  `questionID` int(11) DEFAULT NULL,
  `question_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`quizquestID`),
  UNIQUE KEY `quizID` (`quizID`,`questionID`),
  KEY `quizquestion` (`quizID`,`questionID`),
  KEY `questionID` (`questionID`),
  CONSTRAINT `quiz_quiz_questions_ibfk_1` FOREIGN KEY (`quizID`) REFERENCES `quiz` (`quizID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `quiz_quiz_questions_ibfk_2` FOREIGN KEY (`questionID`) REFERENCES `quiz_subject_questions` (`questionID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB; 


CREATE TABLE `quiz_quiz_question_answers` (
  `answerID` int(11) NOT NULL AUTO_INCREMENT,
  `quizquestID` int(11) DEFAULT NULL,
  `answer` varchar(150) DEFAULT NULL,
  `point` float(6,3) DEFAULT '0.000',
  PRIMARY KEY (`answerID`),
  UNIQUE KEY `quizquestID` (`quizquestID`,`answer`),
  KEY `quizquest` (`quizquestID`),
  CONSTRAINT `quiz_quiz_question_answers_ibfk_1` FOREIGN KEY (`quizquestID`) REFERENCES `quiz_quiz_questions` (`quizquestID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


CREATE TABLE `quiz_studentanswer` (
  `studentID` int(11) DEFAULT NULL,
  `quizquestID` int(11) DEFAULT NULL,
  `answerID` int(11) DEFAULT NULL,
  UNIQUE KEY `studentID` (`studentID`,`quizquestID`),
  KEY `quizquestID` (`quizquestID`),
  KEY `answerID` (`answerID`),
  CONSTRAINT `quiz_studentanswer_ibfk_1` FOREIGN KEY (`quizquestID`) REFERENCES `quiz_quiz_questions` (`quizquestID`) ON UPDATE CASCADE,
  CONSTRAINT `quiz_studentanswer_ibfk_2` FOREIGN KEY (`answerID`) REFERENCES `quiz_quiz_question_answers` (`answerID`) ON UPDATE CASCADE
) ENGINE=InnoDB;




CREATE TABLE `quiz_subject_question_answers` (
  `answerID` int(11) NOT NULL AUTO_INCREMENT,
  `questionID` int(11) DEFAULT NULL,
  `answer` varchar(150) DEFAULT NULL,
  `point` float(6,3) DEFAULT '0.000',
  PRIMARY KEY (`answerID`),
  UNIQUE KEY `questionID` (`questionID`,`answer`),
  KEY `questid` (`questionID`),
  CONSTRAINT `quiz_subject_question_answers_ibfk_1` FOREIGN KEY (`questionID`) REFERENCES `quiz_subject_questions` (`questionID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


