$ErrorActionPreference = 'Continue'
$ProgressPreference   = 'SilentlyContinue'

function Section($name) { "`n===== $name =====" }

Section 'STEP 1: Restart task log'
if (Test-Path 'C:\Windows\Temp\aimsql-restart.log') {
    Get-Content 'C:\Windows\Temp\aimsql-restart.log' -ErrorAction SilentlyContinue
} else {
    "MISSING: C:\Windows\Temp\aimsql-restart.log not found"
}

Section 'STEP 1b: Scheduled task info'
$task = Get-ScheduledTask -TaskName 'AIMSQL_Restart_20260506_0230' -ErrorAction SilentlyContinue
if ($task) {
    $task | Get-ScheduledTaskInfo | Format-List LastRunTime, LastTaskResult, NextRunTime, NumberOfMissedRuns, TaskName
} else {
    "MISSING: scheduled task AIMSQL_Restart_20260506_0230 not found"
}

Section 'STEP 2: SQL service state + process start times'
foreach ($svc in 'MSSQL$AIMSQL','MSSQL$SQLEXPRESS','MSSQL$MICROSOFT##WID') {
    $w = Get-CimInstance Win32_Service -Filter "Name='$svc'" -ErrorAction SilentlyContinue
    if ($null -eq $w) { "$svc : NOT FOUND"; continue }
    $proc = $null
    if ($w.ProcessId -gt 0) { $proc = Get-Process -Id $w.ProcessId -ErrorAction SilentlyContinue }
    [PSCustomObject]@{
        Name      = $w.Name
        State     = $w.State
        StartMode = $w.StartMode
        PID       = $w.ProcessId
        StartTime = if ($proc) { $proc.StartTime } else { 'n/a' }
        WorkingMB = if ($proc) { [math]::Round($proc.WorkingSet64/1MB,1) } else { 'n/a' }
    } | Format-List
}

Section 'STEP 3: MSSQL Application events since 02:00 today'
try {
    $events = Get-WinEvent -FilterHashtable @{
        LogName='Application'
        StartTime=(Get-Date '2026-05-06 02:00:00')
        ProviderName='MSSQL$AIMSQL','MSSQL$SQLEXPRESS','MSSQL$MICROSOFT##WID'
    } -ErrorAction Stop | Sort-Object TimeCreated
    "Total events: $($events.Count)"
    "17890 count: $(($events | Where-Object Id -eq 17890).Count)"
    $events | Select-Object TimeCreated, ProviderName, Id, LevelDisplayName, @{n='Msg';e={ ($_.Message -split "`n")[0].Substring(0,[Math]::Min(220,($_.Message -split "`n")[0].Length)) }} | Format-Table -AutoSize -Wrap
} catch {
    "No MSSQL Application events since 02:00 today (or query failed: $_)"
}

Section 'STEP 4: System log 10:00-12:50 today (incident window)'
try {
    $sys = Get-WinEvent -FilterHashtable @{
        LogName='System'
        StartTime=(Get-Date '2026-05-06 10:00:00')
        EndTime=(Get-Date '2026-05-06 12:50:00')
    } -ErrorAction Stop | Where-Object {
        $_.ProviderName -in @('Service Control Manager','Microsoft-Windows-Resource-Exhaustion-Detector','Microsoft-Windows-Resource-Exhaustion-Resolver','Microsoft-Windows-WER-SystemErrorReporting','Application Popup','Microsoft-Windows-Kernel-General','Microsoft-Windows-Kernel-Power','Microsoft-Windows-WER-Diag') -or $_.LevelDisplayName -in @('Error','Warning','Critical')
    } | Sort-Object TimeCreated
    "System events in window: $($sys.Count)"
    $sys | Select-Object TimeCreated, ProviderName, Id, LevelDisplayName, @{n='Msg';e={ ($_.Message -split "`n")[0].Substring(0,[Math]::Min(200,($_.Message -split "`n")[0].Length)) }} | Format-Table -AutoSize -Wrap
} catch {
    "System log query failed: $_"
}

Section 'STEP 4b: SvcRestartTask runs today'
Get-ScheduledTask -TaskName 'SvcRestartTask' -ErrorAction SilentlyContinue | Get-ScheduledTaskInfo | Format-List TaskName, LastRunTime, LastTaskResult, NextRunTime

Section 'STEP 5: ERRORLOG location + tail'
$el = Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\','S:\SQL\' -Recurse -Filter ERRORLOG -ErrorAction SilentlyContinue | Where-Object { $_.FullName -match 'AIMSQL' -or $_.Directory.Parent.Name -match 'AIMSQL' } | Select-Object FullName, LastWriteTime, Length
if (-not $el) {
    $el = Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\','S:\SQL\' -Recurse -Filter ERRORLOG -ErrorAction SilentlyContinue | Select-Object FullName, LastWriteTime, Length
}
$el | Format-Table -AutoSize
$aimErrLog = ($el | Where-Object FullName -match 'AIMSQL' | Select-Object -First 1).FullName
if (-not $aimErrLog) { $aimErrLog = ($el | Sort-Object LastWriteTime -Descending | Select-Object -First 1).FullName }
if ($aimErrLog -and (Test-Path $aimErrLog)) {
    "--- Tail ($aimErrLog) ---"
    Get-Content $aimErrLog -Tail 200 -ErrorAction SilentlyContinue
} else {
    "No ERRORLOG identified for AIMSQL"
}

Section 'STEP 6: AIMSQL memory snapshot via sqlcmd'
$sqlcmd = (Get-Command sqlcmd -ErrorAction SilentlyContinue).Source
if (-not $sqlcmd) {
    foreach ($p in 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe','C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe','C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe') {
        if (Test-Path $p) { $sqlcmd = $p; break }
    }
}
"sqlcmd: $sqlcmd"
if ($sqlcmd) {
    "--- memory counters ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h -1 -W -Q "SET NOCOUNT ON; SELECT counter_name + '|' + CAST(cntr_value AS varchar(20)) FROM sys.dm_os_performance_counters WHERE (object_name LIKE '%:Memory Manager%' AND counter_name IN ('Total Server Memory (KB)','Target Server Memory (KB)')) OR (object_name LIKE '%:Buffer Manager%' AND counter_name='Page life expectancy');"
    "--- process memory ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h -1 -W -Q "SET NOCOUNT ON; SELECT 'page_fault_count|' + CAST(page_fault_count AS varchar(20)) + '|memory_util_pct|' + CAST(memory_utilization_percentage AS varchar(20)) + '|low_mem|' + CAST(process_physical_memory_low AS varchar(20)) FROM sys.dm_os_process_memory;"
    "--- connection counts ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h -1 -W -Q "SET NOCOUNT ON; SELECT 'active_connections|' + CAST(COUNT(*) AS varchar(20)) FROM sys.dm_exec_connections;"
    "--- sessions by login ---"
    & $sqlcmd -S '.\AIMSQL' -E -d master -h -1 -W -Q "SET NOCOUNT ON; SELECT login_name + '|' + CAST(COUNT(*) AS varchar(10)) FROM sys.dm_exec_sessions WHERE is_user_process=1 GROUP BY login_name;"
} else {
    "sqlcmd not located"
}

Section 'STEP 6b: Active RDP sessions (quser)'
& quser.exe 2>&1

Section 'STEP 7: Windows uptime'
$os = Get-CimInstance Win32_OperatingSystem
$boot = $os.LastBootUpTime
$uptime = (Get-Date) - $boot
"LastBootUpTime: $boot"
"UptimeHours: $([math]::Round($uptime.TotalHours,1))"
"Now: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss zzz')"

Section 'STEP 8: System memory pressure snapshot (now)'
$os = Get-CimInstance Win32_OperatingSystem
[PSCustomObject]@{
    TotalMB        = [math]::Round($os.TotalVisibleMemorySize/1024,0)
    FreeMB         = [math]::Round($os.FreePhysicalMemory/1024,0)
    PctFree        = [math]::Round(100*$os.FreePhysicalMemory/$os.TotalVisibleMemorySize,1)
    CommitTotalMB  = [math]::Round(($os.TotalVirtualMemorySize)/1024,0)
    CommitFreeMB   = [math]::Round(($os.FreeVirtualMemory)/1024,0)
} | Format-List

Section 'DONE'