-- El archivo queda comentado para que se lean los comentarios antes de correr en mtodos los colegios
-- Verificar que los update y los insert no dañen las configuraciones del colegio donde se vaya apliacr el script esta diseñado para correr en el 
-- colegio alberto einstein de acuerdo a necesidades especificas
ALTER TABLE `school_year` 
ADD COLUMN `activities_scy` SET('NOTAPPLY','INSCRIPTION') NULL DEFAULT 'NOTAPPLY' COMMENT 'status par inscripciones' AFTER `next_name_scy`;

ALTER TABLE `school_year` 
CHANGE COLUMN `activities_scy` `activities_scy` SET('NOTAPPLY', 'INSCRIPTION') NULL DEFAULT 'NOTAPPLY' COMMENT 'status par inscripciones' ;


CREATE TABLE IF NOT EXISTS `list` (
  `serial_lst` INT NOT NULL AUTO_INCREMENT,
  `name_lst` VARCHAR(100) NULL,
  `alias_lst` ENUM('ENABLEDPREPAY') NULL,
  `status_lst` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_lst`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `list_student_year` (
  `serial_lsy` INT NOT NULL AUTO_INCREMENT,
  `serial_lst` INT NOT NULL,
  `serial_std` INT(11) NOT NULL,
  `serial_scy` INT(11) NULL,
  `date_lsy` DATETIME NULL,
  `serial_usr` INT(11) NOT NULL,
  `status_lsy` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_lsy`, `serial_usr`),
  CONSTRAINT `fk_preapid_student_year_student1`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_preapid_student_year_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_list_student_year_list1`
    FOREIGN KEY (`serial_lst`)
    REFERENCES `list` (`serial_lst`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_list_student_year_user1`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_preapid_student_year_student1_idx` ON `list_student_year` (`serial_std` ASC);

CREATE INDEX `fk_preapid_student_year_school_year1_idx` ON `list_student_year` (`serial_scy` ASC);

CREATE INDEX `fk_list_student_year_list1_idx` ON `list_student_year` (`serial_lst` ASC);

CREATE INDEX `fk_list_student_year_user1_idx` ON `list_student_year` (`serial_usr` ASC);


CREATE TABLE IF NOT EXISTS `list_student_year_log` (
  `serial_lsl` INT NOT NULL,
  `serial_scy` INT(11) NOT NULL,
  `serial_lsy` INT NOT NULL,
  `date_lsl` DATETIME NULL,
  `activity_lsl` ENUM('CREATE', 'DROOP') NULL DEFAULT 'CREATE',
  `status_lsl` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_lsl`),
  CONSTRAINT `fk_list_student_year_log_list_student_year1`
    FOREIGN KEY (`serial_lsy`)
    REFERENCES `list_student_year` (`serial_lsy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_list_student_year_log_school_year1`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_list_student_year_log_list_student_year1_idx` ON `list_student_year_log` (`serial_lsy` ASC);

CREATE INDEX `fk_list_student_year_log_school_year1_idx` ON `list_student_year_log` (`serial_scy` ASC);


INSERT INTO `list` (`name_lst`, `alias_lst`, `status_lst`) VALUES ('LISTA DE PREPAGADOS', 'ENABLEDPREPAY', 'ACTIVE');



CREATE TABLE IF NOT EXISTS `payment_mode` (
  `serial_ptm` INT NOT NULL AUTO_INCREMENT,
  `name_ptm` VARCHAR(45) NULL,
  `short_name_pct` VARCHAR(45) NULL,
  `alias_ptm` ENUM('DEFERREDCURRENT', 'PREPAID', 'CURRENT') NOT NULL,
  `status_ptm` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ptm`))
ENGINE = InnoDB;

ALTER TABLE `payment_method` 
ADD COLUMN `serial_ptm` INT NULL AFTER `serial_pym`,
ADD INDEX `fk_serial_pym_ptm_idx` (`serial_ptm` ASC);
ALTER TABLE `payment_method` 
ADD CONSTRAINT `fk_serial_pym_ptm`
  FOREIGN KEY (`serial_ptm`)
  REFERENCES `payment_mode` (`serial_ptm`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


CREATE TABLE IF NOT EXISTS `payment_method_concept_year_discount` (
  `serial_ppy` INT NOT NULL AUTO_INCREMENT,
  `serial_pym` INT(11) NOT NULL,
  `serial_ydc` INT(11) NOT NULL,
  `serial_pct` INT(11) NOT NULL,
  `serial_ypc` INT(11) NOT NULL,
  `discount_date_limit_ppy` DATE NULL,
  `status_ppy` ENUM('ACTIVE', 'INACTIVE') NULL,
  PRIMARY KEY (`serial_ppy`),
  CONSTRAINT `fk_payment_method_concept_year_discount_payment_method1`
    FOREIGN KEY (`serial_pym`)
    REFERENCES `payment_method` (`serial_pym`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_payment_method_concept_year_discount_discount_payment_conc1`
    FOREIGN KEY (`serial_ydc` , `serial_pct`)
    REFERENCES `discount_payment_concept` (`serial_ydc` , `serial_pct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_payment_method_concept_year_discount_year_level_specialty_1`
    FOREIGN KEY (`serial_ypc`)
    REFERENCES `year_level_specialty_payment_concept` (`serial_ypc`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_payment_method_concept_year_discount_payment_method1_idx` ON `payment_method_concept_year_discount` (`serial_pym` ASC);

CREATE INDEX `fk_payment_method_concept_year_discount_discount_payment_co_idx` ON `payment_method_concept_year_discount` (`serial_ydc` ASC, `serial_pct` ASC);

CREATE INDEX `fk_payment_method_concept_year_discount_year_level_specialt_idx` ON `payment_method_concept_year_discount` (`serial_ypc` ASC);


ALTER TABLE `student_year_debts` 
ADD COLUMN `prepaid_deb` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `start_date_deb`;

ALTER TABLE `wizard_document_school_year` 
CHANGE COLUMN `type_wdy` `type_wdy` ENUM('FORM', 'NONE', 'INSURANCE', 'DEBITS_CARD', 'EDUCATIONAL_SERVICES', 'PREPAID') NOT NULL DEFAULT 'NONE' ;

ALTER TABLE `preaproved_student` 
ADD COLUMN `serial_pym` INT NULL AFTER `serial_pst`,
ADD COLUMN `serial_ccd` INT NULL AFTER `serial_pym`,
ADD COLUMN `serial_fin` INT NULL AFTER `serial_ccd`,
ADD INDEX `fk_pst_pym_idx` (`serial_pym` ASC),
ADD INDEX `fk_pst_ccd_idx` (`serial_ccd` ASC),
ADD INDEX `fk_pst_fin_idx` (`serial_fin` ASC);
ALTER TABLE `preaproved_student` 
ADD CONSTRAINT `fk_pst_pym`
  FOREIGN KEY (`serial_pym`)
  REFERENCES `payment_method` (`serial_pym`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_pst_ccd`
  FOREIGN KEY (`serial_ccd`)
  REFERENCES `credit_card` (`serial_ccd`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_pst_fin`
  FOREIGN KEY (`serial_fin`)
  REFERENCES `financial_institution` (`serial_fin`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- insertar el modo mde pago diferido corriente
  INSERT INTO `payment_mode` (`name_ptm`, `short_name_pct`, `alias_ptm`, `status_ptm`) VALUES ('Diferido corriente', 'dc', 'DEFERREDCURRENT', 'ACTIVE');
-- a la forma de pago tarjeta de crédito se le asigna como diferido corriente
UPDATE `payment_method` SET `serial_ptm`='1' WHERE `serial_pym`='1';

ALTER TABLE `preaproved_student` 
ADD COLUMN `serial_pdt` INT NULL AFTER `serial_std`,
ADD INDEX `fk_pst_pdt_idx` (`serial_pdt` ASC);
ALTER TABLE `preaproved_student` 
ADD CONSTRAINT `fk_pst_pdt`
  FOREIGN KEY (`serial_pdt`)
  REFERENCES `payment_detail` (`serial_pdt`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
 -- aumentar los tipos de contrato prepago
ALTER TABLE `wizard_document_school_year` 
CHANGE COLUMN `type_wdy` `type_wdy` ENUM('FORM', 'NONE', 'INSURANCE', 'DEBITS_CARD', 'EDUCATIONAL_SERVICES', 'PREPAID', 'TRAVEL') NOT NULL DEFAULT 'NONE' ;
-- Inserción de contrato de prepago verificar seriales en el colegio donde se vaya a aplicar 
INSERT INTO `wizard_document` (`code_wid`, `name_wid`, `description_wid`, `status_wid`) VALUES ('201504', 'Estudiantes en prepago', 'Estudiantes en prepago', 'ACTIVE');
INSERT INTO `wizard_document` (`code_wid`, `name_wid`, `description_wid`, `status_wid`) VALUES ('201505', 'Transporte', 'Transporte', 'ACTIVE');

-- configuración para el colegio alberto einstein verificar los seriales de los difenetes contratos y años lectivos para configurar los contratos por año lectivo
INSERT INTO `wizard_document_school_year` (`serial_wdy`, `serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES (NULL, '1', '3', 'EDUCATIONAL_SERVICES', 'ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wdy`, `serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES (NULL, '2', '3', 'DEBITS_CARD', 'ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wdy`, `serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES (NULL, '3', '3', 'INSURANCE', 'ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES ('4', '3', 'PREPAID', 'ACTIVE');
INSERT INTO `wizard_document_school_year` (`serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES ('5', '3', 'TRAVEL', 'ACTIVE');
-- ingreso de titúlos para el wixzard de inscripciones para
INSERT INTO `registration_process_wizard` (`name_rpw`, `order_rpw`, `status_rpw`) VALUES ('SECCIÓN TRANSPORTE', '1', 'ACTIVE');
INSERT INTO `registration_process_wizard` (`name_rpw`, `order_rpw`, `status_rpw`) VALUES ('SECCIÓN PREPAGO', '2', 'ACTIVE');


ALTER TABLE `student_year_debts` 
ADD COLUMN `preaproved_deb` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `prepaid_deb`;

ALTER TABLE `list_student_year` 
ADD COLUMN `acept_lsy` ENUM('YES', 'NO') NULL AFTER `serial_usr`;

ALTER TABLE `list` 
CHANGE COLUMN `alias_lst` `alias_lst` ENUM('ENABLEDPREPAY', 'INSURANCE') NULL DEFAULT NULL ;


INSERT INTO `list` (`name_lst`, `alias_lst`, `status_lst`) VALUES ('TOMA SEGURO', 'INSURANCE', 'ACTIVE');

INSERT INTO `wizard_document` (`code_wid`, `name_wid`, `description_wid`, `status_wid`) VALUES ('201506', 'Toma seguro', 'Si el padre de familia toma seguro', 'ACTIVE');



ALTER TABLE `wizard_document_school_year` 
CHANGE COLUMN `type_wdy` `type_wdy` ENUM('FORM', 'NONE', 'INSURANCE', 'DEBITS_CARD', 'EDUCATIONAL_SERVICES', 'PREPAID', 'TRAVEL', 'TAKE_INSURANCE') NOT NULL DEFAULT 'NONE' ;

INSERT INTO `wizard_document_school_year` (`serial_wid`, `serial_scy`, `type_wdy`, `status_wdy`) VALUES ('6', '3', 'TAKE_INSURANCE', 'ACTIVE');


ALTER TABLE `list` 
CHANGE COLUMN `alias_lst` `alias_lst` ENUM('ENABLEDPREPAY', 'INSURANCE', 'FINALIZE') NULL DEFAULT NULL ;

INSERT INTO `list` (`name_lst`, `alias_lst`,status_lst) VALUES ('FINALIZACIÓN DE PROCESO', 'FINALIZE','ACTIVE');

INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('625', '52', 'Inscripción financiero', 'laravel/public/wizardPayments', '100.00', 'LOCKABLE');

insert into allowed_pages_by_process values(625,'wizardcontroller@getindex');
insert into allowed_pages_by_process values(625,'wizardcontroller@posttravelsection');
insert into allowed_pages_by_process values(625,'wizardcontroller@postsavetravelsection');
insert into allowed_pages_by_process values(625,'wizardcontroller@getcheckisasignedpaymenttostudent');
insert into allowed_pages_by_process values(625,'wizardcontroller@getcheckisasignedpaymentgrouptostudent');
insert into allowed_pages_by_process values(625,'wizardcontroller@poststudensprepaid');
insert into allowed_pages_by_process values(625,'wizardcontroller@postsavestudentsprepaid');
insert into allowed_pages_by_process values(625,'wizardcontroller@poststudenspreaproved');
insert into allowed_pages_by_process values(625,'wizardcontroller@postsavepreaprovedstudnets');
insert into allowed_pages_by_process values(625,'wizardcontroller@poststudensinsurance');
insert into allowed_pages_by_process values(625,'wizardcontroller@getviewreview');
insert into allowed_pages_by_process values(625,'wizardcontroller@getprepaidquestion');

insert into allowed_pages_by_process values('497','paymentcontroller@getdates');
insert into allowed_pages_by_process values('497','paymentmethodcontroller@getpaymentmethod');
insert into allowed_pages_by_process values('497','paymentmodecontroller@getpymentsmode');


INSERT INTO `payment_mode` (`serial_ptm`, `name_ptm`, `short_name_pct`, `alias_ptm`, `status_ptm`) VALUES ('2', 'Prepago', 'pp', 'PREPAID', 'ACTIVE');
INSERT INTO `payment_mode` (`serial_ptm`, `name_ptm`, `short_name_pct`, `alias_ptm`, `status_ptm`) VALUES ('3', 'Efectivo/corriente', 'cr', 'CURRENT', 'ACTIVE');


UPDATE `payment_method` SET `serial_ptm`='2' WHERE `serial_pym`='2';
UPDATE `payment_method` SET `serial_ptm`='2' WHERE `serial_pym`='4';
UPDATE `payment_method` SET `serial_ptm`='1' WHERE `serial_pym`='5';
UPDATE `payment_method` SET `serial_ptm`='3' WHERE `serial_pym`='3';

ALTER TABLE `payment_method` 
ADD COLUMN `avalible_preaproved_pym` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `type_pym`;

ALTER TABLE `student_year_discount_values` 
ADD COLUMN `payment_method_discount_ydv` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `status_ydv`;

ALTER TABLE `student_year_discount_values` 
CHANGE COLUMN `payment_method_discount_ydv` `payment_method_discount_ydv` ENUM('YES', 'NO') NULL DEFAULT 'NO' AFTER `comment_ydv`;

UPDATE `student_year` SET `date_start_invoice_sty`='2017-09-01' WHERE `serial_scy`='3';

UPDATE `registration_process_wizard` SET `name_rpw`='BIENVENIDO' WHERE `serial_rpw`='8';

insert into allowed_pages_by_process values(625,'wizardcontroller@postsaveservicescontract');

UPDATE `registration_process_wizard` SET `name_rpw`='SECCIÓN TRANSPORTE' WHERE `serial_rpw`='9';

INSERT INTO `registration_process_wizard` (`serial_rpw`, `name_rpw`, `order_rpw`, `status_rpw`) VALUES ('10', 'SELECCIÓN DE PREPAGO', '3', 'ACTIVE');

INSERT INTO `registration_process_wizard` (`serial_rpw`, `name_rpw`, `order_rpw`, `status_rpw`) VALUES ('11', 'SECCIÓN DE PREAPROBADOS', '4', 'ACTIVE');


-- Tomar en cuenta el año lectivo puede cambiar de sereial_scy de acuerdo al colegio donde se vaya a correr el scricpt 
UPDATE `school_year` SET `enable_travel_scy`='NO' WHERE `serial_scy`='2';
UPDATE `school_year` SET `enable_travel_scy`='YES' WHERE `serial_scy`='3';
UPDATE `payment_method` SET `serial_ptm`='3' WHERE `serial_pym`='2';
UPDATE `payment_method` SET `serial_ptm`='3' WHERE `serial_pym`='4';
UPDATE `payment_method` SET `serial_ptm`='3' WHERE `serial_pym`='5';
INSERT INTO `payment_method` (`serial_pym`, `serial_ptm`, `name_pym`, `requires_owner_info_pym`, `requires_financial_pym`, `requires_credit_card_pym`, `type_pym`, `avalible_preaproved_pym`, `status_pym`) VALUES ('', '3', 'TARJETA DE CRÉDITO', 'YES', 'YES', 'YES', 'CARD', 'YES', 'ACTIVE');
INSERT INTO `payment_method` (`serial_pym`, `serial_ptm`, `name_pym`, `requires_owner_info_pym`, `requires_financial_pym`, `requires_credit_card_pym`, `type_pym`, `avalible_preaproved_pym`, `status_pym`) VALUES ('', '2', 'CHEQUE', 'YES', 'YES', 'NO', 'PAYCHECK', 'NO', 'ACTIVE');
INSERT INTO `payment_method` (`serial_pym`, `serial_ptm`, `name_pym`, `requires_owner_info_pym`, `requires_financial_pym`, `requires_credit_card_pym`, `type_pym`, `avalible_preaproved_pym`, `status_pym`) VALUES ('', '2', 'TRANSFERENCIA / DEPÓSITO', 'YES', 'NO', 'NO', 'TRANSFER', 'NO', 'ACTIVE');

-- migrar formas de pago para el colegio einstein
update payment_detail set serial_pym=6 where serial_pym=1 and status_pdt='ACTIVE' and serial_ccd in (3,5,7,6);
UPDATE `registration_process_wizard` SET `name_rpw`='REGISTRO DE INSCRIPCIÓN' WHERE `serial_rpw`='8';
UPDATE `registration_process_wizard` SET `name_rpw`='SELECCIÓN TRANSPORTE' WHERE `serial_rpw`='9';
UPDATE `registration_process_wizard` SET `name_rpw`='PREPAGO' WHERE `serial_rpw`='10';
UPDATE `registration_process_wizard` SET `name_rpw`='OPCIONES DE PAGO' WHERE `serial_rpw`='11';
UPDATE school_year SET `activities_scy`='INSCRIPTION' WHERE `serial_scy`='3';
UPDATE `process` SET `name_prc`='Iniciar' WHERE `serial_prc`='625';
INSERT INTO `process` (`serial_prc`,`name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES (627,'Inscripciones', '#', '51', 'LOCKABLE');
UPDATE `process` SET `prc_serial_prc`='627' WHERE `serial_prc`='625';

ALTER TABLE `payment_mode` 
CHANGE COLUMN `alias_ptm` `alias_ptm` ENUM('DEFERREDCURRENT', 'PREPAID', 'CURRENT', 'DEFERREDCURRENTSINGLE') NOT NULL ;