#!/usr/bin/env sh
# test-search-db-schema.sh
#
# Schema contract between spine's --sqlite-update output and the search form
# in sisudoc-spine-search-cgi.
#
# Spine holds the only copy of the DDL; the search form holds no DDL at all,
# only a small set of column references. Nothing connects the two, so a
# column renamed in spine is found by whoever runs a search next. This is the
# check that finds it earlier: build a search database, then read the column
# references out of the search form's source and require every one of them to
# exist in what was built.
#
# It also requires the three-way agreement on schema version: the version
# spine declares, the version stamped into the database it built, and the
# version the search form expects.
#
# Usage:
#   ./test/test-search-db-schema.sh [/path/to/spine]
#
# Environment:
#   SpinePOD             pod directory to build the db from, relative to the
#                        spine directory (default: data/pod, the bundled set)
#   SpineSearchCGIsrc    path to the search cgi source file or its repository
#                        (default: ../sisudoc-spine-search-cgi)
#
# sqlite3 is needed. If it is not on PATH the script re-runs itself inside a
# nix shell that has it.
#
# Exit codes:
#   0  spine's database satisfies every expectation the search form has
#   1  a mismatch was found (printed)
#   2  set-up problem (no binary, no cgi source, no sqlite3, no samples)

set -e

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"

if ! command -v sqlite3 > /dev/null 2>&1; then
  if command -v nix > /dev/null 2>&1; then
    echo "sqlite3 not on PATH, re-running inside nix shell \"nixpkgs#sqlite\""
    exec nix shell "nixpkgs#sqlite" -c "$0" "$@"
  fi
  echo "ERROR: sqlite3 not on PATH and nix not available." >&2
  exit 2
fi

SPINE_BIN="${1:-}"
if [ -z "$SPINE_BIN" ]; then
  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
  else
    echo "ERROR: spine binary not found. Specify path as argument." >&2
    exit 2
  fi
fi

# the search form's source, the other half of the contract
CGI_SRC="${SpineSearchCGIsrc:-$SPINE_DIR/../sisudoc-spine-search-cgi}"
if [ -d "$CGI_SRC" ]; then
  CGI_SRC="$CGI_SRC/src/sisudoc/spine_search.d"
fi
if [ ! -f "$CGI_SRC" ]; then
  echo "ERROR: search cgi source not found at $CGI_SRC" >&2
  echo "  set \$SpineSearchCGIsrc to the sisudoc-spine-search-cgi checkout" >&2
  exit 2
fi

SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
if [ -d "$SAMPLES_RAW" ]; then
  SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
else
  echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
  exit 2
fi

OUT_DIR="$SCRIPT_DIR/current-search-db"
DB_DIR="$OUT_DIR/sqlite-database"
DB_NAME="spine.search.db"
DB="$DB_DIR/$DB_NAME"

echo "spine binary: $SPINE_BIN"
echo "search cgi:   $CGI_SRC"
echo "samples:      $SAMPLES_DIR"

rm -rf "$OUT_DIR"
mkdir -p "$DB_DIR"

echo "Building search database ..."
"$SPINE_BIN" --sqlite-db-recreate \
  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
# the update has to be able to fail the test: spine exits non-zero when any
# sqlite statement failed, and that is exactly the case this test is here for
if ! "$SPINE_BIN" --sqlite-update \
  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
  --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
  echo "FAIL: spine --sqlite-update reported failure"
  grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
  exit 1
fi

FAILURES=0
fail() { echo "  MISMATCH $*"; FAILURES=$((FAILURES + 1)); }

# ── the database was built at all
if [ ! -f "$DB" ]; then
  echo "FAIL: no database at $DB"
  exit 1
fi

# ── the tables the search form joins
echo "Checking tables ..."
for t in metadata_and_text doc_objects topic_register; do
  found=$(sqlite3 "$DB" \
    "select count(*) from sqlite_master where type='table' and name='$t';")
  [ "$found" = "1" ] || fail "table $t missing"
done

# ── the tables are populated, an empty db satisfies a column check trivially
docs=$(sqlite3 "$DB" "select count(*) from metadata_and_text;" 2>/dev/null || echo 0)
objs=$(sqlite3 "$DB" "select count(*) from doc_objects;" 2>/dev/null || echo 0)
echo "  documents: $docs, objects: $objs"
[ "$docs" -gt 0 ] || fail "metadata_and_text is empty"
[ "$objs" -gt 0 ] || fail "doc_objects is empty"

# ── schema version: spine's declaration, the stamp in the db, the search
#    form's expectation; all three have to agree
echo "Checking schema version ..."
SPINE_SQL_SRC="$SPINE_DIR/src/sisudoc/outputs/io_out/sqlite.d"
declared=$(sed -n 's/^enum sqlite_db_schema_version *= *\([0-9][0-9]*\);.*/\1/p' \
  "$SPINE_SQL_SRC" | head -1)
stamped=$(sqlite3 "$DB" "pragma user_version;")
expected=$(sed -n 's/^enum sqlite_db_schema_version_expected *= *\([0-9][0-9]*\);.*/\1/p' \
  "$CGI_SRC" | head -1)
echo "  spine declares: ${declared:-unset}, db stamped: ${stamped:-unset}, cgi expects: ${expected:-unset}"
if [ -z "$declared" ]; then
  fail "spine declares no sqlite_db_schema_version in $SPINE_SQL_SRC"
elif [ "$stamped" != "$declared" ]; then
  fail "db stamped user_version $stamped, spine declares $declared"
fi
if [ -z "$expected" ]; then
  fail "search form declares no sqlite_db_schema_version_expected"
elif [ -n "$declared" ] && [ "$expected" != "$declared" ]; then
  fail "search form expects schema version $expected, spine writes $declared"
fi

# ── every column the search form names has to exist
#    the references are qualified, table.column, which is what makes them
#    findable without parsing D
echo "Checking columns named by the search form ..."
COLS=$(grep -o \
  'metadata_and_text\.[a-z_]*\|doc_objects\.[a-z_]*\|topic_register\.[a-z_]*' \
  "$CGI_SRC" | sort -u)
if [ -z "$COLS" ]; then
  fail "no column references found in $CGI_SRC (has the source changed shape?)"
fi
count=0
for ref in $COLS; do
  table=${ref%%.*}
  column=${ref#*.}
  [ -n "$column" ] || continue
  count=$((count + 1))
  found=$(sqlite3 "$DB" \
    "select count(*) from pragma_table_info('$table') where name='$column';")
  [ "$found" = "1" ] || fail "$table.$column named by the search form, absent from the db"
done
echo "  column references checked: $count"

echo ""
if [ "$FAILURES" -eq 0 ]; then
  echo "PASS: spine's search database satisfies the search form's expectations"
  exit 0
else
  echo "FAIL: $FAILURES mismatch(es)"
  exit 1
fi