# REVISION INICIAL

- Estado: **OBSERVADO**
- Decision: **No generar SQL de FASE 2 ni INSERT productivo final**

## Resumen tecnico

El esquema adjunto `database/sgetecba_consolidado_multicarrera_espanol_postgresql-19.05.2026.sql` si contempla un flujo formal de importacion por lotes con `lotes_importacion`, `archivos_importacion`, `staging_excel_1..3`, `staging_estudiantes_unificados`, `conflictos_datos` y `errores_importacion`. Eso permite ejecutar una FASE 1 profesional sin tocar tablas finales.

Sin embargo, el analisis real de los tres Excel detecta conflictos de gravedad alta que bloquean la aprobacion:

- 54 filas de estudiantes con campos obligatorios vacios.
- 5 casos de mismo CI con nombres distintos.
- 2 casos de mismo codigo de estudiante con nombres distintos.
- 1 caso de mismo codigo de estudiante con CI distinto.
- 394 matriculaciones sin estudiante maestro relacionable.
- 38 asistencias sin estudiante maestro relacionable.

Tambien existe un riesgo adicional de compatibilidad: el backend actual sigue modelando tablas y columnas distintas al SQL consolidado. El SQL usa `inscripciones`, `ofertas_asignaturas`, `detalle_asistencia` y `abandonos_estudiantes`; el codigo actual aun consulta `matriculaciones`, `ofertas_asignatura` y `asistencias`. Aunque eso no impide la FASE 1 de datos, si impide aprobar una importacion productiva sin alinear primero la capa de aplicacion.

## Riesgos

- Alta probabilidad de consolidar personas equivocadas si se importa por CI/codigo sin revision manual.
- Riesgo de inscripciones huerfanas si se cargan matriculaciones que no enlazan con `estudiantes`.
- Riesgo de asistencia huerfana y duplicada.
- Riesgo funcional por desalineacion entre schema SQL y modelos/rutas del backend.
- Riesgo de seguridad: la hoja `Usuarios` contiene claves aparentes en texto plano o numericas y no debe migrarse directamente a `usuarios`.

## Recomendacion

Ejecutar solo FASE 1: limpieza, staging, validacion y conflictos. Resolver los conflictos de gravedad alta y alinear backend vs schema antes de cualquier carga final.

# DIAGNOSTICO DE LOS EXCEL

## Excel Semestral

- Archivo: `docs/SEMESTRAL-SISTEMA INTEGRAL ACADEMICO (2) (1).xlsx`
- Hojas detectadas:
  - `Estudiantes`: 277 filas de datos
  - `Matriculaciones`: 1745 filas de datos
  - `Carreras`: 5 filas de datos
  - `Asignaturas`: 101 filas de datos
  - `Asistencia`: 25418 filas de datos
  - `Usuarios`: 9 filas de datos
- Columnas principales:
  - `Estudiantes`: `COD_ESTUDIANTE`, `PATERNO`, `MATERNO`, `NOMBRES`, `CEDULA`, `GENERO`, `TELEFONO`, `CORREO`, `CARRERA`, `COD_CARRERA`, `TURNO`, `ESTADO`, `FECHA_INSCRIPCION`
  - `Matriculaciones`: `COD_ESTUDIANTE`, `NOMBRE_COMPLETO`, `CEDULA`, `CARRERA`, `TURNO_ESTUDIANTE`, `COD_MATERIA`, `NOMBRE_MATERIA`, `PERIODO`, `NIVEL`, `TURNO_MATERIA`, `FECHA_REGISTRO`
  - `Asignaturas`: `Codigo`, `Nombre`, `Periodo`, `Nivel`, `Codigo_Carrera`, `Turno`, `Dia`
  - `Asistencia`: `FECHA`, `PATERNO`, `MATERNO`, `NOMBRES`, `TELEFONO`, `CI`, `CORREO_INSTITUCIONAL`, `CODIGO`, `GENERO`, `CARRERA`, `TURNO_INSCRIPCION`, `TURNO_ASIGNATURA`, `AÑO_ASIGNATURA`, `ASIGNATURA`, `TIPO_ASIG`, `ESTADO_ASISTENCIA`, `ESTADO_INSCRIPCION`
- Hallazgos:
  - Sin vacios criticos en `Estudiantes`.
  - `Matriculaciones` tiene 153 referencias a estudiante sin maestro resoluble.
  - `Matriculaciones` presenta 2 periodos con patron irregular.
  - `Asignaturas` tiene 1 codigo con nombres distintos.
  - `Asistencia` tiene 6 referencias a estudiante sin maestro resoluble.
  - `Asistencia` contiene 464 posibles duplicados exactos por clave operacional.

## Excel Modular

- Archivo: `docs/MODULAR-SISTEMA INTEGRAL ACADEMICO.xlsx`
- Hojas detectadas:
  - `Estudiantes`: 431 filas de datos
  - `Matriculaciones`: 2311 filas de datos
  - `Carreras`: 2 filas de datos
  - `Asignaturas`: 91 filas de datos
  - `Asistencia`: 4475 filas de datos
  - `Usuarios`: 5 filas de datos
- Hallazgos:
  - 52 estudiantes sin `ESTADO`.
  - 1 estudiante sin `COD_ESTUDIANTE`.
  - 5 CIs duplicados.
  - 4 casos de mismo CI con distinto nombre.
  - 3 casos de mismo CI con distinto codigo.
  - 1 caso de mismo codigo con distinto nombre.
  - `Asignaturas` tiene 3 codigos con nombres distintos.
  - `Asistencia` contiene 86 posibles duplicados exactos.

## Excel Anual

- Archivo: `docs/ANUAL-SISTEMA INTEGRAL ACADEMICO (4).xlsx`
- Hojas detectadas:
  - `Estudiantes`: 431 filas de datos
  - `Matriculaciones`: 3670 filas de datos
  - `Carreras`: 6 filas de datos
  - `Asignaturas`: 142 filas de datos
  - `Asistencia`: 51510 filas de datos
  - `Usuarios`: 9 filas de datos
- Hallazgos:
  - 1 estudiante sin `ESTADO`.
  - `Matriculaciones` tiene 241 referencias a estudiante sin maestro resoluble.
  - `Matriculaciones` presenta 327 periodos con patron irregular.
  - `Asignaturas` tiene 9 codigos con nombres distintos.
  - `Asistencia` tiene 32 referencias a estudiante sin maestro resoluble.
  - `Asistencia` contiene 2573 posibles duplicados exactos.

## Conflictos cruzados entre Excel

- Total estudiantes detectados: 1139 filas.
- Total estudiantes unicos potenciales por CI/codigo: 1133.
- Casos `mismo CI + distinto nombre`: 5.
- Casos `mismo CI + distinto codigo`: 4.
- Casos `mismo codigo + distinto nombre`: 2.
- Casos `mismo codigo + distinto CI`: 1.
- Identidades potenciales con mas de una carrera: 2.
- Identidades potenciales con mas de una modalidad: 2.

### Muestras de conflictos altos

- CI `7998674`
  - ANUAL fila 199: `CIA-26015` `URIONA CLAROS DAMARIS`
  - MODULAR fila 196: `ENF-26021` `MONTECINOS ROJAS LEONEL SEBASTTIAN`
- CI `14176250`
  - MODULAR fila 28: `RAX-26014` `VACA GILARDE BRYAN ALEJANDRO`
  - MODULAR fila 36: `RAX-26061` `CAZAS LIZARRO ARACELI LISETTE`
- Codigo `ENF-25012`
  - MODULAR fila 342: CI `14150609`, `SOLIS CADIMA NEYDI`
  - SEMESTRAL fila 6: CI `9392984`, `HERBAS MEDRANO RUBEN ALEXANDER`

# ANALISIS DEL SQL ADJUNTO

## Tablas finales identificadas

- Catalogos y seguridad:
  - `roles`
  - `usuarios`
  - `usuario_roles`
  - `generos`
  - `estados_estudiante`
  - `estados_inscripcion`
  - `modalidades_academicas`
  - `niveles_academicos`
  - `turnos`
  - `grupos`
  - `estados_asistencia`
  - `motivos_abandono`
- Academicas:
  - `periodos_academicos`
  - `carreras`
  - `asignaturas`
  - `ofertas_asignaturas`
  - `docentes`
  - `asignaciones_docente`
  - `estudiantes`
  - `mapa_fuentes_estudiante`
  - `inscripciones`
  - `sesiones_asistencia`
  - `detalle_asistencia`
  - `faltas`
  - `abandonos_estudiantes`
- Importacion, calidad y auditoria:
  - `lotes_importacion`
  - `archivos_importacion`
  - `errores_importacion`
  - `auditoria_logs`

## Tablas de staging y conflictos

- `staging_excel_1`
- `staging_excel_2`
- `staging_excel_3`
- `staging_estudiantes_unificados`
- `conflictos_datos`
- `errores_importacion`

## Restricciones relevantes

- PK en todas las tablas por `id`.
- FKs fuertes desde `ofertas_asignaturas`, `inscripciones`, `sesiones_asistencia`, `detalle_asistencia`, `faltas` y `abandonos_estudiantes`.
- `UNIQUE` criticas:
  - `roles.codigo`
  - `usuarios.nombre_usuario`
  - `generos.codigo`
  - `estados_* .codigo`
  - `modalidades_academicas.codigo`
  - `periodos_academicos.codigo`
  - `carreras.codigo`
  - `asignaturas (codigo, nombre)`
  - `ofertas_asignaturas (asignatura_id, carrera_id, modalidad_academica_id, periodo_academico_id, nivel_academico_id, turno_id, grupo_id)`
  - `mapa_fuentes_estudiante (archivo_origen, hoja_origen, numero_fila_origen)`
  - `ux_inscripcion_estudiante_oferta_activo`
  - `ux_sesion_asistencia_oferta_fecha`
  - `detalle_asistencia (sesion_asistencia_id, enrollment_id)`
- `CHECK` criticas:
  - `periodos_academicos.fecha_fin >= fecha_inicio`
  - `sesiones_asistencia.estado_sesion in ('OPEN','CLOSED','CANCELLED')`
  - `lotes_importacion.estado`
  - `staging_estudiantes_unificados.estado_consolidacion`
  - `conflictos_datos.gravedad`
  - `conflictos_datos.estado_resolucion`

## Catalogos obligatorios

- `generos`
- `estados_estudiante`
- `estados_inscripcion`
- `modalidades_academicas`
- `niveles_academicos`
- `turnos`
- `grupos`
- `estados_asistencia`
- `motivos_abandono`
- `periodos_academicos`
- `carreras`
- `asignaturas`

## Orden correcto de insercion

1. Catalogos base
2. `modalidades_academicas`
3. `periodos_academicos`
4. `carreras`
5. `niveles_academicos`
6. `grupos`
7. `turnos`
8. `asignaturas`
9. `docentes`
10. `ofertas_asignaturas`
11. `estudiantes`
12. `mapa_fuentes_estudiante`
13. `inscripciones`
14. `sesiones_asistencia`
15. `detalle_asistencia`
16. `faltas`
17. `abandonos_estudiantes`
18. `auditoria_logs`
19. `lotes_importacion` actualizado y resumen operativo

## Observaciones de modelo

- El SQL respeta la regla de negocio de estudiante multi-carrera y multi-modalidad.
- La tabla `conflictos_datos` no tiene literalmente `valor_original` ni `valor_normalizado`; el script de FASE 1 debe mapearlos sobre `valor_archivo_1` y `valor_archivo_2`.
- Las tablas `staging_excel_1..3` son genericas; para no alterar schema, la normalizacion adicional debe guardarse dentro de `datos_originales -> normalized`.
- Existe desalineacion entre este SQL y el backend actual. Eso debe resolverse antes de produccion.

# MAPEO DE DATOS

## Excel -> staging

### Hojas `Estudiantes`

- `COD_ESTUDIANTE` -> `codigo_estudiante_detectado`
- `CEDULA` -> `cedula_identidad_detectada`
- `PATERNO + MATERNO + NOMBRES` -> `nombre_completo_detectado`
- `CARRERA` -> `carrera_detectada`
- `ESTADO` -> `estado_detectado`
- resto de columnas -> `datos_originales.raw`
- campos normalizados (`ci_normalizado`, `telefono_normalizado`, `estado_normalizado`, `genero_normalizado`, `fecha_inscripcion`) -> `datos_originales.normalized`

### Hojas `Matriculaciones`

- `COD_ESTUDIANTE` -> `codigo_estudiante_detectado`
- `CEDULA` -> `cedula_identidad_detectada`
- `NOMBRE_COMPLETO` -> `nombre_completo_detectado`
- `CARRERA` -> `carrera_detectada`
- `NOMBRE_MATERIA` -> `asignatura_detectada`
- `datos_originales.raw` -> fila original completa
- `datos_originales.normalized` -> `periodo_normalizado`, `nivel_normalizado`, `turno_materia_normalizado`, `fecha_registro`

### Hojas `Asignaturas`

- `Codigo` y `Nombre` -> `datos_originales.raw`
- `Nombre` -> `asignatura_detectada`
- `Codigo_Carrera`/`Turno`/`Periodo`/`Nivel` -> `datos_originales.normalized`

### Hojas `Asistencia`

- `CODIGO` -> `codigo_estudiante_detectado`
- `CI` -> `cedula_identidad_detectada`
- `PATERNO + MATERNO + NOMBRES` -> `nombre_completo_detectado`
- `CARRERA` -> `carrera_detectada`
- `ASIGNATURA` -> `asignatura_detectada`
- `ESTADO_INSCRIPCION` -> `estado_detectado`
- `datos_originales.normalized` -> `fecha_clase`, `estado_asistencia`, `tipo_asig`, `turnos`, `nivel`

## Staging -> tablas finales

- `staging_excel_*` -> `staging_estudiantes_unificados`
- `staging_estudiantes_unificados` -> `estudiantes`
- `datos_originales.normalized.carrera/cod_carrera` -> `carreras`
- `datos_originales.normalized.modalidad` -> `modalidades_academicas`
- `datos_originales.normalized.periodo` -> `periodos_academicos`
- `datos_originales.normalized.nivel` -> `niveles_academicos`
- `datos_originales.normalized.turno` -> `turnos`
- `datos_originales.normalized.grupo` -> `grupos`
- `datos_originales.normalized.asignatura/codigo_materia` -> `asignaturas`
- `carrera + modalidad + periodo + nivel + turno + grupo + asignatura` -> `ofertas_asignaturas`
- `estudiante + oferta` -> `inscripciones`
- `oferta + fecha_clase` -> `sesiones_asistencia`
- `sesion + inscripcion + estado_asistencia` -> `detalle_asistencia`
- `detalle_asistencia` con estado que cuenta falta -> `faltas`
- indicadores de abandono -> `abandonos_estudiantes`
- fila fuente -> `mapa_fuentes_estudiante`

# REGLAS DE LIMPIEZA APLICADAS

- `trim` de extremos.
- colapso de espacios internos multiples.
- `upper` para comparacion.
- remocion logica de tildes para matching.
- CI normalizado:
  - convertir notacion cientifica de Excel a entero textual
  - remover `.0`
  - remover separadores no alfanumericos
- telefono normalizado con solo digitos.
- fechas:
  - aceptar `dd/mm/yyyy`
  - aceptar serial Excel
- genero normalizado a catalogo `M`, `F`, `OTRO`, `SIN_DATO`
- estado estudiante normalizado a `INSCRITO`, `ACTIVO`, `INACTIVO`, `ABANDONO`, `PROGRAMADO`
- turnos normalizados a `MANANA`, `TARDE`, `NOCHE`
- niveles preservados y normalizados (`SEM 1`, `ANO 1`, etc.)
- periodos preservados textual y marcados como irregulares cuando no cumplen patron.
- variantes de carrera y asignatura registradas como conflicto de catalogo, no corregidas automaticamente.

# REGLAS DE VALIDACION

## Estudiantes

- `COD_ESTUDIANTE` obligatorio cuando la fuente lo provee.
- `NOMBRE_COMPLETO` obligatorio.
- `ESTADO` obligatorio.
- CI no vacio recomendado y obligatorio para identidad fuerte cuando exista en la fuente.
- `GENERO` debe existir en catalogo.
- conflicto alto si:
  - mismo CI con distinto nombre
  - mismo codigo con distinto nombre
  - mismo codigo con distinto CI

## Academico

- modalidad valida: `ANUAL`, `SEMESTRAL`, `MODULAR`
- carrera candidata valida por catalogo o enviada a conflicto
- asignatura candidata valida por codigo/nombre o enviada a conflicto
- `NIVEL`, `PERIODO`, `TURNO` y `GRUPO` deben normalizarse antes de final

## Inscripciones

- no cargar inscripcion si no existe estudiante maestro resoluble
- no cargar si no existe oferta academica resoluble
- no cargar si la clave exacta ya existe

## Asistencia

- fecha de clase valida
- estado de asistencia dentro de catalogo
- no cargar si no existe inscripcion resoluble
- no cargar si la sesion/inscripcion ya esta duplicada

# DATA_CONFLICTS

## Tipos de conflicto detectados

- `CAMPO_OBLIGATORIO_VACIO`
- `MISMO_CI_DISTINTO_NOMBRE`
- `MISMO_CI_DISTINTO_CODIGO`
- `MISMO_CODIGO_DISTINTO_NOMBRE`
- `MISMO_CODIGO_DISTINTO_CI`
- `MATRICULACION_SIN_ESTUDIANTE`
- `ASISTENCIA_SIN_ESTUDIANTE`
- `CATALOGO_ASIGNATURA_VARIANTE`
- `PERIODO_IRREGULAR`
- `ASISTENCIA_DUPLICADA`

## Resumen por severidad

- ALTA:
  - 54 filas de estudiantes con obligatorios vacios
  - 5 casos de CI con nombre distinto
  - 2 casos de codigo con nombre distinto
  - 1 caso de codigo con CI distinto
  - 394 matriculaciones huerfanas
  - 38 asistencias huerfanas
- MEDIA:
  - 4 casos de CI con codigo distinto
  - 13 variantes de asignatura por mismo codigo
  - 329 periodos irregulares en matriculaciones
  - 3123 posibles duplicados exactos de asistencia
- BAJA:
  - 2 identidades multi-carrera
  - 2 identidades multi-modalidad

## Estructura recomendada para `conflictos_datos`

- `tipo_conflicto`
- `tipo_entidad`
- `clave_entidad`
- `archivo_origen_1`
- `hoja_origen`
- `numeros_fila_origen`
- `nombre_campo`
- `valor_archivo_1` como valor original
- `valor_archivo_2` como valor normalizado o valor comparado
- `valor_recomendado`
- `gravedad`
- `estado_resolucion`
- `recomendacion`
- `accion_sugerida`

# SQL FASE 1

Se entrega en:

- `database/sgetecba_import_phase1_staging_validation.sql`

Ese script cubre:

- funciones auxiliares de normalizacion
- registro de lote y archivos
- carga a `staging_excel_1..3`
- consolidacion a `staging_estudiantes_unificados`
- validaciones y conflictos
- actualizacion del estado del lote
- consultas de reporte

# REPORTE DE APROBACION

- Estado general: **OBSERVADO**
- Total registros analizados por Excel:
  - Semestral: 27555 filas de negocio
  - Modular: 7310 filas de negocio
  - Anual: 55759 filas de negocio
- Total registros candidatos a staging:
  - Operacionales (`Estudiantes`, `Matriculaciones`, `Asignaturas`, `Asistencia`): 90602
- Total estudiantes detectados: 1139
- Total estudiantes unicos potenciales: 1133
- Total estudiantes con mas de una carrera: 2 identidades
- Total estudiantes con mas de una modalidad: 2 identidades
- Total inscripciones fuente: 7726
- Total asistencias fuente: 81403
- Total conflictos altos detectados: no resueltos
- Tablas afectadas:
  - `staging_excel_1`
  - `staging_excel_2`
  - `staging_excel_3`
  - `staging_estudiantes_unificados`
  - `conflictos_datos`
  - `errores_importacion`
  - potencialmente `estudiantes`, `inscripciones`, `detalle_asistencia`, `faltas`, `abandonos_estudiantes`

## Acciones de correccion necesarias

1. Resolver manualmente los 5 casos de mismo CI con distinto nombre.
2. Resolver manualmente los casos de reutilizacion de codigo de estudiante.
3. Completar `ESTADO` y `COD_ESTUDIANTE` faltantes en Modular/Anual.
4. Corregir o excluir las 394 matriculaciones sin estudiante maestro.
5. Corregir o excluir las 38 asistencias sin estudiante maestro.
6. Definir catalogo oficial para variantes de asignaturas por mismo codigo.
7. Normalizar `PERIODO` para modalidades Anual/Semestral/Modular.
8. Alinear backend actual con el schema consolidado antes de produccion.

## Decision final

**OBSERVADO.**

No se debe generar ni ejecutar `INSERT` productivo de FASE 2 hasta cerrar todos los conflictos de gravedad alta y la desalineacion de modelo.
