-- DBAOps Sovereign™ — v3 Universal Platform Schema Migration
-- Powered by RHOYLLE INNOVATION EMPIRE LLC
-- Adds: licensing tiers, custom RBAC, compliance frameworks,
-- patch catalog, remediation workflow, webhook system, TLS/TDE tracking.

USE SovereignRepository;
GO

-- =============================================
-- Drop + recreate v3 tables (FK children first)
-- =============================================
-- v12+ adds ops.RemediationExecutionLog → patch.RemediationActions — must drop before parent
IF OBJECT_ID('ops.RemediationExecutionLog', 'U') IS NOT NULL DROP TABLE ops.RemediationExecutionLog;
IF OBJECT_ID('patch.RemediationActions','U') IS NOT NULL DROP TABLE patch.RemediationActions;
IF OBJECT_ID('patch.BuildCatalog','U') IS NOT NULL DROP TABLE patch.BuildCatalog;
IF OBJECT_ID('sec.ComplianceFrameworks','U') IS NOT NULL DROP TABLE sec.ComplianceFrameworks;
IF OBJECT_ID('sec.TLSStatus','U') IS NOT NULL DROP TABLE sec.TLSStatus;
IF OBJECT_ID('sec.TDEStatus','U') IS NOT NULL DROP TABLE sec.TDEStatus;
IF OBJECT_ID('ctl.CustomRoleAssignments','U') IS NOT NULL DROP TABLE ctl.CustomRoleAssignments;
IF OBJECT_ID('ctl.CustomRoles','U') IS NOT NULL DROP TABLE ctl.CustomRoles;
IF OBJECT_ID('ctl.LicenseEntitlements','U') IS NOT NULL DROP TABLE ctl.LicenseEntitlements;
IF OBJECT_ID('config.WebhookDeliveries','U') IS NOT NULL DROP TABLE config.WebhookDeliveries;
IF OBJECT_ID('config.Webhooks','U') IS NOT NULL DROP TABLE config.Webhooks;
IF OBJECT_ID('config.BestPracticeFindings','U') IS NOT NULL DROP TABLE config.BestPracticeFindings;
IF OBJECT_ID('config.BestPracticeRules','U') IS NOT NULL DROP TABLE config.BestPracticeRules;
GO

-- Repair: legacy DBs may have ctl.Servers without ctl.ConsoleUsers (FK in v3 requires it)
IF OBJECT_ID('ctl.ConsoleUsers', 'U') IS NULL
BEGIN
    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'))
    );
END
GO

IF NOT EXISTS (SELECT 1 FROM ctl.ConsoleUsers)
BEGIN
    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);
END
GO

-- =============================================
-- ctl.LicenseEntitlements — 7-tier model
-- =============================================
CREATE TABLE ctl.LicenseEntitlements (
    LicenseId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    Edition          NVARCHAR(64) NOT NULL,
    Tier             NVARCHAR(32) NOT NULL DEFAULT 'Community',
    InstancesAllowed INT NOT NULL DEFAULT 3,
    EnginesAllowed   NVARCHAR(MAX) NULL,
    RetentionDays    INT NOT NULL DEFAULT 7,
    MaxUsers         INT NOT NULL DEFAULT 1,
    MaxCustomRoles   INT NOT NULL DEFAULT 0,
    FeaturesJson     NVARCHAR(MAX) NULL,
    ExpiresAt        DATETIME2 NULL,
    IsActive         BIT NOT NULL DEFAULT 1,
    LicenseKey       NVARCHAR(512) NULL,
    CreatedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_License_Tier CHECK (Tier IN (
        'Community','Starter','Professional','Enterprise',
        'Government','CriticalInfra','Unlimited'
    ))
);
GO

-- =============================================
-- ctl.CustomRoles — user-defined RBAC
-- =============================================
CREATE TABLE ctl.CustomRoles (
    RoleId          INT IDENTITY(1,1) PRIMARY KEY,
    RoleName        NVARCHAR(64) NOT NULL UNIQUE,
    PermissionsJson NVARCHAR(MAX) NOT NULL,
    IsBuiltIn       BIT NOT NULL DEFAULT 0,
    CreatedBy       NVARCHAR(256) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TABLE ctl.CustomRoleAssignments (
    AssignmentId    INT IDENTITY(1,1) PRIMARY KEY,
    UserId          UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.ConsoleUsers(UserId),
    RoleId          INT NOT NULL REFERENCES ctl.CustomRoles(RoleId),
    GrantedBy       NVARCHAR(256) NULL,
    GrantedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_RoleAssignment UNIQUE (UserId, RoleId)
);
GO

-- =============================================
-- sec — compliance frameworks + TLS/TDE
-- =============================================
CREATE TABLE sec.ComplianceFrameworks (
    FrameworkId     INT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    FrameworkName   NVARCHAR(64) NOT NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    LastAuditDate   DATETIME2 NULL,
    ComplianceScore INT NULL CHECK (ComplianceScore >= 0 AND ComplianceScore <= 100),
    FindingsJson    NVARCHAR(MAX) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_Compliance_Server_FW UNIQUE (ServerId, FrameworkName)
);
GO

CREATE TABLE sec.TLSStatus (
    TLSStatusId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ForceEncryption BIT NULL,
    CertificateExpiry DATETIME2 NULL,
    CertificateSubject NVARCHAR(512) NULL,
    TLSVersion      NVARCHAR(16) NULL,
    IsCompliant     BIT NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TABLE sec.TDEStatus (
    TDEStatusId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    EncryptionState INT NULL,
    EncryptorType   NVARCHAR(64) NULL,
    KeyAlgorithm    NVARCHAR(64) NULL,
    KeyLength       INT NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_TDE_Server_DB UNIQUE (ServerId, DatabaseName)
);
GO

-- =============================================
-- patch — build catalog + remediation
-- =============================================
CREATE TABLE patch.BuildCatalog (
    CatalogId       INT IDENTITY(1,1) PRIMARY KEY,
    Engine          NVARCHAR(64) NOT NULL,
    MajorVersion    INT NOT NULL,
    BuildNumber     NVARCHAR(64) NOT NULL,
    DisplayName     NVARCHAR(256) NOT NULL,
    ReleaseDate     DATE NOT NULL,
    IsSecurityUpdate BIT NOT NULL DEFAULT 0,
    CVEList         NVARCHAR(MAX) NULL,
    KBArticle       NVARCHAR(64) NULL,
    DownloadUrl     NVARCHAR(512) NULL,
    EndOfSupport    DATE NULL,
    CONSTRAINT UQ_Build_Engine_Version UNIQUE (Engine, BuildNumber)
);
GO

CREATE TABLE patch.RemediationActions (
    ActionId        UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ActionType      NVARCHAR(64) NOT NULL,
    Category        NVARCHAR(64) NULL,
    Title           NVARCHAR(256) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    ActionScript    NVARCHAR(MAX) NULL,
    Severity        NVARCHAR(32) NOT NULL DEFAULT 'Medium',
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Pending',
    RequestedBy     NVARCHAR(256) NULL,
    ApprovedBy      NVARCHAR(256) NULL,
    ApprovedAt      DATETIME2 NULL,
    ScheduledFor    DATETIME2 NULL,
    ExecutedAt      DATETIME2 NULL,
    ResultJson      NVARCHAR(MAX) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_Remediation_Status CHECK (Status IN (
        'Pending','Approved','Scheduled','Running','Completed','Failed','Dismissed'
    ))
);
GO
CREATE INDEX IX_Remediation_Server_Status ON patch.RemediationActions (ServerId, Status);
GO

-- =============================================
-- config — webhooks + best practices
-- =============================================
CREATE TABLE config.Webhooks (
    WebhookId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    [Name]          NVARCHAR(128) NOT NULL,
    [Url]           NVARCHAR(1024) NOT NULL,
    Secret          NVARCHAR(256) NULL,
    Events          NVARCHAR(MAX) NOT NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    CreatedBy       NVARCHAR(256) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TABLE config.WebhookDeliveries (
    DeliveryId      UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    WebhookId       UNIQUEIDENTIFIER NOT NULL REFERENCES config.Webhooks(WebhookId),
    EventType       NVARCHAR(64) NOT NULL,
    PayloadJson     NVARCHAR(MAX) NULL,
    StatusCode      INT NULL,
    ResponseBody    NVARCHAR(MAX) NULL,
    DeliveredAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Success         BIT NOT NULL DEFAULT 0
);
GO

CREATE TABLE config.BestPracticeRules (
    RuleId          INT IDENTITY(1,1) PRIMARY KEY,
    Engine          NVARCHAR(64) NOT NULL DEFAULT 'SQL Server',
    Category        NVARCHAR(64) NOT NULL,
    RuleName        NVARCHAR(256) NOT NULL UNIQUE,
    Description     NVARCHAR(MAX) NULL,
    CheckQuery      NVARCHAR(MAX) NULL,
    Severity        NVARCHAR(32) NOT NULL DEFAULT 'Warning',
    Recommendation  NVARCHAR(MAX) NULL,
    DocumentationUrl NVARCHAR(512) NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1
);
GO

CREATE TABLE config.BestPracticeFindings (
    FindingId       UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    RuleId          INT NOT NULL REFERENCES config.BestPracticeRules(RuleId),
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    CurrentValue    NVARCHAR(256) NULL,
    ExpectedValue   NVARCHAR(256) NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Open',
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE INDEX IX_BPFindings_Server ON config.BestPracticeFindings (ServerId, Status);
GO

-- =============================================
-- Seed: default license (Community tier)
-- =============================================
IF NOT EXISTS (SELECT 1 FROM ctl.LicenseEntitlements WHERE Tier = 'Community')
    INSERT INTO ctl.LicenseEntitlements (Edition, Tier, InstancesAllowed, EnginesAllowed, RetentionDays, MaxUsers, MaxCustomRoles, FeaturesJson)
    VALUES ('DBAOps Sovereign Community', 'Community', 3,
        '["SQL Server","PostgreSQL"]', 7, 1, 0,
        '{"fleet.view":true,"fleet.register":true,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":false,"compliance.view":false,"compliance.audit":false,"remediation.view":false,"remediation.execute":false,"ai.predictions":false,"ai.rootcause":false,"security.posture":false,"security.audit":false,"admin.users":false,"admin.roles.custom":false,"admin.license":true,"integrations.powerbi":false,"integrations.webhook":false,"export.csv":false,"export.odata":false}');
GO

-- Seed: built-in RBAC roles with permission flags
IF NOT EXISTS (SELECT 1 FROM ctl.CustomRoles WHERE RoleName = 'Administrator')
INSERT INTO ctl.CustomRoles (RoleName, PermissionsJson, IsBuiltIn) VALUES
('Administrator', '{"fleet.view":true,"fleet.register":true,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":true,"compliance.view":true,"compliance.audit":true,"remediation.view":true,"remediation.execute":true,"ai.predictions":true,"ai.rootcause":true,"security.posture":true,"security.audit":true,"admin.users":true,"admin.roles.custom":true,"admin.license":true,"integrations.powerbi":true,"integrations.webhook":true,"export.csv":true,"export.odata":true}', 1);
IF NOT EXISTS (SELECT 1 FROM ctl.CustomRoles WHERE RoleName = 'Operator')
INSERT INTO ctl.CustomRoles (RoleName, PermissionsJson, IsBuiltIn) VALUES
('Operator', '{"fleet.view":true,"fleet.register":false,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":true,"compliance.view":true,"compliance.audit":false,"remediation.view":true,"remediation.execute":false,"ai.predictions":true,"ai.rootcause":true,"security.posture":true,"security.audit":false,"admin.users":false,"admin.roles.custom":false,"admin.license":false,"integrations.powerbi":true,"integrations.webhook":false,"export.csv":true,"export.odata":true}', 1);
IF NOT EXISTS (SELECT 1 FROM ctl.CustomRoles WHERE RoleName = 'Auditor')
INSERT INTO ctl.CustomRoles (RoleName, PermissionsJson, IsBuiltIn) VALUES
('Auditor', '{"fleet.view":true,"fleet.register":false,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":false,"compliance.view":true,"compliance.audit":true,"remediation.view":true,"remediation.execute":false,"ai.predictions":false,"ai.rootcause":false,"security.posture":true,"security.audit":true,"admin.users":false,"admin.roles.custom":false,"admin.license":false,"integrations.powerbi":true,"integrations.webhook":false,"export.csv":true,"export.odata":true}', 1);
IF NOT EXISTS (SELECT 1 FROM ctl.CustomRoles WHERE RoleName = 'Developer')
INSERT INTO ctl.CustomRoles (RoleName, PermissionsJson, IsBuiltIn) VALUES
('Developer', '{"fleet.view":true,"fleet.register":false,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":false,"compliance.view":false,"compliance.audit":false,"remediation.view":false,"remediation.execute":false,"ai.predictions":false,"ai.rootcause":false,"security.posture":false,"security.audit":false,"admin.users":false,"admin.roles.custom":false,"admin.license":false,"integrations.powerbi":false,"integrations.webhook":false,"export.csv":false,"export.odata":false}', 1);
IF NOT EXISTS (SELECT 1 FROM ctl.CustomRoles WHERE RoleName = 'Executive')
INSERT INTO ctl.CustomRoles (RoleName, PermissionsJson, IsBuiltIn) VALUES
('Executive', '{"fleet.view":true,"fleet.register":false,"telemetry.view":true,"alerts.view":true,"alerts.acknowledge":false,"compliance.view":true,"compliance.audit":false,"remediation.view":true,"remediation.execute":false,"ai.predictions":true,"ai.rootcause":true,"security.posture":true,"security.audit":false,"admin.users":false,"admin.roles.custom":false,"admin.license":true,"integrations.powerbi":true,"integrations.webhook":false,"export.csv":true,"export.odata":true}', 1);
GO

-- Seed: best practice rules for SQL Server
IF NOT EXISTS (SELECT 1 FROM config.BestPracticeRules WHERE RuleName = 'MaxMemory_NotDefault')
INSERT INTO config.BestPracticeRules (Engine, Category, RuleName, Description, Severity, Recommendation) VALUES
('SQL Server', 'Memory', 'MaxMemory_NotDefault', 'Max Server Memory should not be left at default (2147483647 MB)', 'Critical', 'Set Max Server Memory to approximately 80-90% of total physical memory, leaving room for the OS and other processes.'),
('SQL Server', 'Parallelism', 'MAXDOP_NotZero', 'MAXDOP should not be 0 on multi-core systems', 'Warning', 'Set MAXDOP to the number of physical cores per NUMA node (typically 4-8). Use ALTER SERVER CONFIGURATION SET MAXDOP = N.'),
('SQL Server', 'Parallelism', 'CostThreshold_NotDefault', 'Cost Threshold for Parallelism default of 5 is too low for most workloads', 'Warning', 'Increase Cost Threshold for Parallelism to 25-50. Monitor query plans before and after.'),
('SQL Server', 'TempDB', 'TempDB_MultipleFiles', 'TempDB should have multiple data files equal to the number of logical processors (up to 8)', 'Warning', 'Add tempdb data files to match CPU cores (up to 8). All files should be equal in size with the same autogrowth.'),
('SQL Server', 'Security', 'SA_Disabled', 'The SA account should be disabled or renamed', 'Critical', 'Disable the SA account: ALTER LOGIN sa DISABLE; Use Windows Authentication or named SQL logins instead.'),
('SQL Server', 'Backup', 'Checksum_Enabled', 'Backup checksum should be enabled', 'Warning', 'Use WITH CHECKSUM on all backup operations. Enable BACKUP CHECKSUM as a server default.'),
('SQL Server', 'IO', 'InstantFileInit', 'Instant File Initialization should be enabled for the SQL Server service account', 'Warning', 'Grant the SQL Server service account the SE_MANAGE_VOLUME_NAME privilege (Perform Volume Maintenance Tasks).'),
('SQL Server', 'Recovery', 'AutoShrink_Disabled', 'Auto-shrink should be disabled on all databases', 'Critical', 'Disable auto-shrink: ALTER DATABASE [dbname] SET AUTO_SHRINK OFF; Auto-shrink causes fragmentation and I/O overhead.');
GO

-- Seed: SQL Server patch build catalog (recent CUs)
IF NOT EXISTS (SELECT 1 FROM patch.BuildCatalog WHERE Engine = 'SQL Server' AND BuildNumber = '16.0.4175.1')
INSERT INTO patch.BuildCatalog (Engine, MajorVersion, BuildNumber, DisplayName, ReleaseDate, IsSecurityUpdate, KBArticle) VALUES
('SQL Server', 16, '16.0.4175.1', 'SQL Server 2022 CU17', '2025-10-16', 0, 'KB5042578'),
('SQL Server', 16, '16.0.4165.4', 'SQL Server 2022 CU16', '2025-08-14', 1, 'KB5041160'),
('SQL Server', 16, '16.0.4155.4', 'SQL Server 2022 CU15', '2025-06-12', 0, 'KB5039747'),
('SQL Server', 15, '15.0.4415.2', 'SQL Server 2019 CU30', '2025-10-10', 0, 'KB5042749'),
('SQL Server', 15, '15.0.4405.4', 'SQL Server 2019 CU29', '2025-08-08', 1, 'KB5041233'),
('SQL Server', 14, '14.0.3485.1', 'SQL Server 2017 CU31 + GDR', '2025-01-14', 1, 'KB5040940');
GO
-- PostgreSQL builds
IF NOT EXISTS (SELECT 1 FROM patch.BuildCatalog WHERE Engine = 'PostgreSQL' AND BuildNumber = '17.2')
INSERT INTO patch.BuildCatalog (Engine, MajorVersion, BuildNumber, DisplayName, ReleaseDate, IsSecurityUpdate) VALUES
('PostgreSQL', 17, '17.2', 'PostgreSQL 17.2', '2025-11-14', 1),
('PostgreSQL', 16, '16.6', 'PostgreSQL 16.6', '2025-11-14', 1),
('PostgreSQL', 15, '15.10', 'PostgreSQL 15.10', '2025-11-14', 1);
GO
-- MySQL builds
IF NOT EXISTS (SELECT 1 FROM patch.BuildCatalog WHERE Engine = 'MySQL' AND BuildNumber = '8.4.3')
INSERT INTO patch.BuildCatalog (Engine, MajorVersion, BuildNumber, DisplayName, ReleaseDate, IsSecurityUpdate) VALUES
('MySQL', 8, '8.4.3', 'MySQL 8.4.3 LTS', '2025-10-15', 1),
('MySQL', 8, '8.0.40', 'MySQL 8.0.40', '2025-10-15', 1),
('MySQL', 9, '9.1.0', 'MySQL 9.1.0 Innovation', '2025-10-15', 0);
GO
-- Oracle builds
IF NOT EXISTS (SELECT 1 FROM patch.BuildCatalog WHERE Engine = 'Oracle' AND BuildNumber = '23.6.0')
INSERT INTO patch.BuildCatalog (Engine, MajorVersion, BuildNumber, DisplayName, ReleaseDate, IsSecurityUpdate) VALUES
('Oracle', 23, '23.6.0', 'Oracle Database 23ai 23.6', '2025-10-15', 1),
('Oracle', 19, '19.25.0', 'Oracle Database 19c RU 19.25', '2025-10-15', 1);
GO

-- Compliance framework templates
IF NOT EXISTS (SELECT 1 FROM config.BestPracticeRules WHERE RuleName = 'CJIS_Encryption_AtRest')
INSERT INTO config.BestPracticeRules (Engine, Category, RuleName, Description, Severity, Recommendation) VALUES
('SQL Server', 'CJIS', 'CJIS_Encryption_AtRest', 'CJIS Policy 5.10.1.2: Criminal justice data must be encrypted at rest', 'Critical', 'Enable TDE on all databases containing CJI data. Use AES-256 encryption.'),
('SQL Server', 'CJIS', 'CJIS_Encryption_InTransit', 'CJIS Policy 5.10.1.1: Data in transit must use TLS 1.2+', 'Critical', 'Enable Force Encryption on the SQL Server instance. Ensure TLS 1.2 or higher is configured.'),
('SQL Server', 'CJIS', 'CJIS_AuditLogging', 'CJIS Policy 5.4.1: Audit logs must record all access to CJI', 'Critical', 'Enable SQL Server Audit with login, schema change, and data access events.'),
('SQL Server', 'NIST', 'NIST_AC2_AccountManagement', 'NIST 800-53 AC-2: Manage information system accounts', 'Warning', 'Review and disable orphaned logins. Enforce password policies. Remove unnecessary sysadmin members.'),
('SQL Server', 'NIST', 'NIST_AU6_AuditReview', 'NIST 800-53 AU-6: Review and analyze audit records', 'Warning', 'Implement automated review of SQL Server audit logs. Flag anomalous access patterns.'),
('SQL Server', 'PCI_DSS', 'PCI_Req8_UniqueIDs', 'PCI DSS Req 8: Assign unique ID to each person with access', 'Critical', 'Eliminate shared SQL logins. Each DBA must have a named account with individual credentials.'),
('SQL Server', 'HIPAA', 'HIPAA_AccessControl', 'HIPAA 164.312(a): Implement access controls for ePHI databases', 'Critical', 'Restrict database access to minimum necessary. Use role-based access. Enable row-level security for PHI tables.'),
('SQL Server', 'DISA_STIG', 'STIG_SqlListenerEncryption', 'DISA STIG V-213966: SQL Server must use TLS for listener', 'Critical', 'Configure Force Protocol Encryption in SQL Server Configuration Manager. Deploy a trusted certificate.');
GO

PRINT 'DBAOps Sovereign™ — v3 universal platform schema applied.';
GO
