#!/usr/bin/env bash
#
# 合併後檢查：活動排程（mergeparty / donation / rank_event）—— merge_check 第 16~20 格
#
# 由 merge_check 於第 12 項（Final Check 內）之後呼叫。流程分兩階段：
#
#   前置作業：
#     ① 從 AccountDB(Pre) worlds.start_time 取 target world 開服 unixtime
#     ② 於 target WorldDB 以「當地午夜制」算伺服器天數 N、event_start_time（當地今天 00:00 unixtime）
#     ③ 帶入 N 跑三張 AccountDB(Pre) 活動排程表，撈「第 N 天起最近一檔」的 id
#     ④ 解析合併 Log（同目錄 Test.log*）打點
#
#   階段一（Log 比對 gate）：合併 Log 的 5 個打點，先全部與前置算/撈的值比對
#       16. 我算的 N                == Log Server Days
#       17. 我算的 event_start_time == Log Event Start Time
#       18(log). mergeparty schedule id == Log Start MergeParty
#       19(log). donation  schedule id == Log Start Donation
#       20(log). rank_event schedule id 集合 == Log Start RankEvent 集合
#     其中 16 & 17 為全域 gate：兩者不一致則 start_time 基準不可信，18~20 不查 DB 直接判 X。
#
#   階段二（Post DB 檢查）：gate 通過、且該活動 schedule id == Log id 後，才查 Post DB
#       18. activity_mergeparty_info   有 id & start_time
#       19. activity_donation_info     有 id & start_time，且 total_accumulation = 0
#       20. activity_rank_event_info   逐 group 有 open_group_id & id & start_time，且 rewarded = 0
#
# 計分：16~20 共 5 格，O 與 N/A 計入通過。
# 參數：$1=merge_setup.ini 絕對路徑  $2=POSTFIX_BEFORE  $3=POSTFIX_AFTER  $4=test|live
# 回傳：exit code = 16~20 通過的格數（0~5）。

[ "$4" == "" ] && exec echo "Usage: $0 <absolute path for the merge_setup.ini> <postfix for the db before merge> <postfix for the db after merge> <test/live>"

MERGE_SETUP_INI=$1
POSTFIX_BEFORE=$2
POSTFIX_AFTER=$3
ENV_ARG=$4

case "$ENV_ARG" in
    test)
        ENVIRONMENT="測試"
        SCRIPT1="send_chatbot_text_only_test"
        SCRIPT2="send_messages_test"
        ;;
    live)
        ENVIRONMENT="正式"
        SCRIPT1="send_chatbot_text_only"
        SCRIPT2="send_messages"
        ;;
    *) echo "Unknown environment: $ENV_ARG (only 'test' or 'live' allowed)" >&2; exit 0 ;;
esac

OUTPUT_FILE="/tmp/merge_result_monitor.log"

source $HOME/.gamerc

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/load_merge_db_setup"
load_merge_db_setup "$MERGE_SETUP_INI" || { echo "[ERROR] load_merge_db_setup 失敗" >&2; exit 0; }

# target world（清單第一個）
target_world_id="${world_order[0]}"

# 合併 Log 與 ini 同目錄
merge_dir=$(dirname "$MERGE_SETUP_INI")
shopt -s nullglob
logfiles=("$merge_dir"/Test.log*)
shopt -u nullglob

# --- 連線資料（風格 A：ssh 到 DB IP 後本機 psql -U postgres）---
acc_ip=$(get_accountdb_value "AccountDBIP")
acc_pass=$(get_accountdb_value "AccountDBPW")
acc_db_pre="$(get_accountdb_value "AccountDBName")${POSTFIX_BEFORE}"

wdb_ip=$(get_worlddb_value "$target_world_id" "WorldDBIP")
wdb_pass=$(get_worlddb_value "$target_world_id" "WorldDBPassword")
wdb_name="$(get_worlddb_value "$target_world_id" "WorldDBName")"
wdb_pre="${wdb_name}${POSTFIX_BEFORE}"
wdb_post="${wdb_name}${POSTFIX_AFTER}"

run_acc_pre()   { ssh -o StrictHostKeyChecking=no "$acc_ip" "PGPASSWORD=\"$acc_pass\" psql -U postgres $acc_db_pre -Atqc \"$1\""; }
run_wdb_pre()   { ssh -o StrictHostKeyChecking=no "$wdb_ip" "PGPASSWORD=\"$wdb_pass\" psql -U postgres $wdb_pre  -Atqc \"$1\""; }
run_wdb_post()  { ssh -o StrictHostKeyChecking=no "$wdb_ip" "PGPASSWORD=\"$wdb_pass\" psql -U postgres $wdb_post -Atqc \"$1\""; }
dump_wdb_pre()  { ssh -o StrictHostKeyChecking=no "$wdb_ip" "PGPASSWORD=\"$wdb_pass\" psql -U postgres $wdb_pre  -c \"$1\""; }
dump_wdb_post() { ssh -o StrictHostKeyChecking=no "$wdb_ip" "PGPASSWORD=\"$wdb_pass\" psql -U postgres $wdb_post -c \"$1\""; }

# 解析 Log 打點：取 [數字]
parse_log_num() {  # $1 = 行內關鍵字（單值取最後一次出現）
    [ ${#logfiles[@]} -eq 0 ] && return
    grep -hE "$1" "${logfiles[@]}" 2>/dev/null | tail -1 | grep -oE '\[[0-9]+\]' | head -1 | tr -d '[]'
}
# 解析 Log rank_event 打點（通用）：每筆統一輸出 "og|id"（og 可空，視研發是否打 open_group）
#   Start RankEvent[10004]      → 1 參數 = id           → 輸出 "|10004"
#   Start RankEvent[999:10004]  → 2 參數 = open_group:id → 輸出 "999|10004"
parse_log_rank() {
    [ ${#logfiles[@]} -eq 0 ] && return
    grep -hE ",Start RankEvent" "${logfiles[@]}" 2>/dev/null | while IFS= read -r line; do
        local body nums cnt n1 n2
        body=$(printf '%s' "$line" | sed 's/.*Start RankEvent//')
        nums=$(printf '%s' "$body" | grep -oE '[0-9]+')
        cnt=$(printf '%s\n' "$nums" | grep -c .)
        if [ "$cnt" -ge 2 ]; then
            n1=$(printf '%s\n' "$nums" | sed -n '1p')
            n2=$(printf '%s\n' "$nums" | sed -n '2p')
            printf '%s|%s\n' "$n1" "$n2"
        elif [ "$cnt" -eq 1 ]; then
            printf '|%s\n' "$nums"
        fi
    done | sort -u
}

echo "=============================================="
echo "16~20. WorldDB > [活動排程] activity_*_info 檢查"
echo "  target world id = ${target_world_id}"
echo "  AccountDB(Pre)  = ${acc_db_pre} @ ${acc_ip}"
echo "  WorldDB (Pre)   = ${wdb_pre} @ ${wdb_ip}"
echo "  WorldDB (Post)  = ${wdb_post} @ ${wdb_ip}"
echo "  合併 Log        = ${merge_dir}/Test.log* (${#logfiles[@]} 個)"
echo "=============================================="

# ====== 前置作業 ======
# ① 開服 unixtime（AccountDB Pre）
start_time=$(run_acc_pre "SELECT start_time FROM worlds WHERE id = ${target_world_id};")
if [ -z "$start_time" ]; then
    echo "[ERROR] 無法從 ${acc_db_pre}.worlds 取得 id=${target_world_id} 的 start_time，16~20 全部標 X" >&2
    for n in 16 17 18 19 20; do echo "${n}. 活動排程檢查 ( X ) 前置失敗：取不到 start_time" | tee -a "$OUTPUT_FILE"; done
    exit 0
fi

# ② 伺服器天數 N、event_start_time（target WorldDB 當地午夜制）
#    基準日採「合併程式打點時間」（Log Server Days 行的時間戳），不用 check 跑的當下 CURRENT_DATE；
#    否則合併在 23:5x 打點、check 跨午夜才跑時 CURRENT_DATE 會多一天 → 16/17 假性判 X。
#    天數仍由 DB start_time 獨立算、仍與 Log Server Days 比對驗證（不默許程式對），只是「今天」鎖定成合併執行日。
wdb_tz=$(run_wdb_pre "SHOW timezone;")
log_exec_date=""
[ ${#logfiles[@]} -gt 0 ] && log_exec_date=$(grep -hE ",Server Days " "${logfiles[@]}" 2>/dev/null | tail -1 | awk -F',' '{print $2}' | awk '{print $1}' | tr '/' '-')
case "$log_exec_date" in
    [0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]) base_date_sql="DATE '${log_exec_date}'" ;;
    *) log_exec_date=""; base_date_sql="CURRENT_DATE" ;;   # 取不到合法日期：fallback 回 CURRENT_DATE（維持原行為）
esac
N=$(run_wdb_pre "SELECT ${base_date_sql} - to_timestamp(${start_time})::date + 1;")
event_start_time=$(run_wdb_pre "SELECT EXTRACT(EPOCH FROM ${base_date_sql}::timestamptz)::bigint;")

# ③ 三張排程表撈「第 N 天起最近一檔」（AccountDB Pre）
sched_mp=$(run_acc_pre "SELECT id FROM activity_mergeparty_schedule WHERE sell != 0 AND open_type = 1000 AND open_param >= ${N} ORDER BY open_param ASC LIMIT 1;")
sched_dn=$(run_acc_pre "SELECT id FROM activity_donation_schedule WHERE sell != 0 AND open_type = 1000 AND open_param >= ${N} ORDER BY open_param ASC LIMIT 1;")
sched_rk=$(run_acc_pre "SELECT DISTINCT ON (open_group) open_group || '|' || id FROM activity_rank_event_schedule WHERE sell != 0 AND open_type = 1000 AND open_param >= ${N} ORDER BY open_group ASC, open_param ASC;")
sched_rk_ids=$(printf '%s\n' "$sched_rk" | awk -F'|' 'NF{print $2}' | sort -un)

# ④ 解析合併 Log 打點
log_serverdays=$(parse_log_num ",Server Days ")
log_event_start=$(parse_log_num ",Event Start Time ")
log_mergeparty=$(parse_log_num ",Start MergeParty")
log_donation=$(parse_log_num ",Start Donation")
log_rk=$(parse_log_rank)

echo "  start_time(開服)  = ${start_time}"
echo "  WorldDB timezone  = ${wdb_tz}"
echo "  天數基準日         = ${log_exec_date:-CURRENT_DATE（Log 無 Server Days 時間戳，fallback）}"
echo "  伺服器天數 N       = ${N}    (Log Server Days = ${log_serverdays:-<none>})"
echo "  event_start_time  = ${event_start_time}    (Log Event Start Time = ${log_event_start:-<none>})"
echo "  排程 MergeParty id = ${sched_mp:-<none>}   (Log = ${log_mergeparty:-<none>})"
echo "  排程 Donation  id = ${sched_dn:-<none>}   (Log = ${log_donation:-<none>})"
echo "  排程 RankEvent (open_group|id):"
echo "${sched_rk:-  <none>}" | sed 's/^/    /'
echo "  Log    RankEvent (open_group|id，og 空=研發尚未打 open_group):"
echo "${log_rk:-  <none>}" | sed 's/^/    /'
echo

# 通知：三張排程表第 N 天起若查無資料，研判營運尚未設定該活動（非合併失敗，但屬營運疏漏），
# 報告內提示 + 走既有 SCRIPT2（send_messages[_test]，依 test/live 分流不同通知群組）推 ERROR 事件
notify_sched_unset() {  # $1=table  $2=活動名  $3=send_messages key（同時是 /tmp/<key>_monitor.log 檔名）
    echo "[通知] $1 第 ${N} 天起查無資料：$2 可能未設定排程（非合併失敗，請 CS 確認是否需設定）" | tee -a "$OUTPUT_FILE"
    # send_messages 發的是 /tmp/<key>_monitor.log 的「內容」（$3 僅作標題），必須先把訊息寫進該檔才會送出
    echo "[${ENVIRONMENT}合併] World ID ${target_world_id} ( 伺服器天數 ${N} ), $1 沒有設定符合此天數規則的資料, 請產營確認看看." > "/tmp/$3_monitor.log"
    "$HOME/bin/${SCRIPT2}" ERROR "$3" "活動排程未設定: $2 (Target World ID ${target_world_id})" 2>/dev/null || true
    sched_missing=1
}
sched_missing=0
[ -z "$sched_mp" ] && notify_sched_unset "activity_mergeparty_schedule" "MergeParty" "merge_activity_mergeparty_unset"
[ -z "$sched_dn" ] && notify_sched_unset "activity_donation_schedule"   "Donation"   "merge_activity_donation_unset"
[ -z "$sched_rk" ] && notify_sched_unset "activity_rank_event_schedule" "RankEvent"  "merge_activity_rank_event_unset"
[ "$sched_missing" = 1 ] && echo

# 備查 dump：三張 info 表 Pre / Post
echo "---- activity_mergeparty_info ( PRE ) ----";  dump_wdb_pre  "SELECT * FROM activity_mergeparty_info;"
echo "---- activity_mergeparty_info ( POST ) ----"; dump_wdb_post "SELECT * FROM activity_mergeparty_info;"
echo "---- activity_donation_info ( PRE ) ----";    dump_wdb_pre  "SELECT * FROM activity_donation_info;"
echo "---- activity_donation_info ( POST ) ----";   dump_wdb_post "SELECT * FROM activity_donation_info;"
echo "---- activity_rank_event_info ( PRE ) ----";  dump_wdb_pre  "SELECT * FROM activity_rank_event_info ORDER BY open_group_id;"
echo "---- activity_rank_event_info ( POST ) ----"; dump_wdb_post "SELECT * FROM activity_rank_event_info ORDER BY open_group_id;"
echo

pass=0

# =================================================================
# 階段一：合併 Log 五值比對（Log == 前置算/撈）
# =================================================================
echo "---- 階段一：合併 Log 五值比對（Log == 前置算/撈）----"

# 16. 伺服器天數 N
if [ -n "$log_serverdays" ] && [ "$N" = "$log_serverdays" ]; then
    chk16=1
    echo "16. [Log] 伺服器天數 N == Log Server Days ( O ) N=${N}" | tee -a "$OUTPUT_FILE"
    pass=$((pass + 1))
else
    chk16=0
    echo "16. [Log] 伺服器天數 N == Log Server Days ( X ) 算出 N=${N}, Log=${log_serverdays:-<none>}" | tee -a "$OUTPUT_FILE"
fi

# 17. event_start_time
if [ -n "$log_event_start" ] && [ "$event_start_time" = "$log_event_start" ]; then
    chk17=1
    echo "17. [Log] event_start_time == Log Event Start Time ( O ) ${event_start_time}" | tee -a "$OUTPUT_FILE"
    pass=$((pass + 1))
else
    chk17=0
    echo "17. [Log] event_start_time == Log Event Start Time ( X ) 算出=${event_start_time}, Log=${log_event_start:-<none>}" | tee -a "$OUTPUT_FILE"
fi

# 18~20 的 Log 層：schedule id == Log 打點 id（含「都空 = N/A」視為一致）
log_layer_ok() {  # $1=schedule_id $2=log_id；0=一致(含都空)，1=不一致
    [ -z "$1" ] && [ -z "$2" ] && return 0
    [ "$1" = "$2" ] && return 0
    return 1
}
log_layer_ok "$sched_mp" "$log_mergeparty"  && chk18_log=1 || chk18_log=0
log_layer_ok "$sched_dn" "$log_donation"    && chk19_log=1 || chk19_log=0

# rank_event Log 層（通用）：研發若打 open_group:id 則與 schedule 的 og|id 完整對應；只打 id 則比 id 集合
rk_has_og=1
[ -z "$log_rk" ] && rk_has_og=0
if [ -n "$log_rk" ]; then
    while IFS='|' read -r _og _id; do
        [ -z "$_og" ] && rk_has_og=0
    done <<< "$log_rk"
fi
log_rk_ids=$(printf '%s\n' "$log_rk" | awk -F'|' 'NF{print $2}' | sort -un)
if [ -z "$sched_rk" ] && [ -z "$log_rk" ]; then
    chk20_log=1
elif [ "$rk_has_og" = 1 ]; then
    [ "$(printf '%s\n' "$sched_rk" | sort -u)" = "$(printf '%s\n' "$log_rk" | sort -u)" ] && chk20_log=1 || chk20_log=0
else
    [ "$sched_rk_ids" = "$log_rk_ids" ] && chk20_log=1 || chk20_log=0
fi

echo "    18 [Log] MergeParty schedule=${sched_mp:-<none>} vs Log=${log_mergeparty:-<none>} -> $([ "$chk18_log" = 1 ] && echo OK || echo NG)"
echo "    19 [Log] Donation  schedule=${sched_dn:-<none>} vs Log=${log_donation:-<none>} -> $([ "$chk19_log" = 1 ] && echo OK || echo NG)"
echo "    20 [Log] RankEvent schedule=[$(echo ${sched_rk:-<none>})] vs Log=[$(echo ${log_rk:-<none>})] (帶open_group=${rk_has_og}) -> $([ "$chk20_log" = 1 ] && echo OK || echo NG)"

# 全域 gate：N 與 event_start_time 都要與 Log 一致，DB 的 start_time 基準才可信
[ "$chk16" = 1 ] && [ "$chk17" = 1 ] && gate=1 || gate=0
echo

# =================================================================
# 階段二：Post DB 資料檢查（需 gate 通過 + 各活動 Log 一致）
# =================================================================
echo "---- 階段二：Post DB 資料檢查（gate=${gate}；需 16 & 17 通過才查）----"

# 18. mergeparty
if [ "$gate" != 1 ]; then
    echo "18. [DB] activity_mergeparty_info ( X ) 前置 Log 比對(N/event_start_time)未過，跳過 DB 檢查" | tee -a "$OUTPUT_FILE"
elif [ "$chk18_log" != 1 ]; then
    echo "18. [DB] activity_mergeparty_info ( X ) schedule id=${sched_mp:-<none>} 與 Log=${log_mergeparty:-<none>} 不一致" | tee -a "$OUTPUT_FILE"
elif [ -z "$log_mergeparty" ]; then
    echo "18. [DB] activity_mergeparty_info ( O, N/A：排程無第 N 天起活動、Log 未打點 )" | tee -a "$OUTPUT_FILE"
    pass=$((pass + 1))
else
    cnt=$(run_wdb_post "SELECT COUNT(*) FROM activity_mergeparty_info WHERE id = ${log_mergeparty} AND start_time = ${event_start_time};")
    if [ "${cnt:-0}" -ge 1 ]; then
        echo "18. [DB] activity_mergeparty_info ( O ) id=${log_mergeparty}, start_time=${event_start_time}" | tee -a "$OUTPUT_FILE"
        pass=$((pass + 1))
    else
        echo "18. [DB] activity_mergeparty_info ( X ) 期望 id=${log_mergeparty}, start_time=${event_start_time}" | tee -a "$OUTPUT_FILE"
    fi
fi

# 19. donation（多驗 total_accumulation = 0）
if [ "$gate" != 1 ]; then
    echo "19. [DB] activity_donation_info ( X ) 前置 Log 比對(N/event_start_time)未過，跳過 DB 檢查" | tee -a "$OUTPUT_FILE"
elif [ "$chk19_log" != 1 ]; then
    echo "19. [DB] activity_donation_info ( X ) schedule id=${sched_dn:-<none>} 與 Log=${log_donation:-<none>} 不一致" | tee -a "$OUTPUT_FILE"
elif [ -z "$log_donation" ]; then
    echo "19. [DB] activity_donation_info ( O, N/A：排程無第 N 天起活動、Log 未打點 )" | tee -a "$OUTPUT_FILE"
    pass=$((pass + 1))
else
    cnt=$(run_wdb_post "SELECT COUNT(*) FROM activity_donation_info WHERE id = ${log_donation} AND start_time = ${event_start_time} AND total_accumulation = 0;")
    if [ "${cnt:-0}" -ge 1 ]; then
        echo "19. [DB] activity_donation_info ( O ) id=${log_donation}, start_time=${event_start_time}, total_accumulation=0" | tee -a "$OUTPUT_FILE"
        pass=$((pass + 1))
    else
        echo "19. [DB] activity_donation_info ( X ) 期望 id=${log_donation}, start_time=${event_start_time}, total_accumulation=0" | tee -a "$OUTPUT_FILE"
    fi
fi

# 20. rank_event（逐 group，多驗 rewarded = 0）
if [ "$gate" != 1 ]; then
    echo "20. [DB] activity_rank_event_info ( X ) 前置 Log 比對(N/event_start_time)未過，跳過 DB 檢查" | tee -a "$OUTPUT_FILE"
elif [ "$chk20_log" != 1 ]; then
    echo "20. [DB] activity_rank_event_info ( X ) schedule=[$(echo ${sched_rk:-<none>})] 與 Log=[$(echo ${log_rk:-<none>})] 不一致" | tee -a "$OUTPUT_FILE"
elif [ -z "$sched_rk" ]; then
    echo "20. [DB] activity_rank_event_info ( O, N/A：排程無第 N 天起活動、Log 未打點 )" | tee -a "$OUTPUT_FILE"
    pass=$((pass + 1))
else
    rk_ok=1
    while IFS='|' read -r og rid; do
        [ -z "$og" ] && continue
        cnt=$(run_wdb_post "SELECT COUNT(*) FROM activity_rank_event_info WHERE open_group_id = ${og} AND id = ${rid} AND start_time = ${event_start_time} AND rewarded = 0;")
        if [ "${cnt:-0}" -ge 1 ]; then
            echo "    20 [DB] open_group_id=${og}, id=${rid}, start_time=${event_start_time}, rewarded=0 ( O )"
        else
            echo "    20 [DB] open_group_id=${og}, id=${rid}, start_time=${event_start_time}, rewarded=0 ( X )"
            rk_ok=0
        fi
    done <<< "$sched_rk"
    if [ "$rk_ok" = 1 ]; then
        echo "20. [DB] activity_rank_event_info ( O ) 全 group 如預期（ids=[${sched_rk_ids}]）" | tee -a "$OUTPUT_FILE"
        pass=$((pass + 1))
    else
        echo "20. [DB] activity_rank_event_info ( X ) 部分 group 未如預期，詳見上方逐筆" | tee -a "$OUTPUT_FILE"
    fi
fi

echo
echo "[活動排程] 16~20 通過 ${pass} / 5 格"
exit "$pass"
