-- =====================================================================
--  SISTEMA DE NOTAS · U.E. José Ballivián        (esquema v2)
--  MySQL 5.7+ / MariaDB 10.4+  (XAMPP, Laragon, WAMP)
--
--  PRINCIPIO DE DISEÑO: ACUMULATIVO POR GESTIÓN
--   · Lo que NO cambia cada año es global: usuarios, docentes, estudiantes,
--     catálogo de asignaturas y campos de saberes.
--   · Lo que SÍ depende del año cuelga de una gestión: trimestres, escala de
--     notas, plan de estudios, cursos, inscripciones, notas, asistencia,
--     citaciones, kárdex, horarios y comunicados.
--   · Al cerrar una gestión se congelan los resultados (resultados_area y
--     resultado_final) y queda en solo lectura. Nunca se borra nada.
-- =====================================================================
CREATE DATABASE IF NOT EXISTS notas_ballivian
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE notas_ballivian;

SET FOREIGN_KEY_CHECKS = 0;
DROP VIEW  IF EXISTS vw_historial_estudiante;
DROP TABLE IF EXISTS comunicado_lecturas, comunicados, horarios, kardex, citaciones,
  licencias, asistencia, resultados_area, notas, movimientos, inscripciones, estudiantes,
  carga_docente, cursos, plan_asignaturas, escala_notas, asignaturas, campos_saberes,
  trimestres, gestiones, docentes, auditoria, configuracion, usuarios;
SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
--  A. TABLAS GLOBALES (persisten entre gestiones)
-- =====================================================================

-- Acceso solo con CI. password_hash NULL => la clave es el propio CI.
CREATE TABLE usuarios (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ci                VARCHAR(15)  NOT NULL UNIQUE,
  apellidos         VARCHAR(80)  NOT NULL,
  nombres           VARCHAR(80)  NOT NULL,
  rol               ENUM('director','administrador','docente','regente','familia') NOT NULL,
  password_hash     VARCHAR(255) NULL,
  estado            ENUM('activa','bloqueada','inactiva') NOT NULL DEFAULT 'activa',
  intentos_fallidos TINYINT UNSIGNED NOT NULL DEFAULT 0,
  bloqueado_hasta   DATETIME NULL,
  ultimo_ingreso    DATETIME NULL,
  creado_en         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (rol), INDEX (estado)
) ENGINE=InnoDB;

CREATE TABLE docentes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  usuario_id  INT UNSIGNED NOT NULL UNIQUE,
  telefono    VARCHAR(20) NULL,
  activo      TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- El estudiante es único para toda la vida escolar; cada año se inscribe.
CREATE TABLE estudiantes (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  usuario_id     INT UNSIGNED NULL UNIQUE,      -- cuenta de la familia
  ci             VARCHAR(15) NOT NULL UNIQUE,
  rude           VARCHAR(20) NULL,
  apellidos      VARCHAR(80) NOT NULL,
  nombres        VARCHAR(80) NOT NULL,
  sexo           ENUM('M','F') NOT NULL,
  fecha_nac      DATE NULL,
  tutor_nombre   VARCHAR(100) NULL,
  tutor_telefono VARCHAR(20) NULL,
  salud          VARCHAR(150) NULL,
  creado_en      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (apellidos, nombres),
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Catálogo (se pueden agregar o desactivar libremente).
CREATE TABLE campos_saberes (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre  VARCHAR(80) NOT NULL UNIQUE,
  orden   TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE asignaturas (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campo_id             INT UNSIGNED NOT NULL,
  nombre               VARCHAR(100) NOT NULL UNIQUE,
  horas_defecto        TINYINT NOT NULL DEFAULT 2,
  en_boletin_defecto   TINYINT(1) NOT NULL DEFAULT 1,
  en_promedio_defecto  TINYINT(1) NOT NULL DEFAULT 1,
  activa               TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (campo_id) REFERENCES campos_saberes(id)
) ENGINE=InnoDB;

CREATE TABLE configuracion (
  clave  VARCHAR(60) PRIMARY KEY,
  valor  VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE auditoria (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  usuario_id  INT UNSIGNED NULL,
  ci          VARCHAR(15) NULL,
  evento      VARCHAR(60) NOT NULL,   -- login_ok, login_fallido, clave_restablecida, nota_editada, gestion_cerrada...
  detalle     VARCHAR(250) NULL,
  ip          VARCHAR(45) NULL,
  creado_en   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (evento), INDEX (creado_en), INDEX (usuario_id)
) ENGINE=InnoDB;

-- =====================================================================
--  B. TABLAS POR GESTIÓN
-- =====================================================================
CREATE TABLE gestiones (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  anio          SMALLINT NOT NULL UNIQUE,
  estado        ENUM('planificada','activa','cerrada') NOT NULL DEFAULT 'planificada',
  fecha_inicio  DATE NULL,
  fecha_fin     DATE NULL,
  cerrada_en    DATETIME NULL,
  cerrada_por   INT UNSIGNED NULL
) ENGINE=InnoDB;

CREATE TABLE trimestres (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gestion_id    INT UNSIGNED NOT NULL,
  numero        TINYINT NOT NULL,                 -- 1, 2, 3
  estado        ENUM('abierto','cerrado') NOT NULL DEFAULT 'cerrado',
  fecha_cierre  DATE NULL,
  UNIQUE (gestion_id, numero),
  FOREIGN KEY (gestion_id) REFERENCES gestiones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ED / DA / DO / DP configurable por gestión.
CREATE TABLE escala_notas (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gestion_id  INT UNSIGNED NOT NULL,
  letra       VARCHAR(3) NOT NULL,
  descripcion VARCHAR(60) NOT NULL,
  minimo      TINYINT UNSIGNED NOT NULL,
  maximo      TINYINT UNSIGNED NOT NULL,
  aprueba     TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE (gestion_id, letra),
  FOREIGN KEY (gestion_id) REFERENCES gestiones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Plan de estudios: qué materias lleva cada nivel en cada gestión.
-- Cambiar el plan en 2027 no altera los boletines de 2026.
CREATE TABLE plan_asignaturas (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gestion_id     INT UNSIGNED NOT NULL,
  nivel          TINYINT NOT NULL,                 -- 1..6
  asignatura_id  INT UNSIGNED NOT NULL,
  horas          TINYINT NOT NULL DEFAULT 2,
  en_boletin     TINYINT(1) NOT NULL DEFAULT 1,
  en_promedio    TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE (gestion_id, nivel, asignatura_id),
  FOREIGN KEY (gestion_id)    REFERENCES gestiones(id)   ON DELETE CASCADE,
  FOREIGN KEY (asignatura_id) REFERENCES asignaturas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cursos (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gestion_id  INT UNSIGNED NOT NULL,
  nivel       TINYINT NOT NULL,
  paralelo    CHAR(1) NOT NULL,
  turno       ENUM('mañana','tarde') NOT NULL DEFAULT 'mañana',
  asesor_id   INT UNSIGNED NULL,
  UNIQUE (gestion_id, nivel, paralelo),
  FOREIGN KEY (gestion_id) REFERENCES gestiones(id) ON DELETE CASCADE,
  FOREIGN KEY (asesor_id)  REFERENCES docentes(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE carga_docente (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  docente_id     INT UNSIGNED NOT NULL,
  asignatura_id  INT UNSIGNED NOT NULL,
  curso_id       INT UNSIGNED NOT NULL,
  UNIQUE (asignatura_id, curso_id),
  INDEX (docente_id),
  FOREIGN KEY (docente_id)    REFERENCES docentes(id)    ON DELETE CASCADE,
  FOREIGN KEY (asignatura_id) REFERENCES asignaturas(id) ON DELETE CASCADE,
  FOREIGN KEY (curso_id)      REFERENCES cursos(id)      ON DELETE CASCADE
) ENGINE=InnoDB;

-- Inscripción = estudiante + curso + gestión (vía curso). Es el eje del historial.
CREATE TABLE inscripciones (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estudiante_id    INT UNSIGNED NOT NULL,
  curso_id         INT UNSIGNED NOT NULL,
  nro_lista        SMALLINT NOT NULL DEFAULT 0,
  estado           ENUM('activa','retirado','trasladado') NOT NULL DEFAULT 'activa',
  resultado_final  ENUM('en_curso','promovido','retenido','retirado') NOT NULL DEFAULT 'en_curso',
  promedio_final   DECIMAL(5,2) NULL,
  UNIQUE (estudiante_id, curso_id),
  INDEX (curso_id, nro_lista),
  FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id) ON DELETE CASCADE,
  FOREIGN KEY (curso_id)      REFERENCES cursos(id)      ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE movimientos (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  tipo            ENUM('retiro','traslado','cambio_paralelo','reingreso') NOT NULL,
  detalle         VARCHAR(200) NULL,
  fecha           DATE NOT NULL,
  usuario_id      INT UNSIGNED NULL,
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Notas enteras 0-100. Letra, promedio y situación se calculan en PHP.
CREATE TABLE notas (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  asignatura_id   INT UNSIGNED NOT NULL,
  trimestre_id    INT UNSIGNED NOT NULL,
  nota            TINYINT UNSIGNED NULL,
  estado          ENUM('preliminar','aprobada') NOT NULL DEFAULT 'preliminar',
  actualizado_por INT UNSIGNED NULL,
  actualizado_en  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE (inscripcion_id, asignatura_id, trimestre_id),
  INDEX (asignatura_id, trimestre_id),
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE,
  FOREIGN KEY (asignatura_id)  REFERENCES asignaturas(id)   ON DELETE CASCADE,
  FOREIGN KEY (trimestre_id)   REFERENCES trimestres(id)    ON DELETE CASCADE
) ENGINE=InnoDB;

-- Foto congelada de los resultados al cerrar la gestión.
CREATE TABLE resultados_area (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  asignatura_id   INT UNSIGNED NOT NULL,
  nota_t1         TINYINT UNSIGNED NULL,
  nota_t2         TINYINT UNSIGNED NULL,
  nota_t3         TINYINT UNSIGNED NULL,
  promedio        DECIMAL(5,2) NOT NULL,
  promedio_red    TINYINT UNSIGNED NOT NULL,
  letra           VARCHAR(3) NOT NULL,
  situacion       ENUM('promovido','retenido') NOT NULL,
  UNIQUE (inscripcion_id, asignatura_id),
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE,
  FOREIGN KEY (asignatura_id)  REFERENCES asignaturas(id)   ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE asistencia (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  fecha           DATE NOT NULL,
  estado          ENUM('P','A','F','L') NOT NULL DEFAULT 'P',
  detalle         VARCHAR(120) NULL,
  registrado_por  INT UNSIGNED NULL,
  UNIQUE (inscripcion_id, fecha),
  INDEX (fecha),
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE licencias (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  desde           DATE NOT NULL,
  hasta           DATE NOT NULL,
  motivo          VARCHAR(150) NULL,
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE citaciones (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  motivo          VARCHAR(250) NOT NULL,
  emitida_por     INT UNSIGNED NOT NULL,
  origen          ENUM('regencia','asesoria','docente','direccion') NOT NULL DEFAULT 'regencia',
  fecha_cita      DATE NOT NULL,
  hora_cita       TIME NULL,
  estado          ENUM('pendiente','notificada','asistio','no_asistio') NOT NULL DEFAULT 'pendiente',
  leida           TINYINT(1) NOT NULL DEFAULT 0,
  leida_en        DATETIME NULL,
  creada_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (estado),
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE kardex (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inscripcion_id  INT UNSIGNED NOT NULL,
  tipo            ENUM('felicitacion','llamada_atencion') NOT NULL,
  descripcion     VARCHAR(250) NOT NULL,
  autor_id        INT UNSIGNED NULL,
  fecha           DATE NOT NULL,
  FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE horarios (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  curso_id       INT UNSIGNED NOT NULL,
  dia            TINYINT NOT NULL,                -- 1=Lunes ... 6=Sábado
  periodo        TINYINT NOT NULL,                -- 1..7
  asignatura_id  INT UNSIGNED NOT NULL,
  docente_id     INT UNSIGNED NOT NULL,
  publicado      TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE (curso_id, dia, periodo),
  INDEX (docente_id, dia, periodo),
  FOREIGN KEY (curso_id)      REFERENCES cursos(id)      ON DELETE CASCADE,
  FOREIGN KEY (asignatura_id) REFERENCES asignaturas(id) ON DELETE CASCADE,
  FOREIGN KEY (docente_id)    REFERENCES docentes(id)    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE comunicados (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gestion_id     INT UNSIGNED NOT NULL,
  titulo         VARCHAR(150) NOT NULL,
  mensaje        TEXT NOT NULL,
  destino        ENUM('colegio','nivel','curso','estudiante') NOT NULL DEFAULT 'colegio',
  nivel          TINYINT NULL,
  curso_id       INT UNSIGNED NULL,
  estudiante_id  INT UNSIGNED NULL,
  adjunto        VARCHAR(200) NULL,
  publicado_por  INT UNSIGNED NOT NULL,
  borrador       TINYINT(1) NOT NULL DEFAULT 0,
  creado_en      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (gestion_id),
  FOREIGN KEY (gestion_id) REFERENCES gestiones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE comunicado_lecturas (
  comunicado_id INT UNSIGNED NOT NULL,
  usuario_id    INT UNSIGNED NOT NULL,
  leido_en      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (comunicado_id, usuario_id),
  FOREIGN KEY (comunicado_id) REFERENCES comunicados(id) ON DELETE CASCADE,
  FOREIGN KEY (usuario_id)    REFERENCES usuarios(id)    ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
--  C. VISTA: historial de cada estudiante a lo largo de los años
-- =====================================================================
CREATE VIEW vw_historial_estudiante AS
SELECT e.id AS estudiante_id, e.ci, e.apellidos, e.nombres,
       g.anio, c.nivel, c.paralelo, i.id AS inscripcion_id,
       i.estado, i.resultado_final, i.promedio_final
FROM inscripciones i
JOIN estudiantes e ON e.id = i.estudiante_id
JOIN cursos c      ON c.id = i.curso_id
JOIN gestiones g   ON g.id = c.gestion_id;

-- =====================================================================
--  D. DATOS INICIALES
-- =====================================================================
INSERT INTO configuracion (clave, valor) VALUES
  ('colegio_nombre','U.E. José Ballivián'),
  ('colegio_sigla','JB'),
  ('nota_minima_aprobacion','51'),
  ('max_intentos_login','5'),
  ('minutos_bloqueo','10'),
  ('faltas_consecutivas_alerta','3');

INSERT INTO gestiones (anio, estado, fecha_inicio, fecha_fin)
VALUES (2026,'activa','2026-02-02','2026-12-04');

INSERT INTO trimestres (gestion_id, numero, estado, fecha_cierre) VALUES
  (1,1,'cerrado',NULL),(1,2,'cerrado',NULL),(1,3,'abierto','2026-10-17');

INSERT INTO escala_notas (gestion_id, letra, descripcion, minimo, maximo, aprueba) VALUES
  (1,'ED','En desarrollo',0,50,0),
  (1,'DA','Desarrollo aceptable',51,68,1),
  (1,'DO','Desarrollo óptimo',69,84,1),
  (1,'DP','Desarrollo pleno',85,100,1);

INSERT INTO campos_saberes (nombre, orden) VALUES
  ('Comunidad y Sociedad',1),
  ('Ciencia, Tecnología y Producción',2),
  ('Vida, Tierra y Territorio',3),
  ('Cosmos y Pensamiento',4);

INSERT INTO asignaturas (campo_id, nombre, horas_defecto, en_boletin_defecto, en_promedio_defecto) VALUES
  (1,'Comunicación y Lenguajes',4,1,1),
  (1,'Lengua Extranjera',2,1,1),
  (1,'Ciencias Sociales',3,1,1),
  (1,'Educación Física y Deportes',2,1,1),
  (2,'Matemática',5,1,1),
  (2,'Física',3,1,1),
  (2,'Química',3,1,1),
  (2,'Técnica Tecnológica General',3,1,1),
  (3,'Biología – Geografía',3,1,1),
  (4,'Cosmovisiones, Filosofía y Psicología',2,1,1),
  (4,'Valores, Espiritualidades y Religiones',2,1,1),
  (1,'Educación Musical',2,1,1),
  (2,'Computación',2,0,0);

-- Plan 2026: todas las materias en los 6 niveles (luego se ajusta desde el sistema).
INSERT INTO plan_asignaturas (gestion_id, nivel, asignatura_id, horas, en_boletin, en_promedio)
SELECT 1, n.nivel, a.id, a.horas_defecto, a.en_boletin_defecto, a.en_promedio_defecto
FROM asignaturas a
CROSS JOIN (SELECT 1 AS nivel UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) n;

-- Usuarios de prueba (clave inicial = CI)
INSERT INTO usuarios (ci, apellidos, nombres, rol) VALUES
  ('1000001','Huanca','Sergio','director'),
  ('1000002','Villca','Ana','administrador'),
  ('1000003','Rodríguez','Marcos','docente'),
  ('1000004','Choque','Rosa','regente'),
  ('8123406','Mamani Quispe','María Fernanda','familia');

INSERT INTO docentes (usuario_id) VALUES (3);
INSERT INTO cursos (gestion_id, nivel, paralelo, turno, asesor_id) VALUES (1,4,'B','mañana',1);
INSERT INTO estudiantes (usuario_id, ci, rude, apellidos, nombres, sexo, tutor_nombre, tutor_telefono)
VALUES (5,'8123406','8123406','Mamani Quispe','María Fernanda','F','Carmen Quispe','721 66 308');
INSERT INTO inscripciones (estudiante_id, curso_id, nro_lista) VALUES (1,1,1);
INSERT INTO carga_docente (docente_id, asignatura_id, curso_id) VALUES (1,5,1);