-- SCRIPT DE BASE DE DATOS: S.E.F (Sistema de Evaluación Formativa)
-- Institución: IED Evelyn Abuchaibe de Daes
-- Versión: 1.2 (Soporte IA Gemini + Login Híbrido)


CREATE TABLE `student_profiles` (
  `user_id` CHAR(36) NOT NULL,
  `institutional_code` VARCHAR(50) NULL,
  `piar_status` TINYINT(1) DEFAULT 0 COMMENT 'True = Requiere Ajustes',
  `piar_details` JSON NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_profile_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- 2. ACADÉMICO
-- -----------------------------------------------------
CREATE TABLE `academic_years` (
  `year_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(20) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_current` TINYINT(1) DEFAULT 0,
  CONSTRAINT `chk_dates` CHECK (`end_date` > `start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `courses` (
  `course_id` INT AUTO_INCREMENT PRIMARY KEY,
  `year_id` INT NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `level_numeric` INT NOT NULL,
  CONSTRAINT `fk_course_year` FOREIGN KEY (`year_id`) REFERENCES `academic_years` (`year_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subjects` (
  `subject_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `area` VARCHAR(100) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `teacher_assignments` (
  `assignment_id` INT AUTO_INCREMENT PRIMARY KEY,
  `teacher_id` CHAR(36) NOT NULL,
  `course_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `is_director` TINYINT(1) DEFAULT 0,
  UNIQUE KEY `unique_assignment` (`teacher_id`, `course_id`, `subject_id`),
  CONSTRAINT `fk_assign_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_assign_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`),
  CONSTRAINT `fk_assign_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `enrollments` (
  `enrollment_id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` CHAR(36) NOT NULL,
  `course_id` INT NOT NULL,
  `year_id` INT NOT NULL,
  `status` ENUM('ACTIVE', 'WITHDRAWN', 'TRANSFERRED') DEFAULT 'ACTIVE',
  CONSTRAINT `fk_enroll_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_enroll_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- 3. CURRÍCULO
-- -----------------------------------------------------
CREATE TABLE `units` (
  `unit_id` INT AUTO_INCREMENT PRIMARY KEY,
  `subject_id` INT NOT NULL,
  `period` INT NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `learning_goal` TEXT NOT NULL,
  `is_steam_project` TINYINT(1) DEFAULT 0,
  `weight_config` JSON DEFAULT NULL,
  CONSTRAINT `fk_unit_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `steam_collaborations` (
  `steam_id` INT AUTO_INCREMENT PRIMARY KEY,
  `main_unit_id` INT NOT NULL,
  `collaborating_subject_id` INT NOT NULL,
  CONSTRAINT `fk_steam_unit` FOREIGN KEY (`main_unit_id`) REFERENCES `units` (`unit_id`),
  CONSTRAINT `fk_steam_subject` FOREIGN KEY (`collaborating_subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `learning_intentions` (
  `ia_id` INT AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT NOT NULL,
  `level` ENUM('ADQUISICION', 'PROFUNDIZACION', 'TRANSFERENCIA') NOT NULL,
  `description` TEXT NOT NULL,
  CONSTRAINT `fk_ia_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `success_criteria` (
  `ce_id` INT AUTO_INCREMENT PRIMARY KEY,
  `ia_id` INT NOT NULL,
  `description` TEXT NOT NULL,
  `anti_false_positive_prompt` TEXT NULL,
  `min_evidence_required` TINYINT(1) DEFAULT 1,
  `remedial_resource_url` TEXT NULL,
  `common_errors` JSON NULL,
  CONSTRAINT `fk_ce_ia` FOREIGN KEY (`ia_id`) REFERENCES `learning_intentions` (`ia_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- 4. EVALUACIÓN Y BITÁCORA
-- -----------------------------------------------------
CREATE TABLE `bitacora_logs` (
  `log_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `student_id` CHAR(36) NOT NULL,
  `ce_id` INT NOT NULL,
  `teacher_id` CHAR(36) NOT NULL,
  `status` ENUM('VERDE', 'AMARILLO', 'ROJO') NOT NULL,
  `error_tag` VARCHAR(100) NULL,
  `action_taken` ENUM('MICRO_LECCION', 'AGRUPAMIENTO', 'TUTORIA', 'TRIAJE', 'NINGUNA') DEFAULT 'NINGUNA',
  `feedback_text` TEXT NULL,
  `evidence_url` TEXT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_log_lookup` (`student_id`, `ce_id`),
  CONSTRAINT `fk_log_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_log_ce` FOREIGN KEY (`ce_id`) REFERENCES `success_criteria` (`ce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `student_progress_snapshot` (
  `student_id` CHAR(36) NOT NULL,
  `ce_id` INT NOT NULL,
  `current_status` ENUM('VERDE', 'AMARILLO', 'ROJO') NOT NULL,
  `attempts_count` INT DEFAULT 0,
  `last_updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `next_check_due` DATETIME NULL,
  `is_locked_for_summative` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`student_id`, `ce_id`),
  INDEX `idx_sla_check` (`next_check_due`),
  CONSTRAINT `fk_snap_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_snap_ce` FOREIGN KEY (`ce_id`) REFERENCES `success_criteria` (`ce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `remedial_tasks` (
  `task_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `student_id` CHAR(36) NOT NULL,
  `ce_id` INT NOT NULL,
  `resource_url` TEXT NOT NULL,
  `instructions` TEXT NULL COMMENT 'Instrucciones específicas del docente/IA',
  `is_ai_generated` TINYINT(1) DEFAULT 0 COMMENT '1 si fue sugerida por Gemini',
  `assigned_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  `is_verified_by_teacher` TINYINT(1) DEFAULT 0,
  CONSTRAINT `fk_task_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `summative_grades` (
  `grade_id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` CHAR(36) NOT NULL,
  `unit_id` INT NOT NULL,
  `e1_score` DECIMAL(5,2) NULL,
  `e2_score` DECIMAL(5,2) NULL,
  `e3_score` DECIMAL(5,2) NULL,
  `final_score` DECIMAL(5,2) NULL,
  `includes_excellence_bonus` TINYINT(1) DEFAULT 0,
  `is_closed` TINYINT(1) DEFAULT 0,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_grade_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_grade_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `recovery_requests` (
  `request_id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` CHAR(36) NOT NULL,
  `unit_id` INT NOT NULL,
  `status` ENUM('PENDING', 'APPROVED', 'COMPLETED', 'REJECTED') DEFAULT 'PENDING',
  `previous_grade` DECIMAL(5,2) NULL,
  `new_grade` DECIMAL(5,2) NULL,
  `exam_date` DATE NULL,
  CONSTRAINT `fk_rec_student` FOREIGN KEY (`student_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_rec_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- TRIGGER DE AUTOMATIZACIÓN (CORE DE NEGOCIO)
-- -----------------------------------------------------
DELIMITER $$
CREATE TRIGGER `after_bitacora_insert` AFTER INSERT ON `bitacora_logs`
FOR EACH ROW
BEGIN
    INSERT INTO `student_progress_snapshot` 
    (`student_id`, `ce_id`, `current_status`, `attempts_count`, `last_updated_at`, `is_locked_for_summative`)
    VALUES 
    (NEW.student_id, NEW.ce_id, NEW.status, 
     IF(NEW.status = 'ROJO', 1, 0),
     NOW(),
     IF(NEW.status = 'VERDE', 0, 1)
    )
    ON DUPLICATE KEY UPDATE
        `current_status` = NEW.status,
        `attempts_count` = IF(NEW.status = 'ROJO', `attempts_count` + 1, `attempts_count`),
        `is_locked_for_summative` = IF(NEW.status = 'VERDE', 0, 1),
        `last_updated_at` = NOW();
END$$
DELIMITER ;
