-- ============================================================
-- Vistas SQL para reportes de cruce asignación vs contratados
-- Divididas en 3 vistas independientes para rendimiento óptimo
-- ============================================================

-- ============================================================
-- 1. vista_contratados_sin_asignacion
--    Docentes con contrato activo sin asignación académica vigente
-- ============================================================
DROP VIEW IF EXISTS vista_contratados_sin_asignacion;

CREATE VIEW vista_contratados_sin_asignacion AS
SELECT
    c.NoDocumento,
    c.Nombre,
    c.Cargo,
    c.TipoContrato,
    c.FechaInicio,
    c.FechaFin,
    IFNULL(c.Area, '') AS EscuelaContratacion
FROM colaboradores c
LEFT JOIN (
    SELECT DISTINCT ph.Docente
    FROM programacion_horarios ph
    INNER JOIN programacion p ON p.Id = ph.IdProgramacion
    WHERE p.EstadoGrupo != 'Cancelado'
      AND ph.FechaFin >= CURDATE()
) asig ON asig.Docente = c.NoDocumento
WHERE c.Estado = 1
  AND c.Rol = 'Academico'
  AND asig.Docente IS NULL;


-- ============================================================
-- 2. vista_asignados_sin_contrato
--    Docentes asignados académicamente sin contrato ni solicitud firmada
-- ============================================================
DROP VIEW IF EXISTS vista_asignados_sin_contrato;

CREATE VIEW vista_asignados_sin_contrato AS
SELECT
    vph.Docente      AS NoDocumento,
    vph.DocenteNombre AS Nombre,
    vph.PeriodoNombre,
    vph.Parte,
    vph.CodigoAsignatura,
    vph.NombreAsignatura,
    vph.Escuela       AS EscuelaAsignacion,
    vph.Crn
FROM vista_programacion_horarios vph
LEFT JOIN colaboradores c
    ON c.NoDocumento = vph.Docente
    AND c.Estado = 1
LEFT JOIN solicitud_contratos sc
    ON sc.NoDocumento = vph.Docente
    AND sc.EstadoProceso = 6
    AND sc.FechaFinReal >= CURDATE()
WHERE vph.FechaFin >= CURDATE()
  AND vph.Docente IS NOT NULL
  AND vph.Docente != ''
  AND c.Id IS NULL
  AND sc.Id IS NULL
GROUP BY vph.Docente, vph.Crn;


-- ============================================================
-- 3. vista_asignados_con_solicitud
--    Docentes asignados que tienen solicitud de contrato (no firmada)
--    con detalle de cada NRC y estado de la solicitud
-- ============================================================
DROP VIEW IF EXISTS vista_asignados_con_solicitud;

CREATE VIEW vista_asignados_con_solicitud AS
SELECT
    vph.Docente        AS NoDocumento,
    vph.DocenteNombre  AS Nombre,
    sc.Cargo,
    sc.TipoContrato,
    sc.FechaInicioReal AS FechaInicio,
    sc.FechaFinReal    AS FechaFin,
    IFNULL(sc.Escuela, '') AS EscuelaSolicitud,
    sc.EstadoProceso,
    vph.PeriodoNombre,
    vph.Parte,
    vph.CodigoAsignatura,
    vph.NombreAsignatura,
    vph.Escuela        AS EscuelaAsignacion,
    vph.Crn
FROM vista_programacion_horarios vph
INNER JOIN (
    SELECT s1.NoDocumento, s1.Id, s1.Cargo, s1.TipoContrato,
           s1.FechaInicioReal, s1.FechaFinReal, s1.Escuela, s1.EstadoProceso
    FROM solicitud_contratos s1
    INNER JOIN (
        SELECT NoDocumento, MAX(Id) AS MaxId
        FROM solicitud_contratos
        WHERE FechaFinReal >= CURDATE()
          AND EstadoProceso != 6
        GROUP BY NoDocumento
    ) s2 ON s1.Id = s2.MaxId
) sc ON sc.NoDocumento = vph.Docente
WHERE vph.FechaFin >= CURDATE()
  AND vph.Docente IS NOT NULL
  AND vph.Docente != ''
GROUP BY vph.Docente, vph.Crn;
