/*********************** Ranjith Balachandran April 10,2018 **********************/

CREATE TABLE `cgpaToPercentageConversion_constants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constantName` varchar(45) DEFAULT NULL,
  `constantValue` float(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `constantName_UNIQUE` (`constantName`)
) ENGINE=InnoDB;

  
CREATE TABLE `cgpaToPercentageConversion_formula` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formulaName` varchar(50) DEFAULT NULL,
  `formula` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `cgpaToPercentageConversion_cgpaRange` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cgpaFrom` float(5,2) DEFAULT NULL,
  `cgpaTo` float(5,2) DEFAULT NULL,
  `ruleAppliedYear` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `cgpaRange_formula_subjectCategory_relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cgpaToPercentageConversion_formula_id` INT(10) DEFAULT NULL,
  `cgpaToPercentageConversion_cgpaRange_id` INT(10) DEFAULT NULL,
  `subject_category_id` INT(10) DEFAULT NULL,
  `departments_id` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `courseConductContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `certificateType` INT(3) DEFAULT NULL,
  `courseConductContent` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
  

ALTER TABLE cgpaToPercentageConversion_cgpaRange ADD CONSTRAINT cgpaRangeUnique UNIQUE (cgpaFrom, cgpaTo, ruleAppliedYear);

CREATE TABLE `ccPrefix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ccPrefix` varchar(50) DEFAULT NULL,
  `prefixYear` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE `studentConductCertificateNo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentaccount_id` INT(10) DEFAULT NULL,
  `conductCertificateNo` varchar(50) DEFAULT NULL,
  `prefixYear` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ALTER TABLE ccPrefix ADD CONSTRAINT ccPrefixYearUnique UNIQUE (ccPrefix, prefixYear);
ALTER TABLE studentConductCertificateNo ADD CONSTRAINT studentCcNoUnique UNIQUE (studentaccount_id, conductCertificateNo);

CREATE TABLE `collegeExtraDetailsSettings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bankName` varchar(30) DEFAULT NULL,
  `bankCode` varchar(30) DEFAULT NULL,
  `accountNo` varchar(30) DEFAULT NULL,
  `branchName` varchar(20) DEFAULT NULL,
  `place` varchar(50) DEFAULT NULL,
  `feeCode` varchar(50) DEFAULT NULL,
  `examType` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/** stthomas **/
INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Thrissur','supplementary');

INSERT INTO collegeExtraDetailsSettings (bankName,bankCode,accountNo,place,feeCode,examType) VALUES ('South Indian Bank','SIB','0368073000000815','Thrissur','466','regular');

INSERT INTO collegeExtraDetailsSettings (bankName,bankCode,accountNo,place,examType) VALUES ('State Bank of India','SBI','34398192504','Thrissur','revaluation');



/** vimala **/
INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Thrissur','supplementary');


INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Thrissur','revaluation');

/** stjosephs **/
INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Thrissur','supplementary');


INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Thrissur','revaluation');

/** mes **/
INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Malappuram','supplementary');


INSERT INTO collegeExtraDetailsSettings (place,examType) VALUES ('Malappuram','revaluation');

ALTER TABLE gracemarks_student_marks ADD COLUMN adminUpdatedMarks INT DEFAULT NULL;