CREATE TABLE IF NOT EXISTS ai_help_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    usuario_id BIGINT UNSIGNED NOT NULL,
    titulo VARCHAR(160) NOT NULL,
    estado ENUM('activa', 'cerrada') NOT NULL DEFAULT 'activa',
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    last_activity_at DATETIME NULL,
    closed_at DATETIME NULL,
    reopened_at DATETIME NULL,
    INDEX idx_ai_help_sessions_empresa_usuario_estado (empresa_id, usuario_id, estado, id),
    INDEX idx_ai_help_sessions_closed_at (closed_at),
    CONSTRAINT fk_ai_help_sessions_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
    CONSTRAINT fk_ai_help_sessions_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @ai_help_has_session_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ai_help_messages'
      AND COLUMN_NAME = 'session_id'
);
SET @ai_help_add_session_sql := IF(
    @ai_help_has_session_id = 0,
    'ALTER TABLE ai_help_messages ADD COLUMN session_id BIGINT UNSIGNED NULL AFTER id',
    'SELECT 1'
);
PREPARE ai_help_add_session_stmt FROM @ai_help_add_session_sql;
EXECUTE ai_help_add_session_stmt;
DEALLOCATE PREPARE ai_help_add_session_stmt;

SET @ai_help_has_session_idx := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'ai_help_messages'
      AND INDEX_NAME = 'idx_ai_help_messages_session'
);
SET @ai_help_add_session_idx_sql := IF(
    @ai_help_has_session_idx = 0,
    'ALTER TABLE ai_help_messages ADD INDEX idx_ai_help_messages_session (session_id, id)',
    'SELECT 1'
);
PREPARE ai_help_add_session_idx_stmt FROM @ai_help_add_session_idx_sql;
EXECUTE ai_help_add_session_idx_stmt;
DEALLOCATE PREPARE ai_help_add_session_idx_stmt;
