-- DBAOps Sovereign™ — v16 Repair: core platform tables from base schema.sql
-- When migrate skips v1 because ctl.Servers exists, collectors/ops UX still expect
-- config.AlertThreshold, baseline.*, audit.ActionLog, inv/cap/ops/backup/hadr/patch facts.
-- Idempotent — safe to run on healthy databases.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'config')
    EXEC('CREATE SCHEMA config');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'baseline')
    EXEC('CREATE SCHEMA baseline');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'audit')
    EXEC('CREATE SCHEMA audit');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'backup')
    EXEC('CREATE SCHEMA [backup]');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'hadr')
    EXEC('CREATE SCHEMA hadr');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'patch')
    EXEC('CREATE SCHEMA patch');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'inv')
    EXEC('CREATE SCHEMA inv');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'ops')
    EXEC('CREATE SCHEMA ops');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'cap')
    EXEC('CREATE SCHEMA cap');
GO

IF OBJECT_ID(N'config.AlertThreshold', N'U') IS NULL
BEGIN
    CREATE TABLE config.AlertThreshold (
        ThresholdId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId      UNIQUEIDENTIFIER NULL REFERENCES ctl.Servers(ServerId),
        MetricName    NVARCHAR(64) NOT NULL,
        WarningValue  DECIMAL(18,4) NULL,
        CriticalValue DECIMAL(18,4) NOT NULL,
        UNIQUE (ServerId, MetricName)
    );
END
GO

IF OBJECT_ID(N'baseline.ConfigurationSnapshot', N'U') IS NULL
BEGIN
    CREATE TABLE baseline.ConfigurationSnapshot (
        SnapshotId      UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        SnapshotType    NVARCHAR(64) NOT NULL,
        CapturedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        PayloadHash     CHAR(64) NULL,
        PayloadJson     NVARCHAR(MAX) NOT NULL
    );
    CREATE INDEX IX_Baseline_Server_Type ON baseline.ConfigurationSnapshot (ServerId, SnapshotType, CapturedAt DESC);
END
GO

IF OBJECT_ID(N'baseline.DriftFinding', N'U') IS NULL
BEGIN
    CREATE TABLE baseline.DriftFinding (
        DriftId         UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId      UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DetectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        Category       NVARCHAR(64) NOT NULL,
        Severity       NVARCHAR(16) NOT NULL,
        Summary        NVARCHAR(512) NOT NULL,
        DetailsJson    NVARCHAR(MAX) NULL
    );
END
GO

IF OBJECT_ID(N'audit.ActionLog', N'U') IS NULL
BEGIN
    CREATE TABLE audit.ActionLog (
        LogId           UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        Actor           NVARCHAR(256) NOT NULL,
        Action          NVARCHAR(64) NOT NULL,
        Resource        NVARCHAR(512) NULL,
        HttpMethod      NVARCHAR(8) NULL,
        HttpPath        NVARCHAR(512) NULL,
        StatusCode      INT NULL,
        ServerId        UNIQUEIDENTIFIER NULL REFERENCES ctl.Servers(ServerId),
        DetailsJson     NVARCHAR(MAX) NULL
    );
    CREATE INDEX IX_Audit_Created ON audit.ActionLog (CreatedAt DESC);
END
GO

IF OBJECT_ID(N'[backup].DatabaseBackupFact', N'U') IS NULL
BEGIN
    CREATE TABLE [backup].DatabaseBackupFact (
        BackupFactId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId           UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName       NVARCHAR(256) NOT NULL,
        RecoveryModel      NVARCHAR(32) NULL,
        LastFullBackup       DATETIME2 NULL,
        LastDiffBackup       DATETIME2 NULL,
        LastLogBackup        DATETIME2 NULL,
        RpoHoursTarget       INT NULL,
        IsCompliant          BIT NOT NULL DEFAULT 1,
        CollectedAt          DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT UQ_Backup_Server_Db UNIQUE (ServerId, DatabaseName)
    );
    CREATE INDEX IX_Backup_Server ON [backup].DatabaseBackupFact (ServerId);
END
GO

IF OBJECT_ID(N'hadr.AGReplicaFact', N'U') IS NULL
BEGIN
    CREATE TABLE hadr.AGReplicaFact (
        ReplicaFactId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId            UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        AGName              NVARCHAR(128) NOT NULL,
        ReplicaRole         NVARCHAR(32) NOT NULL,
        SynchronizationState NVARCHAR(64) NULL,
        EstimatedDataLossSeconds INT NULL,
        SuspendReason       NVARCHAR(256) NULL,
        CollectedAt         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_Hadr_Server_AG ON hadr.AGReplicaFact (ServerId, AGName);
END
GO

IF OBJECT_ID(N'patch.InstancePatchFact', N'U') IS NULL
BEGIN
    CREATE TABLE patch.InstancePatchFact (
        PatchFactId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId           UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        ProductVersion     NVARCHAR(64) NULL,
        ProductLevel       NVARCHAR(32) NULL,
        SqlBuildNumber     INT NULL,
        CuBehindCount      INT NOT NULL DEFAULT 0,
        CollectedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE INDEX IX_Patch_Server_Collected ON patch.InstancePatchFact (ServerId, CollectedAt DESC);
END
GO

IF OBJECT_ID(N'inv.DatabaseInventoryFact', N'U') IS NULL
BEGIN
    CREATE TABLE inv.DatabaseInventoryFact (
        InvFactId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId           UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName       NVARCHAR(256) NOT NULL,
        SizeMB             BIGINT NULL,
        [State]            NVARCHAR(32) NULL,
        RecoveryModel      NVARCHAR(32) NULL,
        CollectedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT UQ_Inv_Server_Db UNIQUE (ServerId, DatabaseName)
    );
END
GO

IF OBJECT_ID(N'ops.AgentJobRunFact', N'U') IS NULL
BEGIN
    CREATE TABLE ops.AgentJobRunFact (
        JobRunFactId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId           UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        JobName            NVARCHAR(256) NOT NULL,
        LastRunOutcome     NVARCHAR(32) NULL,
        LastRunDate        DATETIME2 NULL,
        LastRunDurationSec INT NULL,
        IsDisabled         BIT NOT NULL DEFAULT 0,
        CollectedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT UQ_Job_Server_Name UNIQUE (ServerId, JobName)
    );
END
GO

IF OBJECT_ID(N'cap.VolumeSpaceFact', N'U') IS NULL
BEGIN
    CREATE TABLE cap.VolumeSpaceFact (
        VolFactId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId           UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        MountPoint         NVARCHAR(64) NOT NULL,
        TotalGB            DECIMAL(18,2) NULL,
        FreeGB             DECIMAL(18,2) NULL,
        CollectedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT UQ_Vol_Server_Mount UNIQUE (ServerId, MountPoint)
    );
END
GO

/* Default fleet thresholds (NULL ServerId = all instances) */
IF NOT EXISTS (SELECT 1 FROM config.AlertThreshold WHERE ServerId IS NULL AND MetricName = N'blockingSessions')
    INSERT INTO config.AlertThreshold (ServerId, MetricName, WarningValue, CriticalValue)
    VALUES (NULL, N'blockingSessions', 1, 5);
IF NOT EXISTS (SELECT 1 FROM config.AlertThreshold WHERE ServerId IS NULL AND MetricName = N'cpuPercent')
    INSERT INTO config.AlertThreshold (ServerId, MetricName, WarningValue, CriticalValue)
    VALUES (NULL, N'cpuPercent', 75, 90);
IF NOT EXISTS (SELECT 1 FROM config.AlertThreshold WHERE ServerId IS NULL AND MetricName = N'activeSessions')
    INSERT INTO config.AlertThreshold (ServerId, MetricName, WarningValue, CriticalValue)
    VALUES (NULL, N'activeSessions', 500, 2000);
GO
