#!/usr/bin/env bash
#
# First-install database bootstrap for the Nquiry stack. Opens an SSM tunnel
# to the bastion, applies the schema-shim from scripts/ci-bootstrap-db.sql,
# then runs all pending app migrations via `npm run db:migrate`.
#
# Why this exists: NQU-821. The baseline migration (supabase/migrations/
# 20240120000000_baseline.sql) references auth.uid() and auth.users — helpers
# that aren't present on a vanilla RDS PostgreSQL instance. The dev RDS was
# bootstrapped long ago and migrations have been ratcheting forward against
# the live schema since. A truly fresh customer RDS hits the gap. This script
# applies the shim first, then the migration runner picks up cleanly.
#
# Companion to apply.sh, bootstrap-secrets.sh, and deploy-image.sh. Full
# customer-install order:
#
#   ../../apply.sh                # provisions infrastructure
#   ../../bootstrap-secrets.sh    # populates Secrets Manager
#   ../../bootstrap-db.sh         # this script — bootstraps DB schema + migrates
#   ../../deploy-image.sh         # deploys the application image
#
# Usage:
#   cd infrastructure/terraform/environments/<your-env>
#   ../../bootstrap-db.sh
#
# Or from anywhere with a path:
#   /path/to/infrastructure/terraform/bootstrap-db.sh /path/to/your/env-dir
#
# Idempotent. ci-bootstrap-db.sql uses CREATE IF NOT EXISTS / CREATE OR REPLACE
# throughout; npm run db:migrate skips migrations already in _migrations.

set -euo pipefail

bold=$'\033[1m'
red=$'\033[31m'
dim=$'\033[2m'
reset=$'\033[0m'

target_dir="${1:-$PWD}"

# Resolve script + repo root (this file lives at <repo>/infrastructure/terraform/).
script_dir="$(cd "$(dirname "$0")" && pwd)"
repo_root="$(cd "$script_dir/../.." && pwd)"

ssm_pid=""
cleanup() {
  if [[ -n "$ssm_pid" ]]; then
    kill "$ssm_pid" 2>/dev/null || true
    wait "$ssm_pid" 2>/dev/null || true
  fi
}
abort() {
  cleanup
  echo
  echo "${red}Aborted.${reset}"
  exit 130
}
trap cleanup EXIT
trap abort INT

require_cmd() {
  if ! command -v "$1" >/dev/null 2>&1; then
    echo "${red}$1 not found on PATH.${reset} $2" >&2
    exit 1
  fi
}

require_cmd terraform "Install Terraform >= 1.0 and retry."
require_cmd aws       "Install the AWS CLI v2 and retry."
require_cmd jq        "Install jq and retry."
require_cmd npm       "Install Node.js + npm and retry."

# psql may live under Homebrew's libpq formula (not symlinked by default).
if command -v psql >/dev/null 2>&1; then
  PSQL=psql
elif [[ -x /opt/homebrew/opt/libpq/bin/psql ]]; then
  PSQL=/opt/homebrew/opt/libpq/bin/psql
elif [[ -x /usr/local/opt/libpq/bin/psql ]]; then
  PSQL=/usr/local/opt/libpq/bin/psql
else
  echo "${red}psql not found.${reset} Install postgresql or libpq (brew install libpq) and retry." >&2
  exit 1
fi

if [[ ! -d "$target_dir" ]]; then
  echo "${red}Not a directory:${reset} $target_dir" >&2
  exit 1
fi
if [[ ! -f "$target_dir/main.tf" ]]; then
  echo "${red}No main.tf in $target_dir.${reset} Run this from a Terraform environment directory." >&2
  exit 1
fi
if [[ ! -f "$target_dir/terraform.tfvars" ]]; then
  echo "${red}No terraform.tfvars in $target_dir.${reset} Required for db_password lookup." >&2
  exit 1
fi
if [[ ! -f "$repo_root/scripts/ci-bootstrap-db.sql" ]]; then
  echo "${red}Missing $repo_root/scripts/ci-bootstrap-db.sql.${reset} Re-clone or restore the file." >&2
  exit 1
fi

cd "$target_dir"

read_tfvar() {
  # Extract a string value from terraform.tfvars. Naive but sufficient for the
  # simple `key = "value"` lines in customer tfvars files. Matches the same
  # pattern as bootstrap-secrets.sh.
  local key="$1"
  grep -E "^${key}[[:space:]]*=" terraform.tfvars \
    | head -1 \
    | sed -E "s/^${key}[[:space:]]*=[[:space:]]*\"(.+)\".*/\1/"
}

echo "${bold}==> Discovering RDS + bastion${reset}"

cluster=$(terraform output -raw ecs_cluster_name 2>/dev/null || true)
if [[ -z "$cluster" ]]; then
  echo "${red}terraform output ecs_cluster_name not available.${reset} Run apply.sh first." >&2
  exit 1
fi
name_prefix="${cluster%-cluster}"
region="${AWS_REGION:-$(terraform output -raw aws_region 2>/dev/null || echo us-east-1)}"

# RDS endpoint: prefer terraform output if exposed; fall back to AWS lookup.
rds_host=$(terraform output -raw database_endpoint 2>/dev/null \
            | sed -E 's/:[0-9]+$//' \
            || true)
if [[ -z "$rds_host" ]]; then
  rds_host=$(aws rds describe-db-instances --region "$region" \
              --query "DBInstances[?contains(DBInstanceIdentifier,\`${name_prefix}\`)].Endpoint.Address" \
              --output text)
fi
if [[ -z "$rds_host" || "$rds_host" == "None" ]]; then
  echo "${red}Could not resolve RDS endpoint for ${name_prefix}.${reset}" >&2
  exit 1
fi

bastion_id=$(aws ec2 describe-instances --region "$region" \
              --filters "Name=tag:Name,Values=${name_prefix}-bastion" \
                        "Name=instance-state-name,Values=running" \
              --query 'Reservations[0].Instances[0].InstanceId' --output text)
if [[ -z "$bastion_id" || "$bastion_id" == "None" ]]; then
  echo "${red}Could not find a running bastion tagged Name=${name_prefix}-bastion.${reset}" >&2
  exit 1
fi

db_password=$(read_tfvar db_password)
if [[ -z "$db_password" ]]; then
  echo "${red}db_password not found in terraform.tfvars.${reset}" >&2
  exit 1
fi

db_name="${DB_NAME:-investigation_app}"
db_user="${DB_USER:-app_admin}"
local_port="${BOOTSTRAP_LOCAL_PORT:-5433}"

echo "  RDS:     $rds_host"
echo "  Bastion: $bastion_id"
echo "  Tunnel:  localhost:$local_port → $rds_host:5432"

echo
echo "${bold}==> Opening SSM port-forwarding tunnel${reset}"

aws ssm start-session --target "$bastion_id" --region "$region" \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters "{\"host\":[\"$rds_host\"],\"portNumber\":[\"5432\"],\"localPortNumber\":[\"$local_port\"]}" \
  > /tmp/bootstrap-db-tunnel.log 2>&1 &
ssm_pid=$!

# Poll the local port until the tunnel accepts a TCP connect or we hit 60s.
ready=false
for _ in {1..30}; do
  if PGPASSWORD="$db_password" "$PSQL" \
      "sslmode=require host=localhost port=$local_port dbname=$db_name user=$db_user connect_timeout=2" \
      -c "SELECT 1" >/dev/null 2>&1; then
    ready=true
    break
  fi
  sleep 2
done
if [[ "$ready" != "true" ]]; then
  echo "${red}Tunnel did not become ready within 60s. See /tmp/bootstrap-db-tunnel.log${reset}" >&2
  exit 1
fi
echo "  ${dim}Tunnel ready.${reset}"

echo
echo "${bold}==> Applying schema shim (ci-bootstrap-db.sql)${reset}"

# ON_ERROR_STOP=0 because the file ends with `GRANT ... TO ci_user` which only
# exists in the CI service container — customer RDS uses app_admin. Those
# trailing GRANTs error harmlessly; every preceding statement is idempotent.
PGPASSWORD="$db_password" "$PSQL" \
  "sslmode=require host=localhost port=$local_port dbname=$db_name user=$db_user" \
  -v ON_ERROR_STOP=0 \
  -f "$repo_root/scripts/ci-bootstrap-db.sql" \
  > /tmp/bootstrap-db-shim.log 2>&1
errors_other_than_ci_user=$(grep -E '^psql:' /tmp/bootstrap-db-shim.log | grep -vc 'ci_user does not exist' || true)
if [[ "$errors_other_than_ci_user" -gt 0 ]]; then
  echo "${red}Schema shim errored:${reset}" >&2
  grep -E '^psql:' /tmp/bootstrap-db-shim.log | grep -v 'ci_user does not exist' >&2
  exit 1
fi
echo "  ${dim}Shim applied (extensions + auth/storage schemas + helpers).${reset}"

echo
echo "${bold}==> Running app migrations${reset}"

cd "$repo_root"
DB_HOST=localhost \
DB_PORT="$local_port" \
DB_TUNNEL=true \
DB_NAME="$db_name" \
DB_USER="$db_user" \
DB_PASSWORD="$db_password" \
DB_SSL=true \
  npm run db:migrate

echo
echo "${bold}Bootstrap complete.${reset} Schema + migrations are live on $rds_host."
echo "Next: ${dim}cd $target_dir && ../../deploy-image.sh${reset}"