-- v25 — Server security facts, customer policy overrides, license manifest metadata

IF OBJECT_ID('sec.ServerSecurityFact', 'U') IS NULL
BEGIN
  CREATE TABLE sec.ServerSecurityFact (
    SecurityFactId              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId                    UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    SqlAuditEnabled             BIT NULL,
    XpCmdshellEnabled           BIT NULL,
    AdHocDistributedQueries     BIT NULL,
    ClrStrictSecurity           BIT NULL,
    CrossDbOwnershipChaining    BIT NULL,
    RemoteAdminConnections      BIT NULL,
    SaLoginDisabled             BIT NULL,
    MetadataJson                NVARCHAR(MAX) NULL,
    CollectedAt                 DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_ServerSecurityFact_Server UNIQUE (ServerId)
  );
  CREATE INDEX IX_ServerSecurityFact_Server ON sec.ServerSecurityFact (ServerId);
END;
GO

IF OBJECT_ID('config.CustomerPolicyOverride', 'U') IS NULL
BEGIN
  CREATE TABLE config.CustomerPolicyOverride (
    OverrideId     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    PolicyCode     NVARCHAR(64) NOT NULL REFERENCES config.PolicyThresholdCatalog(PolicyCode),
    OverrideValue  FLOAT NOT NULL,
    Reason         NVARCHAR(512) NULL,
    SetBy          NVARCHAR(256) NULL,
    CreatedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    IsActive       BIT NOT NULL DEFAULT 1,
    CONSTRAINT UQ_CustomerPolicyOverride_Code UNIQUE (PolicyCode)
  );
END;
GO

IF OBJECT_ID('ctl.LicenseManifestMeta', 'U') IS NULL
BEGIN
  CREATE TABLE ctl.LicenseManifestMeta (
    MetaId           INT IDENTITY(1,1) PRIMARY KEY,
    LicenseId        NVARCHAR(128) NOT NULL,
    SourcePath       NVARCHAR(1024) NULL,
    SignatureValid   BIT NOT NULL DEFAULT 0,
    SignatureStatus  NVARCHAR(64) NULL,
    InstalledAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    InstalledBy      NVARCHAR(256) NULL,
    ManifestJson     NVARCHAR(MAX) NULL
  );
END;
GO
