#!/usr/bin/env bash
# Prints the SQL that creates the least-privilege MySQL accounts this app
# expects. Values are read from .env (or the file given as $1).
#
#   scripts/dev/create-db-accounts.sh | mysql -u root -p          # MySQL 8
#   scripts/dev/create-db-accounts.sh | mysql                      # Homebrew MariaDB (socket auth)
#
# DB_ACCOUNT_HOST sets the host part of the app accounts (default: localhost;
# use '%' or your app subnet for a remote DB server).
set -euo pipefail

ENV_FILE="${1:-.env}"
get() { grep -E "^$1=" "$ENV_FILE" | tail -1 | cut -d= -f2- | sed -e 's/^"//' -e 's/"$//'; }

HOST="${DB_ACCOUNT_HOST:-localhost}"
MASTER_DB="$(get DB_MASTER_DATABASE)"
WEB_USER="$(get DB_MASTER_USERNAME)"; WEB_PASS="$(get DB_MASTER_PASSWORD)"
MIG_USER="$(get DB_MIGRATOR_USERNAME)"; MIG_PASS="$(get DB_MIGRATOR_PASSWORD)"
PROV_USER="$(get DB_PROVISIONER_USERNAME)"; PROV_PASS="$(get DB_PROVISIONER_PASSWORD)"
PATTERN="$(get DB_TENANT_NAME_PATTERN)"
# social_tenant_%04d -> social_tenant_%
# Deliberately NOT escaping "_": MariaDB does not honour GRANT OPTION from an
# escaped wildcard grant (`social\_tenant\_%`) when the provisioner later runs
# GRANT ... ON `social_tenant_0001`.*. An unescaped "_" only widens the match
# to names shaped like social?tenant?..., which is acceptable.
TENANT_LIKE="${PATTERN%%%*}%"

for v in MASTER_DB WEB_USER WEB_PASS MIG_USER MIG_PASS PROV_USER PROV_PASS PATTERN; do
  if [ -z "${!v}" ]; then echo "Missing value for $v in $ENV_FILE" >&2; exit 1; fi
done

cat <<SQL
CREATE DATABASE IF NOT EXISTS \`${MASTER_DB}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Web/worker credential: data access on master only, no DDL.
CREATE USER IF NOT EXISTS '${WEB_USER}'@'${HOST}' IDENTIFIED BY '${WEB_PASS}';
ALTER USER '${WEB_USER}'@'${HOST}' IDENTIFIED BY '${WEB_PASS}';
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON \`${MASTER_DB}\`.* TO '${WEB_USER}'@'${HOST}';

-- Master migrations (deploy step only).
CREATE USER IF NOT EXISTS '${MIG_USER}'@'${HOST}' IDENTIFIED BY '${MIG_PASS}';
ALTER USER '${MIG_USER}'@'${HOST}' IDENTIFIED BY '${MIG_PASS}';
GRANT ALL PRIVILEGES ON \`${MASTER_DB}\`.* TO '${MIG_USER}'@'${HOST}';

-- Provisioning worker: create tenant schemas + their users, nothing else.
CREATE USER IF NOT EXISTS '${PROV_USER}'@'${HOST}' IDENTIFIED BY '${PROV_PASS}';
ALTER USER '${PROV_USER}'@'${HOST}' IDENTIFIED BY '${PROV_PASS}';
GRANT ALL PRIVILEGES ON \`${TENANT_LIKE}\`.* TO '${PROV_USER}'@'${HOST}' WITH GRANT OPTION;
GRANT CREATE USER ON *.* TO '${PROV_USER}'@'${HOST}';

FLUSH PRIVILEGES;
SQL