#!/usr/bin/env bash
# Run all IOM migrations in dependency order against an existing database.
# Usage:
#   ./migrate.sh                        # prompts for credentials
#   DB_USER=root DB_PASS=secret DB_HOST=localhost ./migrate.sh

set -euo pipefail

DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

# ── Credentials ────────────────────────────────────────────────
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-3306}"
DB_NAME="${DB_NAME:-BeezifiIOM}"

if [ -z "${DB_USER:-}" ]; then
  read -rp "MySQL user: " DB_USER
fi

if [ -z "${DB_PASS:-}" ]; then
  read -rsp "MySQL password: " DB_PASS
  echo
fi

MYSQL_CMD="mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASS} ${DB_NAME}"

# ── Helpers ────────────────────────────────────────────────────
GREEN='\033[0;32m'; RED='\033[0;31m'; RESET='\033[0m'

run_migration() {
  local file="$1"
  local name
  name="$(basename "$file")"
  printf "  %-60s" "$name"
  if $MYSQL_CMD < "$file" 2>/tmp/migrate_err; then
    printf "${GREEN}OK${RESET}\n"
  else
    printf "${RED}FAILED${RESET}\n"
    cat /tmp/migrate_err
    echo
    echo "Migration failed. Fix the error above and re-run."
    exit 1
  fi
}

# ── Run migrations in dependency order ─────────────────────────
echo
echo "Database : ${DB_NAME} @ ${DB_HOST}:${DB_PORT}"
echo "Running migrations..."
echo

# Core user/product field additions (ALTER TABLE — safe to re-run)
run_migration "$DIR/migration_add_password_reset.sql"
run_migration "$DIR/migration_add_totp.sql"
run_migration "$DIR/migration_add_product_fields.sql"
run_migration "$DIR/migration_add_images.sql"

# New feature tables
run_migration "$DIR/migration_add_multicurrency.sql"
run_migration "$DIR/migration_add_batch_serial.sql"
run_migration "$DIR/migration_add_integrations.sql"
run_migration "$DIR/migration_add_accounting.sql"
run_migration "$DIR/migration_add_pos.sql"

# Billing (drops old tables — idempotent via CREATE TABLE)
run_migration "$DIR/migration_billing.sql"

# System admin + promo codes
run_migration "$DIR/migration_sysadmin.sql"

# 2026-05-05 platform upgrades
run_migration "$DIR/migration_20260505_operational_upgrade.sql"
run_migration "$DIR/migration_20260505_add_beezifi_accounting.sql"
run_migration "$DIR/migration_20260505_competitiveness_upgrade.sql"
run_migration "$DIR/migration_20260505_planning_forecast_enhancements.sql"
run_migration "$DIR/migration_20260505_warehouse_execution.sql"

# Schema alignment (conditional column renames — run last)
run_migration "$DIR/migration_20260505_sync_schema_alignment.sql"

echo
echo "All migrations completed successfully."