# DBAOps.SecurityCompliance.psm1
# RHOYLLE INNOVATION EMPIRE LLC - Enterprise Security Compliance Module
# gMSA, SA-disabled, TDE, TLS, RBAC enforcement checks
# Cross-platform: PowerShell 7+ (Windows, Linux, macOS)

$ErrorActionPreference = "Continue"

function Get-SecurityComplianceStatus {
<#
.SYNOPSIS
    Comprehensive security compliance check for SQL Server
.DESCRIPTION
    Validates: gMSA (Group Managed Service Accounts), SA disabled, TDE, TLS, RBAC
.EXAMPLE
    Get-SecurityComplianceStatus -SqlInstance "SERVER01" -Database "DBAOpsRepository"
#>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)][string]$SqlInstance,
        [string]$Database = "DBAOpsRepository",
        [switch]$IncludeDetails
    )
    
    $results = @{
        ServerName = $SqlInstance
        CheckedAt = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
        Compliance = @{}
        Passed = 0
        Failed = 0
        Warnings = 0
    }
    
    # 1. SA Account Disabled
    try {
        $saCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query "SELECT name, is_disabled FROM sys.sql_logins WHERE name = 'sa'" -EnableException
        $saDisabled = $saCheck -and $saCheck.is_disabled -eq 1
        $results.Compliance.SA_Disabled = @{
            Passed = $saDisabled
            Status = if ($saDisabled) { "PASS" } else { "FAIL" }
            Message = if ($saDisabled) { "SA account is disabled" } else { "CRITICAL: SA account is enabled" }
        }
        if ($saDisabled) { $results.Passed++ } else { $results.Failed++ }
    } catch {
        $results.Compliance.SA_Disabled = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
        $results.Failed++
    }
    
    # 2. TDE (Transparent Data Encryption)
    try {
        $tdeCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query @"
SELECT name, is_encrypted FROM sys.databases 
WHERE database_id > 4 AND state_desc = 'ONLINE'
"@ -EnableException
        $unencrypted = @($tdeCheck | Where-Object { $_.is_encrypted -eq 0 })
        $results.Compliance.TDE = @{
            Passed = ($unencrypted.Count -eq 0)
            Status = if ($unencrypted.Count -eq 0) { "PASS" } else { "WARNING" }
            Message = if ($unencrypted.Count -eq 0) { "All user databases encrypted" } else { "$($unencrypted.Count) database(s) not encrypted: $($unencrypted.name -join ', ')" }
            UnencryptedDatabases = $unencrypted.name
        }
        if ($unencrypted.Count -eq 0) { $results.Passed++ } else { $results.Warnings++ }
    } catch {
        $results.Compliance.TDE = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
        $results.Failed++
    }
    
    # 3. TLS/Connection Encryption
    try {
        $tlsCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query @"
SELECT COUNT(*) AS Unencrypted FROM sys.dm_exec_connections 
WHERE session_id > 50 AND encrypt_option = 'FALSE'
"@ -EnableException
        $unencrypted = [int]($tlsCheck.Unencrypted)
        $results.Compliance.TLS = @{
            Passed = ($unencrypted -eq 0)
            Status = if ($unencrypted -eq 0) { "PASS" } else { "WARNING" }
            Message = if ($unencrypted -eq 0) { "All connections encrypted" } else { "$unencrypted unencrypted connection(s)" }
        }
        if ($unencrypted -eq 0) { $results.Passed++ } else { $results.Warnings++ }
    } catch {
        $results.Compliance.TLS = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
        $results.Failed++
    }
    
    # 4. xp_cmdshell Disabled
    try {
        $xpCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query "EXEC sp_configure 'xp_cmdshell'" -EnableException
        $xpValue = ($xpCheck | Where-Object { $_.name -eq 'xp_cmdshell' }).run_value
        $xpDisabled = $xpValue -eq 0
        $results.Compliance.xp_cmdshell_Disabled = @{
            Passed = $xpDisabled
            Status = if ($xpDisabled) { "PASS" } else { "FAIL" }
            Message = if ($xpDisabled) { "xp_cmdshell is disabled" } else { "CRITICAL: xp_cmdshell is enabled" }
        }
        if ($xpDisabled) { $results.Passed++ } else { $results.Failed++ }
    } catch {
        $results.Compliance.xp_cmdshell_Disabled = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
        $results.Failed++
    }
    
    # 5. gMSA (Group Managed Service Account) - Service Account Check
    try {
        $svcCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query "SELECT servicename, service_account FROM sys.dm_server_services" -EnableException
        $sqlSvc = $svcCheck | Where-Object { $_.servicename -like '*SQL Server*' } | Select-Object -First 1
        $acct = $sqlSvc.service_account
        $isGMSA = $acct -like '*$'  # gMSA accounts end with $
        $results.Compliance.gMSA = @{
            Passed = $isGMSA
            Status = if ($isGMSA) { "PASS" } else { "WARNING" }
            Message = if ($isGMSA) { "SQL Service uses gMSA: $acct" } else { "SQL Service account: $acct (consider gMSA for enterprise)" }
            ServiceAccount = $acct
        }
        if ($isGMSA) { $results.Passed++ } else { $results.Warnings++ }
    } catch {
        $results.Compliance.gMSA = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
        $results.Failed++
    }
    
    # 6. RBAC - Windows Auth mode (preferred over SQL auth for RBAC)
    try {
        $authCheck = Invoke-DbaQuery -SqlInstance $SqlInstance -Query "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly" -EnableException
        $winAuth = [int]$authCheck.WindowsAuthOnly -eq 1
        $results.Compliance.RBAC_WindowsAuth = @{
            Passed = $winAuth
            Status = if ($winAuth) { "PASS" } else { "INFO" }
            Message = if ($winAuth) { "Windows Authentication mode" } else { "Mixed mode (SQL + Windows)" }
        }
        if ($winAuth) { $results.Passed++ } else { $results.Warnings++ }
    } catch {
        $results.Compliance.RBAC_WindowsAuth = @{ Passed = $false; Status = "ERROR"; Message = $_.Exception.Message }
    }
    
    return $results
}

Export-ModuleMember -Function Get-SecurityComplianceStatus
