-- Migración v6 - Mandatos GE Gestión Laboral
-- Agrega logo de empresa cliente y campos de respaldo de novedades.
-- Ejecutar en phpMyAdmin sobre la base de datos del aplicativo.

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'logo_nombre'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE companies ADD COLUMN logo_nombre VARCHAR(255) NULL AFTER estado',
    'SELECT "La columna companies.logo_nombre 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 = 'companies' AND COLUMN_NAME = 'logo_ruta'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE companies ADD COLUMN logo_ruta VARCHAR(255) NULL AFTER logo_nombre',
    'SELECT "La columna companies.logo_ruta 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 = '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;

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;
