#!/bin/bash
# ===========================================
# 检查会员订阅情况
# ===========================================
# 用法:
#   ./check-members.sh                # 生产数据库
#   ./check-members.sh --dev          # 开发数据库 (milkydata_dev)
#   ./check-members.sh --dev --json   # JSON 格式输出
# ===========================================

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
ENV_FILE="$PROJECT_ROOT/.env"

# ---- 颜色 ----
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
CYAN='\033[0;36m'
BOLD='\033[1m'
NC='\033[0m' # No Color

# ---- 参数 ----
DB_NAME="milkydata"
OUTPUT_JSON=false

while [[ $# -gt 0 ]]; do
  case "$1" in
    --dev) DB_NAME="milkydata_dev"; shift ;;
    --json) OUTPUT_JSON=true; shift ;;
    --help|-h)
      echo "用法: $0 [--dev] [--json]"
      echo "  --dev   查询开发数据库 (milkydata_dev)"
      echo "  --json  JSON 格式输出"
      exit 0
      ;;
    *) echo "未知参数: $1"; exit 1 ;;
  esac
done

# ---- 获取 SSH 和数据库配置 ----
SSH_HOST=$(grep '^SSH_SERVER=' "$ENV_FILE" 2>/dev/null | head -1 | cut -d'=' -f2- | tr -d '"' | tr -d "'" | xargs)
if [ -z "$SSH_HOST" ]; then
  echo "${RED}❌ 未找到 SSH_SERVER 配置，请检查 $ENV_FILE${NC}"
  exit 1
fi

# 从 DATABASE_URL 解析数据库连接参数
DB_URL=$(grep '^DATABASE_URL=' "$ENV_FILE" 2>/dev/null | head -1 | cut -d'=' -f2- | tr -d '"' | tr -d "'" | xargs)
# 提取用户名:密码:主机:端口
DB_USER=$(echo "$DB_URL" | sed 's|.*://\([^:]*\):.*|\1|')
DB_PASS=$(echo "$DB_URL" | sed 's|.*://[^:]*:\([^@]*\)@.*|\1|')
DB_HOST=$(echo "$DB_URL" | sed 's|.*@\([^:]*\):.*|\1|')
DB_PORT=$(echo "$DB_URL" | sed 's|.*:\([0-9]*\)/.*|\1|')

# 拼接目标数据库的连接串
TARGET_URL="postgres://${DB_USER}:${DB_PASS}@127.0.0.1:${DB_PORT}/${DB_NAME}"

# PSQL 执行函数：通过 docker exec（服务器上 psql 在容器内）
psql_exec() {
  local sql="$1"
  local result
  result=$(ssh "$SSH_HOST" "docker exec 1Panel-postgresql-FtMo psql '$TARGET_URL' -At -c \"$sql\" 2>/dev/null" 2>/dev/null)
  if [ -z "$result" ]; then
    result=$(ssh "$SSH_HOST" "psql -d $DB_NAME -At -c \"$sql\" 2>/dev/null" 2>/dev/null)
  fi
  # 去掉末尾空行
  echo "$result" | sed '/^$/d'
}

echo -e "${CYAN}══════════════════════════════════════${NC}"
echo -e "${CYAN}  会员订阅状态检查${NC}"
echo -e "${CYAN}  数据库: ${BOLD}$DB_NAME${NC}"
echo -e "${CYAN}  时间:   $(date '+%Y-%m-%d %H:%M:%S')${NC}"
echo -e "${CYAN}══════════════════════════════════════${NC}"
echo ""

# ---- 总览 ----
SQL_OVERVIEW=$(cat <<'SQL'
SELECT
  COUNT(*)                                                                  AS total_users,
  COUNT(*) FILTER (WHERE is_member = true)                                  AS total_members,
  COUNT(*) FILTER (WHERE is_member = true AND membership_expires_at > NOW()) AS active_members,
  COUNT(*) FILTER (WHERE is_member = true AND membership_expires_at <= NOW()) AS expired_members,
  COUNT(*) FILTER (WHERE is_member = true AND membership_expires_at IS NULL)  AS permanent_members,
  COUNT(*) FILTER (WHERE is_member = true
                   AND membership_expires_at IS NOT NULL
                   AND membership_expires_at > NOW()
                   AND membership_expires_at <= NOW() + INTERVAL '7 days')   AS expiring_7d,
  COUNT(*) FILTER (WHERE is_member = true
                   AND membership_expires_at IS NOT NULL
                   AND membership_expires_at > NOW()
                   AND membership_expires_at <= NOW() + INTERVAL '30 days')  AS expiring_30d
FROM users;
SQL
)

OVERVIEW=$(psql_exec "$SQL_OVERVIEW") || {
  echo -e "${RED}❌ 数据库查询失败 (SSH: $SSH_HOST, DB: $DB_NAME)${NC}"
  echo -e "${YELLOW}  尝试直接连接: ssh $SSH_HOST docker exec 1Panel-postgresql-FtMo psql ...${NC}"
  exit 1
}

IFS='|' read -r total_users total_members active_members expired_members permanent_members expiring_7d expiring_30d <<< "$OVERVIEW"

# 如果新字段名(is_member)查不到，用旧字段名(is_paid)重查
if [ -z "$total_members" ] || [ "$total_members" = "0" ] && [ "$total_users" != "0" ]; then
  SQL_OVERVIEW_OLD=$(cat <<'SQL'
SELECT
  COUNT(*)                                                                  AS total_users,
  COUNT(*) FILTER (WHERE is_paid = true)                                    AS total_members,
  COUNT(*) FILTER (WHERE is_paid = true AND paid_expires_at > NOW())         AS active_members,
  COUNT(*) FILTER (WHERE is_paid = true AND paid_expires_at <= NOW())        AS expired_members,
  COUNT(*) FILTER (WHERE is_paid = true AND paid_expires_at IS NULL)         AS permanent_members,
  COUNT(*) FILTER (WHERE is_paid = true
                   AND paid_expires_at IS NOT NULL
                   AND paid_expires_at > NOW()
                   AND paid_expires_at <= NOW() + INTERVAL '7 days')         AS expiring_7d,
  COUNT(*) FILTER (WHERE is_paid = true
                   AND paid_expires_at IS NOT NULL
                   AND paid_expires_at > NOW()
                   AND paid_expires_at <= NOW() + INTERVAL '30 days')        AS expiring_30d
FROM users;
SQL
)
  OVERVIEW=$(psql_exec "$SQL_OVERVIEW_OLD")
  IFS='|' read -r total_users total_members active_members expired_members permanent_members expiring_7d expiring_30d <<< "$OVERVIEW"
  USE_OLD_COLS=true
else
  USE_OLD_COLS=false
fi

# ---- 活跃会员列表 ----
SQL_LIST=$(cat <<'SQL'
SELECT
  id,
  COALESCE(openid, 'N/A')                                                                    AS openid,
  COALESCE(nickname, CONCAT('用户 #', id))                                                   AS display_name,
  membership_expires_at::date                                                                AS expires_on,
  CASE
    WHEN membership_expires_at IS NULL THEN '永久'
    WHEN membership_expires_at <= NOW() THEN '已过期'
    ELSE CONCAT('剩 ', EXTRACT(DAY FROM membership_expires_at - NOW()), ' 天')
  END                                                                                        AS status,
  CASE
    WHEN membership_expires_at IS NULL THEN 99999
    WHEN membership_expires_at <= NOW() THEN -1
    ELSE EXTRACT(DAY FROM membership_expires_at - NOW())::int
  END                                                                                        AS days_left
FROM users
WHERE is_member = true
ORDER BY
  CASE WHEN membership_expires_at IS NULL THEN 0 ELSE 1 END,
  membership_expires_at DESC;
SQL
)

if [ "$USE_OLD_COLS" = true ]; then
  SQL_LIST_OLD=$(cat <<'SQL'
SELECT
  id,
  COALESCE(openid, 'N/A')                                                                    AS openid,
  COALESCE(nickname, CONCAT('用户 #', id))                                                   AS display_name,
  paid_expires_at::date                                                                      AS expires_on,
  CASE
    WHEN paid_expires_at IS NULL THEN '永久'
    WHEN paid_expires_at <= NOW() THEN '已过期'
    ELSE CONCAT('剩 ', EXTRACT(DAY FROM paid_expires_at - NOW()), ' 天')
  END                                                                                        AS status,
  CASE
    WHEN paid_expires_at IS NULL THEN 99999
    WHEN paid_expires_at <= NOW() THEN -1
    ELSE EXTRACT(DAY FROM paid_expires_at - NOW())::int
  END                                                                                        AS days_left
FROM users
WHERE is_paid = true
ORDER BY
  CASE WHEN paid_expires_at IS NULL THEN 0 ELSE 1 END,
  paid_expires_at DESC;
SQL
)
  MEMBER_LIST=$(psql_exec "$SQL_LIST_OLD")
else
  MEMBER_LIST=$(psql_exec "$SQL_LIST")
fi

# ---- 订单与会员一致性检查 ----
SQL_ORDER_CHECK=$(cat <<'SQL'
SELECT
  u.id,
  COALESCE(u.nickname, CONCAT('用户 #', u.id)) AS name,
  COUNT(po.id) AS total_orders,
  COUNT(po.id) FILTER (WHERE po.status = 'paid') AS paid_orders,
  CASE
    WHEN u.id IN (2053, 5258) THEN '测试贡献发放'
    ELSE ''
  END AS note
FROM users u
LEFT JOIN payment_orders po ON po.user_id = u.id
WHERE u.is_member = true
  AND u.is_admin = false
GROUP BY u.id, u.nickname, u.is_admin
HAVING COUNT(po.id) FILTER (WHERE po.status = 'paid') = 0
ORDER BY u.id;
SQL
)

ORDER_ISSUES=$(psql_exec "$SQL_ORDER_CHECK")

# ---- 近期到期的活跃会员 ----
SQL_EXPIRING=$(cat <<'SQL'
SELECT
  id,
  COALESCE(nickname, CONCAT('用户 #', id)) AS display_name,
  membership_expires_at::date              AS expires_on,
  EXTRACT(DAY FROM membership_expires_at - NOW())::int AS days_left
FROM users
WHERE is_member = true
  AND membership_expires_at IS NOT NULL
  AND membership_expires_at > NOW()
  AND membership_expires_at <= NOW() + INTERVAL '7 days'
ORDER BY membership_expires_at;
SQL
)

EXPIRING_LIST=$(psql_exec "$SQL_EXPIRING")

# ---- JSON 输出 ----
if [ "$OUTPUT_JSON" = true ]; then
  cat <<JSON
{
  "db": "$DB_NAME",
  "time": "$(date --iso-8601=seconds)",
  "total_users": $total_users,
  "total_members": $total_members,
  "active_members": $active_members,
  "expired_members": $expired_members,
  "permanent_members": $permanent_members,
  "expiring_within_7d": $expiring_7d,
  "expiring_within_30d": $expiring_30d,
  "members": [
JSON
  FIRST=true
  while IFS='|' read -r id openid name expires_on status days_left; do
    [ -z "$id" ] && continue
    [ "$FIRST" = true ] || echo ","
    FIRST=false
    cat <<JSON
    { "id": $id, "name": "$name", "expires_on": "${expires_on:-永久}", "status": "$status", "days_left": ${days_left:-0} }
JSON
  done <<< "$MEMBER_LIST"
  echo ""
  echo "  ]"
  echo "}"
  exit 0
fi

# ---- 表格输出 ----
echo -e "${BOLD}📊 总览${NC}"
echo -e "  ${GREEN}总用户数:     ${BOLD}$total_users${NC}"
echo -e "  ${CYAN}会员总数:     ${BOLD}$total_members${NC}（占总用户 $(( total_users > 0 ? total_members * 100 / total_users : 0 ))%）"
echo -e "  ${GREEN}活跃会员:     ${BOLD}$active_members${NC}（已付费且未过期）"
echo -e "  ${YELLOW}已过期会员:   ${BOLD}$expired_members${NC}"
echo -e "  ${BOLD}永久会员:     ${BOLD}$permanent_members${NC}"
echo ""
echo -e "  ${YELLOW}7 天内到期:   ${BOLD}$expiring_7d${NC}"
echo -e "  ${YELLOW}30 天内到期:  ${BOLD}$expiring_30d${NC}"
echo ""

# ---- 活跃会员列表 ----
if [ -n "$MEMBER_LIST" ]; then
  echo -e "${BOLD}📋 会员列表${NC}"
  printf "  ${CYAN}%-4s  %-22s  %-12s  %s${NC}\n" "ID" "名称" "到期日" "状态"
  echo "  ─────────────────────────────────────────────"
  while IFS='|' read -r id openid name expires_on status days_left; do
    [ -z "$id" ] && continue
    if [ "$status" = "永久" ]; then
      printf "  %-4d  %-22s  %-12s  %s\n" "$id" "${name:0:22}" "永久" "🔒 永久"
    elif [ "$status" = "已过期" ]; then
      printf "  %-4d  %-22s  %-12s  %s\n" "$id" "${name:0:22}" "$expires_on" "❌ $status"
    elif [ "$days_left" -le 7 ]; then
      printf "  %-4d  %-22s  %-12s  %s\n" "$id" "${name:0:22}" "$expires_on" "⚠️  $status"
    else
      printf "  %-4d  %-22s  %-12s  %s\n" "$id" "${name:0:22}" "$expires_on" "✅ $status"
    fi
  done <<< "$MEMBER_LIST"
  echo ""
fi

# ---- 近期到期提醒 ----
if [ -n "$EXPIRING_LIST" ]; then
  echo -e "${BOLD}⏰ 近期到期（7 天内）${NC}"
  while IFS='|' read -r id name expires_on days_left; do
    [ -z "$id" ] && continue
    echo -e "  ${YELLOW}⚠️  用户 #$id（${name}）将于 $expires_on 到期，剩余 ${days_left} 天${NC}"
  done <<< "$EXPIRING_LIST"
  echo ""
fi

echo -e "${CYAN}══════════════════════════════════════${NC}"

# ---- 订单异常提醒 ----
if [ -n "$ORDER_ISSUES" ]; then
  echo -e "${BOLD}⚠️  订单异常：有会员身份但无成功付款记录${NC}"
  echo "  ！用户可能通过手动操作获得了会员身份"
  while IFS='|' read -r id name total_orders paid_orders note; do
    [ -z "$id" ] && continue
    msg="⚠️  用户 #${id}（${name}）：共 ${total_orders} 条订单，${paid_orders} 条已支付"
    if [ -n "$note" ]; then
      msg="${msg}（${note}）"
    fi
    echo -e "  ${YELLOW}${msg}${NC}"
  done <<< "$ORDER_ISSUES"
  echo ""
fi