SQLTool.ps1 — source

#requires -Version 7.0
<#
.SYNOPSIS
    SQLTool helper for PowerShell 7+.

.DESCRIPTION
    Get-SQLTool returns a fresh cSQLTool instance. Classes are defined inside
    Get-SQLTool on purpose so each call refreshes types/behavior (module usage,
    many instances per day).

.EXAMPLE
    . "C:\Data\OneDrive - Geeks Forge\Chief of Staff\SQLTool_2026-0920d.ps1"
    $SQLTool = Get-SQLTool
    BulkInsert
#>

function global:Get-SQLTool {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $false)]
        [string]$Server = '.',

        [Parameter(Mandatory = $false)]
        [string]$Instance = '.',

        [Parameter(Mandatory = $false)]
        [string]$Database = '.',

        [Parameter(Mandatory = $false)]
        [switch]$Quiet,

        [Parameter(Mandatory = $false)]
        [switch]$VerboseMessages
    )

    #---------------------------------------------------------------------------------------------------------------#
    # Assemblies must load BEFORE nested class definitions (property types are resolved at define time)
    #---------------------------------------------------------------------------------------------------------------#
    $sqlClientOk = $false
    try {
        Add-Type -AssemblyName System.Data -ErrorAction Stop
        # Prefer Microsoft.Data.SqlClient on PS7 when present; else System.Data.SqlClient
        $bulkType = 'Microsoft.Data.SqlClient.SqlBulkCopy' -as [type]
        if (-not $bulkType) {
            try { Import-Module Microsoft.Data.SqlClient -ErrorAction Stop } catch { }
            $bulkType = 'Microsoft.Data.SqlClient.SqlBulkCopy' -as [type]
        }
        if (-not $bulkType) {
            try { Add-Type -AssemblyName System.Data.SqlClient -ErrorAction Stop } catch { }
            $bulkType = 'System.Data.SqlClient.SqlBulkCopy' -as [type]
        }
        if (-not ('System.Data.DataTable' -as [type])) {
            throw "System.Data.DataTable not available"
        }
        if (-not $bulkType) {
            throw "SqlBulkCopy not available (install Microsoft.Data.SqlClient or System.Data.SqlClient)"
        }
        $script:SQLTool_UseMds = [bool]('Microsoft.Data.SqlClient.SqlBulkCopy' -as [type])
        $sqlClientOk = $true
    }
    catch {
        Write-Host ("Get-SQLTool: assembly load failed: {0}" -f $_.Exception.Message) -ForegroundColor Red
        return $null
    }

    if (-not $sqlClientOk) { return $null }

    #---------------------------------------------------------------------------------------------------------------#
    # Nested types — redefined each Get-SQLTool call (intentional refresh)
    #---------------------------------------------------------------------------------------------------------------#
    class cCsvFileData {
        [string]$FullName = '.'
        [bool]$Found = $false
        [bool]$Loaded = $false
        [int]$Rows = -1
        [object]$cols = @()
        [string]$DirectoryName = '.'
        [string]$Name = '.'
        [long]$Length = -1
        [datetime]$CreationTime = [datetime]'1900-01-01'
        [datetime]$LastAccessTime = [datetime]'1900-01-01'
        [datetime]$LastWriteTime = [datetime]'1900-01-01'

        cCsvFileData() {}
        cCsvFileData([string]$FullName) { $this.Init($FullName) }

        [void] Init([string]$FullName) {
            $this.FullName = $FullName
            $this.Found = $false
            $this.Loaded = $false
            $this.Rows = -1
            $this.cols = @()

            if (-not (Test-Path -LiteralPath $this.FullName)) {
                $this.Found = $false
                return
            }

            $this.Found = $true
            try {
                $finfo = Get-Item -LiteralPath $this.FullName
                $this.DirectoryName = $finfo.DirectoryName
                $this.Name = $finfo.Name
                $this.CreationTime = $finfo.CreationTime
                $this.LastAccessTime = $finfo.LastAccessTime
                $this.LastWriteTime = $finfo.LastWriteTime
                $this.Length = [long]$finfo.Length

                $row = Import-Csv -LiteralPath $this.FullName | Select-Object -First 1
                if ($null -ne $row) {
                    $this.cols = @($row.PSObject.Properties.Name)
                }
                else {
                    $this.cols = @()
                }
            }
            catch {
                Write-Host ("Error reading CSV '{0}': {1}" -f $this.FullName, $_.Exception.Message) -ForegroundColor Red
                $this.cols = @()
            }
        }
    }

    class cSQLTool {
        [cCsvFileData]$CsvFile = [cCsvFileData]::new()
        [object]$CurrentCsv = $null
        [System.Collections.Generic.List[object]]$CsvFiles

        [string]$SQLServer = '.'
        [string]$DBName = '.'
        [string]$DBSchema = 'dbo'
        [string]$DBTable = '.'
        [string]$DBTableTemp = '.'
        [string]$DBTableName = '.'          # bare table name (no schema/brackets)
        [string]$UniqueKey = '.'            # PK column list from DBTable (comma-separated if composite)
        [string]$SQLInstance = '.'
        [string]$ConnStr = '.'
        [bool]$ConnStrSet = $false
        [object]$Conn = $null
        [bool]$ConnFound = $false
        [string]$ConnState = '.'

        [System.Data.DataTable]$dt1
        [System.Data.DataTable]$dt2
        [System.Data.DataTable]$dt3
        [System.Data.DataTable]$Tables

        [object]$bulk
        [bool]$BulkReady = $false
        [object]$cmd
        [bool]$CmdReady = $false

        [string]$Query1 = ''
        [string]$Query2 = ''
        [string]$Query3 = ''

        [bool]$TrustCert = $true
        [bool]$VerboseMessages = $false
        [bool]$Quiet = $false
        [bool]$UseMicrosoftDataSqlClient = $false

        cSQLTool() {
            $this.CsvFiles = [System.Collections.Generic.List[object]]::new()
            $this.InitDt()
        }

        cSQLTool([string]$Server, [string]$DBName) {
            $this.CsvFiles = [System.Collections.Generic.List[object]]::new()
            $this.Init($Server, $DBName)
            $this.InitDt()
        }

        cSQLTool([string]$Server, [string]$Instance, [string]$DBName) {
            $this.CsvFiles = [System.Collections.Generic.List[object]]::new()
            $this.Init($Server, $Instance, $DBName)
            $this.InitDt()
        }

        hidden [string] QuoteIdent([string]$Name) {
            if ([string]::IsNullOrWhiteSpace($Name)) { return '[]' }
            return ('[{0}]' -f $Name.Replace(']', ']]'))
        }

        hidden [string] QuoteTwoPart([string]$Schema, [string]$Name) {
            return ('{0}.{1}' -f $this.QuoteIdent($Schema), $this.QuoteIdent($Name))
        }

        hidden [object] NewSqlConnection([string]$ConnectionString) {
            # Use New-Object / type names so parse does not require MDS assemblies loaded
            if ($this.UseMicrosoftDataSqlClient) {
                return New-Object -TypeName 'Microsoft.Data.SqlClient.SqlConnection' -ArgumentList $ConnectionString
            }
            return New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $ConnectionString
        }

        hidden [object] NewSqlBulkCopy([object]$Connection) {
            if ($this.UseMicrosoftDataSqlClient) {
                return New-Object -TypeName 'Microsoft.Data.SqlClient.SqlBulkCopy' -ArgumentList $Connection
            }
            return New-Object -TypeName 'System.Data.SqlClient.SqlBulkCopy' -ArgumentList $Connection
        }

        hidden [object] NewSqlDataAdapter([object]$Command) {
            if ($this.UseMicrosoftDataSqlClient) {
                return New-Object -TypeName 'Microsoft.Data.SqlClient.SqlDataAdapter' -ArgumentList $Command
            }
            return New-Object -TypeName 'System.Data.SqlClient.SqlDataAdapter' -ArgumentList $Command
        }

        [void] AddCsv([string]$FullName) {
            try {
                $add = [cCsvFileData]::new($FullName)
                [void]$this.CsvFiles.Add($add)
                if (-not $add.Found) {
                    $this.WriteErr("CSV not found: $FullName")
                }
            }
            catch {
                $this.WriteErr("Error adding CSV '$FullName': $($_.Exception.Message)")
            }
        }

        [void] BuildConnStr() {
            $this.ShowMessageC('Cyan', 'BuildConnStr()')
            $this.ConnStr = '.'
            $this.ConnStrSet = $false

            if ($this.SQLServer -eq '.') { $this.WriteErr('Must set SQL Server first'); return }
            if ($this.DBName -eq '.') { $this.WriteErr('Must set DB name first'); return }

            if ($this.SQLInstance -ne '.') {
                $useServer = '{0}\{1}' -f $this.SQLServer, $this.SQLInstance
            }
            else {
                $useServer = $this.SQLServer
            }

            try {
                $this.ConnStr = 'Server={0};Database={1};Integrated Security=True' -f $useServer, $this.DBName
                if ($this.TrustCert) {
                    $this.ConnStr += ';TrustServerCertificate=True'
                }
                $this.ConnStrSet = ($this.ConnStr.Length -gt 10)
            }
            catch {
                $this.ConnStrSet = $false
                $this.WriteErr("BuildConnStr failed: $($_.Exception.Message)")
            }
        }

        [void] CloseConnection() {
            $this.ShowMessageC('Cyan', 'CloseConnection()')
            $this.RefreshConnState()

            if ($null -eq $this.Conn) {
                $this.WriteHostMsg('Connection not found')
                $this.SetConnFound()
                return
            }

            if ([string]$this.Conn.State -eq 'Open') {
                try {
                    $this.Conn.Close()
                    $this.RefreshConnState()
                }
                catch {
                    $this.WriteErr("Error closing connection: $($_.Exception.Message)")
                }
            }
            else {
                $this.WriteHostMsg('Connection not open')
            }
            $this.SetConnFound()
        }

        [void] DisposeConnection() {
            $this.ShowMessageC('Cyan', 'DisposeConnection()')

            if ($null -eq $this.Conn) {
                $this.WriteHostMsg('Connection not found, nothing to do')
                $this.ConnFound = $false
                $this.ConnState = 'NoConnection'
                return
            }

            if ([string]$this.Conn.State -eq 'Open') {
                $this.CloseConnection()
            }

            try {
                $this.Conn.Dispose()
            }
            catch {
                $this.WriteErr("Error disposing connection: $($_.Exception.Message)")
            }
            finally {
                $this.Conn = $null
                $this.cmd = $null
                $this.CmdReady = $false
                if ($null -ne $this.bulk) {
                    try { $this.bulk.Close() } catch { }
                    try { $this.bulk.Dispose() } catch { }
                    $this.bulk = $null
                }
                $this.BulkReady = $false
                $this.ConnState = 'NoConnection'
                $this.ConnFound = $false
            }

            $this.WriteHostMsg('Connection was disposed')
            $this.SetConnFound()
        }

        [void] GetConnectionState() {
            $this.ShowMessageC('Cyan', 'GetConnectionState()')
            $this.RefreshConnState()
            if ($null -eq $this.Conn) {
                $this.WriteHostMsg('Connector does not exist')
            }
            $this.SetConnFound()
        }

        [void] Init([string]$Server, [string]$DBName) {
            $this.ShowMessageC('Cyan', 'Init(2)')
            $this.SQLServer = $Server
            $this.SQLInstance = '.'
            $this.DBName = $DBName
            $this.SetConnFound()
        }

        [void] Init([string]$Server, [string]$Instance, [string]$DBName) {
            $this.ShowMessageC('Cyan', 'Init(3)')
            $this.SQLServer = $Server
            $this.SQLInstance = $Instance
            $this.DBName = $DBName
            $this.SetConnFound()
        }

        [void] InitBulk() {
            $this.ShowMessageC('Cyan', 'InitBulk()')
            $this.BulkReady = $false

            if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                $this.WriteErr('InitBulk: connection must be open')
                return
            }
            if ($null -eq $this.CurrentCsv -or $null -eq $this.CurrentCsv.cols -or @($this.CurrentCsv.cols).Count -eq 0) {
                $this.WriteErr('InitBulk: CurrentCsv has no columns')
                return
            }
            if ($this.DBTableTemp -eq '.' -or [string]::IsNullOrWhiteSpace($this.DBTableTemp)) {
                $this.WriteErr('InitBulk: DBTableTemp not set (call SetTable first)')
                return
            }

            try {
                if ($null -ne $this.bulk) {
                    try { $this.bulk.Close() } catch { }
                    try { $this.bulk.Dispose() } catch { }
                    $this.bulk = $null
                }

                $this.bulk = $this.NewSqlBulkCopy($this.Conn)
                $this.bulk.DestinationTableName = $this.DBTableTemp
                $this.bulk.BatchSize = 5000
                $this.bulk.BulkCopyTimeout = 600
                $this.bulk.ColumnMappings.Clear()

                foreach ($c in @($this.CurrentCsv.cols)) {
                    [void]$this.bulk.ColumnMappings.Add($c, $c)
                }
                $this.BulkReady = $true
            }
            catch {
                $this.BulkReady = $false
                $this.WriteErr("InitBulk failed: $($_.Exception.Message)")
            }
        }

        [void] InitDt() {
            $this.ShowMessageC('Cyan', 'InitDt()')
            try { $this.dt1 = [System.Data.DataTable]::new() } catch { $this.WriteErr("Error creating dt1: $($_.Exception.Message)") }
            try { $this.dt2 = [System.Data.DataTable]::new() } catch { $this.WriteErr("Error creating dt2: $($_.Exception.Message)") }
            try { $this.dt3 = [System.Data.DataTable]::new() } catch { $this.WriteErr("Error creating dt3: $($_.Exception.Message)") }
        }

        [void] LoadCurrentCsv() {
            $this.LoadCurrentCsv($null)
        }

        [void] LoadCurrentCsv([string]$UniqueKey) {
            $this.ShowMessageC('Cyan', 'LoadCurrentCsv()')

            if ($null -eq $this.CurrentCsv) {
                $this.WriteErr('LoadCurrentCsv: CurrentCsv is not set')
                return
            }
            if (-not $this.CurrentCsv.Found) {
                $this.WriteErr("LoadCurrentCsv: file not found: $($this.CurrentCsv.FullName)")
                return
            }
            if ($null -eq $this.CurrentCsv.cols -or @($this.CurrentCsv.cols).Count -eq 0) {
                $this.WriteErr('LoadCurrentCsv: no columns discovered from CSV header')
                return
            }

            try {
                if ($null -eq $this.dt1) {
                    $this.dt1 = [System.Data.DataTable]::new()
                }
                else {
                    $this.dt1.Clear()
                    $this.dt1.Columns.Clear()
                    $this.dt1.Constraints.Clear()
                }

                foreach ($c in @($this.CurrentCsv.cols)) {
                    [void]$this.dt1.Columns.Add($c)
                }

                $rows = Import-Csv -LiteralPath $this.CurrentCsv.FullName
                if (-not [string]::IsNullOrWhiteSpace($UniqueKey)) {
                    if (@($this.CurrentCsv.cols) -notcontains $UniqueKey) {
                        $this.WriteErr("LoadCurrentCsv: UniqueKey '$UniqueKey' is not a CSV column")
                        return
                    }
                    $rows = $rows | Sort-Object -Property $UniqueKey -Unique
                }

                foreach ($item in $rows) {
                    $row = $this.dt1.NewRow()
                    foreach ($c in @($this.CurrentCsv.cols)) {
                        $v = $item.$c
                        $row[$c] = if ([string]::IsNullOrWhiteSpace([string]$v)) { [System.DBNull]::Value } else { $v }
                    }
                    [void]$this.dt1.Rows.Add($row)
                }

                $this.CurrentCsv.Loaded = $true
                $this.CurrentCsv.Rows = $this.dt1.Rows.Count
                $this.ShowMessageC('Cyan', 'LoadCurrentCsv()', ("rows={0}" -f $this.CurrentCsv.Rows))
            }
            catch {
                $this.CurrentCsv.Loaded = $false
                $this.WriteErr("LoadCurrentCsv failed: $($_.Exception.Message)")
            }
        }

        [void] LoadTableList() {
            $this.ShowMessageC('Cyan', 'LoadTableList()')
            $this.SetCmd()
            if (-not $this.CmdReady) { return }

            if ($this.DBName -eq '.' -or [string]::IsNullOrWhiteSpace($this.DBName)) {
                $this.WriteErr('LoadTableList: DBName is not set')
                return
            }

            try {
                $this.cmd.CommandTimeout = 600
                $db = $this.QuoteIdent($this.DBName)
                $this.cmd.CommandText = "
                USE $db;
                SELECT
                    s.name AS SchemaName,
                    t.name AS TableName,
                    ISNULL(STUFF((
                        SELECT ',' + c.name
                        FROM sys.index_columns AS ic
                        INNER JOIN sys.columns AS c
                            ON  c.object_id = ic.object_id
                            AND c.column_id = ic.column_id
                        WHERE ic.object_id = i.object_id
                          AND ic.index_id  = i.index_id
                        ORDER BY ic.key_ordinal
                        FOR XML PATH(''), TYPE
                    ).value('.', 'nvarchar(max)'), 1, 1, ''), '') AS [Primary]
                FROM sys.tables AS t
                INNER JOIN sys.schemas AS s
                    ON t.schema_id = s.schema_id
                LEFT JOIN sys.indexes AS i
                    ON  i.object_id = t.object_id
                    AND i.is_primary_key = 1
                ORDER BY s.name, t.name;
                "
                $this.Tables = [System.Data.DataTable]::new()
                $adapter = $this.NewSqlDataAdapter($this.cmd)
                [void]$adapter.Fill($this.Tables)
            }
            catch {
                $this.WriteErr("LoadTableList failed: $($_.Exception.Message)")
            }
        }

        [void] OpenConnection() {
            $this.ShowMessageC('Cyan', 'OpenConnection()')

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.' -or [string]::IsNullOrWhiteSpace($this.ConnStr)) {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.WriteErr('You need to set the connection string first')
                $this.SetConnFound()
                return
            }

            try {
                $needNew = ($null -eq $this.Conn)
                if (-not $needNew) {
                    if ($this.Conn.ConnectionString -ne $this.ConnStr) {
                        $this.DisposeConnection()
                        $needNew = $true
                    }
                }

                if ($needNew) {
                    $this.Conn = $this.NewSqlConnection($this.ConnStr)
                }

                if ([string]$this.Conn.State -ne 'Open') {
                    $this.Conn.Open()
                }
            }
            catch {
                $this.WriteErr("Error opening connection: $($_.Exception.Message)")
            }

            $this.RefreshConnState()
            $this.SetConnFound()
        }

        [void] ResetConnection() {
            $this.ShowMessageC('Cyan', 'ResetConnection()')
            $savedStr = $this.ConnStr
            $savedSet = $this.ConnStrSet
            $this.DisposeConnection()
            $this.ConnStr = $savedStr
            $this.ConnStrSet = $savedSet
            if ($this.ConnStrSet) {
                $this.OpenConnection()
            }
        }

        [void] SetCmd() {
            $this.ShowMessageC('Cyan', 'SetCmd()')
            $this.CmdReady = $false
            $this.RefreshConnState()

            if (-not $this.ConnStrSet) {
                $this.WriteErr('The connection string must be set first')
                return
            }
            if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                $this.WriteErr('You must connect to SQL first')
                return
            }

            try {
                $this.cmd = $this.Conn.CreateCommand()
                $this.CmdReady = $true
            }
            catch {
                $this.CmdReady = $false
                $this.WriteErr("Error creating command: $($_.Exception.Message)")
            }
        }

        [void] SetConnFound() {
            $this.ShowMessageC('Cyan', 'SetConnFound()')
            if ($null -eq $this.Conn) {
                $this.ConnFound = $false
                if ($this.ConnState -eq '.' -or [string]::IsNullOrWhiteSpace($this.ConnState)) {
                    $this.ConnState = 'NoConnection'
                }
            }
            else {
                $this.ConnFound = $true
                try { $this.ConnState = [string]$this.Conn.State }
                catch { $this.ConnState = 'Error' }
            }
        }

        [void] RefreshConnState() {
            if ($null -eq $this.Conn) {
                $this.ConnState = 'NoConnection'
                return
            }
            try { $this.ConnState = [string]$this.Conn.State }
            catch { $this.ConnState = 'Error' }
        }

        [void] SetTable([string]$Name) {
            $this.ShowMessageC('Cyan', 'SetTable()', $Name)
            $this.DBTableName = $Name
            $this.DBTable = $this.QuoteTwoPart($this.DBSchema, $Name)
            $this.DBTableTemp = $this.QuoteTwoPart($this.DBSchema, ($Name + '_Temp'))
            $this.UniqueKey = '.'
        }

        [void] SetSQLServer([string]$Value) {
            $this.ShowMessageC('Cyan', 'SetSQLServer()', $Value)
            $this.SQLServer = $Value
        }

        [void] SetSQLInstance([string]$Value) {
            $this.ShowMessageC('Cyan', 'SetSQLInstance()', $Value)
            $this.SQLInstance = $Value
        }

        [void] SetDBName([string]$Value) {
            $this.ShowMessageC('Cyan', 'SetDBName()', $Value)
            $this.DBName = $Value
        }

        [void] SelectCsv([string]$FullName) {
            $match = @($this.CsvFiles | Where-Object { $_.FullName -eq $FullName })
            if ($match.Count -eq 0) {
                $this.CurrentCsv = $null
                $this.WriteErr("SelectCsv: not in CsvFiles: $FullName")
                return
            }
            $this.CurrentCsv = $match[0]
        }


        [bool] SqlTableExists([string]$QuotedTwoPartName) {
            $this.ShowMessageC('Cyan', 'SqlTableExists()', $QuotedTwoPartName)
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { return $false }
            try {
                $safe = $QuotedTwoPartName.Replace("'", "''")
                $this.cmd.CommandText = "
                SELECT CASE WHEN OBJECT_ID(N'$safe', 'U') IS NULL THEN 0 ELSE 1 END;
                "
                $val = $this.cmd.ExecuteScalar()
                return ([int]$val -eq 1)
            }
            catch {
                $this.WriteErr("SqlTableExists failed for $QuotedTwoPartName : $($_.Exception.Message)")
                return $false
            }
        }

        [string[]] GetPrimaryKeyColumns([string]$Schema, [string]$Table) {
            $this.ShowMessageC('Cyan', 'GetPrimaryKeyColumns()', ("{0}.{1}" -f $Schema, $Table))
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { return @() }

            $schemaQ = $Schema.Replace("'", "''")
            $tableQ = $Table.Replace("'", "''")
            $this.cmd.CommandText = "
            SELECT c.name
            FROM sys.tables AS t
            INNER JOIN sys.schemas AS s
                ON t.schema_id = s.schema_id
            INNER JOIN sys.indexes AS i
                ON  i.object_id = t.object_id
                AND i.is_primary_key = 1
            INNER JOIN sys.index_columns AS ic
                ON  ic.object_id = i.object_id
                AND ic.index_id  = i.index_id
            INNER JOIN sys.columns AS c
                ON  c.object_id = ic.object_id
                AND c.column_id = ic.column_id
            WHERE s.name = N'$schemaQ'
              AND t.name = N'$tableQ'
            ORDER BY ic.key_ordinal;
            "
            $dt = [System.Data.DataTable]::new()
            $adapter = $this.NewSqlDataAdapter($this.cmd)
            [void]$adapter.Fill($dt)
            return @($dt.Rows | ForEach-Object { [string]$_['name'] })
        }

        [string[]] GetTableColumns([string]$Schema, [string]$Table) {
            $this.ShowMessageC('Cyan', 'GetTableColumns()', ("{0}.{1}" -f $Schema, $Table))
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { return @() }

            $schemaQ = $Schema.Replace("'", "''")
            $tableQ = $Table.Replace("'", "''")
            # Exclude computed and identity (identity usually PK; MediaFiles uses business key)
            $this.cmd.CommandText = "
            SELECT c.name
            FROM sys.tables AS t
            INNER JOIN sys.schemas AS s
                ON t.schema_id = s.schema_id
            INNER JOIN sys.columns AS c
                ON c.object_id = t.object_id
            WHERE s.name = N'$schemaQ'
              AND t.name = N'$tableQ'
              AND c.is_computed = 0
              AND c.is_identity = 0
            ORDER BY c.column_id;
            "
            $dt = [System.Data.DataTable]::new()
            $adapter = $this.NewSqlDataAdapter($this.cmd)
            [void]$adapter.Fill($dt)
            return @($dt.Rows | ForEach-Object { [string]$_['name'] })
        }

        [string] BuildMergeSql([string[]]$Columns, [string[]]$KeyColumns) {
            $cols = @($Columns | Where-Object { -not [string]::IsNullOrWhiteSpace($_) })
            $keys = @($KeyColumns | Where-Object { -not [string]::IsNullOrWhiteSpace($_) })
            if ($cols.Count -eq 0) { throw 'BuildMergeSql: no columns' }
            if ($keys.Count -eq 0) { throw 'BuildMergeSql: no key columns' }

            foreach ($k in $keys) {
                if ($cols -notcontains $k) {
                    throw "BuildMergeSql: key column '$k' is not in the column list"
                }
            }

            $updateCols = @($cols | Where-Object { $keys -notcontains $_ })
            $onParts = @($keys | ForEach-Object { "t.$($this.QuoteIdent($_)) = s.$($this.QuoteIdent($_))" })
            $onClause = ($onParts -join " AND`n            ")

            if ($updateCols.Count -gt 0) {
                $setParts = @($updateCols | ForEach-Object {
                    "            $($this.QuoteIdent($_)) = s.$($this.QuoteIdent($_))"
                })
                $setClause = ($setParts -join ",`n")
                $whenMatched = "
        WHEN MATCHED THEN UPDATE SET
$setClause"
            }
            else {
                $whenMatched = ''
            }

            $insertList = ($cols | ForEach-Object { $this.QuoteIdent($_) }) -join ', '
            $valuesList = ($cols | ForEach-Object { "s.$($this.QuoteIdent($_))" }) -join ', '

            return "
        MERGE $($this.DBTable) AS t
        USING $($this.DBTableTemp) AS s
            ON $onClause$whenMatched
        WHEN NOT MATCHED BY TARGET THEN INSERT
            ($insertList)
        VALUES
            ($valuesList);
        "
        }

        [void] BulkInsertCsv([string]$FullName, [string]$TableName) {
            $this.ShowMessageC('Cyan', 'BulkInsertCsv()', ("{0} -> {1}" -f $FullName, $TableName))

            if ([string]::IsNullOrWhiteSpace($FullName)) {
                $this.WriteErr('BulkInsertCsv: FullName is required'); return
            }
            if ([string]::IsNullOrWhiteSpace($TableName)) {
                $this.WriteErr('BulkInsertCsv: TableName is required'); return
            }

            $this.SetTable($TableName)
            $this.AddCsv($FullName)
            $this.SelectCsv($FullName)
            if ($null -eq $this.CurrentCsv -or -not $this.CurrentCsv.Found) {
                $this.WriteErr("BulkInsertCsv: CSV not available: $FullName"); return
            }

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet) {
                $this.WriteErr('BulkInsertCsv: connection string not set'); return
            }

            $openedHere = $false
            try {
                if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                    $this.OpenConnection()
                    $openedHere = $true
                }
                $this.SetCmd()
                if (-not $this.CmdReady) {
                    $this.WriteErr('BulkInsertCsv: command not ready'); return
                }

                # Destination main table must exist (MERGE needs it); staging is ensured below
                if (-not $this.SqlTableExists($this.DBTable)) {
                    $this.WriteErr("BulkInsertCsv: destination table does not exist: $($this.DBTable)")
                    return
                }

                $csvCols = @($this.CurrentCsv.cols)
                $this.EnsureTempTableForCsv($csvCols)

                if (-not $this.SqlTableExists($this.DBTableTemp)) {
                    $this.WriteErr("BulkInsertCsv: temp table still missing after ensure: $($this.DBTableTemp)")
                    return
                }

                # UniqueKey from destination table primary key
                $keyCols = @($this.GetPrimaryKeyColumns($this.DBSchema, $this.DBTableName))
                if ($keyCols.Count -eq 0) {
                    $this.WriteErr("BulkInsertCsv: no primary key found on $($this.DBTable)")
                    return
                }
                $this.UniqueKey = ($keyCols -join ',')

                $tableCols = @($this.GetTableColumns($this.DBSchema, $this.DBTableName))
                if ($tableCols.Count -eq 0) {
                    $this.WriteErr("BulkInsertCsv: no usable columns on $($this.DBTable)")
                    return
                }

                $mergeCols = @($tableCols | Where-Object { $csvCols -contains $_ })
                if ($mergeCols.Count -eq 0) {
                    $this.WriteErr('BulkInsertCsv: no overlapping columns between CSV and destination table')
                    return
                }

                $missingKeys = @($keyCols | Where-Object { $mergeCols -notcontains $_ })
                if ($missingKeys.Count -gt 0) {
                    $this.WriteErr("BulkInsertCsv: PK column(s) missing from CSV: $($missingKeys -join ', ')")
                    return
                }

                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = "TRUNCATE TABLE $($this.DBTableTemp)"
                [void]$this.cmd.ExecuteNonQuery()

                # Sort-Object -Unique supports a single property; use first PK column when composite
                $uniqueForLoad = $keyCols[0]
                $this.LoadCurrentCsv($uniqueForLoad)
                if (-not $this.CurrentCsv.Loaded) {
                    $this.WriteErr('BulkInsertCsv: CSV load failed'); return
                }

                # Map bulk columns to the merge column set (CSV may have extras)
                $this.CurrentCsv.cols = $mergeCols
                $this.InitBulk()
                if (-not $this.BulkReady) {
                    $this.WriteErr('BulkInsertCsv: bulk copy not ready'); return
                }

                $this.bulk.WriteToServer($this.dt1)
                $this.bulk.Close()

                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = $this.BuildMergeSql($mergeCols, $keyCols)
                [void]$this.cmd.ExecuteNonQuery()
                $this.ShowMessageC('Cyan', 'BulkInsertCsv()', 'completed')
            }
            catch {
                $this.WriteErr("BulkInsertCsv failed: $($_.Exception.Message)")
            }
            finally {
                # Leave connection open if caller already had it open
                if ($openedHere) {
                    $this.CloseConnection()
                }
            }
        }


        [string[]] GetTempTableColumns([string]$Schema, [string]$TempTableBareName) {
            # All non-computed columns (include identity if any)
            $this.ShowMessageC('Cyan', 'GetTempTableColumns()', ("{0}.{1}" -f $Schema, $TempTableBareName))
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { return @() }

            $schemaQ = $Schema.Replace("'", "''")
            $tableQ = $TempTableBareName.Replace("'", "''")
            $this.cmd.CommandText = "
            SELECT c.name
            FROM sys.tables AS t
            INNER JOIN sys.schemas AS s
                ON t.schema_id = s.schema_id
            INNER JOIN sys.columns AS c
                ON c.object_id = t.object_id
            WHERE s.name = N'$schemaQ'
              AND t.name = N'$tableQ'
              AND c.is_computed = 0
            ORDER BY c.column_id;
            "
            $dt = [System.Data.DataTable]::new()
            $adapter = $this.NewSqlDataAdapter($this.cmd)
            [void]$adapter.Fill($dt)
            return @($dt.Rows | ForEach-Object { [string]$_['name'] })
        }

        [bool] CsvColumnsMatchTable([string[]]$CsvColumns, [string[]]$TableColumns) {
            $a = @($CsvColumns | ForEach-Object { $_.ToLowerInvariant() } | Sort-Object -Unique)
            $b = @($TableColumns | ForEach-Object { $_.ToLowerInvariant() } | Sort-Object -Unique)
            if ($a.Count -ne $b.Count) { return $false }
            for ($i = 0; $i -lt $a.Count; $i++) {
                if ($a[$i] -ne $b[$i]) { return $false }
            }
            return $true
        }

        [void] DropTableIfExists([string]$QuotedTwoPartName) {
            $this.ShowMessageC('Cyan', 'DropTableIfExists()', $QuotedTwoPartName)
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { return }
            $safe = $QuotedTwoPartName.Replace("'", "''")
            $this.cmd.CommandText = "
            IF OBJECT_ID(N'$safe', 'U') IS NOT NULL
                DROP TABLE $QuotedTwoPartName;
            "
            [void]$this.cmd.ExecuteNonQuery()
        }

        [void] CreateTempTableForCsv([string[]]$CsvColumns) {
            $this.ShowMessageC('Cyan', 'CreateTempTableForCsv()')
            if (-not $this.CmdReady) { $this.SetCmd() }
            if (-not $this.CmdReady) { throw 'CreateTempTableForCsv: command not ready' }
            if ($null -eq $CsvColumns -or @($CsvColumns).Count -eq 0) {
                throw 'CreateTempTableForCsv: no CSV columns'
            }

            $cols = @($CsvColumns)
            $created = $false

            # Prefer typed clone from main table when every CSV column exists there
            if ($this.SqlTableExists($this.DBTable)) {
                $mainCols = @($this.GetTableColumns($this.DBSchema, $this.DBTableName))
                $missingOnMain = @($cols | Where-Object { $mainCols -notcontains $_ })
                if ($missingOnMain.Count -eq 0) {
                    $selectList = ($cols | ForEach-Object { $this.QuoteIdent($_) }) -join ', '
                    # SELECT INTO creates the staging table with correct types from the main table
                    $this.cmd.CommandText = "
                    SELECT TOP (0) $selectList
                    INTO $($this.DBTableTemp)
                    FROM $($this.DBTable);
                    "
                    [void]$this.cmd.ExecuteNonQuery()
                    $created = $true
                    $this.ShowMessageC('Cyan', 'CreateTempTableForCsv()', 'created via SELECT TOP 0 from main')
                }
            }

            if (-not $created) {
                # CSV-driven staging: nvarchar(max) is correct for disposable load tables
                $defs = @($cols | ForEach-Object {
                    "            $($this.QuoteIdent($_)) nvarchar(max) NULL"
                })
                $this.cmd.CommandText = "
                CREATE TABLE $($this.DBTableTemp) (
$($defs -join ",`n")
                );
                "
                [void]$this.cmd.ExecuteNonQuery()
                $this.ShowMessageC('Cyan', 'CreateTempTableForCsv()', 'created as nvarchar(max) from CSV headers')
            }
        }

        # Staging tables are disposable: when CSV shape drifts, DROP + recreate beats ALTER.
        [void] EnsureTempTableForCsv([string[]]$CsvColumns) {
            $this.ShowMessageC('Cyan', 'EnsureTempTableForCsv()')
            $tempBare = $this.DBTableName + '_Temp'

            if ($this.SqlTableExists($this.DBTableTemp)) {
                $existing = @($this.GetTempTableColumns($this.DBSchema, $tempBare))
                if ($this.CsvColumnsMatchTable($CsvColumns, $existing)) {
                    $this.ShowMessageC('Cyan', 'EnsureTempTableForCsv()', 'schema matches CSV')
                    return
                }
                $this.WriteHostMsg("Temp table $($this.DBTableTemp) columns differ from CSV — dropping and recreating")
                $this.DropTableIfExists($this.DBTableTemp)
            }

            $this.CreateTempTableForCsv($CsvColumns)
        }

        [void] BulkInsertCsvTempOnly([string]$FullName, [string]$TableName) {
            $this.ShowMessageC('Cyan', 'BulkInsertCsvTempOnly()', ("{0} -> {1}_Temp" -f $FullName, $TableName))

            if ([string]::IsNullOrWhiteSpace($FullName)) {
                $this.WriteErr('BulkInsertCsvTempOnly: FullName is required'); return
            }
            if ([string]::IsNullOrWhiteSpace($TableName)) {
                $this.WriteErr('BulkInsertCsvTempOnly: TableName is required'); return
            }

            $this.SetTable($TableName)
            $this.AddCsv($FullName)
            $this.SelectCsv($FullName)
            if ($null -eq $this.CurrentCsv -or -not $this.CurrentCsv.Found) {
                $this.WriteErr("BulkInsertCsvTempOnly: CSV not available: $FullName"); return
            }
            if ($null -eq $this.CurrentCsv.cols -or @($this.CurrentCsv.cols).Count -eq 0) {
                $this.WriteErr('BulkInsertCsvTempOnly: CSV has no columns'); return
            }

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet) {
                $this.WriteErr('BulkInsertCsvTempOnly: connection string not set'); return
            }

            $openedHere = $false
            try {
                if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                    $this.OpenConnection()
                    $openedHere = $true
                }
                $this.SetCmd()
                if (-not $this.CmdReady) {
                    $this.WriteErr('BulkInsertCsvTempOnly: command not ready'); return
                }

                $csvCols = @($this.CurrentCsv.cols)
                $this.EnsureTempTableForCsv($csvCols)

                if (-not $this.SqlTableExists($this.DBTableTemp)) {
                    $this.WriteErr("BulkInsertCsvTempOnly: temp table still missing after ensure: $($this.DBTableTemp)")
                    return
                }

                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = "TRUNCATE TABLE $($this.DBTableTemp)"
                [void]$this.cmd.ExecuteNonQuery()

                # No UniqueKey / no MERGE — load all CSV rows as-is
                $this.LoadCurrentCsv($null)
                if (-not $this.CurrentCsv.Loaded) {
                    $this.WriteErr('BulkInsertCsvTempOnly: CSV load failed'); return
                }

                $this.InitBulk()
                if (-not $this.BulkReady) {
                    $this.WriteErr('BulkInsertCsvTempOnly: bulk copy not ready'); return
                }

                $this.bulk.WriteToServer($this.dt1)
                $this.bulk.Close()
                $this.ShowMessageC('Cyan', 'BulkInsertCsvTempOnly()', ("completed rows={0}" -f $this.CurrentCsv.Rows))
            }
            catch {
                $this.WriteErr("BulkInsertCsvTempOnly failed: $($_.Exception.Message)")
            }
            finally {
                if ($openedHere) {
                    $this.CloseConnection()
                }
            }
        }


        #-----------------------------------------------------------------------------------------------------------#
        # Query -> DataTable helpers (Query1/2/3 -> dt1/2/3)
        #-----------------------------------------------------------------------------------------------------------#
        hidden [void] EnsureDtSlot([int]$Slot) {
            switch ($Slot) {
                1 {
                    if ($null -eq $this.dt1) { $this.dt1 = [System.Data.DataTable]::new() }
                    else {
                        $this.dt1.Clear()
                        $this.dt1.Columns.Clear()
                        $this.dt1.Constraints.Clear()
                    }
                }
                2 {
                    if ($null -eq $this.dt2) { $this.dt2 = [System.Data.DataTable]::new() }
                    else {
                        $this.dt2.Clear()
                        $this.dt2.Columns.Clear()
                        $this.dt2.Constraints.Clear()
                    }
                }
                3 {
                    if ($null -eq $this.dt3) { $this.dt3 = [System.Data.DataTable]::new() }
                    else {
                        $this.dt3.Clear()
                        $this.dt3.Columns.Clear()
                        $this.dt3.Constraints.Clear()
                    }
                }
                default { throw "EnsureDtSlot: Slot must be 1, 2, or 3 (got $Slot)" }
            }
        }

        hidden [System.Data.DataTable] GetDtSlot([int]$Slot) {
            switch ($Slot) {
                1 { return $this.dt1 }
                2 { return $this.dt2 }
                3 { return $this.dt3 }
                default { throw "GetDtSlot: Slot must be 1, 2, or 3 (got $Slot)" }
            }
            return $null
        }

        [void] SetQuery([int]$Slot, [string]$Sql) {
            switch ($Slot) {
                1 { $this.Query1 = $Sql }
                2 { $this.Query2 = $Sql }
                3 { $this.Query3 = $Sql }
                default {
                    $this.WriteErr("SetQuery: Slot must be 1, 2, or 3 (got $Slot)")
                    return
                }
            }
        }

        [string] GetQuery([int]$Slot) {
            switch ($Slot) {
                1 { return [string]$this.Query1 }
                2 { return [string]$this.Query2 }
                3 { return [string]$this.Query3 }
                default {
                    $this.WriteErr("GetQuery: Slot must be 1, 2, or 3 (got $Slot)")
                    return ''
                }
            }
            return ''
        }

        # Runs QueryN into dtN. Slot is 1, 2, or 3.
        [void] FillDt([int]$Slot) {
            $this.ShowMessageC('Cyan', 'FillDt()', ("slot={0}" -f $Slot))

            if ($Slot -lt 1 -or $Slot -gt 3) {
                $this.WriteErr("FillDt: Slot must be 1, 2, or 3 (got $Slot)"); return
            }

            $sql = $this.GetQuery($Slot)
            if ([string]::IsNullOrWhiteSpace($sql)) {
                $this.WriteErr(("FillDt: Query{0} is empty — set it or call ExecuteQuery(...)" -f $Slot))
                return
            }

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet) {
                $this.WriteErr('FillDt: connection string not set'); return
            }

            $openedHere = $false
            try {
                if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                    $this.OpenConnection()
                    $openedHere = $true
                }
                $this.SetCmd()
                if (-not $this.CmdReady) {
                    $this.WriteErr('FillDt: command not ready'); return
                }

                $this.EnsureDtSlot($Slot)
                $target = $this.GetDtSlot($Slot)
                if ($null -eq $target) {
                    $this.WriteErr(("FillDt: dt{0} is null" -f $Slot)); return
                }

                try { $this.cmd.CommandType = [System.Data.CommandType]::Text } catch { }
                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = $sql

                $adapter = $this.NewSqlDataAdapter($this.cmd)
                [void]$adapter.Fill($target)

                $this.ShowMessageC('Cyan', 'FillDt()', ("slot={0} rows={1} cols={2}" -f $Slot, $target.Rows.Count, $target.Columns.Count))
            }
            catch {
                $msg = $_.Exception.Message
                if ($null -ne $_.Exception.InnerException -and -not [string]::IsNullOrWhiteSpace($_.Exception.InnerException.Message)) {
                    $msg = ('{0} | {1}' -f $msg, $_.Exception.InnerException.Message)
                }
                $this.WriteErr("FillDt failed: $msg")
            }
            finally {
                if ($openedHere) {
                    $this.CloseConnection()
                }
            }
        }

        # Convenience: store SQL in QueryN, then FillDt(N).
        [void] ExecuteQuery([string]$Sql, [int]$Slot) {
            $this.ShowMessageC('Cyan', 'ExecuteQuery()', ("slot={0}" -f $Slot))

            if ($Slot -lt 1 -or $Slot -gt 3) {
                $this.WriteErr("ExecuteQuery: Slot must be 1, 2, or 3 (got $Slot)"); return
            }
            if ([string]::IsNullOrWhiteSpace($Sql)) {
                $this.WriteErr('ExecuteQuery: Sql is required'); return
            }

            $this.SetQuery($Slot, $Sql)
            $this.FillDt($Slot)
        }

        [void] RunSP([string]$Name) {
            $this.ShowMessageC('Cyan', 'RunSP()', $Name)

            if ([string]::IsNullOrWhiteSpace($Name)) {
                $this.WriteErr('RunSP: Name is required'); return
            }

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet) {
                $this.WriteErr('RunSP: connection string not set'); return
            }

            $openedHere = $false
            try {
                if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                    $this.OpenConnection()
                    $openedHere = $true
                }
                $this.SetCmd()
                if (-not $this.CmdReady) {
                    $this.WriteErr('RunSP: command not ready'); return
                }

                $raw = $Name.Trim()
                $schema = $this.DBSchema
                $procName = $raw
                if ($raw.Contains('.')) {
                    $dot = $raw.IndexOf('.')
                    $schemaPart = $raw.Substring(0, $dot).Trim()
                    $namePart = $raw.Substring($dot + 1).Trim()
                    $schema = $schemaPart.TrimStart('[').TrimEnd(']').Replace(']]', ']')
                    $procName = $namePart.TrimStart('[').TrimEnd(']').Replace(']]', ']')
                }
                if ([string]::IsNullOrWhiteSpace($procName)) {
                    $this.WriteErr('RunSP: procedure name is required'); return
                }
                if ([string]::IsNullOrWhiteSpace($schema)) { $schema = 'dbo' }

                $twoPart = $this.QuoteTwoPart($schema, $procName)
                $safe = $twoPart.Replace("'", "''")
                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = "
                SELECT CASE
                    WHEN OBJECT_ID(N'$safe', 'P') IS NOT NULL THEN 1
                    WHEN OBJECT_ID(N'$safe', 'PC') IS NOT NULL THEN 1
                    ELSE 0
                END;
                "
                $exists = [int]$this.cmd.ExecuteScalar()
                if ($exists -ne 1) {
                    $this.WriteErr("RunSP: stored procedure not found: $twoPart")
                    return
                }

                $ran = $false
                try {
                    $this.cmd.CommandType = [System.Data.CommandType]::StoredProcedure
                    $this.cmd.CommandText = ('{0}.{1}' -f $schema, $procName)
                    $this.cmd.CommandTimeout = 600
                    [void]$this.cmd.ExecuteNonQuery()
                    $ran = $true
                }
                catch {
                    # Fall back to EXEC with quoted two-part name
                    try { $this.cmd.CommandType = [System.Data.CommandType]::Text } catch { }
                    $this.cmd.CommandText = "EXEC $twoPart"
                    $this.cmd.CommandTimeout = 600
                    [void]$this.cmd.ExecuteNonQuery()
                    $ran = $true
                }

                if ($ran) {
                    $this.ShowMessageC('Cyan', 'RunSP()', 'completed')
                }
            }
            catch {
                $msg = $_.Exception.Message
                if ($null -ne $_.Exception.InnerException -and -not [string]::IsNullOrWhiteSpace($_.Exception.InnerException.Message)) {
                    $msg = ('{0} | {1}' -f $msg, $_.Exception.InnerException.Message)
                }
                $this.WriteErr("RunSP failed: $msg")
            }
            finally {
                try {
                    if ($null -ne $this.cmd) {
                        $this.cmd.CommandType = [System.Data.CommandType]::Text
                        if ($null -ne $this.cmd.Parameters) { $this.cmd.Parameters.Clear() }
                    }
                }
                catch { }
                if ($openedHere) {
                    $this.CloseConnection()
                }
            }
        }

        # Profile one temp-table column via T-SQL aggregates (full scan).
        hidden [hashtable] ProfileTempColumn([string]$ColumnName) {
            $qi = $this.QuoteIdent($ColumnName)
            $this.cmd.CommandTimeout = 600
            $this.cmd.CommandText = "
            SELECT
                COUNT_BIG(*) AS total_cnt,
                SUM(CASE
                        WHEN $qi IS NULL OR LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) = N'' THEN 1
                        ELSE 0
                    END) AS blank_cnt,
                MAX(LEN(LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))))) AS max_len,
                SUM(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                         AND TRY_CONVERT(datetime2, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi)))) IS NULL
                        THEN 1 ELSE 0
                    END) AS nondate_cnt,
                SUM(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                         AND TRY_CONVERT(float, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi)))) IS NULL
                        THEN 1 ELSE 0
                    END) AS nonnum_cnt,
                SUM(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                         AND (
                                LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%.%'
                             OR LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%e%'
                             OR LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%E%'
                         )
                        THEN 1 ELSE 0
                    END) AS has_frac_or_exp_cnt,
                SUM(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                         AND (
                                LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%e%'
                             OR LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%E%'
                         )
                        THEN 1 ELSE 0
                    END) AS has_exp_cnt,
                SUM(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) NOT IN (N'0', N'1')
                        THEN 1 ELSE 0
                    END) AS nonbit_cnt,
                MIN(TRY_CONVERT(float, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))))) AS min_num,
                MAX(TRY_CONVERT(float, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))))) AS max_num,
                MAX(CASE
                        WHEN $qi IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) LIKE N'%.%'
                         AND TRY_CONVERT(float, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi)))) IS NOT NULL
                        THEN LEN(LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))))
                             - CHARINDEX(N'.', LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))))
                        ELSE 0
                    END) AS max_scale,
                MAX(CASE
                        WHEN $qi IS NOT NULL
                         AND TRY_CONVERT(float, LTRIM(RTRIM(CONVERT(nvarchar(max), $qi)))) IS NOT NULL
                         AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N''
                        THEN LEN(REPLACE(REPLACE(LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))), N'-', N''), N'.', N''))
                        ELSE 0
                    END) AS max_digits
            FROM $($this.DBTableTemp);
            "
            $dt = [System.Data.DataTable]::new()
            $adapter = $this.NewSqlDataAdapter($this.cmd)
            [void]$adapter.Fill($dt)
            $row = $dt.Rows[0]
            $total = [long]$row['total_cnt']
            $blank = [long]$row['blank_cnt']
            $nonblank = $total - $blank
            $maxLen = 0
            if (-not [DBNull]::Value.Equals($row['max_len']) -and $null -ne $row['max_len']) {
                $maxLen = [int]$row['max_len']
            }
            $nondate = [long]$row['nondate_cnt']
            $nonnum = [long]$row['nonnum_cnt']
            $hasFracOrExp = [long]$row['has_frac_or_exp_cnt']
            $hasExp = [long]$row['has_exp_cnt']
            $nonbit = [long]$row['nonbit_cnt']
            $minNum = $null
            $maxNum = $null
            if (-not [DBNull]::Value.Equals($row['min_num'])) { $minNum = [double]$row['min_num'] }
            if (-not [DBNull]::Value.Equals($row['max_num'])) { $maxNum = [double]$row['max_num'] }
            $maxScale = 0
            if (-not [DBNull]::Value.Equals($row['max_scale'])) { $maxScale = [int]$row['max_scale'] }
            $maxDigits = 0
            if (-not [DBNull]::Value.Equals($row['max_digits'])) { $maxDigits = [int]$row['max_digits'] }

            return @{
                total       = $total
                nonblank    = $nonblank
                maxLen      = $maxLen
                allDate     = ($nonblank -gt 0 -and $nondate -eq 0)
                allNumeric  = ($nonblank -gt 0 -and $nonnum -eq 0)
                hasDecimal  = ($hasFracOrExp -gt 0 -and $hasExp -eq 0)
                hasExp      = ($hasExp -gt 0)
                allBit      = ($nonblank -gt 0 -and $nonbit -eq 0)
                minNum      = $minNum
                maxNum      = $maxNum
                maxScale    = $maxScale
                maxDigits   = $maxDigits
            }
        }

        hidden [bool] TempColumnNeedsNVarChar([string]$ColumnName) {
            $qi = $this.QuoteIdent($ColumnName)
            # Sample distinct non-blank values; any codepoint > 127 => nvarchar
            $this.cmd.CommandTimeout = 600
            $this.cmd.CommandText = "
            SELECT DISTINCT TOP (2000)
                LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) AS v
            FROM $($this.DBTableTemp)
            WHERE $qi IS NOT NULL
              AND LTRIM(RTRIM(CONVERT(nvarchar(max), $qi))) <> N'';
            "
            $dt = [System.Data.DataTable]::new()
            $adapter = $this.NewSqlDataAdapter($this.cmd)
            [void]$adapter.Fill($dt)
            foreach ($r in $dt.Rows) {
                $s = [string]$r['v']
                foreach ($ch in $s.ToCharArray()) {
                    if ([int][char]$ch -gt 127) { return $true }
                }
            }
            return $false
        }

        hidden [string] InferColumnSqlType([string]$ColumnName, [bool]$NotNull) {
            $nullSuffix = if ($NotNull) { ' NOT NULL' } else { ' NULL' }
            $p = $this.ProfileTempColumn($ColumnName)

            if ($p.nonblank -le 0) {
                return ("varchar(500){0}" -f $nullSuffix)
            }

            if ($p.allDate) {
                return ("[datetime]{0}" -f $nullSuffix)
            }

            if ($p.allNumeric) {
                if ($p.allBit) {
                    return ("bit{0}" -f $nullSuffix)
                }
                if ($p.hasExp) {
                    return ("float{0}" -f $nullSuffix)
                }
                if ($p.hasDecimal) {
                    $s = [Math]::Max(0, [Math]::Min(38, [int]$p.maxScale))
                    $digits = [Math]::Max(1, [int]$p.maxDigits)
                    $prec = [Math]::Max($digits, $s)
                    if ($prec -lt 18) { $prec = 18 }
                    if ($prec -gt 38) { $prec = 38 }
                    if ($s -gt $prec) { $s = $prec }
                    return ("decimal({0},{1}){2}" -f $prec, $s, $nullSuffix)
                }
                $mn = $p.minNum
                $mx = $p.maxNum
                if ($null -eq $mn) { $mn = 0 }
                if ($null -eq $mx) { $mx = 0 }
                if ($mn -ge 0 -and $mx -le 255) {
                    return ("tinyint{0}" -f $nullSuffix)
                }
                if ($mn -ge -32768 -and $mx -le 32767) {
                    return ("smallint{0}" -f $nullSuffix)
                }
                if ($mn -ge -2147483648 -and $mx -le 2147483647) {
                    return ("int{0}" -f $nullSuffix)
                }
                return ("bigint{0}" -f $nullSuffix)
            }

            $L = [Math]::Max(1, [int]$p.maxLen)
            $size = $L * 2
            $needsNv = $this.TempColumnNeedsNVarChar($ColumnName)
            if ($needsNv) {
                if ($size -gt 4000) {
                    return ("nvarchar(max){0}" -f $nullSuffix)
                }
                return ("nvarchar({0}){1}" -f $size, $nullSuffix)
            }
            if ($size -gt 8000) {
                return ("varchar(max){0}" -f $nullSuffix)
            }
            return ("varchar({0}){1}" -f $size, $nullSuffix)
        }

        [void] CreateMainTableFromTemp([string]$Identity) {
            $this.ShowMessageC('Cyan', 'CreateMainTableFromTemp()', $Identity)

            if ([string]::IsNullOrWhiteSpace($Identity)) {
                $this.WriteErr('CreateMainTableFromTemp: Identity column name is required'); return
            }
            if ([string]::IsNullOrWhiteSpace($this.DBTableName) -or $this.DBTableName -eq '.') {
                $this.WriteErr('CreateMainTableFromTemp: DBTableName not set (call SetTable first)'); return
            }
            if ([string]::IsNullOrWhiteSpace($this.DBTable) -or $this.DBTable -eq '.') {
                $this.WriteErr('CreateMainTableFromTemp: DBTable not set (call SetTable first)'); return
            }
            if ([string]::IsNullOrWhiteSpace($this.DBTableTemp) -or $this.DBTableTemp -eq '.') {
                $this.WriteErr('CreateMainTableFromTemp: DBTableTemp not set (call SetTable first)'); return
            }
            if ([string]::IsNullOrWhiteSpace($this.DBSchema)) {
                $this.WriteErr('CreateMainTableFromTemp: DBSchema not set'); return
            }

            if (-not $this.ConnStrSet -or $this.ConnStr -eq '.') {
                $this.BuildConnStr()
            }
            if (-not $this.ConnStrSet) {
                $this.WriteErr('CreateMainTableFromTemp: connection string not set'); return
            }

            $openedHere = $false
            try {
                if ($null -eq $this.Conn -or [string]$this.Conn.State -ne 'Open') {
                    $this.OpenConnection()
                    $openedHere = $true
                }
                $this.SetCmd()
                if (-not $this.CmdReady) {
                    $this.WriteErr('CreateMainTableFromTemp: command not ready'); return
                }

                if (-not $this.SqlTableExists($this.DBTableTemp)) {
                    $this.WriteErr("CreateMainTableFromTemp: temp table does not exist: $($this.DBTableTemp)")
                    return
                }
                if ($this.SqlTableExists($this.DBTable)) {
                    $this.WriteErr("CreateMainTableFromTemp: main table already exists (will not overwrite): $($this.DBTable)")
                    return
                }

                $tempBare = $this.DBTableName + '_Temp'
                $cols = @($this.GetTempTableColumns($this.DBSchema, $tempBare))
                if ($cols.Count -eq 0) {
                    $this.WriteErr("CreateMainTableFromTemp: no columns on $($this.DBTableTemp)")
                    return
                }

                $idMatch = @($cols | Where-Object { $_.Equals($Identity, [System.StringComparison]::OrdinalIgnoreCase) })
                if ($idMatch.Count -eq 0) {
                    $this.WriteErr("CreateMainTableFromTemp: Identity column '$Identity' not found on $($this.DBTableTemp)")
                    return
                }
                $identityCol = $idMatch[0]

                $defs = [System.Collections.Generic.List[string]]::new()
                $summary = [System.Collections.Generic.List[string]]::new()
                foreach ($c in $cols) {
                    $isId = $c.Equals($identityCol, [System.StringComparison]::OrdinalIgnoreCase)
                    $sqlType = $this.InferColumnSqlType($c, $isId)
                    [void]$defs.Add(("            {0} {1}" -f $this.QuoteIdent($c), $sqlType))
                    [void]$summary.Add(('{0}={1}' -f $c, $sqlType))
                }

                $pkName = 'PK_' + $this.DBTableName
                $createSql = "
                CREATE TABLE $($this.DBTable) (
$($defs -join ",`n"),
                    CONSTRAINT $($this.QuoteIdent($pkName)) PRIMARY KEY ($($this.QuoteIdent($identityCol)))
                );
                "
                $this.cmd.CommandTimeout = 600
                $this.cmd.CommandText = $createSql
                [void]$this.cmd.ExecuteNonQuery()
                $this.ShowMessageC('Cyan', 'CreateMainTableFromTemp()', ('created {0} ({1})' -f $this.DBTable, ($summary -join '; ')))
            }
            catch {
                $msg = $_.Exception.Message
                if ($null -ne $_.Exception.InnerException -and -not [string]::IsNullOrWhiteSpace($_.Exception.InnerException.Message)) {
                    $msg = ('{0} | {1}' -f $msg, $_.Exception.InnerException.Message)
                }
                $this.WriteErr("CreateMainTableFromTemp failed: $msg")
            }
            finally {
                if ($openedHere) {
                    $this.CloseConnection()
                }
            }
        }

        hidden [void] WriteErr([string]$Message) {
            if (-not $this.Quiet) {
                Write-Host $Message -ForegroundColor Red
            }
        }

        hidden [void] WriteHostMsg([string]$Message) {
            if (-not $this.Quiet) {
                Write-Host $Message
            }
        }

        [void] ShowMessage([string]$Head) {
            if ($this.VerboseMessages) { $this.ShowMessageC('White', $Head) }
        }

        [void] ShowMessage([string]$Head, [string]$Data) {
            if ($this.VerboseMessages) { $this.ShowMessageC('White', $Head, $Data) }
        }

        [void] ShowMessageC([string]$Color, [string]$Head) {
            if ($this.VerboseMessages -and -not $this.Quiet) {
                Write-Host $Head -ForegroundColor $Color
            }
        }

        [void] ShowMessageC([string]$Color, [string]$Head, [string]$Data) {
            if ($this.VerboseMessages -and -not $this.Quiet) {
                Write-Host ('{0} : {1}' -f $Head.PadRight(60), $Data) -ForegroundColor $Color
            }
        }
    }

    #---------------------------------------------------------------------------------------------------------------#
    try {
        if ($Database -ne '.' -and $Instance -ne '.') {
            $tool = [cSQLTool]::new($Server, $Instance, $Database)
        }
        elseif ($Database -ne '.' -and $Server -ne '.') {
            $tool = [cSQLTool]::new($Server, $Database)
        }
        else {
            $tool = [cSQLTool]::new()
            if ($Server -ne '.') { $tool.SetSQLServer($Server) }
            if ($Instance -ne '.') { $tool.SetSQLInstance($Instance) }
            if ($Database -ne '.') { $tool.SetDBName($Database) }
        }

        $tool.Quiet = [bool]$Quiet
        $tool.VerboseMessages = [bool]$VerboseMessages
        $tool.UseMicrosoftDataSqlClient = [bool]$script:SQLTool_UseMds
        return $tool
    }
    catch {
        Write-Host ("Get-SQLTool failed: {0}" -f $_.Exception.Message) -ForegroundColor Red
        return $null
    }
}

#-------------------------------------------------------------------------------------------------------------------#
# Sample driver — uses BulkInsertCsv method on a fresh SQLTool instance
#-------------------------------------------------------------------------------------------------------------------#
function global:BulkInsert {
    [CmdletBinding()]
    param(
        [string]$CsvPath = 'C:\Data\Sample.csv',
        [string]$Server = 'ServerName',
        [string]$Instance = 'InstanceName',
        [string]$Database = 'DatabaseName',
        [string]$Table = 'TableName'
    )

    $SQLTool = Get-SQLTool -Server $Server -Instance $Instance -Database $Database
    if ($null -eq $SQLTool) { return }

    $SQLTool.BuildConnStr()
    # Server / Instance / Database already set via Get-SQLTool.
    # FullName + TableName go to the method; UniqueKey comes from DBTable PK.
    $SQLTool.BulkInsertCsv($CsvPath, $Table)
}

#-------------------------------------------------------------------------------------------------------------------#
# Sample driver — temp-only load (ensure/create staging, no MERGE)
#-------------------------------------------------------------------------------------------------------------------#
function global:BulkInsertTempOnly {
    [CmdletBinding()]
    param(
        [string]$CsvPath = 'C:\Data\Sample.csv',
        [string]$Server = 'ServerName',
        [string]$Instance = 'InstanceName',
        [string]$Database = 'DatabaseName',
        [string]$Table = 'TableName'
    )

    $SQLTool = Get-SQLTool -Server $Server -Instance $Instance -Database $Database
    if ($null -eq $SQLTool) { return }

    $SQLTool.BuildConnStr()
    $SQLTool.BulkInsertCsvTempOnly($CsvPath, $Table)
}