<#
================================================================================
    ____  ____  ___   ____            
   / __ \/ __ )/   | / __ \____  _____
  / / / / __  / /| |/ / / / __ \/ ___/
 / /_/ / /_/ / ___ / /_/ / /_/ (__  ) 
/_____/_____/_/  |_\____/ .___/____/  
                       /_/            
================================================================================
  DBAOps Ultimate - Drift Detection & Remediation Module
  Version: 2.0
================================================================================

.SYNOPSIS
    DBAOps.DriftDetection - Real-time Database Drift Monitoring
    Rivals DBmaestro's drift management capabilities

.DESCRIPTION
    This module provides:
    - Real-time detection of unauthorized schema changes
    - Configuration drift monitoring
    - Security permission drift detection
    - Automated remediation workflows
    - Comprehensive drift audit trail

.AUTHOR
    JB Gakumba - Sr SQL DBA / DBAOps Architect

.VERSION
    2.0 - January 2026

.NOTES
    Requires: dbatools, SqlServer modules
================================================================================
#>

#region Module Configuration
$Script:ModuleName = "DBAOps.DriftDetection"
$Script:ModuleVersion = "2.0.0"
$Script:BaselinePath = "E:\DBAOps\Baselines"
$Script:DriftLogPath = "E:\DBAOps\Logs\DriftDetection"
$Script:CentralServer = "AZDBAOPSSQL01"
$Script:AlertThreshold = "Warning"  # Info, Warning, Critical
#endregion

#region Helper Functions
function Write-DriftLog {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string]$Message,
        
        [ValidateSet('Info', 'Warning', 'Error', 'Critical', 'Success')]
        [string]$Level = 'Info',
        
        [string]$ServerName,
        [string]$Database
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"
    $color = switch ($Level) {
        'Info'     { 'White' }
        'Warning'  { 'Yellow' }
        'Error'    { 'Red' }
        'Critical' { 'Magenta' }
        'Success'  { 'Green' }
    }
    Write-Host "[$timestamp] [$Level] $Message" -ForegroundColor $color
    
    # File logging
    $logFile = Join-Path $Script:DriftLogPath "DriftDetection_$(Get-Date -Format 'yyyyMMdd').log"
    if (-not (Test-Path $Script:DriftLogPath)) {
        New-Item -Path $Script:DriftLogPath -ItemType Directory -Force | Out-Null
    }
    Add-Content -Path $logFile -Value "[$timestamp] [$Level] [$ServerName] [$Database] $Message"
    
    # Database logging for critical events
    if ($Level -in @('Warning', 'Error', 'Critical')) {
        try {
            $logQuery = @"
INSERT INTO DBAOpsRepository.dbo.DriftLog (LogTime, Level, ServerName, DatabaseName, Message)
VALUES (GETDATE(), '$Level', '$ServerName', '$Database', '$($Message -replace "'", "''")');
"@
            Invoke-DbaQuery -SqlInstance $Script:CentralServer -Query $logQuery -EnableException
        } catch {
            # Silent fail
        }
    }
}

function Get-ObjectHash {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string]$Content
    )
    
    $sha256 = [System.Security.Cryptography.SHA256]::Create()
    $hash = $sha256.ComputeHash([System.Text.Encoding]::UTF8.GetBytes($Content))
    return [BitConverter]::ToString($hash) -replace '-', ''
}
#endregion

#region Baseline Management
function New-SchemaBaseline {
    <#
    .SYNOPSIS
        Creates a baseline snapshot of database schema for drift comparison
    
    .DESCRIPTION
        Captures a complete snapshot of all database objects including:
        - Object definitions with hashes
        - Security permissions
        - Configuration settings
        - Index statistics
    
    .PARAMETER SqlInstance
        SQL Server instance name
    
    .PARAMETER Database
        Database name
    
    .PARAMETER BaselineName
        Name for this baseline (e.g., "Production_v1.0")
    
    .EXAMPLE
        New-SchemaBaseline -SqlInstance "PRODSQL01" -Database "AppDB" -BaselineName "Prod_v1.0"
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        
        [Parameter(Mandatory)]
        [string]$Database,
        
        [string]$BaselineName = "Baseline_$(Get-Date -Format 'yyyyMMdd_HHmmss')",
        
        [string]$Description
    )
    
    Write-DriftLog "Creating baseline: $BaselineName for $SqlInstance.$Database" -Level Info -ServerName $SqlInstance -Database $Database
    
    $baseline = [PSCustomObject]@{
        BaselineName   = $BaselineName
        SqlInstance    = $SqlInstance
        Database       = $Database
        CreatedAt      = Get-Date
        CreatedBy      = $env:USERNAME
        Description    = $Description
        Objects        = @()
        Security       = @()
        Configuration  = @()
        TotalObjects   = 0
        BaselineHash   = $null
    }
    
    try {
        # Capture Tables
        Write-DriftLog "  Capturing tables..." -Level Info -ServerName $SqlInstance -Database $Database
        $tables = Get-DbaDbTable -SqlInstance $SqlInstance -Database $Database | Where-Object { $_.Schema -notlike 'sys*' }
        foreach ($table in $tables) {
            $script = $table | Export-DbaScript -Passthru
            $baseline.Objects += [PSCustomObject]@{
                ObjectType  = 'Table'
                Schema      = $table.Schema
                Name        = $table.Name
                FullName    = "[$($table.Schema)].[$($table.Name)]"
                Definition  = $script
                Hash        = Get-ObjectHash -Content $script
                RowCount    = $table.RowCount
                CreateDate  = $table.CreateDate
            }
        }
        
        # Capture Stored Procedures
        Write-DriftLog "  Capturing stored procedures..." -Level Info -ServerName $SqlInstance -Database $Database
        $procs = Get-DbaDbStoredProcedure -SqlInstance $SqlInstance -Database $Database -ExcludeSystemSp
        foreach ($proc in $procs) {
            $script = $proc | Export-DbaScript -Passthru
            $baseline.Objects += [PSCustomObject]@{
                ObjectType = 'StoredProcedure'
                Schema     = $proc.Schema
                Name       = $proc.Name
                FullName   = "[$($proc.Schema)].[$($proc.Name)]"
                Definition = $script
                Hash       = Get-ObjectHash -Content $script
                CreateDate = $proc.CreateDate
                ModifyDate = $proc.DateLastModified
            }
        }
        
        # Capture Views
        Write-DriftLog "  Capturing views..." -Level Info -ServerName $SqlInstance -Database $Database
        $views = Get-DbaDbView -SqlInstance $SqlInstance -Database $Database -ExcludeSystemView
        foreach ($view in $views) {
            $script = $view | Export-DbaScript -Passthru
            $baseline.Objects += [PSCustomObject]@{
                ObjectType = 'View'
                Schema     = $view.Schema
                Name       = $view.Name
                FullName   = "[$($view.Schema)].[$($view.Name)]"
                Definition = $script
                Hash       = Get-ObjectHash -Content $script
                CreateDate = $view.CreateDate
            }
        }
        
        # Capture Functions
        Write-DriftLog "  Capturing functions..." -Level Info -ServerName $SqlInstance -Database $Database
        $funcs = Get-DbaDbUdf -SqlInstance $SqlInstance -Database $Database
        foreach ($func in $funcs) {
            $script = $func | Export-DbaScript -Passthru
            $baseline.Objects += [PSCustomObject]@{
                ObjectType = 'Function'
                Schema     = $func.Schema
                Name       = $func.Name
                FullName   = "[$($func.Schema)].[$($func.Name)]"
                Definition = $script
                Hash       = Get-ObjectHash -Content $script
            }
        }
        
        # Capture Security Permissions
        Write-DriftLog "  Capturing security permissions..." -Level Info -ServerName $SqlInstance -Database $Database
        $permQuery = @"
SELECT 
    dp.state_desc + ' ' + dp.permission_name AS Permission,
    dp.class_desc AS ObjectClass,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(dp.major_id) AS ObjectName,
    prin.name AS GranteeName,
    prin.type_desc AS PrincipalType
FROM sys.database_permissions dp
JOIN sys.database_principals prin ON dp.grantee_principal_id = prin.principal_id
LEFT JOIN sys.objects o ON dp.major_id = o.object_id
WHERE prin.name NOT IN ('dbo', 'guest', 'public', 'INFORMATION_SCHEMA', 'sys')
AND dp.class_desc != 'DATABASE'
ORDER BY prin.name, dp.permission_name
"@
        $perms = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $permQuery
        foreach ($perm in $perms) {
            $permString = "$($perm.Permission) ON [$($perm.SchemaName)].[$($perm.ObjectName)] TO [$($perm.GranteeName)]"
            $baseline.Security += [PSCustomObject]@{
                Permission    = $perm.Permission
                ObjectClass   = $perm.ObjectClass
                SchemaName    = $perm.SchemaName
                ObjectName    = $perm.ObjectName
                GranteeName   = $perm.GranteeName
                PrincipalType = $perm.PrincipalType
                Hash          = Get-ObjectHash -Content $permString
            }
        }
        
        # Capture Configuration
        Write-DriftLog "  Capturing configuration..." -Level Info -ServerName $SqlInstance -Database $Database
        $configQuery = @"
SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
ORDER BY name
"@
        $config = Invoke-DbaQuery -SqlInstance $SqlInstance -Query $configQuery
        foreach ($cfg in $config) {
            $baseline.Configuration += [PSCustomObject]@{
                Name        = $cfg.name
                Value       = $cfg.value
                ValueInUse  = $cfg.value_in_use
                IsDynamic   = $cfg.is_dynamic
                IsAdvanced  = $cfg.is_advanced
            }
        }
        
        $baseline.TotalObjects = $baseline.Objects.Count
        
        # Calculate overall baseline hash
        $allContent = $baseline.Objects | ForEach-Object { $_.Hash } | Out-String
        $baseline.BaselineHash = Get-ObjectHash -Content $allContent
        
        # Save baseline to file
        $baselinePath = Join-Path $Script:BaselinePath $SqlInstance.Replace('\', '_')
        if (-not (Test-Path $baselinePath)) {
            New-Item -Path $baselinePath -ItemType Directory -Force | Out-Null
        }
        
        $baselineFile = Join-Path $baselinePath "$Database`_$BaselineName.json"
        $baseline | ConvertTo-Json -Depth 10 | Out-File -FilePath $baselineFile -Encoding UTF8
        
        # Log to database
        $logQuery = @"
INSERT INTO DBAOpsRepository.dbo.SchemaBaseline 
(BaselineName, SqlInstance, DatabaseName, CreatedAt, CreatedBy, TotalObjects, BaselineHash, FilePath)
VALUES ('$BaselineName', '$SqlInstance', '$Database', GETDATE(), '$env:USERNAME', $($baseline.TotalObjects), 
        '$($baseline.BaselineHash)', '$baselineFile')
"@
        Invoke-DbaQuery -SqlInstance $Script:CentralServer -Query $logQuery -EnableException
        
        Write-DriftLog "Baseline created: $($baseline.TotalObjects) objects captured" -Level Success -ServerName $SqlInstance -Database $Database
        
    } catch {
        Write-DriftLog "Baseline creation failed: $($_.Exception.Message)" -Level Error -ServerName $SqlInstance -Database $Database
        throw
    }
    
    return $baseline
}

function Get-SchemaBaseline {
    <#
    .SYNOPSIS
        Retrieves an existing baseline
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        
        [Parameter(Mandatory)]
        [string]$Database,
        
        [string]$BaselineName
    )
    
    $baselinePath = Join-Path $Script:BaselinePath $SqlInstance.Replace('\', '_')
    
    if ($BaselineName) {
        $baselineFile = Join-Path $baselinePath "$Database`_$BaselineName.json"
    } else {
        # Get most recent baseline
        $baselineFile = Get-ChildItem -Path $baselinePath -Filter "$Database`_*.json" | 
            Sort-Object LastWriteTime -Descending | 
            Select-Object -First 1 -ExpandProperty FullName
    }
    
    if (Test-Path $baselineFile) {
        return Get-Content $baselineFile | ConvertFrom-Json
    }
    
    return $null
}
#endregion

#region Drift Detection
function Test-SchemaDrift {
    <#
    .SYNOPSIS
        Compares current database state against baseline to detect drift
    
    .DESCRIPTION
        Performs comprehensive drift detection including:
        - Schema object changes (create, alter, drop)
        - Security permission changes
        - Configuration drift
    
    .PARAMETER SqlInstance
        SQL Server instance name
    
    .PARAMETER Database
        Database name
    
    .PARAMETER Baseline
        Baseline object to compare against
    
    .EXAMPLE
        $drift = Test-SchemaDrift -SqlInstance "PRODSQL01" -Database "AppDB"
    #>
    [CmdletBinding()]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        
        [Parameter(Mandatory)]
        [string]$Database,
        
        [PSCustomObject]$Baseline,
        
        [string]$BaselineName
    )
    
    Write-DriftLog "═══════════════════════════════════════════════════════════════" -Level Info
    Write-DriftLog "  DBAOps Drift Detection: $SqlInstance.$Database" -Level Info
    Write-DriftLog "═══════════════════════════════════════════════════════════════" -Level Info
    
    # Get baseline if not provided
    if (-not $Baseline) {
        $Baseline = Get-SchemaBaseline -SqlInstance $SqlInstance -Database $Database -BaselineName $BaselineName
        if (-not $Baseline) {
            throw "No baseline found for $SqlInstance.$Database. Create one with New-SchemaBaseline first."
        }
    }
    
    $driftReport = [PSCustomObject]@{
        SqlInstance      = $SqlInstance
        Database         = $Database
        BaselineName     = $Baseline.BaselineName
        BaselineDate     = $Baseline.CreatedAt
        ScanTime         = Get-Date
        HasDrift         = $false
        DriftSeverity    = 'None'
        DriftItems       = @()
        AddedObjects     = @()
        ModifiedObjects  = @()
        DeletedObjects   = @()
        SecurityChanges  = @()
        ConfigChanges    = @()
        Summary          = $null
    }
    
    try {
        # Create current snapshot for comparison
        Write-DriftLog "Creating current snapshot..." -Level Info -ServerName $SqlInstance -Database $Database
        $currentSnapshot = New-SchemaBaseline -SqlInstance $SqlInstance -Database $Database -BaselineName "TempSnapshot_$(Get-Date -Format 'yyyyMMddHHmmss')"
        
        # Compare Objects
        Write-DriftLog "Comparing schema objects..." -Level Info -ServerName $SqlInstance -Database $Database
        
        $baselineObjects = @{}
        foreach ($obj in $Baseline.Objects) {
            $baselineObjects[$obj.FullName] = $obj
        }
        
        $currentObjects = @{}
        foreach ($obj in $currentSnapshot.Objects) {
            $currentObjects[$obj.FullName] = $obj
        }
        
        # Find added objects
        foreach ($objName in $currentObjects.Keys) {
            if (-not $baselineObjects.ContainsKey($objName)) {
                $obj = $currentObjects[$objName]
                $driftReport.AddedObjects += [PSCustomObject]@{
                    DriftType   = 'Added'
                    ObjectType  = $obj.ObjectType
                    ObjectName  = $obj.FullName
                    Severity    = 'Warning'
                    DetectedAt  = Get-Date
                    Details     = "New object created after baseline"
                }
                Write-DriftLog "  DRIFT: New $($obj.ObjectType) detected: $($obj.FullName)" -Level Warning -ServerName $SqlInstance -Database $Database
            }
        }
        
        # Find deleted objects
        foreach ($objName in $baselineObjects.Keys) {
            if (-not $currentObjects.ContainsKey($objName)) {
                $obj = $baselineObjects[$objName]
                $driftReport.DeletedObjects += [PSCustomObject]@{
                    DriftType   = 'Deleted'
                    ObjectType  = $obj.ObjectType
                    ObjectName  = $obj.FullName
                    Severity    = 'Critical'
                    DetectedAt  = Get-Date
                    Details     = "Object removed since baseline"
                }
                Write-DriftLog "  DRIFT: $($obj.ObjectType) deleted: $($obj.FullName)" -Level Critical -ServerName $SqlInstance -Database $Database
            }
        }
        
        # Find modified objects
        foreach ($objName in $currentObjects.Keys) {
            if ($baselineObjects.ContainsKey($objName)) {
                $baselineObj = $baselineObjects[$objName]
                $currentObj = $currentObjects[$objName]
                
                if ($baselineObj.Hash -ne $currentObj.Hash) {
                    $driftReport.ModifiedObjects += [PSCustomObject]@{
                        DriftType      = 'Modified'
                        ObjectType     = $currentObj.ObjectType
                        ObjectName     = $currentObj.FullName
                        Severity       = 'Warning'
                        DetectedAt     = Get-Date
                        BaselineHash   = $baselineObj.Hash
                        CurrentHash    = $currentObj.Hash
                        Details        = "Object definition changed"
                    }
                    Write-DriftLog "  DRIFT: $($currentObj.ObjectType) modified: $($currentObj.FullName)" -Level Warning -ServerName $SqlInstance -Database $Database
                }
            }
        }
        
        # Compare Security Permissions
        Write-DriftLog "Comparing security permissions..." -Level Info -ServerName $SqlInstance -Database $Database
        
        $baselinePerms = @{}
        foreach ($perm in $Baseline.Security) {
            $key = "$($perm.Permission)|$($perm.ObjectName)|$($perm.GranteeName)"
            $baselinePerms[$key] = $perm
        }
        
        $currentPerms = @{}
        foreach ($perm in $currentSnapshot.Security) {
            $key = "$($perm.Permission)|$($perm.ObjectName)|$($perm.GranteeName)"
            $currentPerms[$key] = $perm
        }
        
        # Find permission changes
        foreach ($key in $currentPerms.Keys) {
            if (-not $baselinePerms.ContainsKey($key)) {
                $perm = $currentPerms[$key]
                $driftReport.SecurityChanges += [PSCustomObject]@{
                    DriftType   = 'PermissionAdded'
                    Permission  = $perm.Permission
                    ObjectName  = $perm.ObjectName
                    GranteeName = $perm.GranteeName
                    Severity    = 'Warning'
                    DetectedAt  = Get-Date
                }
                Write-DriftLog "  DRIFT: New permission: $($perm.Permission) on $($perm.ObjectName) to $($perm.GranteeName)" -Level Warning -ServerName $SqlInstance -Database $Database
            }
        }
        
        foreach ($key in $baselinePerms.Keys) {
            if (-not $currentPerms.ContainsKey($key)) {
                $perm = $baselinePerms[$key]
                $driftReport.SecurityChanges += [PSCustomObject]@{
                    DriftType   = 'PermissionRemoved'
                    Permission  = $perm.Permission
                    ObjectName  = $perm.ObjectName
                    GranteeName = $perm.GranteeName
                    Severity    = 'Critical'
                    DetectedAt  = Get-Date
                }
                Write-DriftLog "  DRIFT: Permission removed: $($perm.Permission) on $($perm.ObjectName) from $($perm.GranteeName)" -Level Critical -ServerName $SqlInstance -Database $Database
            }
        }
        
        # Compile results
        $driftReport.DriftItems = @($driftReport.AddedObjects) + @($driftReport.ModifiedObjects) + @($driftReport.DeletedObjects) + @($driftReport.SecurityChanges)
        $driftReport.HasDrift = ($driftReport.DriftItems.Count -gt 0)
        
        # Determine severity
        if ($driftReport.DeletedObjects.Count -gt 0 -or ($driftReport.SecurityChanges | Where-Object { $_.DriftType -eq 'PermissionRemoved' }).Count -gt 0) {
            $driftReport.DriftSeverity = 'Critical'
        } elseif ($driftReport.DriftItems.Count -gt 0) {
            $driftReport.DriftSeverity = 'Warning'
        }
        
        # Create summary
        $driftReport.Summary = [PSCustomObject]@{
            TotalDriftItems    = $driftReport.DriftItems.Count
            AddedObjects       = $driftReport.AddedObjects.Count
            ModifiedObjects    = $driftReport.ModifiedObjects.Count
            DeletedObjects     = $driftReport.DeletedObjects.Count
            SecurityChanges    = $driftReport.SecurityChanges.Count
            ConfigChanges      = $driftReport.ConfigChanges.Count
        }
        
        # Log to database
        if ($driftReport.HasDrift) {
            $logQuery = @"
INSERT INTO DBAOpsRepository.dbo.DriftDetectionHistory 
(SqlInstance, DatabaseName, BaselineName, ScanTime, HasDrift, DriftSeverity, DriftCount, AddedCount, ModifiedCount, DeletedCount, SecurityCount)
VALUES ('$SqlInstance', '$Database', '$($Baseline.BaselineName)', GETDATE(), 1, '$($driftReport.DriftSeverity)', 
        $($driftReport.DriftItems.Count), $($driftReport.AddedObjects.Count), $($driftReport.ModifiedObjects.Count), 
        $($driftReport.DeletedObjects.Count), $($driftReport.SecurityChanges.Count))
"@
            Invoke-DbaQuery -SqlInstance $Script:CentralServer -Query $logQuery -EnableException
        }
        
        # Cleanup temp snapshot file
        $tempFile = Join-Path $Script:BaselinePath "$SqlInstance\$Database`_TempSnapshot_*.json"
        Get-ChildItem -Path $tempFile -ErrorAction SilentlyContinue | Remove-Item -Force
        
    } catch {
        Write-DriftLog "Drift detection failed: $($_.Exception.Message)" -Level Error -ServerName $SqlInstance -Database $Database
        throw
    }
    
    # Final summary
    Write-DriftLog "═══════════════════════════════════════════════════════════════" -Level Info
    if ($driftReport.HasDrift) {
        Write-DriftLog "  DRIFT DETECTED: $($driftReport.DriftItems.Count) items changed" -Level Warning
        Write-DriftLog "  Severity: $($driftReport.DriftSeverity)" -Level $(if ($driftReport.DriftSeverity -eq 'Critical') { 'Critical' } else { 'Warning' })
    } else {
        Write-DriftLog "  NO DRIFT DETECTED - Schema matches baseline" -Level Success
    }
    Write-DriftLog "═══════════════════════════════════════════════════════════════" -Level Info
    
    return $driftReport
}
#endregion

#region Remediation
function Invoke-DriftRemediation {
    <#
    .SYNOPSIS
        Generates and optionally applies remediation scripts for detected drift
    
    .DESCRIPTION
        Creates SQL scripts to restore database state to baseline:
        - DROP scripts for added objects
        - ALTER/CREATE scripts for modified objects
        - CREATE scripts for deleted objects
        - GRANT/REVOKE scripts for security changes
    
    .PARAMETER DriftReport
        Output from Test-SchemaDrift
    
    .PARAMETER Apply
        Apply remediation scripts immediately
    
    .PARAMETER Force
        Skip confirmation prompts
    
    .EXAMPLE
        $drift = Test-SchemaDrift -SqlInstance "PRODSQL01" -Database "AppDB"
        Invoke-DriftRemediation -DriftReport $drift -Apply
    #>
    [CmdletBinding(SupportsShouldProcess, ConfirmImpact = 'High')]
    [OutputType([PSCustomObject])]
    param(
        [Parameter(Mandatory)]
        [PSCustomObject]$DriftReport,
        
        [switch]$Apply,
        
        [switch]$Force,
        
        [string]$OutputPath = "E:\DBAOps\Remediation"
    )
    
    Write-DriftLog "Generating remediation scripts for $($DriftReport.SqlInstance).$($DriftReport.Database)" -Level Info
    
    $remediation = [PSCustomObject]@{
        DriftReport      = $DriftReport
        Scripts          = @()
        GeneratedAt      = Get-Date
        AppliedAt        = $null
        Status           = 'Generated'
        OutputPath       = $null
    }
    
    if (-not $DriftReport.HasDrift) {
        Write-DriftLog "No drift to remediate" -Level Info
        return $remediation
    }
    
    $timestamp = Get-Date -Format 'yyyyMMddHHmmss'
    $scriptPath = Join-Path $OutputPath "$($DriftReport.Database)_Remediation_$timestamp.sql"
    
    if (-not (Test-Path $OutputPath)) {
        New-Item -Path $OutputPath -ItemType Directory -Force | Out-Null
    }
    
    $scriptContent = @"
/*
================================================================================
  DBAOps Ultimate - Drift Remediation Script
================================================================================
  Server:    $($DriftReport.SqlInstance)
  Database:  $($DriftReport.Database)
  Baseline:  $($DriftReport.BaselineName)
  Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
  
  REVIEW CAREFULLY BEFORE APPLYING
================================================================================
*/

SET XACT_ABORT ON;
SET NOCOUNT ON;

USE [$($DriftReport.Database)];
GO

PRINT 'Starting drift remediation...';
PRINT '================================================';

BEGIN TRANSACTION DriftRemediation;

BEGIN TRY

"@
    
    # Handle added objects (may need to be dropped)
    if ($DriftReport.AddedObjects.Count -gt 0) {
        $scriptContent += @"

    -- ============================================
    -- ADDED OBJECTS (Consider removal)
    -- WARNING: These objects were added after baseline
    -- Uncomment to remove unauthorized additions
    -- ============================================
    
"@
        foreach ($obj in $DriftReport.AddedObjects) {
            $scriptContent += "    -- PRINT 'Removing unauthorized $($obj.ObjectType): $($obj.ObjectName)'`n"
            $scriptContent += "    -- DROP $($obj.ObjectType) $($obj.ObjectName);`n"
            $scriptContent += "    -- GO`n`n"
        }
    }
    
    # Handle deleted objects (need to be restored)
    if ($DriftReport.DeletedObjects.Count -gt 0) {
        $scriptContent += @"

    -- ============================================
    -- DELETED OBJECTS (Must be restored)
    -- These objects were removed and need recreation
    -- ============================================
    
"@
        # Get baseline to retrieve original definitions
        $baseline = Get-SchemaBaseline -SqlInstance $DriftReport.SqlInstance -Database $DriftReport.Database -BaselineName $DriftReport.BaselineName
        
        foreach ($deleted in $DriftReport.DeletedObjects) {
            $originalObj = $baseline.Objects | Where-Object { $_.FullName -eq $deleted.ObjectName }
            if ($originalObj -and $originalObj.Definition) {
                $scriptContent += "    PRINT 'Restoring $($deleted.ObjectType): $($deleted.ObjectName)';`n"
                $scriptContent += "    $($originalObj.Definition)`n"
                $scriptContent += "    GO`n`n"
            } else {
                $scriptContent += "    -- TODO: Restore $($deleted.ObjectType): $($deleted.ObjectName)`n"
                $scriptContent += "    -- Original definition not available in baseline`n`n"
            }
        }
    }
    
    # Handle modified objects (need to be reverted)
    if ($DriftReport.ModifiedObjects.Count -gt 0) {
        $scriptContent += @"

    -- ============================================
    -- MODIFIED OBJECTS (Revert to baseline)
    -- These objects were changed and need restoration
    -- ============================================
    
"@
        $baseline = Get-SchemaBaseline -SqlInstance $DriftReport.SqlInstance -Database $DriftReport.Database -BaselineName $DriftReport.BaselineName
        
        foreach ($modified in $DriftReport.ModifiedObjects) {
            $originalObj = $baseline.Objects | Where-Object { $_.FullName -eq $modified.ObjectName }
            if ($originalObj -and $originalObj.Definition) {
                $scriptContent += "    PRINT 'Reverting $($modified.ObjectType): $($modified.ObjectName)';`n"
                # Use CREATE OR ALTER where possible
                $definition = $originalObj.Definition -replace 'CREATE\s+(PROCEDURE|FUNCTION|VIEW|TRIGGER)', 'CREATE OR ALTER $1'
                $scriptContent += "    $definition`n"
                $scriptContent += "    GO`n`n"
            }
        }
    }
    
    # Handle security changes
    if ($DriftReport.SecurityChanges.Count -gt 0) {
        $scriptContent += @"

    -- ============================================
    -- SECURITY CHANGES
    -- ============================================
    
"@
        foreach ($secChange in $DriftReport.SecurityChanges) {
            if ($secChange.DriftType -eq 'PermissionAdded') {
                $scriptContent += "    -- Revoke unauthorized permission`n"
                $scriptContent += "    REVOKE $($secChange.Permission -replace 'GRANT ', '') ON [$($secChange.ObjectName)] FROM [$($secChange.GranteeName)];`n"
                $scriptContent += "    GO`n`n"
            } elseif ($secChange.DriftType -eq 'PermissionRemoved') {
                $scriptContent += "    -- Restore required permission`n"
                $scriptContent += "    $($secChange.Permission) ON [$($secChange.ObjectName)] TO [$($secChange.GranteeName)];`n"
                $scriptContent += "    GO`n`n"
            }
        }
    }
    
    $scriptContent += @"

    COMMIT TRANSACTION DriftRemediation;
    PRINT '================================================';
    PRINT 'Drift remediation completed successfully';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION DriftRemediation;
    
    PRINT 'ERROR: Remediation failed!';
    PRINT 'Error: ' + ERROR_MESSAGE();
    
    THROW;
END CATCH
GO
"@
    
    # Save script
    $scriptContent | Out-File -FilePath $scriptPath -Encoding UTF8
    $remediation.OutputPath = $scriptPath
    $remediation.Scripts += $scriptPath
    
    Write-DriftLog "Remediation script generated: $scriptPath" -Level Success
    
    # Apply if requested
    if ($Apply) {
        if ($Force -or $PSCmdlet.ShouldProcess($DriftReport.Database, "Apply drift remediation")) {
            Write-DriftLog "Applying remediation script..." -Level Warning
            try {
                Invoke-DbaQuery -SqlInstance $DriftReport.SqlInstance -Database $DriftReport.Database -File $scriptPath -EnableException
                $remediation.AppliedAt = Get-Date
                $remediation.Status = 'Applied'
                Write-DriftLog "Remediation applied successfully" -Level Success
            } catch {
                $remediation.Status = 'Failed'
                Write-DriftLog "Remediation failed: $($_.Exception.Message)" -Level Error
                throw
            }
        }
    }
    
    return $remediation
}
#endregion

#region Continuous Monitoring
function Start-DriftMonitor {
    <#
    .SYNOPSIS
        Starts continuous drift monitoring for specified databases
    
    .EXAMPLE
        Start-DriftMonitor -SqlInstance "PRODSQL01" -Database "AppDB" -IntervalMinutes 60
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string[]]$SqlInstance,
        
        [Parameter(Mandatory)]
        [string[]]$Database,
        
        [int]$IntervalMinutes = 60,
        
        [switch]$SendAlerts,
        
        [string]$AlertEmail
    )
    
    Write-DriftLog "Starting drift monitor (Interval: $IntervalMinutes minutes)" -Level Info
    Write-DriftLog "Monitoring: $($SqlInstance -join ', ') / $($Database -join ', ')" -Level Info
    
    while ($true) {
        foreach ($server in $SqlInstance) {
            foreach ($db in $Database) {
                try {
                    $drift = Test-SchemaDrift -SqlInstance $server -Database $db
                    
                    if ($drift.HasDrift -and $SendAlerts) {
                        # Send alert
                        $subject = "DBAOps DRIFT ALERT: $server.$db [$($drift.DriftSeverity)]"
                        $body = @"
Drift detected on $server.$db

Severity: $($drift.DriftSeverity)
Total Changes: $($drift.DriftItems.Count)
Added Objects: $($drift.AddedObjects.Count)
Modified Objects: $($drift.ModifiedObjects.Count)
Deleted Objects: $($drift.DeletedObjects.Count)
Security Changes: $($drift.SecurityChanges.Count)

Please review and take appropriate action.
"@
                        if ($AlertEmail) {
                            Send-MailMessage -To $AlertEmail -Subject $subject -Body $body -SmtpServer "smtp.local"
                        }
                    }
                } catch {
                    Write-DriftLog "Monitoring error for $server.$db : $($_.Exception.Message)" -Level Error
                }
            }
        }
        
        Write-DriftLog "Next scan in $IntervalMinutes minutes..." -Level Info
        Start-Sleep -Seconds ($IntervalMinutes * 60)
    }
}
#endregion

#region Module Exports
Export-ModuleMember -Function @(
    'New-SchemaBaseline',
    'Get-SchemaBaseline',
    'Test-SchemaDrift',
    'Invoke-DriftRemediation',
    'Start-DriftMonitor'
)
#endregion
