#!/usr/bin/env bash
# =============================================================================
# apply-consolidated-structure.sh — applies production_structure_consolidated.sql
# (the entire stacie_Aggie_v1.0 structure, including LarCare's web/CMS data —
# LarCare has no separate database) to Server 1 in one pass.
#
# Safe to re-run: every section keeps its own idempotent guards. Takes a
# gzipped backup first.
#
# Usage (as the app DB user, e.g. from CI):
#     DB_NAME='stacie_Aggie_v1.0' DB_USER='stacie_aggie' DB_PASS='...' bash apply-consolidated-structure.sh
#
# Usage (as root on the server, socket auth):
#     sudo bash apply-consolidated-structure.sh
# =============================================================================
set -uo pipefail

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

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

if [[ ! -f "${SQL_FILE}" ]]; then
  echo "!! Missing ${SQL_FILE}"; exit 1
fi

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 -------------------------------------------------------------------
echo ">> Applying ${SQL_FILE}"
if mysql "${AUTH[@]}" "${DB_NAME}" < "${SQL_FILE}"; then
  echo "==================== SUMMARY ===================="
  echo "Applied OK."
  echo "Backup    : ${BACKUP}"
  echo "==================================================="
else
  echo "!! FAILED applying ${SQL_FILE} — backup is at ${BACKUP}"
  exit 1
fi