# DBAOps.Alerting.psm1 - Minimal Working Version
$Script:Config = @{
    CentralServer = "DPS172091\DEV"
    Database = "DBAOpsRepository"
}

function Get-OnCallDBA {
    [CmdletBinding()]
    param()
    
    $query = @"
SELECT TOP 1 
    PrimaryDBA, PrimaryPhone, PrimaryEmail,
    BackupDBA, BackupPhone, BackupEmail,
    RotationStartDate, RotationEndDate,
    DATEDIFF(DAY, GETDATE(), RotationEndDate) AS DaysRemaining
FROM config.OnCallRotation
WHERE IsActive = 1 
AND GETDATE() BETWEEN RotationStartDate AND RotationEndDate
"@
    
    try {
        $result = Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $query
        return $result
    } catch {
        Write-Warning "Failed to get on-call: $_"
        return $null
    }
}

function Set-OnCallRotation {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)][string]$PrimaryDBA,
        [Parameter(Mandatory)][string]$PrimaryPhone,
        [string]$PrimaryEmail,
        [string]$BackupDBA,
        [string]$BackupPhone,
        [datetime]$StartDate = (Get-Date),
        [datetime]$EndDate = (Get-Date).AddDays(7)
    )
    
    $query = @"
INSERT INTO config.OnCallRotation 
(PrimaryDBA, PrimaryPhone, PrimaryEmail, BackupDBA, BackupPhone, RotationStartDate, RotationEndDate, IsActive)
VALUES 
('$PrimaryDBA', '$PrimaryPhone', '$PrimaryEmail', '$BackupDBA', '$BackupPhone', '$($StartDate.ToString("yyyy-MM-dd"))', '$($EndDate.ToString("yyyy-MM-dd"))', 1)
"@
    
    try {
        Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $query
        Write-Host "On-call rotation set: $PrimaryDBA ($PrimaryPhone)" -ForegroundColor Green
    } catch {
        Write-Warning "Failed to set on-call: $_"
    }
}

function Send-DBAOpsAlert {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)][string]$Title,
        [Parameter(Mandatory)][string]$Message,
        [ValidateSet('Info','Warning','Error','Critical')][string]$Severity = 'Info',
        [string]$ServerName,
        [string]$Database
    )
    
    Write-Host "[$Severity] $Title - $Message" -ForegroundColor $(
        switch($Severity) {
            'Info' { 'Cyan' }
            'Warning' { 'Yellow' }
            'Error' { 'Red' }
            'Critical' { 'Magenta' }
        }
    )
    
    # Log to database
    $logQuery = @"
INSERT INTO alerts.NotificationLog (AlertId, Channel, Recipient, Message, Priority, Status, SentAt)
VALUES (NEWID(), 'Console', 'DBA', '$($Message -replace "'", "''")', '$Severity', 'Sent', GETDATE())
"@
    
    try {
        Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $logQuery -EnableException
    } catch {
        # Silent fail for logging
    }
    
    return [PSCustomObject]@{
        Success = $true
        Title = $Title
        Message = $Message
        Severity = $Severity
    }
}

Export-ModuleMember -Function Get-OnCallDBA, Set-OnCallRotation, Send-DBAOpsAlert

