-- v23 — Key Vault inventory + security hardening tables

IF OBJECT_ID('sec.KeyVaultInventory', 'U') IS NULL
BEGIN
    CREATE TABLE sec.KeyVaultInventory (
        InventoryId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
        ServerId            UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
        DatabaseName        NVARCHAR(256)    NULL,
        VaultName           NVARCHAR(256)    NOT NULL,
        KeyName             NVARCHAR(512)    NULL,
        KeyVersion          NVARCHAR(128)    NULL,
        KeyType             NVARCHAR(64)     NULL,
        EncryptorType       NVARCHAR(128)    NULL,
        ExpiryDate          DATETIME2        NULL,
        DaysUntilExpiry     INT              NULL,
        IsCompliant         BIT              NOT NULL DEFAULT 0,
        LastSeenAt          DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
        MetadataJson        NVARCHAR(MAX)    NULL
    );
    CREATE NONCLUSTERED INDEX IX_KeyVault_Server ON sec.KeyVaultInventory (ServerId);
END
GO

IF OBJECT_ID('sec.KeyVaultSecretSnapshot', 'U') IS NULL
BEGIN
    CREATE TABLE sec.KeyVaultSecretSnapshot (
        SnapshotId          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
        VaultName           NVARCHAR(256)    NOT NULL,
        SecretName          NVARCHAR(512)    NOT NULL,
        Enabled             BIT              NULL,
        ExpiresOn           DATETIME2        NULL,
        DaysUntilExpiry     INT              NULL,
        ContentType         NVARCHAR(128)    NULL,
        CollectedAt         DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME()
    );
    CREATE NONCLUSTERED INDEX IX_KVSecret_Vault ON sec.KeyVaultSecretSnapshot (VaultName, CollectedAt DESC);
END
GO

PRINT 'v23 — Key Vault security tables ready.';
GO
