CREATE TABLE quick_reports_staff_authorization (
  `id` INT NOT NULL AUTO_INCREMENT,
  `quickReportName` VARCHAR(100) NOT NULL,
  `staffId` INT(11) NOT NULL,
  PRIMARY KEY (`id`));

  
INSERT INTO quick_reports_staff_authorization (staffId, quickReportName) 
SELECT staffID as staffId,'StudentInfo' FROM academic_reports;

INSERT INTO quick_reports_staff_authorization (staffId, quickReportName) 
SELECT staffID as staffId,'AttendanceInfo' FROM academic_reports;

INSERT INTO quick_reports_staff_authorization (staffId, quickReportName) 
SELECT staffID as staffId,'StaffInfo' FROM academic_reports;


/*************kishan **********************/

drop table normalization_rules;
drop table normalization_rule2_assignment;
drop table normalization_rule2_exams;
drop table normalization_rule2;
drop table normalization_rule3_groupassignments;
drop table normalization_rule3_groupexams;
drop table normalization_rule3;
drop table pseudo_normalization_rule3_groupassignments;
drop table pseudo_normalization_rule3_groupexams;
drop table pseudo_normalization_rule3;


CREATE TABLE `normalization_rule` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `ruleName` varchar(50) DEFAULT NULL,
  `isapproved` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



CREATE TABLE `normalization_rule_assign` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `normalization_rule_id` int(11) NOT NULL,
  `batchID` int(8) DEFAULT NULL,
  `semID` int(4) DEFAULT NULL,
  `subjectID` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `normalization_rule_assign_fk_1` FOREIGN KEY (`normalization_rule_id`) REFERENCES `normalization_rule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;



CREATE TABLE `normalization_rule_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `normalization_rule_id` int(11) NOT NULL,
  `attn_range_from` float(6,3) NOT NULL,
  `attn_range_to` float(6,3) NOT NULL,
  `attendance_mark` float(6,3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `normalization_rule_id` (`normalization_rule_id`),
  CONSTRAINT `normalization_rule_attendance_fk_1` FOREIGN KEY (`normalization_rule_id`) REFERENCES `normalization_rule` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;



CREATE TABLE `normalization_rule_groupassignments` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `normalization_rule_id` int(11) NOT NULL,
  `assignmenttypeIDs` varchar(10) NOT NULL,
  `assignmentMark` float(6,3) DEFAULT NULL,
  `assignmentBestOf` int(11) DEFAULT NULL,
  `averageFlag` tinyint(2) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `normalization_rule_id` (`normalization_rule_id`),
  CONSTRAINT `normalization_rule_groupassignments_fk_1` FOREIGN KEY (`normalization_rule_id`) REFERENCES `normalization_rule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


CREATE TABLE `normalization_rule_groupexams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `normalization_rule_id` int(11) NOT NULL,
  `examtypeIDs` varchar(10) NOT NULL,
  `examMark` float(6,3) DEFAULT NULL,
  `examBestOf` int(4) DEFAULT NULL,
  `averageFlag` tinyint(2) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `normalization_rule_id` (`normalization_rule_id`),
  CONSTRAINT `normalization_rule_groupexams_fk_1` FOREIGN KEY (`normalization_rule_id`) REFERENCES `normalization_rule` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


alter table normalization_rule_assign add constraint uc_normalization_assign1 unique(batchID,semID,subjectID);
alter table normalise_marks add constraint uc_normalize unique(studentID,batchID,subjectID,semID,examTypeID);

ALTER TABLE  `normalization_rule_assign` ADD  `pseudosubjectID` INT( 11 ) NULL ;

ALTER TABLE  `aprove_normalise_mark` ADD  `subjectID` INT( 11 ) NOT NULL ;


 ALTER TABLE  `aprove_normalise_mark` ADD  `pseudosubjectID` INT( 11 ) NOT NULL ;
 
 alter table aprove_normalise_mark add constraint uc_approve1 unique(batchID,semID,subjectID,staffID,pseudosubjectID);


CREATE TABLE normalization_rule_examMarkRange
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	rule_groupexams_id INT NOT NULL,
	percentFrom FLOAT(5,2) NOT NULL,
	percentTo FLOAT(5,2) NOT NULL,
	marks FLOAT(5,2) NOT NULL,
	FOREIGN KEY (rule_groupexams_id) REFERENCES normalization_rule_groupexams (id) ON DELETE CASCADE
);

CREATE TABLE normalization_rule_assignmentMarkRange
(
	id INT PRIMARY KEY AUTO_INCREMENT,
	rule_groupassignment_id INT NOT NULL,
	percentFrom FLOAT(5,2) NOT NULL,
	percentTo FLOAT(5,2) NOT NULL,
	marks FLOAT(5,2) NOT NULL,
	FOREIGN KEY (rule_groupassignment_id) REFERENCES normalization_rule_groupassignments (id) ON DELETE CASCADE
);
