#!/usr/bin/env bash
# Replaces the database and files on this VM with a Lovable Cloud export,
# or with one of this VM's own backups:
#   ~/bin/app-restore EXPORT [FILES]
# EXPORT is the database export from Lovable (a .backup file, or the .zip
# around it) or a backup from ~/backups. FILES is a directory holding, for
# each storage bucket, a BUCKET.zip as Lovable's Storage view downloads it,
# or a BUCKET directory; ~/backups/files is one. Default: ~/lovable.
#
# It starts Supabase over with an empty database, loads the whole export
# into a scratch database, restore_source, and copies across: extensions,
# accounts (users, their Google/Apple/Microsoft identities, two-factor
# settings), the public schema with its data, policies, and permissions,
# the app's triggers and policies on Supabase's own tables, the list of
# migrations applied, and storage buckets and files, through Supabase's
# Storage API. restore_source stays, for reference, until the next restore.
# From https://exe.dev/deploy/migrate-lovable
set -euo pipefail
export=$(realpath "${1:?usage: app-restore EXPORT [FILES]}")
files=$(realpath -m "${2:-$HOME/lovable}")
work=$HOME/.restore
supa() { grep "^$1=" "$HOME/supabase/.env" | cut -d= -f2-; }
sql() { docker exec -i -e PGOPTIONS=-cclient_min_messages=warning supabase-db psql -X -q -U supabase_admin -v ON_ERROR_STOP=1 "$@" >/dev/null; }  # SQL on stdin
query() { docker exec -e PGOPTIONS=-cclient_min_messages=warning supabase-db psql -X -q -U supabase_admin -v ON_ERROR_STOP=1 -tA "$@"; }
rm -rf "$work" && mkdir -p "$work"
if [ "${export##*.}" = zip ]; then
  unzip -q "$export" -d "$work/zip"
  export=$(find "$work/zip" -type f -name '*.backup' | head -1)
  [ -n "$export" ] || { echo "No .backup file in $1"; exit 1; }
fi
pg_restore -l "$export" > "$work/toc.list"  # stops here if it isn't a database export
if [ "${YES:-}" != 1 ]; then
  read -r -p "This replaces all data in this VM's database and storage. Type yes: " ok
  [ "$ok" = yes ] || exit 1
fi
exec 9>"$HOME/.deploy.lock" && flock 9  # no deploy while this runs

echo "==> Starting over with an empty database and storage"
sudo systemctl stop app
cd "$HOME/supabase"
sh run.sh stop >/dev/null 2>&1
sudo rm -rf volumes/db/data volumes/storage/stub
sh run.sh start >/dev/null 2>&1

echo "==> Loading the whole export into a scratch database, restore_source"
query -c "drop database if exists restore_source" -c "create database restore_source"
pg_restore -f - --no-owner --no-acl "$export" 2>/dev/null |
  docker exec -i supabase-db psql -X -q -U supabase_admin -d restore_source > "$work/scratch.log" 2>&1 || true

echo "==> Extensions"
grep -E '^[0-9]+; [0-9]+ [0-9]+ EXTENSION ' "$work/toc.list" > "$work/extensions.list" || true
# One this Supabase doesn't have is reported, not fatal: the tables that
# need it will say so below.
pg_restore -f - -L "$work/extensions.list" "$export" |
  docker exec -i supabase-db psql -X -q -U supabase_admin 2>&1 >/dev/null | sed 's/^/    /'

echo "==> Accounts: users, their sign-in methods, and two-factor settings"
# Row by row from the scratch database, in the columns both sides have, so
# a newer or older Supabase Auth on either side doesn't matter.
columns() {  # columns DATABASE SCHEMA.TABLE
  query -d "$1" -c "select attname from pg_attribute where attrelid = to_regclass('$2')
    and attnum > 0 and not attisdropped and attgenerated = '' order by attnum"
}
copy() {  # copy SCHEMA.TABLE, if the export has it
  local here there cols
  here=$(columns postgres "$1")
  there=$(columns restore_source "$1")
  [ -n "$there" ] || return 0
  cols=$(grep -Fx -f <(echo "$there") <<<"$here" | sed 's/.*/"&"/' | paste -sd, || true)
  [ -n "$cols" ] || return 0
  docker exec supabase-db sh -c "psql -X -q -U supabase_admin -d restore_source -c '\\copy (select $cols from $1) to stdout' |
    psql -X -q -U supabase_admin -d postgres -v ON_ERROR_STOP=1 -c '\\copy $1 ($cols) from pstdin'"
  echo "    $1: $(query -c "select count(*) from $1") rows"
}
for t in auth.users auth.identities auth.mfa_factors auth.webauthn_credentials; do copy "$t"; done

echo "==> Your tables and their data, functions, policies, and permissions"
pg_restore -l -n public "$export" > "$work/public.list"
pg_restore -f - -L "$work/public.list" "$export" | sql --single-transaction

echo "==> Your triggers and policies on Supabase's own tables"
query -c "select 'TRIGGER ' || event_object_schema || ' ' || event_object_table || ' ' || trigger_name || ' '
  from information_schema.triggers union select 'POLICY ' || schemaname || ' ' || tablename || ' ' || policyname || ' '
  from pg_policies" > "$work/existing.txt"
pg_restore -l -n auth -n storage "$export" | grep -E '^[0-9]+; [0-9]+ [0-9]+ (TRIGGER|POLICY) ' |
  grep -vFf "$work/existing.txt" > "$work/hooks.list" || true
cut -d' ' -f4- "$work/hooks.list" | sed 's/ [a-z_]*$//; s/^/    /'  # what, schema, table, name
pg_restore -f - -L "$work/hooks.list" "$export" | sql --single-transaction

echo "==> Migrations it has"
query -c "create schema if not exists supabase_migrations;
  create table if not exists supabase_migrations.schema_migrations (version text primary key, statements text[], name text);
  alter schema supabase_migrations owner to postgres; alter table supabase_migrations.schema_migrations owner to postgres"
copy supabase_migrations.schema_migrations
# The export comes from a database that has every migration in the
# repository, though it may not list them.
for f in "$HOME"/app/supabase/migrations/*.sql; do
  [ -e "$f" ] || break
  name=$(basename "$f" .sql)
  echo "insert into supabase_migrations.schema_migrations (version, name) values ('${name%%_*}', '${name#*_}') on conflict do nothing;"
done | sql

echo "==> Storage buckets and files"
api=http://localhost:8001/storage/v1
key=$(supa SUPABASE_SECRET_KEY)
query -d restore_source -c "select row_to_json(b) from (select id, name, public, file_size_limit, allowed_mime_types
  from storage.buckets order by id) b" > "$work/buckets.json"
query -d restore_source -F $'\t' -c "select bucket_id, name, coalesce(version, ''),
  coalesce(metadata->>'mimetype', 'application/octet-stream') from storage.objects order by 1, 2" > "$work/objects.tsv"
while read -r bucket; do
  b=$(jq -r .id <<<"$bucket")
  curl -fsS "$api/bucket" -H "apikey: $key" -H 'content-type: application/json' -d "$bucket" >/dev/null
  dir=$files/$b
  if [ -f "$files/$b.zip" ]; then
    dir=$work/files/$b && mkdir -p "$dir" && unzip -q "$files/$b.zip" -d "$dir"
  fi
  n=0 missing=0
  while IFS=$'\t' read -r ob name version type; do
    [ "$ob" = "$b" ] || continue
    f=$dir/$name
    [ -d "$f" ] && f=$f/$version  # in a backup, each version of a file is in a folder with its name
    [ -f "$f" ] || f=$(find "$dir" -type f -path "*/$name" 2>/dev/null | head -1)  # a zip with a folder around it
    if [ -z "$f" ] || [ ! -f "$f" ]; then
      missing=$((missing + 1)) && echo "$b/$name" >> "$work/missing.txt" && continue
    fi
    curl -fsS "$api/object/$b/$(jq -rn --arg p "$name" '$p | @uri | gsub("%2F"; "/")')" -H "apikey: $key" \
      -H "content-type: $type" -H 'x-upsert: true' --data-binary "@$f" >/dev/null
    n=$((n + 1))
  done < "$work/objects.tsv"
  echo -n "    $b ($(jq -r 'if .public then "public" else "private" end' <<<"$bucket")): $n file$([ $n = 1 ] || echo s)"
  [ "$missing" = 0 ] && echo || echo ", $missing not found"
done < "$work/buckets.json"
# The files go back to the users who uploaded them, for policies that check.
docker exec supabase-db sh -c "psql -X -q -U supabase_admin -d restore_source -c '\\copy (select bucket_id, name, owner, owner_id, user_metadata, created_at from storage.objects) to stdout' |
  psql -X -q -U supabase_admin -d postgres -v ON_ERROR_STOP=1 -c 'create table storage.restore_owners (bucket_id text, name text, owner uuid, owner_id text, user_metadata jsonb, created_at timestamptz)' \
    -c '\\copy storage.restore_owners from pstdin' \
    -c 'update storage.objects o set owner_id = r.owner_id, owner = r.owner, user_metadata = r.user_metadata, created_at = r.created_at from storage.restore_owners r where o.bucket_id = r.bucket_id and o.name = r.name' \
    -c 'drop table storage.restore_owners'"

echo "==> Rows here and in the export"
for t in auth.users auth.identities storage.objects $(query -c "select 'public.' || quote_ident(tablename) from pg_tables where schemaname = 'public' order by 1"); do
  echo "$t $(query -c "select count(*) from $t") $(query -d restore_source -c "select count(*) from $t" 2>/dev/null || echo -)"
done | column -t -N TABLE,HERE,EXPORT | sed 's/^/    /'

pg_restore -f - -a -n cron -t job "$export" 2>/dev/null | awk -F'\t' '
  /^COPY cron\.job / { split(substr($0, index($0, "(") + 1), c, /[,)] ?/); for (i in c) col[c[i]] = i; on = 1; next }
  /^\\\.$/ { on = 0 }
  on { print "    " $col["jobname"] " (" $col["schedule"] "): " substr($col["command"], 1, 100) }' > "$work/jobs.txt"
if [ -s "$work/jobs.txt" ]; then
  echo "==> Scheduled jobs in the export, not set up here (see the recipe):"
  cat "$work/jobs.txt"
fi
others=$(query -d restore_source -c "select string_agg(nspname, ', ' order by nspname) from pg_namespace
  where nspname not like 'pg\_%' and nspname not in ('information_schema', 'public', 'auth', 'storage',
  'realtime', '_realtime', 'extensions', 'graphql', 'graphql_public', 'net', 'pgbouncer', 'vault', 'cron',
  'supabase_functions', 'supabase_migrations', 'pgsodium', 'pgsodium_masks', '_analytics', '_supavisor')")
[ -z "$others" ] || echo "==> Not restored, but in restore_source: the schemas $others"
sudo systemctl start app
rm -rf "$work/zip" "$work/files"
[ ! -s "$work/missing.txt" ] || echo "==> Files not found in $files: listed in $work/missing.txt"
echo "==> Done. Everyone signs in again; their passwords are the same."