#!/usr/bin/env bash
set -euo pipefail

backup_root=/home/admin/backups/sub-lb-tap365-org
ts=$(date +%Y%m%d_%H%M%S)
backup_dir="$backup_root/backup-date-$ts"
full_dir="$backup_dir/full"
structured_dir="$backup_dir/structured"
meta_dir="$backup_dir/meta"
service_root=/home/admin/docker-projects/sub2api
postgres_container=postgres
postgres_db=sub2api
redis_host=127.0.0.1
redis_port=6379
redis_conf=/home/admin/docker-projects/redis-base/redis.conf

mkdir -p "$full_dir" "$structured_dir" "$meta_dir"

echo "[1/7] 读取 PostgreSQL 凭证"
pg_env_file=$(mktemp)
docker inspect "$postgres_container" --format '{{range .Config.Env}}{{println .}}{{end}}' > "$pg_env_file"
pg_user=$(awk -F= '/^POSTGRES_USER=/{print $2}' "$pg_env_file")
pg_pass=$(awk -F= '/^POSTGRES_PASSWORD=/{print $2}' "$pg_env_file")
rm -f "$pg_env_file"
[ -n "$pg_user" ]
[ -n "$pg_pass" ]

redis_pass=$(awk '/^requirepass /{print $2; exit}' "$redis_conf")
[ -n "$redis_pass" ]

printf 'service=sub-lb.tap365.org\nhost=82.29.54.80\nsource_root=%s\nbackup_dir=%s\npostgres_container=%s\npostgres_db=%s\nredis=%s:%s\ncreated_at=%s\n' \
  "$service_root" "$backup_dir" "$postgres_container" "$postgres_db" "$redis_host" "$redis_port" "$(date '+%F %T %z')" > "$backup_dir/manifest.txt"

echo "[2/7] 导出 PostgreSQL 全库"
docker exec -e PGPASSWORD="$pg_pass" "$postgres_container" \
  pg_dump -U "$pg_user" -d "$postgres_db" | gzip -9 > "$full_dir/postgres_sub2api.sql.gz"

echo "[3/7] 导出 schema + 结构化 CSV"
docker exec -e PGPASSWORD="$pg_pass" "$postgres_container" \
  pg_dump --schema-only -U "$pg_user" -d "$postgres_db" > "$structured_dir/schema_only.sql"

export_table() {
  local table_name="$1"
  docker exec -i -e PGPASSWORD="$pg_pass" "$postgres_container" \
    psql -X -U "$pg_user" -d "$postgres_db" -c "COPY (SELECT * FROM public.${table_name}) TO STDOUT WITH CSV HEADER" \
    > "$structured_dir/${table_name}.csv"
}

for tbl in \
  accounts account_groups groups proxies users api_keys user_subscriptions settings \
  payment_orders announcements invite_commission_records promo_codes promo_code_usages; do
  export_table "$tbl"
done

docker exec -i -e PGPASSWORD="$pg_pass" "$postgres_container" \
  psql -X -U "$pg_user" -d "$postgres_db" -At <<'SQL' > "$structured_dir/table_row_counts.tsv"
SELECT tablename || E'\t' || (
  xpath('/row/cnt/text()', query_to_xml(format('select count(*) as cnt from public.%I', tablename), false, true, ''))
)[1]::text
FROM pg_tables
WHERE schemaname='public'
ORDER BY tablename;
SQL

echo "[4/7] 导出 Redis RDB"
REDISCLI_AUTH="$redis_pass" redis-cli -h "$redis_host" -p "$redis_port" --rdb "$full_dir/redis_6379.rdb" >/dev/null

echo "[5/7] 采集运行态配置"
cp "$service_root/config.yaml" "$meta_dir/config.yaml"
test -f "$service_root/deploy/.env" && cp "$service_root/deploy/.env" "$meta_dir/deploy.env"
test -f "$service_root/backend/.env" && cp "$service_root/backend/.env" "$meta_dir/backend.env"
test -f "$service_root/deploy/start-sub2api.sh" && cp "$service_root/deploy/start-sub2api.sh" "$meta_dir/start-sub2api.sh"
systemctl cat sub2api-backup.service > "$meta_dir/sub2api-backup.service.txt" 2>/dev/null || true
docker inspect postgres > "$meta_dir/postgres.container.inspect.json"
docker inspect redis-base > "$meta_dir/redis.container.inspect.json"
tar -C "$meta_dir" -czf "$full_dir/runtime_config.tar.gz" .

echo "[6/7] 生成 sha256 清单"
(
  cd "$backup_dir"
  find . -type f ! -name 'sha256.txt' -print0 | sort -z | xargs -0 sha256sum > sha256.txt
)

echo "[7/7] 完成"
printf '%s\n' "$backup_dir"