#!/bin/bash
# ============================================================================
# DigiServer - Database Backup (restore-ready for Docker image / host upgrades)
# ----------------------------------------------------------------------------
# Produces a timestamped, self-contained bundle under ./backups/<timestamp>/:
#
#   dashboard.db.gz          - CONSISTENT full snapshot of the SQLite database
#                              (player, playlist, playlist_content,
#                               player_feedback and all other tables)
#   players_playlists.sql.gz - logical dump of player/playlist tables
#                              (secondary safety net; human-readable)
#   SHA256SUMS               - integrity check
#   MANIFEST.txt             - row counts, sizes, checksums, app version
#   RESTORE.md               - copy-paste restore instructions for this bundle
#
# The bundle is designed so that after a `docker compose build` + host upgrade
# you can run ./restore_database.sh <timestamp> and be back exactly as before.
#
# Usage:
#   ./backup_players_playlists.sh                # full restore-ready backup
#   ./backup_players_playlists.sh --logical-only # SQL dump only (no 2.2GB copy)
# ============================================================================
set -euo pipefail

PROJECT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
COMPOSE="docker compose -f $PROJECT_DIR/docker-compose.yml"
CONTAINER="digiserver-v2"
DB_HOST="$PROJECT_DIR/data/instance/dashboard.db"
DB_CTR="/app/instance/dashboard.db"
SNAP_CTR="/app/instance/__snapshot.db"
BACKUP_ROOT="$PROJECT_DIR/backups"
TS="$(date +%Y%m%d_%H%M%S)"
DEST="$BACKUP_ROOT/$TS"

ALL_DUMP_TABLES=(player playlist playlist_content player_user player_edit content group group_content)

LOGICAL_ONLY=0
[ "${1:-}" = "--logical-only" ] && LOGICAL_ONLY=1

mkdir -p "$DEST"
echo "============================================================"
echo " DigiServer restore-ready backup"
echo " Target: $DEST"
echo "============================================================"

if [ ! -f "$DB_HOST" ]; then
  echo "ERROR: database not found at $DB_HOST"
  exit 1
fi

# ---------------------------------------------------------------------------
# 1. Consistent snapshot via SQLite online backup API (safe while app runs)
# ---------------------------------------------------------------------------
if [ "$LOGICAL_ONLY" -eq 0 ]; then
  echo
  echo "[1/5] Creating consistent DB snapshot (online backup API)..."
  $COMPOSE exec -T digiserver-app python - <<'PYEOF'
import sqlite3
src = sqlite3.connect('/app/instance/dashboard.db')
dst = sqlite3.connect('/app/instance/__snapshot.db')
with dst:
    src.backup(dst)
dst.close(); src.close()
print("  snapshot created")
PYEOF

  docker cp "$CONTAINER:$SNAP_CTR" "$DEST/dashboard.db"
  $COMPOSE exec -T digiserver-app rm -f "$SNAP_CTR"
  echo "  -> size: $(du -h "$DEST/dashboard.db" | cut -f1)"

  echo
  echo "[2/5] Compressing snapshot..."
  gzip -f "$DEST/dashboard.db"
  echo "  -> $(ls -lh "$DEST/dashboard.db.gz" | awk '{print $5}')"
else
  echo
  echo "[1/5] Skipping full snapshot (--logical-only)"
  echo "[2/5] Skipping compression"
fi

# ---------------------------------------------------------------------------
# 3. Logical SQL dump (secondary safety net)
# ---------------------------------------------------------------------------
echo
echo "[3/5] Creating logical SQL dump of player/playlist tables..."
TABLE_LIST=$(printf '"%s",' "${ALL_DUMP_TABLES[@]}")
TABLE_LIST="[${TABLE_LIST%,}]"

$COMPOSE exec -T digiserver-app python - "$TABLE_LIST" > "$DEST/players_playlists.sql" <<'PYEOF'
import sqlite3, sys
tables = eval(sys.argv[1])
con = sqlite3.connect('/app/instance/dashboard.db')
o = sys.stdout
o.write("-- DigiServer logical backup: players & playlists\n")
o.write("-- Tables: %s\n\n" % ", ".join(tables))
o.write("PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n\n")
for t in tables:
    row = con.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (t,)).fetchone()
    if not row or not row[0]:
        continue
    o.write("DROP TABLE IF EXISTS \"%s\";\n" % t)
    o.write(row[0].rstrip() + ";\n")
    cur = con.execute('SELECT * FROM "%s"' % t)
    cols = [d[0] for d in cur.description]
    collist = ",".join('"%s"' % c for c in cols)
    batch = []
    def flush(b):
        if b:
            o.write("INSERT INTO \"%s\" (%s) VALUES\n%s;\n" % (t, collist, ",\n".join(b)))
    for rec in cur:
        vals = []
        for v in rec:
            if v is None:                       vals.append("NULL")
            elif isinstance(v, (int, float)):   vals.append(str(v))
            elif isinstance(v, bytes):          vals.append("X'%s'" % v.hex())
            else:                               vals.append("'" + str(v).replace("'", "''") + "'")
        batch.append("(%s)" % ",".join(vals))
        if len(batch) >= 500:
            flush(batch); batch = []
    flush(batch)
    o.write("\n")
o.write("COMMIT;\n")
PYEOF

gzip -f "$DEST/players_playlists.sql"
echo "  -> $(ls -lh "$DEST/players_playlists.sql.gz" | awk '{print $5}')"

# ---------------------------------------------------------------------------
# 4. Checksums
# ---------------------------------------------------------------------------
echo
echo "[4/5] Writing checksums..."
( cd "$DEST" && sha256sum *.gz > SHA256SUMS )
sed 's/^/  /' "$DEST/SHA256SUMS"

# ---------------------------------------------------------------------------
# 5. Manifest + restore instructions
# ---------------------------------------------------------------------------
echo
echo "[5/5] Writing MANIFEST.txt and RESTORE.md..."
{
  echo "DigiServer backup manifest"
  echo "=========================="
  echo "Created:        $(date -Iseconds)"
  echo "Host:           $(hostname)"
  echo "Backup ID:      $TS"
  echo "Source DB:      $DB_HOST"
  echo "Source DB size: $(du -h "$DB_HOST" | cut -f1)"
  echo ""
  echo "Row counts at backup time:"
  $COMPOSE exec -T digiserver-app python - <<'PYEOF' 2>/dev/null || echo "  (unavailable)"
import sqlite3
con = sqlite3.connect('/app/instance/dashboard.db')
for t in ['player','playlist','playlist_content','player_user','player_edit',
          'player_feedback','content','group','group_content','user']:
    try:
        n = con.execute('SELECT COUNT(*) FROM "%s"' % t).fetchone()[0]
        print('  %-20s %9d' % (t, n))
    except Exception as e:
        print('  %-20s ERROR %s' % (t, e))
PYEOF
  echo ""
  echo "Files:"
  ( cd "$DEST" && ls -lh | tail -n +2 | sed 's/^/  /' )
} > "$DEST/MANIFEST.txt"

cat > "$DEST/RESTORE.md" <<EOF
# Restore this DigiServer backup

Backup ID: **$TS**
Created:   $(date -Iseconds)

## What's in this bundle

| File | Purpose |
|------|---------|
| \`dashboard.db.gz\` | Full consistent snapshot (all tables) - primary restore source |
| \`players_playlists.sql.gz\` | Logical dump of player/playlist tables - secondary source |
| \`SHA256SUMS\` | Integrity check |
| \`MANIFEST.txt\` | Row counts + metadata |

## Restore procedure (after Docker image / host upgrade)

Run from the project root (\`$PROJECT_DIR\`):

\`\`\`bash
./restore_database.sh $TS
\`\`\`

Or do it manually:

\`\`\`bash
# 1. Stop the app so nothing writes to the DB
docker compose stop digiserver-app

# 2. Verify the backup is intact
( cd backups/$TS && sha256sum -c SHA256SUMS )

# 3. Decompress and swap in the database
gunzip -c backups/$TS/dashboard.db.gz > data/instance/dashboard.db

# 4. Fix ownership (container runs as uid 1000)
sudo chown 1000:1000 data/instance/dashboard.db
chmod 644 data/instance/dashboard.db

# 5. Remove any stale journal file
rm -f data/instance/dashboard.db-journal

# 6. Rebuild/start the upgraded image
docker compose up -d --build

# 7. Re-run migrations (new schema may have been added)
docker compose exec -T digiserver-app python /app/migrations/add_https_config_table.py
docker compose exec -T digiserver-app python /app/migrations/add_player_user_table.py
docker compose exec -T digiserver-app python /app/migrations/add_email_to_https_config.py
docker compose exec -T digiserver-app python /app/migrations/migrate_player_user_global.py

# 8. Verify
docker compose exec -T digiserver-app python -c "
import sqlite3
c=sqlite3.connect('/app/instance/dashboard.db')
print('players:  ', c.execute('SELECT COUNT(*) FROM player').fetchone()[0])
print('playlists:', c.execute('SELECT COUNT(*) FROM playlist').fetchone()[0])
print('items:    ', c.execute('SELECT COUNT(*) FROM playlist_content').fetchone()[0])
"
\`\`\`

## Important notes

- The container's **entrypoint only creates a DB if one is missing** - dropping in this
  file bypasses re-initialisation, which is what we want.
- Ownership **must be uid/gid 1000** (host \`pi:pi\` == container \`appuser\`).
- If the new app version changed the schema, the migration step (7) is mandatory.
EOF

echo
echo "============================================================"
echo " Backup complete and restore-ready: $DEST"
echo " To restore later:  ./restore_database.sh $TS"
echo "============================================================"