-- Creacion de tabla para agrupar materias
CREATE TABLE `subject_parent` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;

-- Se aumenta un campo para el foreign key
ALTER TABLE `cat_subject` 
ADD COLUMN `parent_subject` INT NULL DEFAULT NULL AFTER `status_csb`,
ADD INDEX `fk_cat_subject_1_idx` (`parent_subject` ASC);

-- Se establece la relacion
ALTER TABLE `cat_subject` 
ADD CONSTRAINT `fk_cat_subject_1`
  FOREIGN KEY (`parent_subject`)
  REFERENCES `subject_parent` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

-- Process
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('600', '149', 'Estadísticas', '#', '6', 'LOCKABLE');

INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('601', '600', 'Materias', 'laravel/public/statistics', '1', 'LOCKABLE');

-- Allow Pages
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@getindex');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@postsections');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@postsubjects');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@postperiods');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@postsubperiods');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@postqualitativeequivalence');
INSERT INTO allowed_pages_by_process (serial_prc, url_dsc) VALUES (601, 'statisticscontroller@poststatisticsdata');


-- Catalogo de nueva tabla (Colegio Terranova) puede servir de referencia
--INSERT INTO `subject_parent` (`name`, `description`) VALUES ('ARTE', 'ARTE');
-- INSERT INTO `subject_parent` (`name`) VALUES ('BIOLOGÍA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('CIENCIAS NATURALES');
-- INSERT INTO `subject_parent` (`name`) VALUES ('DESARROLLO PERSONAL Y SOCIAL');
-- INSERT INTO `subject_parent` (`name`) VALUES ('DRAMA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('EDUCACIÓN FÍSICA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('EDUCACIÓN PARA LA CIUDADANÍA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('ENTORNO NATURAL Y SOCIAL');
-- INSERT INTO `subject_parent` (`name`) VALUES ('ESPAÑOL');
-- INSERT INTO `subject_parent` (`name`) VALUES ('ESTUDIOS SOCIALES');
-- INSERT INTO `subject_parent` (`name`) VALUES ('FÍSICA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('FRANCÉS');
-- INSERT INTO `subject_parent` (`name`) VALUES ('FUNCIONES BÁSICAS');
-- INSERT INTO `subject_parent` (`name`) VALUES ('GESTIÓN EMPRESARIAL');
-- INSERT INTO `subject_parent` (`name`) VALUES ('HISTORIA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('IB LEARNER PROFILE');
-- INSERT INTO `subject_parent` (`name`) VALUES ('INGLÉS');
-- INSERT INTO `subject_parent` (`name`) VALUES ('MATEMÁTICA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('MÚSICA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('PSICOLOGÍA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('QUÍMICA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('TECNOLOGÍA');
-- INSERT INTO `subject_parent` (`name`) VALUES ('TEORÍA DEL CONOCIMIENTO');


-- Update a cat_subject SOLAMENTE TERRANOVA
-- UPDATE `cat_subject` SET `parent_subject`='1' WHERE `serial_csb`='60';