-- DBAOps Sovereign™ — v14 Repair: perf.Snapshots when base schema.sql was skipped
-- Migrate only runs schema.sql on empty repos; databases that gained ctl.Servers via
-- additive scripts may never have received perf telemetry tables. Idempotent additive repair.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'perf')
    EXEC('CREATE SCHEMA perf');
GO

IF OBJECT_ID(N'perf.Snapshots', N'U') IS NULL
BEGIN
    CREATE TABLE perf.Snapshots (
        SnapshotId          UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        ServerId            UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        CollectedAt         DATETIME2 NOT NULL,
        CpuPercent          DECIMAL(5,2) NULL,
        SqlMemoryUsedMB     INT NULL,
        BlockingSessions    INT NOT NULL DEFAULT 0,
        ActiveSessions      INT NULL,
        BatchRequestsPerSec DECIMAL(18,2) NULL,
        AvgSignalWaitMs     DECIMAL(18,4) NULL,
        RawMetricsJson      NVARCHAR(MAX) NULL,
        CONSTRAINT FK_Snapshots_Server FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
    );
    CREATE INDEX IX_Snapshots_Server_Collected ON perf.Snapshots (ServerId, CollectedAt DESC);
END
GO
