-- Imágenes de firma privadas y versionadas para los cuadernos de Tutoría.
--
-- Una imagen de firma es una evidencia de conformidad: no constituye por sí
-- sola una firma digital con certificado criptográfico. Cada reemplazo crea
-- una versión nueva y conserva la versión anterior como no vigente.

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tut_ocurrencia_firma (
    id_ocurrencia_firma BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_ocurrencia BIGINT UNSIGNED NOT NULL,
    rol_firma VARCHAR(50) NOT NULL,
    version SMALLINT UNSIGNED NOT NULL,
    firmante_nombre VARCHAR(220) NOT NULL,
    firmante_documento VARCHAR(20) NULL,
    fecha_firma DATE NOT NULL,
    consentimiento_confirmado TINYINT(1) NOT NULL DEFAULT 0,
    consentimiento_texto VARCHAR(500) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(700) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    tamanio_bytes INT UNSIGNED NOT NULL,
    ancho_px INT UNSIGNED NOT NULL,
    alto_px INT UNSIGNED NOT NULL,
    sha256 CHAR(64) NOT NULL,
    es_vigente TINYINT(1) NOT NULL DEFAULT 1,
    reemplaza_id BIGINT UNSIGNED NULL,
    revocado_en DATETIME NULL,
    motivo_revocacion VARCHAR(300) NULL,
    id_usuario_creador INT UNSIGNED NULL,
    id_usuario_revoca INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_ocurrencia_firma),
    UNIQUE KEY uq_tut_ocurrencia_firma_version (id_ocurrencia, rol_firma, version),
    KEY idx_tut_ocurrencia_firma_vigente (id_ocurrencia, rol_firma, es_vigente),
    KEY idx_tut_ocurrencia_firma_sha256 (sha256),
    KEY idx_tut_ocurrencia_firma_reemplaza (reemplaza_id),
    CONSTRAINT fk_tut_ocurrencia_firma_ocurrencia FOREIGN KEY (id_ocurrencia)
        REFERENCES tut_ocurrencia (id_ocurrencia) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tut_ocurrencia_firma_reemplaza FOREIGN KEY (reemplaza_id)
        REFERENCES tut_ocurrencia_firma (id_ocurrencia_firma) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_ocurrencia_firma_creador FOREIGN KEY (id_usuario_creador)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_ocurrencia_firma_revoca FOREIGN KEY (id_usuario_revoca)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tut_ocurrencia_seguimiento_firma (
    id_ocurrencia_seguimiento_firma BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_ocurrencia_seguimiento BIGINT UNSIGNED NOT NULL,
    rol_firma VARCHAR(50) NOT NULL,
    version SMALLINT UNSIGNED NOT NULL,
    firmante_nombre VARCHAR(220) NOT NULL,
    firmante_documento VARCHAR(20) NULL,
    fecha_firma DATE NOT NULL,
    consentimiento_confirmado TINYINT(1) NOT NULL DEFAULT 0,
    consentimiento_texto VARCHAR(500) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(700) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    tamanio_bytes INT UNSIGNED NOT NULL,
    ancho_px INT UNSIGNED NOT NULL,
    alto_px INT UNSIGNED NOT NULL,
    sha256 CHAR(64) NOT NULL,
    es_vigente TINYINT(1) NOT NULL DEFAULT 1,
    reemplaza_id BIGINT UNSIGNED NULL,
    revocado_en DATETIME NULL,
    motivo_revocacion VARCHAR(300) NULL,
    id_usuario_creador INT UNSIGNED NULL,
    id_usuario_revoca INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_ocurrencia_seguimiento_firma),
    UNIQUE KEY uq_tut_occ_seg_firma_version (id_ocurrencia_seguimiento, rol_firma, version),
    KEY idx_tut_occ_seg_firma_vigente (id_ocurrencia_seguimiento, rol_firma, es_vigente),
    KEY idx_tut_occ_seg_firma_sha256 (sha256),
    KEY idx_tut_occ_seg_firma_reemplaza (reemplaza_id),
    CONSTRAINT fk_tut_occ_seg_firma_seguimiento FOREIGN KEY (id_ocurrencia_seguimiento)
        REFERENCES tut_ocurrencia_seguimiento (id_ocurrencia_seguimiento) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tut_occ_seg_firma_reemplaza FOREIGN KEY (reemplaza_id)
        REFERENCES tut_ocurrencia_seguimiento_firma (id_ocurrencia_seguimiento_firma) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_occ_seg_firma_creador FOREIGN KEY (id_usuario_creador)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_occ_seg_firma_revoca FOREIGN KEY (id_usuario_revoca)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tut_incidencia_firma (
    id_incidencia_firma BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_incidencia INT UNSIGNED NOT NULL,
    rol_firma VARCHAR(50) NOT NULL,
    version SMALLINT UNSIGNED NOT NULL,
    firmante_nombre VARCHAR(220) NOT NULL,
    firmante_documento VARCHAR(20) NULL,
    fecha_firma DATE NOT NULL,
    consentimiento_confirmado TINYINT(1) NOT NULL DEFAULT 0,
    consentimiento_texto VARCHAR(500) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(700) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    tamanio_bytes INT UNSIGNED NOT NULL,
    ancho_px INT UNSIGNED NOT NULL,
    alto_px INT UNSIGNED NOT NULL,
    sha256 CHAR(64) NOT NULL,
    es_vigente TINYINT(1) NOT NULL DEFAULT 1,
    reemplaza_id BIGINT UNSIGNED NULL,
    revocado_en DATETIME NULL,
    motivo_revocacion VARCHAR(300) NULL,
    id_usuario_creador INT UNSIGNED NULL,
    id_usuario_revoca INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_incidencia_firma),
    UNIQUE KEY uq_tut_incidencia_firma_version (id_incidencia, rol_firma, version),
    KEY idx_tut_incidencia_firma_vigente (id_incidencia, rol_firma, es_vigente),
    KEY idx_tut_incidencia_firma_sha256 (sha256),
    KEY idx_tut_incidencia_firma_reemplaza (reemplaza_id),
    CONSTRAINT fk_tut_incidencia_firma_incidencia FOREIGN KEY (id_incidencia)
        REFERENCES tut_incidencia (id_incidencia) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tut_incidencia_firma_reemplaza FOREIGN KEY (reemplaza_id)
        REFERENCES tut_incidencia_firma (id_incidencia_firma) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_incidencia_firma_creador FOREIGN KEY (id_usuario_creador)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_incidencia_firma_revoca FOREIGN KEY (id_usuario_revoca)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tut_incidencia_seguimiento_firma (
    id_incidencia_seguimiento_firma BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_incidencia_seguimiento BIGINT UNSIGNED NOT NULL,
    rol_firma VARCHAR(50) NOT NULL,
    version SMALLINT UNSIGNED NOT NULL,
    firmante_nombre VARCHAR(220) NOT NULL,
    firmante_documento VARCHAR(20) NULL,
    fecha_firma DATE NOT NULL,
    consentimiento_confirmado TINYINT(1) NOT NULL DEFAULT 0,
    consentimiento_texto VARCHAR(500) NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(700) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    tamanio_bytes INT UNSIGNED NOT NULL,
    ancho_px INT UNSIGNED NOT NULL,
    alto_px INT UNSIGNED NOT NULL,
    sha256 CHAR(64) NOT NULL,
    es_vigente TINYINT(1) NOT NULL DEFAULT 1,
    reemplaza_id BIGINT UNSIGNED NULL,
    revocado_en DATETIME NULL,
    motivo_revocacion VARCHAR(300) NULL,
    id_usuario_creador INT UNSIGNED NULL,
    id_usuario_revoca INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_incidencia_seguimiento_firma),
    UNIQUE KEY uq_tut_inc_seg_firma_version (id_incidencia_seguimiento, rol_firma, version),
    KEY idx_tut_inc_seg_firma_vigente (id_incidencia_seguimiento, rol_firma, es_vigente),
    KEY idx_tut_inc_seg_firma_sha256 (sha256),
    KEY idx_tut_inc_seg_firma_reemplaza (reemplaza_id),
    CONSTRAINT fk_tut_inc_seg_firma_seguimiento FOREIGN KEY (id_incidencia_seguimiento)
        REFERENCES tut_incidencia_seguimiento (id_incidencia_seguimiento) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tut_inc_seg_firma_reemplaza FOREIGN KEY (reemplaza_id)
        REFERENCES tut_incidencia_seguimiento_firma (id_incidencia_seguimiento_firma) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_inc_seg_firma_creador FOREIGN KEY (id_usuario_creador)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_tut_inc_seg_firma_revoca FOREIGN KEY (id_usuario_revoca)
        REFERENCES usuario_sistema (id_usuario) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;