#!/usr/bin/env bash
# Create MySQL databases and app user for the tankfarm platform.
# Needs root MySQL (auth_socket via pkexec/sudo). Safe to re-run.
set -euo pipefail
ROOT="$(cd "$(dirname "$0")/.." && pwd)"
CONS_ENV="$ROOT/nmdpr_consumption/backend/.env"
SUPPLY_ENV="$ROOT/nmdpr_supply/backend/.env"

USER_NAME="${MYSQL_USER:-nmdpr}"
HOST="${MYSQL_HOST:-127.0.0.1}"
PORT="${MYSQL_PORT:-3306}"
CONS_DB="${MYSQL_CONSUMPTION_DB:-nmdpr_consumption}"
SUPPLY_DB="${MYSQL_SUPPLY_DB:-nmdpr_supply}"

existing=""
if [ -f "$CONS_ENV" ]; then
  existing=$(grep -E '^MYSQL_PASSWORD=' "$CONS_ENV" | tail -n1 | cut -d= -f2- || true)
fi
if [ -z "$existing" ] && [ -f "$SUPPLY_ENV" ]; then
  existing=$(grep -E '^MYSQL_PASSWORD=' "$SUPPLY_ENV" | tail -n1 | cut -d= -f2- || true)
fi
PASS="${MYSQL_PASSWORD:-$existing}"
if [ -z "$PASS" ]; then
  PASS="$(openssl rand -hex 16)"
fi

SQL=$(cat <<EOF
CREATE DATABASE IF NOT EXISTS \`${CONS_DB}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS \`${SUPPLY_DB}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS '${USER_NAME}'@'localhost' IDENTIFIED BY '${PASS}';
CREATE USER IF NOT EXISTS '${USER_NAME}'@'127.0.0.1' IDENTIFIED BY '${PASS}';
ALTER USER '${USER_NAME}'@'localhost' IDENTIFIED BY '${PASS}';
ALTER USER '${USER_NAME}'@'127.0.0.1' IDENTIFIED BY '${PASS}';
GRANT ALL PRIVILEGES ON \`${CONS_DB}\`.* TO '${USER_NAME}'@'localhost';
GRANT ALL PRIVILEGES ON \`${CONS_DB}\`.* TO '${USER_NAME}'@'127.0.0.1';
GRANT ALL PRIVILEGES ON \`${SUPPLY_DB}\`.* TO '${USER_NAME}'@'localhost';
GRANT ALL PRIVILEGES ON \`${SUPPLY_DB}\`.* TO '${USER_NAME}'@'127.0.0.1';
FLUSH PRIVILEGES;
EOF
)

run_mysql() {
  # debian-sys-maint uses auth_socket (Ubuntu MySQL packages). Root often has a password.
  if [ -r /etc/mysql/debian.cnf ] && mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT 1" >/dev/null 2>&1; then
    mysql --defaults-file=/etc/mysql/debian.cnf -e "$SQL"
    return 0
  fi
  if mysql --protocol=socket -u root -e "SELECT 1" >/dev/null 2>&1; then
    mysql --protocol=socket -u root -e "$SQL"
    return 0
  fi
  if command -v pkexec >/dev/null 2>&1; then
    if pkexec mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT 1" >/dev/null 2>&1; then
      pkexec mysql --defaults-file=/etc/mysql/debian.cnf -e "$SQL"
      return 0
    fi
    pkexec mysql --protocol=socket -u root -e "$SQL"
    return 0
  fi
  if sudo -n mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT 1" >/dev/null 2>&1; then
    sudo -n mysql --defaults-file=/etc/mysql/debian.cnf -e "$SQL"
    return 0
  fi
  if sudo -n mysql --protocol=socket -u root -e "SELECT 1" >/dev/null 2>&1; then
    sudo -n mysql --protocol=socket -u root -e "$SQL"
    return 0
  fi
  echo "Cannot authenticate to MySQL as root / debian-sys-maint."
  echo "Run this script with sudo, or create databases $CONS_DB and $SUPPLY_DB"
  echo "and user $USER_NAME yourself, then set MYSQL_PASSWORD in both .env files."
  return 1
}

run_mysql
echo "MySQL databases ready: $CONS_DB, $SUPPLY_DB (user $USER_NAME)"

python3 - "$CONS_ENV" "$SUPPLY_ENV" "$HOST" "$PORT" "$USER_NAME" "$PASS" "$CONS_DB" "$SUPPLY_DB" <<'PY'
import os, sys, urllib.parse
cons_env, supply_env, host, port, user, password, cons_db, supply_db = sys.argv[1:]
qpass = urllib.parse.quote_plus(password)

def upsert(path, kv):
    lines = []
    if os.path.exists(path):
        lines = open(path, encoding="utf-8").read().splitlines()
    keys = set(kv)
    out = []
    seen = set()
    for line in lines:
        if line.strip().startswith("#") or "=" not in line:
            out.append(line)
            continue
        k = line.split("=", 1)[0].strip()
        if k in keys:
            out.append(f"{k}={kv[k]}")
            seen.add(k)
        else:
            out.append(line)
    missing = [k for k in kv if k not in seen]
    if missing:
        out.append("")
        out.append("# MySQL")
        for k in missing:
            out.append(f"{k}={kv[k]}")
    os.makedirs(os.path.dirname(path), exist_ok=True)
    with open(path, "w", encoding="utf-8") as f:
        f.write("\n".join(out).rstrip() + "\n")

upsert(cons_env, {
    "MYSQL_HOST": host,
    "MYSQL_PORT": port,
    "MYSQL_USER": user,
    "MYSQL_PASSWORD": password,
    "MYSQL_DATABASE": cons_db,
})
upsert(supply_env, {
    "MYSQL_HOST": host,
    "MYSQL_PORT": port,
    "MYSQL_USER": user,
    "MYSQL_PASSWORD": password,
    "MYSQL_DATABASE": supply_db,
    "DATABASE_URL": f"mysql+pymysql://{user}:{qpass}@{host}:{port}/{supply_db}?charset=utf8mb4",
    "CONSUMPTION_DATABASE_URL": f"mysql+pymysql://{user}:{qpass}@{host}:{port}/{cons_db}?charset=utf8mb4",
})
print(f"Wrote MySQL settings to:\n  {cons_env}\n  {supply_env}")
PY