#!/usr/bin/env bash
# validate.sh — 校验 V1.sql 与 docs/03 的两个集合一致性：
#   维度 1: 表名集合
#   维度 2: 每张共有表的列名集合
#
# 用法: bash validate.sh <V1.sql> <docs/03 path>
# 退出码:
#   0 = 一致
#   1 = 不一致（差异明细打印到 stderr）
#   2 = 用法错误（路径找不到等）

set -uo pipefail
export LC_ALL=C   # sort / comm 行为确定

V1=${1:?missing V1 sql path}
DOC=${2:?missing docs/03 path}

[ -f "$V1" ]  || { echo "validate.sh: V1 not found: $V1"   >&2; exit 2; }
[ -f "$DOC" ] || { echo "validate.sh: docs not found: $DOC" >&2; exit 2; }

ERR=0

# ─── 维度 1: 表名集合 ───────────────────────────────────────────
TABLES_DOC=$(grep -E '^## `[^`]+`' "$DOC" \
              | sed -E 's/^## `([^`]+)`.*/\1/' | sort -u)
TABLES_SQL=$(grep -E '^CREATE TABLE `[^`]+`' "$V1" \
              | sed -E 's/^CREATE TABLE `([^`]+)`.*/\1/' | sort -u)

ONLY_DOC=$(comm -23 <(echo "$TABLES_DOC") <(echo "$TABLES_SQL"))
ONLY_SQL=$(comm -13 <(echo "$TABLES_DOC") <(echo "$TABLES_SQL"))

if [ -n "$ONLY_DOC" ] || [ -n "$ONLY_SQL" ]; then
  {
    echo "=== 维度 1: 表名集合不一致 ==="
    [ -n "$ONLY_DOC" ] && { echo "  docs/03 有但 V1 无:"; echo "$ONLY_DOC" | sed 's/^/    - /'; }
    [ -n "$ONLY_SQL" ] && { echo "  V1 有但 docs/03 无:"; echo "$ONLY_SQL" | sed 's/^/    - /'; }
  } >&2
  ERR=1
  # 表数差异 → 不再做列校验
  exit 1
fi

# ─── 维度 2: 每张共有表的列名集合 ──────────────────────────────
COMMON=$(comm -12 <(echo "$TABLES_DOC") <(echo "$TABLES_SQL"))

extract_doc_cols() {
  local table=$1
  awk -v t="$table" '
    $0 ~ "^## `" t "`"        { in_table=1; in_fields=0; next }
    in_table && /^## `/        { exit }
    in_table && /^### 字段/    { in_fields=1; next }
    in_table && in_fields && /^###/ { in_fields=0 }
    in_table && in_fields && /^\|/ {
      n = split($0, a, "|")
      gsub(/^[ ]+|[ ]+$/, "", a[2])
      gsub(/`/, "", a[2])
      if (a[2] != "" && a[2] != "字段" && a[2] !~ /^-+$/) print a[2]
    }
  ' "$DOC" | sort -u
}

extract_sql_cols() {
  local table=$1
  awk -v t="$table" '
    $0 ~ "^CREATE TABLE `" t "`" { in_table=1; next }
    in_table && /^\)/             { in_table=0; next }
    in_table && /^[[:space:]]*`[^`]+`/ \
             && $0 !~ /^[[:space:]]*(PRIMARY|UNIQUE|KEY|FOREIGN|CONSTRAINT|INDEX)/ {
      match($0, /`[^`]+`/)
      print substr($0, RSTART+1, RLENGTH-2)
    }
  ' "$V1" | sort -u
}

while IFS= read -r t; do
  [ -z "$t" ] && continue
  D_COLS=$(extract_doc_cols "$t")
  S_COLS=$(extract_sql_cols "$t")
  ONLY_D=$(comm -23 <(echo "$D_COLS") <(echo "$S_COLS"))
  ONLY_S=$(comm -13 <(echo "$D_COLS") <(echo "$S_COLS"))
  if [ -n "$ONLY_D" ] || [ -n "$ONLY_S" ]; then
    {
      echo "=== 维度 2: 表 \`$t\` 列名不一致 ==="
      [ -n "$ONLY_D" ] && { echo "  docs/03 有但 V1 无:"; echo "$ONLY_D" | sed 's/^/    - /'; }
      [ -n "$ONLY_S" ] && { echo "  V1 有但 docs/03 无:"; echo "$ONLY_S" | sed 's/^/    - /'; }
    } >&2
    ERR=1
  fi
done <<< "$COMMON"

if [ $ERR -ne 0 ]; then
  exit 1
fi

echo "validate.sh: ✓ 表名集合 + 每表列名集合 与 docs/03 一致"
exit 0