-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- View: vw_regularidades_basica
-- Version: 3.0.0
-- Recupera las cursadas y equivalencias de regularidad (Aprobadas/Reprobadas).
-- Cursadas / Equivalencias de Regularidad / Otros Reconocimientos de regularidad. /Promociones (solo cuando la comision no tiene instancia de regularidad)
-- 
-- Valores de los campos:
--    origen (cursadas)       : R = Acta de Regularidad / P = Acta de Promoción (son alumnos que no estan en acta de cursadas de esa comision)  / C = Equivalencia de Regularidad
--    alcance (equivalencias) : Equivalencia de Regularidad (Solo recupera estas)
--    tipo                    : Regularidad / Promoción / Equivalencia de Regularidad  (No se consideran las Equivalencias Parciales)
--    resultado               : A - Aprobado / R - Desaprobado / U - Ausente
--    tipo_acta y tipo_tramite: N - Normal / R - Rectificativo
--    vigente                 : 1 - Es Vigente / 0 - No esta vigente
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- DROP VIEW vw_regularidades_basica;
CREATE OR REPLACE VIEW vw_regularidades_basica
(
 elemento,
 id_acta,
 id_acta_original,
 tipo_acta,
 origen,
 comision,
 equivalencia_tramite,
 equivalencia_tramite_original,
 tipo_tramite,
 equivalencia,
 persona,
 alumno,
 plan_version,
 fecha,
 fecha_vigencia,
 es_vigente,
 escala_nota,
 nota,
 resultado
 )
AS 
 -- 1. Actas de Cursadas
 SELECT 
    sga_comisiones.elemento,
    sga_actas.id_acta,
    CASE sga_actas.tipo_acta
      WHEN 'N' THEN sga_actas.id_acta
      WHEN 'R' THEN sga_actas.acta_referencia
    END as id_acta_original,
    sga_actas.tipo_acta,
    sga_actas.origen,
    sga_actas.comision,
    cast(null as integer) as equivalencia_tramite,
    cast(null as integer) as equivalencia_tramite_original,
    cast(null as char(1)) as tipo_tramite,
    cast(null as integer) as equivalencia,
    sga_alumnos.persona,
    sga_actas_detalle.alumno,
    sga_actas_detalle.plan_version,
    sga_actas_detalle.fecha,
    sga_actas_detalle.fecha_vigencia,
    CASE 
      WHEN (sga_actas_detalle.fecha_vigencia IS NULL OR sga_actas_detalle.fecha_vigencia >= CURRENT_DATE) THEN 1
      ELSE 0
    END as es_vigente,
    sga_actas_detalle.escala_nota,
    sga_actas_detalle.nota,
    sga_actas_detalle.resultado
   
 FROM 
    sga_actas,
    sga_actas_detalle,
    sga_comisiones,
    sga_alumnos
      
 WHERE sga_actas_detalle.id_acta = sga_actas.id_acta
 AND sga_comisiones.comision = sga_actas.comision
 AND sga_alumnos.alumno = sga_actas_detalle.alumno
 AND sga_actas.estado = 'C' -- Cerrado
 AND sga_actas.origen = 'R' -- Regularidades..
 AND sga_actas_detalle.rectificado = 'N' -- No Rectificado
 AND sga_actas_detalle.estado = 'A' -- Activo 
  
UNION ALL 

 -- 2. Actas de Promociones (de comisiones que son solo promocionales)
 SELECT 
    sga_comisiones.elemento,
    sga_actas.id_acta,
    CASE sga_actas.tipo_acta
      WHEN 'N' THEN sga_actas.id_acta
      WHEN 'R' THEN sga_actas.acta_referencia
    END as id_acta_original,
    sga_actas.tipo_acta,
    sga_actas.origen,
    sga_actas.comision,
    cast(null as integer) as equivalencia_tramite,
    cast(null as integer) as equivalencia_tramite_original,
    cast(null as char(1)) as tipo_tramite,
    cast(null as integer) as equivalencia,
    sga_alumnos.persona,
    sga_actas_detalle.alumno,
    sga_actas_detalle.plan_version,
    sga_actas_detalle.fecha,
    sga_actas_detalle.fecha_vigencia,
    CASE 
      WHEN (sga_actas_detalle.fecha_vigencia IS NULL OR sga_actas_detalle.fecha_vigencia >= CURRENT_DATE) THEN 1
      ELSE 0
    END as es_vigente,
    sga_actas_detalle.escala_nota,
    sga_actas_detalle.nota,
    sga_actas_detalle.resultado
   
 FROM 
    sga_actas,
    sga_actas_detalle,
    sga_comisiones,
    sga_alumnos
      
 WHERE sga_actas_detalle.id_acta = sga_actas.id_acta
 AND sga_comisiones.comision = sga_actas.comision
 AND sga_alumnos.alumno = sga_actas_detalle.alumno
 AND sga_actas.estado = 'C' -- Cerrado
 AND sga_actas.origen = 'P' -- Promociones
 AND sga_actas_detalle.rectificado = 'N' -- No Rectificado
 AND sga_actas_detalle.estado = 'A' -- Activo 
 AND NOT EXISTS (SELECT 1 FROM sga_comisiones_instancias 
                  WHERE sga_comisiones_instancias.comision = sga_actas.comision 
                    AND sga_comisiones_instancias.instancia = 1) -- NO Existe instancia de cursada
  
UNION ALL 

 -- 3. Equivalencias de regularidad: Original y Rectificativas
 SELECT 
    sga_equiv_otorgada.elemento,
    cast(null as integer) as id_acta,
    cast(null as integer) as id_acta_original,
    cast(null as char(1)) as tipo_acta,
    'C' as origen,
    cast(null as integer) as comision,
    sga_equiv_tramite.equivalencia_tramite,
    CASE sga_equiv_tramite.tipo_tramite
      WHEN 'N' THEN sga_equiv_tramite.equivalencia_tramite
      WHEN 'R' THEN sga_equiv_tramite.rectifica_a 
    END as equivalencia_tramite_original,
    sga_equiv_tramite.tipo_tramite as tipo_tramite,
    sga_equiv_otorgada.equivalencia,
    sga_alumnos.persona,
    sga_equiv_tramite.alumno,
    sga_equiv_tramite.plan_version,
    sga_equiv_otorgada.fecha,
    sga_equiv_otorgada.fecha_vigencia,
    CASE 
      WHEN (sga_equiv_otorgada.fecha_vigencia IS NULL OR sga_equiv_otorgada.fecha_vigencia >= CURRENT_DATE) THEN 1
      ELSE 0
    END as es_vigente,
    sga_equiv_otorgada.escala_nota,
    sga_equiv_otorgada.nota,
    sga_equiv_otorgada.resultado
 FROM 
    sga_equiv_tramite,
    sga_equiv_otorgada,
    sga_alumnos
 WHERE sga_equiv_otorgada.equivalencia_tramite = sga_equiv_tramite.equivalencia_tramite
 AND sga_alumnos.alumno = sga_equiv_tramite.alumno
 AND sga_equiv_tramite.estado = 'C' -- Cerrado
 AND sga_equiv_otorgada.alcance = 'R' -- Equivalencia de Regularidad
 AND sga_equiv_otorgada.rectificado = 'N' -- No Rectificado
 AND sga_equiv_otorgada.estado = 'A' -- Activo 
 AND sga_equiv_otorgada.resultado IN ('A','R') -- Aprobado/Reprobado
;

-- ++++++++++++++++++++++++++++++ Fin view vw_regularidades_basica +++++++++++++++++++++++++++++++++++++++

GRANT SELECT ON vw_regularidades_basica to public;