-- campo en asistencia adicional de tipos de faltas
ALTER TABLE attendance ADD COLUMN additional_state_ace  ENUM ('NONE','CLASS_DROPOUT','EARLY_DEPARTURE') NOT NULL
DEFAULT 'NONE' AFTER blocked_by_inspector;

-- campo que indica a que hora se enviara la notificacion de novedades de asistencia
ALTER TABLE school_year ADD COLUMN time_send_notification_scy TIME NULL AFTER activities_scy;
ALTER TABLE school_year ADD COLUMN time_processed_supervisor_scy TIME NULL AFTER time_send_notification_scy;
ALTER TABLE school_year ADD COLUMN day_processed_supervisor_scy INT NULL AFTER time_processed_supervisor_scy;
ALTER TABLE school_year ADD COLUMN incidence_justifications_scy ENUM('YES','NO') NULL DEFAULT 'NO' AFTER day_processed_supervisor_scy;

insert into process (serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc) values(806,NULL,'Justificaciones de Asistencia','#',777,'LOCKABLE');
insert into process (serial_prc,prc_serial_prc, name_prc, link_prc, weight_prc, status_prc) values(807,806,'Administración de Justificaciones de Representantes','laravel/public/administrateGuardianJustification',777,'LOCKABLE');

insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@getIndex');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@postLoadAwaitView');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@postLoadApprovedView');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@postLoadRejectedView');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@postSave');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@getInfoModal');
insert into allowed_pages_by_process values(807,'administrateguardianjustificationcontroller@postReport');


-- crear tabla de justificacion

-- Creación de tabla que muestra cuantas tarjetas y cuentas bancarias tiene el representante del aspirante
CREATE TABLE guardian_justification_attendance (
  `serial_gja` INT NOT NULL AUTO_INCREMENT,
  `serial_std` INT NOT NULL,
  `serial_scd` INT NOT NULL,
  `serial_ddc` INT NOT NULL,
  `serial_osb` INT NULL,
  `serial_lop` INT NULL,
  `serial_sbc` INT NULL,
  `serial_obs` INT NULL,  -- razon de justificacion
  `date_justification_gja`   DATE NOT NULL, -- fecha para la que se esta haciendo la justificación
  `serial_usr` INT NOT NULL,
  `type_gja` ENUM('EXCUSED_ABSENCE','EXCUSED_DELAY','PERMISSION','EARLY_DEPARTURE'),
  `locked_by_inspector` ENUM('YES','NO') NOT NULL DEFAULT 'NO',
  `observation` TEXT NULL,
  `status_gja` ENUM('ACTIVE','APPROVED','REJECTED','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `processed_at` DATETIME DEFAULT NULL DEFAULT NULL, -- fecha en que se procesa la justificacion
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_gja`),
  CONSTRAINT student FOREIGN KEY (serial_std) REFERENCES student (serial_std) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT schedule FOREIGN KEY (serial_scd) REFERENCES schedule (serial_scd) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT date_day_configuration FOREIGN KEY (serial_ddc) REFERENCES date_day_configuration (serial_ddc) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT optional_subject FOREIGN KEY (serial_osb) REFERENCES optional_subject (serial_osb) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT level_optional_group FOREIGN KEY (serial_lop) REFERENCES level_optional_group (serial_lop) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT user_relathion FOREIGN KEY (serial_usr) REFERENCES user (serial_usr) ON DELETE NO ACTION ON UPDATE NO ACTION
);

ALTER TABLE `attendance`
CHANGE COLUMN `type_ace` `type_ace`
enum('UNEXCUSED_ABSENCE','EXCUSED_ABSENCE','DELAY','ESCAPE','PERMISSION','EXCUSED_DELAY') NOT NULL;

ALTER TABLE `incidence_category`
CHANGE COLUMN `attendance_relation_ica` `attendance_relation_ica`
enum('NONE','UNEXCUSED_ABSENCE','EXCUSED_ABSENCE','DELAY','PRESENT','EXCUSED_DELAY','EARLY_DEPARTURE') NOT NULL DEFAULT 'NONE';