-- OtOpcUa SQL Server historian backend - schema version 2: OPC UA Part 11 modified-value history (Gitea #668).
-- $(Schema) is substituted by SqlServerSchemaBootstrapper (identifier-validated) BEFORE the script is split on GO lines.
-- Every statement is guarded so a re-run is a no-op.
--
-- One row per sample a HistoryUpdate inserted, replaced or deleted, carrying the PRIOR value (all NULL for an
-- insert). Rows are written by the SAME statement that changes hist.Sample (OUTPUT ... INTO), so a failed update
-- leaves no audit row and a committed one always has its row.
-- WARNING: OUTPUT ... INTO forbids, on its target table: enabled triggers, either side of a FOREIGN KEY, and CHECK
-- constraints. Do not add any of those to SampleModified, or every HistoryUpdate fails.
IF OBJECT_ID(N'[$(Schema)].[SampleModified]', N'U') IS NULL
CREATE TABLE [$(Schema)].[SampleModified] (
  TagId int NOT NULL,
  SourceTimeUtc datetime2(7) NOT NULL,
  ModifiedId bigint IDENTITY(1,1) NOT NULL,
  ModificationTimeUtc datetime2(7) NOT NULL,
  UpdateType tinyint NOT NULL,
  Principal nvarchar(200) NOT NULL,
  ApiKeyId nvarchar(200) NULL,
  ValueKind tinyint NULL,
  NumValue float NULL,
  IntValue bigint NULL,
  StrValue nvarchar(max) NULL,
  StatusCode int NULL,
  ServerTimeUtc datetime2(7) NULL,
  CONSTRAINT PK_SampleModified PRIMARY KEY CLUSTERED (TagId, SourceTimeUtc, ModifiedId))
GO
IF NOT EXISTS (SELECT 1 FROM [$(Schema)].[SchemaVersion] WHERE Version = 2)
INSERT [$(Schema)].[SchemaVersion] (Version, AppliedUtc) VALUES (2, SYSUTCDATETIME())
GO