reset_db

#!/bin/bash

set -eu -o pipefail

ssh_user_host=postgres@localhost
db_user=machine_manager
db_name=machine_manager
backup_file=~/.config/machine_manager/backup.sql

make_backup() {
	# Make the backup folder if it doesn't already exist
	mkdir -p "$(dirname "$backup_file")"
	# Rotate existing backups
	mv "$backup_file.2" "$backup_file.3" 2> /dev/null || true
	mv "$backup_file.1" "$backup_file.2" 2> /dev/null || true
	mv "$backup_file"   "$backup_file.1" 2> /dev/null || true

	# For the backup, create a table with a subset of the machines table, containing
	# just the columns with data that can't be probed.  This way, after a big schema
	# change, we still typically will not need to edit backup.sql for the restore.
	create_backup_subset="\
	DROP TABLE IF EXISTS machines__backup;
	CREATE TABLE         machines__backup AS SELECT
	                         hostname, type, wireguard_ip, wireguard_port, wireguard_privkey,
	                         wireguard_pubkey, wireguard_expose, ssh_port, ssh_user,
	                         ssh_expose, country, release, boot, added_time
	                     FROM machines;

	DROP TABLE IF EXISTS machine_tags__backup;
	CREATE TABLE         machine_tags__backup AS SELECT * FROM machine_tags;

	DROP TABLE IF EXISTS networks__backup;
	CREATE TABLE         networks__backup AS SELECT * FROM networks;

	DROP TABLE IF EXISTS machine_addresses__backup;
	CREATE TABLE         machine_addresses__backup AS SELECT * FROM machine_addresses;

	DROP TABLE IF EXISTS machine_forwards__backup;
	CREATE TABLE         machine_forwards__backup AS SELECT * FROM machine_forwards;
	"
	echo "$create_backup_subset" | ssh "$ssh_user_host" "psql -v ON_ERROR_STOP=1 -d $db_name"

	tables=(machines machine_tags networks machine_addresses machine_forwards)
	pg_dump_table_args=$(for table in ${tables[@]}; do echo -n " --table" "${table}__backup"; done)

	# Create the backup file
	ssh "$ssh_user_host" "pg_dump --data-only $pg_dump_table_args machine_manager" > "$backup_file"
	# Fix the backup to restore into the correct tables
	for table in ${tables[@]}; do
		sed -i -r "s,^COPY public\.${table}__backup ,COPY public.$table ," "$backup_file"
	done
}

if [[ "${1:-}" != "--use-existing-backup" ]]; then
	make_backup
fi

# Drop database, create database, import schema and backup
reset_db="\
DROP DATABASE IF EXISTS          $db_name;
CREATE DATABASE                  $db_name;
GRANT ALL PRIVILEGES ON DATABASE $db_name TO $db_user;
ALTER DATABASE                   $db_name SET bytea_output TO 'escape';
"
echo "$reset_db" | ssh "$ssh_user_host" "psql -v ON_ERROR_STOP=1"
cat schema.sql   | ssh "$ssh_user_host" "psql -v ON_ERROR_STOP=1 -d $db_name"
# pg_dump dumps tables in an order that violates foreign key constraints when
# restored, so use `set session_replication_role = replica;` to disable all
# triggers (including FK constraints).
(
	echo "set session_replication_role = replica;" &&
	cat "$backup_file"
) | ssh "$ssh_user_host" "psql -v ON_ERROR_STOP=1 -d $db_name"

echo "Backup restored.  You should probe all machines now."