-- Migración: habilitar respuestas dinámicas por pregunta (JSON)
-- Útil si tu tabla respuestas_encuesta fue creada con columnas fijas NOT NULL.
-- Recomendación: haz un backup antes.

-- 1) Agregar columnas nuevas si no existen
SET @t := 'respuestas_encuesta';

SET @c := 'respuestas_json';
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @t AND COLUMN_NAME = @c);
SET @sql := IF(@exists = 0, CONCAT('ALTER TABLE `', @t, '` ADD COLUMN `', @c, '` LONGTEXT NULL'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := 'sucursal_id';
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @t AND COLUMN_NAME = @c);
SET @sql := IF(@exists = 0, CONCAT('ALTER TABLE `', @t, '` ADD COLUMN `', @c, '` INT UNSIGNED NULL'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := 'preguntas_count';
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @t AND COLUMN_NAME = @c);
SET @sql := IF(@exists = 0, CONCAT('ALTER TABLE `', @t, '` ADD COLUMN `', @c, '` INT UNSIGNED NOT NULL DEFAULT 0'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c := 'sucursal';
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @t AND COLUMN_NAME = @c);
SET @sql := IF(@exists = 0, CONCAT('ALTER TABLE `', @t, '` ADD COLUMN `', @c, '` VARCHAR(120) NULL'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Relajar NOT NULL en columnas históricas (si existen) para no bloquear inserts
-- Si tu instalación ya no usa estas columnas, es correcto que queden NULL.
ALTER TABLE `respuestas_encuesta`
  MODIFY COLUMN `calificacion_servicio` TINYINT UNSIGNED NULL,
  MODIFY COLUMN `calificacion_higiene` TINYINT UNSIGNED NULL,
  MODIFY COLUMN `calificacion_atencion_usuario` TINYINT UNSIGNED NULL,
  MODIFY COLUMN `calificacion_atencion_doctores` TINYINT UNSIGNED NULL,
  MODIFY COLUMN `calificacion_atencion_terapeutas` TINYINT UNSIGNED NULL;
