#!/usr/bin/env bash
# ─────────────────────────────────────────────────────────────────────────────
#  QuantumCals — MySQL backup to Google Cloud Storage + report to the admin panel
#
#  What it does:
#    1. dumps all databases (central + every tenant) with mysqldump, gzipped
#    2. uploads the file to a GCS bucket
#    3. records the run in Admin → Logs → Backups (success or failure)
#    4. deletes local copies older than KEEP_LOCAL_DAYS
#
#  Setup (once):   see the "SETUP" notes at the bottom of this file.
#  Crontab:        0 2 * * * /opt/qc-backup/db-backup.sh >> /var/log/qc-db-backup.log 2>&1
# ─────────────────────────────────────────────────────────────────────────────
set -Eeuo pipefail

# ── settings — edit these ────────────────────────────────────────────────────
APP_DIR="/var/www/limsv2"                  # folder that contains limsv2's `artisan`
PHP_BIN="$(command -v php || echo /usr/bin/php)"
BUCKET="gs://YOUR_BUCKET_NAME/db-backups"   # where backups are uploaded
LOCAL_DIR="/var/backups/qc-mysql"           # local copy before upload
KEEP_LOCAL_DAYS=3                           # local files older than this are removed
BACKUP_NAME="mysql-daily"                   # label shown in the admin panel
MYSQL_DEFAULTS="/root/.my.cnf"              # MySQL user/password live here, not in this script

# How to report to the admin panel — pick ONE:
#   • same server as limsv2  → leave ADMIN_URL empty (uses `php artisan backup:log`)
#   • different server       → set ADMIN_URL + BACKUP_LOG_TOKEN (reports over HTTPS)
ADMIN_URL=""                                # e.g. https://admin.quantumcals.com  (no trailing slash)
BACKUP_LOG_TOKEN=""                         # same value as BACKUP_LOG_TOKEN in limsv2's .env
# ─────────────────────────────────────────────────────────────────────────────

STAMP="$(date +%Y-%m-%d_%H%M)"
FILE="qc_all_${STAMP}.sql.gz"
LOCAL_FILE="${LOCAL_DIR}/${FILE}"
STARTED_AT="$(date '+%Y-%m-%d %H:%M:%S')"
SECONDS=0
ERR_FILE="$(mktemp)"

report() {   # report <status> [--key=value ...]  -> admin panel (artisan locally, HTTPS API remotely)
  local status="$1"; shift
  if [ -z "$ADMIN_URL" ]; then
    "$PHP_BIN" "$APP_DIR/artisan" backup:log "$status" "$@" --name="$BACKUP_NAME" --started-at="$STARTED_AT" --source=cron \
      || echo "WARN: could not write the backup log (artisan)" >&2
    return 0
  fi
  # remote: translate --key=value options into API fields
  local args=(--data-urlencode "status=$status" --data-urlencode "backup_name=$BACKUP_NAME"
              --data-urlencode "started_at=$STARTED_AT" --data-urlencode "host=$(hostname)")
  local opt key val
  for opt in "$@"; do
    key="${opt%%=*}"; key="${key#--}"; val="${opt#*=}"
    case "$key" in
      database) key=database_name ;;  file) key=file_name ;;  size) key=file_size ;;
      path) key=storage_path ;;       duration) key=duration_seconds ;;
    esac
    args+=(--data-urlencode "${key}=${val}")
  done
  curl -fsS --max-time 30 --retry 3 --retry-delay 5 -X POST "${ADMIN_URL}/api/backup-log" \
    -H "X-Backup-Token: ${BACKUP_LOG_TOKEN}" -H "Accept: application/json" "${args[@]}" >/dev/null \
    || echo "WARN: could not reach ${ADMIN_URL}/api/backup-log (check ADMIN_URL / token)" >&2
}

on_error() {
  local msg; msg="$(tail -c 900 "$ERR_FILE" | tr '\n' ' ')"
  echo "BACKUP FAILED: ${msg:-unknown error}" >&2
  report failed --database=all --duration="$SECONDS" --message="${msg:-Backup script failed at line $1}"
  rm -f "$ERR_FILE"
  exit 1
}
trap 'on_error $LINENO' ERR

mkdir -p "$LOCAL_DIR"
echo "[$STARTED_AT] backup started → $LOCAL_FILE"

# 1) dump every database (consistent snapshot for InnoDB, includes routines/triggers/events)
mysqldump --defaults-extra-file="$MYSQL_DEFAULTS" \
  --all-databases --single-transaction --quick --routines --triggers --events \
  2>>"$ERR_FILE" | gzip -6 > "$LOCAL_FILE"

# sanity check: a real dump is never tiny
SIZE="$(stat -c%s "$LOCAL_FILE")"
if [ "$SIZE" -lt 10240 ]; then echo "dump is only ${SIZE} bytes — looks broken" >>"$ERR_FILE"; false; fi
gzip -t "$LOCAL_FILE" 2>>"$ERR_FILE"
CHECKSUM="$(sha256sum "$LOCAL_FILE" | cut -d' ' -f1)"

# 2) upload to Google Cloud Storage (gcloud storage, falls back to gsutil)
if command -v gcloud >/dev/null 2>&1; then
  gcloud storage cp "$LOCAL_FILE" "${BUCKET}/${FILE}" --quiet 2>>"$ERR_FILE"
else
  gsutil -q cp "$LOCAL_FILE" "${BUCKET}/${FILE}" 2>>"$ERR_FILE"
fi

# 3) record success in the admin panel
report success --database=all --file="$FILE" --size="$SIZE" --path="${BUCKET}/${FILE}" \
  --checksum="$CHECKSUM" --duration="$SECONDS"

# 4) tidy up local copies
find "$LOCAL_DIR" -name 'qc_all_*.sql.gz' -type f -mtime +"$KEEP_LOCAL_DAYS" -delete
rm -f "$ERR_FILE"
echo "[$(date '+%Y-%m-%d %H:%M:%S')] backup OK — ${FILE} (${SIZE} bytes, ${SECONDS}s)"

# ─────────────────────────────────────────────────────────────────────────────
#  SETUP (run once on the Google Cloud VM, as root or with sudo)
#
#  a) put the script in place and make it executable
#       sudo mkdir -p /opt/qc-backup
#       sudo cp /var/www/limsv2/scripts/db-backup.sh /opt/qc-backup/db-backup.sh
#       sudo chmod 700 /opt/qc-backup/db-backup.sh
#
#  b) store the MySQL login in /root/.my.cnf (only root can read it)
#       [client]
#       user=backup_user
#       password=YOUR_MYSQL_PASSWORD
#       host=127.0.0.1
#     then: sudo chmod 600 /root/.my.cnf
#
#  c) make sure the VM can write to the bucket (once):
#       gcloud storage ls gs://YOUR_BUCKET_NAME     # should list without error
#
#  d) run it once by hand, then check Admin → Logs → Backups
#       sudo /opt/qc-backup/db-backup.sh
#
#  e) backup runs on a DIFFERENT server than limsv2? set ADMIN_URL and
#     BACKUP_LOG_TOKEN above (token: `openssl rand -hex 32`, same value in limsv2 .env,
#     then `php artisan config:clear` on the limsv2 server). Test the link:
#       curl -i -X POST "$ADMIN_URL/api/backup-log" -H "X-Backup-Token: $TOKEN" \
#            -H "Accept: application/json" -d status=running -d backup_name=link-test
#     -> "HTTP/2 201" means the backup server is connected to the admin panel.
#
#  f) schedule it:  sudo crontab -e   and add
#       0 2 * * * /opt/qc-backup/db-backup.sh >> /var/log/qc-db-backup.log 2>&1
# ─────────────────────────────────────────────────────────────────────────────
