ALTER TABLE `cat_subject`
ADD COLUMN `english_name_csb` VARCHAR(45) NULL DEFAULT NULL AFTER `short_name_csb`;

-- proceso reporte transcript
INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES ('721', '151', 'Reporte Transcript', 'laravel/public/transcriptReport', '3', 'LOCKABLE');


-- tabla de equivalencias
CREATE TABLE `transcript_scales_grades` (
  `serial_tsg` INT NOT NULL AUTO_INCREMENT,
  `low_grade_tsg` DECIMAL(5,2) NOT NULL,
  `high_grade_tsg` DECIMAL(5,2) NOT NULL,
  `letter_grade_tsg` VARCHAR(45) NOT NULL,
  `number_grade_tsg` DECIMAL(5,1) NOT NULL,
  `status_tsg` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_tsg`));
-- primera configuracion
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('97.0', '100', 'A+', '4');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('93', '96.9', 'A', '4');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('90', '92.9', 'A-', '3.7');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('87', '89.9', 'B+', '3.3');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('83', '86.9', 'B', '3');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('80', '82.9', 'B-', '2.7');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('77', '79.9', 'C+', '2.3');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('73', '76.9', 'C', '2.0');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('70', '72.9', 'C-', '1.7');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('67', '69.9', 'D+', '1.3');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('66', '66.9', 'D', '1.0');
INSERT INTO `transcript_scales_grades` (`low_grade_tsg`, `high_grade_tsg`, `letter_grade_tsg`, `number_grade_tsg`) VALUES ('0', '65.9', 'F', '0.0');


ALTER TABLE `level_section_year_specialty`
ADD COLUMN `number_level` INT NULL AFTER `serial_set`,
ADD COLUMN `use_for_transcript` ENUM('YES','NO') NOT NULL DEFAULT 'NO' AFTER `number_level`;

CREATE TABLE `student_transcript` (
  `serial_str` INT NOT NULL AUTO_INCREMENT,
  `serial_std` INT NULL,
  `comment_str` TEXT NULL,
  PRIMARY KEY (`serial_str`),
  INDEX `fk_serial_std_idx` (`serial_std` ASC),
  CONSTRAINT `fk_serial_std`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `student_transcript`
ADD COLUMN `status_str` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE' AFTER `comment_str`;