-- DBAOps Sovereign™ — SovereignRepository Schema (extended control plane)
-- Powered by RHOYLLE INNOVATION EMPIRE LLC

USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'SovereignRepository')
    CREATE DATABASE SovereignRepository;
GO
USE SovereignRepository;
GO

-- Schemas
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'ctl')
    EXEC('CREATE SCHEMA ctl');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'comp')
    EXEC('CREATE SCHEMA comp');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'perf')
    EXEC('CREATE SCHEMA perf');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'sec')
    EXEC('CREATE SCHEMA sec');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'audit')
    EXEC('CREATE SCHEMA audit');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'config')
    EXEC('CREATE SCHEMA config');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'baseline')
    EXEC('CREATE SCHEMA baseline');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'backup')
    EXEC('CREATE SCHEMA [backup]');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'hadr')
    EXEC('CREATE SCHEMA hadr');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'patch')
    EXEC('CREATE SCHEMA patch');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'inv')
    EXEC('CREATE SCHEMA inv');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'ops')
    EXEC('CREATE SCHEMA ops');
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'cap')
    EXEC('CREATE SCHEMA cap');
GO

-- Drop FK-child-first (new facts before ctl.Servers)
IF OBJECT_ID('cap.VolumeSpaceFact', 'U') IS NOT NULL DROP TABLE cap.VolumeSpaceFact;
IF OBJECT_ID('ops.AgentJobRunFact', 'U') IS NOT NULL DROP TABLE ops.AgentJobRunFact;
IF OBJECT_ID('inv.DatabaseInventoryFact', 'U') IS NOT NULL DROP TABLE inv.DatabaseInventoryFact;
IF OBJECT_ID('patch.InstancePatchFact', 'U') IS NOT NULL DROP TABLE patch.InstancePatchFact;
IF OBJECT_ID('hadr.AGReplicaFact', 'U') IS NOT NULL DROP TABLE hadr.AGReplicaFact;
IF OBJECT_ID('[backup].DatabaseBackupFact', 'U') IS NOT NULL DROP TABLE [backup].DatabaseBackupFact;
IF OBJECT_ID('baseline.DriftFinding', 'U') IS NOT NULL DROP TABLE baseline.DriftFinding;
IF OBJECT_ID('baseline.ConfigurationSnapshot', 'U') IS NOT NULL DROP TABLE baseline.ConfigurationSnapshot;
IF OBJECT_ID('audit.ActionLog', 'U') IS NOT NULL DROP TABLE audit.ActionLog;
IF OBJECT_ID('config.AlertThreshold', 'U') IS NOT NULL DROP TABLE config.AlertThreshold;
IF OBJECT_ID('perf.Snapshots', 'U') IS NOT NULL DROP TABLE perf.Snapshots;
IF OBJECT_ID('ctl.License', 'U') IS NOT NULL DROP TABLE ctl.License;
IF OBJECT_ID('comp.ComplianceScores', 'U') IS NOT NULL DROP TABLE comp.ComplianceScores;
IF OBJECT_ID('ctl.Alerts', 'U') IS NOT NULL DROP TABLE ctl.Alerts;
IF OBJECT_ID('ctl.ConsoleUsers', 'U') IS NOT NULL DROP TABLE ctl.ConsoleUsers;
IF OBJECT_ID('ctl.Servers', 'U') IS NOT NULL DROP TABLE ctl.Servers;

-- ctl.Servers — Fleet inventory
CREATE TABLE ctl.Servers (
    ServerId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerName      NVARCHAR(256) NOT NULL,
    Engine          NVARCHAR(64) NOT NULL DEFAULT 'SQL Server',
    Version         NVARCHAR(64),
    Edition         NVARCHAR(64),
    HealthScore     INT CHECK (HealthScore >= 0 AND HealthScore <= 100),
    LastSeen        DATETIME2,
    Environment     NVARCHAR(64),
    BusinessUnit    NVARCHAR(128),
    Reachable       BIT NULL,
    LastReachCheck  DATETIME2 NULL,
    CreatedAt       DATETIME2 DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2 DEFAULT SYSUTCDATETIME(),
    UNIQUE(ServerName)
);

-- Dashboard / API RBAC (DOMC-style roles; extend with AD/gMSA in production)
CREATE TABLE ctl.ConsoleUsers (
    UserId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    Email           NVARCHAR(256) NOT NULL UNIQUE,
    PasswordHash    NVARCHAR(256) NOT NULL,
    DisplayName     NVARCHAR(128) NULL,
    Role            NVARCHAR(32) NOT NULL,
    IsActive        BIT DEFAULT 1,
    CreatedAt       DATETIME2 DEFAULT SYSUTCDATETIME(),
    CHECK (Role IN ('Administrator', 'Operator', 'Auditor', 'Developer', 'Executive'))
);

-- ctl.Alerts
CREATE TABLE ctl.Alerts (
    AlertId         UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    Severity        NVARCHAR(32) NOT NULL,
    Message         NVARCHAR(MAX),
    Source          NVARCHAR(64) NULL,
    CreatedAt       DATETIME2 DEFAULT SYSUTCDATETIME(),
    AcknowledgedAt  DATETIME2,
    CONSTRAINT FK_Alerts_Server FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
);

-- comp.ComplianceScores
CREATE TABLE comp.ComplianceScores (
    ComplianceId    UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    Framework       NVARCHAR(64) NOT NULL,
    Score           INT CHECK (Score >= 0 AND Score <= 100),
    Status          NVARCHAR(32),
    LastScan        DATETIME2,
    CONSTRAINT FK_Compliance_Server FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
);

-- perf.Snapshots
CREATE TABLE perf.Snapshots (
    SnapshotId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId            UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    CollectedAt         DATETIME2 NOT NULL,
    CpuPercent          DECIMAL(5,2) NULL,
    SqlMemoryUsedMB     INT NULL,
    BlockingSessions    INT NOT NULL DEFAULT 0,
    ActiveSessions      INT NULL,
    BatchRequestsPerSec DECIMAL(18,2) NULL,
    AvgSignalWaitMs     DECIMAL(18,4) NULL,
    RawMetricsJson      NVARCHAR(MAX) NULL,
    CONSTRAINT FK_Snapshots_Server FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
);
CREATE INDEX IX_Snapshots_Server_Collected ON perf.Snapshots (ServerId, CollectedAt DESC);

-- Policy-driven thresholds (fleet default: ServerId NULL)
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)
);

-- Baseline & drift (configuration snapshots — extend with security principals later)
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);

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
);

-- Immutable audit trail (API & console actions)
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);

-- backup.DatabaseBackupFact — RPO-oriented backup coverage (collector-ingested)
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);

-- hadr.AGReplicaFact — Always On replica sync snapshot
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);

-- patch.InstancePatchFact — build / CU currency
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);

-- inv.DatabaseInventoryFact — database list + size + state
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)
);

-- ops.AgentJobRunFact — SQL Agent last-run outcome (fleet job monitor)
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)
);

-- cap.VolumeSpaceFact — drive / mount free space
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)
);

-- ctl.License
CREATE TABLE ctl.License (
    LicenseId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    Edition         NVARCHAR(64) NOT NULL,
    InstancesAllowed INT NOT NULL,
    ExpiresAt       DATETIME2,
    IsActive        BIT DEFAULT 1,
    CreatedAt       DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- Seed license
INSERT INTO ctl.License (Edition, InstancesAllowed, ExpiresAt, IsActive)
VALUES ('Trial', 10, DATEADD(DAY, 30, SYSUTCDATETIME()), 1);

-- Seed console users — inactive by default; complete POST /api/setup/bootstrap before production use
INSERT INTO ctl.ConsoleUsers (Email, PasswordHash, DisplayName, Role, IsActive)
VALUES
(N'admin@sovereign.local', N'$2b$10$IeipWI9IqsnLV63PtvD.KeKX3Wha2FKmQmF7cY8mSHaO1TgV.tAmO', N'Fleet Admin', N'Administrator', 0),
(N'operator@sovereign.local', N'$2b$10$IeipWI9IqsnLV63PtvD.KeKX3Wha2FKmQmF7cY8mSHaO1TgV.tAmO', N'Operator', N'Operator', 0),
(N'auditor@sovereign.local', N'$2b$10$IeipWI9IqsnLV63PtvD.KeKX3Wha2FKmQmF7cY8mSHaO1TgV.tAmO', N'Auditor', N'Auditor', 0);

-- Default fleet thresholds (NULL ServerId = all instances)
INSERT INTO config.AlertThreshold (ServerId, MetricName, WarningValue, CriticalValue)
VALUES
(NULL, N'blockingSessions', 1, 5),
(NULL, N'cpuPercent', 75, 90),
(NULL, N'activeSessions', 500, 2000);

PRINT 'DBAOps Sovereign™ — SovereignRepository schema created.';
GO
