# 本地终端示例 (需安装 gcloud SDK):
# gcloud compute ssh --zone "YOUR_ZONE" "YOUR_INSTANCE_NAME" --project "YOUR_PROJECT_ID"

# 1. 安装 Docker
sudo apt-get update
sudo apt-get install -y docker.io
# 启动并授权当前用户 (避免 sudo)
sudo systemctl start docker
sudo usermod -aG docker $USER
newgrp docker

# 2. 安装 MySQL 客户端 (用于连接 OceanBase)
sudo apt-get install -y default-mysql-client

# 1. 拉取最新社区版镜像 (必须确认 tag 为 4.5.0 或更新)
# 注意: 'latest' 标签可能仍指向 stable 4.3.x 版本，需手动查找最新 tag.
# 请访问 DockerHub 或 ghcr.io 查询 `oceanbase/oceanbase-ce` 的 4.5.0+ tag
docker pull oceanbase/oceanbase-ce:4.5.0.0-100000012025112711


# 2. 启动容器 (Mini 模式，适合本地开发验证)
# 注意：向量索引构建需要一定的内存，建议至少分配 8GB+ RAM 给容器
docker run -p 2881:2881 -p 8080:8080 --name ob-agent-engine \
    -e FULL_DRY_RUN=false \
    -e MODE=mini \
    -e OB_MEMORY_LIMIT=8G \
    -e OB_DATAFILE_SIZE=10G \
    -d oceanbase/oceanbase-ce:4.5.0.0-100000012025112711

# 3. 检查启动状态
docker logs -f ob-agent-engine | grep "boot success!"

# 在 VM 终端连接 OceanBase (默认为 sys 租户, 无密码)
mysql -h127.0.0.1 -P2881 -uroot

# 或者使用 Docker 内置客户端 (无需在宿主机安装 mysql-client):
docker exec -it ob-agent-engine obclient -h127.0.0.1 -P2881 -uroot

-- 1. 确认版本 (必须 >= 4.5.0)
-- ⚠️ 如果 SELECT version() 返回类似 4.3.x.x, 请更换 image
SELECT version();

-- 重置环境:
-- 1. 清理默认的 'test' 租户及其资源池 (test 租户可能已删但 pool 仍在占用 13 核 CPU)
DROP TENANT IF EXISTS test;
DROP RESOURCE POOL IF EXISTS test_pool;
DROP RESOURCE UNIT IF EXISTS test_unit;

-- 2. 清理 agent 相关旧数据
DROP TENANT IF EXISTS agent_tenant;
DROP RESOURCE POOL IF EXISTS agent_pool;
DROP RESOURCE UNIT IF EXISTS agent_unit;

-- 检查当前服务器资源状态 (排查 CPU/MEM/DISK 不足)
SELECT SVR_IP, CPU_CAPACITY, CPU_ASSIGNED, MEMORY_LIMIT, MEM_ASSIGNED, LOG_DISK_CAPACITY, LOG_DISK_ASSIGNED
FROM oceanbase.GV$OB_SERVERS;

-- 2. 创建一个支持向量的业务单元 (Unit) 和 资源池 (Resource Pool)
-- 修正:
--  a. 针对 Mini 模式 CPU 不足: 使用 MAX_CPU 1 (或 0.5)
--  b. 针对 MEMORY_SIZE 小于最小限制: 使用 2G
--  c. 针对 LOG_DISK 不足: 显式指定 2G (默认值可能不够)
CREATE RESOURCE UNIT agent_unit MAX_CPU 1, MIN_CPU 1, MEMORY_SIZE '2G', LOG_DISK_SIZE '2G';
CREATE RESOURCE POOL agent_pool UNIT = 'agent_unit', UNIT_NUM = 1;

-- 验证资源池是否创建成功
SELECT name, unit_count FROM oceanbase.DBA_OB_RESOURCE_POOLS WHERE name = 'agent_pool';

-- 3. 创建业务租户 (Tenant)
-- 注意：OceanBase 租户模式通常为 MySQL 模式
CREATE TENANT agent_tenant
    RESOURCE_POOL_LIST=('agent_pool'),
    CHARSET='utf8mb4',
    Locality='F@zone1';

-- 4. 配置访问白名单 (重要: 解决 Access denied 问题)
-- Docker 容器内这步是必须的，否则宿主机连接会被拒绝
ALTER TENANT agent_tenant SET VARIABLES ob_tcp_invited_nodes='%';

# 5. 登录业务租户 (格式: 用户名@租户名)
# 退出当前 sys 租户会话，重新连接:
mysql -h127.0.0.1 -P2881 -uroot@agent_tenant
# 或: docker exec -it ob-agent-engine obclient -h127.0.0.1 -P2881 -uroot@agent_tenant

-- 0. 创建并选择数据库 (必选, 否则报错 'No database selected')
CREATE DATABASE IF NOT EXISTS agent_db;
USE agent_db;

-- 1. 创建测试表 (验证 VECTOR 类型)
CREATE TABLE vector_test (
    id INT PRIMARY KEY,
    embedding VECTOR(3) -- 3维向量
);

-- 2. 插入向量数据
INSERT INTO vector_test VALUES (1, '[0.1, 0.2, 0.3]');
INSERT INTO vector_test VALUES (2, '[0.4, 0.5, 0.6]');
INSERT INTO vector_test VALUES (3, '[0.7, 0.8, 0.9]');

-- 3. 验证 HNSW 索引创建
-- 语法修正: 使用 CREATE VECTOR INDEX 语法，l2 (欧氏距离) 由 OceanBase 自动定义
CREATE VECTOR INDEX idx_vector_hnsw ON vector_test(embedding)
    WITH (distance=l2, type=hnsw, lib=vsag);

-- 4. 验证向量距离查询
SELECT id, l2_distance(embedding, '[0.1, 0.2, 0.3]') as dist
FROM vector_test
ORDER BY dist ASC
LIMIT 3;

-- 5. 删除索引 (清理环境)
-- 使用 show index from vector_test; 查看当前索引
-- 注意: 部分 OB 版本不支持 IF EXISTS 语法，请直接执行 DROP
-- (清理我实验留下的索引: idx_test_3)
DROP INDEX idx_test_3 ON vector_test;
-- (清理您创建的索引: idx_vector_hnsw)
DROP INDEX idx_vector_hnsw ON vector_test;

/*
 * OceanBase Unified Schema for Agentic AI
 * Mode: MySQL compatible
 */

-- 1. 会话表 (管理 Context Window 的生命周期)
CREATE TABLE memory_sessions (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    agent_id VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    metadata JSON COMMENT '存储会话级别的配置，如 Model 参数、User Profile 快照'
);

-- 2. 会话日志表 (Short-term Memory / Raw Stream)
-- 特性:
--   - 极高频写入 (Append-only)
--   - 通常按 session_id + seq_id 检索
--   - 自动过期 (TTL) 可选 (Partition by Range)
CREATE TABLE memory_logs (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(64) NOT NULL,
    seq_id INT NOT NULL COMMENT '会话内的消息序号',
    role ENUM('system', 'user', 'assistant', 'tool') NOT NULL,
    content TEXT COMMENT '原始文本内容',
    embedding VECTOR(1536) COMMENT '可选: 这里的向量用于 Context Window 的即时检索 (Short-term RAG)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_seq (session_id, seq_id)
);

-- 3. 记忆制品表 (Long-term: Episodic + Semantic)
-- 这是一张 "Memory Bank" 的核心宽表
-- 包含了 Vector (用于模糊语义检索) 和 JSON (用于精确事实检索)
CREATE TABLE memory_artifacts (
    artifact_id VARCHAR(64) PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,

    -- Memory Type 分类
    --   'episodic': 对话片段的总结 (Experience)
    --   'semantic': 事实性知识 (Fact)，如 "User likes Python"
    --   'procedural': 技能 or Tool 使用偏好
    memory_type VARCHAR(32) NOT NULL,

    -- Content & Vector
    content TEXT NOT NULL COMMENT '记忆的具体文本描述',
    embedding VECTOR(1536) NOT NULL COMMENT 'Sematic Vector (e.g. OpenAI text-embedding-3-small)',

    -- Metadata (for Filter)
    source_session_id VARCHAR(64) COMMENT '来源会话 ID (Lineage)',
    importance_score FLOAT DEFAULT 0.5 COMMENT '记忆重要性，用于 Reflection 淘汰',
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tags JSON COMMENT '标签，如 ["preference", "coding"]',

    -- Indexing
    INDEX idx_user_type (user_id, memory_type)
);

-- 4. 创建向量索引 (分离创建以确保 V4.5.0 语法兼容性)
CREATE VECTOR INDEX idx_memory_vec ON memory_artifacts (embedding)
    WITH (distance=l2, type=hnsw, lib=vsag);

-- 1. 插入模拟 Session
INSERT INTO memory_sessions (session_id, user_id, agent_id, metadata) VALUES
('sess_001', 'user_001', 'agent_alpha', '{"mode": "dev"}'),
('sess_002', 'user_001', 'agent_alpha', '{"mode": "prod"}'),
('sess_003', 'user_001', 'agent_alpha', '{"mode": "test"}');

-- 2. 插入 Short-term Logs (模拟最近的对话)
INSERT INTO memory_logs (session_id, seq_id, role, content) VALUES
('sess_001', 1, 'user', 'How do I use Python list comprehensions?'),
('sess_001', 2, 'assistant', 'You can use [x for x in iterable].'),
('sess_001', 3, 'user', 'Can I filter items too?'),
-- JOIN 验证数据:
('sess_003', 1, 'user', 'My API key is sk-12345, please remember it.'),
('sess_003', 2, 'assistant', 'I will save that securely.');

-- 3. 插入 Long-term Artifacts (模拟记忆)
-- 注意: 使用 CONCAT 和 REPEAT 生成符合 1536 维定义的 Dummy Vector (此处仅为示例，实际应由 Embedding 模型生成)
INSERT INTO memory_artifacts (artifact_id, user_id, memory_type, content, tags, embedding, source_session_id) VALUES
('mem_001', 'user_001', 'semantic', 'User prefers concise code style.', '["preference", "coding"]',
 CONCAT('[', REPEAT('0.1,', 1535), '0.1]'), 'sess_001'),
('mem_002', 'user_001', 'episodic', 'User asked about Python list optimization in previous session.', '["python", "optimization"]',
 CONCAT('[', REPEAT('0.2,', 1535), '0.2]'), 'sess_001'),
-- JOIN 验证数据 (关联 sess_003):
('mem_abc_123', 'user_001', 'episodic', 'User provided API key.', '["security", "config"]',
 CONCAT('[', REPEAT('0.3,', 1535), '0.3]'), 'sess_003');

/*
 * 场景: User A 正在问 Python 相关问题
 * 需求:
 *   1. 检索 Long-term Memory 中关于 'coding' 的偏好 (Hybrid: Tag='coding' + Vector Search)
 *   2. 关联最近 5 分钟的 Short-term Logs (Session Context)
 * 预期:
 *   OceanBase 的执行计划应能高效处理 Vector Scan + Relational Access
 */

SELECT
    m.content AS memory_content,
    m.memory_type,
    -- 计算与 Query Vector (模拟为全 0.15) 的距离
    l2_distance(m.embedding, CONCAT('[', REPEAT('0.15,', 1535), '0.15]')) as relevance
FROM
    memory_artifacts m
WHERE
    m.user_id = 'user_001'
    -- 混合检索条件 1: Metadata Filter (JSON or Column)
    AND JSON_CONTAINS(m.tags, '"coding"')
    -- 混合检索条件 2: Vector Search
    AND l2_distance(m.embedding, CONCAT('[', REPEAT('0.15,', 1535), '0.15]')) < 5.0
ORDER BY
    relevance ASC
LIMIT 5;

-- 联合查询示例 (Join Log for Context)
-- 实际场景通常分别查询，但 OB 支持 Join
-- 假设 memory_artifacts 记录了 source_session_id
SELECT
    m.content as insight,
    l.content as source_chat
FROM
    memory_artifacts m
JOIN
    memory_logs l ON m.source_session_id = l.session_id
WHERE
    m.artifact_id = 'mem_abc_123'
LIMIT 10;

-- 1. 准备数据: 创建会话与对话流 (使用 IGNORE 避免重复插入)
INSERT IGNORE INTO memory_sessions (session_id, user_id, agent_id) VALUES ('sess_chat_001', 'user_alice', 'agent_chat');
INSERT INTO memory_logs (session_id, seq_id, role, content) VALUES
('sess_chat_001', 1, 'user', 'Hello, who are you?'),
('sess_chat_001', 2, 'assistant', 'I am your AI assistant.'),
('sess_chat_001', 3, 'user', 'Tell me a joke.');

-- 2. 验证查询: 重建 Context (Top-K Messages)
SELECT role, content
FROM memory_logs
WHERE session_id = 'sess_chat_001'
ORDER BY seq_id ASC
LIMIT 10;

-- 1. 准备数据: 假设 Agent 分析出 Alice 是素食者
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, memory_type, content, embedding) VALUES
('fact_u001_01', 'user_alice', 'semantic', 'User is vegetarian.',
 CONCAT('[', REPEAT('0.1,', 1535), '0.1]')); -- 模拟向量

-- 2. 验证查询: 检索用户偏好 (Vector Search)
-- Query: "Suggest a dinner place" -> Embedding: [0.1...]
SELECT content, l2_distance(embedding, CONCAT('[', REPEAT('0.1,', 1535), '0.1]')) as dist
FROM memory_artifacts
WHERE user_id = 'user_alice' AND memory_type = 'semantic'
ORDER BY dist ASC
LIMIT 1;

-- 1. 准备数据: 插入摘要
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, source_session_id, memory_type, content, embedding) VALUES
('episode_sess_001', 'user_alice', 'sess_chat_001', 'episodic',
 'In session sess_chat_001, user asked for jokes and introductions.',
 CONCAT('[', REPEAT('0.2,', 1535), '0.2]'));

-- 2. 验证查询: 查找历史对话摘要
SELECT content, source_session_id
FROM memory_artifacts
WHERE user_id = 'user_alice' AND memory_type = 'episodic';

-- 1. 更新状态: 记录当前步骤
UPDATE memory_sessions
SET metadata = JSON_MERGE_PATCH(COALESCE(metadata, '{}'), '{"current_step": "approval_pending", "draft_id": "doc_123"}')
WHERE session_id = 'sess_chat_001';

-- 2. 验证查询: 读取断点状态以恢复执行
SELECT session_id, JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.current_step')) as status
FROM memory_sessions
WHERE session_id = 'sess_chat_001';

-- 1. 准备数据: 记录一次成功的工具调用
INSERT INTO memory_logs (session_id, seq_id, role, content) VALUES
('sess_chat_001', 4, 'tool', '{"tool_name": "weather_api", "params": {"city": "Paris"}, "status": "success"}');

-- 2. 提取为 Procedural Artifact
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, memory_type, content, tags, embedding) VALUES
('proc_001', 'user_alice', 'procedural', 'Use weather_api with city name for forecast.', '["tool", "weather"]',
 CONCAT('[', REPEAT('0.3,', 1535), '0.3]'));

-- 3. 验证查询: 检索相关工具经验
SELECT content
FROM memory_artifacts
WHERE JSON_CONTAINS(tags, '"tool"');

-- 假设我们将外部知识也存入 artifacts，type为 'knowledge'
-- 0. 扩展类型定义 (若采用 ENUM 严格模式)
-- ALTER TABLE memory_artifacts MODIFY COLUMN memory_type ENUM('episodic', 'semantic', 'procedural', 'knowledge') NOT NULL;

-- 1. 准备数据: 插入知识片段
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, memory_type, content, embedding) VALUES
('kb_doc_001_chunk1', 'system_global', 'knowledge', 'OceanBase V4.5 supports native vector types.',
 CONCAT('[', REPEAT('0.8,', 1535), '0.8]'));

-- 2. 验证查询: 知识库问答
SELECT content
FROM memory_artifacts
WHERE user_id = 'system_global' -- 假设 public data 归属 system
ORDER BY l2_distance(embedding, CONCAT('[', REPEAT('0.8,', 1535), '0.8]')) ASC
LIMIT 1;

-- 1. 准备数据: Team Session
INSERT IGNORE INTO memory_sessions (session_id, user_id, agent_id) VALUES ('sess_team_001', 'sub-task-1', 'squad-leader');
INSERT INTO memory_logs (session_id, seq_id, role, content) VALUES
('sess_team_001', 1, 'user', 'Build a snake game.'),
('sess_team_001', 2, 'assistant', '[Planner]: Designed architecture.'),
('sess_team_001', 3, 'assistant', '[Coder]: Implemented main loop.');

-- 2. 验证查询: 获取完整协作流
SELECT content FROM memory_logs WHERE session_id = 'sess_team_001' ORDER BY seq_id;

-- 1. 验证查询: 查询最近 5 分钟的操作日志
SELECT *
FROM memory_logs
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ORDER BY created_at DESC;

-- 0. 准备数据: 插入旧的偏好 (Likes spicy)
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, memory_type, content, importance_score, embedding) VALUES
('fact_u001_old', 'user_alice', 'semantic', 'User likes spicy food.', 0.8,
 CONCAT('[', REPEAT('0.9,', 1535), '0.9]'));

-- 1. 操作: 降低旧记忆权重 (Soft Delete)
UPDATE memory_artifacts
SET importance_score = 0.1
WHERE user_id = 'user_alice' AND content LIKE '%likes spicy%';

-- 2. 操作: 插入新事实
INSERT IGNORE INTO memory_artifacts (artifact_id, user_id, memory_type, content, embedding) VALUES
('fact_u001_02', 'user_alice', 'semantic', 'User dislikes spicy food.',
 CONCAT('[', REPEAT('0.15,', 1535), '0.15]'));

-- 3. 验证查询: 只取高权重记忆
SELECT content FROM memory_artifacts
WHERE user_id = 'user_alice' AND importance_score > 0.4;

-- 1. 操作: 用户点赞，增加关联 Artifact 的权重
UPDATE memory_artifacts
SET importance_score = importance_score + 0.1
WHERE artifact_id = 'fact_u001_02';

-- 2. 验证查询: 确认权重更新
SELECT importance_score FROM memory_artifacts WHERE artifact_id = 'fact_u001_02';

/**
 * Global Cleanup Script
 * Warning: This will delete all data created by the verification suite.
 */

-- 1. 清理 Sessions (级联清理 Logs 如果有外键，但此处 Schema 无外键约束)
DELETE FROM memory_sessions WHERE session_id IN ('sess_chat_001', 'sess_team_001');

-- 2. 清理 Logs
DELETE FROM memory_logs WHERE session_id IN ('sess_chat_001', 'sess_team_001');

-- 3. 清理 Artifacts
DELETE FROM memory_artifacts WHERE user_id IN ('user_alice', 'system_global') OR artifact_id LIKE 'proc_%';