/*
 * DBAOps Sovereign™ — Schema v7: Assurance Graph & Action Engine
 * Evidence-Bound Autonomous Data Security and Operational Assurance
 *
 * Features:
 *   1. Assurance Graph / Blast Radius Engine
 *   2. Recoverability Assurance (full truth)
 *   3. Policy-as-Code Engine
 *   4. Economic Risk Engine
 *   5. Continuous Evidence Packs
 *   6. Safe Autonomous Remediation Orchestrator
 *   7. Sovereign Data Residency & Trust Controls
 */

USE SovereignRepository;
GO

-- ===== 1. Assurance Graph =====
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'graph')
  EXEC('CREATE SCHEMA graph');
GO

IF OBJECT_ID('graph.ExposureNode', 'U') IS NULL
BEGIN
  CREATE TABLE graph.ExposureNode (
    NodeId         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    NodeType       NVARCHAR(32)     NOT NULL, -- Server, Database, Identity, Column, Application, BackupCopy, Replica, NetworkPath
    NodeName       NVARCHAR(512)    NOT NULL,
    ServerId       UNIQUEIDENTIFIER NULL,
    Properties     NVARCHAR(MAX)    NULL, -- JSON
    SensitivityScore INT            NOT NULL DEFAULT 0,
    ExposureScore    INT            NOT NULL DEFAULT 0,
    BlastRadius      INT            NOT NULL DEFAULT 0,
    LastEvaluated    DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_ExpNode_Type ON graph.ExposureNode (NodeType, ExposureScore DESC);
END;
GO

IF OBJECT_ID('graph.ExposureEdge', 'U') IS NULL
BEGIN
  CREATE TABLE graph.ExposureEdge (
    EdgeId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    SourceNodeId UNIQUEIDENTIFIER NOT NULL,
    TargetNodeId UNIQUEIDENTIFIER NOT NULL,
    EdgeType     NVARCHAR(64)     NOT NULL, -- CanAccess, Contains, ReplicatesTo, BackedUpTo, DependsOn, Exposes
    Weight       INT              NOT NULL DEFAULT 1,
    Properties   NVARCHAR(MAX)    NULL,
    FOREIGN KEY (SourceNodeId) REFERENCES graph.ExposureNode(NodeId),
    FOREIGN KEY (TargetNodeId) REFERENCES graph.ExposureNode(NodeId)
  );
  CREATE NONCLUSTERED INDEX IX_ExpEdge_Source ON graph.ExposureEdge (SourceNodeId);
  CREATE NONCLUSTERED INDEX IX_ExpEdge_Target ON graph.ExposureEdge (TargetNodeId);
END;
GO

IF OBJECT_ID('graph.BlastRadiusSnapshot', 'U') IS NULL
BEGIN
  CREATE TABLE graph.BlastRadiusSnapshot (
    SnapshotId   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    NodeId       UNIQUEIDENTIFIER NOT NULL,
    NodeType     NVARCHAR(32)     NOT NULL,
    NodeName     NVARCHAR(512)    NOT NULL,
    BlastRadius  INT              NOT NULL,
    AffectedNodes INT             NOT NULL DEFAULT 0,
    SensitiveDataReachable INT    NOT NULL DEFAULT 0,
    RecoverabilityGap      INT    NOT NULL DEFAULT 0,
    ComplianceExposure     INT    NOT NULL DEFAULT 0,
    FinancialExposure      DECIMAL(18,2) NULL,
    Narrative    NVARCHAR(MAX)    NULL,
    EvaluatedAt  DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (NodeId) REFERENCES graph.ExposureNode(NodeId)
  );
END;
GO

-- ===== 2. Recoverability Assurance =====
IF OBJECT_ID('backup.RecoverabilityAssurance', 'U') IS NULL
BEGIN
  CREATE TABLE [backup].RecoverabilityAssurance (
    AssuranceId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId          UNIQUEIDENTIFIER NOT NULL,
    DatabaseName      NVARCHAR(256)    NOT NULL,
    BackupExists      BIT NOT NULL DEFAULT 0,
    RestoreTested     BIT NOT NULL DEFAULT 0,
    RTOAchieved_Sec   INT NULL,
    RTORequired_Sec   INT NULL,
    RPOAchieved_Min   INT NULL,
    RPORequired_Min   INT NULL,
    EncryptionVerified BIT NOT NULL DEFAULT 0,
    OffsiteCopyVerified BIT NOT NULL DEFAULT 0,
    ImmutableCopyExists BIT NOT NULL DEFAULT 0,
    RecoveryCredsVerified BIT NOT NULL DEFAULT 0,
    DependencyChainVerified BIT NOT NULL DEFAULT 0,
    DRRunbookAttached BIT NOT NULL DEFAULT 0,
    AssuranceScore    INT NOT NULL DEFAULT 0,
    AssuranceGrade    CHAR(1) NOT NULL DEFAULT 'F',
    LastAssessed      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Narrative         NVARCHAR(MAX) NULL,
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_RecovAssur_Server ON [backup].RecoverabilityAssurance (ServerId, AssuranceScore);
END;
GO

-- ===== 3. Policy-as-Code =====
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'policy')
  EXEC('CREATE SCHEMA policy');
GO

IF OBJECT_ID('policy.Rules', 'U') IS NULL
BEGIN
  CREATE TABLE policy.Rules (
    RuleId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RuleName      NVARCHAR(256)    NOT NULL,
    RuleCode      NVARCHAR(64)     NOT NULL UNIQUE,
    Category      NVARCHAR(64)     NOT NULL,
    Severity      NVARCHAR(16)     NOT NULL DEFAULT 'High',
    Description   NVARCHAR(MAX)    NOT NULL,
    PolicyLogic   NVARCHAR(MAX)    NOT NULL, -- JSON: conditions, thresholds, targets
    IsEnabled     BIT              NOT NULL DEFAULT 1,
    EnforcementMode NVARCHAR(16)   NOT NULL DEFAULT 'Alert', -- Alert, Block, AutoRemediate
    CreatedAt     DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('policy.Evaluations', 'U') IS NULL
BEGIN
  CREATE TABLE policy.Evaluations (
    EvaluationId  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RuleId        UNIQUEIDENTIFIER NOT NULL,
    ServerId      UNIQUEIDENTIFIER NULL,
    DatabaseName  NVARCHAR(256)    NULL,
    Result        NVARCHAR(16)     NOT NULL, -- Pass, Fail, Exception, NotApplicable
    Evidence      NVARCHAR(MAX)    NULL,
    EvidenceHash  CHAR(64)         NULL,
    ExceptionOwner NVARCHAR(256)   NULL,
    ExceptionReason NVARCHAR(MAX)  NULL,
    RemediationAction NVARCHAR(MAX) NULL,
    EvaluatedAt   DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (RuleId) REFERENCES policy.Rules(RuleId)
  );
  CREATE NONCLUSTERED INDEX IX_PolicyEval_Rule ON policy.Evaluations (RuleId, EvaluatedAt DESC);
  CREATE NONCLUSTERED INDEX IX_PolicyEval_Result ON policy.Evaluations (Result, EvaluatedAt DESC);
END;
GO

-- ===== 4. Economic Risk Engine =====
IF OBJECT_ID('sec.EconomicRisk', 'U') IS NULL
BEGIN
  CREATE TABLE sec.EconomicRisk (
    RiskId               UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId             UNIQUEIDENTIFIER NOT NULL,
    DatabaseName         NVARCHAR(256)    NULL,
    RiskCategory         NVARCHAR(64)     NOT NULL,
    Description          NVARCHAR(MAX)    NOT NULL,
    Exploitability       INT NOT NULL DEFAULT 0,
    DataSensitivity      INT NOT NULL DEFAULT 0,
    BlastRadius          INT NOT NULL DEFAULT 0,
    RecoverabilityWeakness INT NOT NULL DEFAULT 0,
    BusinessCriticality  INT NOT NULL DEFAULT 0,
    CostOfDowntimePerHour DECIMAL(18,2) NULL,
    CompliancePenalty    DECIMAL(18,2) NULL,
    CustomerImpact       INT NOT NULL DEFAULT 0,
    CompositeRiskScore   INT NOT NULL DEFAULT 0,
    ExpectedLossExposure DECIMAL(18,2) NULL,
    RemediationEffortHours DECIMAL(8,2) NULL,
    RiskReducedPerHour   DECIMAL(18,2) NULL,
    Recommendation       NVARCHAR(MAX)    NULL,
    EvaluatedAt          DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_EconRisk_Score ON sec.EconomicRisk (CompositeRiskScore DESC);
END;
GO

-- ===== 5. Evidence Packs =====
IF OBJECT_ID('sec.EvidencePack', 'U') IS NULL
BEGIN
  CREATE TABLE sec.EvidencePack (
    PackId         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    PackName       NVARCHAR(256)    NOT NULL,
    FrameworkOrPurpose NVARCHAR(128) NOT NULL,
    ServerId       UNIQUEIDENTIFIER NULL,
    GeneratedBy    NVARCHAR(256)    NOT NULL DEFAULT 'system',
    ControlCount   INT              NOT NULL DEFAULT 0,
    PassCount      INT              NOT NULL DEFAULT 0,
    FailCount      INT              NOT NULL DEFAULT 0,
    ExceptionCount INT              NOT NULL DEFAULT 0,
    OverallResult  NVARCHAR(16)     NOT NULL DEFAULT 'Pending',
    PackageHash    CHAR(64)         NOT NULL,
    PayloadJson    NVARCHAR(MAX)    NOT NULL,
    GeneratedAt    DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_EvidPack_Framework ON sec.EvidencePack (FrameworkOrPurpose, GeneratedAt DESC);
END;
GO

-- ===== 6. Safe Remediation Orchestrator =====
IF OBJECT_ID('ops.RemediationPlan', 'U') IS NULL
BEGIN
  CREATE TABLE ops.RemediationPlan (
    PlanId            UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId          UNIQUEIDENTIFIER NOT NULL,
    DatabaseName      NVARCHAR(256)    NULL,
    SourceRiskId      UNIQUEIDENTIFIER NULL,
    PlanName          NVARCHAR(256)    NOT NULL,
    RemediationType   NVARCHAR(64)     NOT NULL,
    PreflightChecks   NVARCHAR(MAX)    NULL, -- JSON
    DependencyAnalysis NVARCHAR(MAX)   NULL, -- JSON
    SimulationResult  NVARCHAR(MAX)    NULL, -- JSON
    MaintenanceWindow NVARCHAR(128)    NULL,
    RollbackArtifact  NVARCHAR(MAX)    NULL,
    ApprovalChain     NVARCHAR(MAX)    NULL, -- JSON
    ExecutionScript   NVARCHAR(MAX)    NULL,
    CanaryConfig      NVARCHAR(MAX)    NULL, -- JSON
    PostChangeVerification NVARCHAR(MAX) NULL, -- JSON
    Status            NVARCHAR(32)     NOT NULL DEFAULT 'Draft',
    CreatedBy         NVARCHAR(256)    NOT NULL,
    ApprovedBy        NVARCHAR(256)    NULL,
    ExecutedAt        DATETIME2        NULL,
    VerifiedAt        DATETIME2        NULL,
    ClosedAt          DATETIME2        NULL,
    CreatedAt         DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_RemPlan_Status ON ops.RemediationPlan (Status, CreatedAt DESC);
END;
GO

-- ===== 7. Sovereignty Controls =====
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'sovereignty')
  EXEC('CREATE SCHEMA sovereignty');
GO

IF OBJECT_ID('sovereignty.TenantConfig', 'U') IS NULL
BEGIN
  CREATE TABLE sovereignty.TenantConfig (
    TenantId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    TenantName     NVARCHAR(256)    NOT NULL,
    DeploymentMode NVARCHAR(32)     NOT NULL DEFAULT 'OnPrem', -- OnPrem, Hybrid, CustomerCloud
    DataResidencyRegion NVARCHAR(64) NULL,
    EncryptionKeyOwner NVARCHAR(32) NOT NULL DEFAULT 'Customer', -- Customer, Platform
    TelemetryExportPolicy NVARCHAR(MAX) NULL, -- JSON: what may leave the environment
    RedactionRules NVARCHAR(MAX)    NULL, -- JSON: field-level redaction
    AuditRetentionDays INT          NOT NULL DEFAULT 365,
    IsActive       BIT              NOT NULL DEFAULT 1,
    CreatedAt      DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('sovereignty.DataResidencyLog', 'U') IS NULL
BEGIN
  CREATE TABLE sovereignty.DataResidencyLog (
    LogId          BIGINT IDENTITY(1,1) PRIMARY KEY,
    TenantId       UNIQUEIDENTIFIER NULL,
    Action         NVARCHAR(64)     NOT NULL,
    DataCategory   NVARCHAR(64)     NOT NULL,
    SourceRegion   NVARCHAR(64)     NULL,
    DestinationRegion NVARCHAR(64)  NULL,
    Justification  NVARCHAR(MAX)    NULL,
    Allowed        BIT              NOT NULL DEFAULT 1,
    LoggedAt       DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== Seed: Core Policy Rules =====
IF NOT EXISTS (SELECT 1 FROM policy.Rules WHERE RuleCode = 'POL-RESTORE-FRESHNESS')
BEGIN
  INSERT INTO policy.Rules (RuleName, RuleCode, Category, Severity, Description, PolicyLogic, EnforcementMode) VALUES
    ('Restore Test Freshness', 'POL-RESTORE-FRESHNESS', 'Recoverability', 'Critical',
     'No PROD database with regulated data may lack tested restore evidence newer than 14 days.',
     '{"target":"databases","condition":"lastRestoreTest.ageDays > 14","scope":"production","dataClass":["PII","PHI","PCI"]}', 'Alert'),
    ('Elevated Login Ownership', 'POL-LOGIN-OWNER', 'Identity', 'High',
     'No login with elevated rights may exist without mapped business owner, ticket, and expiry.',
     '{"target":"credentials","condition":"privilegeLevel IN (sysadmin,db_owner) AND (ownerMissing OR expiryMissing)"}', 'Alert'),
    ('Backup Residency Compliance', 'POL-BACKUP-RESIDENCY', 'Data Residency', 'Critical',
     'No backup copy containing classified data may leave approved residency boundaries.',
     '{"target":"backups","condition":"dataClass != null AND residencyRegion != approvedRegion"}', 'Block'),
    ('Patch Lag Limit', 'POL-PATCH-LAG', 'Patching', 'High',
     'No engine may exceed approved patch lag without exception record.',
     '{"target":"patchStatus","condition":"cuBehindCount > 2 AND exceptionRecord == null"}', 'Alert'),
    ('HA Mode for Critical DBs', 'POL-HA-CRITICAL', 'Resilience', 'Critical',
     'No database marked critical may run without HA mode meeting declared SLA.',
     '{"target":"databases","condition":"criticality == critical AND haMode == null"}', 'Alert'),
    ('TDE Required for Sensitive Data', 'POL-TDE-REQUIRED', 'Encryption', 'Critical',
     'All databases containing PII, PHI, or PCI data must have TDE enabled.',
     '{"target":"databases","condition":"sensitiveData == true AND tdeEnabled == false"}', 'Alert'),
    ('Orphaned Account Cleanup', 'POL-ORPHAN-CLEANUP', 'Identity', 'High',
     'Orphaned accounts must be removed within 7 days of detection.',
     '{"target":"credentials","condition":"isOrphaned == true AND detectedDaysAgo > 7"}', 'AutoRemediate'),
    ('Certificate Expiry Warning', 'POL-CERT-EXPIRY', 'Encryption', 'High',
     'All certificates must be renewed at least 30 days before expiry.',
     '{"target":"certificates","condition":"daysUntilExpiry <= 30 AND daysUntilExpiry > 0"}', 'Alert'),
    ('DR Drill Cadence', 'POL-DR-DRILL', 'Resilience', 'High',
     'DR drills must be executed at least quarterly for all Tier 0 and Tier 1 systems.',
     '{"target":"drPlans","condition":"securityTier <= 1 AND lastDrillDays > 90"}', 'Alert'),
    ('Change Approval Required', 'POL-CHANGE-APPROVAL', 'Governance', 'Critical',
     'No production change may execute without documented approval from authorized reviewer.',
     '{"target":"changes","condition":"environment == production AND approvedBy == null AND status == Executed"}', 'Block');
END;
GO

PRINT 'Schema v7 (Assurance Graph & Action Engine) applied successfully.';
GO
