-- ============================================
-- SICA - SISTEMA INTEGRADO DE CONVIVENCIA Y ASISTENCIA
-- Script Completo de Restauración de Base de Datos
-- Fecha: 2025-12-28
-- ============================================

-- Eliminar base de datos si existe y crearla nueva
DROP DATABASE IF EXISTS sica2025;
CREATE DATABASE sica2025 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sica2025;

-- ============================================
-- TABLAS PRINCIPALES DEL SISTEMA
-- ============================================

-- 1. TABLA: usuarios
CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    rol ENUM('admin', 'docente', 'auxiliar', 'estudiante', 'apoderado') NOT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    ultimo_acceso DATETIME,
    INDEX idx_usuario (usuario),
    INDEX idx_rol (rol),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. TABLA: anios_lectivos
CREATE TABLE anios_lectivos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anio VARCHAR(20) NOT NULL UNIQUE,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    estado ENUM('activo', 'inactivo', 'finalizado') DEFAULT 'inactivo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_anio (anio),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. TABLA: niveles
CREATE TABLE niveles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. TABLA: turnos
CREATE TABLE turnos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    abreviatura VARCHAR(20) NOT NULL UNIQUE,
    hora_ingreso TIME NOT NULL,
    hora_salida TIME NOT NULL,
    tolerancia_minutos INT DEFAULT 15,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. TABLA: grados
CREATE TABLE grados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    grado VARCHAR(20) NOT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_nivel_anio_grado (id_nivel, id_anio_lectivo, grado),
    INDEX idx_nivel (id_nivel),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. TABLA: secciones
CREATE TABLE secciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_grado INT NOT NULL,
    id_turno INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    seccion VARCHAR(10) NOT NULL,
    grado VARCHAR(20) NOT NULL COMMENT 'Nombre del grado (redundante para facilitar búsquedas)',
    capacidad INT DEFAULT 40,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id) ON DELETE CASCADE,
    FOREIGN KEY (id_grado) REFERENCES grados(id) ON DELETE CASCADE,
    FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_nivel_grado_turno_seccion_anio (id_nivel, id_grado, id_turno, seccion, id_anio_lectivo),
    INDEX idx_nivel (id_nivel),
    INDEX idx_turno (id_turno),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. TABLA: areas_academicas
CREATE TABLE areas_academicas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    area VARCHAR(100) NOT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_nivel_anio_area (id_nivel, id_anio_lectivo, area),
    INDEX idx_nivel (id_nivel),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. TABLA: tipos_incidencia
CREATE TABLE tipos_incidencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo VARCHAR(100) NOT NULL UNIQUE,
    descripcion TEXT,
    gravedad ENUM('baja', 'media', 'alta', 'muy_alta') DEFAULT 'media',
    puntos_penalidad INT DEFAULT 0 COMMENT 'Puntos de penalización por este tipo de incidencia',
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tipo (tipo),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. TABLA: estados_asistencia
CREATE TABLE estados_asistencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    abreviatura VARCHAR(10) NOT NULL UNIQUE,
    descripcion TEXT,
    color VARCHAR(20) DEFAULT '#6c757d',
    icono VARCHAR(50) DEFAULT 'circle-fill',
    conteo_asistencia ENUM('si', 'no') DEFAULT 'si',
    permite_tardanza ENUM('si', 'no') DEFAULT 'no',
    requiere_justificacion ENUM('si', 'no') DEFAULT 'no',
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_nombre (nombre),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. TABLA: calendario
CREATE TABLE calendario (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fecha DATE NOT NULL UNIQUE,
    tipo ENUM('feriado', 'vacacion', 'dia_no_laboral', 'fin_semana_largo') NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    aniolectivo_id INT NULL,
    afecta_todos_niveles BOOLEAN DEFAULT TRUE,
    niveles_afectados JSON NULL,
    observaciones TEXT,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (aniolectivo_id) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    INDEX idx_fecha (fecha),
    INDEX idx_tipo (tipo),
    INDEX idx_aniolectivo (aniolectivo_id),
    INDEX idx_estado (estado),
    INDEX idx_unique_fecha (fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. TABLA: configuracion
CREATE TABLE configuracion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parametro VARCHAR(100) NOT NULL UNIQUE,
    valor TEXT,
    descripcion TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parametro (parametro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. TABLA: estudiantes
CREATE TABLE estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NULL,
    dni VARCHAR(8) NOT NULL UNIQUE,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    fecha_nacimiento DATE NOT NULL,
    genero ENUM('M', 'F') NOT NULL,
    telefono VARCHAR(20),
    direccion TEXT,
    estado ENUM('activo', 'inactivo', 'retirado', 'trasladado') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_apellidos (apellido_paterno, apellido_materno),
    INDEX idx_nombres (nombres),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. TABLA: docentes
CREATE TABLE docentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NULL,
    dni VARCHAR(8) NOT NULL UNIQUE,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    telefono VARCHAR(20),
    direccion TEXT,
    fecha_nacimiento DATE,
    genero ENUM('M', 'F'),
    especialidad VARCHAR(100),
    estado ENUM('activo', 'inactivo', 'retirado') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_apellidos (apellido_paterno, apellido_materno),
    INDEX idx_nombres (nombres),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. TABLA: auxiliares
CREATE TABLE auxiliares (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NULL,
    dni VARCHAR(8) NOT NULL UNIQUE,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    telefono VARCHAR(20),
    direccion TEXT,
    fecha_nacimiento DATE,
    genero ENUM('M', 'F'),
    id_turno INT NULL COMMENT 'Turno asignado (opcional)',
    estado ENUM('activo', 'inactivo', 'retirado') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_apellidos (apellido_paterno, apellido_materno),
    INDEX idx_nombres (nombres),
    INDEX idx_turno (id_turno),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. TABLA: apoderados
CREATE TABLE apoderados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NULL,
    dni VARCHAR(8) NOT NULL UNIQUE,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    nombres VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    telefono VARCHAR(20) NOT NULL,
    telefono_alternativo VARCHAR(20),
    direccion TEXT,
    fecha_nacimiento DATE,
    genero ENUM('M', 'F'),
    estado ENUM('activo', 'inactivo', 'retirado') DEFAULT 'activo',
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_usuario) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_apellidos (apellido_paterno, apellido_materno),
    INDEX idx_nombres (nombres),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. TABLA: estudiantes_apoderados (Vinculación estudiantes-apoderados)
CREATE TABLE estudiantes_apoderados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_apoderado INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    parentesco VARCHAR(50) DEFAULT 'Padre/Madre',
    es_principal BOOLEAN DEFAULT FALSE,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_apoderado) REFERENCES apoderados(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_estudiante_anio (id_estudiante, id_anio_lectivo),
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_apoderado (id_apoderado),
    INDEX idx_anio_lectivo (id_anio_lectivo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. TABLA: docente_areas (Relación docentes-áreas académicas)
CREATE TABLE docente_areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_docente INT NOT NULL,
    id_area_academica INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURRENT_DATE),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_docente) REFERENCES docentes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_area_academica) REFERENCES areas_academicas(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_docente_area_anio (id_docente, id_area_academica, id_anio_lectivo),
    INDEX idx_docente (id_docente),
    INDEX idx_area_academica (id_area_academica),
    INDEX idx_anio_lectivo (id_anio_lectivo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. TABLA: programaciones
CREATE TABLE programaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_seccion INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    id_tutor INT NULL,
    id_auxiliar INT NULL,
    id_turno INT NOT NULL,
    capacidad INT DEFAULT 40,
    estado ENUM('activo', 'inactivo', 'finalizado') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_seccion) REFERENCES secciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_tutor) REFERENCES docentes(id) ON DELETE SET NULL,
    FOREIGN KEY (id_auxiliar) REFERENCES auxiliares(id) ON DELETE SET NULL,
    FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_seccion_anio (id_seccion, id_anio_lectivo),
    INDEX idx_seccion (id_seccion),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_tutor (id_tutor),
    INDEX idx_auxiliar (id_auxiliar),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. TABLA: programaciones_estudiantes
CREATE TABLE programaciones_estudiantes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_programacion INT NOT NULL,
    id_estudiante INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURRENT_DATE),
    estado ENUM('activo', 'trasladado', 'retirado') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_programacion_estudiante (id_programacion, id_estudiante),
    INDEX idx_programacion (id_programacion),
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. TABLA: programaciones_docentes_areas
CREATE TABLE programaciones_docentes_areas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_programacion INT NOT NULL,
    id_docente INT NOT NULL,
    id_area_academica INT NOT NULL,
    fecha_asignacion DATE DEFAULT (CURRENT_DATE),
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_docente) REFERENCES docentes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_area_academica) REFERENCES areas_academicas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_programacion_docente_area (id_programacion, id_docente, id_area_academica),
    INDEX idx_programacion (id_programacion),
    INDEX idx_docente (id_docente),
    INDEX idx_area_academica (id_area_academica)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. TABLA: horarios
CREATE TABLE horarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_nivel INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    id_turno INT NOT NULL,
    estado ENUM('activo', 'inactivo') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_nivel) REFERENCES niveles(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_nivel_anio_turno (id_nivel, id_anio_lectivo, id_turno),
    INDEX idx_nivel (id_nivel),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_turno (id_turno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. TABLA: asistencias
CREATE TABLE asistencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    id_programacion INT NOT NULL,
    id_turno INT NOT NULL,
    id_estado_asistencia INT NOT NULL,
    fecha DATE NOT NULL,
    hora_registro TIME NOT NULL,
    estado_codigo VARCHAR(50) NOT NULL,
    minutos_tardanza INT DEFAULT 0,
    registrado_por VARCHAR(50) NOT NULL,
    metodo_registro ENUM('qr', 'manual', 'automatico') DEFAULT 'manual',
    observaciones TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_estado_asistencia) REFERENCES estados_asistencia(id) ON DELETE RESTRICT,
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_fecha (fecha),
    INDEX idx_programacion (id_programacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 23. TABLA: incidencias
CREATE TABLE incidencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    id_tipo_incidencia INT NOT NULL,
    id_docente INT NOT NULL,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    descripcion TEXT NOT NULL,
    estado ENUM('activo', 'resuelto', 'archivado') DEFAULT 'activo',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_tipo_incidencia) REFERENCES tipos_incidencia(id) ON DELETE RESTRICT,
    FOREIGN KEY (id_docente) REFERENCES docentes(id) ON DELETE CASCADE,
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_anio_lectivo (id_anio_lectivo),
    INDEX idx_fecha (fecha),
    INDEX idx_tipo (id_tipo_incidencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 24. TABLA: notificaciones
CREATE TABLE notificaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_apoderado INT NOT NULL,
    id_estudiante INT NOT NULL,
    id_anio_lectivo INT NOT NULL,
    tipo ENUM('inasistencia', 'tardanza', 'falta_repetida', 'alerta') NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    mensaje TEXT NOT NULL,
    fecha_notificacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    leida BOOLEAN DEFAULT FALSE,
    fecha_lectura DATETIME NULL,
    email_enviado BOOLEAN DEFAULT FALSE,
    fecha_email DATETIME NULL,
    estado ENUM('activo', 'archivado') DEFAULT 'activo',
    FOREIGN KEY (id_apoderado) REFERENCES apoderados(id) ON DELETE CASCADE,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (id_anio_lectivo) REFERENCES anios_lectivos(id) ON DELETE CASCADE,
    INDEX idx_apoderado (id_apoderado),
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_leida (leida),
    INDEX idx_fecha (fecha_notificacion),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 25. TABLA: notificaciones_email
CREATE TABLE notificaciones_email (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_apoderado INT NOT NULL,
    id_estudiante INT NOT NULL,
    id_referencia INT NULL COMMENT 'ID de referencia (asistencia, incidencia, etc.)',
    tipo_notificacion ENUM('inasistencia', 'tardanza', 'falta_repetida', 'alerta', 'incidencia') NOT NULL,
    asunto VARCHAR(255) NOT NULL,
    mensaje TEXT NOT NULL,
    estado_envio ENUM('pendiente', 'enviado', 'error') DEFAULT 'pendiente',
    intentos INT DEFAULT 0 COMMENT 'Número de intentos de envío',
    email_enviado VARCHAR(255) NULL COMMENT 'Email al que se envió',
    fecha_envio DATETIME NULL COMMENT 'Fecha y hora de envío exitoso',
    error_mensaje TEXT NULL COMMENT 'Mensaje de error si falló el envío',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_apoderado) REFERENCES apoderados(id) ON DELETE CASCADE,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id) ON DELETE CASCADE,
    INDEX idx_apoderado (id_apoderado),
    INDEX idx_estudiante (id_estudiante),
    INDEX idx_estado_envio (estado_envio),
    INDEX idx_tipo (tipo_notificacion),
    INDEX idx_fecha_envio (fecha_envio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- VISTAS
-- ============================================

-- VISTA: v_programaciones_resumen
CREATE OR REPLACE VIEW v_programaciones_resumen AS
SELECT
    p.id,
    p.id_seccion,
    p.id_anio_lectivo,
    an.anio as anio_lectivo,
    p.id_tutor,
    CONCAT(d.apellido_paterno, ' ', d.apellido_materno, ', ', d.nombres) as tutor_nombre,
    p.id_auxiliar,
    CONCAT(a.apellido_paterno, ' ', a.apellido_materno, ', ', a.nombres) as auxiliar_nombre,
    p.id_turno,
    t.nombre as turno_nombre,
    t.abreviatura as turno_abreviatura,
    t.hora_ingreso,
    t.hora_salida,
    t.tolerancia_minutos,
    s.id_nivel,
    n.nombre as nivel_nombre,
    s.id_grado,
    g.grado,
    s.seccion,
    p.capacidad,
    p.estado,
    COUNT(DISTINCT pe.id_estudiante) as total_estudiantes,
    COUNT(DISTINCT pda.id) as total_areas
FROM programaciones p
LEFT JOIN anios_lectivos an ON p.id_anio_lectivo = an.id
LEFT JOIN docentes d ON p.id_tutor = d.id
LEFT JOIN auxiliares a ON p.id_auxiliar = a.id
LEFT JOIN turnos t ON p.id_turno = t.id
LEFT JOIN secciones s ON p.id_seccion = s.id
LEFT JOIN niveles n ON s.id_nivel = n.id
LEFT JOIN grados g ON s.id_grado = g.id
LEFT JOIN programaciones_estudiantes pe ON p.id = pe.id_programacion AND pe.estado = 'activo'
LEFT JOIN programaciones_docentes_areas pda ON p.id = pda.id_programacion AND pda.estado = 'activo'
GROUP BY p.id;

-- VISTA: v_estudiantes_programacion
CREATE OR REPLACE VIEW v_estudiantes_programacion AS
SELECT
    e.id as estudiante_id,
    e.dni,
    CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) as estudiante_nombre,
    e.genero,
    pe.id_programacion,
    s.id_nivel,
    n.nombre as nivel,
    g.grado,
    s.seccion,
    t.nombre as turno,
    t.abreviatura as turno_abreviatura,
    p.id_anio_lectivo,
    an.anio
FROM estudiantes e
INNER JOIN programaciones_estudiantes pe ON e.id = pe.id_estudiante
INNER JOIN programaciones p ON pe.id_programacion = p.id
INNER JOIN secciones s ON p.id_seccion = s.id
INNER JOIN niveles n ON s.id_nivel = n.id
INNER JOIN grados g ON s.id_grado = g.id
INNER JOIN turnos t ON p.id_turno = t.id
INNER JOIN anios_lectivos an ON p.id_anio_lectivo = an.id
WHERE pe.estado = 'activo' AND e.estado = 'activo';

-- VISTA: v_calendario_detalle
CREATE OR REPLACE VIEW v_calendario_detalle AS
SELECT
    c.id,
    c.fecha,
    DAYNAME(c.fecha) as dia_semana,
    DAY(c.fecha) as dia,
    MONTH(c.fecha) as mes,
    YEAR(c.fecha) as anio,
    c.tipo,
    c.nombre,
    c.descripcion,
    c.aniolectivo_id,
    an.anio as anio_lectivo,
    c.afecta_todos_niveles,
    c.niveles_afectados,
    c.observaciones,
    c.estado,
    CASE
        WHEN DAYOFWEEK(c.fecha) IN (1, 7) THEN 1
        ELSE 0
    END as es_fin_de_semana
FROM calendario c
LEFT JOIN anios_lectivos an ON c.aniolectivo_id = an.id;

-- VISTA: v_asistencias_detalle
CREATE OR REPLACE VIEW v_asistencias_detalle AS
SELECT
    a.id,
    a.id_estudiante,
    e.dni,
    CONCAT(e.apellido_paterno, ' ', e.apellido_materno, ', ', e.nombres) as estudiante_nombre,
    e.genero,
    a.id_anio_lectivo,
    an.anio as anio_lectivo,
    a.id_programacion,
    s.id_nivel,
    n.nombre as nivel_nombre,
    g.grado,
    s.seccion,
    a.id_turno,
    t.nombre as turno_nombre,
    t.abreviatura as turno_abreviatura,
    t.hora_ingreso,
    t.hora_salida,
    t.tolerancia_minutos,
    a.id_estado_asistencia,
    ea.nombre as estado_nombre,
    ea.abreviatura as estado_abreviatura,
    ea.color as estado_color,
    ea.icono as estado_icono,
    a.fecha,
    a.hora_registro,
    a.estado_codigo,
    a.minutos_tardanza,
    a.registrado_por,
    a.metodo_registro,
    a.observaciones,
    a.created_at
FROM asistencias a
INNER JOIN estudiantes e ON a.id_estudiante = e.id
INNER JOIN anios_lectivos an ON a.id_anio_lectivo = an.id
INNER JOIN programaciones p ON a.id_programacion = p.id
INNER JOIN secciones s ON p.id_seccion = s.id
INNER JOIN niveles n ON s.id_nivel = n.id
INNER JOIN grados g ON s.id_grado = g.id
INNER JOIN turnos t ON a.id_turno = t.id
INNER JOIN estados_asistencia ea ON a.id_estado_asistencia = ea.id;

-- ============================================
-- FIN DEL SCRIPT DE RESTAURACIÓN (ESTRUCTURA)
-- ============================================
-- NOTA: Los datos se insertan con el script 'datos_basicos_completos.sql'

