-- Parche 089
-- Referencias normalizadas y trazables de personas en los cuadernos de
-- ocurrencias e incidencias. Los textos originales de cada ficha se conservan;
-- esta tabla agrega el vínculo verificable y una instantánea del contexto al
-- momento del registro, sin exponer el documento completo en la interfaz.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS tut_libro_persona_referencia (
    id_persona_referencia BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    libro ENUM('OCURRENCIA','INCIDENCIA') NOT NULL,
    id_registro BIGINT UNSIGNED NOT NULL,
    rol VARCHAR(50) NOT NULL,
    tipo_persona ENUM('ESTUDIANTE','DOCENTE','FAMILIAR') NOT NULL,
    id_persona INT UNSIGNED NOT NULL,
    id_estudiante_contexto INT UNSIGNED NULL,
    id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
    snapshot_nombre VARCHAR(260) NOT NULL,
    snapshot_documento_mascara VARCHAR(30) NULL,
    snapshot_contexto VARCHAR(500) NULL,
    id_usuario_creador INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_persona_referencia),
    KEY idx_tut_libro_persona_registro (libro, id_registro, rol),
    KEY idx_tut_libro_persona_persona (id_persona, id_anio_lectivo),
    KEY idx_tut_libro_persona_estudiante (id_estudiante_contexto, id_anio_lectivo),
    KEY idx_tut_libro_persona_usuario (id_usuario_creador),
    CONSTRAINT fk_tut_libro_persona_persona FOREIGN KEY (id_persona)
        REFERENCES persona (id_persona) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_tut_libro_persona_estudiante FOREIGN KEY (id_estudiante_contexto)
        REFERENCES estudiante (id_estudiante) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_tut_libro_persona_anio FOREIGN KEY (id_anio_lectivo)
        REFERENCES anio_lectivo (id_anio_lectivo) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_tut_libro_persona_usuario FOREIGN KEY (id_usuario_creador)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;