/*
 * DBAOps Sovereign™ — Schema v12
 *
 * Delivers the compliance / learning / analytics / remediation gaps from the
 * framework review (additive only; idempotent on re-run).
 *
 * Already present from v8 (schema-v8-differentiators.sql) — not recreated here:
 *   - learning.Modules, learning.UserProgress
 *   - analytics.ValueEvents, analytics.ValueConfiguration
 *
 * This file adds:
 *   1. sec.ComplianceFrameworkCatalog — master framework catalog (separate from per-server sec.ComplianceFrameworks scores)
 *   2. sec.ComplianceControls — Severity, RemediationGuidance, CheckQuery, AutomatedCheck, FrameworkCatalogId
 *   3. ops.RemediationExecutionLog — execution + rollback audit for patch.RemediationActions
 *   4. Optional seed: learning.Modules rows when table is empty
 */

USE SovereignRepository;
GO

-- ===== 1. Master compliance framework catalog =====

IF OBJECT_ID('sec.ComplianceFrameworkCatalog', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ComplianceFrameworkCatalog (
    CatalogId           INT IDENTITY(1,1) PRIMARY KEY,
    FrameworkCode       NVARCHAR(64)  NOT NULL,
    FrameworkName       NVARCHAR(200) NOT NULL,
    StandardBody        NVARCHAR(128) NULL,
    Version             NVARCHAR(32)  NULL,
    Description         NVARCHAR(MAX) NULL,
    ControlCount        INT NULL,
    IsActive            BIT NOT NULL DEFAULT 1,
    CreatedAt           DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_ComplianceFrameworkCatalog_Code UNIQUE (FrameworkCode)
  );
  CREATE NONCLUSTERED INDEX IX_ComplianceFrameworkCatalog_Active ON sec.ComplianceFrameworkCatalog (IsActive, FrameworkCode);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sec.ComplianceFrameworkCatalog WHERE FrameworkCode = 'SOX')
BEGIN
  INSERT INTO sec.ComplianceFrameworkCatalog (FrameworkCode, FrameworkName, StandardBody, Version, Description, ControlCount, IsActive) VALUES
    ('SOX', 'Sarbanes-Oxley Act', 'SEC / PCAOB', '2002', 'Financial reporting and internal controls (SOX 404).', NULL, 1),
    ('HIPAA', 'HIPAA Security Rule', 'HHS', '2013', 'Protected health information safeguards.', NULL, 1),
    ('PCI-DSS', 'PCI Data Security Standard', 'PCI SSC', '4.0', 'Payment card data protection.', NULL, 1),
    ('GDPR', 'General Data Protection Regulation', 'EU', '2016/679', 'EU personal data processing.', NULL, 1),
    ('NIST', 'NIST SP 800-53', 'NIST', 'Rev 5', 'Security and privacy controls for federal and industry systems.', NULL, 1),
    ('FedRAMP', 'FedRAMP Baseline', 'GSA', 'High', 'Cloud security authorization for federal use.', NULL, 1),
    ('DORA', 'Digital Operational Resilience Act', 'EU', '2025', 'ICT risk management for financial entities.', NULL, 1),
    ('CJIS', 'CJIS Security Policy', 'FBI', '5.9+', 'Criminal justice information services.', NULL, 1),
    ('DISA-STIG', 'DISA Security Technical Implementation Guides', 'DISA', NULL, 'DoD configuration benchmarks.', NULL, 1),
    ('ISO27001', 'ISO/IEC 27001', 'ISO', '2022', 'Information security management systems.', NULL, 1);
END;
GO

-- ===== 2. Enhance sec.ComplianceControls (executable check + guidance) =====

IF COL_LENGTH('sec.ComplianceControls', 'Severity') IS NULL
  ALTER TABLE sec.ComplianceControls ADD Severity NVARCHAR(32) NOT NULL CONSTRAINT DF_CompCtrl_Severity DEFAULT 'Medium';
GO

IF COL_LENGTH('sec.ComplianceControls', 'RemediationGuidance') IS NULL
  ALTER TABLE sec.ComplianceControls ADD RemediationGuidance NVARCHAR(MAX) NULL;
GO

IF COL_LENGTH('sec.ComplianceControls', 'CheckQuery') IS NULL
  ALTER TABLE sec.ComplianceControls ADD CheckQuery NVARCHAR(MAX) NULL;
GO

IF COL_LENGTH('sec.ComplianceControls', 'AutomatedCheck') IS NULL
  ALTER TABLE sec.ComplianceControls ADD AutomatedCheck BIT NOT NULL CONSTRAINT DF_CompCtrl_AutoCheck DEFAULT 0;
GO

IF COL_LENGTH('sec.ComplianceControls', 'FrameworkCatalogId') IS NULL
BEGIN
  ALTER TABLE sec.ComplianceControls ADD FrameworkCatalogId INT NULL;
  ALTER TABLE sec.ComplianceControls ADD CONSTRAINT FK_CompCtrl_FrameworkCatalog
    FOREIGN KEY (FrameworkCatalogId) REFERENCES sec.ComplianceFrameworkCatalog (CatalogId);
  CREATE NONCLUSTERED INDEX IX_CompCtrl_FrameworkCatalog ON sec.ComplianceControls (FrameworkCatalogId);
END;
GO

-- Map existing FrameworkId strings to catalog rows
UPDATE c
SET c.FrameworkCatalogId = cat.CatalogId
FROM sec.ComplianceControls c
INNER JOIN sec.ComplianceFrameworkCatalog cat ON cat.FrameworkCode = c.FrameworkId
WHERE c.FrameworkCatalogId IS NULL;
GO

-- Example executable checks (templates — run in collector context with proper permissions)
UPDATE sec.ComplianceControls SET
  Severity = 'High',
  AutomatedCheck = 1,
  RemediationGuidance = 'Enable and verify SQL Server Audit or Extended Events for security-relevant actions; retain per policy.',
  CheckQuery = N'SELECT CASE WHEN EXISTS (SELECT 1 FROM sys.server_audit_specifications WHERE is_state_enabled = 1) THEN 1 ELSE 0 END AS AuditEnabled;'
WHERE ControlCode = 'NIST-AU-2' AND FrameworkId = 'NIST';

UPDATE sec.ComplianceControls SET
  Severity = 'Critical',
  AutomatedCheck = 1,
  RemediationGuidance = 'Ensure FULL recovery model and recent log backups for databases under PCI scope.',
  CheckQuery = N'SELECT d.name AS DatabaseName, d.recovery_model_desc FROM sys.databases d WHERE d.database_id > 4 AND d.recovery_model_desc <> ''FULL'';'
WHERE ControlCode = 'PCI-3.4' AND FrameworkId = 'PCI-DSS';

UPDATE sec.ComplianceControls SET
  Severity = 'High',
  AutomatedCheck = 1,
  RemediationGuidance = 'Verify TDE or equivalent protection for databases storing regulated data.',
  CheckQuery = N'SELECT d.name FROM sys.databases d LEFT JOIN sys.dm_database_encryption_keys k ON d.database_id = k.database_id WHERE d.database_id > 4 AND (k.encryption_state IS NULL OR k.encryption_state <> 3);'
WHERE ControlCode = 'NIST-SC-28' AND FrameworkId = 'NIST';
GO

-- ===== 3. Remediation execution log (rollback auditing) =====

IF OBJECT_ID('ops.RemediationExecutionLog', 'U') IS NULL
BEGIN
  CREATE TABLE ops.RemediationExecutionLog (
    ExecutionLogId      BIGINT IDENTITY(1,1) PRIMARY KEY,
    RemediationActionId UNIQUEIDENTIFIER NOT NULL,
    ServerId            UNIQUEIDENTIFIER NOT NULL,
    TriggeredAt         DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    TriggeredBy         NVARCHAR(256) NOT NULL,
    ApprovedBy          NVARCHAR(256) NULL,
    ApprovedAt          DATETIME2 NULL,
    ExecutedAt          DATETIME2 NULL,
    CompletedAt         DATETIME2 NULL,
    Status              NVARCHAR(32) NOT NULL,
    ScriptExecuted      NVARCHAR(MAX) NULL,
    Result              NVARCHAR(MAX) NULL,
    RollbackExecuted    BIT NOT NULL DEFAULT 0,
    RollbackScript      NVARCHAR(MAX) NULL,
    RollbackResult      NVARCHAR(MAX) NULL,
    RollbackAt          DATETIME2 NULL,
    CONSTRAINT FK_RemedExecLog_Action FOREIGN KEY (RemediationActionId) REFERENCES patch.RemediationActions (ActionId),
    CONSTRAINT FK_RemedExecLog_Server FOREIGN KEY (ServerId) REFERENCES ctl.Servers (ServerId),
    CONSTRAINT CK_RemedExecLog_Status CHECK (Status IN (
      'Pending','Approved','Scheduled','Running','Executing','Completed','Failed','RolledBack','Cancelled','Dismissed'
    ))
  );
  CREATE NONCLUSTERED INDEX IX_RemedExecLog_Action ON ops.RemediationExecutionLog (RemediationActionId, TriggeredAt DESC);
  CREATE NONCLUSTERED INDEX IX_RemedExecLog_Server ON ops.RemediationExecutionLog (ServerId, Status, TriggeredAt DESC);
END;
GO

-- ===== 4. Seed learning.Modules when empty (DB-backed curriculum) =====

IF NOT EXISTS (SELECT 1 FROM learning.Modules)
BEGIN
  INSERT INTO learning.Modules (ModuleCode, ModuleTitle, Description, Category, Engine, DifficultyLevel, EstimatedMinutes, VideoUrl, ContentHtml, Prerequisites, IsActive) VALUES
    ('SQL-PERF-101', 'SQL Server Performance Fundamentals', 'Waits, baselines, and DMV-driven triage.', 'Performance', 'SQL Server', 1, 45, NULL, N'<p>Introduction to wait stats, baselines, and the Sovereign telemetry model.</p>', NULL, 1),
    ('SQL-HADR-201', 'Always On Availability Groups', 'Topology, health, and failover readiness.', 'High Availability', 'SQL Server', 3, 90, NULL, N'<p>AG roles, synchronization, and recoverability ties to Sovereign assurance.</p>', '["SQL-PERF-101"]', 1),
    ('SQL-SEC-301', 'Database Security & Compliance', 'TDE, access, audit, and evidence packs.', 'Security', 'SQL Server', 3, 60, NULL, N'<p>Map controls to sec.ComplianceControls and evidence generation.</p>', NULL, 1),
    ('PG-OPS-101', 'PostgreSQL Operations Essentials', 'Connections, vacuum, and monitoring hooks.', 'Operations', 'PostgreSQL', 2, 50, NULL, N'<p>Collector-safe monitoring for PostgreSQL estates.</p>', NULL, 1),
    ('ORA-LIC-201', 'Oracle Licensing Awareness', 'Edition features and optimization posture.', 'Licensing', 'Oracle', 2, 40, NULL, N'<p>High-level license and feature awareness for multi-engine fleets.</p>', NULL, 1);
END;
GO

PRINT 'Schema v12 (Compliance catalog, control checks, remediation execution log) applied successfully.';
GO
