#!/usr/bin/env bash
set -euo pipefail
[[ "${RUN_ISOLATED_DB_TEST:-}" == "1" ]] || { printf '%s\n' 'Set RUN_ISOLATED_DB_TEST=1 to run a disposable local database test.'; exit 1; }
project_root="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
migration_name=20260909210000_reimbursement_draft_expense_type
container_id="$(docker run -d --rm --network none --tmpfs /var/lib/postgresql/data -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16-alpine)"
trap 'docker rm -f "$container_id" >/dev/null' EXIT
ready=false
for attempt in $(seq 1 30); do
  if docker exec "$container_id" pg_isready -U postgres >/dev/null 2>&1; then ready=true; break; fi
  sleep 1
done
[[ "$ready" == true ]] || { printf '%s\n' 'Disposable database did not become ready.'; exit 1; }
run_sql() { docker exec -i "$container_id" psql -X -q -v ON_ERROR_STOP=1 -U postgres; }
for migration in "$project_root"/server/prisma/migrations/*/migration.sql; do
  [[ "$migration" == *"/$migration_name/"* ]] && continue
  run_sql < "$migration" >/dev/null
done
run_sql <<'SQL'
INSERT INTO departments (id, name) VALUES ('10000000-0000-4000-8000-000000000001', 'synthetic-department');
INSERT INTO employees (id, employee_no, name, email, department_id, title, hire_date, bank_account_encrypted, bank_name, password_hash)
VALUES ('10000000-0000-4000-8000-000000000002', 'synthetic-migration', 'synthetic', 'migration@example.invalid', '10000000-0000-4000-8000-000000000001', 'synthetic', '2026-09-09', 'not-real-bank-data', 'synthetic', 'not-a-login-hash');
INSERT INTO expense_types (id, code, name, category, sort_order)
VALUES ('10000000-0000-4000-8000-000000000003', 'synthetic_migration', 'synthetic', 'office', 999);
INSERT INTO reimbursement_forms (id, owner_id, submitter_id, department_id, reimbursement_date)
VALUES ('10000000-0000-4000-8000-000000000004', '10000000-0000-4000-8000-000000000002', '10000000-0000-4000-8000-000000000002', '10000000-0000-4000-8000-000000000001', '2026-09-09');
INSERT INTO reimbursement_items (id, reimbursement_id, expense_type_id, expense_date, amount, description)
VALUES ('10000000-0000-4000-8000-000000000005', '10000000-0000-4000-8000-000000000004', '10000000-0000-4000-8000-000000000003', '2026-09-09', 100, 'synthetic');
DO $$ BEGIN
  BEGIN
    UPDATE reimbursement_items SET expense_type_id = NULL;
    RAISE EXCEPTION 'baseline unexpectedly accepts null classification';
  EXCEPTION WHEN not_null_violation THEN NULL;
  END;
END $$;
SQL
run_sql < "$project_root/server/prisma/migrations/$migration_name/migration.sql"
run_sql <<'SQL'
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM reimbursement_items WHERE expense_type_id = '10000000-0000-4000-8000-000000000003' AND amount = 100) THEN
    RAISE EXCEPTION 'existing item changed';
  END IF;
END $$;
INSERT INTO reimbursement_items (id, reimbursement_id, expense_type_id, expense_date, amount, description)
VALUES ('10000000-0000-4000-8000-000000000006', '10000000-0000-4000-8000-000000000004', NULL, '2026-09-09', 50, 'synthetic unclassified');
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM reimbursement_items WHERE id = '10000000-0000-4000-8000-000000000006' AND expense_type_id IS NULL) THEN
    RAISE EXCEPTION 'unclassified item not persisted';
  END IF;
  BEGIN
    UPDATE reimbursement_items SET expense_type_id = '10000000-0000-4000-8000-000000000099';
    RAISE EXCEPTION 'foreign key no longer enforced';
  EXCEPTION WHEN foreign_key_violation THEN NULL;
  END;
  BEGIN
    UPDATE reimbursement_items SET amount = NULL;
    RAISE EXCEPTION 'amount became nullable';
  EXCEPTION WHEN not_null_violation THEN NULL;
  END;
  BEGIN
    UPDATE reimbursement_items SET expense_date = NULL;
    RAISE EXCEPTION 'date became nullable';
  EXCEPTION WHEN not_null_violation THEN NULL;
  END;
  BEGIN
    ALTER TABLE reimbursement_items ALTER COLUMN expense_type_id SET NOT NULL;
    RAISE EXCEPTION 'unsafe rollback unexpectedly succeeded';
  EXCEPTION WHEN not_null_violation THEN NULL;
  END;
END $$;
UPDATE reimbursement_items SET expense_type_id = '10000000-0000-4000-8000-000000000003' WHERE expense_type_id IS NULL;
ALTER TABLE reimbursement_items ALTER COLUMN expense_type_id SET NOT NULL;
SQL
printf '%s\n' 'PASS: baseline, persisted null classification, unchanged existing data, FK, required amount/date, guarded rollback.'