USE akrcsist_bd0003_2026;

CREATE TABLE IF NOT EXISTS mon_cuaderno_campo (
    id_cuaderno INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_docente INT UNSIGNED NOT NULL,
    id_observador_usuario INT UNSIGNED NULL,
    momento_monitoreo ENUM('DIAGNOSTICO','PROCESO','CIERRE') NOT NULL DEFAULT 'PROCESO',
    observador_nombre VARCHAR(180) NOT NULL,
    fecha DATE NOT NULL,
    id_nivel CHAR(3) NULL,
    id_grado TINYINT UNSIGNED NULL,
    id_seccion TINYINT UNSIGNED NULL,
    id_area INT UNSIGNED NULL,
    actividad_aprendizaje VARCHAR(260) NULL,
    proceso_retroalimentacion TEXT NULL,
    compromisos_mejora TEXT NULL,
    necesidad_priorizada TEXT NULL,
    tema_trabajo_colegiado VARCHAR(260) NULL,
    proposito_trabajo_colegiado TEXT NULL,
    fecha_trabajo_colegiado DATE NULL,
    responsable_trabajo_colegiado VARCHAR(180) NULL,
    estado ENUM('BORRADOR','VALIDADO','PLANIFICADO') NOT NULL DEFAULT 'BORRADOR',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en DATETIME NULL,
    PRIMARY KEY (id_cuaderno),
    KEY idx_cc_fecha (fecha, estado),
    KEY idx_cc_momento (momento_monitoreo),
    KEY fk_cc_docente (id_docente),
    KEY fk_cc_usuario (id_observador_usuario),
    KEY fk_cc_nivel (id_nivel),
    KEY fk_cc_grado (id_grado),
    KEY fk_cc_seccion (id_seccion),
    KEY fk_cc_area (id_area),
    CONSTRAINT fk_cc_docente FOREIGN KEY (id_docente) REFERENCES docente(id_docente),
    CONSTRAINT fk_cc_usuario FOREIGN KEY (id_observador_usuario) REFERENCES usuario_sistema(id_usuario),
    CONSTRAINT fk_cc_nivel FOREIGN KEY (id_nivel) REFERENCES nivel_educativo(id_nivel),
    CONSTRAINT fk_cc_grado FOREIGN KEY (id_grado) REFERENCES grado(id_grado),
    CONSTRAINT fk_cc_seccion FOREIGN KEY (id_seccion) REFERENCES seccion(id_seccion),
    CONSTRAINT fk_cc_area FOREIGN KEY (id_area) REFERENCES area_curricular(id_area)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mon_cuaderno_campo_detalle (
    id_detalle INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_cuaderno INT UNSIGNED NOT NULL,
    orden TINYINT UNSIGNED NOT NULL DEFAULT 1,
    aspecto_observar VARCHAR(260) NULL,
    descripcion_hechos TEXT NULL,
    priorizacion_hechos TEXT NULL,
    apreciaciones_interrogantes TEXT NULL,
    prioridad ENUM('BAJA','MEDIA','ALTA') NOT NULL DEFAULT 'MEDIA',
    genera_trabajo_colegiado TINYINT(1) NOT NULL DEFAULT 0,
    actualizado_en DATETIME NULL,
    PRIMARY KEY (id_detalle),
    KEY idx_ccd_cuaderno (id_cuaderno, orden),
    CONSTRAINT fk_ccd_cuaderno FOREIGN KEY (id_cuaderno) REFERENCES mon_cuaderno_campo(id_cuaderno) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW vw_mon_cuaderno_campo AS
SELECT
    c.id_cuaderno,
    c.fecha,
    YEAR(c.fecha) AS anio,
    c.momento_monitoreo,
    c.estado,
    c.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,
    c.observador_nombre,
    c.id_nivel,
    ne.descripcion AS nivel,
    c.id_grado,
    g.nombre_corto AS grado,
    c.id_seccion,
    s.nombre_corto AS seccion,
    c.id_area,
    a.nombre AS area_curricular,
    c.actividad_aprendizaje,
    c.necesidad_priorizada,
    c.tema_trabajo_colegiado,
    c.fecha_trabajo_colegiado,
    c.responsable_trabajo_colegiado,
    COUNT(d.id_detalle) AS hechos_registrados,
    SUM(CASE
        WHEN NULLIF(d.priorizacion_hechos, '') IS NOT NULL OR d.genera_trabajo_colegiado = 1 THEN 1
        ELSE 0
    END) AS hechos_priorizados,
    SUM(CASE WHEN d.prioridad = 'ALTA' THEN 1 ELSE 0 END) AS alta_prioridad
FROM mon_cuaderno_campo c
INNER JOIN docente doc ON doc.id_docente = c.id_docente
INNER JOIN persona p ON p.id_persona = doc.id_persona
LEFT JOIN nivel_educativo ne ON ne.id_nivel = c.id_nivel
LEFT JOIN grado g ON g.id_grado = c.id_grado
LEFT JOIN seccion s ON s.id_seccion = c.id_seccion
LEFT JOIN area_curricular a ON a.id_area = c.id_area
LEFT JOIN mon_cuaderno_campo_detalle d ON d.id_cuaderno = c.id_cuaderno
GROUP BY
    c.id_cuaderno,
    c.fecha,
    YEAR(c.fecha),
    c.momento_monitoreo,
    c.estado,
    c.id_docente,
    p.numero_documento,
    COALESCE(NULLIF(p.nombre_completo, ''), TRIM(CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres))),
    c.observador_nombre,
    c.id_nivel,
    ne.descripcion,
    c.id_grado,
    g.nombre_corto,
    c.id_seccion,
    s.nombre_corto,
    c.id_area,
    a.nombre,
    c.actividad_aprendizaje,
    c.necesidad_priorizada,
    c.tema_trabajo_colegiado,
    c.fecha_trabajo_colegiado,
    c.responsable_trabajo_colegiado;