-- Script para arreglar la tabla user_history
-- Ejecutar este script en la base de datos si hay problemas con la migración automática

-- 1. Desactivar verificaciones de foreign keys

-- 2. Eliminar solo las foreign keys de la tabla user_history (no PRIMARY ni otros índices)
-- Obtener y eliminar solo las foreign keys reales dinámicamente
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('ALTER TABLE user_history DROP FOREIGN KEY ', CONSTRAINT_NAME) SEPARATOR '; ')
INTO @sql
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'user_history' 
AND TABLE_SCHEMA = DATABASE()
AND CONSTRAINT_NAME IS NOT NULL
AND CONSTRAINT_NAME != 'PRIMARY'
AND CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME 
    FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE TABLE_NAME = 'user_history' 
    AND TABLE_SCHEMA = DATABASE()
);

-- Ejecutar el SQL generado si hay foreign keys
SET @sql = IFNULL(@sql, 'SELECT "No foreign keys found"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. Agregar nuevas columnas si no existen
ALTER TABLE user_history ADD COLUMN IF NOT EXISTS nombre_usuario VARCHAR(100) NOT NULL DEFAULT 'Sistema' AFTER target_user_id;
ALTER TABLE user_history ADD COLUMN IF NOT EXISTS id_usuario INT AFTER nombre_usuario;

-- 3. Migrar datos existentes si hay columnas antiguas
-- Copiar datos de performed_by a nombre_usuario si existe
UPDATE user_history 
SET nombre_usuario = performed_by 
WHERE performed_by IS NOT NULL AND performed_by != '';

-- Copiar datos de performed_by_id a id_usuario si existe
UPDATE user_history 
SET id_usuario = performed_by_id 
WHERE performed_by_id IS NOT NULL AND performed_by_id > 0;

-- 4. Eliminar columnas antiguas
ALTER TABLE user_history DROP COLUMN IF EXISTS performed_by;
ALTER TABLE user_history DROP COLUMN IF EXISTS performed_by_id;

-- 5. Restaurar foreign key checks

-- 6. Verificar la estructura final
DESCRIBE user_history;
