-- DBAOps Sovereign™ — Additive v2 Schema Migration
-- Powered by RHOYLLE INNOVATION EMPIRE LLC
-- All statements use IF NOT EXISTS / COL_LENGTH guards — safe to re-run (idempotent).
-- No v1 tables are dropped or altered.

USE SovereignRepository;
GO

-- =============================================
-- New schemas
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'alert')
    EXEC('CREATE SCHEMA alert');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'ai')
    EXEC('CREATE SCHEMA ai');
GO

-- =============================================
-- Drop + recreate all v2 tables (FK children first).
-- These are new in v2 with no legacy production data.
-- Also cleans up any stale tables from broken-schema runs.
-- =============================================
IF OBJECT_ID('alert.Active','U') IS NOT NULL DROP TABLE alert.Active;
IF OBJECT_ID('alert.History','U') IS NOT NULL DROP TABLE alert.History;
IF OBJECT_ID('alert.Rules','U') IS NOT NULL DROP TABLE alert.Rules;
IF OBJECT_ID('ai.Predictions','U') IS NOT NULL DROP TABLE ai.Predictions;
IF OBJECT_ID('ai.RootCauseAnalysis','U') IS NOT NULL DROP TABLE ai.RootCauseAnalysis;
IF OBJECT_ID('ai.Recommendations','U') IS NOT NULL DROP TABLE ai.Recommendations;
IF OBJECT_ID('perf.QueryStats','U') IS NOT NULL DROP TABLE perf.QueryStats;
IF OBJECT_ID('perf.QueryTexts','U') IS NOT NULL DROP TABLE perf.QueryTexts;
IF OBJECT_ID('perf.WaitStats','U') IS NOT NULL DROP TABLE perf.WaitStats;
IF OBJECT_ID('perf.InstanceMetrics','U') IS NOT NULL DROP TABLE perf.InstanceMetrics;
IF OBJECT_ID('perf.ExecutionPlans','U') IS NOT NULL DROP TABLE perf.ExecutionPlans;
IF OBJECT_ID('perf.FileIOStats','U') IS NOT NULL DROP TABLE perf.FileIOStats;
IF OBJECT_ID('perf.TempDBStats','U') IS NOT NULL DROP TABLE perf.TempDBStats;
IF OBJECT_ID('inv.ServerConfiguration','U') IS NOT NULL DROP TABLE inv.ServerConfiguration;
IF OBJECT_ID('inv.DatabaseFiles','U') IS NOT NULL DROP TABLE inv.DatabaseFiles;
IF OBJECT_ID('inv.Indexes','U') IS NOT NULL DROP TABLE inv.Indexes;
IF OBJECT_ID('inv.MissingIndexes','U') IS NOT NULL DROP TABLE inv.MissingIndexes;
IF OBJECT_ID('inv.[Statistics]','U') IS NOT NULL DROP TABLE inv.[Statistics];
IF OBJECT_ID('[backup].History','U') IS NOT NULL DROP TABLE [backup].History;
IF OBJECT_ID('[backup].Compliance','U') IS NOT NULL DROP TABLE [backup].Compliance;
IF OBJECT_ID('sec.LoginAudit','U') IS NOT NULL DROP TABLE sec.LoginAudit;
IF OBJECT_ID('sec.PermissionChanges','U') IS NOT NULL DROP TABLE sec.PermissionChanges;
IF OBJECT_ID('sec.SchemaChanges','U') IS NOT NULL DROP TABLE sec.SchemaChanges;
IF OBJECT_ID('security.LoginAudit','U') IS NOT NULL DROP TABLE security.LoginAudit;
IF OBJECT_ID('security.PermissionChanges','U') IS NOT NULL DROP TABLE security.PermissionChanges;
IF OBJECT_ID('security.SchemaChanges','U') IS NOT NULL DROP TABLE security.SchemaChanges;
IF OBJECT_ID('ctl.Databases','U') IS NOT NULL DROP TABLE ctl.Databases;
IF OBJECT_ID('ctl.CollectionSchedules','U') IS NOT NULL DROP TABLE ctl.CollectionSchedules;
IF OBJECT_ID('ctl.Environments','U') IS NOT NULL DROP TABLE ctl.Environments;
GO

-- =============================================
-- ctl — extended control-plane tables
-- =============================================

IF OBJECT_ID('ctl.Environments', 'U') IS NULL
CREATE TABLE ctl.Environments (
    EnvironmentId   INT IDENTITY(1,1) PRIMARY KEY,
    Name            NVARCHAR(64) NOT NULL UNIQUE,
    Description     NVARCHAR(256) NULL,
    SortOrder       INT NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('ctl.Databases', 'U') IS NULL
CREATE TABLE ctl.Databases (
    DatabaseId      UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    CompatLevel     INT NULL,
    RecoveryModel   NVARCHAR(32) NULL,
    QueryStoreEnabled BIT NOT NULL DEFAULT 0,
    CDCEnabled      BIT NOT NULL DEFAULT 0,
    TDEEnabled      BIT NOT NULL DEFAULT 0,
    SizeMB          BIGINT NULL,
    [State]         NVARCHAR(32) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_ctl_Databases_Server_DB UNIQUE (ServerId, DatabaseName)
);
GO

IF OBJECT_ID('ctl.CollectionSchedules', 'U') IS NULL
CREATE TABLE ctl.CollectionSchedules (
    ScheduleId      INT IDENTITY(1,1) PRIMARY KEY,
    CollectorName   NVARCHAR(128) NOT NULL UNIQUE,
    IntervalMinutes INT NOT NULL DEFAULT 5,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    Description     NVARCHAR(512) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- =============================================
-- perf — extended performance tables
-- =============================================

IF OBJECT_ID('perf.WaitStats', 'U') IS NULL
BEGIN
    CREATE TABLE perf.WaitStats (
        WaitStatsId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        WaitType        NVARCHAR(128) NOT NULL,
        Category        NVARCHAR(64) NULL,
        WaitingTasksCount BIGINT NOT NULL DEFAULT 0,
        WaitTimeMs      BIGINT NOT NULL DEFAULT 0,
        SignalWaitTimeMs BIGINT NOT NULL DEFAULT 0,
        DeltaWaitTimeMs BIGINT NULL,
        DeltaSignalMs   BIGINT NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_WaitStats_Server_Collected ON perf.WaitStats (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID('perf.InstanceMetrics', 'U') IS NULL
BEGIN
    CREATE TABLE perf.InstanceMetrics (
        MetricId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        PLE             BIGINT NULL,
        MemoryGrantsPending INT NULL,
        CompilationsPerSec DECIMAL(18,2) NULL,
        RecompilationsPerSec DECIMAL(18,2) NULL,
        LockWaitsPerSec DECIMAL(18,2) NULL,
        PageSplitsPerSec DECIMAL(18,2) NULL,
        LazyWritesPerSec DECIMAL(18,2) NULL,
        FreeListStallsPerSec DECIMAL(18,2) NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_InstanceMetrics_Server_Collected ON perf.InstanceMetrics (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID('perf.QueryTexts', 'U') IS NULL
CREATE TABLE perf.QueryTexts (
    QueryTextId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    QueryHash       VARBINARY(32) NOT NULL,
    NormalizedText  NVARCHAR(MAX) NOT NULL,
    FirstSeen       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_QueryTexts_Hash UNIQUE (QueryHash)
);
GO

IF OBJECT_ID('perf.QueryStats', 'U') IS NULL
BEGIN
    CREATE TABLE perf.QueryStats (
        QueryStatsId    UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName    NVARCHAR(256) NULL,
        QueryTextId     UNIQUEIDENTIFIER NULL REFERENCES perf.QueryTexts(QueryTextId),
        QueryHash       VARBINARY(32) NULL,
        PlanHash        VARBINARY(32) NULL,
        ExecutionCount  BIGINT NOT NULL DEFAULT 0,
        TotalCpuTimeMs  BIGINT NULL,
        TotalDurationMs BIGINT NULL,
        TotalLogicalReads BIGINT NULL,
        TotalPhysicalReads BIGINT NULL,
        TotalLogicalWrites BIGINT NULL,
        AvgCpuTimeMs    AS (CASE WHEN ExecutionCount > 0 THEN TotalCpuTimeMs / ExecutionCount ELSE 0 END),
        AvgDurationMs   AS (CASE WHEN ExecutionCount > 0 THEN TotalDurationMs / ExecutionCount ELSE 0 END),
        AvgLogicalReads AS (CASE WHEN ExecutionCount > 0 THEN TotalLogicalReads / ExecutionCount ELSE 0 END),
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_QueryStats_Server_Collected ON perf.QueryStats (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID('perf.ExecutionPlans', 'U') IS NULL
CREATE TABLE perf.ExecutionPlans (
    PlanId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    PlanHash        VARBINARY(32) NOT NULL,
    QueryHash       VARBINARY(32) NULL,
    PlanXml         NVARCHAR(MAX) NULL,
    HasMissingIndex BIT NOT NULL DEFAULT 0,
    HasImplicitConversion BIT NOT NULL DEFAULT 0,
    HasSpill        BIT NOT NULL DEFAULT 0,
    EstimatedCost   DECIMAL(18,4) NULL,
    FirstSeen       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    LastSeen        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('perf.FileIOStats', 'U') IS NULL
BEGIN
    CREATE TABLE perf.FileIOStats (
        FileIOId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName    NVARCHAR(256) NOT NULL,
        FileName        NVARCHAR(256) NOT NULL,
        FileType        NVARCHAR(16) NULL,
        ReadLatencyMs   DECIMAL(18,2) NULL,
        WriteLatencyMs  DECIMAL(18,2) NULL,
        IoStallReadMs   BIGINT NULL,
        IoStallWriteMs  BIGINT NULL,
        NumReads        BIGINT NULL,
        NumWrites       BIGINT NULL,
        BytesRead       BIGINT NULL,
        BytesWritten    BIGINT NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_FileIOStats_Server_Collected ON perf.FileIOStats (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID('perf.TempDBStats', 'U') IS NULL
BEGIN
    CREATE TABLE perf.TempDBStats (
        TempDBStatsId   UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        VersionStoreSizeMB DECIMAL(18,2) NULL,
        UserObjectSizeMB DECIMAL(18,2) NULL,
        InternalObjectSizeMB DECIMAL(18,2) NULL,
        FreeSpaceMB     DECIMAL(18,2) NULL,
        PFSContentionPct DECIMAL(5,2) NULL,
        GAMContentionPct DECIMAL(5,2) NULL,
        SGAMContentionPct DECIMAL(5,2) NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_TempDBStats_Server_Collected ON perf.TempDBStats (ServerId, CollectedAt DESC);
END
GO

-- =============================================
-- inv — extended inventory tables
-- =============================================

IF OBJECT_ID('inv.ServerConfiguration', 'U') IS NULL
CREATE TABLE inv.ServerConfiguration (
    ConfigId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ConfigName      NVARCHAR(256) NOT NULL,
    ConfigValue     INT NULL,
    ValueInUse      INT NULL,
    MinimumValue    INT NULL,
    MaximumValue    INT NULL,
    IsDynamic       BIT NOT NULL DEFAULT 0,
    IsAdvanced      BIT NOT NULL DEFAULT 0,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_ServerConfig_Server_Name UNIQUE (ServerId, ConfigName)
);
GO

IF OBJECT_ID('inv.DatabaseFiles', 'U') IS NULL
CREATE TABLE inv.DatabaseFiles (
    FileId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    LogicalName     NVARCHAR(256) NOT NULL,
    FileType        NVARCHAR(16) NOT NULL,
    PhysicalPath    NVARCHAR(512) NULL,
    SizeMB          DECIMAL(18,2) NULL,
    UsedMB          DECIMAL(18,2) NULL,
    GrowthMB        DECIMAL(18,2) NULL,
    IsPercentGrowth BIT NOT NULL DEFAULT 0,
    MaxSizeMB       BIGINT NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_DatabaseFiles_Server_DB_File UNIQUE (ServerId, DatabaseName, LogicalName)
);
GO

IF OBJECT_ID('inv.Indexes', 'U') IS NULL
BEGIN
    CREATE TABLE inv.Indexes (
        IndexRecordId   UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName    NVARCHAR(256) NOT NULL,
        SchemaName      NVARCHAR(128) NULL,
        TableName       NVARCHAR(256) NOT NULL,
        IndexName       NVARCHAR(256) NULL,
        IndexType       NVARCHAR(64) NULL,
        FragmentationPct DECIMAL(5,2) NULL,
        PageCount       BIGINT NULL,
        UserSeeks       BIGINT NULL,
        UserScans       BIGINT NULL,
        UserLookups     BIGINT NULL,
        UserUpdates     BIGINT NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_Indexes_Server_Collected ON inv.Indexes (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID('inv.MissingIndexes', 'U') IS NULL
BEGIN
    CREATE TABLE inv.MissingIndexes (
        MissingIndexId  UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName    NVARCHAR(256) NOT NULL,
        SchemaName      NVARCHAR(128) NULL,
        TableName       NVARCHAR(256) NOT NULL,
        EqualityColumns NVARCHAR(MAX) NULL,
        InequalityColumns NVARCHAR(MAX) NULL,
        IncludeColumns  NVARCHAR(MAX) NULL,
        ImpactScore     DECIMAL(18,2) NULL,
        UserSeeks       BIGINT NULL,
        UserScans       BIGINT NULL,
        AvgTotalUserCost DECIMAL(18,4) NULL,
        AvgUserImpact   DECIMAL(5,2) NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_MissingIndexes_Server_Impact ON inv.MissingIndexes (ServerId, ImpactScore DESC);
END
GO

IF OBJECT_ID('inv.Statistics', 'U') IS NULL
CREATE TABLE inv.[Statistics] (
    StatId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    SchemaName      NVARCHAR(128) NULL,
    TableName       NVARCHAR(256) NOT NULL,
    StatName        NVARCHAR(256) NOT NULL,
    LastUpdated     DATETIME2 NULL,
    [RowCount]      BIGINT NULL,
    RowsModified    BIGINT NULL,
    ModificationPct AS (CASE WHEN [RowCount] > 0 THEN CAST(RowsModified AS DECIMAL(18,2)) / [RowCount] * 100 ELSE 0 END),
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- =============================================
-- backup — extended backup tables
-- =============================================

IF OBJECT_ID('[backup].History', 'U') IS NULL
BEGIN
    CREATE TABLE [backup].History (
        BackupHistoryId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName    NVARCHAR(256) NOT NULL,
        BackupType      NVARCHAR(16) NOT NULL,
        BackupStartDate DATETIME2 NOT NULL,
        BackupFinishDate DATETIME2 NULL,
        BackupSizeMB    DECIMAL(18,2) NULL,
        CompressedSizeMB DECIMAL(18,2) NULL,
        FirstLSN        NVARCHAR(64) NULL,
        LastLSN         NVARCHAR(64) NULL,
        MediaSetId      INT NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_BackupHistory_Server_DB ON [backup].History (ServerId, DatabaseName, BackupStartDate DESC);
END
GO

IF OBJECT_ID('[backup].Compliance', 'U') IS NULL
CREATE TABLE [backup].Compliance (
    ComplianceId    UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    RpoHours        INT NOT NULL DEFAULT 24,
    LastFullBackup  DATETIME2 NULL,
    LastLogBackup   DATETIME2 NULL,
    HoursSinceFullBackup AS (DATEDIFF(HOUR, LastFullBackup, SYSUTCDATETIME())),
    HoursSinceLogBackup  AS (DATEDIFF(HOUR, LastLogBackup, SYSUTCDATETIME())),
    IsCompliant     AS (CASE WHEN LastFullBackup IS NOT NULL AND DATEDIFF(HOUR, LastFullBackup, SYSUTCDATETIME()) <= RpoHours THEN 1 ELSE 0 END),
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_BackupCompliance_Server_DB UNIQUE (ServerId, DatabaseName)
);
GO

-- =============================================
-- sec — security tracking tables
-- =============================================

IF OBJECT_ID('sec.LoginAudit', 'U') IS NULL
BEGIN
    CREATE TABLE sec.LoginAudit (
        LoginAuditId    UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        LoginName       NVARCHAR(256) NOT NULL,
        EventType       NVARCHAR(32) NOT NULL,
        ClientHost      NVARCHAR(256) NULL,
        ApplicationName NVARCHAR(256) NULL,
        EventTime       DATETIME2 NOT NULL,
        CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_LoginAudit_Server_Time ON sec.LoginAudit (ServerId, EventTime DESC);
END
GO

IF OBJECT_ID('sec.PermissionChanges', 'U') IS NULL
CREATE TABLE sec.PermissionChanges (
    ChangeId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NULL,
    Principal       NVARCHAR(256) NOT NULL,
    Permission      NVARCHAR(256) NOT NULL,
    ActionType      NVARCHAR(32) NOT NULL,
    EventTime       DATETIME2 NOT NULL,
    PerformedBy     NVARCHAR(256) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('sec.SchemaChanges', 'U') IS NULL
CREATE TABLE sec.SchemaChanges (
    SchemaChangeId  UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NULL,
    ObjectName      NVARCHAR(512) NOT NULL,
    ObjectType      NVARCHAR(64) NULL,
    DDLEvent        NVARCHAR(64) NOT NULL,
    EventTime       DATETIME2 NOT NULL,
    LoginName       NVARCHAR(256) NULL,
    TSQLCommand     NVARCHAR(MAX) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- =============================================
-- alert — configurable alerting
-- =============================================

IF OBJECT_ID('alert.Rules', 'U') IS NULL
CREATE TABLE alert.Rules (
    RuleId          INT IDENTITY(1,1) PRIMARY KEY,
    RuleName        NVARCHAR(128) NOT NULL UNIQUE,
    MetricName      NVARCHAR(128) NOT NULL,
    Operator        NVARCHAR(8) NOT NULL DEFAULT '>',
    WarningThreshold DECIMAL(18,4) NULL,
    CriticalThreshold DECIMAL(18,4) NOT NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    Description     NVARCHAR(512) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_AlertRule_Operator CHECK (Operator IN ('>', '<', '>=', '<=', '=', '!='))
);
GO

IF OBJECT_ID('alert.Active', 'U') IS NULL
BEGIN
    CREATE TABLE alert.Active (
        ActiveAlertId   UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        RuleId          INT NULL REFERENCES alert.Rules(RuleId),
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        Severity        NVARCHAR(32) NOT NULL,
        Message         NVARCHAR(MAX) NOT NULL,
        MetricValue     DECIMAL(18,4) NULL,
        FiredAt         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        AcknowledgedAt  DATETIME2 NULL,
        AcknowledgedBy  NVARCHAR(256) NULL
    );
    CREATE INDEX IX_AlertActive_Server_Fired ON alert.Active (ServerId, FiredAt DESC);
END
GO

IF OBJECT_ID('alert.History', 'U') IS NULL
BEGIN
    CREATE TABLE alert.History (
        AlertHistoryId  UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        RuleId          INT NULL,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        Severity        NVARCHAR(32) NOT NULL,
        Message         NVARCHAR(MAX) NOT NULL,
        MetricValue     DECIMAL(18,4) NULL,
        FiredAt         DATETIME2 NOT NULL,
        ResolvedAt      DATETIME2 NULL,
        ResolvedBy      NVARCHAR(256) NULL,
        DurationMinutes AS (DATEDIFF(MINUTE, FiredAt, ISNULL(ResolvedAt, SYSUTCDATETIME())))
    );
    CREATE INDEX IX_AlertHistory_Server_Fired ON alert.History (ServerId, FiredAt DESC);
END
GO

-- =============================================
-- ai — ML / AI placeholder tables
-- =============================================

IF OBJECT_ID('ai.Predictions', 'U') IS NULL
CREATE TABLE ai.Predictions (
    PredictionId    UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    MetricName      NVARCHAR(128) NOT NULL,
    PredictedValue  DECIMAL(18,4) NOT NULL,
    ConfidencePct   DECIMAL(5,2) NULL,
    PredictionWindow NVARCHAR(32) NULL,
    ModelVersion    NVARCHAR(64) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('ai.RootCauseAnalysis', 'U') IS NULL
CREATE TABLE ai.RootCauseAnalysis (
    AnalysisId      UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    IncidentSummary NVARCHAR(MAX) NOT NULL,
    RootCause       NVARCHAR(MAX) NULL,
    ConfidencePct   DECIMAL(5,2) NULL,
    EvidenceJson    NVARCHAR(MAX) NULL,
    ModelVersion    NVARCHAR(64) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('ai.Recommendations', 'U') IS NULL
CREATE TABLE ai.Recommendations (
    RecommendationId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    Category        NVARCHAR(64) NOT NULL,
    Title           NVARCHAR(256) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    ImpactScore     DECIMAL(5,2) NULL,
    ActionSql       NVARCHAR(MAX) NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Pending',
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_Recommendation_Status CHECK (Status IN ('Pending', 'Accepted', 'Dismissed', 'Applied'))
);
GO

-- =============================================
-- Seed data (idempotent)
-- =============================================

IF NOT EXISTS (SELECT 1 FROM ctl.Environments WHERE Name = 'Production')
    INSERT INTO ctl.Environments (Name, Description, SortOrder) VALUES ('Production', 'Live production workloads', 1);
IF NOT EXISTS (SELECT 1 FROM ctl.Environments WHERE Name = 'Staging')
    INSERT INTO ctl.Environments (Name, Description, SortOrder) VALUES ('Staging', 'Pre-production staging', 2);
IF NOT EXISTS (SELECT 1 FROM ctl.Environments WHERE Name = 'Development')
    INSERT INTO ctl.Environments (Name, Description, SortOrder) VALUES ('Development', 'Developer workstations and shared dev', 3);
IF NOT EXISTS (SELECT 1 FROM ctl.Environments WHERE Name = 'Test')
    INSERT INTO ctl.Environments (Name, Description, SortOrder) VALUES ('Test', 'QA and integration testing', 4);
IF NOT EXISTS (SELECT 1 FROM ctl.Environments WHERE Name = 'Pilot')
    INSERT INTO ctl.Environments (Name, Description, SortOrder) VALUES ('Pilot', 'DBAOps Sovereign pilot evaluation', 5);
GO

IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'Telemetry')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('Telemetry', 1, 1, 'CPU, memory, sessions, batch requests');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'WaitStats')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('WaitStats', 5, 1, 'sys.dm_os_wait_stats snapshot');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'InstanceMetrics')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('InstanceMetrics', 5, 1, 'PLE, memory grants, compilations');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'FleetFacts')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('FleetFacts', 15, 1, 'Backup, HADR, patch, inventory, jobs, volumes');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'QueryStore')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('QueryStore', 15, 1, 'Top queries by CPU/duration/reads');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'FileIOStats')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('FileIOStats', 10, 1, 'Per-file I/O latency from sys.dm_io_virtual_file_stats');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'TempDBStats')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('TempDBStats', 10, 1, 'tempdb contention and space usage');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'IndexHealth')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('IndexHealth', 60, 1, 'Index fragmentation and usage');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'MissingIndexes')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('MissingIndexes', 60, 1, 'Missing index recommendations');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'StatisticsFreshness')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('StatisticsFreshness', 60, 1, 'Statistics last-updated dates');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'ServerConfiguration')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('ServerConfiguration', 1440, 1, 'sp_configure snapshot (daily)');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'DatabaseFiles')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('DatabaseFiles', 60, 1, 'File-level size and growth');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'BackupHistory')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('BackupHistory', 60, 1, 'Full backup history with LSN chain');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'SecurityAudit')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('SecurityAudit', 30, 1, 'Login failures, permission and DDL changes');
IF NOT EXISTS (SELECT 1 FROM ctl.CollectionSchedules WHERE CollectorName = 'BaselineDrift')
    INSERT INTO ctl.CollectionSchedules (CollectorName, IntervalMinutes, IsEnabled, Description) VALUES ('BaselineDrift', 1440, 1, 'Configuration baseline comparison (daily)');
GO

IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'High CPU')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('High CPU', 'CpuPercent', '>', 75, 90, 'SQL Server CPU utilization');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Low PLE')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Low PLE', 'PLE', '<', 600, 300, 'Page Life Expectancy below threshold');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Blocking Sessions')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Blocking Sessions', 'BlockingSessions', '>', 1, 5, 'Active blocking chains');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Memory Grants Pending')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Memory Grants Pending', 'MemoryGrantsPending', '>', 2, 10, 'Queries waiting for memory grant');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'High Signal Waits')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('High Signal Waits', 'AvgSignalWaitMs', '>', 10, 25, 'CPU pressure via signal waits');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Volume Space Low')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Volume Space Low', 'FreeGB', '<', 20, 10, 'Drive free space critically low');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Backup RPO Breach')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Backup RPO Breach', 'HoursSinceFullBackup', '>', 24, 48, 'Full backup older than RPO target');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'HADR Not Synchronized')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('HADR Not Synchronized', 'EstimatedDataLossSeconds', '>', 30, 120, 'AG replica data loss risk');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Index Fragmentation')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Index Fragmentation', 'FragmentationPct', '>', 30, 70, 'Index fragmentation needs attention');
IF NOT EXISTS (SELECT 1 FROM alert.Rules WHERE RuleName = 'Stale Statistics')
    INSERT INTO alert.Rules (RuleName, MetricName, Operator, WarningThreshold, CriticalThreshold, Description) VALUES ('Stale Statistics', 'ModificationPct', '>', 10, 30, 'Statistics modification percentage');
GO

PRINT 'DBAOps Sovereign™ — v2 additive schema applied successfully.';
GO
