#!/usr/bin/env bash
# =============================================================================
# apply-migrations.sh — apply ABOS incremental DB changes (schema + procedures
# + views) to a target database, in dependency order.
#
# Safe to re-run: every script guards its own objects (CREATE ... IF NOT EXISTS,
# information_schema-guarded ALTERs, DROP PROCEDURE IF EXISTS + CREATE, CREATE OR
# REPLACE VIEW). This rewrites the legacy hardcoded `db_abos_v0.1` name to the
# target DB at apply time, and takes a gzipped backup first.
#
# 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='stacie_Aggie_v1.0' DB_USER='stacie_aggie' DB_PASS='...' bash apply-migrations.sh
#
# Env overrides:  DB_NAME (default stacie_Aggie_v1.0), DB_USER (default root),
#                 DB_PASS (optional; if empty, relies on socket/root auth).
# =============================================================================
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)"
BACKUP_DIR="${BACKUP_DIR:-${HOME}/aggie-db-backups}"

# mysql/mysqldump auth (use MYSQL_PWD to avoid ps exposure + the insecure warning)
AUTH=(-u "${DB_USER}")
[[ -n "${DB_PASS}" ]] && export MYSQL_PWD="${DB_PASS}"

# Ordered: tables/columns first, then procedures/views that depend on them.
# Intentionally excludes production_deploy_all.sql (a consolidated duplicate of
# the five fix_*/app_settings scripts below) and seed_dim_agent_configs.example.sql
# (example seed data, not for production).
FILES=(
  app_settings.sql
  dim_physical_assets.sql
  fact_leads.sql
  fact_meetings.sql
  fact_appointments.sql
  fact_campaigns.sql
  fact_asset_allocations.sql
  fact_vat.sql
  add_financial_entity_contact_fields.sql
  add_business_extended_fields.sql
  add_lead_contact_link.sql
  add_lead_service_link_and_description.sql
  fix_all_business_id_columns.sql
  fix_finance_business_id_columns.sql
  fix_user_module_access.sql
  fix_dim_expense_categories.sql
  fix_sp_bulk_create_tenant_users.sql
  fix_finance_hrm_platform_procedures.sql
  finance_views.sql
  bank_account_branch_fields_and_banks.sql
  user_business_access.sql
  # sp_process_ai_bank_statement.sql is intentionally NOT run — it uses MySQL 8's
  # JSON_VALUE(... RETURNING type) syntax, which is a hard parse error on MariaDB
  # (the actual production server). fix_sp_process_ai_bank_statement.sql below is
  # a complete, self-sufficient replacement (identical signature/logic, CAST()
  # instead of RETURNING) — running it alone is sufficient on both MySQL and MariaDB.
  fix_sp_process_ai_bank_statement.sql
  attendance_sp_views.sql
  payroll_sp_views.sql
  digital_assets_sp_views.sql
  loans_sp_views.sql
  orders_sp_views.sql
  products_sp_views.sql
  purchases_sp_views.sql
  suppliers_sp_views.sql
  orphan_domains_sp_views.sql
  fix_opportunity_stage.sql
  fix_vw_user_module_access_detail.sql
  projects_foundation_refactor.sql
  projects_hierarchy_and_ai.sql
  fact_audit_logs.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 sed "s/db_abos_v0\.1/\`${DB_NAME}\`/g" "${path}" | mysql "${AUTH[@]}" "${DB_NAME}"; 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))