-- ============================================
-- MIGRACIÓN: Actualización de tabla asistencias
-- Fecha: 2025-12-28
-- Descripción: Integrar asistencias con programaciones y estados_asistencia
-- ============================================

-- NOTA: Ejecutar primero migracion_estados_asistencia.sql

-- ============================================
-- CAMBIOS EN LA TABLA asistencias
-- ============================================

-- 1. Agregar campo id_estado_asistencia (FK)
ALTER TABLE asistencias ADD COLUMN id_estado_asistencia INT NULL AFTER id_anio_lectivo;

-- 2. Agregar campo id_programacion (FK)
ALTER TABLE asistencias ADD COLUMN id_programacion INT NULL AFTER id_estado_asistencia;

-- 3. Agregar campo id_turno (FK)
ALTER TABLE asistencias ADD COLUMN id_turno INT NULL AFTER id_programacion;

-- 4. Modificar campo estado para que sea VARCHAR (ya no ENUM)
-- Primero creamos un nuevo campo temporal
ALTER TABLE asistencias ADD COLUMN estado_nuevo VARCHAR(50) NULL AFTER minutos_tardanza;

-- 5. Migrar datos del ENUM al nuevo campo y a estados_asistencia
-- Actualizar estado_nuevo basado en el ENUM actual
UPDATE asistencias SET estado_nuevo =
    CASE estado
        WHEN 'asistente' THEN 'Asistencia'
        WHEN 'tarde' THEN 'Tardanza'
        WHEN 'falta' THEN 'Falta Injustificada'
        WHEN 'falta_justificada' THEN 'Falta Justificada'
        ELSE 'Asistencia'
    END;

-- 6. Crear relación con estados_asistencia basándose en el estado
UPDATE asistencias a
INNER JOIN estados_asistencia ea ON a.estado_nuevo = ea.nombre
SET a.id_estado_asistencia = ea.id;

-- 7. Crear relaciones con programaciones y turnos
-- Buscar la programación activa del estudiante para el año lectivo
UPDATE asistencias a
INNER JOIN programaciones_estudiantes pe ON a.id_estudiante = pe.id_estudiante AND a.id_anio_lectivo = pe.id_anio_lectivo
SET a.id_programacion = pe.id_programacion
WHERE pe.estado = 'activo';

-- Obtener el turno de la programación
UPDATE asistencias a
INNER JOIN programaciones p ON a.id_programacion = p.id
SET a.id_turno = p.id_turno
WHERE a.id_programacion IS NOT NULL;

-- 8. Agregar foreign keys
ALTER TABLE asistencias
    ADD CONSTRAINT fk_asistencias_estado FOREIGN KEY (id_estado_asistencia) REFERENCES estados_asistencia(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    ADD CONSTRAINT fk_asistencias_programacion FOREIGN KEY (id_programacion) REFERENCES programaciones(id) ON DELETE SET NULL ON UPDATE CASCADE,
    ADD CONSTRAINT fk_asistencias_turno FOREIGN KEY (id_turno) REFERENCES turnos(id) ON DELETE SET NULL ON UPDATE CASCADE;

-- 9. Hacer los campos NOT NULL después de la migración
ALTER TABLE asistencias
    MODIFY COLUMN id_estado_asistencia INT NOT NULL,
    MODIFY COLUMN id_programacion INT NOT NULL,
    MODIFY COLUMN id_turno INT NOT NULL;

-- 10. Eliminar el viejo campo ENUM
ALTER TABLE asistencias DROP COLUMN estado;

-- 11. Renombrar el campo nuevo
ALTER TABLE asistencias CHANGE COLUMN estado_nuevo estado_codigo VARCHAR(50);

-- 12. Actualizar el índice único para incluir solo id_estudiante y fecha
-- (ya existe, no necesita cambios)

-- 13. Crear índices para mejor rendimiento
CREATE INDEX idx_asistencias_programacion ON asistencias(id_programacion);
CREATE INDEX idx_asistencias_turno ON asistencias(id_turno);
CREATE INDEX idx_asistencias_estado ON asistencias(id_estado_asistencia);
CREATE INDEX idx_asistencias_fecha ON asistencias(fecha);

-- ============================================
-- 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,
    p.id_seccion,
    s.seccion,
    g.grado,
    n.nombre AS nivel,
    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.descripcion AS estado_descripcion,
    ea.color AS estado_color,
    ea.icono AS estado_icono,
    ea.conteo_asistencia,
    ea.permite_tardanza,
    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 grados g ON s.id_grado = g.id
INNER JOIN niveles n ON g.id_nivel = n.id
INNER JOIN turnos t ON a.id_turno = t.id
INNER JOIN estados_asistencia ea ON a.id_estado_asistencia = ea.id;

-- ============================================
-- NOTAS IMPORTANTES
-- ============================================
--
-- CAMBIOS REALIZADOS:
-- 1. Se agregó id_estado_asistencia (FK a estados_asistencia)
-- 2. Se agregó id_programacion (FK a programaciones)
-- 3. Se agregó id_turno (FK a turnos)
-- 4. El campo 'estado' cambió de ENUM a VARCHAR (estado_codigo)
-- 5. Se creó vista v_asistencias_detalle con toda la información relacionada
--
-- RELACIONES:
-- - Una asistencia pertenece a un estado (Asistencia, Tardanza, etc.)
-- - Una asistencia está vinculada a una programación (año lectivo + sección)
-- - Una asistencia tiene un turno (Mañana, Tarde)
--
-- VENTAJAS:
-- - Información completa del contexto educativo en cada asistencia
-- - Flexibilidad para agregar nuevos estados sin modificar estructura
-- - Mejor rendimiento con índices apropiados
-- - Vista simplificada para consultas frecuentes
--
-- ============================================
