/*
 * DBAOps Sovereign™ — Schema v13 Strategic Platform
 * Supports phased roadmap: graph events, onboarding, self-healing learning,
 * legal evidence custody, time-to-value benchmark, migration import audit.
 */

USE SovereignRepository;
GO

-- ===== Phase 1 — Event-driven graph revision trail (collectors append) =====
IF OBJECT_ID('graph.GraphChangeEvent', 'U') IS NULL
BEGIN
  CREATE TABLE graph.GraphChangeEvent (
    EventId       BIGINT IDENTITY(1,1) PRIMARY KEY,
    EventType     NVARCHAR(64) NOT NULL,
    SourceSystem  NVARCHAR(64) NOT NULL DEFAULT 'Collector',
    ServerId      UNIQUEIDENTIFIER NULL,
    LoginName     NVARCHAR(256) NULL,
    DatabaseName  NVARCHAR(256) NULL,
    PayloadJson   NVARCHAR(MAX) NULL,
    GraphRevision BIGINT NOT NULL DEFAULT 1,
    RecordedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_GraphChange_Server ON graph.GraphChangeEvent (ServerId, RecordedAt DESC);
  CREATE NONCLUSTERED INDEX IX_GraphChange_Type ON graph.GraphChangeEvent (EventType, RecordedAt DESC);
END;
GO

-- ===== Phase 2 — Compliant on Contact =====
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'governance')
  EXEC('CREATE SCHEMA governance');
GO

IF OBJECT_ID('governance.ComplianceProfile', 'U') IS NULL
BEGIN
  CREATE TABLE governance.ComplianceProfile (
    ProfileId       INT IDENTITY(1,1) PRIMARY KEY,
    ProfileCode     NVARCHAR(64) NOT NULL UNIQUE,
    TierName        NVARCHAR(128) NOT NULL,
    SecurityTier    INT NOT NULL DEFAULT 1,
    Description     NVARCHAR(MAX) NULL,
    SpConfigureJson NVARCHAR(MAX) NULL,
    RequireTDE      BIT NOT NULL DEFAULT 0,
    RequireSaDisabled BIT NOT NULL DEFAULT 1,
    RequireForceEncryption BIT NOT NULL DEFAULT 1,
    AuditGroupsJson NVARCHAR(MAX) NULL,
    GmsaPattern     NVARCHAR(256) NULL,
    FirewallRulesJson NVARCHAR(MAX) NULL,
    IsActive        BIT NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('governance.OnboardingPipeline', 'U') IS NULL
BEGIN
  CREATE TABLE governance.OnboardingPipeline (
    PipelineId      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    ProfileId       INT NULL,
    CurrentStage    NVARCHAR(64) NOT NULL DEFAULT 'Registered',
    StageRegisteredAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    StageProfileAssignedAt DATETIME2 NULL,
    StagePreflightAt DATETIME2 NULL,
    StageBaselineAt DATETIME2 NULL,
    StagePostflightAt DATETIME2 NULL,
    StageHealthAt DATETIME2 NULL,
    StageGraphAt DATETIME2 NULL,
    StageCertifiedAt DATETIME2 NULL,
    LastError       NVARCHAR(MAX) NULL,
    HealthScore     INT NULL,
    ContainmentCertId UNIQUEIDENTIFIER NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'InProgress',
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers (ServerId),
    FOREIGN KEY (ProfileId) REFERENCES governance.ComplianceProfile (ProfileId)
  );
  CREATE NONCLUSTERED INDEX IX_Onboarding_Server ON governance.OnboardingPipeline (ServerId, Status);
END;
GO

IF NOT EXISTS (SELECT 1 FROM governance.ComplianceProfile WHERE ProfileCode = 'TIER1')
BEGIN
  INSERT INTO governance.ComplianceProfile (ProfileCode, TierName, SecurityTier, Description, RequireTDE, RequireSaDisabled, RequireForceEncryption, SpConfigureJson) VALUES
    ('TIER0', 'Tier 0 — Critical', 0, 'Maximum baseline: TDE, no SA, force encryption, full audit.', 1, 1, 1, '{"max degree of parallelism":1}'),
    ('TIER1', 'Tier 1 — Business critical', 1, 'Production OLTP baseline.', 1, 1, 1, NULL),
    ('TIER2', 'Tier 2 — Support', 2, 'Relaxed encryption where no regulated data.', 0, 1, 1, NULL);
END;
GO

-- ===== Phase 3 — Adaptive self-healing learning =====
IF OBJECT_ID('ops.ManualInterventionLog', 'U') IS NULL
BEGIN
  CREATE TABLE ops.ManualInterventionLog (
    InterventionId BIGINT IDENTITY(1,1) PRIMARY KEY,
    ServerId       UNIQUEIDENTIFIER NULL,
    AlertType      NVARCHAR(128) NOT NULL,
    MetricKey      NVARCHAR(128) NULL,
    Summary        NVARCHAR(512) NOT NULL,
    ActionTaken    NVARCHAR(MAX) NOT NULL,
    PerformedBy    NVARCHAR(256) NOT NULL,
    PerformedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    DetectedByCollector BIT NOT NULL DEFAULT 0,
    RelatedActionCode NVARCHAR(64) NULL
  );
  CREATE NONCLUSTERED INDEX IX_ManualIntervention_Alert ON ops.ManualInterventionLog (AlertType, PerformedAt DESC);
END;
GO

IF OBJECT_ID('ops.SelfHealingTrustState', 'U') IS NULL
BEGIN
  CREATE TABLE ops.SelfHealingTrustState (
    TrustStateId   INT IDENTITY(1,1) PRIMARY KEY,
    ActionCode     NVARCHAR(64) NOT NULL UNIQUE,
    TrustLevel     TINYINT NOT NULL DEFAULT 0,
    TrustScore     INT NOT NULL DEFAULT 0,
    SuccessStreak  INT NOT NULL DEFAULT 0,
    FailureStreak  INT NOT NULL DEFAULT 0,
    LastPromotedAt DATETIME2 NULL,
    LastDemotedAt  DATETIME2 NULL,
    UpdatedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('ops.SelfHealingTrustHistory', 'U') IS NULL
BEGIN
  CREATE TABLE ops.SelfHealingTrustHistory (
    HistoryId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    ActionCode    NVARCHAR(64) NOT NULL,
    FromLevel     TINYINT NOT NULL,
    ToLevel       TINYINT NOT NULL,
    Reason        NVARCHAR(256) NOT NULL,
    ChangedBy     NVARCHAR(256) NULL,
    ChangedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== Phase 4 — Legal-grade evidence =====
IF OBJECT_ID('sec.EvidenceChainOfCustody', 'U') IS NULL
BEGIN
  CREATE TABLE sec.EvidenceChainOfCustody (
    CustodyId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    EvidenceRef   NVARCHAR(256) NOT NULL,
    EvidenceType  NVARCHAR(64) NOT NULL,
    Action        NVARCHAR(32) NOT NULL,
    ActorEmail    NVARCHAR(256) NOT NULL,
    ActorIp       NVARCHAR(64) NULL,
    Purpose       NVARCHAR(512) NULL,
    PrevHash      CHAR(64) NULL,
    EntryHash     CHAR(64) NOT NULL,
    AccessedAt    DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_Custody_Evidence ON sec.EvidenceChainOfCustody (EvidenceRef, AccessedAt DESC);
END;
GO

IF OBJECT_ID('sec.ComplianceAttestation', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ComplianceAttestation (
    AttestationId   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NULL,
    FrameworkCode   NVARCHAR(64) NOT NULL,
    FrameworkVersion NVARCHAR(32) NULL,
    ControlCount    INT NOT NULL,
    StatementText   NVARCHAR(MAX) NOT NULL,
    MerkleRootHash  CHAR(64) NULL,
    Rfc3161Token    NVARCHAR(MAX) NULL,
    SignedBy        NVARCHAR(256) NULL,
    SignedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidUntil      DATETIME2 NULL,
    VerificationInstructions NVARCHAR(MAX) NULL
  );
  CREATE NONCLUSTERED INDEX IX_Attestation_Framework ON sec.ComplianceAttestation (FrameworkCode, SignedAt DESC);
END;
GO

-- ===== Phase 6 — Time-to-value + migration import =====
IF OBJECT_ID('ops.TimeToValueRun', 'U') IS NULL
BEGIN
  CREATE TABLE ops.TimeToValueRun (
    RunId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    TenantId        UNIQUEIDENTIFIER NULL,
    StartedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt     DATETIME2 NULL,
    FleetSize       INT NOT NULL DEFAULT 0,
    DiscoveryDoneAt DATETIME2 NULL,
    FirstHealthAt   DATETIME2 NULL,
    FirstComplianceAt DATETIME2 NULL,
    FirstEvidenceAt DATETIME2 NULL,
    GraphReadyAt    DATETIME2 NULL,
    ElapsedSeconds  INT NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Running'
  );
END;
GO

IF OBJECT_ID('ops.MigrationImportJob', 'U') IS NULL
BEGIN
  CREATE TABLE ops.MigrationImportJob (
    JobId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    SourceVendor    NVARCHAR(64) NOT NULL,
    ImportFormat    NVARCHAR(32) NOT NULL,
    FileName        NVARCHAR(512) NULL,
    ItemsImported   INT NOT NULL DEFAULT 0,
    WarningsJson    NVARCHAR(MAX) NULL,
    ImportedBy      NVARCHAR(256) NOT NULL,
    ImportedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Completed'
  );
END;
GO

PRINT 'Schema v13 (Strategic platform) applied successfully.';
GO
