CREATE TABLE `feeding_type` (
  `serial_fdn` INT NOT NULL AUTO_INCREMENT,
  `name_fdn` VARCHAR(200) NULL,
  `type_fdn` ENUM('QUANTITY', 'PREPAID') NULL,
  `status_fdn` ENUM('ACTIVE', 'INCATIVE') NULL,
  PRIMARY KEY (`serial_fdn`)) ENGINE = InnoDB;


CREATE TABLE `payment_concept_type_feeding` (
  `serial_pyf` INT NOT NULL AUTO_INCREMENT,
  `serial_pct` INT NOT NULL,
  `serial_fdn` INT NOT NULL,
  `quantity_pyf` INT NULL,
  `status_pyf` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_pyf`, `serial_pct`, `serial_fdn`),
  INDEX `fk_pyf_pct_idx` (`serial_pct` ASC),
  CONSTRAINT `fk_pyf_fdn`
    FOREIGN KEY (`serial_pyf`)
    REFERENCES `feeding_type` (`serial_fdn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pyf_pct`
    FOREIGN KEY (`serial_pct`)
    REFERENCES `payment_concept` (`serial_pct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) ENGINE = InnoDB;


INSERT INTO `feeding_type` (`name_fdn`, `type_fdn`, `status_fdn`) VALUES ('Almuerzos', 'QUANTITY', 'ACTIVE');
INSERT INTO `feeding_type` (`name_fdn`, `type_fdn`, `status_fdn`) VALUES ('Refrigerios', 'QUANTITY', 'ACTIVE');
INSERT INTO `feeding_type` (`name_fdn`, `type_fdn`, `status_fdn`) VALUES ('Especiales de la tarde', 'QUANTITY', 'ACTIVE');
INSERT INTO `feeding_type` (`name_fdn`, `type_fdn`, `status_fdn`) VALUES ('Prepago', 'PREPAID', 'ACTIVE');


CREATE TABLE `student_type_feeding` (
  `serial_stf` INT NOT NULL AUTO_INCREMENT,
  `serial_std` INT NULL,
  `serial_scy` INT NULL,
  `serial_fdn` INT NULL,
  `total_prepaid_sft` DOUBLE NULL,
  `total_prepaid_consumed_sft` DOUBLE NULL,
  `balance_prepaid_sft` DOUBLE NULL,
  `total_quantity_sft` INT NULL,
  `total_quantity_consumed_sft` INT NULL,
  `balance_quantity_sft` INT NULL,
  `status_sft` ENUM('ACTIVE', 'INACTIVE') NULL,
  `serial_usr` INT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_stf`),
  INDEX `fk_stf_std_idx` (`serial_std` ASC),
  INDEX `fk_stf_scy_idx` (`serial_scy` ASC),
  INDEX `fk_sft_fdn_idx` (`serial_fdn` ASC),
  INDEX `fk_sft_usr_idx` (`serial_usr` ASC),
  CONSTRAINT `fk_stf_std`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_stf_scy`
    FOREIGN KEY (`serial_scy`)
    REFERENCES `school_year` (`serial_scy`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sft_fdn`
    FOREIGN KEY (`serial_fdn`)
    REFERENCES `feeding_type` (`serial_fdn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sft_usr`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) ENGINE = InnoDB;
	
	CREATE TABLE `student_feeding_consumed` (
  `serial_stc` INT NOT NULL AUTO_INCREMENT,
  `serial_stf` INT NULL,
  `value_quantity_consumed_stc` INT NULL,
  `value_prepaid_consumed_stc` DOUBLE NULL,
  `date_stc` DATE NULL,
  `serial_usr` INT NULL,
  `status_stc` ENUM('ACTIVE', 'INACTIVE') NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_stc`),
  INDEX `fk_stc_stf_idx` (`serial_stf` ASC),
  INDEX `fk_stc_usr_idx` (`serial_usr` ASC),
  CONSTRAINT `fk_stc_stf`
    FOREIGN KEY (`serial_stf`)
    REFERENCES `student_type_feeding` (`serial_stf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_stc_usr`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION) ENGINE = InnoDB;
	
	ALTER TABLE `student_feeding_consumed` 
ADD COLUMN `ip_address_std` VARCHAR(100) NULL AFTER `serial_usr`;


CREATE TABLE `payement_concept_feeding_type` (
  `serial_pcf` INT NOT NULL AUTO_INCREMENT,
  `serial_pct` INT NULL,
  `serial_fdn` INT NULL,
  `serial_usr` INT NULL,
  `quantity_pcf` INT NULL,
  `status_pcf` ENUM('ACTIVE', 'INACTIVE') NULL DEFAULT 'ACTIVE',
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`serial_pcf`),
  INDEX `fk_pcf_fdn_idx` (`serial_fdn` ASC),
  INDEX `fk_pcf_pct_idx` (`serial_pct` ASC),
  INDEX `fk_pcf_usr_idx` (`serial_usr` ASC),
  CONSTRAINT `fk_pcf_fdn`
    FOREIGN KEY (`serial_fdn`)
    REFERENCES `feeding_type` (`serial_fdn`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pcf_pct`
    FOREIGN KEY (`serial_pct`)
    REFERENCES `payment_concept` (`serial_pct`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pcf_usr`
    FOREIGN KEY (`serial_usr`)
    REFERENCES `user` (`serial_usr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)  ENGINE = InnoDB;
	
	ALTER TABLE `payement_concept_feeding_type` 
ADD COLUMN `ip_addres_pcf` VARCHAR(100) NULL AFTER `quantity_pcf`;

INSERT INTO `process` (`serial_prc`, `name_prc`, `link_prc`) VALUES ('858', 'Alimentación ', '#');
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('859', '858', 'Gestión de alimentación', 'laravel/public/feedingManage', '1', 'LOCKABLE');

insert into allowed_pages_by_process values(857,'feedingmanagecontroller@getindex');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@getsetuppaymentstypefeeding');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@getstudentscalculate');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@poststudentinfo');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@postsavestudenttypefeeding');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@poststudenthistory');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@postrecalculate');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@postsavepayementconceptfeedingtype');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@postdeleteconsumed');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@getstudents');
insert into allowed_pages_by_process values(857,'feedingmanagecontroller@postdeleteconsumed');
insert into allowed_pages_by_process values(857,'sectioncontroller@getsectionsbyschoolyearoptionsall');
insert into allowed_pages_by_process values(857,'coursecontroller@allbysectionoptionsall');



