/*
 * DBAOps Sovereign™ — Schema v6: Platinum Magic Features
 * Additive migration — all CREATE guarded by IF NOT EXISTS.
 * Requires: SovereignRepository database from v1–v5.
 *
 * Features:
 *   1.  Cryptographic Audit Chain (sec.AuditChain)
 *   2.  Backup Restore Verification (backup.RestoreVerification)
 *   3.  Sensitive Data Classification (sec.DataClassification)
 *   4.  Credential Lifecycle / Privilege Hygiene (sec.CredentialInventory)
 *   5.  Schema Drift Detection (sec.SchemaFingerprint, sec.SchemaDependency, sec.DriftEvent)
 *   6.  Zero-Trust JIT Privileged Access (sec.AccessRequests, sec.BreakGlassLog)
 *   7.  Continuous Compliance Evidence (sec.ComplianceControls, sec.ComplianceEvidence, sec.ComplianceGap)
 *   8.  Disaster Recovery Validation (hadr.DRPlan, hadr.DRDrillHistory, hadr.BusinessImpact)
 *   9.  Database Cost Intelligence (cap.LicenseInventory, cap.CostAllocation, cap.RightSizeRecommendation)
 *  10.  Encryption / Certificate Lifecycle (sec.EncryptionInventory, sec.CertificateTracking)
 *  11.  SLA / Availability Scorecards (ops.SLATargets, ops.AvailabilityLog, ops.DowntimeEvents)
 *  12.  Change Governance Workflow (ops.ChangeRequests, ops.ChangeCalendar)
 */

USE SovereignRepository;
GO

-- ===== 1. Cryptographic Audit Chain =====
IF OBJECT_ID('sec.AuditChain', 'U') IS NULL
BEGIN
  CREATE TABLE sec.AuditChain (
    ChainId       BIGINT IDENTITY(1,1) PRIMARY KEY,
    PreviousHash  CHAR(64)       NOT NULL DEFAULT REPLICATE('0', 64),
    EntryHash     CHAR(64)       NOT NULL,
    EntryType     NVARCHAR(64)   NOT NULL,
    Payload       NVARCHAR(MAX)  NOT NULL,
    Signature     NVARCHAR(MAX)  NULL,
    ServerName    NVARCHAR(256)  NULL,
    Actor         NVARCHAR(256)  NULL,
    CreatedAt     DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_AuditChain_Type ON sec.AuditChain (EntryType, CreatedAt DESC);
  CREATE NONCLUSTERED INDEX IX_AuditChain_Actor ON sec.AuditChain (Actor, CreatedAt DESC);
END;
GO

-- ===== 2. Backup Restore Verification =====
IF OBJECT_ID('backup.RestoreVerification', 'U') IS NULL
BEGIN
  CREATE TABLE [backup].RestoreVerification (
    VerificationId    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId          UNIQUEIDENTIFIER NOT NULL,
    DatabaseName      NVARCHAR(256)    NOT NULL,
    BackupSetId       NVARCHAR(128)    NULL,
    RestoreStarted    DATETIME2        NULL,
    RestoreCompleted  DATETIME2        NULL,
    CheckDBResult     NVARCHAR(32)     NULL,
    RowCountMatch     BIT              NULL,
    ActualRTO_Seconds INT              NULL,
    ActualRPO_Minutes INT              NULL,
    CertificateHash   CHAR(64)         NULL,
    Status            NVARCHAR(32)     NOT NULL DEFAULT 'Pending',
    CreatedAt         DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_RestoreVerif_Server ON [backup].RestoreVerification (ServerId, CreatedAt DESC);
END;
GO

-- ===== 3. Sensitive Data Classification =====
IF OBJECT_ID('sec.DataClassification', 'U') IS NULL
BEGIN
  CREATE TABLE sec.DataClassification (
    ClassificationId   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    DatabaseName       NVARCHAR(256)    NOT NULL,
    SchemaName         NVARCHAR(128)    NOT NULL DEFAULT 'dbo',
    TableName          NVARCHAR(256)    NOT NULL,
    ColumnName         NVARCHAR(256)    NOT NULL,
    DataType           NVARCHAR(64)     NULL,
    ClassificationType NVARCHAR(32)     NOT NULL,
    SensitivityLevel   NVARCHAR(32)     NOT NULL DEFAULT 'Medium',
    RegulatoryFrameworks NVARCHAR(512)  NULL,
    DetectionMethod    NVARCHAR(32)     NOT NULL DEFAULT 'Pattern',
    ConfidenceScore    DECIMAL(5,2)     NOT NULL DEFAULT 0.0,
    MaskingRecommendation NVARCHAR(256) NULL,
    FirstDetected      DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    LastScanned        DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_DataClass_Server ON sec.DataClassification (ServerId, DatabaseName);
  CREATE NONCLUSTERED INDEX IX_DataClass_Type ON sec.DataClassification (ClassificationType, SensitivityLevel);
END;
GO

-- ===== 4. Credential Lifecycle =====
IF OBJECT_ID('sec.CredentialInventory', 'U') IS NULL
BEGIN
  CREATE TABLE sec.CredentialInventory (
    CredentialId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    LoginName          NVARCHAR(256)    NOT NULL,
    LoginType          NVARCHAR(64)     NOT NULL,
    IsDisabled         BIT              NOT NULL DEFAULT 0,
    CreateDate         DATETIME2        NULL,
    LastLoginDate      DATETIME2        NULL,
    PasswordLastSet    DATETIME2        NULL,
    PasswordExpiry     DATETIME2        NULL,
    IsOrphaned         BIT              NOT NULL DEFAULT 0,
    IsStale            BIT              NOT NULL DEFAULT 0,
    DaysSinceLastLogin INT              NULL,
    Permissions        NVARCHAR(MAX)    NULL,
    PrivilegeLevel     NVARCHAR(32)     NOT NULL DEFAULT 'Standard',
    RiskScore          INT              NOT NULL DEFAULT 0,
    RemediationAction  NVARCHAR(512)    NULL,
    CollectedAt        DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_CredInv_Server ON sec.CredentialInventory (ServerId);
  CREATE NONCLUSTERED INDEX IX_CredInv_Risk ON sec.CredentialInventory (RiskScore DESC);
END;
GO

-- ===== 5. Schema Drift Detection =====
IF OBJECT_ID('sec.SchemaFingerprint', 'U') IS NULL
BEGIN
  CREATE TABLE sec.SchemaFingerprint (
    FingerprintId  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId       UNIQUEIDENTIFIER NOT NULL,
    DatabaseName   NVARCHAR(256)    NOT NULL,
    ObjectName     NVARCHAR(512)    NOT NULL,
    ObjectType     NVARCHAR(32)     NOT NULL,
    DefinitionHash CHAR(64)         NOT NULL,
    CapturedAt     DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_SchemaFP_Server ON sec.SchemaFingerprint (ServerId, DatabaseName, ObjectName);
END;
GO

IF OBJECT_ID('sec.SchemaDependency', 'U') IS NULL
BEGIN
  CREATE TABLE sec.SchemaDependency (
    DependencyId    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    DatabaseName    NVARCHAR(256)    NOT NULL,
    ObjectName      NVARCHAR(512)    NOT NULL,
    DependentObject NVARCHAR(512)    NOT NULL,
    DependencyType  NVARCHAR(64)     NOT NULL,
    CapturedAt      DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('sec.DriftEvent', 'U') IS NULL
BEGIN
  CREATE TABLE sec.DriftEvent (
    EventId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId       UNIQUEIDENTIFIER NOT NULL,
    DatabaseName   NVARCHAR(256)    NOT NULL,
    ObjectName     NVARCHAR(512)    NOT NULL,
    ChangeType     NVARCHAR(32)     NOT NULL,
    OldHash        CHAR(64)         NULL,
    NewHash        CHAR(64)         NULL,
    DetectedAt     DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    ChangedBy      NVARCHAR(256)    NULL,
    ApprovalStatus NVARCHAR(32)     NOT NULL DEFAULT 'Pending',
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_DriftEvent_Server ON sec.DriftEvent (ServerId, DetectedAt DESC);
END;
GO

-- ===== 6. Zero-Trust JIT Access =====
IF OBJECT_ID('sec.AccessRequests', 'U') IS NULL
BEGIN
  CREATE TABLE sec.AccessRequests (
    RequestId     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RequesterId   NVARCHAR(256)    NOT NULL,
    ServerId      UNIQUEIDENTIFIER NOT NULL,
    DatabaseName  NVARCHAR(256)    NULL,
    RequestedRole NVARCHAR(64)     NOT NULL,
    Justification NVARCHAR(MAX)    NOT NULL,
    ApprovedBy    NVARCHAR(256)    NULL,
    GrantedAt     DATETIME2        NULL,
    ExpiresAt     DATETIME2        NULL,
    RevokedAt     DATETIME2        NULL,
    Status        NVARCHAR(32)     NOT NULL DEFAULT 'Pending',
    SessionLog    NVARCHAR(MAX)    NULL,
    CreatedAt     DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_AccessReq_Status ON sec.AccessRequests (Status, CreatedAt DESC);
END;
GO

IF OBJECT_ID('sec.BreakGlassLog', 'U') IS NULL
BEGIN
  CREATE TABLE sec.BreakGlassLog (
    LogId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RequesterId  NVARCHAR(256)    NOT NULL,
    ServerId     UNIQUEIDENTIFIER NOT NULL,
    Reason       NVARCHAR(MAX)    NOT NULL,
    GrantedAt    DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    ReviewedBy   NVARCHAR(256)    NULL,
    ReviewedAt   DATETIME2        NULL,
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

-- ===== 7. Continuous Compliance Evidence =====
IF OBJECT_ID('sec.ComplianceControls', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ComplianceControls (
    ControlId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    FrameworkId      NVARCHAR(64)     NOT NULL,
    ControlCode      NVARCHAR(32)     NOT NULL,
    Description      NVARCHAR(MAX)    NOT NULL,
    Category         NVARCHAR(128)    NOT NULL,
    AutomationStatus NVARCHAR(32)     NOT NULL DEFAULT 'Manual'
  );
  CREATE UNIQUE INDEX UX_CompCtrl_Code ON sec.ComplianceControls (FrameworkId, ControlCode);
END;
GO

IF OBJECT_ID('sec.ComplianceEvidence', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ComplianceEvidence (
    EvidenceId   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ControlId    UNIQUEIDENTIFIER NOT NULL,
    ServerId     UNIQUEIDENTIFIER NULL,
    EvidenceType NVARCHAR(64)     NOT NULL,
    Payload      NVARCHAR(MAX)    NOT NULL,
    CollectedAt  DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    Hash         CHAR(64)         NOT NULL,
    Status       NVARCHAR(32)     NOT NULL DEFAULT 'Pass',
    FOREIGN KEY (ControlId) REFERENCES sec.ComplianceControls(ControlId)
  );
  CREATE NONCLUSTERED INDEX IX_CompEvidence_Ctrl ON sec.ComplianceEvidence (ControlId, CollectedAt DESC);
END;
GO

IF OBJECT_ID('sec.ComplianceGap', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ComplianceGap (
    GapId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ControlId       UNIQUEIDENTIFIER NOT NULL,
    ServerId        UNIQUEIDENTIFIER NULL,
    Finding         NVARCHAR(MAX)    NOT NULL,
    Severity        NVARCHAR(32)     NOT NULL DEFAULT 'Medium',
    RemediationPlan NVARCHAR(MAX)    NULL,
    DueDate         DATE             NULL,
    ResolvedAt      DATETIME2        NULL,
    CreatedAt       DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ControlId) REFERENCES sec.ComplianceControls(ControlId)
  );
END;
GO

-- ===== 8. Disaster Recovery Validation =====
IF OBJECT_ID('hadr.DRPlan', 'U') IS NULL
BEGIN
  CREATE TABLE hadr.DRPlan (
    PlanId            UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId          UNIQUEIDENTIFIER NOT NULL,
    DatabaseName      NVARCHAR(256)    NULL,
    TargetRTO_Minutes INT              NOT NULL DEFAULT 60,
    TargetRPO_Minutes INT              NOT NULL DEFAULT 15,
    DRStrategy        NVARCHAR(64)     NOT NULL DEFAULT 'AlwaysOn',
    SecondaryServer   NVARCHAR(256)    NULL,
    LastDrillDate     DATETIME2        NULL,
    NextDrillDue      DATETIME2        NULL,
    ReadinessScore    INT              NOT NULL DEFAULT 0,
    CreatedAt         DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('hadr.DRDrillHistory', 'U') IS NULL
BEGIN
  CREATE TABLE hadr.DRDrillHistory (
    DrillId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    PlanId            UNIQUEIDENTIFIER NOT NULL,
    DrillType         NVARCHAR(32)     NOT NULL DEFAULT 'Failover',
    StartedAt         DATETIME2        NOT NULL,
    CompletedAt       DATETIME2        NULL,
    ActualRTO_Seconds INT              NULL,
    ActualRPO_Minutes INT              NULL,
    Result            NVARCHAR(32)     NOT NULL DEFAULT 'Pending',
    Findings          NVARCHAR(MAX)    NULL,
    FOREIGN KEY (PlanId) REFERENCES hadr.DRPlan(PlanId)
  );
END;
GO

IF OBJECT_ID('hadr.BusinessImpact', 'U') IS NULL
BEGIN
  CREATE TABLE hadr.BusinessImpact (
    ImpactId            UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId            UNIQUEIDENTIFIER NOT NULL,
    BusinessProcess     NVARCHAR(256)    NOT NULL,
    CriticalityTier     INT              NOT NULL DEFAULT 2,
    EstimatedCostPerHour DECIMAL(18,2)   NULL,
    SLATarget           NVARCHAR(32)     NULL,
    CreatedAt           DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

-- ===== 9. Database Cost Intelligence =====
IF OBJECT_ID('cap.LicenseInventory', 'U') IS NULL
BEGIN
  CREATE TABLE cap.LicenseInventory (
    InventoryId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    ProductName        NVARCHAR(256)    NOT NULL,
    Edition            NVARCHAR(64)     NOT NULL,
    LicenseType        NVARCHAR(64)     NOT NULL DEFAULT 'Per-Core',
    CoreCount          INT              NOT NULL DEFAULT 0,
    ActivatedFeatures  NVARCHAR(MAX)    NULL,
    EstimatedAnnualCost DECIMAL(18,2)   NULL,
    CollectedAt        DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('cap.CostAllocation', 'U') IS NULL
BEGIN
  CREATE TABLE cap.CostAllocation (
    AllocationId  UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId      UNIQUEIDENTIFIER NOT NULL,
    BusinessUnit  NVARCHAR(128)    NOT NULL,
    Application   NVARCHAR(256)    NULL,
    Environment   NVARCHAR(64)     NULL,
    MonthlyCost   DECIMAL(18,2)    NOT NULL DEFAULT 0,
    CostDriver    NVARCHAR(128)    NULL,
    CreatedAt     DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('cap.RightSizeRecommendation', 'U') IS NULL
BEGIN
  CREATE TABLE cap.RightSizeRecommendation (
    RecommendationId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId         UNIQUEIDENTIFIER NOT NULL,
    CurrentConfig    NVARCHAR(MAX)    NOT NULL,
    RecommendedConfig NVARCHAR(MAX)   NOT NULL,
    EstimatedSavings DECIMAL(18,2)    NOT NULL DEFAULT 0,
    Confidence       DECIMAL(5,2)     NOT NULL DEFAULT 0,
    CreatedAt        DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

-- ===== 10. Encryption / Certificate Lifecycle =====
IF OBJECT_ID('sec.EncryptionInventory', 'U') IS NULL
BEGIN
  CREATE TABLE sec.EncryptionInventory (
    InventoryId      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId         UNIQUEIDENTIFIER NOT NULL,
    EncryptionType   NVARCHAR(32)     NOT NULL,
    DatabaseName     NVARCHAR(256)    NULL,
    KeyName          NVARCHAR(256)    NOT NULL,
    Algorithm        NVARCHAR(64)     NULL,
    KeyLength        INT              NULL,
    CreatedDate      DATETIME2        NULL,
    LastRotated      DATETIME2        NULL,
    ExpiryDate       DATETIME2        NULL,
    ComplianceStatus NVARCHAR(32)     NOT NULL DEFAULT 'Unknown',
    CollectedAt      DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_EncInv_Server ON sec.EncryptionInventory (ServerId);
END;
GO

IF OBJECT_ID('sec.CertificateTracking', 'U') IS NULL
BEGIN
  CREATE TABLE sec.CertificateTracking (
    CertificateId      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    CertificateName    NVARCHAR(256)    NOT NULL,
    Subject            NVARCHAR(512)    NULL,
    Issuer             NVARCHAR(512)    NULL,
    ValidFrom          DATETIME2        NULL,
    ValidTo            DATETIME2        NULL,
    KeyLength          INT              NULL,
    SignatureAlgorithm NVARCHAR(64)     NULL,
    IsExpired          BIT              NOT NULL DEFAULT 0,
    DaysUntilExpiry    INT              NULL,
    CollectedAt        DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_CertTrack_Expiry ON sec.CertificateTracking (IsExpired, DaysUntilExpiry);
END;
GO

-- ===== 11. SLA / Availability Scorecards =====
IF OBJECT_ID('ops.SLATargets', 'U') IS NULL
BEGIN
  CREATE TABLE ops.SLATargets (
    TargetId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    ApplicationName    NVARCHAR(256)    NULL,
    AvailabilityTarget DECIMAL(6,3)     NOT NULL DEFAULT 99.900,
    RPOTarget          INT              NULL,
    RTOTarget          INT              NULL,
    ContractReference  NVARCHAR(256)    NULL,
    EffectiveFrom      DATE             NOT NULL DEFAULT GETDATE(),
    CreatedAt          DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

IF OBJECT_ID('ops.AvailabilityLog', 'U') IS NULL
BEGIN
  CREATE TABLE ops.AvailabilityLog (
    LogId           BIGINT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    CheckTime       DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    IsAvailable     BIT              NOT NULL DEFAULT 1,
    ResponseTime_ms INT              NULL,
    DowntimeEventId UNIQUEIDENTIFIER NULL,
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_AvailLog_Server ON ops.AvailabilityLog (ServerId, CheckTime DESC);
END;
GO

IF OBJECT_ID('ops.DowntimeEvents', 'U') IS NULL
BEGIN
  CREATE TABLE ops.DowntimeEvents (
    EventId            UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    StartedAt          DATETIME2        NOT NULL,
    ResolvedAt         DATETIME2        NULL,
    Duration_Minutes   INT              NULL,
    RootCause          NVARCHAR(MAX)    NULL,
    BusinessImpactCost DECIMAL(18,2)    NULL,
    Category           NVARCHAR(64)     NULL,
    CreatedAt          DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
END;
GO

-- ===== 12. Change Governance Workflow =====
IF OBJECT_ID('ops.ChangeRequests', 'U') IS NULL
BEGIN
  CREATE TABLE ops.ChangeRequests (
    RequestId          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId           UNIQUEIDENTIFIER NOT NULL,
    DatabaseName       NVARCHAR(256)    NULL,
    ChangeType         NVARCHAR(64)     NOT NULL,
    Description        NVARCHAR(MAX)    NOT NULL,
    SQLScript          NVARCHAR(MAX)    NULL,
    RiskScore          INT              NOT NULL DEFAULT 0,
    RequestedBy        NVARCHAR(256)    NOT NULL,
    ReviewedBy         NVARCHAR(256)    NULL,
    ApprovedBy         NVARCHAR(256)    NULL,
    ExecutedAt         DATETIME2        NULL,
    RollbackScript     NVARCHAR(MAX)    NULL,
    PreChangeSnapshot  NVARCHAR(MAX)    NULL,
    PostChangeSnapshot NVARCHAR(MAX)    NULL,
    Status             NVARCHAR(32)     NOT NULL DEFAULT 'Draft',
    CreatedAt          DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_ChangeReq_Status ON ops.ChangeRequests (Status, CreatedAt DESC);
END;
GO

IF OBJECT_ID('ops.ChangeCalendar', 'U') IS NULL
BEGIN
  CREATE TABLE ops.ChangeCalendar (
    EntryId           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RequestId         UNIQUEIDENTIFIER NOT NULL,
    ScheduledDate     DATETIME2        NOT NULL,
    MaintenanceWindow NVARCHAR(128)    NULL,
    ConflictsWith     NVARCHAR(MAX)    NULL,
    CreatedAt         DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    FOREIGN KEY (RequestId) REFERENCES ops.ChangeRequests(RequestId)
  );
END;
GO

-- ===== Seed: Compliance Framework Controls =====
IF NOT EXISTS (SELECT 1 FROM sec.ComplianceControls WHERE FrameworkId = 'SOX')
BEGIN
  INSERT INTO sec.ComplianceControls (FrameworkId, ControlCode, Description, Category, AutomationStatus) VALUES
    ('SOX', 'SOX-404.1', 'Access controls over financial data', 'Access Control', 'Automated'),
    ('SOX', 'SOX-404.2', 'Change management for financial systems', 'Change Management', 'Automated'),
    ('SOX', 'SOX-404.3', 'Backup and recovery of financial data', 'Business Continuity', 'Automated'),
    ('SOX', 'SOX-404.4', 'Audit trail for financial transactions', 'Audit', 'Automated'),
    ('HIPAA', 'HIPAA-164.312a', 'Access control — unique user identification', 'Access Control', 'Automated'),
    ('HIPAA', 'HIPAA-164.312b', 'Audit controls — hardware/software/procedural', 'Audit', 'Automated'),
    ('HIPAA', 'HIPAA-164.312c', 'Integrity controls — protect ePHI', 'Data Integrity', 'Automated'),
    ('HIPAA', 'HIPAA-164.312e', 'Transmission security — encryption', 'Encryption', 'Automated'),
    ('PCI-DSS', 'PCI-3.4', 'Render PAN unreadable anywhere stored', 'Data Protection', 'Automated'),
    ('PCI-DSS', 'PCI-6.5', 'Address common coding vulnerabilities', 'Application Security', 'Manual'),
    ('PCI-DSS', 'PCI-8.1', 'Unique ID for each person with access', 'Access Control', 'Automated'),
    ('PCI-DSS', 'PCI-10.1', 'Audit trails linking access to individual', 'Audit', 'Automated'),
    ('GDPR', 'GDPR-25', 'Data protection by design and default', 'Privacy', 'Automated'),
    ('GDPR', 'GDPR-32', 'Security of processing — encryption, pseudonymisation', 'Encryption', 'Automated'),
    ('GDPR', 'GDPR-33', 'Notification of breach to supervisory authority', 'Incident Response', 'Manual'),
    ('NIST', 'NIST-AC-2', 'Account management', 'Access Control', 'Automated'),
    ('NIST', 'NIST-AU-2', 'Audit events', 'Audit', 'Automated'),
    ('NIST', 'NIST-CP-9', 'System backup', 'Business Continuity', 'Automated'),
    ('NIST', 'NIST-SC-28', 'Protection of information at rest', 'Data Protection', 'Automated'),
    ('FedRAMP', 'FedRAMP-AC-2', 'Account management', 'Access Control', 'Automated'),
    ('FedRAMP', 'FedRAMP-AU-2', 'Audit events', 'Audit', 'Automated'),
    ('FedRAMP', 'FedRAMP-SC-13', 'Cryptographic protection', 'Encryption', 'Automated'),
    ('DORA', 'DORA-9', 'ICT-related incident management', 'Incident Response', 'Manual'),
    ('DORA', 'DORA-11', 'Digital operational resilience testing', 'Business Continuity', 'Automated');
END;
GO

PRINT 'Schema v6 (Platinum Magic) applied successfully.';
GO
