#!/usr/bin/env bash
set -euo pipefail

root=$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)
validator=$root/scripts/ci-validate-migrations.mjs
fixture_root=$(mktemp -d)
trap 'rm -rf "$fixture_root"' EXIT

new_repo() {
  local name=$1
  local repo=$fixture_root/$name

  mkdir -p "$repo/apps/api/migrations"
  git -C "$repo" init -q
  git -C "$repo" config user.name 'CI Migration Test'
  git -C "$repo" config user.email 'ci-migration-test@example.invalid'
  printf 'CREATE TABLE accounts (id bigint PRIMARY KEY);\n' > \
    "$repo/apps/api/migrations/0001_init.sql"
  git -C "$repo" add apps/api/migrations/0001_init.sql
  git -C "$repo" commit -qm baseline
  mkdir -p "$repo/deploy/ci"
  git -C "$repo" rev-parse HEAD > \
    "$repo/deploy/ci/production-migration-base"
  printf '%s\n' "$repo"
}

commit_all() {
  local repo=$1
  local message=$2
  git -C "$repo" add -A
  git -C "$repo" commit -qm "$message"
}

expect_pass() {
  local repo=$1
  local base=$2
  local immutable_base=${3:-}
  (
    cd "$repo"
    if [[ -n $immutable_base ]]; then
      node "$validator" "$base" "$immutable_base"
    else
      node "$validator" "$base"
    fi
  )
}

expect_fail() {
  local repo=$1
  local base=$2
  local expected=$3
  local immutable_base=${4:-}
  local output status

  if [[ -n $immutable_base ]]; then
    set +e
    output=$(cd "$repo" && node "$validator" "$base" "$immutable_base" 2>&1)
    status=$?
    set -e
  else
    set +e
    output=$(cd "$repo" && node "$validator" "$base" 2>&1)
    status=$?
    set -e
  fi
  if [[ $status -eq 0 ]]; then
    printf 'migration validator unexpectedly passed: %s\n' "$expected" >&2
    exit 1
  fi
  grep -Fq "$expected" <<<"$output" || {
    printf 'missing expected migration failure %q in:\n%s\n' "$expected" "$output" >&2
    exit 1
  }
}

repo=$(new_repo safe)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_expand_accounts.sql" <<'SQL'
-- A comment mentioning DROP TABLE must not trigger the gate.
ALTER TABLE accounts ADD COLUMN display_name text;
CREATE TABLE audit_events (id bigint PRIMARY KEY, note text);
INSERT INTO audit_events (id, note) VALUES (1, 'DROP TABLE is documentation');
UPDATE accounts SET display_name = 'ready' WHERE display_name IS NULL;
SQL
commit_all "$repo" safe
expect_pass "$repo" "$base"

repo=$(new_repo drop)
base=$(git -C "$repo" rev-parse HEAD)
printf 'DROP\nTABLE accounts;\n' >"$repo/apps/api/migrations/0002_drop.sql"
commit_all "$repo" drop
expect_fail "$repo" "$base" 'destructive DROP operation'

repo=$(new_repo delete)
base=$(git -C "$repo" rev-parse HEAD)
printf 'DELETE FROM accounts;\n' >"$repo/apps/api/migrations/0002_delete.sql"
commit_all "$repo" delete
expect_fail "$repo" "$base" 'DELETE FROM operation'

repo=$(new_repo incompatible_alter)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_alter.sql" <<'SQL'
ALTER TABLE accounts ADD COLUMN external_id text NOT NULL;
ALTER TABLE accounts ALTER COLUMN id TYPE numeric;
SQL
commit_all "$repo" alter
expect_fail "$repo" "$base" 'non-null column addition'
expect_fail "$repo" "$base" 'column type change'

repo=$(new_repo dynamic_sql)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_dynamic.sql" <<'SQL'
DO $$
BEGIN
  EXECUTE 'DROP ' || 'TABLE accounts';
END
$$;
SQL
commit_all "$repo" dynamic
expect_fail "$repo" "$base" 'dynamic SQL execution'

repo=$(new_repo escape_string)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_escape.sql" <<'SQL'
SELECT E'foo\'bar'; DROP TABLE accounts; --';
SQL
commit_all "$repo" escape
expect_fail "$repo" "$base" 'PostgreSQL escape/unicode strings are not allowed'

repo=$(new_repo string_semantics_override)
base=$(git -C "$repo" rev-parse HEAD)
printf 'SET standard_conforming_strings = off;\n' > \
  "$repo/apps/api/migrations/0002_string_mode.sql"
commit_all "$repo" string_mode
expect_fail "$repo" "$base" 'SQL string semantics override'

repo=$(new_repo transaction_control)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_commit.sql" <<'SQL'
ALTER TABLE accounts ADD COLUMN display_name text;
COMMIT;
UPDATE accounts SET display_name = 'partially committed';
SQL
commit_all "$repo" transaction_control
expect_fail "$repo" "$base" 'transaction control operation'

repo=$(new_repo bare_end_transaction)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_end.sql" <<'SQL'
ALTER TABLE accounts ADD COLUMN display_name text;
END;
UPDATE accounts SET display_name = 'partially committed';
SQL
commit_all "$repo" bare_end_transaction
expect_fail "$repo" "$base" 'transaction control operation'

repo=$(new_repo plpgsql_block_end)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_function.sql" <<'SQL'
CREATE FUNCTION account_count() RETURNS bigint
LANGUAGE plpgsql AS $$
BEGIN
  RETURN (SELECT count(*) FROM accounts);
END;
$$;
SQL
commit_all "$repo" plpgsql_block_end
expect_fail "$repo" "$base" 'procedural SQL body'

repo=$(new_repo ordinary_string_procedural_body)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_do_string.sql" <<'SQL'
DO LANGUAGE plpgsql 'BEGIN DROP TABLE accounts; END';
SQL
commit_all "$repo" ordinary_string_procedural_body
expect_fail "$repo" "$base" 'procedural SQL body'

repo=$(new_repo ordinary_string_boundary)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_string_boundary.sql" <<'SQL'
SELECT '\'; DROP TABLE accounts; --';
SQL
commit_all "$repo" ordinary_string
expect_fail "$repo" "$base" 'destructive DROP operation'

repo=$(new_repo unicode_string)
base=$(git -C "$repo" rev-parse HEAD)
printf "SELECT U&'d\\0061ta';\n" >"$repo/apps/api/migrations/0002_unicode.sql"
commit_all "$repo" unicode
expect_fail "$repo" "$base" 'PostgreSQL escape/unicode strings are not allowed'

repo=$(new_repo merge_delete)
base=$(git -C "$repo" rev-parse HEAD)
cat >"$repo/apps/api/migrations/0002_merge.sql" <<'SQL'
MERGE INTO accounts USING stale_accounts ON accounts.id = stale_accounts.id
WHEN MATCHED THEN DELETE;
SQL
commit_all "$repo" merge
expect_fail "$repo" "$base" 'MERGE operation'

repo=$(new_repo replace_view)
base=$(git -C "$repo" rev-parse HEAD)
printf 'CREATE OR REPLACE VIEW active_accounts AS SELECT id FROM accounts;\n' > \
  "$repo/apps/api/migrations/0002_replace.sql"
commit_all "$repo" replace
expect_fail "$repo" "$base" 'CREATE OR REPLACE operation'

repo=$(new_repo rename_function)
base=$(git -C "$repo" rev-parse HEAD)
printf 'ALTER FUNCTION calculate_total() RENAME TO calculate_total_legacy;\n' > \
  "$repo/apps/api/migrations/0002_rename.sql"
commit_all "$repo" rename
expect_fail "$repo" "$base" 'object or column rename'

repo=$(new_repo set_schema)
base=$(git -C "$repo" rev-parse HEAD)
printf 'ALTER TABLE accounts SET SCHEMA archive;\n' > \
  "$repo/apps/api/migrations/0002_schema.sql"
commit_all "$repo" schema
expect_fail "$repo" "$base" 'SET SCHEMA operation'

repo=$(new_repo drop_domain)
base=$(git -C "$repo" rev-parse HEAD)
printf 'DROP DOMAIN account_code CASCADE;\n' > \
  "$repo/apps/api/migrations/0002_drop_domain.sql"
commit_all "$repo" drop_domain
expect_fail "$repo" "$base" 'destructive DROP operation'

repo=$(new_repo immutable)
base=$(git -C "$repo" rev-parse HEAD)
printf '\n-- retroactive edit\n' >>"$repo/apps/api/migrations/0001_init.sql"
commit_all "$repo" edit
expect_fail "$repo" "$base" 'applied migrations are immutable (status M)'

repo=$(new_repo immutable_after_baseline)
base=$(git -C "$repo" rev-parse HEAD)
printf 'ALTER TABLE accounts ADD COLUMN nickname text;\n' > \
  "$repo/apps/api/migrations/0002_nickname.sql"
commit_all "$repo" add
immutable_base=$(git -C "$repo" rev-parse HEAD)
printf '\nUPDATE accounts SET nickname = NULL;\n' >> \
  "$repo/apps/api/migrations/0002_nickname.sql"
commit_all "$repo" modify
output=$(
  cd "$repo"
  node "$validator" "$base" "$immutable_base" 2>&1 || true
)
grep -Fq 'migration present on main is immutable (status M)' <<<"$output" || {
  printf 'stale production baseline did not preserve main migration immutability:\n%s\n' \
    "$output" >&2
  exit 1
}

repo=$(new_repo filename)
base=$(git -C "$repo" rev-parse HEAD)
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/next.SQL"
commit_all "$repo" filename
expect_fail "$repo" "$base" 'new migration must use NNNN_lowercase_name.sql'

repo=$(new_repo ordering)
base=$(git -C "$repo" rev-parse HEAD)
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/0000_late.sql"
commit_all "$repo" ordering
expect_fail "$repo" "$base" 'new migration name must sort after 0001_init.sql'

repo=$(new_repo stale_baseline)
base=$(git -C "$repo" rev-parse HEAD)
printf 'ALTER TABLE accounts ADD COLUMN nickname text;\n' > \
  "$repo/apps/api/migrations/0002_nickname.sql"
commit_all "$repo" main_migration
immutable_base=$(git -C "$repo" rev-parse HEAD)
printf 'ordinary code change\n' >"$repo/README.md"
commit_all "$repo" code_only
expect_pass "$repo" "$base" "$immutable_base"

repo=$(new_repo baseline_advance)
production_base=$(git -C "$repo" rev-parse HEAD)
mkdir -p "$repo/deploy/ci"
printf '%s\n' "$production_base" >"$repo/deploy/ci/production-migration-base"
commit_all "$repo" baseline_file
immutable_base=$(git -C "$repo" rev-parse HEAD)
git -C "$repo" switch -q -c release-baseline-update "$immutable_base"
printf '%s\n' "$immutable_base" >"$repo/deploy/ci/production-migration-base"
commit_all "$repo" valid_baseline_advance
expect_pass "$repo" "$immutable_base" "$immutable_base"

git -C "$repo" switch -q -c malicious-baseline "$immutable_base"
printf 'DROP TABLE accounts;\n' >"$repo/apps/api/migrations/0002_drop.sql"
commit_all "$repo" hidden_destructive_migration
hidden_sha=$(git -C "$repo" rev-parse HEAD)
printf '%s\n' "$hidden_sha" >"$repo/deploy/ci/production-migration-base"
commit_all "$repo" hide_migration_in_baseline
expect_fail "$repo" "$hidden_sha" \
  'production migration baseline must belong to the immutable event base history' \
  "$immutable_base"

repo=$(new_repo nested)
base=$(git -C "$repo" rev-parse HEAD)
mkdir -p "$repo/apps/api/migrations/archive"
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/archive/9999_hidden.sql"
commit_all "$repo" nested
expect_fail "$repo" "$base" 'new migration must use NNNN_lowercase_name.sql'

repo=$(new_repo symlink)
base=$(git -C "$repo" rev-parse HEAD)
printf 'SELECT 1;\n' >"$repo/outside.sql"
ln -s ../../../outside.sql "$repo/apps/api/migrations/0002_symlink.sql"
commit_all "$repo" symlink
expect_fail "$repo" "$base" 'new migration must be a mode 100644 regular file'

repo=$(new_repo baseline_symlink)
base=$(git -C "$repo" rev-parse HEAD)
printf '%s\n' "$base" >"$repo/baseline-target"
rm "$repo/deploy/ci/production-migration-base"
ln -s ../../baseline-target "$repo/deploy/ci/production-migration-base"
commit_all "$repo" baseline_symlink
expect_fail "$repo" "$base" \
  'production migration baseline must be a mode 100644 regular file'

repo=$(new_repo ancestry)
base=$(git -C "$repo" rev-parse HEAD)
git -C "$repo" switch -q --orphan unrelated
printf 'unrelated\n' >"$repo/unrelated"
git -C "$repo" add unrelated
git -C "$repo" commit -qm unrelated
expect_fail "$repo" "$base" 'production migration baseline is not an ancestor of HEAD'

repo=$(new_repo invalid_sha)
expect_fail "$repo" deadbeef 'must be a full lowercase commit SHA'

echo 'migration_tests=PASS'