# Cambio tabla student para datos del mineduc
ALTER TABLE student ADD COLUMN marital_status_std ENUM('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') NOT NULL DEFAULT 'SINGLE' AFTER gender_std,
ADD COLUMN mobile_std varchar(32) NULL AFTER phone_std,
ADD COLUMN orphan_std ENUM('NONE','FATHER','MOTHER','BOTH') NOT NULL DEFAULT 'NONE' AFTER mobile_std,
ADD COLUMN observation_std TEXT NULL AFTER email_std,
DROP COLUMN address_std;

#Creacion de catalogo nivel de educacion para padres
create table education_level_guardian
(
	serial_elg		int not null auto_increment,
	name_elg		varchar(64) not null,
	status_elg		ENUM('ACTIVE','INACTIVE') not null default 'INACTIVE',
	primary key (serial_elg)
)ENGINE=InnoDB;

#Creacion del catalogo profesion para padres
create table area_guardian
(
	serial_arg 		int not null auto_increment,
	name_arg		varchar(64) not null,
	status_arg		ENUM('ACTIVE','INACTIVE') not null default 'INACTIVE',
	primary key (serial_arg)
)ENGINE = InnoDB;

#Creacion del catalogo de ocupacion para padres
create table occupation_guardian
(
	serial_ocg		int not null auto_increment,
	serial_arg 		int not null,
	name_ocg		varchar(64) not null,
	status_ocg		ENUM('ACTIVE','INACTIVE') not null default 'INACTIVE',
	primary key	(serial_ocg),
	constraint FK_area_has_occupation foreign key (serial_arg) references area_guardian(serial_arg)
)ENGINE = InnoDB;


#Cambio tabla guardian para datos del mineduc
ALTER TABLE guardian DROP COLUMN occupation_grd,
DROP COLUMN address_grd,
ADD COLUMN serial_nnt INT AFTER serial_emp,
ADD COLUMN serial_elg INT AFTER serial_nnt,
ADD COLUMN serial_ocg INT AFTER serial_elg,
ADD COLUMN serial_dgr INT AFTER serial_ocg,
ADD COLUMN lives_with_student_grd ENUM('YES','NO') DEFAULT 'YES' AFTER email2_grd,
ADD COLUMN allow_to_pick_student_grd ENUM('YES','NO') DEFAULT 'NO' AFTER lives_with_student_grd,
ADD COLUMN observation_grd text DEFAULT NULL AFTER allow_to_pick_student_grd,
ADD CONSTRAINT FK_guardian_has_nationalty FOREIGN KEY (serial_nnt) REFERENCES nationality(serial_nnt),
ADD CONSTRAINT FK_guardian_has_education_level FOREIGN KEY (serial_elg) REFERENCES education_level_guardian(serial_elg),
ADD CONSTRAINT FK_guardian_has_occupation FOREIGN KEY (serial_ocg) REFERENCES occupation_guardian(serial_ocg);

#Soporte para multiples direcciones estudiante, padres
#Tabla counties(cantones) 
create table counties
(
   serial_cnt           int not null auto_increment comment 'clave primaria de la tabla cantón, auto incremental',
   serial_ste           int not null comment 'clave primaria de la tabla provincia',
   name_cnt             varchar(64) not null comment 'nombre del cantón',
   status_cnt           ENUM('ACTIVE','INACTIVE') not null default 'ACTIVE' comment 'campo que define si el cantón está activo o inactivo',
   primary key (serial_cnt),
   constraint FK_state_has_counties foreign key (serial_ste) references state(serial_ste)
)ENGINE = InnoDB;

#Tabla parish (parroquia) relacionada con canton
create table parish
(
	serial_par 			int not null auto_increment comment 'clave primaria de la tabla parroquia, autoincremental',
	serial_cnt			int not null comment 'clave foranea perteneciente a la tabla canton',
	name_par			varchar(64) not null comment 'nombre de la parroquia',
	status_par			ENUM('ACTIVE','INACTIVE') not null default 'ACTIVE' comment 'define si la parroquia se encuentra activa',
	primary key (serial_par),
	constraint FK_county_has_parish foreign key (serial_cnt) references counties(serial_cnt)
)ENGINE = InnoDB;

#creación del catalogo de zona
CREATE TABLE zone_address
(
	serial_zne int auto_increment not null,
	name_zne varchar(64) not null,
	status_zne ENUM('ACTIVE','INACTIVE') DEFAULT 'INACTIVE' not null,
	PRIMARY KEY (serial_zne)
)
ENGINE= InnoDB;

#Tabla student_address relacionada con student para asignar varias direcciones a un estudiante
create table student_address
(
	serial_sta			int not null auto_increment comment  'clave primaria de la tabla student_address, autoincremental',
	serial_std 			int not null comment 'clave foranea perteneciente a la tabla estudiante',
	serial_ste 			int not null comment 'clave foranea perteneciante a la tabla provincia',
	serial_cnt 			int not null comment 'clave foranea perteneciente a la tabla canton',
	serial_par 			int not null comment 'clave foranea perteneciente a la tabla parroquia',
	serial_zne			int null default null comment 'clave foranea perteneciente a la tabla zona',
	main_street_sta 	varchar(64) not null,
	number_sta			varchar(32) not null, 
	secondary_street_sta varchar(64) null default null,
	address_type_sta 	ENUM('HOME','WORK','OTHER') NOT NULL DEFAULT 'OTHER',
	phone1_sta			varchar(32) not null,
	phone2_sta			varchar(32) null default null,
	main_address_sta    ENUM('YES','NO') NOT NULL DEFAULT 'NO',
	status_sta			ENUM('ACTIVE','INACTIVE') default 'ACTIVE' comment 'indica si la direccion esta en uso o esta borrada',
	primary key(serial_sta),
	constraint FK_student_has_address foreign key (serial_std) references student(serial_std),
	constraint FK_address_has_state foreign key (serial_ste) references state(serial_ste),
	constraint FK_address_has_county foreign key (serial_cnt) references counties(serial_cnt),
	constraint FK_address_has_parish foreign key (serial_par) references parish(serial_par),
	constraint FK_address_has_zone foreign key (serial_zne) references zone_address(serial_zne)
)ENGINE = InnoDB;

#Tabla guardian_address relacionada con guardian para asignar varias direcciones a un estudiante
create table guardian_address(
	serial_gda			int not null auto_increment comment  'clave primaria de la tabla guardian_address, autoincremental',
	serial_grd 			int not null comment 'clave foranea perteneciente a la tabla guardian',
	serial_ste 			int not null comment 'clave foranea perteneciante a la tabla provincia',
	serial_cnt 			int not null comment 'clave foranea perteneciente a la tabla canton',
	serial_par 			int not null comment 'clave foranea perteneciente a la tabla parroquia',
	serial_zne			int null default null comment 'clave foranea perteneciente a la tabla zona',
	main_street_gda 	varchar(64) not null,
	number_gda			varchar(32) not null, 
	secondary_street_gda varchar(64) null default null,
	address_type_gda 	ENUM('HOME','WORK','OTHER') NOT NULL DEFAULT 'OTHER',
	phone1_gda			varchar(32) null default null,
	phone2_gda			varchar(32) null default null, 
	main_address_gda		ENUM('YES','NO') NOT NULL DEFAULT 'NO',
	status_gda			ENUM('ACTIVE','INACTIVE') default 'ACTIVE' comment 'indica si la direccion esta en uso o esta borrada',
	primary key(serial_gda),
	constraint FK_guardian_has_address foreign key (serial_grd) references guardian(serial_grd),
	constraint FK_addressg_has_state foreign key (serial_ste) references state(serial_ste),
	constraint FK_addressg_has_county foreign key (serial_cnt) references counties(serial_cnt),
	constraint FK_addressg_has_parish foreign key (serial_par) references parish(serial_par),
	constraint FK_addressg_has_zone foreign key (serial_zne) references zone_address(serial_zne)
)ENGINE = InnoDB;