-- División del catálogo DECE (para el formulario y pantalla de actividades)
ALTER TABLE `dce_catalog` 
CHANGE COLUMN `type_dca` `type_dca` ENUM('CHANGE_FAMILY_STRUCTURE', 'RECREATIONAL_ACTIVITIES', 'STRENGTHS', 'EXTERNAL_SUPPORT', 'FAMILY_RELATIONSHIP', 'BEHAVIOR', 'FAMILY_PATHOLOGICAL', 'REASON_ATTENTION', 'OTHER') NULL DEFAULT 'OTHER' ,
ADD COLUMN `type_page` ENUM('FORM', 'ACTIVITY') NOT NULL DEFAULT 'FORM' AFTER `type_dca`;


-- allow pages Pantalla de registro de actividades DECE
INSERT INTO  allowed_pages_by_process values
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@postdceform'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@postdceactivities'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'quotacontroller@postsearchfamily'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@postdceformpdfreport'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@postsaveactivitydata'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@getactivitydcedata'),
((select serial_prc from process where link_prc = 'laravel/public/dce/form'), 'dcecontroller@getactivitiesdcebyemployee');


-- registro de actividades
CREATE TABLE `dce_activity` (
  `serial_dcy` INT(11) NOT NULL AUTO_INCREMENT,
  `serial_emp` INT(11) NULL,
  `serial_scy` INT(11) NOT NULL,
  `register_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `activity_date` DATE NOT NULL,
  `status` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dcy`),
  INDEX `fk_employee_register_activity_dce_idx` (`serial_emp` ASC),
  INDEX `fk_school_year_activity_dce_idx` (`serial_scy` ASC),
  CONSTRAINT `fk_employee_register_activity_dce`
    FOREIGN KEY (`serial_emp`)
    REFERENCES `employee` (`serial_emp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
	CONSTRAINT `fk_school_year_activity_dce`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
	
CREATE TABLE `dce_activity_std_crs` (
    `serial_dcy` INT(11) NOT NULL,
    `serial_std` INT(11) NULL,
    `serial_crs` INT(11) NULL,
    `status` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    INDEX `fk_student_activity_dce_idx` (`serial_std` ASC),
    INDEX `fk_course_activity_dce_idx` (`serial_crs` ASC),
    INDEX `fk_activity_dce_idx` (`serial_dcy` ASC),
    CONSTRAINT `fk_student_activity_dce` FOREIGN KEY (`serial_std`)
        REFERENCES `student` (`serial_std`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_course_activity_dce` FOREIGN KEY (`serial_crs`)
        REFERENCES `course` (`serial_crs`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_activity_dce` FOREIGN KEY (`serial_dcy`)
        REFERENCES `dce_activity` (`serial_dcy`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
);
	
CREATE TABLE `dce_activity_reason_request` (
    `serial_dcy` INT(11) NOT NULL,
    `serial_dca` INT(11) NULL,
    `serial_emp` INT(11) NULL,
    `status` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',    
    INDEX `fk_activity_reason_dce_idx` (`serial_dcy` ASC),
    INDEX `fk_dce_activity_catalog_idx` (`serial_dca` ASC),
    INDEX `fk_activity_request_employee_dce_idx` (`serial_emp` ASC),
    CONSTRAINT `fk_activity_reason_dce` FOREIGN KEY (`serial_dcy`)
        REFERENCES `dce_activity` (`serial_dcy`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_dce_activity_catalog` FOREIGN KEY (`serial_dca`)
        REFERENCES `dce_catalog` (`serial_dca`)
        ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_activity_request_employee_dce` FOREIGN KEY (`serial_emp`)
        REFERENCES `employee` (`serial_emp`)
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

ALTER TABLE `dce_activity` 
ADD COLUMN `comment` TEXT NULL DEFAULT NULL AFTER `activity_date`;

ALTER TABLE `dce_common_data` 
CHANGE COLUMN `treatment_time` `treatment_time` VARCHAR(45) NULL DEFAULT NULL AFTER `serial_dca`;

ALTER TABLE `dce_common_data` 
CHANGE COLUMN `age_start` `age_start` VARCHAR(45) NULL DEFAULT NULL AFTER `serial_dca`,
ADD COLUMN `diagnosis` VARCHAR(45) NULL DEFAULT NULL AFTER `relationship`;