#!/bin/bash
set -euo pipefail

if [[ $# -ne 3 ]]; then
    echo "Usage: $0 <database_name> <admin_username> <admin_password>" >&2
    exit 1
fi

DB_NAME="$1"
DB_USER="$2"
DB_PASSWORD="$3"
DB_HOST="user2804.openweb.direct"
DB_PORT="5432"
SSL_MODE="verify-full"
SSL_ROOT_CERT="/etc/ssl/certs/ca-certificates.crt"

identifier_regex='^[A-Za-z_][A-Za-z0-9_]*$'

if [[ ! "$DB_NAME" =~ $identifier_regex ]]; then
    echo "Invalid database name. Use letters, numbers, and underscores, and start with a letter or underscore." >&2
    exit 1
fi

if [[ ! "$DB_USER" =~ $identifier_regex ]]; then
    echo "Invalid admin username. Use letters, numbers, and underscores, and start with a letter or underscore." >&2
    exit 1
fi

if runuser -u postgres -- psql -tA -X postgres -c "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME'" | grep -qx '1'; then
    echo "Database '$DB_NAME' already exists." >&2
    exit 1
fi

if runuser -u postgres -- psql -tA -X postgres -c "SELECT 1 FROM pg_roles WHERE rolname = '$DB_USER'" | grep -qx '1'; then
    echo "Role '$DB_USER' already exists." >&2
    exit 1
fi

runuser -u postgres -- psql -v ON_ERROR_STOP=1 -X \
    -v db_name="$DB_NAME" \
    -v db_user="$DB_USER" \
    -v db_password="$DB_PASSWORD" \
    postgres <<'SQL'

SELECT format(
    'CREATE ROLE %I LOGIN PASSWORD %L NOSUPERUSER NOCREATEROLE NOCREATEDB NOREPLICATION',
    :'db_user',
    :'db_password'
) \gexec

SELECT format(
    'CREATE DATABASE %I OWNER %I TEMPLATE template1',
    :'db_name',
    :'db_user'
) \gexec

SELECT format('REVOKE ALL ON DATABASE %I FROM PUBLIC', :'db_name') \gexec
SELECT format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', :'db_name', :'db_user') \gexec
SQL

runuser -u postgres -- psql -v ON_ERROR_STOP=1 -X \
    -v db_user="$DB_USER" \
    "$DB_NAME" <<'SQL'
REVOKE ALL ON SCHEMA public FROM PUBLIC;
SELECT format('GRANT USAGE, CREATE ON SCHEMA public TO %I', :'db_user') \gexec
SELECT format('ALTER SCHEMA public OWNER TO %I', :'db_user') \gexec
SQL

cat <<EOF
Client database created successfully.

Host: $DB_HOST
Port: $DB_PORT
Database: $DB_NAME
Admin username: $DB_USER
SSL mode: $SSL_MODE
CA bundle for libpq/psql on Linux: $SSL_ROOT_CERT

psql example:
PGPASSWORD='<password>' psql "host=$DB_HOST port=$DB_PORT dbname=$DB_NAME user=$DB_USER sslmode=$SSL_MODE sslrootcert=$SSL_ROOT_CERT"
EOF