-- Ajuste v12 - Validaciones de vinculación en ficha de colaborador
-- Ejecutar en phpMyAdmin sobre la base del aplicativo si las columnas no se crean automáticamente.

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='estudio_seguridad_resultado');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN estudio_seguridad_resultado VARCHAR(80) NULL', 'SELECT "collaborators.estudio_seguridad_resultado 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='collaborators' AND COLUMN_NAME='estudio_seguridad_fecha');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN estudio_seguridad_fecha DATE NULL', 'SELECT "collaborators.estudio_seguridad_fecha 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='collaborators' AND COLUMN_NAME='estudio_seguridad_nombre');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN estudio_seguridad_nombre VARCHAR(255) NULL', 'SELECT "collaborators.estudio_seguridad_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='collaborators' AND COLUMN_NAME='estudio_seguridad_ruta');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN estudio_seguridad_ruta VARCHAR(255) NULL', 'SELECT "collaborators.estudio_seguridad_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='collaborators' AND COLUMN_NAME='estudio_seguridad_observacion');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN estudio_seguridad_observacion TEXT NULL', 'SELECT "collaborators.estudio_seguridad_observacion 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='collaborators' AND COLUMN_NAME='aceptacion_condiciones_estado');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_estado VARCHAR(80) NULL', 'SELECT "collaborators.aceptacion_condiciones_estado 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='collaborators' AND COLUMN_NAME='aceptacion_condiciones_fecha');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_fecha DATE NULL', 'SELECT "collaborators.aceptacion_condiciones_fecha 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='collaborators' AND COLUMN_NAME='aceptacion_condiciones_nombre');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_nombre VARCHAR(255) NULL', 'SELECT "collaborators.aceptacion_condiciones_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='collaborators' AND COLUMN_NAME='aceptacion_condiciones_ruta');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_ruta VARCHAR(255) NULL', 'SELECT "collaborators.aceptacion_condiciones_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='collaborators' AND COLUMN_NAME='aceptacion_condiciones_observacion');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_observacion TEXT NULL', 'SELECT "collaborators.aceptacion_condiciones_observacion ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
