param([switch] $Elevated)

$ErrorActionPreference = 'Stop'
$projectRoot = Split-Path -Parent $PSScriptRoot
$logPath = Join-Path $projectRoot 'mysql-local-setup.log'

if (-not $Elevated) {
    $arguments = @(
        '-NoProfile',
        '-ExecutionPolicy', 'Bypass',
        '-File', ('"{0}"' -f $PSCommandPath),
        '-Elevated'
    )

    $process = Start-Process -FilePath 'powershell.exe' -ArgumentList $arguments -Verb RunAs -Wait -PassThru
    exit $process.ExitCode
}

$serviceName = 'MySQL80'
$mysqlBase = 'C:\Program Files\MySQL\MySQL Server 8.0'
$mysqldPath = Join-Path $mysqlBase 'bin\mysqld.exe'
$mysqlPath = Join-Path $mysqlBase 'bin\mysql.exe'
$defaultsFile = 'C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'
$originalConfig = [IO.File]::ReadAllText($defaultsFile)
$initFile = 'C:\ProgramData\MySQL\microlearn-init.sql'
$mysqlErrorLog = 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\TOORNAA.err'
$envFile = Join-Path $projectRoot 'backend\.env'
$passwordBytes = New-Object byte[] 24
$randomNumberGenerator = [Security.Cryptography.RandomNumberGenerator]::Create()
$randomNumberGenerator.GetBytes($passwordBytes)
$randomNumberGenerator.Dispose()
$password = ([BitConverter]::ToString($passwordBytes)).Replace('-', '').ToLowerInvariant()

$sql = @"
CREATE DATABASE IF NOT EXISTS microlearn CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS microlearn_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS 'microlearn'@'localhost' IDENTIFIED BY '$password';
ALTER USER 'microlearn'@'localhost' IDENTIFIED BY '$password';
CREATE USER IF NOT EXISTS 'microlearn'@'127.0.0.1' IDENTIFIED BY '$password';
ALTER USER 'microlearn'@'127.0.0.1' IDENTIFIED BY '$password';
GRANT ALL PRIVILEGES ON microlearn.* TO 'microlearn'@'localhost';
GRANT ALL PRIVILEGES ON microlearn_test.* TO 'microlearn'@'localhost';
GRANT ALL PRIVILEGES ON microlearn.* TO 'microlearn'@'127.0.0.1';
GRANT ALL PRIVILEGES ON microlearn_test.* TO 'microlearn'@'127.0.0.1';
FLUSH PRIVILEGES;
"@

[IO.File]::WriteAllText($initFile, $sql, [Text.UTF8Encoding]::new($false))
$initFileForMySql = $initFile.Replace('\', '/')

try {
    Stop-Service -Name $serviceName -Force
    [IO.File]::WriteAllText(
        $defaultsFile,
        ($originalConfig.TrimEnd() + "`r`ninit-file=$initFileForMySql`r`n"),
        [Text.UTF8Encoding]::new($false)
    )
    Start-Service -Name $serviceName

    $deadline = [DateTime]::UtcNow.AddSeconds(45)
    do {
        Start-Sleep -Milliseconds 500
        $service = Get-Service -Name $serviceName
    } until ($service.Status -eq 'Running' -or [DateTime]::UtcNow -ge $deadline)

    if ($service.Status -ne 'Running') {
        throw 'MySQL service did not start with the one-time initialization file.'
    }

    $env:MYSQL_PWD = $password
    & $mysqlPath --protocol=TCP --host=127.0.0.1 --port=3306 --user=microlearn --skip-column-names --execute='SELECT 1' | Out-Null
    if ($LASTEXITCODE -ne 0) {
        throw 'The MicroLearn MySQL account could not connect after initialization.'
    }

    $contents = [IO.File]::ReadAllText($envFile)
    $contents = [Regex]::Replace($contents, '(?m)^DB_CONNECTION=.*$', 'DB_CONNECTION=mysql')
    $contents = [Regex]::Replace($contents, '(?m)^DB_HOST=.*$', 'DB_HOST=127.0.0.1')
    $contents = [Regex]::Replace($contents, '(?m)^DB_PORT=.*$', 'DB_PORT=3306')
    $contents = [Regex]::Replace($contents, '(?m)^DB_DATABASE=.*$', 'DB_DATABASE=microlearn')
    $contents = [Regex]::Replace($contents, '(?m)^DB_USERNAME=.*$', 'DB_USERNAME=microlearn')
    $contents = [Regex]::Replace($contents, '(?m)^DB_PASSWORD=.*$', "DB_PASSWORD=$password")
    [IO.File]::WriteAllText($envFile, $contents, [Text.UTF8Encoding]::new($false))

    'MySQL databases, application user, and backend environment configured.' | Set-Content -LiteralPath $logPath
}
catch {
    $details = $_ | Out-String
    if (Test-Path -LiteralPath $mysqlErrorLog) {
        $details += "`r`nMySQL error log:`r`n"
        $details += (Get-Content -LiteralPath $mysqlErrorLog -Tail 80 | Out-String)
    }
    $details | Set-Content -LiteralPath $logPath
    throw
}
finally {
    $env:MYSQL_PWD = $null
    Stop-Service -Name $serviceName -Force -ErrorAction SilentlyContinue
    [IO.File]::WriteAllText($defaultsFile, $originalConfig, [Text.UTF8Encoding]::new($false))
    Start-Service -Name $serviceName
    Remove-Item -LiteralPath $initFile -Force -ErrorAction SilentlyContinue
}