# PowerShell script to add missing database tables to Docker container
# This script executes the combined SQL file in the MariaDB container

Write-Host "Adding missing database tables to Docker container..." -ForegroundColor Green

# Check if Docker is running
try {
    docker ps | Out-Null
    if ($LASTEXITCODE -ne 0) {
        Write-Host "Error: Docker is not running or not accessible" -ForegroundColor Red
        exit 1
    }
} catch {
    Write-Host "Error: Docker command not found. Please install Docker first." -ForegroundColor Red
    exit 1
}

# Check if the database container is running
$containerStatus = docker ps --filter "name=vs-db" --format "{{.Status}}"
if (-not $containerStatus) {
    Write-Host "Error: Database container 'vs-db' is not running" -ForegroundColor Red
    Write-Host "Please start the Docker containers first with: docker-compose up -d" -ForegroundColor Yellow
    exit 1
}

Write-Host "Database container status: $containerStatus" -ForegroundColor Cyan

# Wait for database to be ready
Write-Host "Checking database connectivity..." -ForegroundColor Yellow
$maxAttempts = 30
$attempt = 0

do {
    $attempt++
    Write-Host "Attempt $attempt/$maxAttempts - Testing database connection..." -ForegroundColor Gray
    
    $result = docker exec vs-db mysqladmin ping -h 127.0.0.1 -u root -proot --silent 2>$null
    if ($LASTEXITCODE -eq 0) {
        Write-Host "Database is ready!" -ForegroundColor Green
        break
    }
    
    if ($attempt -eq $maxAttempts) {
        Write-Host "Error: Database is not responding after $maxAttempts attempts" -ForegroundColor Red
        exit 1
    }
    
    Start-Sleep -Seconds 2
} while ($attempt -lt $maxAttempts)

# Execute the SQL file
Write-Host "Executing SQL script to add missing tables..." -ForegroundColor Yellow

try {
    # Copy the SQL file to the container
    docker cp "deploy/add_missing_tables.sql" vs-db:/tmp/add_missing_tables.sql
    if ($LASTEXITCODE -ne 0) {
        Write-Host "Error: Failed to copy SQL file to container" -ForegroundColor Red
        exit 1
    }
    
    # Execute the SQL file
    $output = docker exec vs-db mysql -u root -proot easystream -e "source /tmp/add_missing_tables.sql" 2>&1
    if ($LASTEXITCODE -ne 0) {
        Write-Host "Error executing SQL script:" -ForegroundColor Red
        Write-Host $output -ForegroundColor Red
        exit 1
    }
    
    # Clean up the temporary file
    docker exec vs-db rm /tmp/add_missing_tables.sql
    
    Write-Host "Success! All missing database tables have been added." -ForegroundColor Green
    Write-Host $output -ForegroundColor Cyan
    
} catch {
    Write-Host "Error: Failed to execute SQL script - $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}

# Verify tables were created
Write-Host "Verifying table creation..." -ForegroundColor Yellow

$verifyTables = @(
    "db_roles",
    "db_user_roles", 
    "db_role_permissions",
    "db_live_streams",
    "db_stream_viewers",
    "db_api_keys",
    "db_api_logs",
    "db_video_analytics",
    "db_platform_metrics"
)

$allTablesExist = $true
foreach ($table in $verifyTables) {
    $tableExists = docker exec vs-db mysql -u root -proot easystream -e "SHOW TABLES LIKE '$table';" --silent
    if ($tableExists -match $table) {
        Write-Host "✓ Table '$table' created successfully" -ForegroundColor Green
    } else {
        Write-Host "✗ Table '$table' was not created" -ForegroundColor Red
        $allTablesExist = $false
    }
}

if ($allTablesExist) {
    Write-Host "`nAll database tables have been successfully added to the Docker container!" -ForegroundColor Green
    Write-Host "The EasyStream platform now has complete RBAC, Live Streaming, and Analytics functionality." -ForegroundColor Cyan
} else {
    Write-Host "`nSome tables were not created successfully. Please check the error messages above." -ForegroundColor Red
    exit 1
}

Write-Host "`nDatabase setup complete! You can now use all platform features." -ForegroundColor Green