-- Autor Fernando Salas 2017-23-06
-- Scripts para el formulario de solvencia económica de admisiones

-- Creación de campos referentes a si el domicilio del representante es arrendado  
ALTER TABLE `aspirant_guardian_addres` 
ADD COLUMN `home_type_gda` ENUM('LEASED', 'OWN') NULL AFTER `working_abroad`,
ADD COLUMN `name_owner_gda` VARCHAR(100) NULL AFTER `home_type_gda`,
ADD COLUMN `telephone_owner_gda` VARCHAR(32) NULL AFTER `name_owner_gda`,
ADD COLUMN `residence_time_gda` INT(11) NULL AFTER `telephone_owner_gda`,
ADD COLUMN `rent_value_gda` DOUBLE(10,2) NULL AFTER `residence_time_gda`;

-- Creación de tabla que muestra cuantas tarjetas y cuentas bancarias tiene el representante del aspirante
CREATE TABLE `aspirant_guardian_creditcard_account` (
  `serial_caag` INT NOT NULL AUTO_INCREMENT,
  `serial_asgu` INT NOT NULL,
  `serial_ccd` INT NULL,
  `type_account_caag` ENUM('SAVINGS', 'CHECKING') NULL,
  `serial_fin` INT NULL,
  `status_caag` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_caag`),
  INDEX `aspirant_guardian_idx` (`serial_asgu` ASC),
  INDEX `credit_card_idx` (`serial_ccd` ASC),
  INDEX `financial_institution_idx` (`serial_fin` ASC),
  CONSTRAINT `aspirant_guardian`
    FOREIGN KEY (`serial_asgu`)
    REFERENCES `aspirant_guardian` (`serial_asgu`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `credit_card`
    FOREIGN KEY (`serial_ccd`)
    REFERENCES `credit_card` (`serial_ccd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `financial_institution`
    FOREIGN KEY (`serial_fin`)
    REFERENCES `financial_institution` (`serial_fin`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
-- Creación de la tabla de propiedades del representante del aspirante (bienes inmuebles, vehículos)	
   CREATE TABLE `aspirant_guardian_property` (
  `serial_agp` INT NOT NULL AUTO_INCREMENT,
  `serial_asgu` INT NOT NULL,
  `type_agp` ENUM('PROPERTY', 'VEHICLE') NOT NULL DEFAULT 'PROPERTY',
  `type_property_agp` ENUM('HOME', 'DEPARTAMENT', 'GROUND', 'OTHER') NULL,
  `mortgage_agp` ENUM('YES', 'NO') NULL,
  `location_agp` VARCHAR(100) NULL,
  `brand_model_vehicle_agp` VARCHAR(45) NULL,
  `year_vehicle_agp` VARCHAR(45) NULL,
  `garment_vehicle_agp` ENUM('YES', 'NO') NULL,
  `status_agp` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_agp`));
  
  
ALTER TABLE `aspirant_guardian_property` 
ADD INDEX `aspirant_guardian_key_idx` (`serial_asgu` ASC);
ALTER TABLE `aspirant_guardian_property` 
ADD CONSTRAINT `aspirant_guardian_key`
  FOREIGN KEY (`serial_asgu`)
  REFERENCES `aspirant_guardian` (`serial_asgu`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- Adicion de columnas referentes a la actividad laboral del representante del aspirante
ALTER TABLE `aspirant_guardian` 
ADD COLUMN `private_activity_asgu` ENUM('YES', 'NO') NULL AFTER `university_degree_asgu`,
ADD COLUMN `afiliate_asgu` ENUM('YES', 'NO') NULL AFTER `private_activity_asgu`,
ADD COLUMN `dependency_relation_asgu` ENUM('YES', 'NO') NULL AFTER `afiliate_asgu`,
ADD COLUMN `personal_reference_asgu` VARCHAR(100) NULL AFTER `dependency_relation_asgu`;

ALTER TABLE `aspirant_guardian_creditcard_account` 
CHANGE COLUMN `status_caag` `status_caag` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;
ALTER TABLE `aspirant_guardian_property` 
CHANGE COLUMN `status_agp` `status_agp` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE' ;
ALTER TABLE `aspirant_guardian_creditcard_account` 
ADD COLUMN `order_caag` INT(11) NULL AFTER `status_caag`;

ALTER TABLE `aspirant_guardian_property` 
ADD COLUMN `order_agp` INT(11) NULL AFTER `status_agp`;