#!/usr/bin/env bash
# psql-json: run a SQL query against the demo database and return a JSON array.
# Stands in for tkpsql/tkdbr in the local dev environment.
#
# Usage: psql-json --sql "SELECT ..."
#
# Connection defaults match dev/docker-compose.yml; override with standard
# PG* environment variables (PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE).

set -euo pipefail

# On macOS, libpq is keg-only — psql won't be on PATH unless explicitly linked.
# Probe the two common Homebrew prefix locations and prepend if found.
for candidate in /opt/homebrew/opt/libpq/bin /usr/local/opt/libpq/bin; do
  if [[ -x "$candidate/psql" ]]; then
    export PATH="$candidate:$PATH"
    break
  fi
done

if ! command -v psql >/dev/null 2>&1; then
  echo '{"error": "psql not found. Install with: brew install libpq"}'
  exit 1
fi

PGHOST="${PGHOST:-localhost}"
PGPORT="${PGPORT:-5432}"
PGUSER="${PGUSER:-gate}"
PGPASSWORD="${PGPASSWORD:-gate}"
PGDATABASE="${PGDATABASE:-gatepay}"

SQL=""
while [[ $# -gt 0 ]]; do
  case "$1" in
    --sql)       SQL="$2"; shift 2 ;;
    --sql=*)     SQL="${1#--sql=}"; shift ;;
    # Accept standard psql connection flags so psql-json works as a drop-in
    # when the hook rewrites `psql -h ... -c "..."` → `psql-json -h ... --sql "..."`.
    -h)          PGHOST="$2"; shift 2 ;;
    -h*)         PGHOST="${1#-h}"; shift ;;
    -p)          PGPORT="$2"; shift 2 ;;
    -p*)         PGPORT="${1#-p}"; shift ;;
    -U)          PGUSER="$2"; shift 2 ;;
    -U*)         PGUSER="${1#-U}"; shift ;;
    -d)          PGDATABASE="$2"; shift 2 ;;
    -d*)         PGDATABASE="${1#-d}"; shift ;;
    -W|-w)       shift ;;  # password-prompt flags: ignore (we use PGPASSWORD)
    *)           echo "{\"error\": \"psql-json: unknown argument: $1\"}"; exit 1 ;;
  esac
done

if [[ -z "$SQL" ]]; then
  echo '{"error": "psql-json: --sql argument is required"}'
  exit 1
fi

# Strip trailing semicolon — valid for psql -c but breaks the subquery wrapper.
SQL="${SQL%;}"

# Wrap the caller's SQL in json_agg so the output is always a JSON array.
# coalesce handles the empty-result case.
WRAPPED="SELECT coalesce(json_agg(row_to_json(t)), '[]'::json) FROM ($SQL) t"

export PGPASSWORD
psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" \
  --tuples-only --no-align -c "$WRAPPED"
