# Модуль: gper-db.psm1
# Версия: 3.0.0
# Дата:   2026-05-21
# Описание: Firebird-хранилище для GPER Project.
#            Провайдер: FirebirdSql.Data.FirebirdClient (NuGet, скачивается автоматически)
#            БД создаётся автоматически при первом запуске.
#Requires -Version 5.0

# ================================================================
#  КОНФИГУРАЦИЯ ПОДКЛЮЧЕНИЯ
# ================================================================
$Script:FB_HOST    = 'localhost'
$Script:FB_PORT    = 3050
$Script:FB_PATH    = 'L:\Development\009-Self-host\0003-Gper-Project\src\Data\gper.fdb'
$Script:FB_USER    = 'SYSDBA'
$Script:FB_PASS    = 'mid308'
$Script:FB_CHARSET = 'UTF8'

# Кэш DLL провайдера
$Script:FB_LIB_DIR   = Join-Path $env:APPDATA 'gper.ru\lib'
# PS 5.x (Desktop) работает на .NET Framework — нужна сборка netstandard2.0 из v7.x
# PS 7.x (Core) работает на .NET 8 — подходит net8.0 сборка из v9.x
if ($PSVersionTable.PSEdition -eq 'Desktop') {
    $Script:FB_NUGET_VER = '7.10.3'
    $Script:FB_DLL       = Join-Path $Script:FB_LIB_DIR 'FbClient_ns20.dll'
} else {
    $Script:FB_NUGET_VER = '9.1.1'
    $Script:FB_DLL       = Join-Path $Script:FB_LIB_DIR 'FbClient_net8.dll'
}

# Поля с длинным текстом — хранятся как BLOB SUB_TYPE 1 (не ограничены размером записи)
$Script:FB_BLOB_FIELDS = @(
    'TEXT','BODY','CONTENT','DESCRIPTION','DESCR','NOTES',
    'MESSAGE','DATA','VALUE','DETAILS','COMMENT','REMARKS','INFO'
)

# Объект открытого соединения
$Script:FB_CONN = $null

# Путь к файлу с сохранёнными параметрами подключения
$Script:FB_CONFIG_FILE = Join-Path $env:APPDATA 'gper.ru\gper-db.config.json'

# Формула пересборки строки подключения
function script:Build-FbConnStr {
    $Script:FB_CONNSTR = "User ID=$($Script:FB_USER);" +
                         "Password=$($Script:FB_PASS);" +
                         "Database=$($Script:FB_HOST)/$($Script:FB_PORT):$($Script:FB_PATH);" +
                         "Dialect=3;Charset=$($Script:FB_CHARSET);"
}
Build-FbConnStr

# Загружаем сохранённый конфиг (если есть) — переопределяет дефолты
if (Test-Path $Script:FB_CONFIG_FILE) {
    try {
        $_cfg = Get-Content $Script:FB_CONFIG_FILE -Raw | ConvertFrom-Json
        if ($_cfg.PSObject.Properties['Host'] -and $_cfg.Host) { $Script:FB_HOST = $_cfg.Host }
        if ($_cfg.PSObject.Properties['Port'] -and $_cfg.Port) { $Script:FB_PORT = [int]$_cfg.Port }
        if ($_cfg.PSObject.Properties['Path'] -and $_cfg.Path) { $Script:FB_PATH = $_cfg.Path }
        if ($_cfg.PSObject.Properties['User'] -and $_cfg.User) { $Script:FB_USER = $_cfg.User }
        if ($_cfg.PSObject.Properties['Pass'] -and $_cfg.Pass) { $Script:FB_PASS = $_cfg.Pass }
        Build-FbConnStr
    } catch {}
    Remove-Variable _cfg -ErrorAction SilentlyContinue
}

# ================================================================
#  Assert-FirebirdProvider
#  Загружает DLL. Порядок:
#    1. Уже в памяти
#    2. Кэш в %APPDATA%\gper.ru\lib\
#    3. Реестр Firebird (если установлен)
#    4. Скачать с NuGet (nupkg = zip)
# ================================================================
function Assert-FirebirdProvider {
    if ([System.AppDomain]::CurrentDomain.GetAssemblies() |
            Where-Object { $_.GetName().Name -eq 'FirebirdSql.Data.FirebirdClient' }) {
        return
    }

    if (Test-Path $Script:FB_DLL) {
        Add-Type -Path $Script:FB_DLL
        return
    }

    # Попробуем из установленного Firebird/HQbird
    $regInstances = Get-ItemProperty 'HKLM:\SOFTWARE\Firebird Project\Firebird Server\Instances' -ErrorAction SilentlyContinue
    $fbRoot = $regInstances.DefaultInstance
    if (-not $fbRoot) {
        $fbRoot = $regInstances.PSObject.Properties |
            Where-Object { $_.Name -notlike 'PS*' } |
            Select-Object -First 1 -ExpandProperty Value
    }
    if ($fbRoot) {
        $regDll = Get-ChildItem $fbRoot -Recurse -Filter 'FirebirdSql.Data.FirebirdClient.dll' `
            -ErrorAction SilentlyContinue | Select-Object -First 1
        if ($regDll) {
            if (-not (Test-Path $Script:FB_LIB_DIR)) {
                New-Item $Script:FB_LIB_DIR -ItemType Directory -Force | Out-Null
            }
            Copy-Item $regDll.FullName $Script:FB_DLL -Force
            Add-Type -Path $Script:FB_DLL
            return
        }
    }

    # Скачать с NuGet
    Write-Host "Скачиваю FirebirdSql.Data.FirebirdClient v$($Script:FB_NUGET_VER)..." -ForegroundColor Cyan
    if (-not (Test-Path $Script:FB_LIB_DIR)) {
        New-Item $Script:FB_LIB_DIR -ItemType Directory -Force | Out-Null
    }
    $tmpPkg = Join-Path $env:TEMP 'fbclient.nupkg'
    $pkgId  = 'firebirdsql.data.firebirdclient'
    $url    = "https://api.nuget.org/v3-flatcontainer/$pkgId/$($Script:FB_NUGET_VER)/$pkgId.$($Script:FB_NUGET_VER).nupkg"
    Invoke-WebRequest -Uri $url -OutFile $tmpPkg -UseBasicParsing

    $tmpDir = Join-Path $env:TEMP 'fbclient_pkg'
    if (Test-Path $tmpDir) { Remove-Item $tmpDir -Recurse -Force }
    Expand-Archive -Path $tmpPkg -DestinationPath $tmpDir

    # TFM под рантайм: PS Desktop → .NET Framework, PS Core → .NET 6/8
    $tfmOrder = if ($PSVersionTable.PSEdition -eq 'Desktop') {
        @('netstandard2.0', 'net48', 'net45')
    } else {
        @('net8.0', 'net7.0', 'net6.0', 'net5.0', 'netstandard2.0')
    }
    $dll = $null
    foreach ($tfm in $tfmOrder) {
        $c = Join-Path $tmpDir "lib\$tfm\FirebirdSql.Data.FirebirdClient.dll"
        if (Test-Path $c) { $dll = $c; break }
    }
    if (-not $dll) {
        $dll = Get-ChildItem $tmpDir -Recurse -Filter 'FirebirdSql.Data.FirebirdClient.dll' |
            Select-Object -First 1 -ExpandProperty FullName
    }
    if (-not $dll) { throw "Не нашёл DLL в пакете NuGet" }

    Copy-Item $dll $Script:FB_DLL -Force
    Remove-Item $tmpDir -Recurse -Force -ErrorAction SilentlyContinue
    Remove-Item $tmpPkg -Force -ErrorAction SilentlyContinue

    Add-Type -Path $Script:FB_DLL
    Write-Host "Провайдер загружен: $($Script:FB_DLL)" -ForegroundColor Green
}

# ================================================================
#  Open-GperConnection
#  Открывает соединение. Если файл БД не существует — создаёт его.
# ================================================================
function Open-GperConnection {
    Assert-FirebirdProvider

    if ($Script:FB_CONN -and
        $Script:FB_CONN.State -eq [System.Data.ConnectionState]::Open) {
        return
    }

    # Создаём БД если нет файла - только для локального подключения
    $isLocal = (-not $Script:FB_HOST) -or ($Script:FB_HOST -in @('localhost', '127.0.0.1', '::1'))
    if ($isLocal -and $Script:FB_PATH -match '^[A-Za-z]:' -and -not (Test-Path $Script:FB_PATH)) {
        $dir = Split-Path $Script:FB_PATH -Parent
        if (-not (Test-Path $dir)) { New-Item $dir -ItemType Directory -Force | Out-Null }
        Write-Host "Создаю БД: $($Script:FB_PATH)..." -ForegroundColor Cyan
        # Ищем isql.exe (HQbird/Firebird): PATH → реестр
        $isql = Get-Command isql.exe -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Source
        if (-not $isql) {
            $ri = Get-ItemProperty 'HKLM:\SOFTWARE\Firebird Project\Firebird Server\Instances' -ErrorAction SilentlyContinue
            $fbRoot2 = $ri.DefaultInstance
            if (-not $fbRoot2) {
                $fbRoot2 = $ri.PSObject.Properties | Where-Object { $_.Name -notlike 'PS*' } |
                    Select-Object -First 1 -ExpandProperty Value
            }
            if ($fbRoot2) {
                $c = Join-Path $fbRoot2 'isql.exe'
                if (Test-Path $c) { $isql = $c }
            }
        }
        try {
            if ($isql) {
                $tmpSql = Join-Path $env:TEMP 'gper_create_db.sql'
                $createSql = "CREATE DATABASE '$($Script:FB_HOST)/$($Script:FB_PORT):$($Script:FB_PATH)' " +
                             "USER '$($Script:FB_USER)' PASSWORD '$($Script:FB_PASS)' " +
                             "PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8;"
                Set-Content $tmpSql $createSql -Encoding UTF8
                $out = & $isql -input $tmpSql 2>&1
                Remove-Item $tmpSql -Force -ErrorAction SilentlyContinue
                if ($LASTEXITCODE -ne 0) { throw "isql: $out" }
            } else {
                [FirebirdSql.Data.FirebirdClient.FbConnection]::CreateDatabase($Script:FB_CONNSTR, $true)
            }
            Write-Host "БД создана." -ForegroundColor Green
        } catch {
            throw "Ошибка создания БД '$($Script:FB_PATH)': $($_.Exception.Message)"
        }
    }

    try {
        $Script:FB_CONN = [FirebirdSql.Data.FirebirdClient.FbConnection]::new($Script:FB_CONNSTR)
        $Script:FB_CONN.Open()
    } catch {
        $Script:FB_CONN = $null
        throw "Ошибка подключения '$($Script:FB_HOST)/$($Script:FB_PORT):$($Script:FB_PATH)': $($_.Exception.Message)"
    }
}

# ================================================================
#  Close-GperConnection
# ================================================================
function Close-GperConnection {
    if ($Script:FB_CONN -and
        $Script:FB_CONN.State -eq [System.Data.ConnectionState]::Open) {
        $Script:FB_CONN.Close()
        $Script:FB_CONN.Dispose()
        $Script:FB_CONN = $null
    }
}

# ================================================================
#  Invoke-FbNonQuery  — DDL / INSERT / UPDATE / DELETE
#  Invoke-FbScalar    — одно значение
#  Invoke-FbQuery     — SELECT → PSCustomObject[]
# ================================================================
function Invoke-FbNonQuery {
    param([string]$Sql, [hashtable]$Params = @{})
    if (-not $Script:FB_CONN -or $Script:FB_CONN.State -ne [System.Data.ConnectionState]::Open) {
        throw "Invoke-FbNonQuery: нет открытого соединения"
    }
    $tran = $Script:FB_CONN.BeginTransaction()
    $cmd  = $Script:FB_CONN.CreateCommand()
    $cmd.Transaction = $tran
    $cmd.CommandText = $Sql
    foreach ($k in $Params.Keys) {
        $cmd.Parameters.Add(
            [FirebirdSql.Data.FirebirdClient.FbParameter]::new("@$k", [string]$Params[$k])
        ) | Out-Null
    }
    try   { $cmd.ExecuteNonQuery() | Out-Null; $tran.Commit() }
    catch { $tran.Rollback(); throw }
    finally { $cmd.Dispose(); $tran.Dispose() }
}

function Invoke-FbScalar {
    param([string]$Sql, [hashtable]$Params = @{})
    if (-not $Script:FB_CONN -or $Script:FB_CONN.State -ne [System.Data.ConnectionState]::Open) {
        throw "Invoke-FbScalar: нет открытого соединения"
    }
    $tran = $Script:FB_CONN.BeginTransaction()
    $cmd  = $Script:FB_CONN.CreateCommand()
    $cmd.Transaction = $tran
    $cmd.CommandText = $Sql
    foreach ($k in $Params.Keys) {
        $cmd.Parameters.Add(
            [FirebirdSql.Data.FirebirdClient.FbParameter]::new("@$k", [string]$Params[$k])
        ) | Out-Null
    }
    try   { $r = $cmd.ExecuteScalar(); $tran.Commit(); return $r }
    catch { $tran.Rollback(); throw }
    finally { $cmd.Dispose(); $tran.Dispose() }
}

function Invoke-FbQuery {
    param([string]$Sql, [hashtable]$Params = @{})
    if (-not $Script:FB_CONN -or $Script:FB_CONN.State -ne [System.Data.ConnectionState]::Open) {
        throw "Invoke-FbQuery: нет открытого соединения"
    }
    $tran   = $Script:FB_CONN.BeginTransaction()
    $cmd    = $Script:FB_CONN.CreateCommand()
    $cmd.Transaction = $tran
    $cmd.CommandText = $Sql
    foreach ($k in $Params.Keys) {
        $cmd.Parameters.Add(
            [FirebirdSql.Data.FirebirdClient.FbParameter]::new("@$k", [string]$Params[$k])
        ) | Out-Null
    }
    try {
        $reader = $cmd.ExecuteReader()
        $rows   = [System.Collections.Generic.List[object]]::new()
        while ($reader.Read()) {
            $row = [ordered]@{}
            for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                $row[$reader.GetName($i)] = $reader.GetValue($i)
            }
            $rows.Add([PSCustomObject]$row)
        }
        $reader.Close()
        $tran.Commit()
        return $rows.ToArray()
    }
    catch { $tran.Rollback(); throw }
    finally { $cmd.Dispose(); $tran.Dispose()}
}

# ================================================================
#  Вспомогательные
# ================================================================
function Test-FbTable {
    param([string]$TableName)
    $n   = $TableName.ToUpper()
    $cnt = Invoke-FbScalar -Sql "SELECT COUNT(*) FROM RDB`$RELATIONS WHERE RDB`$RELATION_NAME = '$n'"
    return ([int]$cnt -gt 0)
}

function Test-FbColumn {
    param([string]$TableName, [string]$ColumnName)
    $t   = $TableName.ToUpper()
    $c   = $ColumnName.ToUpper()
    $cnt = Invoke-FbScalar -Sql @"
SELECT COUNT(*) FROM RDB`$RELATION_FIELDS
WHERE RDB`$RELATION_NAME = '$t' AND RDB`$FIELD_NAME = '$c'
"@
    return ([int]$cnt -gt 0)
}

function ConvertTo-SafeName {
    param([string]$Name)
    return ($Name -replace '[^A-Za-z0-9_]', '_').ToUpper()
}

# Get-FbColType: VARCHAR(250) для обычных полей, BLOB SUB_TYPE 1 для длинных
# VARCHAR(250) в UTF-8 = 250×4 = 1000 байт → до ~65 полей в таблице
function Get-FbColType {
    param([string]$ColName)
    if ((ConvertTo-SafeName $ColName) -in $Script:FB_BLOB_FIELDS) {
        return 'BLOB SUB_TYPE 1'
    }
    return 'VARCHAR(250)'
}

function Test-GperDbAddr {
    param([string]$Address)
    if ($Address -match '^https?://') {
        try {
            $r = Invoke-WebRequest -Uri $Address -TimeoutSec 3 -Method Head `
                -UseBasicParsing -ErrorAction Stop
            return ($r.StatusCode -lt 500)
        } catch { return $false }
    } else {
        return (Test-Connection -ComputerName $Address -Count 1 -Quiet -ErrorAction SilentlyContinue)
    }
}

# ================================================================
#  Send-ToDb
#  Записывает данные из хэш-таблицы в Firebird.
#
#  ФОРМАТ:
#    @{
#        DbName = 'что угодно'     # зарезервировано
#        ИмяТаблицы = @{
#            Поле1 = 'значение'
#            ПолеN = 'значение'
#        }
#    }
# ================================================================
function Send-ToDb {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory, ValueFromPipeline)]
        [hashtable]$InputObject
    )

    process {
        try {
        if (-not ($InputObject.Keys | Where-Object { $_ -ieq 'DbName' })) {
            throw "Обязательный ключ 'DbName' не найден"
        }

        $tables = $InputObject.GetEnumerator() |
            Where-Object { $_.Value -is [hashtable] } |
            ForEach-Object { $_ }

        if (-not $tables) {
            throw "Нет вложенных хэш-таблиц. Структура: @{ DbName='X'; ИмяТаблицы=@{...} }"
        }

        Open-GperConnection

        foreach ($tableEntry in $tables) {
            $tblName = ConvertTo-SafeName $tableEntry.Key
            $data    = $tableEntry.Value

            if ($data.Count -eq 0) {
                Write-Host "ПРЕДУПРЕЖДЕНИЕ: '$tblName' пустая — пропускаю" -ForegroundColor Yellow
                continue
            }

            # Проверка IP/URL полей
            $addrKeys = @('ip', 'url', 'host', 'address', 'addr', 'endpoint')
            foreach ($key in $data.Keys) {
                if ($key.ToLower() -in $addrKeys -and $data[$key]) {
                    Write-Host "Проверяю доступность '$($data[$key])'..." -ForegroundColor Cyan -NoNewline
                    $ok = Test-GperDbAddr -Address $data[$key]
                    Write-Host " $(if($ok){'доступен'}else{'НЕДОСТУПЕН'})" `
                        -ForegroundColor $(if($ok) { 'Green' } else { 'Yellow' })
                }
            }

            # Создаём таблицу если нет
            if (-not (Test-FbTable $tblName)) {
                $colDefs = ($data.Keys | ForEach-Object {
                    "$(ConvertTo-SafeName $_) $(Get-FbColType $_)"
                }) -join ",`n    "

                Invoke-FbNonQuery -Sql @"
CREATE TABLE $tblName (
    ID        INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT PK_${tblName} PRIMARY KEY,
    $colDefs,
    CREATEDAT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"@
                Write-Host "Таблица '$tblName' создана." -ForegroundColor Green
            }

            # Авто-миграция: новые колонки
            foreach ($col in $data.Keys) {
                $colName = ConvertTo-SafeName $col
                if (-not (Test-FbColumn $tblName $colName)) {
                    Invoke-FbNonQuery -Sql "ALTER TABLE $tblName ADD $colName $(Get-FbColType $col)"
                    Write-Host "Добавлена колонка '$colName' → '$tblName'" -ForegroundColor Yellow
                }
            }

            # INSERT
            $colList   = ($data.Keys | ForEach-Object { ConvertTo-SafeName $_ }) -join ', '
            $paramList = ($data.Keys | ForEach-Object { "@$(ConvertTo-SafeName $_)" }) -join ', '
            $params    = @{}
            foreach ($k in $data.Keys) { $params[(ConvertTo-SafeName $k)] = $data[$k] }

            Invoke-FbNonQuery `
                -Sql    "INSERT INTO $tblName ($colList) VALUES ($paramList)" `
                -Params $params

            Write-Host "Записано: [$tblName] → $($Script:FB_PATH | Split-Path -Leaf)" `
                -ForegroundColor Green
        }

        return [PSCustomObject]@{
            Table   = ($tables | ForEach-Object { ConvertTo-SafeName $_.Key }) -join ', '
            DbPath  = $Script:FB_PATH
            Success = $true
        }
        } catch {
            Write-Host "Send-ToDb: $($_.Exception.Message)" -ForegroundColor Red
            throw
        } finally {
            Close-GperConnection
        }
    }
}

# ================================================================
#  Get-FromDb
# ================================================================
function Get-FromDb {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)] [string]$TableName,
        [string] $Filter = $null,
        [int]    $Limit  = 100
    )
    try {
        Open-GperConnection
        $tbl   = ConvertTo-SafeName $TableName
        $first = if ($Limit -gt 0) { "FIRST $Limit" } else { "" }
        $sql   = "SELECT $first * FROM $tbl"
        if (-not [string]::IsNullOrWhiteSpace($Filter)) { $sql += " WHERE $Filter" }
        $sql  += " ORDER BY ID DESC"
        return Invoke-FbQuery -Sql $sql
    } catch {
        Write-Host "Get-FromDb: $($_.Exception.Message)" -ForegroundColor Red
        throw
    } finally {
        Close-GperConnection
    }
}

# ================================================================
#  Show-DbTables
# ================================================================
function Show-DbTables {
    try {
        Open-GperConnection
        $tables = Invoke-FbQuery -Sql @"
SELECT TRIM(RDB`$RELATION_NAME) AS TABLE_NAME
FROM RDB`$RELATIONS
WHERE RDB`$SYSTEM_FLAG = 0
ORDER BY RDB`$RELATION_NAME
"@
        Write-Host "Таблицы в '$($Script:FB_PATH | Split-Path -Leaf)':" -ForegroundColor Cyan
        foreach ($t in $tables) {
            $cnt = Invoke-FbScalar -Sql "SELECT COUNT(*) FROM $($t.TABLE_NAME)"
            Write-Host "  $($t.TABLE_NAME)  ($cnt записей)" -ForegroundColor White
        }
        return $tables
    } catch {
        Write-Host "Shox`w-DbTables: $($_.Exception.Message)" -ForegroundColor Red
        throw
    } finally {
        Close-GperConnection
    }
}

# ================================================================
#  Show-DbConnections
# ================================================================
function Show-DbConnections {
    try {
        Open-GperConnection
         $sql = "SELECT  MON`$STATE, CASE WHEN MON`$STATE = 1 THEN 'Active' WHEN MON`$STATE = 0 THEN  'Idle' ELSE 'Unknown' END MON`$STATE_NAME, MON`$ATTACHMENT_ID, MON`$USER, MON`$REMOTE_ADDRESS, MON`$TIMESTAMP, MON`$REMOTE_ADDRESS,MON`$REMOTE_PROCESS, MON`$STAT_ID, MON`$REMOTE_HOST, MON`$REMOTE_OS_USER FROM MON`$ATTACHMENTS AS MON WHERE MON`$SYSTEM_FLAG = 0 ORDER BY MON`$TIMESTAMP DESC"
        # Write-Host "sql $sql" -ForegroundColor Cyan
        return Invoke-FbQuery -Sql $sql
    }
    catch {
         Write-Host "Show-DbConnections: $($_.Exception.Message)" -ForegroundColor Red
         throw
    }
    finally {
         Close-GperConnection
    }
}

# ================================================================
#  Remove-DbConnections
# ================================================================
function Remove-DbConnections {    
    param(        
        [Parameter(Mandatory = $false)] 
        [System.Collections.Generic.List[int]]$ID = @()
    )
     try {
            Open-GperConnection            
            if ($ID -and $ID.Count -gt 0) {
                $idList = $ID -join ','
                $FSQL = "DELETE FROM MON`$ATTACHMENTS WHERE MON`$ATTACHMENT_ID IN ($idList)"
                $FMSG = "Убиты подключения: ($idList)"
            } else {
                $FSQL = "DELETE FROM MON`$ATTACHMENTS" 
                $FMSG = "Убиты все подключения"
            }            
            Invoke-FbQuery -Sql $FSQL
            Write-Host $FMSG -ForegroundColor Yellow 
        
     } 
    catch {
        Write-Host "Remove-DbConnections: $($_.Exception.Message)" -ForegroundColor Red
        throw
    } 
    finally { 
        Close-GperConnection
    }
}


# ================================================================
#  Remove-FromDb
# ================================================================
function Remove-FromDb {
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High')]
    param(
        [Parameter(Mandatory)] [string]$TableName,
        [Parameter(Mandatory)] [string]$Filter
    )
    try {
        Open-GperConnection
        $tbl = ConvertTo-SafeName $TableName
        if ($PSCmdlet.ShouldProcess("$tbl WHERE $Filter", "DELETE")) {
            $sql = "DELETE FROM $tbl WHERE $Filter"
            $FMSG = @"
=== sql ===
$sql
=== sql ===
"@
            Write-Host "$FMSG" -ForegroundColor Cyan
            Set-Clipboard -Value $sql
            Invoke-FbNonQuery -Sql $sql
            Write-Host "Удалено из '$tbl' WHERE $Filter" -ForegroundColor Yellow
        }
    } catch {
        Write-Host "Remove-FromDb: $($_.Exception.Message)" -ForegroundColor Red
        throw
    } finally {
        Close-GperConnection
    }
}

# ================================================================
#  Send-NoteTo — быстрая заметка
# ================================================================
function Send-NoteTo {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)] [string]$Text,
        [string]$Tag    = '',
        [string]$Source = $env:COMPUTERNAME
    )
    try {
        Send-ToDb @{
            DbName = 'gperdb'
            Notes  = @{ Text = $Text; Tag = $Tag; Source = $Source }
        }
    } catch {
        Write-Host "Send-NoteTo: $($_.Exception.Message)" -ForegroundColor Red
        throw
    }
}

# ================================================================
#  Invoke-GperSql — произвольный SQL (для управления схемой)
# ================================================================
function Invoke-GperSql {
    [CmdletBinding()]
    param([Parameter(Mandatory)][string]$Sql)
    try {
        Open-GperConnection
        Invoke-FbNonQuery -Sql $Sql
    } catch {
        Write-Host "Invoke-GperSql: $($_.Exception.Message)" -ForegroundColor Red
        throw
    } finally {
        Close-GperConnection
    }
}

# ================================================================
#  GperDb-Update - скачать свежий gper-db.psm1 с сервера
# ================================================================
function Update-GperDb {
    <#
    .SYNOPSIS  Обновить gper-db.psm1 с сервера и перезагрузить в текущем сеансе.
    .EXAMPLE   GperDb-Update
    #>
    $url   = 'https://ps.gper.ru/db/gper-db.psm1'
    $local = $PSCommandPath  # путь к текущему файлу модуля

    Write-Host '--- Обновление gper-db.psm1...' -ForegroundColor Cyan

    try {
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        $bytes = (New-Object System.Net.WebClient).DownloadData($url)
    } catch {
        Write-Host "[X] Не удалось скачать: $_" -ForegroundColor Red
        return
    }

    # Добавляем BOM если нет
    if (-not ($bytes.Count -ge 3 -and $bytes[0] -eq 0xEF -and $bytes[1] -eq 0xBB -and $bytes[2] -eq 0xBF)) {
        $bytes = [byte[]](0xEF, 0xBB, 0xBF) + $bytes
    }

    # Бэкап текущей версии
    if ($local -and (Test-Path $local)) {
        $bak = $local -replace '\.psm1$', "_backup_$(Get-Date -Format 'yyyyMMdd_HHmmss').psm1"
        Copy-Item $local $bak -Force
        Write-Host "[bk] $(Split-Path $bak -Leaf)" -ForegroundColor DarkGray
    }

    $savePath = if ($local) { $local } else { Join-Path $env:TEMP 'gper-db.psm1' }
    [System.IO.File]::WriteAllBytes($savePath, $bytes)
    Write-Host "[OK] Сохранён: $savePath" -ForegroundColor Green

    try {
        Import-Module $savePath -Force -DisableNameChecking -ErrorAction Stop
        Write-Host '[OK] Модуль перезагружен.' -ForegroundColor Green
    } catch {
        Write-Host "[!] Не удалось перезагрузить: $_" -ForegroundColor Yellow
    }
}

# ================================================================
#  Set-GperDbConfig - настройка подключения без редактирования модуля
# ================================================================
function Set-GperDbConfig {
    [CmdletBinding()]
    param(
        [string]$FHost    = '',
        [int]   $FPort    = 0,
        [string]$FPath    = '',
        [string]$FUser    = '',
        [string]$FPass    = ''
    )
    if ($FHost) { $Script:FB_HOST = $FHost }
    if ($FPort) { $Script:FB_PORT = $FPort }
    if ($FPath) { $Script:FB_PATH = $FPath }
    if ($FUser) { $Script:FB_USER = $FUser }
    if ($FPass) { $Script:FB_PASS = $FPass }

    # Пересобрать строку подключения
    Build-FbConnStr

    # Сохранить в файл для персистентности (переживает Import-Module -Force)
    $cfgDir = Split-Path $Script:FB_CONFIG_FILE -Parent
    if (-not (Test-Path $cfgDir)) { New-Item $cfgDir -ItemType Directory -Force | Out-Null }
    [ordered]@{
        Host = $Script:FB_HOST
        Port = $Script:FB_PORT
        Path = $Script:FB_PATH
        User = $Script:FB_USER
        Pass = $Script:FB_PASS
    } | ConvertTo-Json | Set-Content $Script:FB_CONFIG_FILE -Encoding UTF8

    Write-Host "Конфиг сохранён: $($Script:FB_HOST)/$($Script:FB_PORT):$($Script:FB_PATH)" -ForegroundColor Cyan
    Write-Host "  Файл: $($Script:FB_CONFIG_FILE)" -ForegroundColor DarkGray
}

# ================================================================
#  ЭКСПОРТ
# ================================================================
Export-ModuleMember -Function `
    Send-ToDb, Get-FromDb, Show-DbTables, Remove-FromDb, `
    Send-NoteTo, Open-GperConnection, Close-GperConnection, Invoke-GperSql, `
    Show-DbConnections, Remove-DbConnections, Set-GperDbConfig, Update-GperDb
    # Invoke-FbNonQuery, Invoke-FbScalar, Invoke-FbQuery, `
    # Test-FbTable, Test-FbColumn, ConvertTo-SafeName, `
    # Get-FbColType, Test-GperDbAddr, Assert-FirebirdProvider


Set-Alias -Name SendToDb  -Value Send-ToDb
Set-Alias -Name GetFromDb -Value Get-FromDb
Export-ModuleMember -Alias SendToDb, GetFromDb