USE akrcsist_bd0003_2026;

CREATE TABLE IF NOT EXISTS mon_observacion_desempeno_valoracion (
    id_valoracion INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_observacion INT UNSIGNED NOT NULL,
    desempeno_codigo VARCHAR(20) NOT NULL,
    nivel_logro TINYINT UNSIGNED NULL,
    sustento_valoracion TEXT NULL,
    recomendacion TEXT NULL,
    actualizado_en DATETIME NULL,
    PRIMARY KEY (id_valoracion),
    UNIQUE KEY uk_obs_desempeno (id_observacion, desempeno_codigo),
    CONSTRAINT fk_obs_valoracion_observacion
        FOREIGN KEY (id_observacion) REFERENCES mon_observacion_aula (id_observacion)
        ON DELETE CASCADE
);

CREATE OR REPLACE VIEW vw_observacion_aula_docente AS
SELECT
    o.id_observacion,
    o.id_monitoreo,
    o.fecha,
    YEAR(o.fecha) AS anio,
    o.turno,
    o.hora_inicio,
    o.hora_fin,
    o.estado,
    o.titulo_sesion,
    o.logro_sesion,
    o.resumen_observacion,
    o.necesidades_apoyo,
    o.acciones_seguimiento,
    o.observador_nombre,
    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,
    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,
    COUNT(DISTINCT CASE
        WHEN NULLIF(e.evidencia, '') IS NOT NULL
          OR NULLIF(e.interpretacion, '') IS NOT NULL
          OR NULLIF(e.accion_sugerida, '') IS NOT NULL
        THEN e.id_evidencia
    END) AS aspectos_registrados,
    COUNT(DISTINCT CASE
        WHEN v.nivel_logro BETWEEN 1 AND 4 THEN v.desempeno_codigo
    END) AS desempenos_valorados,
    ROUND(AVG(CASE WHEN v.nivel_logro BETWEEN 1 AND 4 THEN v.nivel_logro END), 2) 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 mon_observacion_evidencia e ON e.id_observacion = o.id_observacion
LEFT JOIN mon_observacion_desempeno_valoracion v ON v.id_observacion = o.id_observacion
LEFT JOIN mon_monitoreo m ON m.id_monitoreo = o.id_monitoreo
GROUP BY
    o.id_observacion,
    o.id_monitoreo,
    o.fecha,
    YEAR(o.fecha),
    o.turno,
    o.hora_inicio,
    o.hora_fin,
    o.estado,
    o.titulo_sesion,
    o.logro_sesion,
    o.resumen_observacion,
    o.necesidades_apoyo,
    o.acciones_seguimiento,
    o.observador_nombre,
    o.id_docente,
    p.numero_documento,
    COALESCE(NULLIF(p.nombre_completo, ''), TRIM(CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres))),
    o.id_nivel,
    ne.descripcion,
    o.id_grado,
    g.nombre_corto,
    o.id_seccion,
    s.nombre_corto,
    o.id_area,
    COALESCE(a.nombre, o.areas_curriculares),
    m.porcentaje_logro,
    m.nivel_global,
    m.estado;