#!/usr/bin/env bash
# ============================================================
# 第 18 章 · clickhouse-local 演示脚本
# ------------------------------------------------------------
# 干什么:
#   1) 生成一份「nginx access log 样例」(100 行)
#   2) 用 clickhouse-local 跑 6 段不同形态的 SQL 演示, 完全无需 server
#   3) 输出转 Parquet, 演示「文件即表 + 一行命令转格式」
#
# 前置:
#   - 已安装 clickhouse-local 或 clickhouse 多功能二进制
#     curl https://clickhouse.com/ | sh           # 在当前目录得到 ./clickhouse
#     export CHL="./clickhouse local"   或   alias CHL="clickhouse local"
#
# 用法:
#   bash local_demo.sh
# ============================================================
set -euo pipefail

# 优先用 PATH 上的 clickhouse-local; 没有就用 ./clickhouse local
if command -v clickhouse-local >/dev/null 2>&1; then
  CHL="clickhouse-local"
elif command -v clickhouse >/dev/null 2>&1; then
  CHL="clickhouse local"
elif [[ -x "./clickhouse" ]]; then
  CHL="./clickhouse local"
else
  echo "[ERROR] 找不到 clickhouse-local. 请先 'curl https://clickhouse.com/ | sh'" >&2
  exit 1
fi

WORKDIR="$(mktemp -d -t ch_local_demo_XXXX)"
LOG="${WORKDIR}/access.log"
PARQUET="${WORKDIR}/access.parquet"

echo "==================================================="
echo "工作目录: ${WORKDIR}"
echo "==================================================="

# ------------------------------------------------------------
# 1) 生成 100 行 nginx 风格的 access.log
# ------------------------------------------------------------
echo
echo "[Step 1] 生成 100 行 nginx access.log → ${LOG}"
cat > "${LOG}" <<'EOF_HEAD'
EOF_HEAD

PATHS=("/api/order" "/api/order" "/api/order" "/api/user" "/api/cart" "/api/pay" "/static/js/app.js" "/health")
STATUSES=(200 200 200 200 200 200 304 404 500 502 503)
METHODS=(GET GET GET POST POST DELETE)

for i in $(seq 1 100); do
  ip="$((RANDOM % 200 + 1)).$((RANDOM % 255)).$((RANDOM % 255)).$((RANDOM % 255))"
  ts_offset=$((i * 30))
  ts="$(date -u -d "${ts_offset} seconds ago" +'%d/%b/%Y:%H:%M:%S +0000' 2>/dev/null \
        || date -u -v "-${ts_offset}S" +'%d/%b/%Y:%H:%M:%S +0000')"
  m="${METHODS[$((RANDOM % ${#METHODS[@]}))]}"
  p="${PATHS[$((RANDOM % ${#PATHS[@]}))]}"
  s="${STATUSES[$((RANDOM % ${#STATUSES[@]}))]}"
  size=$((RANDOM % 8000 + 100))
  echo "${ip} - - [${ts}] \"${m} ${p} HTTP/1.1\" ${s} ${size} \"-\" \"curl/7.68\"" >> "${LOG}"
done
wc -l "${LOG}"
head -3 "${LOG}"

# ------------------------------------------------------------
# 2) 演示 SQL ① 总行数
# ------------------------------------------------------------
echo
echo "[Step 2] 总行数"
$CHL --query "
SELECT count() AS total
FROM file('${LOG}', LineAsString, 'line String')
"

# ------------------------------------------------------------
# 3) 演示 SQL ② 状态码分布 Top
# ------------------------------------------------------------
echo
echo "[Step 3] HTTP 状态码分布 Top"
$CHL --query "
WITH file('${LOG}', LineAsString, 'line String') AS T
SELECT
    extract(line, ' (\\d{3}) ') AS status,
    count() AS cnt
FROM T
GROUP BY status
ORDER BY cnt DESC
FORMAT PrettyCompact
"

# ------------------------------------------------------------
# 4) 演示 SQL ③ 5xx 路径 Top 10 (生产场景)
# ------------------------------------------------------------
echo
echo "[Step 4] 5xx 失败接口 Top 10"
$CHL --query "
WITH parsed AS (
    SELECT
        extract(line, ' \"(?:GET|POST|PUT|DELETE) ([^ ?]+)') AS path,
        toUInt16OrZero(extract(line, ' (\\d{3}) ')) AS status
    FROM file('${LOG}', LineAsString, 'line String')
)
SELECT path, count() AS errors
FROM parsed
WHERE status >= 500
GROUP BY path
ORDER BY errors DESC
LIMIT 10
FORMAT PrettyCompact
"

# ------------------------------------------------------------
# 5) 演示 SQL ④ 按方法统计平均响应字节
# ------------------------------------------------------------
echo
echo "[Step 5] 按 HTTP 方法的平均响应字节"
$CHL --query "
SELECT
    extract(line, ' \"([A-Z]+) ') AS method,
    round(avg(toUInt32OrZero(extract(line, ' (\\d{3}) (\\d+) ')))) AS avg_bytes,
    count() AS cnt
FROM file('${LOG}', LineAsString, 'line String')
GROUP BY method
ORDER BY cnt DESC
FORMAT PrettyCompact
"

# ------------------------------------------------------------
# 6) 演示 SQL ⑤ 转 Parquet 落盘 (一行命令)
# ------------------------------------------------------------
echo
echo "[Step 6] 把 log 解析成结构化 Parquet → ${PARQUET}"
$CHL --query "
SELECT
    extract(line, '^([^ ]+) ') AS ip,
    parseDateTimeBestEffortOrNull(extract(line, '\\[([^\\]]+)\\]')) AS ts,
    extract(line, ' \"([A-Z]+) ') AS method,
    extract(line, ' \"(?:GET|POST|PUT|DELETE) ([^ ?]+)') AS path,
    toUInt16OrZero(extract(line, ' (\\d{3}) ')) AS status,
    toUInt32OrZero(extract(line, ' \\d{3} (\\d+) ')) AS bytes
FROM file('${LOG}', LineAsString, 'line String')
FORMAT Parquet
" > "${PARQUET}"
ls -lh "${PARQUET}"

# ------------------------------------------------------------
# 7) 演示 SQL ⑥ 把 Parquet 当表反查
# ------------------------------------------------------------
echo
echo "[Step 7] 反查 Parquet, 验证落盘后还能跑同样的 SQL"
$CHL --query "
SELECT toStartOfMinute(ts) AS minute, count() AS cnt
FROM file('${PARQUET}', Parquet)
WHERE ts IS NOT NULL
GROUP BY minute
ORDER BY minute DESC
LIMIT 5
FORMAT PrettyCompact
"

echo
echo "==================================================="
echo "全部演示完成. 中间文件保留在: ${WORKDIR}"
echo "  rm -rf ${WORKDIR}   # 清理"
echo "==================================================="