# DBAOps-DailyCleanup.ps1
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER01",
    [string]$Database = "DBAOpsRepository",
    [int]$LogRetentionDays = 30,
    [int]$DataRetentionDays = 90
)

Write-Host "=== DBAOps Daily Cleanup ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# 1. Cleanup log files older than retention
$LogPath = "E:\DBAOps\Logs"
if (Test-Path $LogPath) {
    $oldLogs = Get-ChildItem -Path $LogPath -Filter "*.log" -ErrorAction SilentlyContinue | 
               Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$LogRetentionDays) }
    
    if ($oldLogs) {
        $oldLogs | Remove-Item -Force -ErrorAction SilentlyContinue
        Write-Host "  Removed $($oldLogs.Count) old log files" -ForegroundColor Green
    } else {
        Write-Host "  No old log files to remove" -ForegroundColor Gray
    }
}

# 2. Cleanup old data from repository tables
try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()

    $cleanupQueries = @(
        "DELETE FROM perf.ExpensiveQueries WHERE CollectedAt < DATEADD(DAY, -$DataRetentionDays, GETDATE())",
        "DELETE FROM logs.SQLErrorLog WHERE CollectedAt < DATEADD(DAY, -$DataRetentionDays, GETDATE())",
        "DELETE FROM audit.BlockingEvents WHERE CollectedAt < DATEADD(DAY, -$DataRetentionDays, GETDATE())",
        "DELETE FROM health.ServerHealth WHERE CollectedAt < DATEADD(DAY, -$DataRetentionDays, GETDATE())",
        "DELETE FROM alerts.RecentAlerts WHERE CollectedAt < DATEADD(DAY, -$DataRetentionDays, GETDATE())"
    )

    foreach ($query in $cleanupQueries) {
        try {
            $cmd = $connection.CreateCommand()
            $cmd.CommandText = $query
            $cmd.CommandTimeout = 300
            $rows = $cmd.ExecuteNonQuery()
            if ($rows -gt 0) {
                Write-Host "  Cleaned $rows rows: $($query.Substring(12, 30))..." -ForegroundColor Green
            }
        } catch {
            # Table might not exist, skip
        }
    }

    $connection.Close()
    Write-Host "  Database cleanup complete" -ForegroundColor Green
}
catch {
    Write-Host "  Database cleanup error: $_" -ForegroundColor Yellow
}

Write-Host ""
Write-Host "=== Daily Cleanup Complete ===" -ForegroundColor Cyan
Write-Host "Finished: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
