# Informe gerencial y técnico - Consolidación SGTECBA

## 1. Resumen ejecutivo

Se analizaron los 3 Excel del sistema **sgetecba**: **Semestral**, **Modular** y **Anual**. La consolidación debe realizarse en una sola base de datos PostgreSQL, pero con una regla crítica: **un estudiante puede tener una o más carreras y también puede pertenecer a más de una modalidad académica**. Por lo tanto, **students debe representar solamente la identidad del estudiante/persona**; la carrera, modalidad, nivel, turno, grupo, asignatura y periodo deben quedar en tablas relacionales como **enrollments** y **subject_offerings**.

La estrategia recomendada es: **Excel → staging → normalización → data_conflicts → revisión manual → tablas finales → dashboard gerencial**.

## 2. Diagnóstico de archivos

| Modalidad | Estudiantes | Matriculaciones | Carreras | Asignaturas | Asistencia | Usuarios |
|---|---:|---:|---:|---:|---:|---:|
| SEMESTRAL | 277 | 1745 | 5 | 101 | 25418 | 9 |
| MODULAR | 431 | 2311 | 2 | 91 | 4475 | 5 |
| ANUAL | 431 | 3670 | 6 | 142 | 51510 | 9 |
| **TOTAL** | **1139** | **7726** | **13** | **334** | **81403** | **23** |

## 3. Hallazgos ejecutivos

| Indicador | Resultado |
|---|---:|
| Estudiantes registrados en Excel | 1139 |
| Estudiantes únicos potenciales | 1133 |
| Matriculaciones detectadas | 7726 |
| Registros de asistencia | 81403 |
| Estudiantes con más de una carrera | 3 |
| Estudiantes con más de una modalidad | 2 |
| Conflictos de datos detectados | 34 |

## 4. Regla crítica: multi-carrera y multi-modalidad

Un estudiante no debe modelarse como si perteneciera a una sola carrera o una sola modalidad. En el contexto TECBA, un estudiante puede cursar una carrera en modalidad **Anual** y otra carrera en modalidad **Semestral** o **Modular**. Por eso:

- **students** guarda identidad: CI, nombres, apellidos, género, teléfono, correo y estado general.
- **careers** guarda el catálogo de carreras.
- **academic_modalities** guarda Anual, Semestral y Modular.
- **subject_offerings** une carrera + modalidad + periodo + nivel + asignatura + turno + grupo.
- **enrollments** une al estudiante con la oferta académica.

Esta estructura permite historial académico, evita duplicidad y facilita reportes gerenciales confiables.

## 5. Comparación general entre los 3 Excel

Las hojas detectadas son homogéneas en los tres archivos:

- Estudiantes
- Matriculaciones
- Carreras
- Asignaturas
- Asistencia
- Usuarios

## 6. Mapa de columnas equivalentes

| Concepto final | Estudiantes | Matriculaciones | Asistencia |
|---|---|---|---|
| Código estudiante | COD_ESTUDIANTE | COD_ESTUDIANTE | CODIGO |
| CI | CEDULA | CEDULA | CI |
| Nombre | PATERNO + MATERNO + NOMBRES | NOMBRE_COMPLETO | PATERNO + MATERNO + NOMBRES |
| Carrera | CARRERA / COD_CARRERA | CARRERA | CARRERA |
| Modalidad | Archivo origen | Archivo origen | Archivo origen |
| Nivel | — | NIVEL | AÑO_ASIGNATURA |
| Turno | TURNO | TURNO_ESTUDIANTE / TURNO_MATERIA | TURNO_INSCRIPCION / TURNO_ASIGNATURA |
| Asignatura | — | COD_MATERIA / NOMBRE_MATERIA | ASIGNATURA |
| Fecha | FECHA_INSCRIPCION | FECHA_REGISTRO | FECHA |
| Estado | ESTADO | — | ESTADO_ASISTENCIA / ESTADO_INSCRIPCION |

## 7. Indicadores para dashboard tipo Power BI

### Top carreras por matriculación

| Carrera | Matriculaciones |
|---|---:|
| COMERCIO INTERNACIONAL Y ADMINISTRACION ADUANERA | 1559 |
| ENFERMERIA | 1340 |
| FISIOTERAPIA | 1074 |
| SISTEMAS INFORMATICOS | 1023 |
| RAYOS X | 971 |
| DISENO GRAFICO | 560 |
| ELECTRONICA | 371 |
| MERCADOTECNIA | 283 |
| CONTADURIA GENERAL | 245 |
| CONSTRUCCION CIVIL | 161 |
| INGLES | 105 |
| AUTOMATICA INDUSTRIAL | 34 |

### Asistencia por estado

| Estado | Registros |
|---|---:|
| PRESENTE | 55332 |
| FALTA | 13942 |
| ABANDONO | 8165 |
| LICENCIA | 1579 |
| RETRASO | 1319 |
| INACTIVO | 970 |
| ACTIVO | 96 |

### Estado estudiantil

| Estado | Estudiantes |
|---|---:|
| INSCRITO | 911 |
| ABANDONO | 164 |
| VACIO | 53 |
| INACTIVO | 10 |
| PROGRAMADO | 1 |

### Sexo / género

| Género | Estudiantes |
|---|---:|
| M | 572 |
| F | 567 |

## 8. data_conflicts

| Tipo de conflicto | Cantidad |
|---|---:|
| Código de asignatura con nombre/carrera diferente | 13 |
| Mismo CI con diferente nombre | 5 |
| Mismo CI con diferente código estudiante | 4 |
| Campo obligatorio vacío | 4 |
| Registro referencial sin estudiante maestro | 4 |
| Mismo código estudiante con diferente nombre | 2 |
| Mismo código estudiante con diferente CI | 1 |
| Fecha vacía o no validada | 1 |

Los conflictos de alta importancia deben resolverse antes de cargar información en producción. Especialmente: CI con diferentes nombres, código de estudiante con diferentes datos y registros transaccionales que no tienen estudiante maestro.

## 9. Propuesta de base de datos única

### Tablas maestras

| Tabla | Propósito |
|---|---|
| students | Identidad única del estudiante/persona |
| careers | Catálogo oficial de carreras |
| academic_modalities | Catálogo de modalidades: anual, semestral, modular |
| academic_periods | Gestión o periodo académico |
| subjects | Catálogo general de asignaturas |
| subject_offerings | Oferta académica por carrera, modalidad, periodo, nivel, turno y grupo |
| enrollments | Inscripciones del estudiante a una oferta académica |

### Tablas de seguimiento

| Tabla | Propósito |
|---|---|
| attendance_sessions | Cabecera de asistencia por fecha y oferta |
| attendance_details | Detalle de asistencia por estudiante |
| absences | Faltas derivadas de la asistencia |
| student_dropouts | Registro de abandono estudiantil |

### Tablas de staging y control

| Tabla | Propósito |
|---|---|
| import_batches | Lote de importación |
| staging_excel_1 | Datos crudos del Excel Semestral |
| staging_excel_2 | Datos crudos del Excel Modular |
| staging_excel_3 | Datos crudos del Excel Anual |
| staging_students_unified | Consolidación temporal de estudiantes |
| data_conflicts | Conflictos detectados entre fuentes |
| import_errors | Errores de validación/importación |
| audit_logs | Auditoría del sistema |

## 10. Flujo ETL recomendado

1. Cargar Excel Semestral, Modular y Anual.
2. Registrar proceso en **import_batches**.
3. Guardar cada fila original en tablas staging usando **raw_data JSONB**.
4. Normalizar nombres, CI, carrera, estado, turno, modalidad y asignatura.
5. Detectar claves candidatas: CI, código, nombre completo + teléfono.
6. Consolidar estudiantes en **staging_students_unified**.
7. Detectar conflictos y registrar en **data_conflicts**.
8. Revisar conflictos de gravedad alta y media.
9. Aprobar consolidación.
10. Insertar datos en tablas finales.
11. Construir dashboard desde vistas SQL.

## 11. Reglas de resolución de conflictos

| Regla | Acción recomendada |
|---|---|
| CI coincide | Usar CI como identificador principal |
| CI vacío | Usar código o nombre completo + teléfono como clave candidata |
| Mismo CI con diferente nombre | Revisión manual obligatoria |
| Mismo código con diferente CI | No consolidar automáticamente |
| Carrera distinta en el mismo estudiante | No siempre es conflicto; puede ser multi-carrera válido |
| Modalidad distinta en el mismo estudiante | No siempre es conflicto; puede ser multi-modalidad válido |
| Estado distinto | Priorizar fecha más reciente si existe; si no, revisar |
| Dato vacío en un Excel y lleno en otro | Completar con el dato más confiable y registrar trazabilidad |
| Datos dudosos | Enviar a data_conflicts, no a producción |

## 12. Diagrama ER Mermaid

```mermaid
erDiagram
    students ||--o{ enrollments : tiene
    careers ||--o{ subject_offerings : ofrece
    academic_modalities ||--o{ subject_offerings : clasifica
    academic_periods ||--o{ subject_offerings : periodo
    subjects ||--o{ subject_offerings : se_oferta
    subject_offerings ||--o{ enrollments : recibe
    subject_offerings ||--o{ attendance_sessions : genera
    attendance_sessions ||--o{ attendance_details : contiene
    students ||--o{ attendance_details : registra
    attendance_details ||--o| absences : puede_generar
    students ||--o{ student_dropouts : puede_tener
    dropout_reasons ||--o{ student_dropouts : clasifica
    import_batches ||--o{ staging_students_unified : consolida
    import_batches ||--o{ data_conflicts : detecta
    import_batches ||--o{ import_errors : registra
```

## 13. Recomendaciones finales

1. No importar directo a producción.
2. Crear un catálogo oficial de carreras y asignaturas antes de migrar.
3. Resolver primero conflictos de CI y código de estudiante.
4. Aceptar formalmente el modelo multi-carrera y multi-modalidad.
5. Usar vistas SQL para alimentar Power BI o dashboard web.
6. Mantener auditoría de cada cambio crítico.
7. Conservar siempre los datos originales en **raw_data JSONB**.
