-- agregar campo fallecido
ALTER TABLE `guardian` 
ADD COLUMN `died_grd` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `serial_elg2_grd`;
-- crear tabls firecciones de familia
CREATE TABLE `family_address` (
  `serial_fma` INT NOT NULL,
  `serial_fml` INT NOT NULL,
  `serial_ste` INT NULL,
  `serial_cnt` INT NULL,
  `serial_zne` INT NULL,
  `main_street_fma` VARCHAR(64) NULL,
  `number_fma` VARCHAR(32) NULL,
  `secondary_street_fma` VARCHAR(64) NULL,
  `sector_fma` VARCHAR(100) NULL,
  `address_type_fma` ENUM('HOME', 'WORK', 'OTHER') NULL,
  `phone1_fma` VARCHAR(32) NULL,
  `ext_phone1_fma` VARCHAR(45) NULL,
  `phone2_fma` VARCHAR(32) NULL,
  `ext_phone2_fma` VARCHAR(45) NULL,
  `main_address_fma` ENUM('YES', 'NO') NULL,
  `refenece_fma` VARCHAR(200) NULL,
  `city_fma` VARCHAR(45) NULL,
  `status_fma` VARCHAR(45) NULL,
  `coordinate_w_fma` VARCHAR(100) NULL,
  `coordinarte_f_fma` VARCHAR(100) NULL,
  PRIMARY KEY (`serial_fma`),
  INDEX `fma_fml_idx` (`serial_fml` ASC),
  INDEX `fma_ste_idx` (`serial_ste` ASC),
  INDEX `fma_cnt_idx` (`serial_cnt` ASC),
  INDEX `fma_zne_idx` (`serial_zne` ASC),
  CONSTRAINT `fma_fml`
    FOREIGN KEY (`serial_fml`)
    REFERENCES `family` (`serial_fml`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fma_ste`
    FOREIGN KEY (`serial_ste`)
    REFERENCES `state` (`serial_ste`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fma_cnt`
    FOREIGN KEY (`serial_cnt`)
    REFERENCES `counties` (`serial_cnt`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fma_zne`
    FOREIGN KEY (`serial_zne`)
    REFERENCES `zone_address` (`serial_zne`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
	-- serial fma auntoincremental
	
	ALTER TABLE `family_address` 
CHANGE COLUMN `serial_fma` `serial_fma` INT(11) NOT NULL AUTO_INCREMENT ;

-- aumentar el campo seral de parroquia
ALTER TABLE `family_address` 
ADD COLUMN `serial_par` INT NULL AFTER `serial_cnt`,
ADD INDEX `fma_par_idx` (`serial_par` ASC);
ALTER TABLE `family_address` 
ADD CONSTRAINT `fma_par`
  FOREIGN KEY (`serial_par`)
  REFERENCES `parish` (`serial_par`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

  -- agragar el campo de direccion de familia en direccion de representante
  ALTER TABLE `guardian_address` 
ADD COLUMN `serial_fma` INT NULL AFTER `serial_grd`,
ADD INDEX `FK_gda_fma_idx` (`serial_fma` ASC);
ALTER TABLE `guardian_address` 
ADD CONSTRAINT `FK_gda_fma`
  FOREIGN KEY (`serial_fma`)
  REFERENCES `family_address` (`serial_fma`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
 -- agragar el campo de direccion de familia en direccion de estudiante
ALTER TABLE `student_address` 
ADD COLUMN `serial_fma` INT NULL AFTER `serial_std`,
ADD INDEX `FK_student_has_fma_idx` (`serial_fma` ASC);
ALTER TABLE `student_address` 
ADD CONSTRAINT `FK_student_has_fma`
  FOREIGN KEY (`serial_fma`)
  REFERENCES `family_address` (`serial_fma`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- modificar personas autorizadas para que una pueda ser autorizada para varios estudiantes
ALTER TABLE `authorized_persons` 
DROP FOREIGN KEY `fk_authorized_persons_student1`;
ALTER TABLE `authorized_persons` 
CHANGE COLUMN `serial_std` `serial_std` INT(11) NULL ,
DROP INDEX `fk_authorized_persons_student1_idx` ;


CREATE TABLE `authorized_persons_student` (
  `serial_aps` INT NOT NULL AUTO_INCREMENT,
  `serial_aup` INT NULL,
  `serial_std` INT NULL,
  `relationship_aps` VARCHAR(45) NULL,
  `status_aps` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_aps`),
  INDEX `fk_serial_aps_aup_idx` (`serial_aup` ASC),
  INDEX `fk_serial_aps_std_idx` (`serial_std` ASC),
  CONSTRAINT `fk_serial_aps_aup`
    FOREIGN KEY (`serial_aup`)
    REFERENCES `authorized_persons` (`serial_aup`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_serial_aps_std`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

	-- tabla para estudiantes reaprobados
	CREATE TABLE `preaproved_student` (
  `serial_pst` INT NOT NULL AUTO_INCREMENT,
  `serial_fml` INT NULL,
  `serial_std` INT NULL,
  `credit_card_number_pst` VARCHAR(45) NULL,
  `habiente_card_name_pst` VARCHAR(100) NULL,
  `card_type_pst` VARCHAR(45) NULL,
  `status_pst` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_pst`),
  INDEX `fk_pst_fml_idx` (`serial_fml` ASC),
  INDEX `fk_pst_std_idx` (`serial_std` ASC),
  CONSTRAINT `fk_pst_fml`
    FOREIGN KEY (`serial_fml`)
    REFERENCES `family` (`serial_fml`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pst_std`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

	-- propiedad si un descuento es visisble o no
	ALTER TABLE `discount` 
ADD COLUMN `seeable_dsc` ENUM('YES', 'NO') NULL DEFAULT 'YES' AFTER `status_dsc`;

ALTER TABLE `preaproved_student` 
ADD COLUMN `preaproved_pst` ENUM('YES', 'NO') NULL AFTER `status_pst`;


-- tablas de contratos
CREATE TABLE `wizard_document` (
  `serial_wid` int(11) NOT NULL AUTO_INCREMENT,
  `code_wid` varchar(50) NOT NULL,
  `name_wid` varchar(256) NOT NULL,
  `description_wid` varchar(256) NOT NULL,
  `link_generate_wid` text NOT NULL,
  `status_wid` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_wid`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8;
-- conttrato por año
CREATE TABLE `wizard_document_school_year` (
  `serial_wdy` int(11) NOT NULL AUTO_INCREMENT,
  `serial_wid` int(11) NOT NULL, 
  `serial_scy` int(11) NOT NULL,
  `type_wdy` enum('FORM','NONE') NOT NULL DEFAULT 'NONE',
  `status_wdy` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_wdy`),  
  KEY `fk_wdy_wizard_document_idx` (`serial_wid`),
  KEY `fk_wdy_school_year_idx` (`serial_scy`),
  CONSTRAINT `fk_wdy_wizard_document` FOREIGN KEY (`serial_wid`) REFERENCES `wizard_document` (`serial_wid`),
  CONSTRAINT `fk_wdy_level_school_year` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8;
-- contratos por ususario aceptados o no aceptados
CREATE TABLE `wizard_document_guardian_authorize` (
  `serial_wdg` int(11) NOT NULL AUTO_INCREMENT,
  `serial_wdy` int(11) NOT NULL,
  `serial_grd` int(11) NULL DEFAULT NULL,
  `accepted_wdg` enum('PENDING','NO','YES') NOT NULL DEFAULT 'PENDING',
  `date_wdg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status_wdg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_wdg`),
  KEY `fk_wdg_wizard_document_school_year_idx` (`serial_wdy`),
  CONSTRAINT `fk_wdg_wizard_document_school_year` FOREIGN KEY (`serial_wdy`) REFERENCES `wizard_document_school_year` (`serial_wdy`),
  CONSTRAINT `fk_wdg_guardian` FOREIGN KEY (`serial_grd`) REFERENCES `guardian` (`serial_grd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- aumentar los tipos de contrato seguro de vida, debitos de tarjeta, servicios educativos
ALTER TABLE `wizard_document_school_year` 
CHANGE COLUMN `type_wdy` `type_wdy` ENUM('FORM', 'NONE', 'INSURANCE', 'DEBITS_CARD', 'EDUCATIONAL_SERVICES') NOT NULL DEFAULT 'NONE' ;

INSERT INTO `wizard_document` (`serial_wid`,`code_wid`,`name_wid`,`description_wid`,`link_generate_wid`,`status_wid`) VALUES (1,'201501','Contrato de servicios educativos','Contrato de servicios educativos','','ACTIVE');
INSERT INTO `wizard_document` (`serial_wid`,`code_wid`,`name_wid`,`description_wid`,`link_generate_wid`,`status_wid`) VALUES (2,'201502','Acuerdo de debitos de tarjeta','Acuerdo de debitos de tarjeta','','ACTIVE');
INSERT INTO `wizard_document` (`code_wid`, `name_wid`, `description_wid`, `status_wid`) VALUES ('201503', 'Seguro de vida', 'Seguro de vida', 'ACTIVE');

INSERT INTO `wizard_document_school_year` (`serial_wdy`,`serial_wid`,`serial_scy`,`type_wdy`,`status_wdy`) VALUES (1,1,2,'EDUCATIONAL_SERVICES','ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wdy`,`serial_wid`,`serial_scy`,`type_wdy`,`status_wdy`) VALUES (2,2,2,'DEBITS_CARD','ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wdy`,`serial_wid`,`serial_scy`,`type_wdy`,`status_wdy`) VALUES (3,3,2,'INSURANCE','ACTIVE');

-- usuario de web services
INSERT INTO `user` (`username_usr`, `password_usr`,`change_pass_usr`, `status_usr`) VALUES ('admin_ws', md5('1234'),'NO', 'ACTIVE');

insert into user_profile values ('42394','2','ACTIVE');



-- para pagos

ALTER TABLE `payment_detail` 
ADD COLUMN `num_check_pdt` VARCHAR(20) NULL DEFAULT NULL AFTER `description_pdt`,
ADD COLUMN `account_type_pdt` VARCHAR(15) NULL DEFAULT NULL AFTER `num_check_pdt`,
ADD COLUMN `id_owner_pdt` VARCHAR(45) NULL DEFAULT NULL AFTER `owner_pdt`,
ADD COLUMN `cellphone_owner_pdt` VARCHAR(15) NULL DEFAULT NULL AFTER `id_owner_pdt`,
ADD COLUMN `phone_owner_pdt` VARCHAR(15) NULL DEFAULT NULL AFTER `cellphone_owner_pdt`,
ADD COLUMN `email_owner_pdt` VARCHAR(45) NULL DEFAULT NULL AFTER `phone_owner_pdt`,
ADD COLUMN `address_owner_pdt` VARCHAR(128) NULL DEFAULT NULL AFTER `email_owner_pdt`;

ALTER TABLE `payment_method` 
ADD COLUMN `type_pym` ENUM('CARD','PAYCHECK','DEBIT') NULL AFTER `requires_credit_card_pym`;
 
UPDATE `payment_method` SET `type_pym`='DEBIT' WHERE `serial_pym`='1';
UPDATE `payment_method` SET `type_pym`='PAYCHECK' WHERE `serial_pym`='2';
UPDATE `payment_method` SET `type_pym`='CARD' WHERE `serial_pym`='4';