#!/usr/bin/env bash
#
# Pre-deployment check: prove every migration + seeder runs on a REAL MySQL 8 server
# (production target is cPanel MySQL 8). Tests run on SQLite for speed; this catches the
# divergences SQLite hides -- enum/JSON columns, composite indexes, and the conditional
# foreign keys that are skipped on SQLite (users.contact_id / users.employee_id, ...).
#
# Usage:  bash scripts/verify-mysql.sh
# Requires: mysql@8.4 (brew install mysql@8.4) and PHP 8.3 on PATH.
#
set -euo pipefail

# --- locate binaries (Homebrew keg-only mysql@8.4) ---
export PATH="/opt/homebrew/opt/mysql@8.4/bin:/opt/homebrew/opt/php@8.3/bin:/opt/homebrew/bin:${PATH}"

DB_NAME="${RUWAD_MYSQL_DB:-ruwad_test}"
DB_USER="${RUWAD_MYSQL_USER:-root}"
DB_PASS="${RUWAD_MYSQL_PASSWORD:-}"
DB_HOST="${RUWAD_MYSQL_HOST:-127.0.0.1}"
DB_PORT="${RUWAD_MYSQL_PORT:-3306}"

cd "$(dirname "$0")/.."

echo "==> Ensuring MySQL 8 is running"
if ! mysqladmin -u "${DB_USER}" ${DB_PASS:+-p"${DB_PASS}"} ping >/dev/null 2>&1; then
  mysql.server start >/dev/null 2>&1 || /opt/homebrew/opt/mysql@8.4/bin/mysql.server start >/dev/null 2>&1 || true
  for _ in $(seq 1 20); do
    mysqladmin -u "${DB_USER}" ${DB_PASS:+-p"${DB_PASS}"} ping >/dev/null 2>&1 && break
    sleep 1
  done
fi

SERVER_VERSION="$(mysql -u "${DB_USER}" ${DB_PASS:+-p"${DB_PASS}"} -N -e 'SELECT VERSION();')"
echo "    MySQL ${SERVER_VERSION}"
case "${SERVER_VERSION}" in
  8.*) ;;
  *) echo "    WARNING: expected MySQL 8.x (cPanel prod target), found ${SERVER_VERSION}." ;;
esac

echo "==> (Re)creating database ${DB_NAME}"
mysql -u "${DB_USER}" ${DB_PASS:+-p"${DB_PASS}"} -e \
  "DROP DATABASE IF EXISTS ${DB_NAME}; CREATE DATABASE ${DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

echo "==> Running migrate:fresh --seed on MySQL"
DB_CONNECTION=mysql DB_HOST="${DB_HOST}" DB_PORT="${DB_PORT}" \
DB_DATABASE="${DB_NAME}" DB_USERNAME="${DB_USER}" DB_PASSWORD="${DB_PASS}" \
  php artisan migrate:fresh --seed --no-interaction

echo "==> Asserting conditional foreign keys exist on users"
FK_COUNT="$(mysql -u "${DB_USER}" ${DB_PASS:+-p"${DB_PASS}"} -N "${DB_NAME}" -e \
  "SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE \
   WHERE TABLE_SCHEMA='${DB_NAME}' AND TABLE_NAME='users' AND REFERENCED_TABLE_NAME IS NOT NULL;")"
if [ "${FK_COUNT}" -lt 2 ]; then
  echo "    FAIL: conditional FKs users.contact_id/employee_id are missing." >&2
  exit 1
fi

echo "OK: MySQL 8 verification passed (${FK_COUNT} conditional FKs on users, migrations + seeders green)."