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

# Install a database file from the project root into the docker-compose volume
# path used by AlphaX: ./data/altcoin_monitor.db.
#
# Server usage:
#   bash scripts/install_root_db_to_volume.sh
#
# Defaults:
#   source: ./altcoin_monitor.db
#   target: ./data/altcoin_monitor.db
#
# Overrides:
#   ROOT_DB=./my_backup.db bash scripts/install_root_db_to_volume.sh
#   RESTART=0 bash scripts/install_root_db_to_volume.sh
#   BACKUP_OLD=0 bash scripts/install_root_db_to_volume.sh

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

COMPOSE_CMD="${COMPOSE_CMD:-docker compose}"
ROOT_DB="${ROOT_DB:-altcoin_monitor.db}"
TARGET_DB="${TARGET_DB:-data/altcoin_monitor.db}"
BACKUP_DIR="${BACKUP_DIR:-data/backups}"
RESTART="${RESTART:-1}"
BACKUP_OLD="${BACKUP_OLD:-1}"
WEB_SERVICE="${WEB_SERVICE:-alphax-web}"
SCHEDULER_SERVICE="${SCHEDULER_SERVICE:-alphax-scheduler}"

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

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

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

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

verify_db() {
  local db_path="$1"
  command -v python3 >/dev/null 2>&1 || {
    info "python3 not found; skipped integrity check for $db_path"
    return 0
  }
  HOST_DB="$db_path" python3 - <<'PY'
import os
import sqlite3
import sys

db = os.environ["HOST_DB"]
try:
    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()
except sqlite3.DatabaseError as exc:
    print(f"database check failed for {db}: {exc}", file=sys.stderr)
    print("", file=sys.stderr)
    print("This file is not safe to install as the live AlphaX DB.", file=sys.stderr)
    print("Common causes:", file=sys.stderr)
    print("  1. The DB was copied while SQLite WAL mode was active, but the matching .db-wal file was not copied.", file=sys.stderr)
    print("  2. The upload/copy was interrupted and the DB file is truncated.", file=sys.stderr)
    print("  3. The source path is not the real SQLite database file.", file=sys.stderr)
    print("", file=sys.stderr)
    print("On the server, check:", file=sys.stderr)
    print("  ls -lh altcoin_monitor.db*", file=sys.stderr)
    print("  file altcoin_monitor.db", file=sys.stderr)
    print("", file=sys.stderr)
    print("Best fix: export a clean backup from the old machine/container using SQLite backup, then upload that .db file.", file=sys.stderr)
    raise SystemExit(2)
if result != "ok":
    print(f"integrity_check failed for {db}: {result}", file=sys.stderr)
    raise SystemExit(2)
print(f"integrity_check=ok tables={tables} db={db}")
PY
}

[ -f "$ROOT_DB" ] || die "root database not found: $ROOT_DIR/$ROOT_DB"
[ -s "$ROOT_DB" ] || die "root database is empty: $ROOT_DIR/$ROOT_DB"

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

info "source root db: $ROOT_DIR/$ROOT_DB"
info "target volume db: $ROOT_DIR/$TARGET_DB"
info "root db candidates:"
ls -lh "$ROOT_DB"* 2>/dev/null || true

verify_db "$ROOT_DB"

if [ "$RESTART" = "1" ]; then
  info "stopping compose services before replacing SQLite DB"
  if service_exists "$SCHEDULER_SERVICE"; then
    compose stop "$SCHEDULER_SERVICE" >/dev/null 2>&1 || true
  fi
  if service_exists "$WEB_SERVICE"; then
    compose stop "$WEB_SERVICE" >/dev/null 2>&1 || true
  fi
fi

STAMP="$(date +%Y%m%d_%H%M%S)"

if [ "$BACKUP_OLD" = "1" ] && [ -e "$TARGET_DB" ]; then
  OLD_BACKUP="${BACKUP_DIR}/altcoin_monitor.volume_before_root_install.${STAMP}.db"
  info "backing up old volume db to $OLD_BACKUP"
  cp -p "$TARGET_DB" "$OLD_BACKUP"
fi

for sidecar in "${TARGET_DB}-wal" "${TARGET_DB}-shm"; do
  if [ -e "$sidecar" ]; then
    if [ "$BACKUP_OLD" = "1" ]; then
      sidecar_backup="${BACKUP_DIR}/$(basename "$sidecar").volume_before_root_install.${STAMP}"
      info "moving old sidecar $sidecar to $sidecar_backup"
      mv "$sidecar" "$sidecar_backup"
    else
      info "removing old sidecar $sidecar"
      rm -f "$sidecar"
    fi
  fi
done

info "installing root db into volume path"
cp -p "$ROOT_DB" "$TARGET_DB"
chmod 664 "$TARGET_DB" || true

verify_db "$TARGET_DB"

if [ "$RESTART" = "1" ]; then
  info "starting compose services"
  if service_exists "$WEB_SERVICE"; then
    compose up -d "$WEB_SERVICE"
  fi
  if service_exists "$SCHEDULER_SERVICE"; then
    compose up -d "$SCHEDULER_SERVICE"
  fi
fi

info "done"
info "system database path: $ROOT_DIR/$TARGET_DB"
info "old backups path: $ROOT_DIR/$BACKUP_DIR"