USE akrcsist_bd0003_2026;

ALTER TABLE mon_observacion_aula
    ADD COLUMN IF NOT EXISTS dre VARCHAR(80) NULL AFTER momento_monitoreo,
    ADD COLUMN IF NOT EXISTS ugel VARCHAR(80) NULL AFTER dre,
    ADD COLUMN IF NOT EXISTS institucion_codigo VARCHAR(20) NULL AFTER ugel,
    ADD COLUMN IF NOT EXISTS institucion_nombre VARCHAR(180) NULL AFTER institucion_codigo,
    ADD COLUMN IF NOT EXISTS observador_dni VARCHAR(20) NULL AFTER observador_nombre,
    ADD COLUMN IF NOT EXISTS id_competencia INT UNSIGNED NULL AFTER id_area,
    ADD COLUMN IF NOT EXISTS docente_celular VARCHAR(20) NULL AFTER areas_curriculares,
    ADD COLUMN IF NOT EXISTS actividad TEXT NULL AFTER logro_sesion,
    ADD COLUMN IF NOT EXISTS competencia_descripcion TEXT NULL AFTER actividad,
    ADD COLUMN IF NOT EXISTS capacidad TEXT NULL AFTER competencia_descripcion,
    ADD COLUMN IF NOT EXISTS desempeno TEXT NULL AFTER capacidad,
    ADD COLUMN IF NOT EXISTS proposito_sesion TEXT NULL AFTER desempeno,
    ADD COLUMN IF NOT EXISTS estudiantes_matriculados SMALLINT UNSIGNED NULL AFTER proposito_sesion,
    ADD COLUMN IF NOT EXISTS estudiantes_asistentes SMALLINT UNSIGNED NULL AFTER estudiantes_matriculados;

UPDATE mon_observacion_aula
SET
    dre = COALESCE(NULLIF(dre, ''), 'SAN MARTIN'),
    ugel = COALESCE(NULLIF(ugel, ''), 'HUALLAGA'),
    institucion_codigo = COALESCE(NULLIF(institucion_codigo, ''), '0003'),
    institucion_nombre = COALESCE(NULLIF(institucion_nombre, ''), '0003'),
    actividad = COALESCE(NULLIF(actividad, ''), NULLIF(titulo_sesion, '')),
    proposito_sesion = COALESCE(NULLIF(proposito_sesion, ''), NULLIF(logro_sesion, ''))
WHERE id_observacion IS NOT NULL;

DROP VIEW IF EXISTS vw_observacion_aula_docente;

CREATE VIEW vw_observacion_aula_docente AS
SELECT
    o.id_observacion,
    o.id_monitoreo,
    o.fecha,
    YEAR(o.fecha) AS anio,
    o.momento_monitoreo,
    o.turno,
    o.hora_inicio,
    o.hora_fin,
    o.estado,
    o.dre,
    o.ugel,
    o.institucion_codigo,
    o.institucion_nombre,
    o.observador_nombre,
    o.observador_dni,
    o.titulo_sesion,
    o.logro_sesion,
    o.actividad,
    o.id_competencia,
    COALESCE(NULLIF(c.descripcion, ''), NULLIF(o.competencia_descripcion, '')) AS competencia,
    o.competencia_descripcion,
    o.capacidad,
    o.desempeno,
    o.proposito_sesion,
    o.estudiantes_matriculados,
    o.estudiantes_asistentes,
    o.resumen_observacion,
    o.necesidades_apoyo,
    o.acciones_seguimiento,
    o.id_docente,
    p.numero_documento AS dni_docente,
    COALESCE(NULLIF(p.nombre_completo, ''), TRIM(CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres))) AS docente,
    COALESCE(NULLIF(o.docente_celular, ''), p.celular) AS docente_celular,
    o.id_nivel,
    ne.descripcion AS nivel,
    o.id_grado,
    g.nombre_corto AS grado,
    o.id_seccion,
    s.nombre_corto AS seccion,
    o.id_area,
    COALESCE(a.nombre, o.areas_curriculares) AS areas_curriculares,
    (
        SELECT COUNT(DISTINCT e.id_evidencia)
        FROM mon_observacion_evidencia e
        WHERE e.id_observacion = o.id_observacion
          AND (
              NULLIF(e.evidencia, '') IS NOT NULL
              OR NULLIF(e.interpretacion, '') IS NOT NULL
              OR NULLIF(e.accion_sugerida, '') IS NOT NULL
          )
    ) AS aspectos_registrados,
    (
        SELECT COUNT(DISTINCT v.desempeno_codigo)
        FROM mon_observacion_desempeno_valoracion v
        WHERE v.id_observacion = o.id_observacion
          AND v.nivel_logro BETWEEN 1 AND 4
    ) AS desempenos_valorados,
    (
        SELECT ROUND(AVG(v.nivel_logro), 2)
        FROM mon_observacion_desempeno_valoracion v
        WHERE v.id_observacion = o.id_observacion
          AND v.nivel_logro BETWEEN 1 AND 4
    ) AS promedio_nivel,
    m.porcentaje_logro AS instrumento2_logro,
    m.nivel_global AS instrumento2_nivel,
    m.estado AS instrumento2_estado
FROM mon_observacion_aula o
INNER JOIN docente d ON d.id_docente = o.id_docente
INNER JOIN persona p ON p.id_persona = d.id_persona
LEFT JOIN nivel_educativo ne ON ne.id_nivel = o.id_nivel
LEFT JOIN grado g ON g.id_grado = o.id_grado
LEFT JOIN seccion s ON s.id_seccion = o.id_seccion
LEFT JOIN area_curricular a ON a.id_area = o.id_area
LEFT JOIN competencia c ON c.id_competencia = o.id_competencia
LEFT JOIN mon_monitoreo m ON m.id_monitoreo = o.id_monitoreo;