-- =====================================================================
-- HC MEDICA — Migración Onboarding/Tutorial guiado
-- =====================================================================
-- Agrega 2 columnas a la tabla `usuario` para soportar el sistema de
-- onboarding (Driver.js):
--   - tutorial_visto    TINYINT(1)  — flag "ya vio el tour"
--   - tutorial_progreso TEXT        — JSON corto con el ultimo paso visto
--
-- IDEMPOTENTE: se puede ejecutar varias veces sin error. Si las columnas
-- ya existen, devuelve un mensaje informativo en lugar de fallar.
--
-- COMPATIBILIDAD DE CHARSET: el ALTER NO especifica CHARACTER SET ni
-- COLLATE, por lo que las columnas nuevas heredan el charset de la tabla
-- `usuario` (latin1 / latin1_swedish_ci segun BASE PROJECT.sql).
--
-- Fecha: 2026-05-19
-- Ejecutar en: phpMyAdmin -> BD de cada tenant -> pestana SQL
-- =====================================================================


-- ---------------------------------------------------------------------
-- 1) Agregar columna `tutorial_visto` si no existe
-- ---------------------------------------------------------------------
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'usuario'
    AND COLUMN_NAME  = 'tutorial_visto'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `usuario` ADD COLUMN `tutorial_visto` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''0=no ha visto el tour, 1=ya lo vio''',
  'SELECT ''tutorial_visto ya existe -- sin cambios'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------
-- 2) Agregar columna `tutorial_progreso` si no existe
-- ---------------------------------------------------------------------
SET @col_exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'usuario'
    AND COLUMN_NAME  = 'tutorial_progreso'
);
SET @sql := IF(@col_exists = 0,
  'ALTER TABLE `usuario` ADD COLUMN `tutorial_progreso` TEXT NULL DEFAULT NULL COMMENT ''JSON corto con el paso actual del tour''',
  'SELECT ''tutorial_progreso ya existe -- sin cambios'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------
-- 3) Verificacion final -- debe devolver 2 filas
-- ---------------------------------------------------------------------
SELECT
  COLUMN_NAME,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT,
  CHARACTER_SET_NAME,
  COLLATION_NAME,
  COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'usuario'
  AND COLUMN_NAME IN ('tutorial_visto', 'tutorial_progreso')
ORDER BY ORDINAL_POSITION;
