-- DBAOps Sovereign™ — v5 CMS, Security Tiers, Agent Monitoring, Task Scheduling, Reports
-- Powered by RHOYLLE INNOVATION EMPIRE LLC

USE SovereignRepository;
GO

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

-- =============================================
-- Extend ctl.Servers with security tier
-- =============================================
IF COL_LENGTH('ctl.Servers', 'SecurityTier') IS NULL
    ALTER TABLE ctl.Servers ADD SecurityTier INT NULL;
IF COL_LENGTH('ctl.Servers', 'TierReason') IS NULL
    ALTER TABLE ctl.Servers ADD TierReason NVARCHAR(256) NULL;
IF COL_LENGTH('ctl.Servers', 'CMSGroupPath') IS NULL
    ALTER TABLE ctl.Servers ADD CMSGroupPath NVARCHAR(512) NULL;
GO

-- =============================================
-- cms.Configuration — CMS Source of Truth settings
-- =============================================
IF OBJECT_ID('cms.Configuration','U') IS NOT NULL DROP TABLE cms.Configuration;
CREATE TABLE cms.Configuration (
    ConfigId        INT IDENTITY(1,1) PRIMARY KEY,
    CMSServerName   NVARCHAR(256) NOT NULL,
    CMSDatabase     NVARCHAR(128) NOT NULL DEFAULT 'msdb',
    IsHADREnabled   BIT NOT NULL DEFAULT 0,
    HADRPartner     NVARCHAR(256) NULL,
    HADRMode        NVARCHAR(64) NULL,
    AutoDiscovery   BIT NOT NULL DEFAULT 1,
    DiscoverySchedule NVARCHAR(64) NULL DEFAULT '0 2 * * *',
    LastDiscoveryAt DATETIME2 NULL,
    LastDiscoveryCount INT NULL,
    IsActive        BIT NOT NULL DEFAULT 1,
    SetupCompletedAt DATETIME2 NULL,
    SetupBy         NVARCHAR(256) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- =============================================
-- cms.Groups — CMS server groups / folder structure
-- =============================================
IF OBJECT_ID('cms.Groups','U') IS NOT NULL DROP TABLE cms.Groups;
CREATE TABLE cms.Groups (
    GroupId         INT IDENTITY(1,1) PRIMARY KEY,
    GroupName       NVARCHAR(256) NOT NULL,
    ParentGroupId   INT NULL,
    GroupPath       NVARCHAR(512) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    SecurityTier    INT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_CMSGroup_Parent FOREIGN KEY (ParentGroupId) REFERENCES cms.Groups(GroupId)
);
GO

-- =============================================
-- cms.DiscoveryLog — auto-discovery run log
-- =============================================
IF OBJECT_ID('cms.DiscoveryLog','U') IS NOT NULL DROP TABLE cms.DiscoveryLog;
CREATE TABLE cms.DiscoveryLog (
    LogId           INT IDENTITY(1,1) PRIMARY KEY,
    StartedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt     DATETIME2 NULL,
    ServersFound    INT NULL,
    ServersAdded    INT NULL,
    ServersUpdated  INT NULL,
    Errors          NVARCHAR(MAX) NULL,
    InitiatedBy     NVARCHAR(256) NULL
);
GO

-- =============================================
-- ctl.AgentServiceStatus — SQL Agent on/off monitoring
-- =============================================
IF OBJECT_ID('ctl.AgentServiceStatus','U') IS NOT NULL DROP TABLE ctl.AgentServiceStatus;
CREATE TABLE ctl.AgentServiceStatus (
    StatusId        INT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ServiceName     NVARCHAR(256) NOT NULL DEFAULT 'SQLSERVERAGENT',
    IsRunning       BIT NOT NULL,
    StartMode       NVARCHAR(32) NULL,
    LastStartedAt   DATETIME2 NULL,
    LastStoppedAt   DATETIME2 NULL,
    StopReason      NVARCHAR(512) NULL,
    StoppedBy       NVARCHAR(256) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_AgentStatus UNIQUE (ServerId, ServiceName)
);
GO
CREATE INDEX IX_AgentStatus_Server ON ctl.AgentServiceStatus (ServerId);
GO

-- =============================================
-- task.ScheduledTasks — task execution/scheduling
-- =============================================
IF OBJECT_ID('task.TaskHistory','U') IS NOT NULL DROP TABLE task.TaskHistory;
IF OBJECT_ID('task.ScheduledTasks','U') IS NOT NULL DROP TABLE task.ScheduledTasks;

CREATE TABLE task.ScheduledTasks (
    TaskId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    TaskName        NVARCHAR(256) NOT NULL,
    TaskType        NVARCHAR(64) NOT NULL,
    TargetServerId  UNIQUEIDENTIFIER NULL REFERENCES ctl.Servers(ServerId),
    TargetScope     NVARCHAR(64) NOT NULL DEFAULT 'Server',
    Script          NVARCHAR(MAX) NULL,
    Parameters      NVARCHAR(MAX) NULL,
    Schedule        NVARCHAR(64) NULL,
    ScheduledAt     DATETIME2 NULL,
    RequiredRole    NVARCHAR(32) NOT NULL DEFAULT 'Operator',
    IsEnabled       BIT NOT NULL DEFAULT 1,
    CreatedBy       NVARCHAR(256) NOT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_TaskType CHECK (TaskType IN (
        'IndexRebuild','BackupFull','BackupDiff','BackupLog',
        'DBCCCheckDB','UpdateStats','ShrinkLog','CustomScript',
        'PatchAssessment','SecurityScan','ConfigAudit','AgentRestart',
        'FailoverTest','DataCollect','ReportGenerate'
    )),
    CONSTRAINT CK_TaskScope CHECK (TargetScope IN ('Server','Database','Fleet','Group'))
);
GO

CREATE TABLE task.TaskHistory (
    RunId           UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    TaskId          UNIQUEIDENTIFIER NOT NULL REFERENCES task.ScheduledTasks(TaskId),
    StartedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt     DATETIME2 NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Running',
    Output          NVARCHAR(MAX) NULL,
    ErrorMessage    NVARCHAR(MAX) NULL,
    DurationMs      INT NULL,
    ExecutedBy      NVARCHAR(256) NULL,
    CONSTRAINT CK_TaskRunStatus CHECK (Status IN ('Pending','Running','Completed','Failed','Cancelled','TimedOut'))
);
GO
CREATE INDEX IX_TaskHistory_Task ON task.TaskHistory (TaskId, StartedAt DESC);
GO

-- =============================================
-- rpt.SavedReports — saved/scheduled report definitions
-- =============================================
IF OBJECT_ID('rpt.ReportHistory','U') IS NOT NULL DROP TABLE rpt.ReportHistory;
IF OBJECT_ID('rpt.SavedReports','U') IS NOT NULL DROP TABLE rpt.SavedReports;

CREATE TABLE rpt.SavedReports (
    ReportId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ReportName      NVARCHAR(256) NOT NULL,
    ReportType      NVARCHAR(64) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    QueryOrConfig   NVARCHAR(MAX) NOT NULL,
    OutputFormat    NVARCHAR(32) NOT NULL DEFAULT 'HTML',
    Schedule        NVARCHAR(64) NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    CreatedBy       NVARCHAR(256) NOT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_ReportType CHECK (ReportType IN (
        'FleetOverview','ComplianceSummary','PatchStatus','BackupStatus',
        'SecurityPosture','CapacityForecast','AlertHistory','AuditTrail',
        'TechnologyInventory','PerformanceTrend','Custom'
    )),
    CONSTRAINT CK_OutputFormat CHECK (OutputFormat IN ('HTML','CSV','PDF','JSON'))
);
GO

CREATE TABLE rpt.ReportHistory (
    RunId           UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ReportId        UNIQUEIDENTIFIER NOT NULL REFERENCES rpt.SavedReports(ReportId),
    GeneratedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    OutputFormat    NVARCHAR(32) NOT NULL,
    [RowCount]      INT NULL,
    SizeBytes       INT NULL,
    GeneratedBy     NVARCHAR(256) NULL
);
GO

-- =============================================
-- Seed CMS groups with security tiers
-- =============================================
IF NOT EXISTS (SELECT 1 FROM cms.Groups WHERE GroupName = 'Tier 0 — Critical Infrastructure')
BEGIN
    INSERT INTO cms.Groups (GroupName, GroupPath, Description, SecurityTier) VALUES
    ('Tier 0 — Critical Infrastructure', '/Tier0', 'Domain Controllers, PKI, ADFS, Identity Stores — highest confidentiality', 0),
    ('Tier 1 — Business Critical', '/Tier1', 'Production OLTP, Financial systems, PII databases, HIPAA/PCI workloads', 1),
    ('Tier 2 — Business Support', '/Tier2', 'Reporting, data warehousing, integration, staging environments', 2),
    ('Tier 3 — Development & Training', '/Tier3', 'Dev, QA, UAT, sandbox, training, trial instances', 3);
END
GO

-- Seed built-in report templates
IF NOT EXISTS (SELECT 1 FROM rpt.SavedReports WHERE ReportName = 'Fleet Executive Summary')
BEGIN
    INSERT INTO rpt.SavedReports (ReportName, ReportType, Description, QueryOrConfig, OutputFormat, CreatedBy) VALUES
    ('Fleet Executive Summary', 'FleetOverview', 'High-level fleet health, counts, and KPIs', '{"sections":["instances","health","alerts","compliance","patches"]}', 'HTML', 'system'),
    ('Compliance Audit Report', 'ComplianceSummary', 'Framework compliance scores across all servers', '{"sections":["frameworks","findings","recommendations"]}', 'HTML', 'system'),
    ('Patch Currency Report', 'PatchStatus', 'Patch levels, CU gap analysis, security updates', '{"sections":["gap","catalog","security"]}', 'HTML', 'system'),
    ('Backup Coverage Report', 'BackupStatus', 'Backup compliance, RPO status, recovery readiness', '{"sections":["coverage","rpo","noncompliant"]}', 'HTML', 'system'),
    ('Security Posture Report', 'SecurityPosture', 'TLS, TDE, access audit, compliance posture', '{"sections":["tls","tde","audit","posture"]}', 'HTML', 'system'),
    ('Technology Inventory Report', 'TechnologyInventory', 'All HA/DR technologies deployed across fleet', '{"sections":["technologies","hadr","replication"]}', 'HTML', 'system');
END
GO

PRINT 'DBAOps Sovereign™ — v5 CMS, security tiers, agent monitoring, task scheduling, reports schema applied.';
GO
