------Franklin
CREATE TABLE `date_day_configuration` (
  `serial_ddc` INT NOT NULL AUTO_INCREMENT,
  `serial_scy` INT NOT NULL,
  `date_ddc` DATE UNIQUE NOT NULL,
  `day_ddc` ENUM('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY')NOT NULL,
  `working_ddc`boolean NOT NULL,
  `holiday_ddc` boolean NOT NULL,
  `extracurricular_ddc` boolean NOT NULL,
  `status_ddc` ENUM('ACTIVE','INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ddc`));
ALTER TABLE `date_day_configuration` 
ADD CONSTRAINT `fk_serial_dcc_serial_scy`
  FOREIGN KEY (`serial_scy`)
  REFERENCES `school_year` (`serial_scy`)
  
ALTER TABLE `attendance` 
ADD COLUMN `serial_ddc` INT NULL AFTER `serial_sbc`;
ALTER TABLE `attendance` 
ADD CONSTRAINT `fk_serial_ddc`
  FOREIGN KEY (`serial_ddc`)
  REFERENCES `date_day_configuration` (`serial_ddc`);
  
  CREATE TABLE `schedule_header` (
  `serial_shd` INT NOT NULL AUTO_INCREMENT,
  `serial_crs` INT NULL,
  `date_start_shd` DATE NULL,
  `date_end_shd` DATE NULL,
   `in_use_shd` boolean default '1',
  `status_shd` ENUM('ACTIVE','INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_shd`),
  CONSTRAINT `fk_serial_crs_shd`
    FOREIGN KEY (`serial_crs`)
    REFERENCES `course` (`serial_crs`));

	
	
	ALTER TABLE `schedule` 
ADD COLUMN `serial_shd` INT NULL AFTER `status_scd`,
ADD INDEX `fk_serial_shd_idx` (`serial_shd` ASC);
ALTER TABLE `schedule` 
ADD CONSTRAINT `fk_serial_shd`
  FOREIGN KEY (`serial_shd`)
  REFERENCES `schedule_header` (`serial_shd`);
  ALTER TABLE schedule_header add in_use_shd boolean default '1'
  
  
CREATE TABLE `lectionary` (
  `serial_lct` INT NOT NULL AUTO_INCREMENT,
  `serial_ddc` INT NOT NULL,
  `serial_scd` INT NOT NULL,
  `serial_osb` INT NULL DEFAULT NULL,
  `theme_lct` VARCHAR(100) NULL,
  `sub_theme_lct` VARCHAR(400) NULL,
  `dictation` TINYINT(1) NULL DEFAULT 0,
  `status_lct` ENUM('ACTIVE','INACTIVE') NULL,
  PRIMARY KEY (`serial_lct`),
  INDEX `fk_serial_ddc_lct_idx` (`serial_ddc` ASC),
  INDEX `fk_serial_scd_lct_idx` (`serial_scd` ASC),
  INDEX `fk_serial_osb_lct_idx` (`serial_osb` ASC),
  CONSTRAINT `fk_serial_ddc_lct`
    FOREIGN KEY (`serial_ddc`)
    REFERENCES `date_day_configuration` (`serial_ddc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_scd_lct`
    FOREIGN KEY (`serial_scd`)
    REFERENCES `schedule` (`serial_scd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_osb_lct`
    FOREIGN KEY (`serial_osb`)
    REFERENCES `optional_subject` (`serial_osb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
 
 
 CREATE TABLE `course_employee` (
`serial_cem` INT NOT NULL AUTO_INCREMENT,
  `serial_emp` INT NOT NULL,
  `serial_crs` INT NOT NULL,
  `employee_type` ENUM('INSPECTOR','COURSE_AUXILIAR') NULL,
  `status_cem` ENUM('ACTIVE','INACTIVE') NULL,  
  PRIMARY KEY (`serial_cem`),
  CONSTRAINT `fk_serial_emp_cem`
    FOREIGN KEY (`serial_emp`)
    REFERENCES `employee` (`serial_emp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_crs_cem`
    FOREIGN KEY (`serial_crs`)
    REFERENCES `course` (`serial_crs`)
   );


ALTER TABLE `attendance` 
CHANGE COLUMN `type_ace` `type_ace` ENUM('UNEXCUSED_ABSENCE','EXCUSED_ABSENCE','DELAY','ESCAPE','PERMISSION') NOT NULL ,
ADD COLUMN `permission` TINYINT(1) NULL AFTER `status_ace`,
ADD COLUMN `observation` VARCHAR(200) NULL AFTER `permission`,
ADD COLUMN `review` VARCHAR(200) NULL AFTER `observation`,
ADD COLUMN `serial_usr` INT NULL AFTER `review`,
ADD CONSTRAINT `fk_serial_usr_ace`
  FOREIGN KEY (`serial_usr`)
  REFERENCES `user` (`serial_usr`)
;
-----Gabriel
ALTER TABLE subject_year_activity_criteria_period ADD criteria_max_grade decimal(8,5);
ALTER TABLE opt_subject_year_activity_criteria_period ADD criteria_max_grade decimal(8,5);

CREATE TABLE IF NOT EXISTS `subject_category` (
  `serial_scat` INT(11) NOT NULL AUTO_INCREMENT,
  `name_scat` VARCHAR(256) NOT NULL,
  `status_scat` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_scat`))
ENGINE = InnoDB;

ALTER TABLE optional_subject_level ADD COLUMN serial_scat INT(11);

ALTER TABLE optional_subject_level ADD CONSTRAINT fk_serial_scat FOREIGN KEY (serial_scat) REFERENCES subject_category(serial_scat);

ALTER TABLE process MODIFY COLUMN weight_prc decimal(5,2);


ALTER TABLE subject_course ADD has_opt_subject_relation boolean default '0';

CREATE TABLE IF NOT EXISTS `optional_with_subject_course` (
  `serial_owsc` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_osb` INT(11) NOT NULL,
  `serial_sbc` INT(11) NOT NULL,
  `status_owsc` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_owsc`))
ENGINE = InnoDB;

ALTER TABLE optional_with_subject_course ADD CONSTRAINT fk_serial_osb FOREIGN KEY (serial_osb) REFERENCES optional_subject(serial_osb);

ALTER TABLE optional_with_subject_course ADD CONSTRAINT fk_serial_sbc FOREIGN KEY (serial_sbc) REFERENCES subject_course(serial_sbc);


CREATE TABLE IF NOT EXISTS `student_subject_activity_type_averages` (
  `serial_ssata` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_std` INT(11) NOT NULL,
  `serial_csb` INT(11) NULL,
  `serial_sbc` INT(11) NULL,
  `serial_parent` INT(11) NULL,
  `serial_prd` INT(11) NOT NULL,
  `serial_crs` INT(11) NOT NULL,
  `serial_att` INT(11) NOT NULL,
  `att_average` DECIMAL(8,5) NULL,
  `weight` DECIMAL(5,2) NULL,
  `average_data` DECIMAL(8,5) NULL,
  `average_type` ENUM('NORMAL','GROUPED') NOT NULL DEFAULT 'NORMAL',
  PRIMARY KEY (`serial_ssata`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `student_opt_subject_activity_type_averages` (
  `serial_sosata` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_std` INT(11) NOT NULL,
  `serial_csb` INT(11) NULL,
  `serial_parent` INT(11) NULL,
  `serial_relation` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `serial_prd` INT(11) NOT NULL,
  `serial_att` INT(11) NOT NULL,
  `att_average` DECIMAL(8,5) NULL,
  `weight` DECIMAL(5,2) NULL,
  `average_data` DECIMAL(8,5) NULL,
  `average_type` ENUM('NORMAL','GROUPED') NOT NULL DEFAULT 'NORMAL',
  PRIMARY KEY (`serial_sosata`))
ENGINE = InnoDB;


ALTER TABLE `level_section_year` ADD `grade_type_lsy` ENUM('QUALITATIVE','QUANTITATIVE') NOT NULL DEFAULT 'QUANTITATIVE' AFTER `course_director_grade_weight_lsy`;

CREATE TABLE IF NOT EXISTS `discipline_grade_criteria` (
  `serial_dgct` INT(11) NOT NULL AUTO_INCREMENT,
  `name_dgct` VARCHAR(256) NOT NULL,
  `description_dgct` TEXT NOT NULL,
  `status_dgct` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dgct`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `discipline_grader` (
  `serial_dg` INT(11) NOT NULL AUTO_INCREMENT,
  `name_dg` VARCHAR(256) NOT NULL,
  `status_dg` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dg`))
ENGINE = InnoDB;

INSERT INTO `discipline_grader`(`serial_dg`, `name_dg`) VALUES (1,'Supervisor');
INSERT INTO `discipline_grader`(`serial_dg`, `name_dg`) VALUES (2,'Course Director');

CREATE TABLE IF NOT EXISTS `discipline_fault_types` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(256) NOT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `discipline_grader_grade_criteria` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_dgct` INT(11) NOT NULL,
  `serial_dg` INT(11) NOT NULL,
  `serial_lvl` INT(11) NOT NULL,
  `serial_set` INT(11) NOT NULL,
  `serial_scy` INT(11) NOT NULL,
  `weight` DECIMAL(5,2) NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `discipline_fault_types_id` INT(11) NULL,
  PRIMARY KEY (`id`, `serial_dgct`, `serial_dg`),
  INDEX `fk_discipline_criteria_grade_has_discipline_grader_discipli_idx` (`serial_dg` ASC),
  INDEX `fk_discipline_criteria_grade_has_discipline_grader_discipli_idx1` (`serial_dgct` ASC),
  INDEX `fk_discipline_grader_criteria_grade_discipline_fault_types1_idx` (`discipline_fault_types_id` ASC),
  CONSTRAINT `fk_discipline_criteria_grade_has_discipline_grader_discipline1`
    FOREIGN KEY (`serial_dgct`)
    REFERENCES `discipline_grade_criteria` (`serial_dgct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_discipline_criteria_grade_has_discipline_grader_discipline2`
    FOREIGN KEY (`serial_dg`)
    REFERENCES `discipline_grader` (`serial_dg`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_discipline_grader_criteria_grade_discipline_fault_types1`
    FOREIGN KEY (`discipline_fault_types_id`)
    REFERENCES `discipline_fault_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `subject_discipline_criteria_grade` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_dgct` INT(11) NOT NULL,
  `serial_sbj` INT(11) NULL,
  `serial_osb` INT(11) NULL,
  `weight` DECIMAL(5,2) NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `discipline_fault_types_id` INT(11) NULL,
  PRIMARY KEY (`id`, `serial_dgct`),
  INDEX `fk_subject_has_discipline_criteria_grade_discipline_criteri_idx` (`serial_dgct` ASC),
  INDEX `fk_subject_discipline_criteria_grade_discipline_fault_types_idx` (`discipline_fault_types_id` ASC),
  CONSTRAINT `fk_subject_has_discipline_criteria_grade_discipline_criteria_1`
    FOREIGN KEY (`serial_dgct`)
    REFERENCES `discipline_grade_criteria` (`serial_dgct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_subject_discipline_criteria_grade_discipline_fault_types1`
    FOREIGN KEY (`discipline_fault_types_id`)
    REFERENCES `discipline_fault_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `student_subperiod_discipline_criteria_course_director` (
  `serial_ccdd` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_sbp` INT(11) NULL DEFAULT NULL,
  `serial_std` INT(11) NULL DEFAULT NULL,
  `serial_spt` INT(11) NULL DEFAULT NULL,
  `serial_lvl` INT(11) NULL DEFAULT NULL,
  `serial_scy` INT(11) NULL DEFAULT NULL,
  `serial_set` INT(11) NULL DEFAULT NULL,
  `serial_dgct` INT(11) NOT NULL,
  `grade_ccdd` DECIMAL(8,5) NULL,
  PRIMARY KEY (`serial_ccdd`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `student_subperiod_discipline_criteria_supervisor` (
  `serial_sdcs` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_sbp` INT(11) NULL DEFAULT NULL,
  `serial_std` INT(11) NULL DEFAULT NULL,
  `serial_spt` INT(11) NULL DEFAULT NULL,
  `serial_lvl` INT(11) NULL DEFAULT NULL,
  `serial_scy` INT(11) NULL DEFAULT NULL,
  `serial_set` INT(11) NULL DEFAULT NULL,
  `serial_dgct` INT(11) NOT NULL,
  `grade_sdcs` DECIMAL(8,5) NULL,
  PRIMARY KEY (`serial_sdcs`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `student_subperiod_discipline_criteria_subject` (
  `serial_sdcg` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_sbp` INT(11) NULL DEFAULT NULL,
  `serial_sbc` INT(11) NULL DEFAULT NULL,
  `serial_lop` INT(11) NULL DEFAULT NULL,
  `serial_std` INT(11) NULL DEFAULT NULL,
  `serial_spt` INT(11) NULL DEFAULT NULL,
  `serial_lvl` INT(11) NULL DEFAULT NULL,
  `serial_scy` INT(11) NULL DEFAULT NULL,
  `serial_set` INT(11) NULL DEFAULT NULL,
  `serial_dgct` INT(11) NOT NULL,
  `grade_sdcg` DECIMAL(8,5) NULL,
  PRIMARY KEY (`serial_sdcg`))
ENGINE = InnoDB;

DROP TABLE subperiod_discipline_change_log;

CREATE TABLE IF NOT EXISTS `subperiod_discipline_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_ccdd` int(11) DEFAULT NULL,
  `serial_sdcg` int(11) DEFAULT NULL,
  `serial_sdcs` int(11) DEFAULT NULL,
  `serial_dgct` int(11) DEFAULT NULL,
  `last_grade_sdcl` decimal(8,5) NOT NULL,
  `comment_sdcl` text,
  `change_date_sdcl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE `subperiod_discipline_change_log`
ADD KEY `fk_relationship_serial_usr_sdcl` (`serial_usr`),
ADD KEY `fk_relationship_serial_ccdd_sdcl` (`serial_ccdd`),
ADD KEY `fk_relationship_serial_sdcg_sdcl` (`serial_sdcg`),
ADD KEY `fk_relationship_serial_sdcs_sdcl` (`serial_sdcs`),
ADD KEY `fk_relationship_serial_chr_sdcl` (`serial_chr`);

ALTER TABLE `subperiod_discipline_change_log`
ADD CONSTRAINT `fk_relationship_serial_ccdd_sdcl` FOREIGN KEY (`serial_ccdd`) REFERENCES `student_subperiod_discipline_criteria_course_director` (`serial_ccdd`),
ADD CONSTRAINT `fk_relationship_serial_chr_sdcl` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`),
ADD CONSTRAINT `fk_relationship_serial_sdcg_sdcl` FOREIGN KEY (`serial_sdcg`) REFERENCES `student_subperiod_discipline_criteria_subject` (`serial_sdcg`),
ADD CONSTRAINT `fk_relationship_serial_sdcs_sdcl` FOREIGN KEY (`serial_sdcs`) REFERENCES `student_subperiod_discipline_criteria_supervisor` (`serial_sdcs`),
ADD CONSTRAINT `fk_relationship_serial_usr_sdcl` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

CREATE TABLE IF NOT EXISTS `discipline_criteria_equivalence` (
  `serial_dceq` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` INT(11) NOT NULL,
  `value_dceq` DECIMAL(5,2) NOT NULL,
  `name_dceq` VARCHAR(150) NOT NULL,
  `code_dceq` VARCHAR(15) NOT NULL,
  `status_dceq` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dceq`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `discipline_incidences` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `incidence_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `report` TEXT NOT NULL,
  `intervention` TEXT NULL,
  `observations` TEXT NULL,
  `commitment_letter` VARCHAR(256) NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `serial_std` INT(11) NOT NULL,
  `discipline_fault_types_id` INT(11) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_discipline_incidences_discipline_fault_types1_idx` (`discipline_fault_types_id` ASC),
  INDEX `fk_discipline_incidences_student1_idx` (`serial_std` ASC),
  CONSTRAINT `fk_discipline_incidences_discipline_fault_types1`
    FOREIGN KEY (`discipline_fault_types_id`)
    REFERENCES `discipline_fault_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_discipline_incidences_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `discipline_actions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(256) NOT NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `discipline_incidences_actions` (
  `discipline_incidences_id` INT(11) NOT NULL,
  `discipline_actions_id` INT(11) NOT NULL,
  PRIMARY KEY (`discipline_incidences_id`, `discipline_actions_id`),
  INDEX `fk_discipline_incidences_has_discipline_actions_discipline__idx` (`discipline_actions_id` ASC),
  INDEX `fk_discipline_incidences_has_discipline_actions_discipline__idx1` (`discipline_incidences_id` ASC),
  CONSTRAINT `fk_discipline_incidences_has_discipline_actions_discipline_in1`
    FOREIGN KEY (`discipline_incidences_id`)
    REFERENCES `discipline_incidences` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_discipline_incidences_has_discipline_actions_discipline_ac1`
    FOREIGN KEY (`discipline_actions_id`)
    REFERENCES `discipline_actions` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

alter table subject_course add has_lectionary boolean;
alter table subject_course add has_attendance boolean default '1';
alter table subject_course add has_grades boolean default '1';
alter table subject_course add has_discipline boolean default '1';
alter table subject_course add has_schedule boolean default '1';

alter table optional_subject_level add has_lectionary boolean;
alter table optional_subject_level add has_attendance boolean default '1';
alter table optional_subject_level add has_grades boolean default '1';
alter table optional_subject_level add has_discipline boolean default '1';
alter table optional_subject_level add has_schedule boolean default '1';

alter table opt_student_criteria_grade add fulfilled boolean default '1';
alter table student_criteria_grade add fulfilled boolean default '1';

CREATE TABLE `parent_subject_course_period` (
  `serial_csb` INT NOT NULL,
  `serial_crs` INT NOT NULL,
  `serial_att` INT NOT NULL,
  `serial_prd` INT NOT NULL,
  `weight_psc` DECIMAL(5,2),
  `status_psc` ENUM ('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `average_type` ENUM('NORMAL','GROUPED') NOT NULL DEFAULT 'NORMAL',
  CONSTRAINT `fk_serial_csb`
    FOREIGN KEY (`serial_csb`)
    REFERENCES `cat_subject` (`serial_csb`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_crs`
    FOREIGN KEY (`serial_crs`)
    REFERENCES `course` (`serial_crs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_att`
    FOREIGN KEY (`serial_att`)
    REFERENCES `activity_type` (`serial_att`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial:prd`
    FOREIGN KEY (`serial_prd`)
    REFERENCES `period` (`serial_prd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
ALTER TABLE `parent_subject_course_period` 
ADD PRIMARY KEY (`serial_csb`, `serial_crs`, `serial_att`, `serial_prd`);

-------wagner

  KEY `fk_serial_asstu3_idx` (`serial_asstu`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_SIBLINGS			                            */
/*==============================================================*/
CREATE TABLE `aspirant_siblings` (
  `serial_assi` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `serial_sch` int(11) DEFAULT NULL,
  `Fisrt_name_sibling` varchar(45) DEFAULT NULL,
  `Last_name_sibling` varchar(45) DEFAULT NULL,
  `Birthday_sibling` date DEFAULT NULL,
  PRIMARY KEY (`serial_assi`),
  KEY `serail_asstu_idx` (`serial_asstu`),
  KEY `serial_sch_idx` (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Hermanos del aspirante';
/*==============================================================*/
/* Table: ASPIRANT_STUDENT			                            */
/*==============================================================*/
CREATE TABLE `aspirant_student` (
  `serial_asstu` int(11) NOT NULL,
  `serial_wus` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `serial_ste` int(11) DEFAULT NULL,
  `serial_asfml` int(11) DEFAULT NULL,
  `id_asstu` varchar(10) DEFAULT NULL,
  `request` varchar(20) NOT NULL,
  `first_name_asstu` varchar(512) NOT NULL,
  `last_name_asstu` varchar(512) NOT NULL,
  `birthdate_asstu` date NOT NULL,
  `gender_asstu` enum('MALE','FEMALE') NOT NULL,
  `address_asstu` text NOT NULL,
  `phone_asstu` varchar(30) NOT NULL,
  `first_name_father_asstu` varchar(100) DEFAULT NULL,
  `last_name_father_asstu` varchar(100) DEFAULT NULL,
  `first_name_mother_asstu` varchar(100) DEFAULT NULL,
  `last_name_mother_asstu` varchar(100) DEFAULT NULL,
  `city_birth_asstu` varchar(100) DEFAULT NULL,
  `blood_type_asstu` varchar(10) DEFAULT NULL,
  `allergies_asstu` text,
  `lives_with_asstu` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `photo_asstu` varchar(1024) DEFAULT NULL,
  `email_asstu` varchar(256) DEFAULT NULL,
  `rep_first_name_asstu` varchar(100) DEFAULT NULL,
  `rep_last_name_asstu` varchar(100) DEFAULT NULL,
  `rep_relationship_asstu` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') DEFAULT NULL,
  `rep_birthdate_asstu` date DEFAULT NULL,
  `rep_city_birth_asstu` varchar(100) DEFAULT NULL,
  `rep_marital_status_asstu` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') DEFAULT NULL,
  `rep_occupation_asstu` varchar(100) DEFAULT NULL,
  `rep_address_asstu` text,
  `rep_phone_asstu` varchar(13) DEFAULT NULL,
  `rep_mobile_asstu` varchar(13) DEFAULT NULL,
  `rep_company_name_asstu` varchar(80) DEFAULT NULL,
  `rep_company_activity_asstu` text,
  `rep_company_phone_asstu` varchar(13) DEFAULT NULL,
  `rep_photo_asstu` varchar(1024) DEFAULT NULL,
  `rep_gender_asstu` enum('MALE','FEMALE') DEFAULT NULL,
  `rep_id_asstu` varchar(13) DEFAULT NULL,
  `rep_email_asstu` varchar(256) DEFAULT NULL,
  `is_listener_asstu` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `submitted_docs_asstu` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `siblings_asstu` int(11) DEFAULT NULL,
  `position_sibling_asstu` int(11) DEFAULT NULL,
  `siblings_in_school` enum('YES','NO') DEFAULT 'NO',
  `siblings_in_school_detail` varchar(50) DEFAULT NULL,
  `insert_date_asstu` datetime DEFAULT NULL,
  `modification_date_asstu` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Parents_live_asstu` enum('together','separated') NOT NULL DEFAULT 'together',
  `Repeat_level` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `reason_for_repeat` tinytext,
  `Appplication_status` enum('waiting_list','denied','progress','slope','slope_documents','ready to commission','approved','apply next year','collectorship','academic secretary') NOT NULL DEFAULT 'progress',
  `telling_of_entries` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_asstu`),
  KEY `fk_relationship_205` (`serial_dgr`),
  KEY `fk_relationship_206` (`serial_nnt`),
  KEY `fk_relationship_242` (`serial_ste`),
  KEY `serial_lvl_idx` (`serial_lvl`),
  KEY `serial_scy_idx` (`serial_scy`),
  KEY `serial_set_idx` (`serial_set`),
  KEY `serial_spt_idx` (`serial_spt`),
  KEY `fk_serial_asfml_idx` (`serial_asfml`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='web_pre_registration';
/*==============================================================*/
/* Table: COMMITTE					                            */
/*==============================================================*/
CREATE TABLE `committe` (
  `serial_cmt` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `report_cmt` int(11) DEFAULT NULL,
  `inset_date_cmt` date DEFAULT NULL,
  `modify_date_cmt` date DEFAULT NULL,
  PRIMARY KEY (`serial_cmt`),
  KEY `serial_asstu5_idx` (`serial_asstu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: DOCUMENTS					                            */
/*==============================================================*/
CREATE TABLE `documents` (
  `serial_dcm` int(11) NOT NULL AUTO_INCREMENT,
  `name_dcm` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_dcm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: FEEDBACK_FORM				                            */
/*==============================================================*/
CREATE TABLE `feedback_form` (
  `serial_fdbf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_fml` int(11) NOT NULL,
  `serial_crs` int(11) NOT NULL,
  `student_academic_perfrmance` tinytext,
  `student_social_integration` tinytext,
  `parent_comprimised` tinytext,
  `additional_comments` tinytext,
  PRIMARY KEY (`serial_fdbf`),
  KEY `fk_serial_asstu2_idx` (`serial_asstu`),
  KEY `fk_std_serial_2_idx` (`serial_std`),
  KEY `fk_serial:fml2_idx` (`serial_fml`),
  KEY `fk_serial_curse_idx` (`serial_crs`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: PREVIOUS_EDUCATION_ASPIRANT                           */
/*==============================================================*/
CREATE TABLE `previous_education_aspirant` (
  `serial_pred` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) NOT NULL,
  `serial_sch` int(11) DEFAULT NULL,
  `previous_level_asstu` varchar(45) DEFAULT NULL,
  `previous_year_asstu` int(11) DEFAULT NULL,
  `previous_note_asstu` int(11) DEFAULT NULL,
  `previous_note_disciplien_asstu` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_pred`),
  KEY `serial_asstu_idx` (`serial_asstu`),
  KEY `fk_serail_school_idx` (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: SCHOOL					                            */
/*==============================================================*/
CREATE TABLE `school` (
  `serial_sch` int(11) NOT NULL AUTO_INCREMENT,
  `name_sch` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: TYPE_PUBLICITY			                            */
/*==============================================================*/
CREATE TABLE `type_publicity` (
  `serial_typu` int(11) NOT NULL AUTO_INCREMENT,
  `name_typu` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_typu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: TYPE_REPORT				                            */
/*==============================================================*/
CREATE TABLE `type_report` (
  `serial_tyrp` int(11) NOT NULL,
  `name_report` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`serial_tyrp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: WEB_USER_ASPIRANT			                            */
/*==============================================================*/
CREATE TABLE `web_user_aspirant` (
  `serial_wus` int(11) NOT NULL AUTO_INCREMENT,
  `email_wus` varchar(128) NOT NULL,
  `serial_asgu` int(11) DEFAULT NULL,
  `id_asgu` varchar(13) DEFAULT NULL,
  `password_wus` varchar(256) NOT NULL,
  `authentication_key_wus` varchar(1024) DEFAULT NULL,
  `insert_date_wus` datetime DEFAULT NULL,
  `modification_date_wus` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_wus`),
  KEY `fk_serial_asgu2_idx` (`serial_asgu`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='web_user';
/*==============================================================*/
/* Table: YEAR_ASPIRANT_DOCUMENTS                               */
/*==============================================================*/
CREATE TABLE `year_aspirant_documents` (
  `serial_yasdc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_dcm` int(11) DEFAULT NULL,
  `status_ypd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `serial_typu` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_yasdc`),
  KEY `fk_relationship_261_idx` (`serial_scy`),
  KEY `fk_documetns_idx` (`serial_dcm`),
  KEY `fk_typu_idx` (`serial_typu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*==============================================================*/
/* Table: FILTOS PARA LAS TABLAS DE ADMISION                    */
/*==============================================================*/
ALTER TABLE `aspirant_documents`
ADD CONSTRAINT `fk_serial_aspri_stu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_year_asdc` FOREIGN KEY (`serial_yasdc`) REFERENCES `year_aspirant_documents` (`serial_yasdc`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_family`
ADD CONSTRAINT `fk_serial_guardian3` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_guardian`
ADD CONSTRAINT `fk_aspirtant_stu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_nnt` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_elg` FOREIGN KEY (`serial_elg`) REFERENCES `education_level_guardian` (`serial_elg`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_emp` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_ocg` FOREIGN KEY (`serial_ocg`) REFERENCES `occupation_guardian` (`serial_ocg`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `aspirant_guardian_addres`
ADD CONSTRAINT `serial_asgu` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_report`
ADD CONSTRAINT `fk_serial_asstu3` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_type` FOREIGN KEY (`serial_tyrp`) REFERENCES `type_report` (`serial_tyrp`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `aspirant_siblings`
ADD CONSTRAINT `serail_asstu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_sch` FOREIGN KEY (`serial_sch`) REFERENCES `school` (`serial_sch`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_student`
ADD CONSTRAINT `fk_serial_asfml` FOREIGN KEY (`serial_asfml`) REFERENCES `aspirant_family` (`serial_asfml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_dgr` FOREIGN KEY (`serial_dgr`) REFERENCES `degree` (`serial_dgr`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_lvl` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_nnt` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_scy` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_set` FOREIGN KEY (`serial_set`) REFERENCES `section` (`serial_set`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_spt` FOREIGN KEY (`serial_spt`) REFERENCES `specialty` (`serial_spt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_ste` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `committe`
ADD CONSTRAINT `serial_asstu5` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `feedback_form`
ADD CONSTRAINT `fk_serial:fml2` FOREIGN KEY (`serial_fml`) REFERENCES `family` (`serial_fml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_asstu2` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_curse` FOREIGN KEY (`serial_crs`) REFERENCES `course` (`serial_crs`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_std_serial_2` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `previous_education_aspirant`
ADD CONSTRAINT `fk_serail_school` FOREIGN KEY (`serial_sch`) REFERENCES `school` (`serial_sch`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_asstu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `web_user_aspirant`
ADD CONSTRAINT `fk_serial_asgu2` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `year_aspirant_documents`
ADD CONSTRAINT `fk_typu` FOREIGN KEY (`serial_typu`) REFERENCES `type_publicity` (`serial_typu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_documetns` FOREIGN KEY (`serial_dcm`) REFERENCES `documents` (`serial_dcm`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_relationship_261_scy` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);  
-----Wilmer

-- -----------------------------------------------------
-- Table `payment_concept_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_concept_group` (
  `serial_pcg` INT NOT NULL AUTO_INCREMENT COMMENT 'ID de la tabla (All new)',
  `name` VARCHAR(100) NOT NULL COMMENT 'Nombre de la agrupación',
  `toname` ENUM('STUDENT','FATHER','MOTHER','ECONOMIC') NOT NULL DEFAULT 'STUDENT',
  `description` VARCHAR(100) NULL COMMENT 'Descripciónde la agrupación',
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL COMMENT 'Estado',
  PRIMARY KEY (`serial_pcg`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `year_level_speciality_paymennt_concept`
-- -----------------------------------------------------
ALTER TABLE `year_level_specialty_payment_concept` ADD `serial_pcg` INT NULL AFTER `type_ypc` ,
ADD INDEX ( `serial_pcg` );

 ALTER TABLE `year_level_specialty_payment_concept` ADD FOREIGN KEY ( `serial_pcg` ) REFERENCES `payment_concept_group` (
`serial_pcg`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

-- -----------------------------------------------------
-- Family
-- -----------------------------------------------------
ALTER TABLE `family` CHANGE `address_fml` `main_street` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `family` ADD `secundary_street` VARCHAR( 100 ) NOT NULL AFTER `main_street` ,
ADD `number_street` VARCHAR( 10 ) NOT NULL AFTER `secundary_street` ,
ADD `serial_ste` INT NOT NULL AFTER `number_street` ,
ADD `serial_cnt` INT NOT NULL AFTER `serial_ste` ,
ADD `serial_par` INT NOT NULL AFTER `serial_cnt` ,
ADD `phone2_fml` INT( 10 ) NULL AFTER `serial_par` ,
ADD INDEX ( `serial_ste` ),
ADD INDEX ( `serial_cnt` ),
ADD INDEX ( `serial_par` );


ALTER TABLE `family` ADD FOREIGN KEY ( `serial_ste` ) REFERENCES `state` (
`serial_ste`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

ALTER TABLE `family` ADD FOREIGN KEY ( `serial_cnt` ) REFERENCES `counties` (
`serial_cnt`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

ALTER TABLE `family` ADD FOREIGN KEY ( `serial_par` ) REFERENCES `parish` (
`serial_par`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

-- -----------------------------------------------------
-- Family_guardian
-- -----------------------------------------------------
ALTER TABLE `family_guardian` CHANGE `status_fmg` `status_fml` ENUM( 'ACTIVE', 'INACTIVE' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'ACTIVE';

-- -----------------------------------------------------
-- Estudiantes
-- -----------------------------------------------------
ALTER TABLE `student` 
ADD `exit_type` ENUM( 'BUS', 'DOOR' ) NOT NULL DEFAULT 'DOOR' AFTER `school_entrydate_std` ,
ADD `left_alone` ENUM( 'YES', 'NO' ) NOT NULL DEFAULT 'YES' AFTER `exit_type` ,
ADD `last_time` ENUM('YES','NO') NOT NULL DEFAULT 'NO' AFTER `left_alone`; 

-- -----------------------------------------------------
-- Student_Guardian
-- -----------------------------------------------------
ALTER TABLE `student_guardian` ADD `economic_guardian_stg` ENUM( 'YES', 'NO' ) NOT NULL DEFAULT 'NO' AFTER `legal_guardian_stg`; 

-- -----------------------------------------------------
-- Table `authorized_persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `authorized_persons` (
  `serial_aup` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `id_aup` VARCHAR(13) NOT NULL,
  `relationship` VARCHAR(45) NOT NULL,
  `serial_std` INT NOT NULL,
  `path_photo` VARCHAR(250) NULL,
  PRIMARY KEY (`serial_aup`),
  INDEX `fk_authorized_persons_student1_idx` (`serial_std` ASC),
  CONSTRAINT `fk_authorized_persons_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `invoice`
-- -----------------------------------------------------
ALTER TABLE `invoice` ADD `secundary_address_paying_phy` VARCHAR( 100 ) NULL AFTER `address_paying_phy` ,
ADD `number_address_paying_phy` VARCHAR( 10 ) NULL AFTER `secundary_address_paying_phy`;

-- ----------------------------------------------------- 
-- Table `custom_lists` 
-- ----------------------------------------------------- 
CREATE TABLE IF NOT EXISTS `custom_lists` ( 
`serial_clt` INT NOT NULL AUTO_INCREMENT, 
`name` VARCHAR(45) NOT NULL, 
`description` VARCHAR(100) NULL, 
`status` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', 
PRIMARY KEY (`serial_clt`)) ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `customlists_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `customlists_user` ( `serial_clt` INT NOT NULL, `serial_usr` INT(11) NOT NULL, INDEX `fk_customlists_user_custom_lists_idx` (`serial_clt` ASC), INDEX `fk_customlists_user_user1_idx` (`serial_usr` ASC), CONSTRAINT `fk_customlists_user_custom_lists` FOREIGN KEY (`serial_clt`) REFERENCES `custom_lists` (`serial_clt`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_customlists_user_user1` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `link`
-- -----------------------------------------------------
UPDATE `rutademic`.`process` SET `link_prc` = 'modules/payment_concept_group/fNewPaymentConceptGroup' WHERE `process`.`serial_prc` =412;


-- -----------------------------------------------------
-- Table `student`
-- -----------------------------------------------------
ALTER TABLE `student` ADD `check_trans` ENUM('YES','NO') NULL AFTER `last_time`;


-- -----------------------------------------------------
-- Table `year_level_speciality_payment_concept`
-- -----------------------------------------------------
ALTER TABLE `year_level_specialty_payment_concept` ADD `check_require` ENUM('YES','NO') NOT NULL AFTER `serial_pcg`;


-- -----------------------------------------------------
-- Table `medical_records`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `medical_records` (
  `serial_mdr` INT NOT NULL AUTO_INCREMENT,
  `serial_usr` INT(11) NOT NULL,
  `cont_pregnancy` ENUM('YES','') NOT NULL DEFAULT '',
  `preclamsia` ENUM('YES','') NOT NULL DEFAULT '',
  `threat_abortion` ENUM('YES','') NOT NULL DEFAULT '',
  `ivu` ENUM('YES','') NOT NULL DEFAULT '',
  `medication` VARCHAR(150) NULL DEFAULT '',
  `end` ENUM('YES','') NOT NULL DEFAULT '',
  `premature` ENUM('YES','') NOT NULL DEFAULT '',
  `cesarea` ENUM('YES','') NOT NULL DEFAULT '',
  `low_weight` ENUM('YES','') NOT NULL DEFAULT '',
  `high_weight` ENUM('YES','') NOT NULL DEFAULT '',
  `malformations` ENUM('YES','') NOT NULL DEFAULT '',
  `asphyxia` ENUM('YES','') NOT NULL DEFAULT '',
  `jaundice` ENUM('YES','') NOT NULL DEFAULT '',
  `seizures` ENUM('YES','') NOT NULL DEFAULT '',
  `language_delay` ENUM('YES','') NOT NULL DEFAULT '',
  `psychomotor_delay` ENUM('YES','') NOT NULL DEFAULT '',
  `normal_dev` ENUM('YES','') NOT NULL DEFAULT '',
  `bcg` VARCHAR(10) NULL,
  `dpt` VARCHAR(10) NULL,
  `antipolio` VARCHAR(10) NULL,
  `hib` VARCHAR(10) NULL,
  `mmr` VARCHAR(10) NULL,
  `pneumococcal` VARCHAR(10) NULL,
  `hepatitis_a` VARCHAR(10) NULL,
  `hepatitis_b` VARCHAR(10) NULL,
  `varicella` VARCHAR(10) NULL,
  `other` VARCHAR(100) NULL,
  `alimentation` VARCHAR(10) NULL,
  `urine` VARCHAR(10) NULL,
  `deposition` VARCHAR(10) NULL,
  `sleep` VARCHAR(10) NULL,
  `drugs` VARCHAR(10) NULL,
  `snuff` VARCHAR(10) NULL,
  `alcohol` VARCHAR(10) NULL,
  `medicine` VARCHAR(10) NULL,
  `clinical` VARCHAR(100) NULL,
  `quirugicos` VARCHAR(100) NULL,
  `traumatic` VARCHAR(100) NULL,
  `allergic` VARCHAR(100) NULL,
  `menarche` VARCHAR(10) NULL,
  `cycles` VARCHAR(10) NULL,
  `fum` VARCHAR(10) NULL,
  `vsa` VARCHAR(10) NULL,
  `method_pf` VARCHAR(10) NULL,
  `medical_history` VARCHAR(10) NULL,
  `current_history` VARCHAR(100) NULL,
  `observations` VARCHAR(100) NULL,
  `status` ENUM('ACTIVE','INACTIVE') NOT NULL,
  PRIMARY KEY (`serial_mdr`),
  INDEX `fk_medical_records_user1_idx` (`serial_usr` ASC),
  CONSTRAINT `fk_medical_records_user1`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;






ALTER TABLE `visits_medical_center` ADD `treatment` TEXT NOT NULL AFTER `diagnosis_vmc`;

ALTER TABLE `visits_medical_center` ADD `serial_cmsp` INT NOT NULL AFTER `reason_vmc`;


-- --------------------------------------------
-- Estructura de tabla para la tabla `code_msp`
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `code_msp` (
  `serial_cmsp` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB ;

ALTER TABLE `code_msp`
  ADD PRIMARY KEY (`serial_cmsp`);