setup_database_now.ps1

# Database Setup Script - Run this after MySQL is started
# Password: 12345

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "Setting up Encore Database" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""

$mysqlPath = "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe"
$password = "12345"
$rootDir = Split-Path -Parent $PSScriptRoot

# Check if MySQL exists
if (-not (Test-Path $mysqlPath)) {
    Write-Host "ERROR: MySQL not found at: $mysqlPath" -ForegroundColor Red
    Write-Host "Please check MySQL installation path." -ForegroundColor Yellow
    exit 1
}

Write-Host "MySQL found at: $mysqlPath" -ForegroundColor Green
Write-Host ""

# Step 1: Test MySQL connection
Write-Host "Step 1: Testing MySQL connection..." -ForegroundColor Yellow
$testResult = & $mysqlPath -u root -p$password -e "SELECT 1;" 2>&1 | Out-String

# Check for actual errors (not just warnings)
if ($testResult -match "ERROR" -or ($LASTEXITCODE -ne 0 -and $testResult -notmatch "Warning")) {
    Write-Host "ERROR: Cannot connect to MySQL!" -ForegroundColor Red
    Write-Host "Error details: $testResult" -ForegroundColor Yellow
    Write-Host "Make sure MySQL service is running." -ForegroundColor Yellow
    Write-Host ""
    exit 1
}

Write-Host "MySQL connection successful!" -ForegroundColor Green
Write-Host ""

# Step 2: Create database
Write-Host "Step 2: Creating database 'encore_db'..." -ForegroundColor Cyan
& $mysqlPath -u root -p$password -e "CREATE DATABASE IF NOT EXISTS encore_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 2>&1 | Out-Null

if ($LASTEXITCODE -eq 0) {
    Write-Host "Database created successfully!" -ForegroundColor Green
} else {
    Write-Host "Database might already exist, continuing..." -ForegroundColor Yellow
}

# Step 3: Load schema
Write-Host ""
Write-Host "Step 3: Loading database schema..." -ForegroundColor Cyan
$schemaPath = Join-Path $rootDir "backend\database\schema.sql"

if (-not (Test-Path $schemaPath)) {
    Write-Host "ERROR: Schema file not found at: $schemaPath" -ForegroundColor Red
    exit 1
}

Get-Content $schemaPath | & $mysqlPath -u root -p$password encore_db 2>&1 | Out-Null

if ($LASTEXITCODE -eq 0) {
    Write-Host "Schema loaded successfully!" -ForegroundColor Green
} else {
    Write-Host "WARNING: There may have been errors loading schema. Some tables might already exist." -ForegroundColor Yellow
}

# Step 4: Load seed data
Write-Host ""
Write-Host "Step 4: Loading seed data..." -ForegroundColor Cyan
$seedPath = Join-Path $rootDir "backend\database\seed.sql"

if (-not (Test-Path $seedPath)) {
    Write-Host "ERROR: Seed file not found at: $seedPath" -ForegroundColor Red
    exit 1
}

Get-Content $seedPath | & $mysqlPath -u root -p$password encore_db 2>&1 | Out-Null

if ($LASTEXITCODE -eq 0) {
    Write-Host "Seed data loaded successfully!" -ForegroundColor Green
} else {
    Write-Host "WARNING: There may have been errors loading seed data. Some data might already exist." -ForegroundColor Yellow
}

# Step 5: Verify setup
Write-Host ""
Write-Host "Step 5: Verifying database setup..." -ForegroundColor Cyan

$userCount = & $mysqlPath -u root -p$password encore_db -e "SELECT COUNT(*) FROM users;" --skip-column-names 2>&1 | Select-Object -Last 1
$eventCount = & $mysqlPath -u root -p$password encore_db -e "SELECT COUNT(*) FROM events;" --skip-column-names 2>&1 | Select-Object -Last 1
$hotelCount = & $mysqlPath -u root -p$password encore_db -e "SELECT COUNT(*) FROM hotels;" --skip-column-names 2>&1 | Select-Object -Last 1

Write-Host ""
Write-Host "========================================" -ForegroundColor Green
Write-Host "Database Setup Complete!" -ForegroundColor Green
Write-Host "========================================" -ForegroundColor Green
Write-Host "Users in database: $userCount" -ForegroundColor Cyan
Write-Host "Events in database: $eventCount" -ForegroundColor Cyan
Write-Host "Hotels in database: $hotelCount" -ForegroundColor Cyan
Write-Host ""
Write-Host "Default login credentials:" -ForegroundColor Yellow
Write-Host "  Admin: admin@encore.com / password123" -ForegroundColor White
Write-Host "  User:  user1@encore.com / password123" -ForegroundColor White
Write-Host "  User:  user2@encore.com / password123" -ForegroundColor White
Write-Host ""
Write-Host "Next steps:" -ForegroundColor Yellow
Write-Host "  1. cd backend" -ForegroundColor White
Write-Host "  2. python -m venv .venv" -ForegroundColor White
Write-Host "  3. .venv\Scripts\activate" -ForegroundColor White
Write-Host "  4. pip install -r requirements.txt" -ForegroundColor White
Write-Host "  5. python app.py" -ForegroundColor White
Write-Host ""