-- v24 — Policy Threshold Catalog (config-driven thresholds; replaces hardcoded literals)
IF OBJECT_ID('config.PolicyThresholdCatalog', 'U') IS NULL
BEGIN
  CREATE TABLE config.PolicyThresholdCatalog (
    PolicyCode       NVARCHAR(64)  NOT NULL PRIMARY KEY,
    Domain           NVARCHAR(32)  NOT NULL,
    Label            NVARCHAR(256) NOT NULL,
    DefaultValue     FLOAT         NOT NULL,
    FloorValue       FLOAT         NULL,
    CeilingValue     FLOAT         NULL,
    Unit             NVARCHAR(32)  NULL,
    Citation         NVARCHAR(512) NULL,
    IndustryDefault  NVARCHAR(64)  NULL,
    IsActive         BIT           NOT NULL DEFAULT 1,
    CreatedAt        DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

MERGE config.PolicyThresholdCatalog AS t
USING (VALUES
  ('capacity.volume.low_free_pct', 'Capacity', 'Volume low free space ratio', 0.08, 0.05, 0.25, 'ratio', 'Internal — capacity planning', 'Commercial'),
  ('capacity.volume.low_free_gb', 'Capacity', 'Volume low free space absolute GB', 10, 5, 50, 'GB', 'Internal — capacity planning', 'Commercial'),
  ('patch.cu_behind_warn', 'Patching', 'CU behind warning threshold', 1, 0, 5, 'count', 'DISA STIG / NIST SI-2', 'Government'),
  ('patch.cu_behind_crit', 'Patching', 'CU behind critical threshold', 3, 1, 12, 'count', 'DISA STIG / NIST SI-2', 'Government'),
  ('perf.cpu_warn_pct', 'Performance', 'CPU utilization warning', 75, 50, 90, 'percent', 'Internal SLO', 'Commercial'),
  ('perf.cpu_crit_pct', 'Performance', 'CPU utilization critical', 90, 75, 99, 'percent', 'Internal SLO', 'Commercial'),
  ('perf.blocking_warn', 'Performance', 'Blocking sessions warning', 1, 0, 10, 'count', 'Internal SLO', 'Commercial'),
  ('perf.blocking_crit', 'Performance', 'Blocking sessions critical', 5, 1, 100, 'count', 'Internal SLO', 'Commercial'),
  ('backup.rpo_hours_full', 'Availability', 'Full backup RPO hours', 24, 12, 168, 'hours', 'NIST CP-9', 'Enterprise'),
  ('backup.rpo_hours_log', 'Availability', 'Log backup RPO hours', 1, 0.25, 24, 'hours', 'NIST CP-9', 'Enterprise'),
  ('security.tls_min_version', 'Security', 'Minimum TLS version numeric', 1.2, 1.2, 1.3, 'version', 'DISA STIG V-213966', 'Government'),
  ('security.orphaned_logins_max', 'Security', 'Max orphaned SQL logins', 0, 0, 5, 'count', 'CJIS 5.4 / NIST AC-2', 'Government'),
  ('security.open_alerts_warn', 'Security', 'Open alerts warning count', 3, 1, 20, 'count', 'Internal SOC', 'Commercial'),
  ('security.open_alerts_crit', 'Security', 'Open alerts critical count', 10, 3, 100, 'count', 'Internal SOC', 'Commercial'),
  ('compliance.score_warn_pct', 'Compliance', 'Compliance score warning', 80, 50, 95, 'percent', 'Internal governance', 'Commercial'),
  ('compliance.score_crit_pct', 'Compliance', 'Compliance score critical', 50, 30, 80, 'percent', 'Internal governance', 'Commercial'),
  ('jobs.failed_warn', 'Operations', 'Failed agent jobs warning', 1, 0, 10, 'count', 'Internal ops', 'Commercial'),
  ('jobs.failed_crit', 'Operations', 'Failed agent jobs critical', 5, 1, 50, 'count', 'Internal ops', 'Commercial'),
  ('hadr.sync_lag_warn_sec', 'HADR', 'HADR sync lag warning seconds', 30, 5, 300, 'seconds', 'Internal DR', 'Enterprise'),
  ('telemetry.stale_hours', 'Operations', 'Telemetry stale threshold hours', 24, 4, 168, 'hours', 'Truthfulness gate', 'Commercial')
) AS s(PolicyCode, Domain, Label, DefaultValue, FloorValue, CeilingValue, Unit, Citation, IndustryDefault)
ON t.PolicyCode = s.PolicyCode
WHEN MATCHED THEN UPDATE SET
  Domain = s.Domain, Label = s.Label, DefaultValue = s.DefaultValue,
  FloorValue = s.FloorValue, CeilingValue = s.CeilingValue, Unit = s.Unit,
  Citation = s.Citation, IndustryDefault = s.IndustryDefault
WHEN NOT MATCHED THEN INSERT (PolicyCode, Domain, Label, DefaultValue, FloorValue, CeilingValue, Unit, Citation, IndustryDefault)
VALUES (s.PolicyCode, s.Domain, s.Label, s.DefaultValue, s.FloorValue, s.CeilingValue, s.Unit, s.Citation, s.IndustryDefault);
GO
