#!/bin/bash
# SQLite backup script for stats.wise-relations.com — runs hourly via cron.
#
# Pattern follows lobout.com/scripts/backup.sh.
#
# Performs a WAL-safe SQLite backup using the .backup command (not a file copy),
# then optionally uploads to Hetzner Object Storage (S3-compatible).
#
# Retention:
#   - Hourly backups kept for 7 days
#   - Midnight backups (filename ends in -0000.sqlite) kept for 30 days
#
# Cron entry (installed by install.sh):
#   0 * * * * root bash /opt/stats/backup.sh >> /var/log/stats-backup.log 2>&1

set -euo pipefail

# ── Config ─────────────────────────────────────────────────────────────────────
INSTALL_DIR="/opt/stats"
DATA_DIR="$INSTALL_DIR/data"
DB_FILE="$DATA_DIR/db.sqlite"
BACKUP_DIR="$INSTALL_DIR/backups"
TIMESTAMP=$(date +%Y%m%d-%H%M)
BACKUP_FILE="$BACKUP_DIR/db-$TIMESTAMP.sqlite"
STATUS_FILE="$BACKUP_DIR/last_backup.json"

# Optional Hetzner Object Storage (S3-compatible). Reads from /opt/stats/env
# if it exists (same credentials file the service uses).
if [[ -f "$INSTALL_DIR/env" ]]; then
    # shellcheck disable=SC1091
    set -a; source "$INSTALL_DIR/env"; set +a
fi
S3_BUCKET="${S3_BUCKET:-}"
S3_ENDPOINT="${S3_ENDPOINT:-https://fsn1.your-objectstorage.com}"

# ── Ensure backup dir exists ───────────────────────────────────────────────────
mkdir -p "$BACKUP_DIR"

# ── Sanity check ───────────────────────────────────────────────────────────────
echo "[$(date -Iseconds)] Starting backup..."

if [[ ! -f "$DB_FILE" ]]; then
    echo "ERROR: Database file not found: $DB_FILE"
    echo '{"status":"error","error":"database file not found","timestamp":"'"$(date -Iseconds)"'"}' > "$STATUS_FILE"
    exit 1
fi

# ── Backup using sqlite3 .backup (WAL-safe, consistent snapshot) ───────────────
sqlite3 "$DB_FILE" ".backup '$BACKUP_FILE'"
BACKUP_SIZE=$(stat -c%s "$BACKUP_FILE" 2>/dev/null || stat -f%z "$BACKUP_FILE" 2>/dev/null)
echo "[$(date -Iseconds)] Local backup done: $BACKUP_FILE ($BACKUP_SIZE bytes)"

# ── Upload to S3 (if configured) ───────────────────────────────────────────────
S3_STATUS="skipped"
if [[ -n "$S3_BUCKET" ]]; then
    if command -v aws &>/dev/null; then
        aws s3 cp "$BACKUP_FILE" "s3://$S3_BUCKET/stats-backups/db-$TIMESTAMP.sqlite" \
            --endpoint-url "$S3_ENDPOINT" \
            --quiet
        S3_STATUS="uploaded"
        echo "[$(date -Iseconds)] Uploaded to s3://$S3_BUCKET/stats-backups/db-$TIMESTAMP.sqlite"
    else
        S3_STATUS="error: aws cli not installed"
        echo "WARNING: aws cli not found, skipping S3 upload"
    fi
fi

# ── Write status JSON ──────────────────────────────────────────────────────────
cat > "$STATUS_FILE" <<EOF
{
    "status": "ok",
    "timestamp": "$(date -Iseconds)",
    "backup_file": "db-$TIMESTAMP.sqlite",
    "backup_size_bytes": $BACKUP_SIZE,
    "s3_upload": "$S3_STATUS"
}
EOF

# ── Retention: delete old backups ─────────────────────────────────────────────
# Delete all hourly backups older than 7 days.
find "$BACKUP_DIR" -name "db-*.sqlite" -mtime +7 -delete 2>/dev/null || true

# Keep midnight backups (filename contains -0000.sqlite) for 30 days.
# Hourly cleanup above already removed non-midnight files after 7 days.
# Delete midnight backups older than 30 days.
find "$BACKUP_DIR" -name "db-*-0000.sqlite" -mtime +30 -delete 2>/dev/null || true

echo "[$(date -Iseconds)] Backup complete."
