-- ============================================================
-- Vista: vista_cruce_asignacion_contratados
-- Cruce bilateral entre docentes contratados y asignaciones
-- Campos de contratación: Nombre, NoDocumento, Cargo,
--   TipoContrato, FechaInicio, FechaFin, Escuela (contrato)
-- Campos de asignación: PeriodoNombre, Parte,
--   CodigoAsignatura, NombreAsignatura, Escuela (asig), Crn
-- ============================================================

DROP VIEW IF EXISTS vista_cruce_asignacion_contratados;

CREATE VIEW vista_cruce_asignacion_contratados AS

-- 1) Contratados SIN asignación
SELECT
    c.NoDocumento,
    c.Nombre,
    c.Cargo,
    c.TipoContrato,
    c.FechaInicio,
    c.FechaFin,
    IFNULL(sc.Escuela, '') AS EscuelaContratacion,
    '' AS PeriodoNombre,
    '' AS Parte,
    '' AS CodigoAsignatura,
    '' AS NombreAsignatura,
    '' AS EscuelaAsignacion,
    '' AS Crn,
    'CONTRATADO_SIN_ASIGNACION' AS EstadoCruce
FROM colaboradores c
LEFT JOIN solicitud_contratos sc
    ON sc.NoDocumento = c.NoDocumento
    AND sc.EstadoProceso = 6
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

UNION ALL

-- 2) Asignados SIN contrato (una fila por CRN)
SELECT
    vph.Docente AS NoDocumento,
    vph.DocenteNombre AS Nombre,
    '' AS Cargo,
    '' AS TipoContrato,
    '' AS FechaInicio,
    '' AS FechaFin,
    '' AS EscuelaContratacion,
    vph.PeriodoNombre,
    vph.Parte,
    vph.CodigoAsignatura,
    vph.NombreAsignatura,
    vph.Escuela AS EscuelaAsignacion,
    vph.Crn,
    'ASIGNADO_SIN_CONTRATO' AS EstadoCruce
FROM vista_programacion_horarios vph
LEFT JOIN colaboradores c2
    ON c2.NoDocumento = vph.Docente
    AND c2.Estado = 1
LEFT JOIN solicitud_contratos sc2
    ON sc2.NoDocumento = vph.Docente
    AND sc2.EstadoProceso = 6
    AND sc2.FechaFinReal >= CURDATE()
WHERE vph.FechaFin >= CURDATE()
  AND vph.Docente IS NOT NULL
  AND vph.Docente != ''
  AND c2.Id IS NULL
  AND sc2.Id IS NULL
GROUP BY vph.Docente, vph.Crn;
