#!/bin/bash
# ═══════════════════════════════════════════════════════════════════════════
# Database Reset Script for E2E Tests
# ═══════════════════════════════════════════════════════════════════════════
#
# This script resets the test database to a clean state.
# It drops and recreates the test database, then runs schema bootstrap.
#
# Usage: ./reset-db.sh [environment]
#   environment: local (default), staging, production
#

set -e

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Configuration
ENVIRONMENT=${1:-local}
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$(dirname "$(dirname "$SCRIPT_DIR")")")"

source "$PROJECT_ROOT/scripts/runtime-env.sh"

echo -e "${YELLOW}═══════════════════════════════════════════════════════════════${NC}"
echo -e "${YELLOW}  Database Reset Script for E2E Tests${NC}"
echo -e "${YELLOW}═══════════════════════════════════════════════════════════════${NC}"

# Load environment variables
if [ "$ENVIRONMENT" = "local" ]; then
    # Prefer the E2E-specific runtime file when present so local E2E runs
    # stay isolated from the main development database.
    if [ -f "$PROJECT_ROOT/.env.test" ]; then
        ENV_FILE="$PROJECT_ROOT/.env.test"
    elif [ -f "$PROJECT_ROOT/.env" ]; then
        ENV_FILE="$PROJECT_ROOT/.env"
    else
        ENV_FILE=""
    fi
elif [ -f "$PROJECT_ROOT/.env.$ENVIRONMENT" ]; then
    ENV_FILE="$PROJECT_ROOT/.env.$ENVIRONMENT"
else
    ENV_FILE=""
fi

if [ -n "$ENV_FILE" ] && [ -f "$ENV_FILE" ]; then
    echo -e "${GREEN}✓${NC} Loading environment from: $ENV_FILE"
    nexus_load_env_file "$ENV_FILE"
else
    echo -e "${YELLOW}!${NC} No runtime env file found; using existing environment variables and defaults"
fi

# Default values
export TIDB_HOST=${TIDB_HOST:-"127.0.0.1"}
export TIDB_PORT=${TIDB_PORT:-"4000"}
export TIDB_USER=${TIDB_USER:-"root"}
export TIDB_PASSWORD=${TIDB_PASSWORD:-""}
export TIDB_DATABASE=${TIDB_DATABASE:-"nexuscrm_test"}

ensure_mysql_client_on_path() {
    if command -v mysql &> /dev/null; then
        return 0
    fi

    if command -v brew &> /dev/null; then
        MYSQL_PREFIX="$(brew --prefix mysql-client 2>/dev/null || true)"
        if [ -n "$MYSQL_PREFIX" ] && [ -x "$MYSQL_PREFIX/bin/mysql" ]; then
            export PATH="$MYSQL_PREFIX/bin:$PATH"
        fi
    fi
}

echo ""
echo -e "${YELLOW}Database Configuration:${NC}"
echo "  Host:     $TIDB_HOST"
echo "  Port:     $TIDB_PORT"
echo "  User:     $TIDB_USER"
echo "  Database: $TIDB_DATABASE"
echo ""

# Check if mysql client is available
ensure_mysql_client_on_path

if ! command -v mysql &> /dev/null; then
    echo -e "${RED}✗${NC} MySQL client not found. Installing..."

    # Try different package managers
    if command -v apt-get &> /dev/null; then
        sudo apt-get update && sudo apt-get install -y mysql-client
    elif command -v yum &> /dev/null; then
        sudo yum install -y mysql
    elif command -v brew &> /dev/null; then
        brew install mysql-client
    else
        echo -e "${RED}✗${NC} Cannot install mysql client automatically"
        echo "Please install mysql client manually"
        exit 1
    fi
fi

ensure_mysql_client_on_path

echo -e "${GREEN}✓${NC} MySQL client available"

# Drop and recreate database
echo ""
echo -e "${YELLOW}Dropping existing database...${NC}"
mysql --protocol=TCP -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" ${TIDB_PASSWORD:+-p"$TIDB_PASSWORD"} \
    -e "DROP DATABASE IF EXISTS $TIDB_DATABASE;"

echo -e "${GREEN}✓${NC} Database dropped"

echo ""
echo -e "${YELLOW}Creating fresh database...${NC}"
mysql --protocol=TCP -h "$TIDB_HOST" -P "$TIDB_PORT" -u "$TIDB_USER" ${TIDB_PASSWORD:+-p"$TIDB_PASSWORD"} \
    -e "CREATE DATABASE $TIDB_DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

echo -e "${GREEN}✓${NC} Database created"

# Schema bootstrap is handled by cmd/setup apply in global-setup.ts Step 2.
# Do NOT run setup_db here — it uses the old bootstrap path which creates
# _System_Object rows with default SharingModel=Private, conflicting with
# the control plane's metadata-first approach.

echo ""
echo -e "${GREEN}═══════════════════════════════════════════════════════════════${NC}"
echo -e "${GREEN}  Database reset complete!${NC}"
echo -e "${GREEN}═══════════════════════════════════════════════════════════════${NC}"
echo ""
echo "The test database '$TIDB_DATABASE' is now ready for E2E tests."