#!/usr/bin/env bash
# Two-machine schema workflow: dev laptop <-> production server (each DB is
# only reachable as "localhost" from its own machine, so this script has two
# modes instead of diffing across the network directly.
#
# On your DEV MACHINE, after changing the schema locally:
#   db/bin/schema-diff.sh pull
#
# On the PRODUCTION SERVER (over SSH), after deploying the updated
# db/skeema/stackscom/*.sql files that "pull" produced:
#   db/bin/schema-diff.sh diff "short_description"
set -euo pipefail

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
SKEEMA_DIR="$ROOT_DIR/db/skeema"
MIGRATIONS_DIR="$ROOT_DIR/db/migrations"

# Skeema doesn't read SKEEMA_PASSWORD itself; it falls back to -p's default,
# which is the MYSQL_PWD env var. Bridge the two so `source db/env` is enough.
if [ -n "${SKEEMA_PASSWORD:-}" ]; then
  export MYSQL_PWD="$SKEEMA_PASSWORD"
fi

MODE="${1:-}"

case "$MODE" in
  pull)
    echo "==> Refreshing development schema snapshot..."
    (cd "$SKEEMA_DIR" && skeema pull development)
    echo "==> Done. Commit/deploy the updated files under db/skeema/ to production."
    ;;

  diff)
    DESC="${2:-schema_update}"

    echo "==> Diffing deployed schema files against production (read-only, applies nothing)..."
    DIFF_OUTPUT="$(cd "$SKEEMA_DIR" && skeema diff production || true)"

    if [ -z "$DIFF_OUTPUT" ]; then
      echo "No differences found between deployed schema files and production. Nothing to migrate."
      exit 0
    fi

    echo "$DIFF_OUTPUT"

    LAST_VERSION=$(find "$MIGRATIONS_DIR" -maxdepth 1 -name 'V*__*.sql' 2>/dev/null \
      | sed -E 's#.*/V([0-9]+)__.*#\1#' \
      | sort -n | tail -1)
    NEXT_VERSION=$(( ${LAST_VERSION:-0} + 1 ))

    DRAFT_FILE="$MIGRATIONS_DIR/V${NEXT_VERSION}__${DESC}.sql.review"
    {
      echo "-- REVIEW BEFORE USE"
      echo "-- Generated from: skeema diff production"
      echo "-- Statements for tables/columns that already exist in production must be removed."
      echo "-- Once reviewed, rename this file to remove the .review suffix so Flyway picks it up."
      echo "$DIFF_OUTPUT"
    } > "$DRAFT_FILE"

    echo "==> Draft migration written to: $DRAFT_FILE"
    echo "==> Review it, then run: mv \"$DRAFT_FILE\" \"${DRAFT_FILE%.review}\""
    echo "==> After that, test with: flyway -configFiles=db/flyway.conf info"
    echo "==> Then apply with:       flyway -configFiles=db/flyway.conf migrate"
    ;;

  *)
    echo "Usage:"
    echo "  db/bin/schema-diff.sh pull                    # run on dev machine"
    echo "  db/bin/schema-diff.sh diff \"description\"       # run on production server via SSH"
    exit 1
    ;;
esac