item replacement and relationships db update

1 files changed, 108 insertions, 0 deletions

diff --git a/apply-updates.sh b/apply-updates.sh
new file mode 100755
index 0000000..ae13767
--- /dev/null
+++ b/apply-updates.sh
@@ -0,0 +1,108 @@
+#!/usr/bin/env bash
+
+set -euo pipefail
+
+# BeepZone Database Updater
+# Applies SQL update scripts from backend/database/dev/ to the configured database.
+
+SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
+WORK_DIR="${SCRIPT_DIR}"
+
+# Check for MariaDB/MySQL client - try common brew locations on macOS
+MYSQL_CLIENT=""
+if command -v mariadb >/dev/null 2>&1; then
+  MYSQL_CLIENT="mariadb"
+elif command -v mysql >/dev/null 2>&1; then
+  MYSQL_CLIENT="mysql"
+elif [[ -x /opt/homebrew/opt/mysql-client/bin/mysql ]]; then
+  MYSQL_CLIENT="/opt/homebrew/opt/mysql-client/bin/mysql"
+  export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"
+elif [[ -x /opt/homebrew/opt/mariadb/bin/mariadb ]]; then
+  MYSQL_CLIENT="/opt/homebrew/opt/mariadb/bin/mariadb"
+  export PATH="/opt/homebrew/opt/mariadb/bin:$PATH"
+elif [[ -x /opt/homebrew/bin/mariadb ]]; then
+  MYSQL_CLIENT="/opt/homebrew/bin/mariadb"
+  export PATH="/opt/homebrew/bin:$PATH"
+elif [[ -x /opt/homebrew/bin/mysql ]]; then
+  MYSQL_CLIENT="/opt/homebrew/bin/mysql"
+  export PATH="/opt/homebrew/bin:$PATH"
+elif [[ -x /usr/local/opt/mysql-client/bin/mysql ]]; then
+  MYSQL_CLIENT="/usr/local/opt/mysql-client/bin/mysql"
+  export PATH="/usr/local/opt/mysql-client/bin:$PATH"
+elif [[ -x /usr/local/bin/mariadb ]]; then
+  MYSQL_CLIENT="/usr/local/bin/mariadb"
+  export PATH="/usr/local/bin:$PATH"
+elif [[ -x /usr/local/bin/mysql ]]; then
+  MYSQL_CLIENT="/usr/local/bin/mysql"
+  export PATH="/usr/local/bin:$PATH"
+else
+  echo "[ERROR] MariaDB/MySQL client is required but not found."
+  exit 1
+fi
+
+ENV_FILE="$SCRIPT_DIR/.env"
+
+# Load existing settings from .env if present
+if [[ -f "$ENV_FILE" ]]; then
+  source "$ENV_FILE"
+else
+  echo "[ERROR] .env file not found. Please run beepzone-helper.sh first to configure the environment."
+  exit 1
+fi
+
+# Set defaults if not loaded from .env (though they should be)
+: "${DB_HOST:=127.0.0.1}"
+: "${DB_PORT:=3306}"
+: "${DB_NAME:=beepzone}"
+: "${DB_USER:=beepzone}"
+: "${DB_PASS:=beepzone}"
+
+echo "=== BeepZone Database Updater ==="
+echo "Target Database: $DB_NAME on $DB_HOST:$DB_PORT"
+echo "User: $DB_USER"
+echo ""
+
+UPDATES_DIR="$WORK_DIR/backend/database/dev"
+
+if [[ ! -d "$UPDATES_DIR" ]]; then
+  echo "[ERROR] Updates directory not found: $UPDATES_DIR"
+  exit 1
+fi
+
+# Find update scripts
+update_scripts=($(find "$UPDATES_DIR" -name "update_*.sql" | sort))
+
+if [[ ${#update_scripts[@]} -eq 0 ]]; then
+  echo "No update scripts found in $UPDATES_DIR."
+  exit 0
+fi
+
+echo "Found ${#update_scripts[@]} update script(s):"
+for script in "${update_scripts[@]}"; do
+  echo " - $(basename "$script")"
+done
+echo ""
+
+read -p "Do you want to apply these updates? (y/N) " -n 1 -r
+echo ""
+if [[ ! $REPLY =~ ^[Yy]$ ]]; then
+  echo "Aborted."
+  exit 0
+fi
+
+echo ""
+for script in "${update_scripts[@]}"; do
+  script_name=$(basename "$script")
+  echo "Applying $script_name..."
+  
+  if "$MYSQL_CLIENT" -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$script"; then
+    echo " [OK] $script_name applied successfully."
+  else
+    echo " [ERROR] Failed to apply $script_name."
+    echo "Stopping further updates."
+    exit 1
+  fi
+done
+
+echo ""
+echo "All updates applied successfully."