CREATE TABLE `applicant_guardian_online` (
   `serial_agd` int(11) NOT NULL AUTO_INCREMENT,
   `serial_cou` int(11) DEFAULT NULL,
   `serial_ste` int(11) DEFAULT NULL,
   `serial_cnt` int(11) DEFAULT NULL,
   `serial_par` int(11) DEFAULT NULL,
   `first_name_agd` varchar(150) DEFAULT NULL,
   `last_name_agd` varchar(150) DEFAULT NULL,
   `id_agd` varchar(150) DEFAULT NULL,
   `email_agd` varchar(150) DEFAULT NULL,
   `phone_agd` varchar(150) DEFAULT NULL,
   `main_street_agd` varchar(200) DEFAULT NULL,
   `number_street_agd` varchar(200) DEFAULT NULL,
   `secondary_street_agd` varchar(200) DEFAULT NULL,
   `city_residence_agd` varchar(200) DEFAULT NULL,
   `sector_agd` varchar(200) DEFAULT NULL,
   `parent_ex_student` enum('YES','NO') DEFAULT NULL,
   `work_company_agd` varchar(45) DEFAULT NULL,
   `status_agd` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
   `number_applicants_agd` int(11) DEFAULT NULL,
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`serial_agd`),
   UNIQUE KEY `serial_agd_UNIQUE` (`serial_agd`),
   KEY `fk_applicant_guardian_online_country_idx` (`serial_cou`),
   KEY `fk_applicant_guardian_online_state1_idx` (`serial_ste`),
   KEY `fk_applicant_guardian_online_counties1_idx` (`serial_cnt`),
   KEY `fk_applicant_guardian_online_parish1_idx` (`serial_par`),
   CONSTRAINT `fk_applicant_guardian_online_counties1` FOREIGN KEY (`serial_cnt`) REFERENCES `counties` (`serial_cnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_guardian_online_country` FOREIGN KEY (`serial_cou`) REFERENCES `country` (`serial_cou`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_guardian_online_parish1` FOREIGN KEY (`serial_par`) REFERENCES `parish` (`serial_par`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_guardian_online_state1` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


 CREATE TABLE `applicant_student_online` (
   `serial_ast` int(11) NOT NULL AUTO_INCREMENT,
   `serial_agd` int(11) NOT NULL,
   `serial_scy` int(11) NOT NULL,
   `serial_set` int(11) NOT NULL,
   `serial_lvl` int(11) NOT NULL,
   `serial_cou_last_school_ast` int(11) NOT NULL COMMENT 'Pais de procedencia',
   `serial_cou` int(11) DEFAULT NULL,
   `serial_ste` int(11) DEFAULT NULL,
   `serial_cnt` int(11) DEFAULT NULL,
   `serial_par` int(11) DEFAULT NULL,
   `first_name_ast` varchar(150) DEFAULT NULL,
   `last_name_ast` varchar(150) DEFAULT NULL,
   `id_ast` varchar(150) DEFAULT NULL,
   `gender_ast` enum('MALE','FEMALE') DEFAULT NULL,
   `school_city_last_school_ast` varchar(150) DEFAULT NULL,
   `last_school_ast` varchar(150) DEFAULT NULL,
   `birthdate_ast` varchar(150) DEFAULT NULL,
   `has_siblins_in_school_ast` enum('YES','NO') DEFAULT NULL,
   `parent_ex_student_ast` enum('YES','NO') DEFAULT NULL,
   `lives_with_ast` enum('PARENTS','FATHER','MOTHER','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') DEFAULT NULL,
   `applicant_type_ast` enum('NEW','REENTRY','RETRY') DEFAULT NULL,
   `has_visited_school_ast` enum('YES','NO') DEFAULT 'NO',
   `flag1_ast` enum('YES','NO') DEFAULT NULL,
   `flag2_ast` enum('YES','NO') DEFAULT NULL,
   `flag3_ast` enum('YES','NO') DEFAULT NULL,
   `status_applicant_ast` enum('ACCEPTED','REJECTED') DEFAULT NULL,
   `observation_ast` text,
   `relationship_ast` enum('MOTHER','FATHER','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') DEFAULT NULL,
   `ip_address_ast` varchar(45) DEFAULT NULL,
   `status_ast` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`serial_ast`),
   KEY `fk_applicant_student_online_school_year1_idx` (`serial_scy`),
   KEY `fk_applicant_student_online_level1_idx` (`serial_lvl`),
   KEY `fk_applicant_student_online_section1_idx` (`serial_set`),
   KEY `fk_applicant_student_online_country1_idx` (`serial_cou_last_school_ast`),
   KEY `fk_applicant_student_online_applicant_guardian1_idx` (`serial_agd`),
   KEY `fk_applicant_student_online_country_idx` (`serial_cou`),
   KEY `fk_applicant_student_online_state1_idx` (`serial_ste`),
   KEY `fk_applicant_student_online_counties1_idx` (`serial_cnt`),
   KEY `fk_applicant_student_online_parish1_idx` (`serial_par`),
   CONSTRAINT `fk_applicant_student_online_applicant_guardian1` FOREIGN KEY (`serial_agd`) REFERENCES `applicant_guardian_online` (`serial_agd`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_country1` FOREIGN KEY (`serial_cou_last_school_ast`) REFERENCES `country` (`serial_cou`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_level1` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_school_year1` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_section1` FOREIGN KEY (`serial_set`) REFERENCES `section` (`serial_set`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_counties1` FOREIGN KEY (`serial_cnt`) REFERENCES `counties` (`serial_cnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_country` FOREIGN KEY (`serial_cou`) REFERENCES `country` (`serial_cou`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_parish1` FOREIGN KEY (`serial_par`) REFERENCES `parish` (`serial_par`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_applicant_student_online_state1` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `applicant_guardian_student_online` (
   `serial_ags` int(11) NOT NULL AUTO_INCREMENT,
   `serial_agd` int(11) NOT NULL,
   `serial_ast` int(11) NOT NULL,
   `relationship_ast` enum('MOTHER','FATHER','OTHER') DEFAULT NULL,
   `status_ags` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`serial_ags`),
   KEY `fk_serial_agd_online` (`serial_agd`),
   KEY `fk_serial_ast_online` (`serial_ast`),
   CONSTRAINT `fk_serial_agd_online` FOREIGN KEY (`serial_agd`) REFERENCES `applicant_guardian_online` (`serial_agd`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `fk_serial_ast_online` FOREIGN KEY (`serial_ast`) REFERENCES `applicant_student_online` (`serial_ast`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

ALTER TABLE `school_setup`
ADD COLUMN `web_online_sst` VARCHAR(100) NULL AFTER `web_primary_sst`;

ALTER TABLE `level_group_school_year`
ADD COLUMN `form_thank_page_online` VARCHAR(100) NULL AFTER `form_thank_page`;


UPDATE `school_setup`
SET web_online_sst='<a href="https://www.tomasmoro.ec/tomas-moro-online/" target="_blank">UETM Online. </a>';