#!/usr/bin/env bash
#
# Dumps the development ClickHouse schema for use in chdb-powered test suites.
#
# Tests use chdb (in-process ClickHouse) instead of a running server.
# This script connects to the dev ClickHouse, runs SHOW CREATE TABLE for
# each user table, and writes a sanitized SQL file that chdb can execute.
#
# Sanitization:
#   - Strips database-qualified names (e.g. latitude_development.spans → spans)
#   - Removes TTL, storage_policy, and ttl_only_drop_parts clauses
#
# Run after applying new ClickHouse migrations to keep the test schema in sync:
#   pnpm --filter @platform/db-clickhouse ch:schema:dump
#

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PKG_DIR="$(cd "$SCRIPT_DIR/../.." && pwd)"
ROOT_DIR="$(cd "$PKG_DIR/../../.." && pwd)"
NODE_ENV="${NODE_ENV:-development}"

if [ "$NODE_ENV" != "development" ]; then
  echo "Error: ch:schema:dump must run against the development database (NODE_ENV=development)."
  echo "This script dumps the dev schema for use in chdb-powered test suites."
  exit 1
fi

ENV_FILE="$ROOT_DIR/.env.${NODE_ENV}"

if [ -f "$ENV_FILE" ]; then
  set -a
  # shellcheck disable=SC1090
  source "$ENV_FILE"
  set +a
fi

CH_URL="${CLICKHOUSE_URL:-http://localhost:8123}"
CH_USER="${CLICKHOUSE_USER:-latitude}"
CH_PASS="${CLICKHOUSE_PASSWORD:-secret}"
CH_DB="${CLICKHOUSE_DB:-latitude_development}"
OUTPUT="$ROOT_DIR/packages/platform/testkit/src/clickhouse/schema.sql"

tables=$(curl -s "${CH_URL}/?user=${CH_USER}&password=${CH_PASS}&database=${CH_DB}" \
  --data-binary "SHOW TABLES FORMAT TabSeparated")

if [ -z "$tables" ]; then
  echo "No tables found in ${CH_DB}"
  exit 1
fi

sanitize() {
  sed \
    -e "s/${CH_DB}\.//g" \
    -e '/^TTL /d' \
    -e "s/, ttl_only_drop_parts = [0-9]*//g" \
    -e "s/, storage_policy = '[^']*'//g" \
    -e "s/SETTINGS ttl_only_drop_parts = [0-9]*, storage_policy = '[^']*', /SETTINGS /g" \
    -e "s/SETTINGS ttl_only_drop_parts = [0-9]*, /SETTINGS /g"
}

{
  echo "-- Auto-generated by ch:schema:dump — do not edit manually."
  echo "-- Regenerate after applying migrations: pnpm --filter @platform/db-clickhouse ch:schema:dump"
  echo ""

  while IFS= read -r table; do
    [ -z "$table" ] && continue

    # Skip goose internal table
    [ "$table" = "goose_db_version" ] && continue

    create_stmt=$(curl -s "${CH_URL}/?user=${CH_USER}&password=${CH_PASS}&database=${CH_DB}" \
      --data-binary "SHOW CREATE TABLE ${table} FORMAT TabSeparatedRaw")

    echo "$create_stmt;" | sanitize
    echo ""
  done <<< "$tables"
} > "$OUTPUT"

echo "Schema dumped to ${OUTPUT}"
