param(
    [ValidateSet("local", "network", "test")]
    [string]$Mode,
    [Alias("DbHost")]
    [string]$HostName = "localhost",
    [int]$Port = 3306,
    [string]$Database = "SALECULATOR",
    [Alias("User")]
    [string]$DbUser = "saleculator",
    [Alias("Password")]
    [string]$DbPassword = "",
    [Alias("ConfigPath")]
    [string]$ConfigFile = "$env:USERPROFILE\Saleculator.properties",
    [string]$RootUser = "root",
    [string]$RootPassword = "",
    [switch]$AllowRemoteClients
)

$ErrorActionPreference = "Stop"

function Find-MySqlTool {
    param([string]$ToolName)

    $command = Get-Command $ToolName -ErrorAction SilentlyContinue
    if ($command) {
        return $command.Source
    }

    $candidates = @(
        "$env:ProgramFiles\MySQL\MySQL Server*\bin\$ToolName.exe",
        "${env:ProgramFiles(x86)}\MySQL\MySQL Server*\bin\$ToolName.exe"
    )

    foreach ($pattern in $candidates) {
        $match = Get-ChildItem -Path $pattern -ErrorAction SilentlyContinue | Select-Object -First 1
        if ($match) {
            return $match.FullName
        }
    }

    throw "$ToolName was not found. Install MySQL client tools or add them to PATH."
}

function Invoke-MySql {
    param(
        [string]$Sql,
        [string]$User,
        [string]$Password,
        [string]$Server = "localhost",
        [int]$ServerPort = 3306,
        [string]$Schema = ""
    )

    $mysql = Find-MySqlTool "mysql"
    $args = @("--host=$Server", "--port=$ServerPort", "--user=$User", "--protocol=tcp", "--batch", "--silent")
    if ($Schema) {
        $args += $Schema
    }

    $env:MYSQL_PWD = $Password
    try {
        $Sql | & $mysql @args
        if ($LASTEXITCODE -ne 0) {
            throw "mysql exited with code $LASTEXITCODE"
        }
    } finally {
        Remove-Item Env:\MYSQL_PWD -ErrorAction SilentlyContinue
    }
}

function Read-Config {
    param([string]$Path)
    $props = [ordered]@{}
    if (Test-Path $Path) {
        foreach ($line in Get-Content $Path) {
            if ($line -match "^\s*([^#!][^=]*)=(.*)$") {
                $props[$matches[1].Trim()] = $matches[2]
            }
        }
    }
    return $props
}

function Write-Config {
    param(
        [string]$Path,
        [hashtable]$Values
    )

    $template = Join-Path (Split-Path -Parent $PSScriptRoot) "config\Saleculator.properties.sample"
    if (!(Test-Path $Path) -and (Test-Path $template)) {
        New-Item -ItemType Directory -Force -Path (Split-Path -Parent $Path) | Out-Null
        Copy-Item $template $Path
    }

    $props = Read-Config $Path
    foreach ($key in $Values.Keys) {
        $props[$key] = $Values[$key]
    }

    $lines = New-Object System.Collections.Generic.List[string]
    $seen = @{}
    if (Test-Path $Path) {
        foreach ($line in Get-Content $Path) {
            if ($line -match "^\s*([^#!][^=]*)=(.*)$") {
                $key = $matches[1].Trim()
                if ($Values.ContainsKey($key)) {
                    $lines.Add("$key=$($Values[$key])")
                    $seen[$key] = $true
                } else {
                    $lines.Add($line)
                }
            } else {
                $lines.Add($line)
            }
        }
    }

    foreach ($key in $Values.Keys) {
        if (!$seen.ContainsKey($key)) {
            $lines.Add("$key=$($Values[$key])")
        }
    }

    New-Item -ItemType Directory -Force -Path (Split-Path -Parent $Path) | Out-Null
    [System.IO.File]::WriteAllLines($Path, [string[]]$lines, [System.Text.Encoding]::GetEncoding("iso-8859-1"))
}

function Get-JdbcUrl {
    param([string]$Server, [int]$ServerPort, [string]$Schema)
    return ("jdbc\:mysql\://{0}:{1}/{2}?characterEncoding\=utf8" -f $Server, $ServerPort, $Schema)
}

if (!$Mode) {
    Write-Host "Saleculator database setup"
    Write-Host "1. Standalone / Main POS database on this machine"
    Write-Host "2. Network client connecting to existing POS database"
    Write-Host "3. Test current database connection"
    $choice = Read-Host "Select 1, 2, or 3"
    if ($choice -eq "1") { $Mode = "local" }
    elseif ($choice -eq "2") { $Mode = "network" }
    elseif ($choice -eq "3") { $Mode = "test" }
    else { throw "Invalid choice." }
}

if ($Mode -eq "local") {
    if (!$DbPassword) {
        $DbPassword = Read-Host "Password for Saleculator database user '$DbUser'"
    }
    if (!$RootPassword) {
        $RootPassword = Read-Host "MySQL admin password for '$RootUser'"
    }

    $clientHost = if ($AllowRemoteClients) { "%" } else { "localhost" }
    $escapedPassword = $DbPassword.Replace("'", "''")
    $sql = @"
CREATE DATABASE IF NOT EXISTS `$Database` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER IF NOT EXISTS '$DbUser'@'$clientHost' IDENTIFIED BY '$escapedPassword';
GRANT ALL PRIVILEGES ON `$Database`.* TO '$DbUser'@'$clientHost';
FLUSH PRIVILEGES;
"@
    Invoke-MySql -Sql $sql -User $RootUser -Password $RootPassword -Server "localhost" -ServerPort $Port

    Write-Config -Path $ConfigFile -Values @{
        "db.URL" = Get-JdbcUrl -Server "localhost" -ServerPort $Port -Schema $Database
        "db.user" = $DbUser
        "db.password" = $DbPassword
        "db.driver" = "com.mysql.jdbc.Driver"
        "db.driverlib" = "./lib/mysql-connector-java-5.1.7-bin.jar"
    }

    Write-Host "Local database configured."
    Write-Host "Config: $ConfigFile"
    Write-Host "Start Saleculator and approve database creation when prompted."
}
elseif ($Mode -eq "network") {
    if (!$DbPassword) {
        $DbPassword = Read-Host "Password for Saleculator database user '$DbUser'"
    }

    Write-Config -Path $ConfigFile -Values @{
        "db.URL" = Get-JdbcUrl -Server $HostName -ServerPort $Port -Schema $Database
        "db.user" = $DbUser
        "db.password" = $DbPassword
        "db.driver" = "com.mysql.jdbc.Driver"
        "db.driverlib" = "./lib/mysql-connector-java-5.1.7-bin.jar"
    }

    Write-Host "Network client database configuration saved."
    Write-Host "Config: $ConfigFile"
}
elseif ($Mode -eq "test") {
    if (!$DbPassword) {
        $DbPassword = Read-Host "Password for Saleculator database user '$DbUser'"
    }
    Invoke-MySql -Sql "SELECT 1;" -User $DbUser -Password $DbPassword -Server $HostName -ServerPort $Port -Schema $Database
    Write-Host "Database connection OK: $HostName`:$Port/$Database"
}
