CaseGambling/scripts/merge-player-db.sh

#!/usr/bin/env bash
# Merge one player's rows from a source SQLite DB into a target SQLite DB.
#
# Usage (sourced or executed):
#   ./scripts/merge-player-db.sh <source.db> <target.db> <username>
#
# Replaces any existing target user with the same username.
# Copies (when present in both DBs): User, InventoryItem, Transaction,
#   PaymentOrder, CaseKeyProgress, CatalogEntry, PrestigeLog, UserAchievement.
# Skips multiplayer state (Jackpot*/Duel*).
# Tolerates older backups missing newer tables/columns (e.g. PaymentOrder).
#
# Requires matching Item / Case / Achievement ids in both DBs (same catalog lineage).
# Requires sqlite3.
set -euo pipefail

SRC_DB="${1:-}"
DST_DB="${2:-}"
USERNAME="${3:-}"

if [[ -z "$SRC_DB" || -z "$DST_DB" || -z "$USERNAME" ]]; then
  echo "Usage: $0 <source.db> <target.db> <username>" >&2
  exit 1
fi

if [[ ! -f "$SRC_DB" ]]; then
  echo "Error: source DB not found: $SRC_DB" >&2
  exit 1
fi

if [[ ! -f "$DST_DB" ]]; then
  echo "Error: target DB not found: $DST_DB" >&2
  echo "Tip: run a full import once first, or start the app so the DB exists." >&2
  exit 1
fi

if ! command -v sqlite3 >/dev/null 2>&1; then
  echo "Error: sqlite3 is required for player-only import" >&2
  exit 1
fi

sql_quote() {
  local s="$1"
  s="${s//\'/\'\'}"
  printf "'%s'" "$s"
}

table_exists() {
  local db="$1"
  local table="$2"
  local n
  n="$(sqlite3 "$db" "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=$(sql_quote "$table");")"
  [[ "$n" -gt 0 ]]
}

# Column names for a table (space-separated), excluding names passed after $2
table_cols() {
  local db="$1"
  local table="$2"
  shift 2
  local -A skip=()
  local e col
  for e in "$@"; do
    skip["$e"]=1
  done
  while IFS='|' read -r _ col _; do
    [[ -n "$col" && -z "${skip[$col]:-}" ]] && printf '%s\n' "$col"
  done < <(sqlite3 -separator '|' "$db" "PRAGMA table_info($(sql_quote "$table"));")
}

# Intersection of columns present in BOTH DBs, preserving destination order.
common_cols() {
  local table="$1"
  shift
  local -A src_set=()
  local col
  while IFS= read -r col; do
    [[ -n "$col" ]] && src_set["$col"]=1
  done < <(table_cols "$SRC_DB" "$table" "$@")

  local -a out=()
  while IFS= read -r col; do
    [[ -n "$col" && -n "${src_set[$col]:-}" ]] && out+=("$col")
  done < <(table_cols "$DST_DB" "$table" "$@")

  local IFS=', '
  echo "${out[*]}"
}

count_rows() {
  local table="$1"
  local qtable="$1"
  [[ "$table" == "Transaction" ]] && qtable='"Transaction"'
  if table_exists "$SRC_DB" "$table"; then
    sqlite3 "$SRC_DB" "SELECT COUNT(*) FROM $qtable WHERE userId = $SRC_UID;"
  else
    echo "skip"
  fi
}

SRC_UID="$(sqlite3 "$SRC_DB" "SELECT id FROM User WHERE username = $(sql_quote "$USERNAME") LIMIT 1;")"
if [[ -z "$SRC_UID" ]]; then
  echo "Error: no user with username '$USERNAME' in source DB" >&2
  exit 2
fi

echo "==> Merging player '$USERNAME' (source id=$SRC_UID)"
echo "    source: $SRC_DB"
echo "    target: $DST_DB"

N_INV="$(count_rows InventoryItem)"
N_TX="$(count_rows Transaction)"
N_PAY="$(count_rows PaymentOrder)"
N_KEY="$(count_rows CaseKeyProgress)"
N_CAT="$(count_rows CatalogEntry)"
N_PRE="$(count_rows PrestigeLog)"
N_ACH="$(count_rows UserAchievement)"
echo "    rows: inventory=$N_INV tx=$N_TX payments=$N_PAY keys=$N_KEY catalog=$N_CAT prestige=$N_PRE achievements=$N_ACH"

USER_COLS="$(common_cols User id)"
if [[ -z "$USER_COLS" ]]; then
  echo "Error: no common User columns between source and target" >&2
  exit 1
fi

INV_COLS="$(common_cols InventoryItem id userId)"
TX_COLS="$(common_cols Transaction id userId)"
if [[ -z "$INV_COLS" || -z "$TX_COLS" ]]; then
  echo "Error: InventoryItem / Transaction schema mismatch" >&2
  exit 1
fi

PAY_COLS=""
if table_exists "$SRC_DB" PaymentOrder && table_exists "$DST_DB" PaymentOrder; then
  PAY_COLS="$(common_cols PaymentOrder userId)"
fi
KEY_COLS=""
table_exists "$SRC_DB" CaseKeyProgress && table_exists "$DST_DB" CaseKeyProgress && KEY_COLS="$(common_cols CaseKeyProgress id userId)"
CAT_COLS=""
table_exists "$SRC_DB" CatalogEntry && table_exists "$DST_DB" CatalogEntry && CAT_COLS="$(common_cols CatalogEntry id userId)"
PRE_COLS=""
table_exists "$SRC_DB" PrestigeLog && table_exists "$DST_DB" PrestigeLog && PRE_COLS="$(common_cols PrestigeLog id userId)"
ACH_COLS=""
table_exists "$SRC_DB" UserAchievement && table_exists "$DST_DB" UserAchievement && ACH_COLS="$(common_cols UserAchievement id userId)"

SQL_EXTRA_DELETE=""
SQL_EXTRA_INSERT=""

if [[ -n "$PAY_COLS" ]]; then
  SQL_EXTRA_DELETE+="
DELETE FROM PaymentOrder
 WHERE id IN (SELECT id FROM src.PaymentOrder WHERE userId = $SRC_UID);
"
  SQL_EXTRA_INSERT+="
INSERT INTO PaymentOrder ($PAY_COLS, userId)
SELECT $PAY_COLS, (SELECT newId FROM _merge_uid)
FROM src.PaymentOrder WHERE userId = $SRC_UID;
"
else
  echo "    note: PaymentOrder absent in backup — skipped (osu top-up orders)"
fi

if [[ -n "$KEY_COLS" ]]; then
  SQL_EXTRA_INSERT+="
INSERT INTO CaseKeyProgress (userId, $KEY_COLS)
SELECT (SELECT newId FROM _merge_uid), $KEY_COLS
FROM src.CaseKeyProgress WHERE userId = $SRC_UID;
"
fi

if [[ -n "$CAT_COLS" ]]; then
  SQL_EXTRA_INSERT+="
INSERT INTO CatalogEntry (userId, $CAT_COLS)
SELECT (SELECT newId FROM _merge_uid), $CAT_COLS
FROM src.CatalogEntry WHERE userId = $SRC_UID;
"
fi

if [[ -n "$PRE_COLS" ]]; then
  SQL_EXTRA_INSERT+="
INSERT INTO PrestigeLog (userId, $PRE_COLS)
SELECT (SELECT newId FROM _merge_uid), $PRE_COLS
FROM src.PrestigeLog WHERE userId = $SRC_UID;
"
fi

if [[ -n "$ACH_COLS" ]]; then
  SQL_EXTRA_INSERT+="
INSERT INTO UserAchievement (userId, $ACH_COLS)
SELECT (SELECT newId FROM _merge_uid), $ACH_COLS
FROM src.UserAchievement WHERE userId = $SRC_UID;
"
fi

SQL_CLEANUP=""
table_exists "$DST_DB" JackpotRound && SQL_CLEANUP+="
UPDATE JackpotRound SET winnerUserId = NULL
  WHERE winnerUserId IN (SELECT id FROM User WHERE username = $(sql_quote "$USERNAME"));
"
table_exists "$DST_DB" DuelRoom && SQL_CLEANUP+="
UPDATE DuelRoom SET winnerUserId = NULL
  WHERE winnerUserId IN (SELECT id FROM User WHERE username = $(sql_quote "$USERNAME"));
DELETE FROM DuelRoom
  WHERE creatorId IN (SELECT id FROM User WHERE username = $(sql_quote "$USERNAME"));
"
table_exists "$DST_DB" JackpotDeposit && SQL_CLEANUP+="
DELETE FROM JackpotDeposit
  WHERE userId IN (SELECT id FROM User WHERE username = $(sql_quote "$USERNAME"));
"
table_exists "$DST_DB" DuelDeposit && SQL_CLEANUP+="
DELETE FROM DuelDeposit
  WHERE userId IN (SELECT id FROM User WHERE username = $(sql_quote "$USERNAME"));
"

sqlite3 "$DST_DB" <<SQL
PRAGMA foreign_keys = ON;
ATTACH DATABASE $(sql_quote "$SRC_DB") AS src;

BEGIN;

$SQL_CLEANUP
DELETE FROM User WHERE username = $(sql_quote "$USERNAME");

$SQL_EXTRA_DELETE

INSERT INTO User ($USER_COLS)
SELECT $USER_COLS
FROM src.User WHERE id = $SRC_UID;

CREATE TEMP TABLE _merge_uid AS
  SELECT id AS newId FROM User WHERE username = $(sql_quote "$USERNAME") LIMIT 1;

INSERT INTO InventoryItem (userId, $INV_COLS)
SELECT (SELECT newId FROM _merge_uid), $INV_COLS
FROM src.InventoryItem WHERE userId = $SRC_UID;

INSERT INTO "Transaction" (userId, $TX_COLS)
SELECT (SELECT newId FROM _merge_uid), $TX_COLS
FROM src."Transaction" WHERE userId = $SRC_UID;

$SQL_EXTRA_INSERT

DROP TABLE _merge_uid;

COMMIT;
DETACH DATABASE src;
SQL

NEW_UID="$(sqlite3 "$DST_DB" "SELECT id FROM User WHERE username = $(sql_quote "$USERNAME") LIMIT 1;")"
echo "==> Player merged as target id=$NEW_UID"
echo "    (jackpot/duel deposits & rooms not imported)"