<#
.SYNOPSIS
    DBAOps Microsoft Teams Integration
    
.DESCRIPTION
    Sends alerts to Microsoft Teams channels via Incoming Webhooks
    Supports:
    - Adaptive Cards for rich formatting
    - Critical, Warning, and Info severity levels
    - Server/Database context
    - Actionable links
    
.PARAMETER WebhookUrl
    Teams Incoming Webhook URL
    
.PARAMETER Subject
    Alert subject/title
    
.PARAMETER Message
    Alert message body
    
.PARAMETER Severity
    Alert severity: Critical, Warning, Info
    
.PARAMETER ServerName
    Optional server name for context
    
.PARAMETER DatabaseName
    Optional database name for context
    
.EXAMPLE
    .\Send-TeamsAlert.ps1 -Subject "Backup Failed" -Message "Database XYZ backup failed" -Severity "Critical" -ServerName "SQLPROD01"
    
.NOTES
    Author: DBAOps Team
    Date: January 19, 2026
    
    To get a Webhook URL:
    1. In Teams, go to the channel
    2. Click ... > Connectors
    3. Add "Incoming Webhook"
    4. Copy the URL
#>

[CmdletBinding()]
param(
    [Parameter()]
    [string]$WebhookUrl,
    
    [Parameter(Mandatory)]
    [string]$Subject,
    
    [Parameter(Mandatory)]
    [string]$Message,
    
    [Parameter()]
    [ValidateSet('Critical', 'Warning', 'Info')]
    [string]$Severity = 'Info',
    
    [Parameter()]
    [string]$Category = 'General',
    
    [Parameter()]
    [string]$ServerName,
    
    [Parameter()]
    [string]$DatabaseName,
    
    [Parameter()]
    [string]$DashboardUrl = 'http://YOURSERVER01/DBAOps',
    
    [Parameter()]
    [string]$ConfigPath = 'E:\DBAOps\Config\TeamsConfig.json'
)

$ErrorActionPreference = "Stop"

# ============================================================================
# Get Webhook URL from config if not provided
# ============================================================================
if ([string]::IsNullOrEmpty($WebhookUrl)) {
    if (Test-Path $ConfigPath) {
        $config = Get-Content $ConfigPath | ConvertFrom-Json
        $WebhookUrl = $config.WebhookUrl
    }
    
    # Try database config
    if ([string]::IsNullOrEmpty($WebhookUrl)) {
        try {
            $query = "SELECT JSON_VALUE(ConfigValue, '$.WebhookUrl') FROM Logging.NotificationConfig WHERE ConfigName = 'Teams_Webhook'"
            $result = Invoke-Sqlcmd -ServerInstance "YOURSERVER01" -Database "DBAOpsRepository" -Query $query -TrustServerCertificate
            $WebhookUrl = $result.Column1
        }
        catch {
            Write-Warning "Could not retrieve webhook URL from database"
        }
    }
}

if ([string]::IsNullOrEmpty($WebhookUrl)) {
    Write-Error "No Teams Webhook URL configured. Please provide -WebhookUrl or configure in $ConfigPath"
    exit 1
}

# ============================================================================
# Set colors and icons based on severity
# ============================================================================
$severityConfig = @{
    'Critical' = @{
        Color = 'attention'  # Red in Adaptive Cards
        ThemeColor = 'dc3545'
        Icon = 'ðŸš¨'
        Emoji = 'ðŸ”´'
    }
    'Warning' = @{
        Color = 'warning'    # Yellow
        ThemeColor = 'ffc107'
        Icon = 'âš ï¸'
        Emoji = 'ðŸŸ¡'
    }
    'Info' = @{
        Color = 'good'       # Green
        ThemeColor = '17a2b8'
        Icon = 'â„¹ï¸'
        Emoji = 'ðŸ”µ'
    }
}

$config = $severityConfig[$Severity]

# ============================================================================
# Build Adaptive Card
# ============================================================================
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

# Build facts array for context
$facts = @(
    @{
        title = "Timestamp"
        value = $timestamp
    },
    @{
        title = "Severity"
        value = "$($config.Emoji) $Severity"
    },
    @{
        title = "Category"
        value = $Category
    }
)

if (-not [string]::IsNullOrEmpty($ServerName)) {
    $facts += @{
        title = "Server"
        value = $ServerName
    }
}

if (-not [string]::IsNullOrEmpty($DatabaseName)) {
    $facts += @{
        title = "Database"
        value = $DatabaseName
    }
}

# Build the Adaptive Card payload
$card = @{
    type = "message"
    attachments = @(
        @{
            contentType = "application/vnd.microsoft.card.adaptive"
            contentUrl = $null
            content = @{
                '$schema' = "http://adaptivecards.io/schemas/adaptive-card.json"
                type = "AdaptiveCard"
                version = "1.4"
                body = @(
                    # Header with icon and title
                    @{
                        type = "TextBlock"
                        size = "Large"
                        weight = "Bolder"
                        text = "$($config.Icon) DBAOps Alert"
                        color = $config.Color
                    },
                    # Subject
                    @{
                        type = "TextBlock"
                        size = "Medium"
                        weight = "Bolder"
                        text = $Subject
                        wrap = $true
                    },
                    # Horizontal line
                    @{
                        type = "TextBlock"
                        text = "â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”â”"
                        color = "light"
                        spacing = "Small"
                    },
                    # Facts
                    @{
                        type = "FactSet"
                        facts = $facts
                    },
                    # Message body
                    @{
                        type = "TextBlock"
                        text = "**Details:**"
                        spacing = "Medium"
                    },
                    @{
                        type = "TextBlock"
                        text = $Message
                        wrap = $true
                        spacing = "Small"
                    }
                )
                actions = @(
                    @{
                        type = "Action.OpenUrl"
                        title = "ðŸ”— Open Dashboard"
                        url = $DashboardUrl
                    }
                )
            }
        }
    )
}

# ============================================================================
# Send to Teams
# ============================================================================
try {
    $jsonPayload = $card | ConvertTo-Json -Depth 20
    
    $response = Invoke-RestMethod -Uri $WebhookUrl -Method Post -Body $jsonPayload -ContentType 'application/json'
    
    Write-Host "âœ… Alert sent to Teams successfully" -ForegroundColor Green
    Write-Host "   Subject: $Subject" -ForegroundColor Gray
    Write-Host "   Severity: $Severity" -ForegroundColor Gray
    
    # Log to database
    try {
        $logQuery = @"
            DECLARE @AlertID BIGINT;
            EXEC Logging.usp_WriteAlert
                @AlertType = 'Teams',
                @Severity = '$Severity',
                @Category = '$Category',
                @Subject = N'$($Subject -replace "'", "''")',
                @Message = N'$($Message -replace "'", "''")',
                @ServerName = $(if($ServerName){"N'$ServerName'"}else{'NULL'}),
                @DatabaseName = $(if($DatabaseName){"N'$DatabaseName'"}else{'NULL'}),
                @Recipients = 'Teams Channel',
                @AlertID = @AlertID OUTPUT;
            
            EXEC Logging.usp_UpdateAlertStatus @AlertID = @AlertID, @DeliveryStatus = 'Sent';
"@
        Invoke-Sqlcmd -ServerInstance "YOURSERVER01" -Database "DBAOpsRepository" -Query $logQuery -TrustServerCertificate
    }
    catch {
        Write-Warning "Could not log alert to database: $($_.Exception.Message)"
    }
    
    exit 0
}
catch {
    Write-Error "Failed to send Teams alert: $($_.Exception.Message)"
    
    # Log failure to database
    try {
        $errorMsg = $_.Exception.Message -replace "'", "''"
        $logQuery = @"
            DECLARE @AlertID BIGINT;
            EXEC Logging.usp_WriteAlert
                @AlertType = 'Teams',
                @Severity = '$Severity',
                @Category = '$Category',
                @Subject = N'$($Subject -replace "'", "''")',
                @Message = N'$($Message -replace "'", "''")',
                @ServerName = $(if($ServerName){"N'$ServerName'"}else{'NULL'}),
                @DatabaseName = $(if($DatabaseName){"N'$DatabaseName'"}else{'NULL'}),
                @Recipients = 'Teams Channel',
                @AlertID = @AlertID OUTPUT;
            
            EXEC Logging.usp_UpdateAlertStatus 
                @AlertID = @AlertID, 
                @DeliveryStatus = 'Failed',
                @ErrorMessage = N'$errorMsg';
"@
        Invoke-Sqlcmd -ServerInstance "YOURSERVER01" -Database "DBAOpsRepository" -Query $logQuery -TrustServerCertificate
    }
    catch {
        # Ignore logging errors
    }
    
    exit 1
}
