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

# Migrate an AlphaX SQLite DB that lives inside a Docker container into the
# docker-compose host volume path: ./data/altcoin_monitor.db.
#
# Safe defaults:
# - uses sqlite3 backup API inside the container, so WAL data is included
# - backs up any existing host ./data/altcoin_monitor.db first
# - refuses to overwrite when the container is already reading from /app/data
# - recreates compose services after migration so the new volume is mounted
#
# Common usage on the server:
#   bash scripts/migrate_container_db_to_volume.sh
#
# Useful overrides:
#   SOURCE_CONTAINER=old-alphax-web bash scripts/migrate_container_db_to_volume.sh
#   SERVICE=alphax-web TARGET_DB=data/altcoin_monitor.db bash scripts/migrate_container_db_to_volume.sh
#   RECREATE=0 bash scripts/migrate_container_db_to_volume.sh
#   ALLOW_STOPPED=1 SOURCE_CONTAINER=old-alphax-web bash scripts/migrate_container_db_to_volume.sh

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
cd "$ROOT_DIR"

COMPOSE_CMD="${COMPOSE_CMD:-docker compose}"
SERVICE="${SERVICE:-alphax-web}"
SCHEDULER_SERVICE="${SCHEDULER_SERVICE:-alphax-scheduler}"
SOURCE_CONTAINER="${SOURCE_CONTAINER:-}"
SOURCE_DB="${SOURCE_DB:-/app/data/altcoin_monitor.db}"
TARGET_DB="${TARGET_DB:-data/altcoin_monitor.db}"
BACKUP_DIR="${BACKUP_DIR:-data/backups}"
RECREATE="${RECREATE:-1}"
STOP_SCHEDULER="${STOP_SCHEDULER:-1}"
FORCE="${FORCE:-0}"
ALLOW_STOPPED="${ALLOW_STOPPED:-1}"

compose() {
  ${COMPOSE_CMD} "$@"
}

die() {
  echo "ERROR: $*" >&2
  exit 1
}

info() {
  echo "[migrate-db] $*"
}

service_exists() {
  compose config --services 2>/dev/null | grep -qx "$1"
}

container_running() {
  local cid="$1"
  [ "$(docker inspect -f '{{.State.Running}}' "$cid" 2>/dev/null || echo false)" = "true" ]
}

container_name_for_log() {
  docker inspect -f '{{.Name}}' "$1" 2>/dev/null | sed 's#^/##'
}

container_id() {
  if [ -n "$SOURCE_CONTAINER" ]; then
    docker inspect -f '{{.Id}}' "$SOURCE_CONTAINER" >/dev/null 2>&1 || die "SOURCE_CONTAINER not found: $SOURCE_CONTAINER"
    docker inspect -f '{{.Id}}' "$SOURCE_CONTAINER"
    return
  fi
  local cid
  cid="$(compose ps -q "$SERVICE" 2>/dev/null || true)"
  if [ -n "$cid" ]; then
    echo "$cid"
    return
  fi

  # Fallback for servers where the old container was not created by the current
  # compose project, or the compose service name/project name changed.
  for name in "$SERVICE" alphax-web alphax_web alphax; do
    cid="$(docker ps -aq --filter "name=^/${name}$" | head -n 1)"
    if [ -n "$cid" ]; then
      echo "$cid"
      return
    fi
  done

  echo "ERROR: service '$SERVICE' is not running and no fallback container was found." >&2
  echo "" >&2
  echo "Run one of these on the server:" >&2
  echo "  docker compose ps" >&2
  echo "  docker ps -a --format 'table {{.Names}}\t{{.Status}}\t{{.Image}}'" >&2
  echo "" >&2
  echo "Then rerun with the actual container name, for example:" >&2
  echo "  SOURCE_CONTAINER=<container_name> bash scripts/migrate_container_db_to_volume.sh" >&2
  exit 1
}

is_source_db_under_mount() {
  local cid="$1"
  local mounted=1
  while IFS= read -r dest; do
    [ -n "$dest" ] || continue
    case "$SOURCE_DB" in
      "$dest"|"$dest"/*)
        mounted=0
        break
        ;;
    esac
  done < <(docker inspect -f '{{range .Mounts}}{{println .Destination}}{{end}}' "$cid")
  return "$mounted"
}

verify_db_in_container() {
  local cid="$1"
  local db_path="$2"
  docker exec -e CHECK_DB="$db_path" "$cid" python - <<'PY'
import os
import sqlite3
import sys

db = os.environ["CHECK_DB"]
conn = sqlite3.connect(f"file:{db}?mode=ro", uri=True, timeout=30)
result = conn.execute("PRAGMA integrity_check").fetchone()[0]
tables = conn.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'").fetchone()[0]
conn.close()
if result != "ok":
    print(f"integrity_check failed: {result}", file=sys.stderr)
    raise SystemExit(2)
print(f"integrity_check=ok tables={tables}")
PY
}

verify_db_on_host_if_possible() {
  local db_path="$1"
  if command -v python3 >/dev/null 2>&1; then
    HOST_DB="$db_path" python3 - <<'PY'
import os
import sqlite3
import sys

db = os.environ["HOST_DB"]
conn = sqlite3.connect(f"file:{db}?mode=ro", uri=True, timeout=30)
result = conn.execute("PRAGMA integrity_check").fetchone()[0]
tables = conn.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table'").fetchone()[0]
conn.close()
if result != "ok":
    print(f"host integrity_check failed: {result}", file=sys.stderr)
    raise SystemExit(2)
print(f"host integrity_check=ok tables={tables}")
PY
  else
    info "python3 not found on host; skipped host integrity check"
  fi
}

CID="$(container_id)"
STAMP="$(date +%Y%m%d_%H%M%S)"
TMP_IN_CONTAINER="/tmp/alphax_container_db_${STAMP}.db"
TMP_ON_HOST="${BACKUP_DIR}/altcoin_monitor.from_container.${STAMP}.tmp.db"

SOURCE_LABEL="${SOURCE_CONTAINER:-$(container_name_for_log "$CID")}"
info "source container: ${SOURCE_LABEL:-$SERVICE} ($CID)"
info "source db: $SOURCE_DB"
info "target db: $TARGET_DB"

RUNNING=0
if container_running "$CID"; then
  RUNNING=1
  docker exec -e SOURCE_DB="$SOURCE_DB" "$CID" sh -lc 'test -s "$SOURCE_DB"' \
    || die "source database does not exist or is empty inside container: $SOURCE_DB"

  if is_source_db_under_mount "$CID" && [ "$FORCE" != "1" ]; then
    info "container path '$SOURCE_DB' is already under a mounted volume/bind mount."
    info "No migration needed. Set FORCE=1 only if you intentionally want to copy it anyway."
    exit 0
  fi
else
  if [ "$ALLOW_STOPPED" != "1" ]; then
    die "source container is stopped. Start it first, or set ALLOW_STOPPED=1 to docker cp the DB from the stopped container."
  fi
  info "source container is stopped; falling back to docker cp. SQLite WAL files cannot be checkpointed in this mode."
fi

mkdir -p "$(dirname "$TARGET_DB")" "$BACKUP_DIR"

if [ "$STOP_SCHEDULER" = "1" ] && service_exists "$SCHEDULER_SERVICE"; then
  info "stopping scheduler service to avoid concurrent writes"
  compose stop "$SCHEDULER_SERVICE" >/dev/null 2>&1 || true
fi

if [ "$RUNNING" = "1" ]; then
  info "creating SQLite backup inside running container"
  docker exec \
    -e SOURCE_DB="$SOURCE_DB" \
    -e TMP_DB="$TMP_IN_CONTAINER" \
    "$CID" python - <<'PY'
import os
import sqlite3

src = os.environ["SOURCE_DB"]
dst = os.environ["TMP_DB"]
try:
    os.remove(dst)
except FileNotFoundError:
    pass

src_conn = sqlite3.connect(f"file:{src}?mode=ro", uri=True, timeout=30)
dst_conn = sqlite3.connect(dst, timeout=30)
src_conn.backup(dst_conn)
dst_conn.close()
src_conn.close()

check = sqlite3.connect(f"file:{dst}?mode=ro", uri=True, timeout=30)
result = check.execute("PRAGMA integrity_check").fetchone()[0]
check.close()
if result != "ok":
    raise SystemExit(f"container backup integrity_check failed: {result}")
print(f"backup_created={dst}")
PY

  info "copying backup from container to host"
  docker cp "${CID}:${TMP_IN_CONTAINER}" "$TMP_ON_HOST"
  docker exec -e TMP_DB="$TMP_IN_CONTAINER" "$CID" sh -lc 'rm -f "$TMP_DB"' >/dev/null 2>&1 || true
else
  info "copying raw DB from stopped container"
  docker cp "${CID}:${SOURCE_DB}" "$TMP_ON_HOST" \
    || die "failed to copy $SOURCE_DB from stopped container. Check SOURCE_DB path."
  for suffix in "-wal" "-shm"; do
    if docker cp "${CID}:${SOURCE_DB}${suffix}" "${TMP_ON_HOST}${suffix}" >/dev/null 2>&1; then
      info "copied sidecar ${SOURCE_DB}${suffix}"
    fi
  done
fi

verify_db_on_host_if_possible "$TMP_ON_HOST"

if [ -e "$TARGET_DB" ]; then
  EXISTING_BACKUP="${BACKUP_DIR}/altcoin_monitor.before_container_migration.${STAMP}.db"
  info "backing up existing target db to $EXISTING_BACKUP"
  cp -p "$TARGET_DB" "$EXISTING_BACKUP"
fi

for sidecar in "${TARGET_DB}-wal" "${TARGET_DB}-shm"; do
  if [ -e "$sidecar" ]; then
    sidecar_backup="${BACKUP_DIR}/$(basename "$sidecar").before_container_migration.${STAMP}"
    info "moving stale sidecar $sidecar to $sidecar_backup"
    mv "$sidecar" "$sidecar_backup"
  fi
done

info "installing migrated db into volume path"
mv "$TMP_ON_HOST" "$TARGET_DB"
chmod 664 "$TARGET_DB" || true

verify_db_on_host_if_possible "$TARGET_DB"

if [ "$RECREATE" = "1" ]; then
  info "recreating compose services so the host volume is mounted"
  compose up -d --force-recreate "$SERVICE"
  if service_exists "$SCHEDULER_SERVICE"; then
    compose up -d --force-recreate "$SCHEDULER_SERVICE"
  fi

  NEW_CID="$(compose ps -q "$SERVICE" 2>/dev/null || true)"
  if [ -n "$NEW_CID" ]; then
    info "verifying migrated db from recreated container"
    verify_db_in_container "$NEW_CID" "$SOURCE_DB"
  fi
else
  info "RECREATE=0, skipped service recreation"
fi

info "done"
info "volume db is now: $ROOT_DIR/$TARGET_DB"
info "backups are in: $ROOT_DIR/$BACKUP_DIR"