CREATE TABLE admission_section (
  `id` INT NOT NULL AUTO_INCREMENT,
  `section` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `section_UNIQUE` (`section` ASC));

ALTER TABLE admission_section 
ADD COLUMN `priority` INT NULL AFTER `section`,
ADD UNIQUE INDEX `priority_UNIQUE` (`priority` ASC);


CREATE TABLE admission_appln_forms_fields (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `label` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `required` VARCHAR(45) NULL,
  `validator` VARCHAR(45) NULL,
  `foreign_key` VARCHAR(45) NULL,
  `table` VARCHAR(45) NULL,
  `value` VARCHAR(45) NULL,
  `display_name` VARCHAR(45) NULL,
  `default` VARCHAR(45) NULL,
  `field_relation` VARCHAR(45) NULL,
  `event_handlers` VARCHAR(45) NULL,
  `sectionID` INT NULL,
  PRIMARY KEY (`id`));

ALTER TABLE admission_appln_forms_fields 
ADD COLUMN `fied_priority` INT NULL AFTER `event_handlers`;



ALTER TABLE admission_appln_forms_fields 
CHANGE COLUMN `foreign_key` `foreign_key` TINYINT(1) NULL DEFAULT NULL ,
CHANGE COLUMN `fied_priority` `field_priority` INT(11) NULL DEFAULT NULL ;


ALTER TABLE admission_section 
ADD COLUMN `isPayment` TINYINT(2) NULL AFTER `priority`, RENAME TO  admission_appln_forms_section ;

ALTER TABLE admission_appln_forms_section 
DROP INDEX `priority_UNIQUE` ;


ALTER TABLE admission_appln_forms_fields 
CHANGE COLUMN `label` `label` VARCHAR(45) NULL ,
CHANGE COLUMN `table` `table_name` VARCHAR(45) NULL DEFAULT NULL ,
CHANGE COLUMN `default` `default_value` VARCHAR(45) NULL DEFAULT NULL ;


CREATE TABLE `gender` (
  `gid` VARCHAR(10) NULL,
  `gname` VARCHAR(10) NULL,
  UNIQUE INDEX `gid_UNIQUE` (`gid` ASC));


CREATE TABLE `admission_appln_current_section` (
  `id` INT NOT NULL,
  `studentID` INT NULL,
  `sectionID` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `studentID_UNIQUE` (`studentID` ASC));
  
  
  ALTER TABLE `admission_appln_current_section` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;





ALTER TABLE `admission_appln_forms_fields` 
ADD COLUMN `user_query` TINYINT(1) NULL AFTER `sectionID`;

CREATE TABLE `admission_appln_user_query` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `table_name` VARCHAR(45) NULL,
  `user_query` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `table_name_UNIQUE` (`table_name` ASC));

  
  
  ALTER TABLE `admission_appln_forms_fields` 
ADD COLUMN `groupname` VARCHAR(45) NULL AFTER `user_query`;

CREATE TABLE `admission_appln_forms_year` (
  `year_name` INT NULL,
  `year_display_name` INT NULL);

  CREATE TABLE `admission_appln_forms_month` (
  `month_name` INT NULL,
  `mont_display_name` INT NULL);

  ALTER TABLE `admission_appln_forms_month` 
CHANGE COLUMN `mont_display_name` `month_display_name` INT(11) NULL DEFAULT NULL ;



CREATE TABLE `admission_appln_forms_bloodgroup` (
  `blood_group_name` VARCHAR(100) NULL,
  `blood_group_display_name` VARCHAR(45) NULL);

  insert into admission_appln_forms_month (month_name,month_display_name) values (1,1);
  insert into admission_appln_forms_month (month_name,month_display_name) values (2,2);
  insert into admission_appln_forms_month (month_name,month_display_name) values (3,3);
  insert into admission_appln_forms_month (month_name,month_display_name) values (4,4);
  insert into admission_appln_forms_month (month_name,month_display_name) values (5,5);
  insert into admission_appln_forms_month (month_name,month_display_name) values (6,6);
  insert into admission_appln_forms_month (month_name,month_display_name) values (7,7);
  insert into admission_appln_forms_month (month_name,month_display_name) values (8,8);
  insert into admission_appln_forms_month (month_name,month_display_name) values (9,9);
  insert into admission_appln_forms_month (month_name,month_display_name) values (10,10);
  insert into admission_appln_forms_month (month_name,month_display_name) values (11,11);
  insert into admission_appln_forms_month (month_name,month_display_name) values (12,12);

  insert into admission_appln_forms_year (year_name,year_display_name) values (2001,2001);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2002,2002);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2003,2003);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2004,2004);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2005,2005);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2006,2006);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2007,2007);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2008,2008);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2009,2009);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2010,2010);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2011,2011);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2012,2012);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2013,2013);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2014,2014);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2015,2015);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2016,2016);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2017,2017);
  insert into admission_appln_forms_year (year_name,year_display_name) values (2018,2018);
  
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("O+","O+");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("O-","O-");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("A+","A+");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("A-","A-");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("B+","B+");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("B-","B-");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("AB+","AB+");
  insert into admission_appln_forms_bloodgroup (blood_group_name,blood_group_display_name) values ("AB-","AB-");
  
  ALTER TABLE `admission_appln_forms_bloodgroup` 
ADD UNIQUE INDEX `blood_group_name_UNIQUE` (`blood_group_name` ASC);

  ALTER TABLE `admission_appln_forms_month` 
ADD UNIQUE INDEX `month_name_UNIQUE` (`month_name` ASC);

  ALTER TABLE `admission_appln_forms_year` 
ADD UNIQUE INDEX `year_name_UNIQUE` (`year_name` ASC);

insert into admission_appln_user_query (table_name,user_query) values ('admission_quotas', 'where quotaID not in (select admission_quotas_id from admission_hide_fee_reservation) order by quotaName ASC');
insert into admission_appln_user_query (table_name,user_query) values ('secondLanguage',' where showforadmission=1');
insert into admission_appln_user_query (table_name,user_query) values ('admission_weightage_quotas', 'where isUG=1');

insert into gender (gid,gname) values ('male','MALE');
insert into gender (gid,gname) values ('female','FEMALE');

/*
 *  
 * @author Aswin
 * @date 13/03/2018
 * 
 */
ALTER TABLE `admission_student_details` 
ADD COLUMN `aadharNo` INT NULL AFTER `bus_stop`,
ADD COLUMN `poverty_line` INT NULL AFTER `aadharNo`,
ADD COLUMN `syllabus10` INT NULL AFTER `poverty_line`,
ADD COLUMN `syllabus12` INT NULL AFTER `syllabus10`;

CREATE TABLE `student_poverty_line` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `poverty_line` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));
  
CREATE TABLE `admission_student_syllabus` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `syllabus` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));


ALTER TABLE `admission_student_details` 
CHANGE COLUMN `aadharNo` `aadharNo` VARCHAR(12) NULL DEFAULT NULL ;

ALTER TABLE `admission_admin` 
ADD COLUMN `deptID` INT NULL DEFAULT 0 AFTER `stafftypeID`;

create table admission_department_phone (id int primary key,deptID int ,phone varchar(45));
alter table admission_department_phone modify column id int  auto_increment;
alter table admission_department_phone modify column deptID int unique;