-- =====================================================
-- MIGRACIÓN: Tabla de Estados de Conversación
-- Fecha: 13 de enero de 2026
-- =====================================================

-- Crear tabla user_states si no existe
CREATE TABLE IF NOT EXISTS user_states (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL,
    state VARCHAR(50) NOT NULL,
    state_data TEXT NULL COMMENT 'JSON con datos adicionales del estado',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_phone (phone_number),
    INDEX idx_state (state),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agregar columna parent_menu_id si no existe
ALTER TABLE menus 
ADD COLUMN IF NOT EXISTS parent_menu_id INT NULL COMMENT 'ID del menú padre para submenús',
ADD CONSTRAINT fk_parent_menu FOREIGN KEY (parent_menu_id) REFERENCES menus(id) ON DELETE SET NULL;

-- Agregar índice para mejorar rendimiento
ALTER TABLE autoresponses
ADD INDEX IF NOT EXISTS idx_trigger_type (trigger_type),
ADD INDEX IF NOT EXISTS idx_priority (priority DESC);

SELECT '✅ Migración completada exitosamente' as status;