#!/usr/bin/env bash
# Faunamix: tüm database/*.sql migration zincirini api/.env (DB_*) ile uygular.
# Kullanım: proje kökünden ./scripts/run-database-migrations.sh
# Railway (veya başka DB) için yerel api/.env'i bozmadan (proje kökünden):
#   FAUNAMIX_ENV_FILE=api/.env.railway ./scripts/run-database-migrations.sh
# Şifreyi dosyaya yazmak istemezsen (tek seferlik, shell geçmişine düşebilir):
#   RAILWAY_DB_PASS='…' FAUNAMIX_ENV_FILE=api/.env.railway ./scripts/run-database-migrations.sh
#
# Railway: public TCP bazen 1045 verir; konteyner içi (mysql.railway.internal) güvenilir.
# Image’da /app/database + /app/scripts olmalı. Not: `railway run` komutu YEREL makinede çalışır (/app yok).
# Konteynerde api/.env genelde yok; Railway üretimde DB_* / MYSQL_* / DATABASE_URL platformdan gelir (açık bayrak şart değil).
# railway ssh + bash -c bazen ortamı boş verir; Linux’ta /proc/<pid>/environ taranır (api/lib/db.php ile aynı MYSQL_* alias).
# İsteğe bağlı açık bayrak: FAUNAMIX_MIGRATE_FROM_ENV=1 (ortam kesin boş / şüpheliyse)
#   railway ssh -- bash --noprofile --norc -c '/app/scripts/run-database-migrations.sh'
# Konteynerde tam yol kullanın; yalnız dosya adıyla çağrılırsa ROOT yanlış çıkıp api/.env hatası verebilir.
# Loglar görünmüyorsa PTY (script -c içinde bayrağı satır içi ver):
#   script -q -e -c 'FAUNAMIX_MIGRATE_FROM_ENV=1 exec /app/scripts/run-database-migrations.sh' /dev/null
# stdbuf bazı imajlarda "missing operand". export …; script -c /yol tek başına bayrağı düşürebilir — kullanma.
set -euo pipefail

# Bootstrap logunda erken çıkış teşhisi (çıktı yok + exit 1).
echo "==> Faunamix migration betiği başladı (pid $$)" >&2

# ROOT: betik dizininden (cwd’den değil). Göreli $0 veya yalnız dosya adı Railway’de api/.env yanlışlığını tetikler.
_SCRIPT_SOURCE="${BASH_SOURCE[0]:-$0}"
if [[ "$_SCRIPT_SOURCE" != /* ]]; then
  _SCRIPT_SOURCE="$PWD/${_SCRIPT_SOURCE#./}"
fi
_SCRIPT_DIR="$(cd "$(dirname "$_SCRIPT_SOURCE")" && pwd)"
ROOT="$(cd "$_SCRIPT_DIR/.." && pwd)"
ENV_FILE="${FAUNAMIX_ENV_FILE:-$ROOT/api/.env}"

# railway ssh alt sürecinde ortam boş kalabiliyor; çalışan süreçlerin environ’undan doldur (PID 1 bazen çıplak).
faunamix_merge_db_keys_from_proc_environ() {
  local procfile="$1"
  [[ -r "$procfile" ]] || return 0
  local _line _k _v
  while IFS= read -r -d '' _line; do
    _k="${_line%%=*}"
    _v="${_line#*=}"
    case "$_k" in
      DB_HOST) [[ -z "${DB_HOST:-}" ]] && DB_HOST="$_v" ;;
      DB_NAME) [[ -z "${DB_NAME:-}" ]] && DB_NAME="$_v" ;;
      DB_USER) [[ -z "${DB_USER:-}" ]] && DB_USER="$_v" ;;
      DB_PASS) [[ -z "${DB_PASS:-}" ]] && DB_PASS="$_v" ;;
      DB_PORT) [[ -z "${DB_PORT:-}" ]] && DB_PORT="$_v" ;;
      MYSQL_HOST) [[ -z "${MYSQL_HOST:-}" ]] && MYSQL_HOST="$_v" ;;
      MYSQL_DATABASE) [[ -z "${MYSQL_DATABASE:-}" ]] && MYSQL_DATABASE="$_v" ;;
      MYSQL_USER) [[ -z "${MYSQL_USER:-}" ]] && MYSQL_USER="$_v" ;;
      MYSQL_PASSWORD) [[ -z "${MYSQL_PASSWORD:-}" ]] && MYSQL_PASSWORD="$_v" ;;
      MYSQL_ROOT_PASSWORD) [[ -z "${MYSQL_ROOT_PASSWORD:-}" ]] && MYSQL_ROOT_PASSWORD="$_v" ;;
      MYSQL_PORT) [[ -z "${MYSQL_PORT:-}" ]] && MYSQL_PORT="$_v" ;;
      DATABASE_URL) [[ -z "${DATABASE_URL:-}" ]] && DATABASE_URL="$_v" ;;
      MYSQL_URL) [[ -z "${MYSQL_URL:-}" ]] && MYSQL_URL="$_v" ;;
      RAILWAY_ENVIRONMENT) [[ -z "${RAILWAY_ENVIRONMENT:-}" ]] && RAILWAY_ENVIRONMENT="$_v" ;;
      RAILWAY_ENVIRONMENT_NAME) [[ -z "${RAILWAY_ENVIRONMENT_NAME:-}" ]] && RAILWAY_ENVIRONMENT_NAME="$_v" ;;
      RAILWAY_PROJECT_ID) [[ -z "${RAILWAY_PROJECT_ID:-}" ]] && RAILWAY_PROJECT_ID="$_v" ;;
      RAILWAY_DEPLOYMENT_ID) [[ -z "${RAILWAY_DEPLOYMENT_ID:-}" ]] && RAILWAY_DEPLOYMENT_ID="$_v" ;;
      RAILWAY_SERVICE_ID) [[ -z "${RAILWAY_SERVICE_ID:-}" ]] && RAILWAY_SERVICE_ID="$_v" ;;
    esac
  done <"$procfile"
}

faunamix_apply_mysql_aliases() {
  DB_HOST="${DB_HOST:-${MYSQL_HOST:-}}"
  DB_PORT="${DB_PORT:-${MYSQL_PORT:-}}"
  DB_NAME="${DB_NAME:-${MYSQL_DATABASE:-}}"
  DB_USER="${DB_USER:-${MYSQL_USER:-}}"
  if [[ -z "${DB_PASS:-}" ]]; then
    DB_PASS="${MYSQL_PASSWORD:-${MYSQL_ROOT_PASSWORD:-}}"
  fi
}

faunamix_try_parse_mysql_database_url() {
  [[ -n "${DB_HOST:-}" ]] && return 0
  local raw="${DATABASE_URL:-}"
  [[ -z "$raw" ]] && raw="${MYSQL_URL:-}"
  [[ -n "$raw" ]] || return 0
  case "$raw" in
    mysql://*) ;;
    mysql2://*) ;;
    *) return 0 ;;
  esac
  command -v php >/dev/null 2>&1 || return 0
  local parsed
  parsed="$(
    DATABASE_URL="$raw" php -r '
      $u = (string) getenv("DATABASE_URL");
      $p = parse_url($u);
      if (!is_array($p) || !isset($p["host"]) || $p["host"] === "") {
        exit(0);
      }
      $emit = static function (string $k, string $v): void {
        echo $k . "=" . escapeshellarg($v) . PHP_EOL;
      };
      $emit("DB_HOST", (string) $p["host"]);
      $emit("DB_PORT", isset($p["port"]) ? (string) (int) $p["port"] : "3306");
      $emit("DB_NAME", isset($p["path"]) ? ltrim((string) $p["path"], "/") : "");
      $emit("DB_USER", isset($p["user"]) ? (string) $p["user"] : "root");
      $emit("DB_PASS", isset($p["pass"]) ? (string) $p["pass"] : "");
    ' 2>/dev/null
  )" || return 0
  [[ -n "$parsed" ]] || return 0
  # shellcheck disable=SC2046
  eval "$parsed" || return 0
}

faunamix_scavenge_db_env_from_proc() {
  [[ -d /proc ]] || return 0
  local procfile n
  n=0
  for procfile in /proc/[0-9]*/environ; do
    [[ -r "$procfile" ]] || continue
    faunamix_merge_db_keys_from_proc_environ "$procfile"
    n=$((n + 1))
    if (( n >= 512 )); then
      break
    fi
  done
  faunamix_apply_mysql_aliases
  faunamix_try_parse_mysql_database_url
}

# railway ssh + bash -c çocuk süreci sık sık boş ortamla başlar; üst (PPID) zincirinde tam env kalır.
faunamix_scavenge_db_env_from_parent_chain() {
  [[ -d /proc ]] || return 0
  local pid ppid steps
  pid="${FAUNAMIX_PROC_SCAN_START_PID:-$PPID}"
  steps=0
  while (( steps < 32 )) && [[ -n "${pid:-}" ]] && [[ "$pid" =~ ^[0-9]+$ ]]; do
    if [[ -r "/proc/$pid/environ" ]]; then
      faunamix_merge_db_keys_from_proc_environ "/proc/$pid/environ"
      faunamix_apply_mysql_aliases
      faunamix_try_parse_mysql_database_url
      [[ -n "${DB_HOST:-}" ]] && return 0
    fi
    ppid=""
    if [[ -r "/proc/$pid/status" ]]; then
      ppid="$(awk '/^PPid:/{print $2; exit}' "/proc/$pid/status" 2>/dev/null || true)"
    fi
    [[ -z "$ppid" || "$ppid" == "$pid" || "$ppid" == 0 ]] && break
    pid="$ppid"
    steps=$((steps + 1))
  done
  return 1
}

# Uygulama süreci (PHP / start.sh) Railway DB değişkenleriyle çalışır; ssh oturumu boş kalabilir.
faunamix_scavenge_db_env_from_app_pids() {
  [[ -d /proc ]] || return 1
  local pid seen="" _new
  if command -v pgrep >/dev/null 2>&1; then
    for _new in $(pgrep -f '/app/api/scripts/start\.sh' 2>/dev/null || true); do
      [[ -n "$_new" ]] && seen+=" $_new"
    done
    for _new in $(pgrep -f 'php.*-S' 2>/dev/null || true); do
      [[ -n "$_new" ]] && seen+=" $_new"
    done
    for _new in $(pgrep php 2>/dev/null | head -n 30 || true); do
      [[ -n "$_new" ]] && seen+=" $_new"
    done
  fi
  if command -v pidof >/dev/null 2>&1; then
    seen+=" $(pidof php 2>/dev/null || true)"
  fi
  for pid in $seen; do
    [[ "$pid" =~ ^[0-9]+$ ]] || continue
    [[ -r "/proc/$pid/environ" ]] || continue
    faunamix_merge_db_keys_from_proc_environ "/proc/$pid/environ"
    faunamix_apply_mysql_aliases
    faunamix_try_parse_mysql_database_url
    [[ -n "${DB_HOST:-}" ]] && return 0
  done
  return 1
}

# health.php kökte her imajda olmayabilir (farklı build); api+database+bu betik Faunamix deploy için yeterli imza.
faunamix_deploy_root_marker() {
  [[ -f "$ROOT/scripts/run-database-migrations.sh" ]] && [[ -d "$ROOT/database" ]] && [[ -d "$ROOT/api" ]]
}

faunamix_railway_like_runtime() {
  [[ -n "${RAILWAY_ENVIRONMENT:-}" ]] \
    || [[ -n "${RAILWAY_ENVIRONMENT_NAME:-}" ]] \
    || [[ -n "${RAILWAY_DEPLOYMENT_ID:-}" ]] \
    || [[ -n "${RAILWAY_PROJECT_ID:-}" ]] \
    || [[ -n "${RAILWAY_SERVICE_ID:-}" ]]
}

faunamix_railway_like_db_host() {
  [[ "${DB_HOST:-}" == *".railway.internal"* ]] || [[ "${DB_HOST:-}" == *".rlwy.net"* ]]
}

if [[ ! -f "$ENV_FILE" ]]; then
  faunamix_scavenge_db_env_from_proc || true
  if [[ -z "${DB_HOST:-}" ]]; then
    faunamix_scavenge_db_env_from_parent_chain || true
  fi
  if [[ -z "${DB_HOST:-}" ]]; then
    faunamix_scavenge_db_env_from_app_pids || true
  fi
fi

USE_PLATFORM_DB_ENV=0
if [[ "${FAUNAMIX_MIGRATE_FROM_ENV:-}" == "1" ]]; then
  USE_PLATFORM_DB_ENV=1
elif [[ ! -f "$ENV_FILE" ]]; then
  if faunamix_deploy_root_marker; then
    # Docker /app imajı: api/.env yoksa .env şartı gösterme; DB_* uygulama sürecinden / ortamdan gelsin.
    USE_PLATFORM_DB_ENV=1
  elif [[ -n "${DB_HOST:-}" ]] && { faunamix_railway_like_runtime || faunamix_railway_like_db_host; }; then
    USE_PLATFORM_DB_ENV=1
  fi
fi

if [[ "$USE_PLATFORM_DB_ENV" == "1" ]]; then
  : "${DB_HOST:?Platform / FAUNAMIX_MIGRATE_FROM_ENV: DB_HOST gerekli}"
  : "${DB_NAME:?Platform / FAUNAMIX_MIGRATE_FROM_ENV: DB_NAME gerekli}"
  : "${DB_USER:?Platform / FAUNAMIX_MIGRATE_FROM_ENV: DB_USER gerekli}"
else
  if [[ ! -f "$ENV_FILE" ]]; then
    echo "Eksik: $ENV_FILE — örnek: cp api/env.example api/.env && düzenleyin"
    echo "Uzak DB için: FAUNAMIX_ENV_FILE ile ayrı dosya verin (repoya eklemeyin)."
    echo "Railway ssh ortamı boşsa: FAUNAMIX_MIGRATE_FROM_ENV=1 /app/scripts/run-database-migrations.sh veya etkileşimli ssh."
    exit 1
  fi

  set -a
  # shellcheck disable=SC1090
  source "$ENV_FILE"
  set +a
fi

DB_HOST="${DB_HOST:-127.0.0.1}"
DB_PORT="${DB_PORT:-3306}"
DB_NAME="${DB_NAME:-faunamix}"
DB_USER="${DB_USER:-root}"
DB_PASS="${DB_PASS:-}"
if [[ -z "$DB_PASS" ]] && [[ -n "${RAILWAY_DB_PASS:-}" ]]; then
  DB_PASS="$RAILWAY_DB_PASS"
fi

# Aynı bash satırında çalışan `php scripts/verify-*.php` çocuk süreci bu değişkenleri miras alsın;
# aksi halde PHP yalnız /proc taramasına kalır (512 dosya + sırasız glob ile MYSQL_DATABASE kaçabilir).
export DB_HOST DB_PORT DB_NAME DB_USER DB_PASS

echo "==> Bağlantı hedefi: ${DB_NAME} @ ${DB_HOST}:${DB_PORT} (kullanıcı: ${DB_USER})" >&2

MYSQL_BASE=(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" --protocol=TCP --default-character-set=utf8mb4 --show-warnings)
if [[ -n "$DB_PASS" ]]; then
  export MYSQL_PWD="$DB_PASS"
else
  unset MYSQL_PWD 2>/dev/null || true
fi
# Railway TLS: Mac Homebrew mysql 9 → --ssl-mode=REQUIRED; Debian/MariaDB → --ssl; internal → kapalı.
if [[ "$DB_HOST" == *".railway.internal"* ]] && [[ "${FAUNAMIX_MYSQL_SSL:-}" != "1" ]]; then
  if mysql --help 2>&1 | grep -q 'ssl-mode'; then
    MYSQL_BASE+=( --ssl-mode=DISABLED )
  else
    MYSQL_BASE+=( --skip-ssl )
  fi
elif [[ "$DB_HOST" == *".rlwy.net"* ]] || [[ "${FAUNAMIX_MYSQL_SSL:-}" == "1" ]]; then
  if mysql --help 2>&1 | grep -q 'ssl-mode'; then
    MYSQL_BASE+=( --ssl-mode=REQUIRED )
  elif mysql --help 2>&1 | grep -q -- '--ssl-verify-server-cert'; then
    MYSQL_BASE+=( --ssl --ssl-verify-server-cert=0 )
  elif mysql --help 2>&1 | grep -q -- '--ssl'; then
    MYSQL_BASE+=( --ssl )
  fi
fi

mysql_run_pipe() {
  local err
  err="$(mktemp)"
  if ! "${MYSQL_BASE[@]}" "$DB_NAME" 2>"$err"; then
    echo "MySQL çıkış $?" >&2
    cat "$err" >&2
    rm -f "$err"
    return 1
  fi
  if grep -q '^ERROR ' "$err" 2>/dev/null; then
    echo "MySQL stderr ERROR:" >&2
    cat "$err" >&2
    rm -f "$err"
    return 1
  fi
  rm -f "$err"
  return 0
}

run_file() {
  local use_db="$1"
  local rel="$2"
  local path="$ROOT/$rel"
  echo "==> $rel"
  if [[ "$use_db" == "1" ]]; then
    # Migration dosyalarındaki USE faunamix; Railway vb. ortamlarda yanlış şemaya yazar.
    # sed yalnızca birebir satır eşleşir (boşluk/CRLF kaçırabilir); akışın başına hedef USE ekleyerek
    # mysql oturumunu her dosyada kesin DB_NAME şemasına sabitle.
    if ! {
      printf '%s\n' "USE \`${DB_NAME}\`;"
      sed -e "s|^USE faunamix;\$|USE \`${DB_NAME}\`;|" "$path"
    } | mysql_run_pipe; then
      echo "Migration başarısız: $rel" >&2
      exit 1
    fi
  else
    if ! mysql_run_pipe < "$path"; then
      echo "Migration başarısız: $rel" >&2
      exit 1
    fi
  fi
}

# 1) Şema + örnek ürünler — Railway’de CREATE DATABASE yetkisi yok; prod-bootstrap sonrası atlanabilir.
run_schema() {
  local path="$ROOT/database/schema.sql"
  echo "==> database/schema.sql"
  local sed_args=(-e "s|^USE faunamix;\$|USE \`${DB_NAME}\`;|")
  if faunamix_railway_like_db_host; then
    sed_args+=(-e '/^CREATE DATABASE /d')
  else
    sed_args+=(-e "s|^CREATE DATABASE IF NOT EXISTS faunamix |CREATE DATABASE IF NOT EXISTS \`${DB_NAME}\` |")
  fi
  if ! sed "${sed_args[@]}" "$path" | mysql_run_pipe; then
    echo "Migration başarısız: database/schema.sql" >&2
    exit 1
  fi
}

if [[ "${FAUNAMIX_SKIP_SCHEMA_SQL:-}" == "1" ]]; then
  echo "==> database/schema.sql (atlandı — FAUNAMIX_SKIP_SCHEMA_SQL=1, tablolar prod-bootstrap ile zaten var)"
else
  run_schema
fi

# 2) Sıralı migration’lar — USE faunamix; run_file içinde DB_NAME ile değiştirilir
FILES=(
  "database/admin_auth.sql"
  "database/migration_add_price_stock.sql"
  "database/migration_site_settings.sql"
  "database/migration_site_pages.sql"
  "database/migration_site_pages_seed_hakkimizda.sql"
  "database/migration_site_pages_hakkimizda_clear_legacy_excerpt.sql"
  "database/migration_nav_menu.sql"
  "database/migration_nav_menu_v2_hierarchy.sql"
  "database/migration_footer_menu.sql"
  "database/migration_footer_menu_show_in_footer.sql"
  "database/migration_cleanup_demo_footer_menu.sql"
  "database/migration_product_management.sql"
  "database/migration_products_fulltext_search.sql"
  "database/migration_admin_password_reset.sql"
  "database/migration_admin_password_history.sql"
  "database/migration_admin_login_lockout.sql"
  "database/migration_contact_submissions.sql"
  "database/migration_site_pages_legal_review.sql"
  "database/migration_site_pages_page_template.sql"
  "database/migration_site_pages_locale.sql"
  "database/migration_site_pages_seed_en_locale.sql"
  "database/migration_nav_footer_locale.sql"
  "database/migration_home_hero_locale.sql"
  "database/migration_site_settings_en_sections.sql"
  "database/migration_i18n_en_empty_content.sql"
  "database/migration_i18n_en_nav_footer_titles_seed.sql"
  "database/migration_i18n_nav_en_dedupe.sql"
  "database/migration_site_pages_seed_en_about_contact.sql"
  "database/migration_i18n_unified_columns.sql"
  "database/migration_i18n_merge_locale_rows.sql"
  "database/migration_home_hero_image_en.sql"
  "database/migration_fix_listing_route_en.sql"
  "database/migration_routes.sql"
  "database/migration_site_pages_seed_system_templates.sql"
  "database/migration_nav_menu_site_page_id.sql"
  "database/migration_product_discount_offers.sql"
  "database/migration_product_discount_offers_schedule.sql"
  "database/migration_reservation_requests.sql"
  "database/migration_reservation_requests_status.sql"
  "database/migration_reservation_option_and_payment_completed.sql"
  "database/migration_reservation_workflow_v2.sql"
  "database/migration_reservation_payment_method.sql"
  "database/migration_ecommerce.sql"
  "database/migration_ecommerce_order_events.sql"
  "database/migration_ecommerce_panel_kv.sql"
  "database/migration_ecommerce_checkout_idempotency.sql"
  "database/migration_page_builder.sql"
  "database/migration_product_variants.sql"
  "database/migration_enterprise_foundation.sql"
  "database/migration_ab_experiments_traffic.sql"
  "database/migration_content_revisions.sql"
  "database/migration_product_flat_routes.sql"
  "database/migration_commerce_facets_wishlist_customers.sql"
  "database/migration_shop_customers_admin_note.sql"
  "database/migration_shop_sessions.sql"
  "database/migration_shop_customer_addresses.sql"
  "database/migration_shop_password_resets.sql"
  "database/migration_customer_return_requests.sql"
  "database/migration_product_reviews.sql"
  "database/migration_product_reviews_verified.sql"
  "database/migration_shop_server_cart.sql"
  "database/migration_commerce_checkout_legal_related_variants.sql"
  "database/migration_shop_cart_abandonment.sql"
  "database/migration_route_type_product_search.sql"
  "database/migration_route_types.sql"
  "database/migration_product_images_clear_samples.sql"
  "database/migration_product_images_samples_paths_wide.sql"
  "database/migration_product_images_clear_picsum_placeholders.sql"
  "database/migration_menu_mojibake_repair.sql"
  "database/migration_deactivate_demo_nav_items.sql"
  "database/migration_deactivate_demo_vitrin_rows.sql"
  "database/migration_vitrin_production_cleanup.sql"
  "database/migration_vitrin_production_cleanup_followup.sql"
  "database/migration_vitrin_test_slug_redirect_listing.sql"
  "database/migration_vitrin_restore_active_product_routes.sql"
  "database/migration_home_hero_slides.sql"
  "database/migration_rum_web_vitals_events.sql"
  "database/migration_rum_rollup_daily.sql"
)

for f in "${FILES[@]}"; do
  run_file 1 "$f"
done

echo "Tamam: tüm sıralı migration dosyaları uygulandı ($DB_NAME @ $DB_HOST:$DB_PORT)."
echo "Doğrulama: php scripts/verify-commerce-schema.php (wishlist / yorum / rezervasyon tabloları)."
echo "Not: Üretim önyükleme api/scripts/prod-bootstrap.sql (+ prod-bootstrap-routing.sql); vitrin içeriği panelden. Yerel demo: api/scripts/dev-demo-seed.sql (elle)."
echo "Opsiyonel: Eski bootstrap demo kalıntıları için database/optional_remove_bootstrap_demo_content.sql (elle, yedek sonrası; migration zincirine dahil değildir)."