#!/usr/bin/env bash
# =============================================================================
# apply-migrations.sh — apply LarCare's schema + views + procedures to a
# target database, in dependency order.
#
# Safe to re-run: schema.sql uses CREATE TABLE IF NOT EXISTS, views.sql uses
# CREATE OR REPLACE VIEW, procedures.sql uses DROP PROCEDURE IF EXISTS +
# CREATE. Takes a gzipped backup first. Deliberately excludes sql/seed.sql —
# that's one-time initial data, not an idempotent migration; run it by hand
# only when seeding a brand-new database.
#
# Usage (as root on the server, socket auth):
#     sudo bash apply-migrations.sh
#
# Usage (as the app DB user, e.g. from CI):
#     DB_NAME='larcare' DB_USER='larcare_migrate' DB_PASS='...' bash apply-migrations.sh
#
# Env overrides: DB_NAME (default larcare), DB_USER (default root),
#                DB_PASS (optional; if empty, relies on socket/root auth).
# =============================================================================
set -uo pipefail

DB_NAME="${DB_NAME:-larcare}"
DB_USER="${DB_USER:-root}"
DB_PASS="${DB_PASS:-}"
DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)/sql"
BACKUP_DIR="${BACKUP_DIR:-${HOME}/larcare-db-backups}"

AUTH=(-u "${DB_USER}")
[[ -n "${DB_PASS}" ]] && export MYSQL_PWD="${DB_PASS}"

# Ordered: tables first, then views/procedures that depend on them.
FILES=(
  schema.sql
  views.sql
  procedures.sql
)

echo ">> Target database : ${DB_NAME}"
echo ">> Connecting as   : ${DB_USER}"
mysql "${AUTH[@]}" -e "USE \`${DB_NAME}\`;" 2>/dev/null \
  || { echo "!! Cannot connect to DB '${DB_NAME}' as '${DB_USER}'"; exit 1; }

# --- Backup first ---------------------------------------------------------
mkdir -p "${BACKUP_DIR}"
TS="$(date +%Y%m%d-%H%M%S)"
BACKUP="${BACKUP_DIR}/${DB_NAME}-${TS}.sql.gz"
echo ">> Backing up -> ${BACKUP}"
if mysqldump "${AUTH[@]}" --single-transaction --routines --triggers --no-tablespaces "${DB_NAME}" | gzip > "${BACKUP}"; then
  echo "   backup OK ($(du -h "${BACKUP}" | cut -f1))"
else
  echo "!! Backup failed — aborting before any changes."; rm -f "${BACKUP}"; exit 1
fi

# --- Apply ----------------------------------------------------------------
ok=(); failed=()
for f in "${FILES[@]}"; do
  path="${DIR}/${f}"
  if [[ ! -f "${path}" ]]; then echo "-- skip (missing): ${f}"; continue; fi
  echo ">> Applying ${f}"
  if mysql "${AUTH[@]}" "${DB_NAME}" < "${path}"; then
    ok+=("${f}")
  else
    echo "   !! FAILED: ${f}"
    failed+=("${f}")
  fi
done

echo ""
echo "==================== SUMMARY ===================="
echo "Applied OK : ${#ok[@]}/${#FILES[@]}"
echo "Failed     : ${#failed[@]}"
((${#failed[@]})) && printf '   - %s\n' "${failed[@]}"
echo "Backup     : ${BACKUP}"
echo "================================================="
((${#failed[@]}==0))