-- Migración v5 - Mandatos GE Gestión Laboral
-- Agrega campos de respaldo para visualizar documento y colaborador en novedades
-- aun cuando la novedad no haya podido enlazarse a la ficha del colaborador.

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'novelties' AND COLUMN_NAME = 'documento_text'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE novelties ADD COLUMN documento_text VARCHAR(80) NULL AFTER collaborator_id',
    'SELECT "La columna novelties.documento_text ya existe" AS mensaje'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'novelties' AND COLUMN_NAME = 'colaborador_text'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE novelties ADD COLUMN colaborador_text VARCHAR(255) NULL AFTER documento_text',
    'SELECT "La columna novelties.colaborador_text ya existe" AS mensaje'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Intenta completar los campos de respaldo en novedades que sí estén enlazadas a colaboradores.
UPDATE novelties n
LEFT JOIN collaborators c ON c.id = n.collaborator_id
SET n.documento_text = COALESCE(NULLIF(n.documento_text,''), c.numero_documento),
    n.colaborador_text = COALESCE(NULLIF(n.colaborador_text,''), c.nombre_completo)
WHERE n.collaborator_id IS NOT NULL;
