/** SUJESH T 30-8-2017**/

CREATE TABLE `arts_category` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `categoryName` VARCHAR(45) NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),UNIQUE INDEX `code` (`code` ASC));


CREATE TABLE `arts_subcategory` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `arts_category_id` INT(11) NOT NULL,
  `subcategoryName` VARCHAR(45) NOT NULL,
   `code` VARCHAR(45) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  PRIMARY KEY (`id`),UNIQUE INDEX `code` (`code` ASC),
  INDEX `fk_arts_subcategory_1_idx` (`arts_category_id` ASC),
  CONSTRAINT `fk_arts_subcategory_1`
    FOREIGN KEY (`arts_category_id`)
    REFERENCES `arts_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

    CREATE TABLE `arts_event_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `eventTypeName` VARCHAR(45) NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `approveFlag` INT(11) NOT NULL,
  PRIMARY KEY (`id`));


  CREATE TABLE `arts_events` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `arts_category_id` INT(11) NOT NULL,
  `arts_subcategory_id` INT(11) NOT NULL,
  `event_name` VARCHAR(45) NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `contestantNo` INT(11) NOT NULL,
  `helpersNo` INT(11) NULL,
  `arts_event_type_id` INT(11) NULL,
  `timeDuration` INT(11) NULL,
  `createdDate` DATETIME NULL,
  `updatedDate` DATETIME NULL,
  `createdBy` INT(11) NULL,
  `updatedBy` INT(11) NULL,
  PRIMARY KEY (`id`),UNIQUE INDEX `code` (`code` ASC),
  INDEX `fk_arts_events_2_idx` (`arts_subcategory_id` ASC),
  INDEX `fk_arts_events_3_idx` (`arts_event_type_id` ASC),
  INDEX `fk_arts_events_1_idx` (`arts_category_id` ASC),
  CONSTRAINT `fk_arts_events_1`
    FOREIGN KEY (`arts_category_id`)
    REFERENCES `arts_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_arts_events_2`
    FOREIGN KEY (`arts_subcategory_id`)
    REFERENCES `arts_subcategory` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_arts_events_3`
    FOREIGN KEY (`arts_event_type_id`)
    REFERENCES `arts_event_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE TABLE `arts_event_rule` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `arts_event_type_id` INT(11) NOT NULL,
  `entityType` VARCHAR(45) NOT NULL,
  `count` INT(11) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy(11)` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_arts_event_rule_1_idx` (`arts_event_type_id` ASC),
  CONSTRAINT `fk_arts_event_rule_1`
    FOREIGN KEY (`arts_event_type_id`)
    REFERENCES `arts_event_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


CREATE TABLE `arts_student_events_reg` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `studentId` INT(11) NOT NULL,
  `statusFlag` INT(11) NULL,
  `arts_event_id` INT(11) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_arts_student_events_reg_1_idx` (`arts_event_id` ASC),
  CONSTRAINT `fk_arts_student_events_reg_1`
    FOREIGN KEY (`arts_event_id`)
    REFERENCES `arts_events` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);



    CREATE TABLE `arts_group_name` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `groupName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));


  CREATE TABLE `arts_approved_students` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `arts_group_name_id` INT(11) NOT NULL,
  `arts_event_id` INT(11) NOT NULL,
  `studentId` INT(11) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_arts_approved_students_1_idx` (`arts_group_name_id` ASC),
  INDEX `fk_arts_approved_students_2_idx` (`arts_event_id` ASC),
  CONSTRAINT `fk_arts_approved_students_1`
    FOREIGN KEY (`arts_group_name_id`)
    REFERENCES `arts_group_name` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_arts_approved_students_2`
    FOREIGN KEY (`arts_event_id`)
    REFERENCES `arts_events` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);



  CREATE TABLE `arts_admin_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `adminTypeName` VARCHAR(45) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`));


CREATE TABLE `arts_admin_privilages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `arts_admin_type_id` INT(11) NOT NULL,
  `menuItems` VARCHAR(45) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_arts_admin_privilages_1_idx` (`arts_admin_type_id` ASC),
  CONSTRAINT `fk_arts_admin_privilages_1`
    FOREIGN KEY (`arts_admin_type_id`)
    REFERENCES `arts_admin_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);




CREATE TABLE `arts_admin_account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `userName` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `contactPhone` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `arts_admin_type_id` INT(11) NOT NULL,
  `createdBy` INT(11) NOT NULL,
  `updatedBy` INT(11) NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_arts_admin_account_1_idx` (`arts_admin_type_id` ASC),
  CONSTRAINT `fk_arts_admin_account_1`
    FOREIGN KEY (`arts_admin_type_id`)
    REFERENCES `arts_admin_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);






ALTER TABLE `arts_category` 
ADD COLUMN `createdBy` INT(11) NOT NULL AFTER `code`,
ADD COLUMN `updatedBy` INT(11) NOT NULL AFTER `createdBy`,
ADD COLUMN `createdDate` DATETIME NOT NULL AFTER `updatedBy`,
ADD COLUMN `updatedDate` DATETIME NOT NULL AFTER `createdDate`;


ALTER TABLE `arts_event_type` 
ADD COLUMN `createdBy` INT(11) NOT NULL AFTER `approveFlag`,
ADD COLUMN `updatedBy` INT(11) NOT NULL AFTER `createdBy`,
ADD COLUMN `createdDate` DATETIME NOT NULL AFTER `updatedBy`,
ADD COLUMN `updatedDate` DATETIME NOT NULL AFTER `createdDate`;


ALTER TABLE `arts_event_type` 
CHANGE COLUMN `approveFlag` `approveFlag` INT(11) NOT NULL DEFAULT 0 ;


ALTER TABLE `arts_event_type` 
ADD UNIQUE INDEX `code_UNIQUE` (`code` ASC);


ALTER TABLE `arts_group_name` 
ADD COLUMN `createdDate` DATETIME NOT NULL AFTER `groupName`,
ADD COLUMN `updatedDate` DATETIME NOT NULL AFTER `createdDate`,
ADD COLUMN `createdBy` INT(11) NOT NULL AFTER `updatedDate`,
ADD COLUMN `updatedBy` INT(11) NOT NULL AFTER `createdBy`;

ALTER TABLE `arts_group_name` 
ADD COLUMN `code` VARCHAR(45) NOT NULL AFTER `updatedBy`,
ADD UNIQUE INDEX `code_UNIQUE` (`code` ASC);


ALTER TABLE `arts_approved_students` 
DROP FOREIGN KEY `fk_arts_approved_students_1`;
ALTER TABLE `arts_approved_students` 
CHANGE COLUMN `arts_group_name_id` `arts_group_name_id` INT(11) NULL ;
ALTER TABLE `arts_approved_students` 
ADD CONSTRAINT `fk_arts_approved_students_1`
  FOREIGN KEY (`arts_group_name_id`)
  REFERENCES `arts_group_name` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

/** Gadheyan **/
ALTER TABLE `arts_student_events_reg` 
DROP FOREIGN KEY `fk_arts_student_events_reg_1`;
ALTER TABLE `arts_student_events_reg` 
ADD CONSTRAINT `fk_arts_student_events_reg_1`
  FOREIGN KEY (`arts_event_id`)
  REFERENCES `arts_events` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;
  
ALTER TABLE `arts_student_events_reg` ADD UNIQUE `studentId_eventId_index`(`studentId`, `arts_event_id`);

ALTER TABLE arts_event_rule 
CHANGE COLUMN `updatedBy(11)` `updatedBy` INT(11) NOT NULL ;


/** Gadheyan **/

ALTER TABLE `arts_group_name` 
ADD COLUMN `deptId` INT NOT NULL AFTER `code`;

/** Gadheyan **/

ALTER TABLE `arts_group_name` 
CHANGE COLUMN `code` `code` VARCHAR(45) NOT NULL AFTER `groupName`,
CHANGE COLUMN `deptId` `deptId` INT(11) NOT NULL AFTER `code`;

/** Gadheyan **/

ALTER TABLE `arts_event_rule` 
ADD COLUMN `categoryId` INT NULL AFTER `count`,
ADD COLUMN `subcategoryId` INT NULL AFTER `categoryId`;


/**SUJESH T**/

CREATE TABLE `arts_admin_department` (
  `id` INT(11) NOT NULL,
  `arts_admin_account_id` INT(11) NOT NULL,
  `deptId` INT(11) NOT NULL,
  PRIMARY KEY (`id`));

  ALTER TABLE `arts_admin_department` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;


ALTER TABLE `arts_admin_department` ADD UNIQUE `adminDepartmentID`(`arts_admin_account_id`, `deptId`);


/**Kishan **/
alter table arts_admin_account add unique (userName);


alter table arts_admin_type add unique (adminTypeName);

alter table arts_event_rule add unique (arts_event_type_id,entityType,categoryId,subcategoryId);

alter table arts_events add unique (arts_category_id,arts_subcategory_id,event_name,code,arts_event_type_id);



-- Gadheyan[9-10-2017 11:34PM] --

ALTER TABLE `arts_group_name` 
ADD COLUMN `arts_event_id` INT NOT NULL AFTER `code`;


























