#!/usr/bin/env bash
#
# Dump every database to a consistent on-disk file so restic snapshots a
# restorable copy rather than a live file mid-write.
#
# Design notes:
#
#  * SQLite dumps run from the HOST, not inside the containers. Vaultwarden
#    and SFTPGo images ship no sqlite3 binary and traggo is distroless with no
#    shell at all, so `podman exec` cannot work there. The DB files are on host
#    bind mounts and `.backup` is safe against a live writer.
#
#  * The Immich dump goes to the NFS mount (-> the NAS), NOT /var/backups.
#    Immich's blobs live on the NAS and its database on mainframe; writing the
#    dump beside the blobs means one NAS restic snapshot captures both halves.
#    A database restored against missing photos is not a restore.
#
#  * .env is PARSED, never sourced. OPENVPN_PASSWORD contains characters that
#    break `.` under bash, and quoting it changes what podman-compose passes to
#    the container -- so the file is left exactly as compose expects and read
#    with a plain parser instead.
#
#  * The sqlite paths are all under /home/connor/data/<svc>/. sftpgo's was
#    /home/connor/docs/sftpgo.db until the service moved its state onto the
#    /var/lib/sftpgo bind mount; the old path then just tripped the "db
#    missing, skipping" WARN, so sftpgo silently went unbacked-up from
#    2026-09-09 while the job still reported success. A skip is a WARN by
#    design -- check the WARNs, they do not fail the run.
#
#  * Every file is written to .tmp and renamed, so a concurrent backup never
#    snapshots a truncated dump.
#
#  * The zstd calls pass -f. Without it a single failed run wedges the job
#    PERMANENTLY: pg_dumpall fails (e.g. the db is not up yet right after a
#    boot), zstd still writes its empty frame, pipefail aborts before the mv,
#    and the orphaned .tmp then makes every later run die at once on
#    "zstd: ... already exists; not overwritten". That is what happened from
#    2026-09-15 to 2026-09-17 -- three nights with no backup, and the ntfy
#    failure text named the dump, not the stale file. The sqlite branch below
#    already rm -f'd its .tmp; these two did not.

set -euo pipefail
umask 077

DEST=/var/backups/db
IMMICH_DEST=/home/connor/photo/immich/backups
ENVFILE=/home/connor/.env

log() { printf '%s backup-db-dump: %s\n' "$(date -Is)" "$*"; }
env_get() { sed -n "s/^$1=//p" "$ENVFILE" | head -1 | sed -E "s/^'(.*)'\$/\1/; s/^\"(.*)\"\$/\1/"; }

mkdir -p "$DEST"
IMMICH_DB_USERNAME=$(env_get IMMICH_DB_USERNAME)
HEDGEDOC_DB_ROOT_PASSWORD=$(env_get HEDGEDOC_DB_ROOT_PASSWORD)
[ -n "$IMMICH_DB_USERNAME" ] || { log "ERROR: could not read IMMICH_DB_USERNAME"; exit 1; }

# --- Postgres (Immich) -----------------------------------------------------
if mountpoint -q /home/connor/photo && mkdir -p "$IMMICH_DEST" 2>/dev/null; then
    tgt="$IMMICH_DEST"
else
    log "WARN: /home/connor/photo not mounted; writing immich dump locally instead"
    tgt="$DEST"
fi
log "immich postgres -> $tgt"
podman exec connor_immich_db_1 sh -c "pg_dumpall -U '$IMMICH_DB_USERNAME'" \
    | zstd -q -f -o "$tgt/immich-pgdump.sql.zst.tmp"
mv -f "$tgt/immich-pgdump.sql.zst.tmp" "$tgt/immich-pgdump.sql.zst"

# --- MariaDB (HedgeDoc) ----------------------------------------------------
log "hedgedoc mariadb"
podman exec connor_hedgedocdb_1 sh -c \
    "mariadb-dump --single-transaction -u root -p'$HEDGEDOC_DB_ROOT_PASSWORD' hedgedoc" \
    | zstd -q -f -o "$DEST/hedgedoc.sql.zst.tmp"
mv -f "$DEST/hedgedoc.sql.zst.tmp" "$DEST/hedgedoc.sql.zst"

# --- SQLite ----------------------------------------------------------------
for spec in \
    "vaultwarden:/home/connor/data/bitwarden/db.sqlite3" \
    "gitea:/home/connor/data/gitea/gitea/gitea.db" \
    "traggo:/home/connor/data/traggo/traggo.db" \
    "sftpgo:/home/connor/data/sftpgo/sftpgo.db" \
    "shanty:/usr/local/shanty/shanty.db" \
; do
    name=${spec%%:*}; path=${spec#*:}
    if [ ! -f "$path" ]; then log "WARN: $name db missing at $path, skipping"; continue; fi
    log "$name sqlite"
    rm -f "$DEST/$name.sqlite3.tmp"
    sqlite3 "$path" ".backup '$DEST/$name.sqlite3.tmp'"
    # Verify before promoting. A corrupt dump that looks like a file is worse
    # than a missing one: it restores without complaint.
    if [ "$(sqlite3 "$DEST/$name.sqlite3.tmp" 'pragma integrity_check;' 2>&1 | head -1)" != "ok" ]; then
        log "ERROR: $name failed integrity_check, keeping previous dump"
        rm -f "$DEST/$name.sqlite3.tmp"; continue
    fi
    mv -f "$DEST/$name.sqlite3.tmp" "$DEST/$name.sqlite3"
done

log "done"
