-- Gadheyan[]

CREATE TABLE `arts_section` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `createdBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedBy` INT NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

 
 CREATE TABLE `arts_event_section` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `arts_event_id` INT NOT NULL,
  `arts_section_id` INT NOT NULL,
  `createdBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedBy` INT NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));
 
  
  ALTER TABLE `arts_event_section` 
ADD INDEX `arts_section_id_fk_idx` (`arts_section_id` ASC),
ADD INDEX `arts_event_id_fk_idx` (`arts_event_id` ASC);
ALTER TABLE `arts_event_section` 
ADD CONSTRAINT `arts_event_id_fk`
  FOREIGN KEY (`arts_event_id`)
  REFERENCES `arts_events` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_section_id_fk`
  FOREIGN KEY (`arts_section_id`)
  REFERENCES `arts_section` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION;
  
  
  
  CREATE TABLE `arts_position` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `position_name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(45) NOT NULL,
  `createdBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedBy` INT NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC));
  
  
  
  CREATE TABLE `arts_position_marks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `arts_category_id` INT NOT NULL,
  `arts_subcategory_id` INT NOT NULL,
  `arts_position_id` INT NOT NULL,
  `arts_position_marks` INT NOT NULL,
  `arts_event_type_id` INT NOT NULL,
  `createdBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedBy` INT NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));
  
  
  
  ALTER TABLE `arts_position_marks` 
ADD INDEX `arts_category_id_fk_idx` (`arts_category_id` ASC),
ADD INDEX `arts_subcategory_id_fk_idx` (`arts_subcategory_id` ASC),
ADD INDEX `arts_position_id_fk_idx` (`arts_position_id` ASC),
ADD INDEX `arts_event_type_id_fk_idx` (`arts_event_type_id` ASC);
ALTER TABLE `arts_position_marks` 
ADD CONSTRAINT `arts_category_id_fk`
  FOREIGN KEY (`arts_category_id`)
  REFERENCES `arts_category` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_subcategory_id_fk`
  FOREIGN KEY (`arts_subcategory_id`)
  REFERENCES `arts_subcategory` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_position_id_fk`
  FOREIGN KEY (`arts_position_id`)
  REFERENCES `arts_position` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_event_type_id_fk`
  FOREIGN KEY (`arts_event_type_id`)
  REFERENCES `arts_event_type` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION;
  
  
  
  
  
  
  CREATE TABLE `arts_student_position` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `arts_event_id` INT NOT NULL,
  `studentaccount_id` INT NOT NULL,
  `arts_position_id` INT NOT NULL,
  `createdBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedBy` INT NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));
  
  
ALTER TABLE `arts_student_position` 
ADD INDEX `arts_event_id_fk_idx` (`arts_event_id` ASC),
ADD INDEX `studentaccount_id_fk_idx` (`studentaccount_id` ASC),
ADD INDEX `arts_position_id_fk_idx` (`arts_position_id` ASC);
ALTER TABLE `arts_student_position` 
ADD CONSTRAINT `arts_event_id_asp_fk`
  FOREIGN KEY (`arts_event_id`)
  REFERENCES `arts_events` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `studentaccount_id_asp_fk`
  FOREIGN KEY (`studentaccount_id`)
  REFERENCES `studentaccount` (`studentID`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_position_id__asp_fk`
  FOREIGN KEY (`arts_position_id`)
  REFERENCES `arts_position` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION;






CREATE TABLE `arts_group_position` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `arts_event_id` INT NOT NULL,
  `arts_group_name_id` INT NOT NULL,
  `arts_position_id` INT NOT NULL,
  `createdBy` INT NOT NULL,
  `updatedBy` INT NOT NULL,
  `createdDate` DATETIME NOT NULL,
  `updatedDate` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `arts_event_id_agp_fk_idx` (`arts_event_id` ASC),
  INDEX `arts_group_name_id_agp_fk_idx` (`arts_group_name_id` ASC),
  INDEX `arts_position_id_agp_fk_idx` (`arts_position_id` ASC),
  CONSTRAINT `arts_event_id_agp_fk`
    FOREIGN KEY (`arts_event_id`)
    REFERENCES `arts_events` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `arts_group_name_id_agp_fk`
    FOREIGN KEY (`arts_group_name_id`)
    REFERENCES `arts_group_name` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `arts_position_id_agp_fk`
    FOREIGN KEY (`arts_position_id`)
    REFERENCES `arts_position` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


ALTER TABLE `arts_group_position` 
DROP FOREIGN KEY `arts_event_id_agp_fk`,
DROP FOREIGN KEY `arts_group_name_id_agp_fk`,
DROP FOREIGN KEY `arts_position_id_agp_fk`;
ALTER TABLE `arts_group_position` 
ADD CONSTRAINT `arts_event_id_agp_fk`
  FOREIGN KEY (`arts_event_id`)
  REFERENCES `arts_events` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_group_name_id_agp_fk`
  FOREIGN KEY (`arts_group_name_id`)
  REFERENCES `arts_group_name` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION,
ADD CONSTRAINT `arts_position_id_agp_fk`
  FOREIGN KEY (`arts_position_id`)
  REFERENCES `arts_position` (`id`)
  ON DELETE RESTRICT
  ON UPDATE NO ACTION;

--Gadheyan[15-Sep-2017 11:47PM]  
ALTER TABLE arts_position_marks
ADD UNIQUE KEY `arts_category_subcategory_position_eventtype_apm_uq` (`arts_category_id`, `arts_subcategory_id`, `arts_position_id`, `arts_event_type_id`)
  
  --Gadheyan[ 18-Sep-2017 8:32PM]
  
  ALTER TABLE `arts_section` 
ADD COLUMN `isDepartment` INT NOT NULL AFTER `code`;
  
  
  