/*
 * Migración Kodezilla -> producción exclusiva de Control de Documentos.
 * Requiere MySQL 8.0 y debe ejecutarse manualmente después de las consultas
 * de validación incluidas en el informe de entrega.
 */

/*
 * PREVALIDACIÓN MANUAL OBLIGATORIA
 *
 * SELECT VERSION();
 *
 * SELECT estatus, COUNT(*)
 * FROM documento_notificacion_x_usuarios
 * GROUP BY estatus;
 *
 * SELECT numero_version, COUNT(*)
 * FROM documento_versiones
 * GROUP BY numero_version
 * ORDER BY numero_version;
 *
 * SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
 * FROM information_schema.COLUMNS
 * WHERE TABLE_SCHEMA = DATABASE()
 *   AND TABLE_NAME IN (
 *       'documentos',
 *       'documento_versiones',
 *       'documento_notificaciones',
 *       'documento_notificacion_x_usuarios',
 *       'documento_autorizaciones'
 *   )
 * ORDER BY TABLE_NAME, ORDINAL_POSITION;
 */

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_control_documentos_exec $$
CREATE PROCEDURE sp_control_documentos_exec(
    IN ejecutar TINYINT,
    IN sentencia LONGTEXT
)
BEGIN
    IF ejecutar = 1 THEN
        SET @control_documentos_sql = sentencia;
        PREPARE control_documentos_stmt FROM @control_documentos_sql;
        EXECUTE control_documentos_stmt;
        DEALLOCATE PREPARE control_documentos_stmt;
    END IF;
END $$

DROP PROCEDURE IF EXISTS sp_control_documentos_validar $$
CREATE PROCEDURE sp_control_documentos_validar()
BEGIN
    /*
     * Impide conversiones con pérdida y la creación del índice único cuando
     * los datos históricos no cumplen las reglas del esquema de Kodezilla.
     */
    IF EXISTS (
        SELECT 1
        FROM documento_notificacion_x_usuarios
        WHERE estatus IS NOT NULL
          AND TRIM(CAST(estatus AS CHAR)) NOT IN ('0', '1')
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Control Documentos: estatus contiene valores distintos de 0/1';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM documento_firmas_versiones
        GROUP BY id_documento_version, id_usuario
        HAVING COUNT(*) > 1
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Control Documentos: existen firmas duplicadas por versión y usuario';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM documento_version_archivos_cambios AS archivo
        LEFT JOIN documento_versiones AS version
          ON version.id_documento_version = archivo.id_documento_version
        WHERE version.id_documento_version IS NULL
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Control Documentos: existen archivos de cambios sin versión';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM documento_version_archivos_cambios AS archivo
        LEFT JOIN usuarios AS usuario
          ON usuario.id_usuario = archivo.id_usuario_creador
        WHERE archivo.id_usuario_creador IS NOT NULL
          AND usuario.id_usuario IS NULL
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Control Documentos: existen archivos de cambios con usuario inválido';
    END IF;
END $$

DELIMITER ;

/* Las tablas nuevas conservan tipos, índices, motor y collation de Kodezilla. */
CREATE TABLE IF NOT EXISTS documento_dudas (
    id_documento_duda INT NOT NULL AUTO_INCREMENT,
    id_documento_version INT DEFAULT NULL,
    id_usuario INT DEFAULT NULL,
    duda TEXT,
    estatus TINYINT DEFAULT '0',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id_documento_duda)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS documento_firmas_versiones (
    id_documento_firma_version INT NOT NULL AUTO_INCREMENT,
    id_documento_version INT DEFAULT NULL,
    id_usuario INT DEFAULT NULL,
    estatus TINYINT(1) DEFAULT '0',
    fecha_firma DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    id_linea_trabajo INT DEFAULT NULL,
    PRIMARY KEY (id_documento_firma_version),
    UNIQUE KEY uq_documento_version_usuario (id_documento_version, id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS documento_x_linea_trabajo (
    id INT NOT NULL AUTO_INCREMENT,
    id_documento INT DEFAULT NULL,
    id_linea_trabajo INT DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS documento_version_archivos_cambios (
    id_documento_version_archivo_cambio INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_documento_version INT UNSIGNED NOT NULL,
    nombre_original VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    ruta_archivo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    id_usuario_creador INT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id_documento_version_archivo_cambio),
    KEY fk_documento_version_archivos_cambios_version (id_documento_version),
    KEY fk_documento_version_archivos_cambios_usuario (id_usuario_creador),
    CONSTRAINT fk_documento_version_archivos_cambios_version
        FOREIGN KEY (id_documento_version)
        REFERENCES documento_versiones (id_documento_version)
        ON DELETE CASCADE,
    CONSTRAINT fk_documento_version_archivos_cambios_usuario
        FOREIGN KEY (id_usuario_creador)
        REFERENCES usuarios (id_usuario)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Validar datos antes de modificar tipos o crear restricciones únicas. */
CALL sp_control_documentos_validar();

/* documentos */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documentos'
          AND COLUMN_NAME = 'id_usuario_creador'
    ),
    'ALTER TABLE documentos ADD COLUMN id_usuario_creador INT NULL AFTER updated_at'
);

/* documento_versiones */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_versiones'
          AND COLUMN_NAME = 'id_usuario_cambio'
    ),
    'ALTER TABLE documento_versiones ADD COLUMN id_usuario_cambio INT NULL AFTER logo_seatsa'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_versiones'
          AND COLUMN_NAME = 'id_usuario_creador'
    ),
    'ALTER TABLE documento_versiones ADD COLUMN id_usuario_creador INT NULL AFTER id_usuario_cambio'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_versiones'
          AND COLUMN_NAME = 'ruta_archivo_cambios'
    ),
    'ALTER TABLE documento_versiones ADD COLUMN ruta_archivo_cambios VARCHAR(255) NULL AFTER id_usuario_creador'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_versiones'
          AND COLUMN_NAME = 'numero_version'
          AND (
              COLUMN_TYPE <> 'varchar(50)'
              OR IS_NULLABLE <> 'YES'
              OR COLLATION_NAME <> 'utf8mb4_unicode_ci'
          )
    ),
    'ALTER TABLE documento_versiones MODIFY COLUMN numero_version VARCHAR(50) COLLATE utf8mb4_unicode_ci NULL AFTER clave'
);

/* documento_notificaciones internas */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_notificaciones'
          AND COLUMN_NAME = 'fecha'
          AND DATA_TYPE <> 'datetime'
    ),
    'ALTER TABLE documento_notificaciones MODIFY COLUMN fecha DATETIME NULL AFTER mensaje'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_notificaciones'
          AND COLUMN_NAME = 'tipo'
    ),
    'ALTER TABLE documento_notificaciones ADD COLUMN tipo ENUM(''calidad'',''difusion'',''recordatorio'') COLLATE utf8mb4_unicode_ci NULL AFTER id_documento_version'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_notificaciones'
          AND COLUMN_NAME = 'id_usuario_creador'
    ),
    'ALTER TABLE documento_notificaciones ADD COLUMN id_usuario_creador INT NULL AFTER tipo'
);

/* Relaciones internas de difusión y enterado. */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_notificacion_x_usuarios'
          AND COLUMN_NAME = 'estatus'
          AND (
              COLUMN_TYPE <> 'tinyint(1)'
              OR IS_NULLABLE <> 'YES'
              OR COALESCE(COLUMN_DEFAULT, '') <> '0'
          )
    ),
    'ALTER TABLE documento_notificacion_x_usuarios MODIFY COLUMN estatus TINYINT(1) NULL DEFAULT 0 AFTER id_documento_notificacion_x_usuario'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_notificacion_x_usuarios'
          AND COLUMN_NAME = 'fecha_enterado'
          AND DATA_TYPE <> 'datetime'
    ),
    'ALTER TABLE documento_notificacion_x_usuarios MODIFY COLUMN fecha_enterado DATETIME NULL AFTER estatus'
);

/* Autorizaciones conservan la fecha histórica y agregan precisión de hora. */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_autorizaciones'
          AND COLUMN_NAME = 'fecha_autorizacion'
          AND DATA_TYPE <> 'datetime'
    ),
    'ALTER TABLE documento_autorizaciones MODIFY COLUMN fecha_autorizacion DATETIME NULL AFTER estatus'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_autorizaciones'
          AND COLUMN_NAME = 'fecha_autorizacion_tecnico'
          AND DATA_TYPE <> 'datetime'
    ),
    'ALTER TABLE documento_autorizaciones MODIFY COLUMN fecha_autorizacion_tecnico DATETIME NULL AFTER estatus_tecnico'
);

/* Completa el índice único si la tabla ya existía sin él. */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_firmas_versiones'
          AND INDEX_NAME = 'uq_documento_version_usuario'
    ),
    'ALTER TABLE documento_firmas_versiones ADD UNIQUE KEY uq_documento_version_usuario (id_documento_version, id_usuario)'
);

/* Completa índices y FK si existía una tabla parcial de archivos de cambios. */
CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_version_archivos_cambios'
          AND INDEX_NAME = 'fk_documento_version_archivos_cambios_version'
    ),
    'ALTER TABLE documento_version_archivos_cambios ADD KEY fk_documento_version_archivos_cambios_version (id_documento_version)'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_version_archivos_cambios'
          AND INDEX_NAME = 'fk_documento_version_archivos_cambios_usuario'
    ),
    'ALTER TABLE documento_version_archivos_cambios ADD KEY fk_documento_version_archivos_cambios_usuario (id_usuario_creador)'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_version_archivos_cambios'
          AND CONSTRAINT_NAME = 'fk_documento_version_archivos_cambios_version'
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ),
    'ALTER TABLE documento_version_archivos_cambios ADD CONSTRAINT fk_documento_version_archivos_cambios_version FOREIGN KEY (id_documento_version) REFERENCES documento_versiones (id_documento_version) ON DELETE CASCADE'
);

CALL sp_control_documentos_exec(
    (
        SELECT COUNT(*) = 0
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = DATABASE()
          AND TABLE_NAME = 'documento_version_archivos_cambios'
          AND CONSTRAINT_NAME = 'fk_documento_version_archivos_cambios_usuario'
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ),
    'ALTER TABLE documento_version_archivos_cambios ADD CONSTRAINT fk_documento_version_archivos_cambios_usuario FOREIGN KEY (id_usuario_creador) REFERENCES usuarios (id_usuario) ON DELETE SET NULL'
);

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_control_documentos_validar $$
DROP PROCEDURE IF EXISTS sp_control_documentos_exec $$
DELIMITER ;