-- SyncApplyRunLog: per-invocation, per-target-table audit of what usp_SyncApply
-- actually did. This closes the biggest observability gap in the pipeline: the
-- apply phase used to be a black box ("apply done") -- queue rows could flip
-- to 'error' or 'dead' with no trace in the service log, and there was no
-- record of how many rows a MERGE/DELETE touched at any point in time. With
-- this table, "what did apply do to table X around time T, and did it fail?"
-- is a single indexed query, and CycleID joins each row back to the exact
-- [cyc:xxxxxxxx] lines in the Python service log.
--
-- Written by usp_SyncApply (sql/02_sync_apply.sql) as a best-effort insert per
-- (invocation, target table). Idle cycles write nothing (the proc's cursor
-- only visits tables that have pending rows), so growth tracks real change
-- traffic, not poll frequency. Retention: unmanaged by default; if it ever
-- grows large, purge by StartedAt, e.g.
--   DELETE FROM ProductionDataBaseSync.SyncApplyRunLog
--    WHERE StartedAt < DATEADD(day, -90, SYSDATETIME());
--
-- Lives under the ProductionDataBaseSync schema (run 00_schema.sql first).
-- Idempotent: safe to re-run. Deploy alongside the updated 02_sync_apply.sql;
-- ordering is forgiving either way (the proc checks for this table and skips
-- the audit insert when it is absent).

IF OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog', 'U') IS NULL
BEGIN
    CREATE TABLE ProductionDataBaseSync.SyncApplyRunLog (
        RunLogID        bigint IDENTITY(1,1) NOT NULL,
        CycleID         nvarchar(40)   NULL,      -- correlation id from the Python service ([cyc:...])
        TargetSchema    nvarchar(128)  NOT NULL,
        TargetTable     nvarchar(255)  NOT NULL,
        PendingCount    int            NOT NULL,  -- pending queue rows seen for this table
        DistinctRecords int            NULL,      -- distinct RecordIDs among them
        MergedCount     int            NULL,      -- rows affected by the MERGE (insert + update)
        DeletedCount    int            NULL,      -- rows affected by the DELETE
        AppliedCount    int            NULL,      -- queue rows flipped to 'applied'
        ErrorCount      int            NULL,      -- queue rows flipped to 'error' (will retry)
        DeadCount       int            NULL,      -- queue rows flipped to 'dead' (retries exhausted)
        Outcome         varchar(10)    NOT NULL,  -- 'ok' | 'error'
        ErrorMsg        nvarchar(max)  NULL,
        StartedAt       datetime2      NOT NULL
                        CONSTRAINT DF_SyncApplyRunLog_Started DEFAULT sysdatetime(),
        DurationMs      int            NULL,
        CONSTRAINT PK_SyncApplyRunLog PRIMARY KEY CLUSTERED (RunLogID)
    );
END
GO

-- Join back to the Python service log of one cycle.
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE name = 'IX_SyncApplyRunLog_Cycle'
                 AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
BEGIN
    CREATE INDEX IX_SyncApplyRunLog_Cycle
        ON ProductionDataBaseSync.SyncApplyRunLog(CycleID);
END
GO

-- "What happened to this table around time T?"
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE name = 'IX_SyncApplyRunLog_Table'
                 AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
BEGIN
    CREATE INDEX IX_SyncApplyRunLog_Table
        ON ProductionDataBaseSync.SyncApplyRunLog(TargetSchema, TargetTable, StartedAt);
END
GO

-- Fast scan for failed applies.
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE name = 'IX_SyncApplyRunLog_Outcome'
                 AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
BEGIN
    CREATE INDEX IX_SyncApplyRunLog_Outcome
        ON ProductionDataBaseSync.SyncApplyRunLog(Outcome, StartedAt);
END
GO