#!/usr/bin/env bash
source "$HOME/.gamerc"
set -euo pipefail

# 用法: ./check_worlds_related_modification_after_merge <environment:test|live> <merge_filename>
if [[ "$#" -ne 2 ]]; then
  echo "用法: $0 <environment:test|live> <merge_filename>" >&2
  exit 1
fi

environment="$1"
merge_filename="$2"

case "$environment" in
  test|live) ;;
  *)
    echo "Error: environment must be 'test' or 'live', got '$environment'." >&2
    exit 1
    ;;
esac

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
file="${SCRIPT_DIR}/merge_n1/${environment}/${merge_filename}"
if [[ ! -f "$file" ]]; then
  echo "找不到檔案: $file" >&2
  exit 1
fi

: "${ACCOUNT_DB_NAME:?ACCOUNT_DB_NAME 未設定}"

# test / live 決定 ssh host（依 MERGE_MODE 切換）
if [[ "$environment" == "test" ]]; then
  case "${MERGE_MODE:-single}" in
    single)   ssh_host="MERGE" ;;
    multiple) ssh_host="MERGE_COMMON" ;;
    *)        echo "Error: unknown MERGE_MODE '${MERGE_MODE}'" >&2; exit 1 ;;
  esac
  OUTPUT_ENV="[測試合併]"
  SCRIPT="$HOME/bin/send_chatbot_text_only_test"
else
  ssh_host="ACCOUNTDB"
  OUTPUT_ENV="[正式合併]"
  SCRIPT="$HOME/bin/send_chatbot_text_only"
fi

psql_user="postgres"

sort_ids_unique() {
  tr -s '[:space:]' '\n' \
    | sed '/^$/d' \
    | sort -n \
    | uniq
}

join_by_comma() {
  paste -sd, -
}

trim_all_ws() {
  tr -d '[:space:]'
}

# 回傳 scalar（例如 COUNT(*)）；失敗時回傳 stderr+stdout 並 return 1
run_psql_scalar() {
  local sql="$1"
  local out=""
  if ! out="$(
    ssh -T -o BatchMode=yes "$ssh_host" \
      "psql -X -U ${psql_user} ${ACCOUNT_DB_NAME} -Atq" \
      <<<"$sql" 2>&1
  )"; then
    printf '%s\n' "$out"
    return 1
  fi
  printf '%s' "$out" | trim_all_ws
}

# 回傳多行結果（例如 SELECT id ... ORDER BY id;），保持每行一筆；失敗 return 1
run_psql_lines() {
  local sql="$1"
  local out=""
  if ! out="$(
    ssh -T -o BatchMode=yes "$ssh_host" \
      "psql -X -U ${psql_user} ${ACCOUNT_DB_NAME} -Atq" \
      <<<"$sql" 2>&1
  )"; then
    printf '%s\n' "$out"
    return 1
  fi
  # psql -Atq 本來就只會輸出資料列；這裡只清掉空行
  printf '%s\n' "$out" | sed '/^$/d'
}

# 用 expected(list) + present(list) 算 missing(list)
# 兩者都必須是一行一個 id 的格式
calc_missing_list() {
  local expected="$1"
  local present="$2"

  declare -A seen=()
  local x=""

  while IFS= read -r x; do
    [[ -z "$x" ]] && continue
    seen["$x"]=1
  done <<<"$present"

  local miss=()
  while IFS= read -r x; do
    [[ -z "$x" ]] && continue
    if [[ -z "${seen[$x]+x}" ]]; then
      miss+=( "$x" )
    fi
  done <<<"$expected"

  if [[ "${#miss[@]}" -gt 0 ]]; then
    printf '%s\n' "${miss[@]}"
  fi
}

line_no=0
all_ok=1

exec 3< "$file"
while IFS= read -r line <&3 || [[ -n "$line" ]]; do
  ((++line_no))

  [[ -z "$line" ]] && continue
  [[ "$line" =~ ^[[:space:]]*# ]] && continue

  # 僅允許數字與空白
  if ! [[ "$line" =~ ^[[:space:]]*[0-9]+([[:space:]]+[0-9]+)*[[:space:]]*$ ]]; then
    echo "L$line_no: invalid content, skipped: $line" >&2
    all_ok=0
    continue
  fi

  # 以「行內順序」為準：第一個是 target，其餘是 source
  read -r -a ids_raw <<<"$line"
  if [[ "${#ids_raw[@]}" -lt 1 ]]; then
    echo "L$line_no: invalid content, skipped: $line" >&2
    all_ok=0
    continue
  fi

  target_world_id="${ids_raw[0]}"
  if ! [[ "$target_world_id" =~ ^[0-9]+$ ]]; then
    echo "L$line_no: Error env=$environment target=<invalid> (non-numeric target: $target_world_id)" >&2
    all_ok=0
    continue
  fi

  # sources：去重排序（避免重複 world id 影響預期筆數）
  sorted_source_world_ids="$(
    printf '%s\n' "${ids_raw[@]:1}" | sort_ids_unique
  )"
  sorted_source_world_ids_output="$(printf '%s\n' "$sorted_source_world_ids" | paste -sd' ' -)"
  sorted_source_world_ids_str="$(printf '%s\n' "$sorted_source_world_ids" | join_by_comma)"

  if [[ -z "$sorted_source_world_ids" ]]; then
    expected_source=0
  else
    expected_source="$(printf '%s\n' "$sorted_source_world_ids" | wc -l | trim_all_ws)"
  fi

  # --- Query: target in worlds ---
  worlds_target_raw="$(run_psql_scalar "SELECT COUNT(*) FROM worlds WHERE id = ${target_world_id};" 2>&1)" || {
    all_ok=0
    echo "L$line_no: Error target=$target_world_id (worlds(target) query failed on ${ssh_host})" >&2
    echo "$worlds_target_raw" >&2
    echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=${sorted_source_world_ids_str:-<none>} (worlds_target_query_failed)"
    continue
  }
  worlds_target="${worlds_target_raw:-0}"

  # --- Query: target in fake_worlds (should be 0) ---
  fake_target_raw="$(run_psql_scalar "SELECT COUNT(*) FROM fake_worlds WHERE id = ${target_world_id};" 2>&1)" || {
    all_ok=0
    echo "L$line_no: Error target=$target_world_id (fake_worlds(target) query failed on ${ssh_host})" >&2
    echo "$fake_target_raw" >&2
    echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=${sorted_source_world_ids_str:-<none>} (fake_target_query_failed)"
    continue
  }
  fake_target="${fake_target_raw:-0}"

  # --- Query: sources in fake_worlds / worlds / fake_worlds.real_world_id ---
  fake_source=0
  worlds_source=0
  present_fake_lines=""
  present_worlds_lines=""
  missing_in_fake_lines=""

  # 新增條件 (5)
  fake_real_source=0
  present_fake_real_lines=""

  if [[ "$expected_source" -gt 0 && -n "$sorted_source_world_ids_str" ]]; then
    present_fake_lines="$(run_psql_lines "SELECT id::text FROM fake_worlds WHERE id IN (${sorted_source_world_ids_str}) ORDER BY id;" 2>&1)" || {
      all_ok=0
      echo "L$line_no: Error target=$target_world_id source_ids=$sorted_source_world_ids_str (fake_worlds(source) query failed on ${ssh_host})" >&2
      echo "$present_fake_lines" >&2
      echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=$sorted_source_world_ids_str (fake_source_query_failed)"
      continue
    }
    fake_source="$(printf '%s\n' "$present_fake_lines" | sed '/^$/d' | wc -l | trim_all_ws)"

    present_worlds_lines="$(run_psql_lines "SELECT id::text FROM worlds WHERE id IN (${sorted_source_world_ids_str}) ORDER BY id;" 2>&1)" || {
      all_ok=0
      echo "L$line_no: Error target=$target_world_id source_ids=$sorted_source_world_ids_str (worlds(source) query failed on ${ssh_host})" >&2
      echo "$present_worlds_lines" >&2
      echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=$sorted_source_world_ids_str (worlds_source_query_failed)"
      continue
    }
    worlds_source="$(printf '%s\n' "$present_worlds_lines" | sed '/^$/d' | wc -l | trim_all_ws)"

    missing_in_fake_lines="$(calc_missing_list "$sorted_source_world_ids" "$present_fake_lines" || true)"

    # (5) fake_worlds.real_world_id 不可包含任何 source_ids
    fake_real_source_raw="$(run_psql_scalar "SELECT COUNT(*) FROM fake_worlds WHERE real_world_id IN (${sorted_source_world_ids_str});" 2>&1)" || {
      all_ok=0
      echo "L$line_no: Error target=$target_world_id source_ids=$sorted_source_world_ids_str (fake_worlds(real_world_id) query failed on ${ssh_host})" >&2
      echo "$fake_real_source_raw" >&2
      echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=$sorted_source_world_ids_str (fake_real_source_query_failed)"
      continue
    }
    fake_real_source="${fake_real_source_raw:-0}"

    if [[ "$fake_real_source" -gt 0 ]]; then
      present_fake_real_lines="$(run_psql_lines "SELECT DISTINCT real_world_id::text FROM fake_worlds WHERE real_world_id IN (${sorted_source_world_ids_str}) ORDER BY real_world_id;" 2>&1)" || {
        present_fake_real_lines=""
      }
    fi
  fi

  # 判斷：必須符合
  # 1) worlds(target) == 1
  # 2) fake_worlds(target) == 0
  # 3) fake_worlds(source) == expected_source
  # 4) worlds(source) == 0
  # 5) fake_worlds.real_world_id 不可命中 source_ids（fake_real_source 必須為 0）
  if [[ "$worlds_target" -eq 1 && "$fake_target" -eq 0 && "$fake_source" -eq "$expected_source" && "$worlds_source" -eq 0 && "$fake_real_source" -eq 0 ]]; then
    echo "L$line_no: Correct env=$environment target=$target_world_id source_ids=${sorted_source_world_ids_str:-<none>} (worlds(target)=$worlds_target fake(target)=$fake_target fake(source)=$fake_source worlds(source)=$worlds_source expected_source=$expected_source fake(real_world_id/source)=$fake_real_source)"
    "$HOME/bin/send_chatbot_text_only_test" "[WORLDS & FAKE_WORLDS] 驗證 更新成功 ^^" "$target_world_id <- ${sorted_source_world_ids_output}"
    continue
  fi

  # --- Error path：補全診斷資訊 ---
  all_ok=0
  echo "L$line_no: Error   env=$environment target=$target_world_id source_ids=${sorted_source_world_ids_str:-<none>} (worlds(target)=$worlds_target fake(target)=$fake_target fake(source)=$fake_source worlds(source)=$worlds_source expected_source=$expected_source fake(real_world_id/source)=$fake_real_source)"

  if [[ "$worlds_target" -ne 1 ]]; then
    if [[ "$worlds_target" -eq 0 ]]; then
      echo "  - 診斷: target=$target_world_id 不存在於 worlds"
    else
      echo "  - 診斷: target=$target_world_id 在 worlds 筆數異常 (count=$worlds_target)"
    fi
  fi

  if [[ "$fake_target" -ne 0 ]]; then
    echo "  - 診斷: target=$target_world_id 不應存在於 fake_worlds (count=$fake_target)"
  fi

  if [[ "$expected_source" -gt 0 ]]; then
    if [[ "$fake_source" -ne "$expected_source" ]]; then
      if [[ -n "${missing_in_fake_lines:-}" ]]; then
        echo "  - 診斷: source_ids 有缺漏於 fake_worlds (missing): $(printf '%s\n' "$missing_in_fake_lines" | paste -sd, -)"
      else
        echo "  - 診斷: fake_worlds(source) 筆數不符，但未能算出 missing（請檢查輸入/重複/查詢輸出）"
      fi
      echo "  - 診斷: fake_worlds(source) present: $(printf '%s\n' "$present_fake_lines" | paste -sd, -)"
    fi

    if [[ "$worlds_source" -ne 0 ]]; then
      echo "  - 診斷: source_ids 不應存在於 worlds，但查到: $(printf '%s\n' "$present_worlds_lines" | paste -sd, -)"
    fi
  fi

  if [[ "$expected_source" -gt 0 && "$fake_real_source" -ne 0 ]]; then
    if [[ -n "${present_fake_real_lines:-}" ]]; then
      echo "  - 診斷: source_ids 不應出現在 fake_worlds.real_world_id，但命中: $(printf '%s\n' "$present_fake_real_lines" | paste -sd, -)"
    else
      echo "  - 診斷: source_ids 不應出現在 fake_worlds.real_world_id，但筆數 count=$fake_real_source（未能列出命中清單）"
    fi
  fi

  "$HOME/bin/send_chatbot_text_only_test" "[WORLDS & FAKE_WORLDS] 驗證 更新失敗 QQ" "$target_world_id <- ${sorted_source_world_ids_output}"

done
exec 3<&-

# 全面結論
if [[ "$all_ok" -eq 1 ]]; then
  echo "完全正確"
  "$SCRIPT" "${OUTPUT_ENV} ${merge_filename} [WORLDS & FAKE_WORLDS] 驗證結果" "成功!"
  exit 0
else
  echo "部分出錯"
  "$SCRIPT" "${OUTPUT_ENV} ${merge_filename} [WORLDS & FAKE_WORLDS] 驗證結果" "失敗!"
  exit 1
fi
