/*
 * DBAOps Sovereign™ — Schema v8: Seven Differentiators
 *
 * 1. Data Sovereignty Guarantee (DataFlowTracker + AirGapMode)
 * 2. Cryptographic Chain of Custody (RFC 3161 timestamps)
 * 3. Intelligent Data Classification (DataDiscoveryEngine)
 * 4. Predictive Cost Intelligence (LicenseOptimizer + CloudCostProjection)
 * 5. Self-Healing Infrastructure (50+ pre-built actions)
 * 6. Unified Estate Intelligence (multi-engine)
 * 7. Knowledge Amplification System (AIMentor + ContextualHelp)
 */

USE SovereignRepository;
GO

-- ===== 1. Data Sovereignty Guarantee =====

IF OBJECT_ID('sovereignty.DataFlowLog', 'U') IS NULL
BEGIN
  CREATE TABLE sovereignty.DataFlowLog (
    FlowId          BIGINT IDENTITY(1,1) PRIMARY KEY,
    TenantId        UNIQUEIDENTIFIER NULL,
    FlowDirection   NVARCHAR(16)  NOT NULL, -- Inbound, Outbound, Internal
    SourceComponent NVARCHAR(128) NOT NULL,
    TargetComponent NVARCHAR(128) NOT NULL,
    DataCategory    NVARCHAR(64)  NOT NULL, -- Telemetry, PII, PHI, PCI, Credentials, Configuration
    DataSizeBytes   BIGINT        NULL,
    EncryptionUsed  BIT           NOT NULL DEFAULT 1,
    AllowedByPolicy BIT           NOT NULL DEFAULT 1,
    BlockedReason   NVARCHAR(256) NULL,
    LoggedAt        DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_DataFlow_Dir ON sovereignty.DataFlowLog (FlowDirection, LoggedAt DESC);
END;
GO

IF OBJECT_ID('sovereignty.AirGapConfig', 'U') IS NULL
BEGIN
  CREATE TABLE sovereignty.AirGapConfig (
    ConfigId             INT IDENTITY(1,1) PRIMARY KEY,
    AirGapEnabled        BIT NOT NULL DEFAULT 0,
    AllowOutboundTelemetry BIT NOT NULL DEFAULT 0,
    AllowLicensePhone    BIT NOT NULL DEFAULT 0,
    AllowUpdateCheck     BIT NOT NULL DEFAULT 0,
    AllowedExternalHosts NVARCHAR(MAX) NULL, -- JSON array
    OfflineLicenseKey    NVARCHAR(MAX) NULL,
    LastPolicyUpdate     DATETIME2 NULL,
    EnabledBy            NVARCHAR(256) NULL,
    EnabledAt            DATETIME2 NULL
  );
END;
GO

-- ===== 2. Cryptographic Chain of Custody (enhance AuditChain) =====

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('sec.AuditChain') AND name = 'RFC3161Timestamp')
BEGIN
  ALTER TABLE sec.AuditChain ADD RFC3161Timestamp VARBINARY(MAX) NULL;
  ALTER TABLE sec.AuditChain ADD TimestampAuthority NVARCHAR(256) NULL;
  ALTER TABLE sec.AuditChain ADD ChainVersion INT NOT NULL DEFAULT 1;
  ALTER TABLE sec.AuditChain ADD IsCourtAdmissible BIT NOT NULL DEFAULT 0;
  ALTER TABLE sec.AuditChain ADD EvidenceFormat NVARCHAR(32) NULL; -- RFC3161, PKCS7, Plain
END;
GO

IF OBJECT_ID('sec.ChainVerification', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ChainVerification (
    VerificationId   BIGINT IDENTITY(1,1) PRIMARY KEY,
    VerifiedFromId   BIGINT NOT NULL,
    VerifiedToId     BIGINT NOT NULL,
    VerifiedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    VerifiedBy       NVARCHAR(256) NOT NULL,
    IntegrityResult  NVARCHAR(16) NOT NULL, -- Valid, Tampered, Broken
    BrokenAtChainId  BIGINT NULL,
    VerificationHash CHAR(64) NOT NULL,
    Report           NVARCHAR(MAX) NULL
  );
END;
GO

-- ===== 3. Intelligent Data Classification (Discovery Engine) =====

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'classification')
  EXEC('CREATE SCHEMA classification');
GO

IF OBJECT_ID('classification.DiscoveryRun', 'U') IS NULL
BEGIN
  CREATE TABLE classification.DiscoveryRun (
    RunId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    DatabaseName    NVARCHAR(256) NULL,
    StartedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt     DATETIME2 NULL,
    TablesScanned   INT NOT NULL DEFAULT 0,
    ColumnsScanned  INT NOT NULL DEFAULT 0,
    FindingsCount   INT NOT NULL DEFAULT 0,
    ScanMode        NVARCHAR(32) NOT NULL DEFAULT 'Full', -- Full, Incremental, Targeted
    Status          NVARCHAR(16) NOT NULL DEFAULT 'Running',
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('classification.DiscoveryFinding', 'U') IS NULL
BEGIN
  CREATE TABLE classification.DiscoveryFinding (
    FindingId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RunId           UNIQUEIDENTIFIER NOT NULL,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    DatabaseName    NVARCHAR(256) NOT NULL,
    SchemaName      NVARCHAR(128) NOT NULL,
    TableName       NVARCHAR(256) NOT NULL,
    ColumnName      NVARCHAR(256) NOT NULL,
    DataType        NVARCHAR(64)  NOT NULL,
    ClassificationType NVARCHAR(32) NOT NULL, -- PII, PHI, PCI, Financial, Credential
    SensitivityLevel   NVARCHAR(16) NOT NULL, -- Critical, High, Medium, Low
    DetectionMethod    NVARCHAR(32) NOT NULL, -- Pattern, Sampling, ML, Manual
    ConfidenceScore    INT NOT NULL DEFAULT 0,
    SampleMatch     NVARCHAR(256) NULL, -- masked sample
    RegulatoryFrameworks NVARCHAR(256) NULL,
    MaskingRecommendation NVARCHAR(256) NULL,
    ApprovalStatus  NVARCHAR(16) NOT NULL DEFAULT 'Pending', -- Pending, Confirmed, FalsePositive, Excluded
    ReviewedBy      NVARCHAR(256) NULL,
    FoundAt         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (RunId) REFERENCES classification.DiscoveryRun(RunId)
  );
  CREATE NONCLUSTERED INDEX IX_DiscFinding_Type ON classification.DiscoveryFinding (ClassificationType, SensitivityLevel);
END;
GO

IF OBJECT_ID('classification.DataFlowMap', 'U') IS NULL
BEGIN
  CREATE TABLE classification.DataFlowMap (
    MapId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    SourceServerId  UNIQUEIDENTIFIER NOT NULL,
    SourceDatabase  NVARCHAR(256) NOT NULL,
    TargetServerId  UNIQUEIDENTIFIER NULL,
    TargetDatabase  NVARCHAR(256) NULL,
    TargetExternal  NVARCHAR(256) NULL,
    DataCategories  NVARCHAR(256) NOT NULL, -- JSON array
    FlowMechanism   NVARCHAR(64) NOT NULL, -- Replication, ETL, Backup, Export, API
    ContainsSensitive BIT NOT NULL DEFAULT 0,
    IsApproved      BIT NOT NULL DEFAULT 0,
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== 4. Predictive Cost Intelligence (FinOps) =====

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'finops')
  EXEC('CREATE SCHEMA finops');
GO

IF OBJECT_ID('finops.LicenseOptimization', 'U') IS NULL
BEGIN
  CREATE TABLE finops.LicenseOptimization (
    OptId             UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId          UNIQUEIDENTIFIER NOT NULL,
    CurrentEdition    NVARCHAR(64) NOT NULL,
    CurrentLicenseType NVARCHAR(32) NOT NULL,
    CurrentCoreCount  INT NOT NULL,
    CurrentAnnualCost DECIMAL(18,2) NOT NULL,
    FeaturesInUse     NVARCHAR(MAX) NULL, -- JSON
    FeaturesAvailable NVARCHAR(MAX) NULL, -- JSON
    RecommendedEdition NVARCHAR(64) NULL,
    RecommendedCores   INT NULL,
    ProjectedAnnualCost DECIMAL(18,2) NULL,
    AnnualSavings     DECIMAL(18,2) NULL,
    Confidence        INT NOT NULL DEFAULT 0,
    Rationale         NVARCHAR(MAX) NULL,
    AssessedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('finops.CloudCostProjection', 'U') IS NULL
BEGIN
  CREATE TABLE finops.CloudCostProjection (
    ProjectionId     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId         UNIQUEIDENTIFIER NOT NULL,
    CloudProvider    NVARCHAR(32) NOT NULL, -- Azure, AWS, GCP
    ServiceType      NVARCHAR(64) NOT NULL, -- SQL MI, RDS, CloudSQL, AzureSQL
    EstimatedMonthlyCost DECIMAL(18,2) NOT NULL,
    CurrentMonthlyCost   DECIMAL(18,2) NOT NULL,
    MigrationComplexity  NVARCHAR(16) NOT NULL, -- Low, Medium, High
    BreakEvenMonths  INT NULL,
    Assumptions      NVARCHAR(MAX) NULL, -- JSON
    ProjectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('finops.CostForecast', 'U') IS NULL
BEGIN
  CREATE TABLE finops.CostForecast (
    ForecastId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ForecastPeriod   NVARCHAR(16) NOT NULL, -- Q1-2026, FY2027, etc.
    Category         NVARCHAR(64) NOT NULL, -- Licensing, Hardware, Cloud, Maintenance
    CurrentSpend     DECIMAL(18,2) NOT NULL,
    ProjectedSpend   DECIMAL(18,2) NOT NULL,
    GrowthRate       DECIMAL(5,2) NULL,
    RiskFactors      NVARCHAR(MAX) NULL, -- JSON
    Recommendations  NVARCHAR(MAX) NULL,
    GeneratedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== 5. Self-Healing Infrastructure =====

IF OBJECT_ID('ops.SelfHealingAction', 'U') IS NULL
BEGIN
  CREATE TABLE ops.SelfHealingAction (
    ActionId         INT IDENTITY(1,1) PRIMARY KEY,
    ActionCode       NVARCHAR(64)  NOT NULL UNIQUE,
    ActionName       NVARCHAR(256) NOT NULL,
    Category         NVARCHAR(64)  NOT NULL,
    Engine           NVARCHAR(64)  NOT NULL DEFAULT 'SQL Server',
    RiskLevel        NVARCHAR(16)  NOT NULL DEFAULT 'Low',
    RequiresApproval BIT NOT NULL DEFAULT 0,
    MaintenanceWindowOnly BIT NOT NULL DEFAULT 0,
    MaxConcurrent    INT NOT NULL DEFAULT 1,
    TriggerCondition NVARCHAR(MAX) NOT NULL, -- JSON
    ActionScript     NVARCHAR(MAX) NOT NULL,
    RollbackScript   NVARCHAR(MAX) NULL,
    PreflightChecks  NVARCHAR(MAX) NULL, -- JSON
    IsEnabled        BIT NOT NULL DEFAULT 1,
    SuccessCount     INT NOT NULL DEFAULT 0,
    FailureCount     INT NOT NULL DEFAULT 0,
    AvgDurationSec   INT NULL,
    CreatedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('ops.SelfHealingExecution', 'U') IS NULL
BEGIN
  CREATE TABLE ops.SelfHealingExecution (
    ExecutionId      BIGINT IDENTITY(1,1) PRIMARY KEY,
    ActionId         INT NOT NULL,
    ServerId         UNIQUEIDENTIFIER NOT NULL,
    DatabaseName     NVARCHAR(256) NULL,
    TriggeredBy      NVARCHAR(64) NOT NULL DEFAULT 'SYSTEM', -- SYSTEM, User, Schedule
    TriggerReason    NVARCHAR(512) NOT NULL,
    ApprovedBy       NVARCHAR(256) NULL,
    ApprovedAt       DATETIME2 NULL,
    StartedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt      DATETIME2 NULL,
    DurationSec      INT NULL,
    Status           NVARCHAR(16) NOT NULL DEFAULT 'Running',
    ScriptExecuted   NVARCHAR(MAX) NULL,
    ResultJson       NVARCHAR(MAX) NULL,
    RollbackExecuted BIT NOT NULL DEFAULT 0,
    RollbackResult   NVARCHAR(MAX) NULL,
    ValueSavedUSD    DECIMAL(18,2) NULL,
    TimeSavedMin     DECIMAL(10,2) NULL,
    FOREIGN KEY (ActionId) REFERENCES ops.SelfHealingAction(ActionId),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_SHExec_Status ON ops.SelfHealingExecution (Status, StartedAt DESC);
END;
GO

-- ===== 6. Value Tracking (ROI Proof) =====

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'analytics')
  EXEC('CREATE SCHEMA analytics');
GO

IF OBJECT_ID('analytics.ValueEvents', 'U') IS NULL
BEGIN
  CREATE TABLE analytics.ValueEvents (
    EventId          BIGINT IDENTITY(1,1) PRIMARY KEY,
    EventType        NVARCHAR(50) NOT NULL,
    ServerId         UNIQUEIDENTIFIER NULL,
    OccurredAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    TimeSavedMinutes DECIMAL(10,2) NULL,
    DowntimePreventedMinutes DECIMAL(10,2) NULL,
    EstimatedValueUSD DECIMAL(18,2) NULL,
    Description      NVARCHAR(1000) NULL,
    ActionTaken      NVARCHAR(MAX) NULL,
    SourceFeature    NVARCHAR(64) NULL
  );
  CREATE NONCLUSTERED INDEX IX_ValueEvent_Type ON analytics.ValueEvents (EventType, OccurredAt DESC);
END;
GO

IF OBJECT_ID('analytics.ValueConfiguration', 'U') IS NULL
BEGIN
  CREATE TABLE analytics.ValueConfiguration (
    ConfigKey   NVARCHAR(100) PRIMARY KEY,
    ConfigValue NVARCHAR(500) NOT NULL,
    Description NVARCHAR(500) NULL
  );
END;
GO

-- Seed cost assumptions
IF NOT EXISTS (SELECT 1 FROM analytics.ValueConfiguration WHERE ConfigKey = 'DowntimeCostPerHour')
BEGIN
  INSERT INTO analytics.ValueConfiguration VALUES
    ('DowntimeCostPerHour', '9000', 'Average cost of downtime per hour in USD'),
    ('DBAHourlyRate', '75', 'Loaded cost of DBA time per hour'),
    ('SQLServerCoreLicenseCost', '15123', 'SQL Server Enterprise per-core license cost'),
    ('OracleProcessorLicenseCost', '47500', 'Oracle Database Enterprise per-processor cost'),
    ('PostgresManagedMonthlyCost', '450', 'Managed PostgreSQL base monthly cost'),
    ('AzureSQLMIMonthlyCostPerCore', '850', 'Azure SQL MI per-vCore monthly cost'),
    ('AWSRDSMonthlyCostPerCore', '780', 'AWS RDS SQL Server per-vCPU monthly cost');
END;
GO

-- ===== 7. Knowledge Amplification System =====

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'learning')
  EXEC('CREATE SCHEMA learning');
GO

IF OBJECT_ID('learning.Modules', 'U') IS NULL
BEGIN
  CREATE TABLE learning.Modules (
    ModuleId      INT IDENTITY(1,1) PRIMARY KEY,
    ModuleCode    NVARCHAR(50) NOT NULL UNIQUE,
    ModuleTitle   NVARCHAR(200) NOT NULL,
    Description   NVARCHAR(MAX) NULL,
    Category      NVARCHAR(64) NOT NULL,
    Engine        NVARCHAR(50) NOT NULL DEFAULT 'All',
    DifficultyLevel INT NOT NULL DEFAULT 1,
    EstimatedMinutes INT NULL,
    VideoUrl      NVARCHAR(500) NULL,
    ContentHtml   NVARCHAR(MAX) NULL,
    Prerequisites NVARCHAR(MAX) NULL, -- JSON array of ModuleCodes
    IsActive      BIT NOT NULL DEFAULT 1,
    CreatedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('learning.UserProgress', 'U') IS NULL
BEGIN
  CREATE TABLE learning.UserProgress (
    UserId      UNIQUEIDENTIFIER NOT NULL,
    ModuleId    INT NOT NULL,
    StartedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt DATETIME2 NULL,
    QuizScore   DECIMAL(5,2) NULL,
    CertificateIssued BIT NOT NULL DEFAULT 0,
    PRIMARY KEY (UserId, ModuleId),
    FOREIGN KEY (ModuleId) REFERENCES learning.Modules(ModuleId)
  );
END;
GO

IF OBJECT_ID('learning.ContextualHelp', 'U') IS NULL
BEGIN
  CREATE TABLE learning.ContextualHelp (
    HelpId       INT IDENTITY(1,1) PRIMARY KEY,
    ContextKey   NVARCHAR(128) NOT NULL UNIQUE,
    MetricOrAlert NVARCHAR(128) NOT NULL,
    Title        NVARCHAR(256) NOT NULL,
    Summary      NVARCHAR(MAX) NOT NULL,
    DetailHtml   NVARCHAR(MAX) NULL,
    WhyItMatters NVARCHAR(MAX) NULL,
    WhatToDoNext NVARCHAR(MAX) NULL,
    RelatedModules NVARCHAR(MAX) NULL, -- JSON array of ModuleCodes
    Severity     NVARCHAR(16) NULL,
    Engine       NVARCHAR(50) NOT NULL DEFAULT 'All'
  );
END;
GO

IF OBJECT_ID('learning.MentorInteraction', 'U') IS NULL
BEGIN
  CREATE TABLE learning.MentorInteraction (
    InteractionId BIGINT IDENTITY(1,1) PRIMARY KEY,
    UserId        UNIQUEIDENTIFIER NOT NULL,
    Context       NVARCHAR(128) NOT NULL,
    Question      NVARCHAR(MAX) NOT NULL,
    Answer        NVARCHAR(MAX) NOT NULL,
    Helpful       BIT NULL,
    AskedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== Seed: 50+ Self-Healing Actions =====

IF NOT EXISTS (SELECT 1 FROM ops.SelfHealingAction WHERE ActionCode = 'SH-KILL-BLOCKING')
BEGIN
  INSERT INTO ops.SelfHealingAction (ActionCode, ActionName, Category, RiskLevel, TriggerCondition, ActionScript, RollbackScript) VALUES
    ('SH-KILL-BLOCKING', 'Kill Blocking Head Session', 'Performance', 'Medium', '{"metric":"blockingSessions","operator":">","threshold":5,"duration":"5m"}', 'KILL @BlockingSpid;', NULL),
    ('SH-CLEAR-PROC-CACHE', 'Clear Procedure Cache for Plan Regression', 'Performance', 'Low', '{"metric":"planRegression","operator":"detected"}', 'DBCC FREEPROCCACHE(@PlanHandle);', NULL),
    ('SH-UPDATE-STATS', 'Update Statistics on Stale Tables', 'Performance', 'Low', '{"metric":"staleStats","operator":">","threshold":1000,"unit":"rows"}', 'UPDATE STATISTICS @Schema.@Table WITH FULLSCAN;', NULL),
    ('SH-REBUILD-INDEX', 'Rebuild Fragmented Index', 'Performance', 'Low', '{"metric":"fragmentation","operator":">","threshold":30}', 'ALTER INDEX @IndexName ON @Schema.@Table REBUILD WITH (ONLINE = ON);', 'ALTER INDEX @IndexName ON @Schema.@Table REORGANIZE;'),
    ('SH-REORGANIZE-INDEX', 'Reorganize Moderately Fragmented Index', 'Performance', 'Low', '{"metric":"fragmentation","operator":"between","min":10,"max":30}', 'ALTER INDEX @IndexName ON @Schema.@Table REORGANIZE;', NULL),
    ('SH-SHRINK-TEMPDB', 'Shrink TempDB After Runaway Growth', 'Space', 'Medium', '{"metric":"tempdbSizeGB","operator":">","threshold":"2x_baseline"}', 'DBCC SHRINKFILE(tempdev, @TargetSizeMB);', NULL),
    ('SH-GROW-DATAFILE', 'Pre-Grow Data File Before Autogrow', 'Space', 'Low', '{"metric":"dataFileFreePercent","operator":"<","threshold":10}', 'ALTER DATABASE @DB MODIFY FILE (NAME = @File, SIZE = @NewSizeMB);', NULL),
    ('SH-GROW-LOGFILE', 'Pre-Grow Log File', 'Space', 'Low', '{"metric":"logFileFreePercent","operator":"<","threshold":15}', 'ALTER DATABASE @DB MODIFY FILE (NAME = @LogFile, SIZE = @NewSizeMB);', NULL),
    ('SH-BACKUP-LOG', 'Emergency Transaction Log Backup', 'Backup', 'Low', '{"metric":"logUsagePercent","operator":">","threshold":85}', 'BACKUP LOG @DB TO DISK = @BackupPath WITH COMPRESSION;', NULL),
    ('SH-BACKUP-FULL', 'Catch-Up Full Backup (RPO Breach)', 'Backup', 'Low', '{"metric":"lastFullBackupAge","operator":">","threshold":"25h"}', 'BACKUP DATABASE @DB TO DISK = @BackupPath WITH COMPRESSION;', NULL),
    ('SH-CYCLE-ERRORLOG', 'Cycle SQL Server Error Log', 'Maintenance', 'Low', '{"metric":"errorLogSizeMB","operator":">","threshold":100}', 'EXEC sp_cycle_errorlog;', NULL),
    ('SH-PURGE-JOBHIST', 'Purge Old Agent Job History', 'Maintenance', 'Low', '{"metric":"jobHistoryRows","operator":">","threshold":100000}', 'EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @CutoffDate;', NULL),
    ('SH-FIX-ORPHAN-USER', 'Fix Orphaned Database User', 'Security', 'Medium', '{"metric":"orphanedUsers","operator":">","threshold":0}', 'ALTER USER @UserName WITH LOGIN = @LoginName;', 'DROP USER @UserName;'),
    ('SH-DISABLE-UNUSED-LOGIN', 'Disable Stale Login (>90 days)', 'Security', 'Medium', '{"metric":"loginLastUsedDays","operator":">","threshold":90}', 'ALTER LOGIN @LoginName DISABLE;', 'ALTER LOGIN @LoginName ENABLE;'),
    ('SH-ROTATE-PASSWORD', 'Rotate SQL Login Password', 'Security', 'High', '{"metric":"passwordAgeDays","operator":">","threshold":90}', 'ALTER LOGIN @LoginName WITH PASSWORD = @NewPassword;', NULL),
    ('SH-ENABLE-TDE', 'Enable TDE on Unencrypted Sensitive DB', 'Security', 'High', '{"metric":"tdeEnabled","operator":"=","threshold":false,"condition":"hasSensitiveData"}', 'CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE @CertName; ALTER DATABASE @DB SET ENCRYPTION ON;', 'ALTER DATABASE @DB SET ENCRYPTION OFF; DROP DATABASE ENCRYPTION KEY;'),
    ('SH-RESTART-AGENT', 'Restart SQL Server Agent Service', 'Service', 'Medium', '{"metric":"agentServiceRunning","operator":"=","threshold":false}', 'NET START SQLSERVERAGENT;', 'NET STOP SQLSERVERAGENT;'),
    ('SH-FAILOVER-AG', 'Failover Availability Group (Planned)', 'HADR', 'High', '{"metric":"primaryHealth","operator":"<","threshold":50,"condition":"secondaryHealthy"}', 'ALTER AVAILABILITY GROUP @AGName FAILOVER;', 'ALTER AVAILABILITY GROUP @AGName FAILOVER;'),
    ('SH-RESUME-AG-DB', 'Resume Suspended AG Database', 'HADR', 'Medium', '{"metric":"agDbSuspended","operator":"=","threshold":true}', 'ALTER DATABASE @DB SET HADR RESUME;', 'ALTER DATABASE @DB SET HADR SUSPEND;'),
    ('SH-FIX-AG-SYNC', 'Repair AG Synchronization', 'HADR', 'Medium', '{"metric":"agSyncState","operator":"!=","threshold":"SYNCHRONIZED"}', 'ALTER AVAILABILITY GROUP @AGName MODIFY REPLICA ON @Replica WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);', NULL),
    ('SH-CHECKDB', 'Run DBCC CHECKDB After Corruption Alert', 'Integrity', 'Low', '{"metric":"suspectPages","operator":">","threshold":0}', 'DBCC CHECKDB(@DB) WITH NO_INFOMSGS;', NULL),
    ('SH-REPAIR-PAGE', 'Automatic Page Repair via AG', 'Integrity', 'High', '{"metric":"corruptPages","operator":">","threshold":0,"condition":"agEnabled"}', 'ALTER DATABASE @DB SET PAGE_VERIFY CHECKSUM; -- AG auto-repair handles rest', NULL),
    ('SH-MEMORY-PRESSURE', 'Reduce Memory Pressure (Free Caches)', 'Performance', 'Low', '{"metric":"memoryPressure","operator":"=","threshold":"high"}', 'DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;', NULL),
    ('SH-MAX-DOP-FIX', 'Fix Incorrect MaxDOP Setting', 'Configuration', 'Low', '{"metric":"maxDOP","operator":"=","threshold":0,"condition":"coreCount>8"}', 'EXEC sp_configure ''max degree of parallelism'', @RecommendedValue; RECONFIGURE;', 'EXEC sp_configure ''max degree of parallelism'', @PreviousValue; RECONFIGURE;'),
    ('SH-COST-THRESHOLD', 'Fix Cost Threshold for Parallelism', 'Configuration', 'Low', '{"metric":"costThreshold","operator":"=","threshold":5}', 'EXEC sp_configure ''cost threshold for parallelism'', 50; RECONFIGURE;', 'EXEC sp_configure ''cost threshold for parallelism'', 5; RECONFIGURE;'),
    ('SH-MAX-MEMORY-FIX', 'Fix Max Server Memory (Left at Default)', 'Configuration', 'Medium', '{"metric":"maxServerMemory","operator":"=","threshold":2147483647}', 'EXEC sp_configure ''max server memory (MB)'', @RecommendedMB; RECONFIGURE;', 'EXEC sp_configure ''max server memory (MB)'', @PreviousValue; RECONFIGURE;'),
    ('SH-ENABLE-BACKUP-COMPRESSION', 'Enable Backup Compression Default', 'Configuration', 'Low', '{"metric":"backupCompressionDefault","operator":"=","threshold":0}', 'EXEC sp_configure ''backup compression default'', 1; RECONFIGURE;', 'EXEC sp_configure ''backup compression default'', 0; RECONFIGURE;'),
    ('SH-ENABLE-OPTIMIZE-ADHOC', 'Enable Optimize for Ad Hoc Workloads', 'Configuration', 'Low', '{"metric":"optimizeForAdHoc","operator":"=","threshold":0}', 'EXEC sp_configure ''optimize for ad hoc workloads'', 1; RECONFIGURE;', 'EXEC sp_configure ''optimize for ad hoc workloads'', 0; RECONFIGURE;'),
    ('SH-ENABLE-QUERY-STORE', 'Enable Query Store', 'Performance', 'Low', '{"metric":"queryStoreEnabled","operator":"=","threshold":false}', 'ALTER DATABASE @DB SET QUERY_STORE = ON;', 'ALTER DATABASE @DB SET QUERY_STORE = OFF;'),
    ('SH-PURGE-QUERY-STORE', 'Purge Query Store When Full', 'Performance', 'Low', '{"metric":"queryStoreSpaceUsed","operator":">","threshold":90}', 'ALTER DATABASE @DB SET QUERY_STORE CLEAR;', NULL),
    ('SH-FIX-VLF-COUNT', 'Reduce Excessive VLF Count', 'Performance', 'Medium', '{"metric":"vlfCount","operator":">","threshold":1000}', 'DBCC SHRINKFILE(@LogFile, 1); ALTER DATABASE @DB MODIFY FILE (NAME = @LogFile, SIZE = @OptimalSizeMB);', NULL),
    ('SH-CLEANUP-MSDB', 'Cleanup MSDB Growth', 'Maintenance', 'Low', '{"metric":"msdbSizeMB","operator":">","threshold":5000}', 'EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @CutoffDate; EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @CutoffDate;', NULL),
    ('SH-KILL-LONG-QUERY', 'Kill Long-Running Query', 'Performance', 'Medium', '{"metric":"queryDurationMin","operator":">","threshold":60}', 'KILL @SessionId;', NULL),
    ('SH-RECOMPILE-PROC', 'Force Recompile Bad Procedure', 'Performance', 'Low', '{"metric":"procPlanQuality","operator":"<","threshold":30}', 'EXEC sp_recompile @ProcName;', NULL),
    ('SH-ENABLE-PAGE-VERIFY', 'Enable CHECKSUM Page Verification', 'Integrity', 'Low', '{"metric":"pageVerify","operator":"!=","threshold":"CHECKSUM"}', 'ALTER DATABASE @DB SET PAGE_VERIFY CHECKSUM;', NULL),
    ('SH-FIX-AUTO-CLOSE', 'Disable AUTO_CLOSE', 'Configuration', 'Low', '{"metric":"autoClose","operator":"=","threshold":true}', 'ALTER DATABASE @DB SET AUTO_CLOSE OFF;', NULL),
    ('SH-FIX-AUTO-SHRINK', 'Disable AUTO_SHRINK', 'Configuration', 'Low', '{"metric":"autoShrink","operator":"=","threshold":true}', 'ALTER DATABASE @DB SET AUTO_SHRINK OFF;', NULL),
    ('SH-ENABLE-IFI', 'Enable Instant File Initialization', 'Configuration', 'Low', '{"metric":"instantFileInit","operator":"=","threshold":false}', '-- Grant SE_MANAGE_VOLUME_NAME to SQL service account', NULL),
    ('SH-FIX-RECOVERY-MODEL', 'Fix Recovery Model Mismatch', 'Backup', 'Medium', '{"metric":"recoveryModel","operator":"!=","threshold":"FULL","condition":"isCritical"}', 'ALTER DATABASE @DB SET RECOVERY FULL;', 'ALTER DATABASE @DB SET RECOVERY @PreviousModel;'),
    ('SH-MARK-DB-SUSPECT-RESET', 'Reset Suspect Database State', 'Integrity', 'High', '{"metric":"dbState","operator":"=","threshold":"SUSPECT"}', 'ALTER DATABASE @DB SET EMERGENCY; DBCC CHECKDB(@DB, REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE @DB SET MULTI_USER;', NULL),
    ('SH-CERT-RENEWAL-ALERT', 'Alert and Prep Certificate Renewal', 'Security', 'Medium', '{"metric":"certExpiryDays","operator":"<","threshold":30}', '-- Generate new certificate and backup; notify admin', NULL),
    ('SH-DISK-SPACE-ALERT', 'Purge Old Files When Disk Critical', 'Space', 'Medium', '{"metric":"diskFreePercent","operator":"<","threshold":5}', '-- Remove old backup files beyond retention policy', NULL),
    ('SH-FIX-COMPATIBILITY-LEVEL', 'Update Database Compatibility Level', 'Configuration', 'Medium', '{"metric":"compatLevel","operator":"<","threshold":"engineVersion-2"}', 'ALTER DATABASE @DB SET COMPATIBILITY_LEVEL = @TargetLevel;', 'ALTER DATABASE @DB SET COMPATIBILITY_LEVEL = @PreviousLevel;'),
    ('SH-ENABLE-CHECKSUM-BACKUP', 'Enable Backup Checksum', 'Backup', 'Low', '{"metric":"backupChecksum","operator":"=","threshold":false}', 'BACKUP DATABASE @DB TO DISK = @Path WITH CHECKSUM, COMPRESSION;', NULL),
    ('SH-FIX-AUTOGROWTH', 'Fix Percentage-Based Autogrowth', 'Configuration', 'Low', '{"metric":"autogrowthType","operator":"=","threshold":"percent"}', 'ALTER DATABASE @DB MODIFY FILE (NAME = @File, FILEGROWTH = @RecommendedMB);', NULL),
    ('SH-CLEANUP-CHANGE-TRACKING', 'Cleanup Change Tracking Side Tables', 'Maintenance', 'Low', '{"metric":"changeTrackingAge","operator":">","threshold":"7d"}', 'ALTER DATABASE @DB SET CHANGE_TRACKING (AUTO_CLEANUP = ON, CHANGE_RETENTION = 3 DAYS);', NULL),
    ('SH-DEADLOCK-PRIORITY', 'Set Deadlock Priority on Known Victim', 'Performance', 'Low', '{"metric":"deadlockVictimCount","operator":">","threshold":5}', 'SET DEADLOCK_PRIORITY LOW;', NULL),
    ('SH-TRACE-FLAG-FIX', 'Enable Recommended Trace Flags', 'Configuration', 'Low', '{"metric":"traceFlagsEnabled","operator":"missing","threshold":"3226,7412"}', 'DBCC TRACEON(3226, -1); DBCC TRACEON(7412, -1);', 'DBCC TRACEOFF(3226, -1); DBCC TRACEOFF(7412, -1);'),
    ('SH-LS-CATCH-UP', 'Force Log Shipping Copy/Restore', 'HADR', 'Medium', '{"metric":"lsLatencyMin","operator":">","threshold":60}', 'EXEC msdb.dbo.sp_start_job @job_name = @LSCopyJob;', NULL),
    ('SH-MIRROR-RESUME', 'Resume Paused Database Mirroring', 'HADR', 'Medium', '{"metric":"mirrorState","operator":"=","threshold":"SUSPENDED"}', 'ALTER DATABASE @DB SET PARTNER RESUME;', 'ALTER DATABASE @DB SET PARTNER SUSPEND;');
END;
GO

-- ===== Seed: Contextual Help (30+ entries) =====
IF NOT EXISTS (SELECT 1 FROM learning.ContextualHelp WHERE ContextKey = 'metric.cpuPercent')
BEGIN
  INSERT INTO learning.ContextualHelp (ContextKey, MetricOrAlert, Title, Summary, WhyItMatters, WhatToDoNext) VALUES
    ('metric.cpuPercent', 'CPU Utilization', 'Server CPU Usage', 'Percentage of CPU consumed by SQL Server and OS processes.', 'Sustained high CPU (>80%) causes query timeouts, blocking, and degraded user experience. Often caused by missing indexes, parameter sniffing, or runaway queries.', 'Check top queries by CPU in the Telemetry page. Look for missing indexes. Consider enabling Query Store.'),
    ('metric.blockingSessions', 'Blocking Sessions', 'Active Blocking Chains', 'Number of sessions currently blocked waiting for locks held by other sessions.', 'Blocking directly impacts application response time. Long blocking chains can cascade into timeouts and application failures.', 'Identify the head blocker in Telemetry. Check if long-running transactions can be optimized. Consider enabling Read Committed Snapshot Isolation (RCSI).'),
    ('metric.memoryUsed', 'Memory Usage', 'SQL Server Memory Consumption', 'Amount of memory currently allocated to the SQL Server buffer pool and other memory clerks.', 'SQL Server is designed to consume available memory. The issue is when max server memory is misconfigured, causing OS memory pressure and paging.', 'Verify max server memory is set correctly (leave 4GB+ for OS). Check for memory-intensive query plans.'),
    ('metric.batchRequests', 'Batch Requests/sec', 'Query Throughput', 'Number of T-SQL batch requests received per second. Key workload indicator.', 'Establishes baseline workload. Sudden spikes may indicate application issues; drops may indicate connectivity problems.', 'Compare against baseline. If significantly higher, check for query plan regressions or application changes.'),
    ('alert.backupOverdue', 'Backup Overdue', 'Backup SLA Breach', 'A database has not been backed up within the required RPO window.', 'Without current backups, data loss is inevitable during failure. RPO breach means the business cannot recover to the required point in time.', 'Run an immediate backup. Check SQL Agent job history for failures. Verify backup storage is accessible.'),
    ('alert.agNotSync', 'AG Not Synchronized', 'Availability Group Health', 'One or more AG replicas are not in SYNCHRONIZED state.', 'Unsynchronized replicas mean potential data loss during failover. The longer the gap, the higher the RPO risk.', 'Check the secondary replica for: network issues, redo queue buildup, log send queue, or suspended data movement.'),
    ('alert.diskLow', 'Low Disk Space', 'Storage Capacity Warning', 'A volume has less than 10% or 10GB free space remaining.', 'SQL Server will crash if data or log files cannot grow. Transaction log full conditions cause all writes to stop.', 'Free space immediately: shrink old files, move data, or extend the volume. Set up proactive monitoring.'),
    ('alert.jobFailed', 'Agent Job Failed', 'SQL Agent Job Failure', 'A scheduled SQL Agent job has reported a failed execution.', 'Failed jobs may mean missed backups, incomplete maintenance, broken ETL, or stale data.', 'Check job history for the specific step that failed. Review error messages. Verify credentials and permissions.'),
    ('metric.patchLevel', 'Patch Currency', 'SQL Server Patch Status', 'How many cumulative updates behind the current patch level this instance is.', 'Unpatched instances are vulnerable to known exploits, may lack critical bug fixes, and fail compliance requirements.', 'Schedule patching during the next maintenance window. Test in non-production first. Verify AG/cluster patching order.'),
    ('metric.tdeStatus', 'TDE Encryption', 'Transparent Data Encryption', 'Whether databases containing sensitive data have TDE enabled for data-at-rest encryption.', 'Without TDE, database files on disk are readable by anyone with file system access. Required by PCI-DSS, HIPAA, and most compliance frameworks.', 'Enable TDE on all databases containing PII/PHI/PCI data. Ensure certificate backup is stored securely offsite.'),
    ('metric.availabilityScore', 'SLA Availability', 'Service Availability Score', 'Actual uptime percentage compared to SLA target (e.g., 99.99%).', 'Directly tied to business SLA commitments. Breaches trigger contractual penalties and erode customer trust.', 'Investigate recent downtime events. Implement HA if not present. Review DR drill results.'),
    ('metric.restoreVerified', 'Restore Verification', 'Backup Recoverability', 'Whether the most recent backup has been tested via actual restore.', 'A backup that cannot be restored is worthless. 34% of restore attempts fail without regular testing.', 'Schedule automated restore verification. Check the Recoverability page for full assurance scoring.'),
    ('metric.orphanedLogins', 'Orphaned Logins', 'Identity Hygiene', 'Database logins that no longer map to a valid Windows/AD account or are never used.', 'Orphaned logins are attack vectors. They persist after employees leave and may retain elevated privileges.', 'Review and remove orphaned accounts. Enable the self-healing action SH-DISABLE-UNUSED-LOGIN.'),
    ('metric.certExpiry', 'Certificate Expiry', 'TLS/TDE Certificate Lifecycle', 'Days until a SQL Server certificate expires.', 'Expired certificates break TDE, AG communication, endpoint encryption, and TLS connections. Recovery from expired certs is complex and urgent.', 'Renew certificates at least 30 days before expiry. Backup new certificate immediately after creation.');
END;
GO

PRINT 'Schema v8 (Seven Differentiators) applied successfully.';
GO
