# 查询（SELECT）
alias rq='curl -s -G localhost:4001/db/query --data-urlencode'

# 执行（INSERT/UPDATE/DELETE/DDL）
alias rxe='curl -s -XPOST localhost:4001/db/execute -H "Content-Type: application/json" -d'

# 创建用户表
rxe '[["CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)"]]'

{
    "results": [{"time": 0.000345}]
}

# 单列索引
rxe '[["CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"]]'

# 复合索引
rxe '[["CREATE INDEX IF NOT EXISTS idx_users_age_name ON users(age, username)"]]'

# 唯一索引
rxe '[["CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username)"]]'

rxe '[["CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER CHECK(quantity > 0),
    price REAL CHECK(price >= 0),
    status TEXT CHECK(status IN (\"pending\", \"paid\", \"shipped\", \"completed\", \"cancelled\")) DEFAULT \"pending\",
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)"]]'

# 使用参数绑定（推荐，防止 SQL 注入）
rxe '[["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "zhangsan", "zhangsan@example.com", 28]]'

{
    "results": [
        {
            "last_insert_id": 1,
            "rows_affected": 1,
            "time": 0.000456
        }
    ]
}

rxe '[
    ["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "lisi", "lisi@example.com", 32],
    ["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "wangwu", "wangwu@example.com", 25],
    ["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "zhaoliu", "zhaoliu@example.com", 30],
    ["INSERT INTO users (username, email, age) VALUES (?, ?, ?)", "sunqi", "sunqi@example.com", 22]
]'

{
    "results": [
        {"last_insert_id": 2, "rows_affected": 1, "time": 0.000123},
        {"last_insert_id": 3, "rows_affected": 1, "time": 0.000112},
        {"last_insert_id": 4, "rows_affected": 1, "time": 0.000109},
        {"last_insert_id": 5, "rows_affected": 1, "time": 0.000115}
    ],
    "time": 0.000500
}

# 如果记录已存在（主键或唯一约束冲突），则替换
rxe '[["INSERT OR REPLACE INTO users (id, username, email, age) VALUES (?, ?, ?, ?)", 1, "zhangsan", "new_email@example.com", 29]]'

# 如果记录已存在，则忽略
rxe '[["INSERT OR IGNORE INTO users (username, email, age) VALUES (?, ?, ?)", "zhangsan", "another@example.com", 30]]'

# 查询所有用户
rq 'q=SELECT * FROM users' | python3 -m json.tool

{
    "results": [
        {
            "columns": ["id", "username", "email", "age", "created_at"],
            "types": ["integer", "text", "text", "integer", "datetime"],
            "values": [
                [1, "zhangsan", "new_email@example.com", 29, "2026-05-10 10:00:00"],
                [2, "lisi", "lisi@example.com", 32, "2026-05-10 10:00:01"],
                [3, "wangwu", "wangwu@example.com", 25, "2026-05-10 10:00:01"],
                [4, "zhaoliu", "zhaoliu@example.com", 30, "2026-05-10 10:00:01"],
                [5, "sunqi", "sunqi@example.com", 22, "2026-05-10 10:00:01"]
            ]
        }
    ],
    "time": 0.000234
}

# WHERE 条件
rq 'q=SELECT * FROM users WHERE age > 25'

# LIKE 模糊匹配
rq 'q=SELECT * FROM users WHERE username LIKE "zhang%"'

# IN 操作符
rq 'q=SELECT * FROM users WHERE id IN (1, 3, 5)'

# 排序
rq 'q=SELECT * FROM users ORDER BY age DESC'

# 分页（LIMIT + OFFSET）
rq 'q=SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 0'
rq 'q=SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 2'

# 计数
rq 'q=SELECT COUNT(*) as total FROM users'

# 按条件分组
rq 'q=SELECT age, COUNT(*) as count FROM users GROUP BY age ORDER BY count DESC'

# 平均值
rq 'q=SELECT AVG(age) as avg_age FROM users'

# 最大值和最小值
rq 'q=SELECT MAX(age) as max_age, MIN(age) as min_age FROM users'

# 先插入一些订单数据
rxe '[
    ["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 1, "笔记本", 2, 5999.0, "completed"],
    ["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 1, "鼠标", 5, 99.0, "shipped"],
    ["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 2, "键盘", 1, 399.0, "pending"],
    ["INSERT INTO orders (user_id, product, quantity, price, status) VALUES (?, ?, ?, ?, ?)", 3, "显示器", 1, 2499.0, "paid"]
]'

# JOIN 查询：用户及其订单
rq 'q=SELECT u.username, o.product, o.quantity, o.price, o.status
     FROM users u
     INNER JOIN orders o ON u.id = o.user_id
     ORDER BY u.username'

# 查找有订单的用户
rq 'q=SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders)'

# 查找消费总额最高的用户
rq 'q=SELECT u.username, SUM(o.quantity * o.price) as total_spent
     FROM users u
     INNER JOIN orders o ON u.id = o.user_id
     GROUP BY u.id
     ORDER BY total_spent DESC
     LIMIT 1'

# 更新单条记录
rxe '[["UPDATE users SET email = ? WHERE id = ?", "zs@new.com", 1]]'

# 同一事务中更新多条
rxe '[
    ["UPDATE users SET age = age + 1 WHERE age < 30"],
    ["UPDATE orders SET status = \"completed\" WHERE status = \"shipped\""]
]'

# 使用 CASE 表达式
rxe '[["UPDATE orders SET price = CASE
    WHEN quantity > 3 THEN price * 0.8
    WHEN quantity > 1 THEN price * 0.9
    ELSE price
END WHERE status != \"completed\""]]'

# 删除特定记录
rxe '[["DELETE FROM orders WHERE status = \"cancelled\""]]'

# 删除所有记录（保留表结构）
rxe '[["DELETE FROM orders"]]'

# 删除用户及其关联订单（需外键约束）
rxe '[["DELETE FROM users WHERE id = 5"]]'

# 查看所有表
rq 'q=SELECT name FROM sqlite_master WHERE type=\"table\" AND name NOT LIKE \"sqlite_%\"'

# 查看特定表的 DDL
rq 'q=SELECT sql FROM sqlite_master WHERE name=\"users\"'

# PRAGMA 方式查看表信息
rq 'q=PRAGMA table_info(users)'

# 重命名表
rxe '[["ALTER TABLE orders RENAME TO customer_orders"]]'

# 添加列
rxe '[["ALTER TABLE users ADD COLUMN phone TEXT"]]'

# 注意：SQLite 不支持 DROP COLUMN（3.35.0 前）
# 如需删除列或修改列类型，需要重建表

rxe '[
    ["CREATE TABLE users_new (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL, age INTEGER DEFAULT 0, phone TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP)"],
    ["INSERT INTO users_new SELECT id, username, email, age, phone, created_at FROM users"],
    ["DROP TABLE users"],
    ["ALTER TABLE users_new RENAME TO users"]
]'

rxe '[["DROP TABLE IF EXISTS customer_orders"]]'

# 删除所有表
rq 'q=SELECT name FROM sqlite_master WHERE type="table"' | \
    python3 -c "
import json, sys
data = json.load(sys.stdin)
tables = data['results'][0]['values']
stmts = [[f'DROP TABLE IF EXISTS {t[0]}'] for t in tables]
print(json.dumps(stmts))
" | curl -s -XPOST localhost:4001/db/execute \
    -H 'Content-Type: application/json' -d @-

# 查看当前 journal 模式
rq 'q=PRAGMA journal_mode'

# 查看页面大小
rq 'q=PRAGMA page_size'

# 查看缓存大小
rq 'q=PRAGMA cache_size'

# 查看所有 PRAGMA 设置
rq 'q=PRAGMA compile_options'

# 1. 创建表
rxe '[
    ["CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, slug TEXT NOT NULL UNIQUE)"],
    ["CREATE TABLE IF NOT EXISTS articles (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, content TEXT, category_id INTEGER, status TEXT CHECK(status IN (\"draft\", \"published\", \"archived\")) DEFAULT \"draft\", view_count INTEGER DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id))"],
    ["CREATE TABLE IF NOT EXISTS comments (id INTEGER PRIMARY KEY AUTOINCREMENT, article_id INTEGER NOT NULL, author TEXT NOT NULL, content TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE)"],
    ["CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status)",
     "CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category_id)",
     "CREATE INDEX IF NOT EXISTS idx_comments_article ON comments(article_id)"]
]'

# 2. 插入分类
rxe '[
    ["INSERT INTO categories (name, slug) VALUES (?, ?)", "技术", "tech"],
    ["INSERT INTO categories (name, slug) VALUES (?, ?)", "随笔", "essay"],
    ["INSERT INTO categories (name, slug) VALUES (?, ?)", "教程", "tutorial"]
]'

# 3. 插入文章
rxe '[["INSERT INTO articles (title, slug, content, category_id, status) VALUES (?, ?, ?, ?, ?)", "rqlite 入门", "rqlite-intro", "本文介绍 rqlite 的基本概念...", 1, "published"]]'

# 4. 查询已发布文章列表
rq 'q=SELECT a.title, a.view_count, c.name as category FROM articles a LEFT JOIN categories c ON a.category_id = c.id WHERE a.status = \"published\" ORDER BY a.created_at DESC'