-- SICA2025 - Sistema Integrado de Convivencia y Asistencia
-- Base de datos para el sistema SICA

CREATE DATABASE IF NOT EXISTS SICA2025 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE SICA2025;

-- ============================================
-- TABLA: anios_lectivos (años escolares)
-- ============================================
CREATE TABLE anios_lectivos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anio VARCHAR(9) NOT NULL, -- Formato: "2025-2026"
    estado ENUM('activo', 'inactivo', 'finalizado') DEFAULT 'activo',
    fecha_inicio DATE,
    fecha_fin DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Año lectivo por defecto
INSERT INTO anios_lectivos (anio, estado, fecha_inicio, fecha_fin)
VALUES ('2025-2026', 'activo', '2025-03-01', '2025-12-31');

-- ============================================
-- TABLA: niveles (inicial, primaria, secundaria)
-- ============================================
CREATE TABLE niveles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL, -- Inicial, Primaria, Secundaria
    estado ENUM('activo', 'inactivo') DEFAULT 'activo'
);

INSERT INTO niveles (nombre) VALUES ('Inicial'), ('Primaria'), ('Secundaria');

-- ============================================
-- TABLA: horarios (horarios por nivel)
-- ============================================
CREATE TABLE horarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    hora_ingreso TIME NOT NULL,
    hora_salida TIME NOT NULL,
    tolerancia_minutos INT DEFAULT 15,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id),
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id),
    UNIQUE KEY unique_horario (id_nivel, id_anio_lectivo)
);

-- ============================================
-- TABLA: secciones (grados y secciones por nivel)
-- ============================================
CREATE TABLE secciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    grado VARCHAR(20) NOT NULL, -- 1°, 2°, 3°, etc.
    seccion VARCHAR(10) NOT NULL, -- A, B, C, etc.
    capacidad INT DEFAULT 30,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id),
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id),
    UNIQUE KEY unique_seccion (id_nivel, id_anio_lectivo, grado, seccion)
);

-- ============================================
-- TABLA: apoderados
-- ============================================
CREATE TABLE apoderados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    dni VARCHAR(8) UNIQUE NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100),
    email VARCHAR(100) NOT NULL,
    telefono VARCHAR(20),
    direccion TEXT,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- ============================================
-- TABLA: docentes
-- ============================================
CREATE TABLE docentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    dni VARCHAR(8) UNIQUE NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100),
    email VARCHAR(100) NOT NULL,
    telefono VARCHAR(20),
    especialidad VARCHAR(100),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- ============================================
-- TABLA: auxiliares
-- ============================================
CREATE TABLE auxiliares (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    dni VARCHAR(8) UNIQUE NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100),
    email VARCHAR(100) NOT NULL,
    telefono VARCHAR(20),
    turno ENUM('mañana', 'tarde', 'completo') DEFAULT 'completo',
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- ============================================
-- TABLA: estudiantes
-- ============================================
CREATE TABLE estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT,
    id_apoderado INT,
    id_seccion INT,
    id_anio_lectivo INT NOT NULL,
    dni VARCHAR(8) UNIQUE NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100),
    fecha_nacimiento DATE,
    genero ENUM('M', 'F') NOT NULL,
    direccion TEXT,
    foto VARCHAR(255),
    codigo_qr VARCHAR(255),
    estado ENUM('activo', 'retirado', 'trasladado') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (id_apoderado) REFERENCES apoderados(id) ON DELETE SET NULL,
    FOREIGN KEY (id_seccion) REFERENCES secciones(id) ON DELETE SET NULL,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id)
);

-- ============================================
-- TABLA: asistencias (registro diario)
-- ============================================
CREATE TABLE asistencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    fecha DATE NOT NULL,
    hora_registro TIME NOT NULL,
    estado ENUM('asistente', 'tarde', 'falta', 'falta_justificada') NOT NULL,
    minutos_tardanza INT DEFAULT 0,
    registrado_por VARCHAR(50), -- usuario que registró
    metodo_registro ENUM('qr', 'manual', 'masivo') DEFAULT 'qr',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id),
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id),
    UNIQUE KEY unique_asistencia (id_estudiante, fecha)
);

-- ============================================
-- TABLA: tipos_incidencia (catálogo de faltas)
-- ============================================
CREATE TABLE tipos_incidencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    gravedad ENUM('leve', 'moderada', 'grave', 'muy_grave') DEFAULT 'moderada',
    puntos_penalidad INT DEFAULT 1,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo'
);

-- Tipos de incidencia por defecto
INSERT INTO tipos_incidencia (nombre, gravedad, puntos_penalidad) VALUES
('No llevó útiles escolares', 'leve', 1),
('No realizó tarea', 'leve', 1),
('Incumplimiento de uniforme', 'leve', 2),
('Disrupción en clase', 'moderada', 3),
('Falta de respeto a compañero', 'moderada', 4),
('Falta de respeto al docente', 'grave', 5),
('Agresión física', 'muy_grave', 10),
('Daño intencional a propiedad', 'grave', 7);

-- ============================================
-- TABLA: incidencias (inconductas registradas)
-- ============================================
CREATE TABLE incidencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_tipo_incidencia INT NOT NULL,
    id_docente INT,
    id_anio_lectivo INT NOT NULL,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    descripcion TEXT NOT NULL,
    accion_tomada TEXT,
    notificado_apoderado ENUM('si', 'no', 'pendiente') DEFAULT 'pendiente',
    estado ENUM('activo', 'resuelto', 'apelado') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id),
    FOREIGN KEY (id_tipo_incidencia) REFERENCES tipos_incidencia(id),
    FOREIGN KEY (id_docente) REFERENCES docentes(id),
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id)
);

-- ============================================
-- TABLA: notificaciones_email (registro de envíos)
-- ============================================
CREATE TABLE notificaciones_email (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_apoderado INT NOT NULL,
    tipo_notificacion ENUM('tardanza', 'incidencia', 'falta', 'general') NOT NULL,
    id_referencia INT, -- id de asistencia o incidencia
    asunto VARCHAR(200) NOT NULL,
    mensaje TEXT NOT NULL,
    email_enviado VARCHAR(100),
    estado_envio ENUM('enviado', 'pendiente', 'error') DEFAULT 'pendiente',
    fecha_envio DATETIME,
    intentos INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id),
    FOREIGN KEY (id_apoderado) REFERENCES apoderados(id)
);

-- ============================================
-- TABLA: configuracion (parámetros del sistema)
-- ============================================
CREATE TABLE configuracion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parametro VARCHAR(50) UNIQUE NOT NULL,
    valor TEXT,
    descripcion TEXT
);

-- Configuración por defecto
INSERT INTO configuracion (parametro, valor, descripcion) VALUES
('nombre_institucion', 'Institución Educativa', 'Nombre de la institución'),
('email_sistema', 'sica@institucion.edu', 'Correo del sistema'),
('max_faltas_riesgo', '15', 'Máximo de faltas para alerta de riesgo'),
('max_tardanzas_alerta', '10', 'Máximo de tardanzas para alerta'),
('email_smtp_host', 'smtp.gmail.com', 'Servidor SMTP'),
('email_smtp_port', '587', 'Puerto SMTP'),
('email_smtp_user', '', 'Usuario SMTP'),
('email_smtp_pass', '', 'Contraseña SMTP'),
('qr_base_url', 'http://localhost/SICA/', 'URL base para generación de QR');

-- ============================================
-- VISTAS ÚTILES
-- ============================================

-- Vista: Estudiantes con información completa
CREATE VIEW v_estudiantes_completo AS
SELECT
    e.id,
    e.dni,
    CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) AS nombre_completo,
    e.email,
    e.genero,
    n.nombre AS nivel,
    s.grado,
    s.seccion,
    a.dni AS dni_apoderado,
    CONCAT(a.apellido_paterno, ' ', a.apellido_materno, ', ', a.nombres) AS apoderado,
    a.email AS email_apoderado,
    e.estado
FROM estudiantes e
LEFT JOIN secciones s ON e.id_seccion = s.id
LEFT JOIN niveles n ON s.id_nivel = n.id
LEFT JOIN apoderados a ON e.id_apoderado = a.id
WHERE e.estado = 'activo';

-- Vista: Reporte de asistencias por estudiante
CREATE VIEW v_reporte_asistencias AS
SELECT
    e.id,
    CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) AS estudiante,
    n.nombre AS nivel,
    s.grado,
    s.seccion,
    COUNT(CASE WHEN a.estado = 'asistente' THEN 1 END) AS asistencias,
    COUNT(CASE WHEN a.estado = 'tarde' THEN 1 END) AS tardanzas,
    SUM(CASE WHEN a.estado = 'tarde' THEN a.minutos_tardanza ELSE 0 END) AS total_minutos_tardanza,
    COUNT(CASE WHEN a.estado = 'falta' THEN 1 END) AS faltas,
    COUNT(CASE WHEN a.estado = 'falta_justificada' THEN 1 END) AS faltas_justificadas
FROM estudiantes e
LEFT JOIN secciones s ON e.id_seccion = s.id
LEFT JOIN niveles n ON s.id_nivel = n.id
LEFT JOIN asistencias a ON e.id = a.id_estudiante
GROUP BY e.id, n.nombre, s.grado, s.seccion;
