#!/bin/bash
# compare_dbpatch_n1_between_test_and_live
# 比對 TEST 與 LIVE 的 schema_version 是否一致
#
# 用法: $0 <ACCOUNTDB|GAMEDB|WORLDDB|SOCIETYDB|ALL>
# 輸出: 區塊式表格 + 結尾固定 `OVERALL: O|X|!` 一行
# Exit code:
#   0 = 全 O（LIVE 與 TEST 對齊）
#   1 = 任一 X（schema_version 真的不同 → 需要 dbpatch 或驗收失敗）
#   2 = 任一 ! （configuration 多筆 / 空表 / ssh 連不上 → 不可信任，需人工查）
#   3 = 內部錯誤（OVERALL 解析失敗等）

source $HOME/.gamerc

usage() {
    exec echo "Usage: $0 <DB_TYPE: ACCOUNTDB / GAMEDB / WORLDDB / SOCIETYDB / ALL>"
}

[[ $# -lt 1 ]] && usage
DB_TYPE="$1"

# ── 共用參數 ────────────────────────────────────────────────────────────
SSH_OPTS=(-o BatchMode=yes -o ConnectTimeout=5 -o StrictHostKeyChecking=accept-new)
SSH_HARD_TIMEOUT="${COMPARE_SSH_TIMEOUT_SEC:-12}"

# OVERALL 嚴重度：! > X > O（不會降級）
OVERALL_STATUS="O"
bump_overall() {
    case "$1" in
        '!') OVERALL_STATUS="!" ;;
        'X') [[ "$OVERALL_STATUS" != "!" ]] && OVERALL_STATUS="X" ;;
    esac
}

# 對單一 (host, db) 探測，輸出 STATUS:DETAIL
# STATUS:
#   O = LIVE schema_version == TEST_VER
#   X = LIVE schema_version != TEST_VER（真 diff）
#   ! = configuration row != 1（多筆/空表）或 ssh/psql 連不上（與舊版混為 X 的兩個 bug）
probe_db() {
    local host="$1" db="$2" test_ver="$3"
    local row_num live_ver maxv

    if ! row_num=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" "$host" \
        "psql -U postgres ${db} -Atqc 'SELECT COUNT(*) FROM configuration'" 2>/dev/null); then
        echo "!:ssh/psql 失敗或逾時 (${SSH_HARD_TIMEOUT}s)"
        return
    fi
    row_num=$(echo "$row_num" | tr -d '\r\n ')

    if [[ -z "$row_num" || ! "$row_num" =~ ^[0-9]+$ ]]; then
        echo "!:row_num 非數字 (raw='${row_num}')"
        return
    fi

    if [[ "$row_num" == "0" ]]; then
        echo "!:configuration 空表 (row=0)"
        return
    fi

    if [[ "$row_num" -gt 1 ]]; then
        # 多筆異常 — 即便 max(schema_version) 跟 TEST 一樣也不能信任
        maxv=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" "$host" \
            "psql -U postgres ${db} -Atqc 'SELECT MAX(schema_version) FROM configuration'" 2>/dev/null \
            | tr -d '\r\n ')
        echo "!:configuration row=${row_num} (max=${maxv:-?}，必須清成 row=1)"
        return
    fi

    # row=1 才取 schema_version 比對
    live_ver=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" "$host" \
        "psql -U postgres ${db} -Atqc 'SELECT schema_version FROM configuration'" 2>/dev/null \
        | tr -d '\r\n ')

    if [[ -z "$live_ver" ]]; then
        echo "!:取 schema_version 失敗 (row=1)"
        return
    fi

    if [[ "$live_ver" == "$test_ver" ]]; then
        echo "O:${live_ver}"
    else
        echo "X:LIVE=${live_ver}"
    fi
}

# ── ACCOUNTDB ───────────────────────────────────────────────────────────
check_accountdb() {
    echo "===================="
    echo "// ACCOUNTDB"

    local test_ver
    test_ver=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" TEST \
        "psql -U postgres ${ACCOUNT_DB_NAME} -Atqc 'SELECT schema_version FROM configuration'" 2>/dev/null \
        | tr -d '\r\n ')

    if [[ -z "$test_ver" ]]; then
        echo "RESULT: (!)"
        echo "原因: 取不到 TEST.${ACCOUNT_DB_NAME}.schema_version"
        bump_overall '!'
        echo "===================="
        echo
        return
    fi

    local probe ret_status detail
    probe=$(probe_db ACCOUNTDB "$ACCOUNT_DB_NAME" "$test_ver")
    ret_status="${probe%%:*}"
    detail="${probe#*:}"

    case "$ret_status" in
        O)
            echo "RESULT: (O)"
            echo "TEST: ${ACCOUNT_DB_NAME} = ${test_ver}"
            echo "LIVE: ${detail}"
            ;;
        X)
            echo "RESULT: (X)"
            echo "TEST: ${ACCOUNT_DB_NAME} = ${test_ver}"
            echo "LIVE: ${detail}"
            bump_overall 'X'
            ;;
        '!')
            echo "RESULT: (!)"
            echo "TEST: ${ACCOUNT_DB_NAME} = ${test_ver}"
            echo "原因: ${detail}"
            bump_overall '!'
            ;;
    esac
    echo "===================="
    echo
}

# ── GAMEDB ──────────────────────────────────────────────────────────────
check_gamedb() {
    echo "===================="
    echo "// GAMEDB"

    local set_id_lists
    set_id_lists=$(ssh CTRL "psql -U postgres WebTool -Atqc \"SELECT DISTINCT set_id FROM db_setup WHERE is_commercial = 1 ORDER BY set_id;\"" 2>/dev/null)

    if [[ -z "$set_id_lists" ]]; then
        echo "RESULT: (!)"
        echo "原因: 從 CTRL 取不到 commercial set_id 列表"
        bump_overall '!'
        echo "===================="
        echo
        return
    fi

    # TEST 基準：用列表第一個 set_id（取代寫死的 10，跨環境攜帶性）
    local first_set_id test_db_first test_ver
    first_set_id=$(echo "$set_id_lists" | head -1)
    test_db_first="${GAME_DB_NAME}${first_set_id}"
    test_ver=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" TEST \
        "psql -U postgres ${test_db_first} -Atqc 'SELECT schema_version FROM configuration'" 2>/dev/null \
        | tr -d '\r\n ')

    if [[ -z "$test_ver" ]]; then
        echo "RESULT: (!)"
        echo "原因: 取不到 TEST.${test_db_first}.schema_version"
        bump_overall '!'
        echo "===================="
        echo
        return
    fi

    local section_status="O"
    local fail_lines=""
    local total=0

    for set_id in $set_id_lists; do
        ((total++))
        local db="${GAME_DB_NAME}${set_id}"
        local probe ret_status detail
        probe=$(probe_db "GAMEDB${set_id}" "$db" "$test_ver")
        ret_status="${probe%%:*}"
        detail="${probe#*:}"

        case "$ret_status" in
            O) ;;  # 對齊就不印（避免 30 個 world 全列）
            X)
                fail_lines+="${db}: ${detail}"$'\n'
                [[ "$section_status" == "O" ]] && section_status="X"
                ;;
            '!')
                fail_lines+="${db}: (!) ${detail}"$'\n'
                section_status="!"
                ;;
        esac
    done

    case "$section_status" in
        O)
            echo "RESULT: (O)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "已比對 ${total} 個 set_id，全部一致"
            ;;
        X)
            echo "RESULT: (X)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "差異 set_id（${total} 個中）："
            echo -n "$fail_lines"
            bump_overall 'X'
            ;;
        '!')
            echo "RESULT: (!)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "差異 / 異常 set_id（${total} 個中）："
            echo -n "$fail_lines"
            bump_overall '!'
            ;;
    esac
    echo "===================="
    echo
}

# ── WORLDDB ─────────────────────────────────────────────────────────────
check_worlddb() {
    echo "===================="
    echo "// WORLDDB"

    local world_id_lists
    world_id_lists=$(ssh CTRL "psql -U postgres WebTool -Atqc \"SELECT world_id FROM db_setup WHERE is_commercial = 1 AND world_id NOT IN (SELECT id FROM fake_worlds) ORDER BY world_id;\"" 2>/dev/null)

    if [[ -z "$world_id_lists" ]]; then
        echo "RESULT: (!)"
        echo "原因: 從 CTRL 取不到 commercial world_id 列表"
        bump_overall '!'
        echo "===================="
        echo
        return
    fi

    local first_world_id test_db_first test_ver
    first_world_id=$(echo "$world_id_lists" | head -1)
    test_db_first="${WORLD_DB_NAME}${first_world_id}"
    test_ver=$(timeout "$SSH_HARD_TIMEOUT" ssh "${SSH_OPTS[@]}" TEST \
        "psql -U postgres ${test_db_first} -Atqc 'SELECT schema_version FROM configuration'" 2>/dev/null \
        | tr -d '\r\n ')

    if [[ -z "$test_ver" ]]; then
        echo "RESULT: (!)"
        echo "原因: 取不到 TEST.${test_db_first}.schema_version"
        bump_overall '!'
        echo "===================="
        echo
        return
    fi

    local section_status="O"
    local fail_lines=""
    local total=0

    for world_id in $world_id_lists; do
        ((total++))
        local db="${WORLD_DB_NAME}${world_id}"
        local probe ret_status detail
        probe=$(probe_db "WORLDDB${world_id}" "$db" "$test_ver")
        ret_status="${probe%%:*}"
        detail="${probe#*:}"

        case "$ret_status" in
            O) ;;
            X)
                fail_lines+="${db}: ${detail}"$'\n'
                [[ "$section_status" == "O" ]] && section_status="X"
                ;;
            '!')
                fail_lines+="${db}: (!) ${detail}"$'\n'
                section_status="!"
                ;;
        esac
    done

    case "$section_status" in
        O)
            echo "RESULT: (O)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "已比對 ${total} 個 world_id，全部一致"
            ;;
        X)
            echo "RESULT: (X)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "差異 world_id（${total} 個中）："
            echo -n "$fail_lines"
            bump_overall 'X'
            ;;
        '!')
            echo "RESULT: (!)"
            echo "TEST: ${test_db_first} = ${test_ver}"
            echo "差異 / 異常 world_id（${total} 個中）："
            echo -n "$fail_lines"
            bump_overall '!'
            ;;
    esac
    echo "===================="
    echo
}

# ── SOCIETYDB（暫不實作，沿用舊版 useless 行為）─────────────────────────
check_societydb() {
    echo "===================="
    echo "// SOCIETYDB"
    echo "（暫未實作，跳過；不影響 OVERALL）"
    echo "===================="
    echo
}

# ── dispatch ────────────────────────────────────────────────────────────
case "${DB_TYPE}" in
    ACCOUNTDB|accountdb) check_accountdb ;;
    GAMEDB|gamedb)       check_gamedb ;;
    WORLDDB|worlddb)     check_worlddb ;;
    SOCIETYDB|societydb) check_societydb ;;
    ALL|all)
        check_accountdb
        check_gamedb
        check_worlddb
        # SOCIETYDB 既有為 useless，ALL 模式不放避免噪音
        ;;
    *)
        echo "ERROR: Unknown DB_TYPE for ${DB_TYPE}"
        usage
        ;;
esac

# ── 結尾 OVERALL（給程式碼 grep 判讀）───────────────────────────────────
echo "OVERALL: ${OVERALL_STATUS}"

case "${OVERALL_STATUS}" in
    O)   exit 0 ;;
    X)   exit 1 ;;
    '!') exit 2 ;;
    *)   exit 3 ;;
esac
