-- Migration script for the TCD cursor columns on an existing TCDProcessStatus table.
-- Run this script if the table already exists in your database.
--
-- FetchedTillRecoveryKey holds the TCD cursor: the last Termination_Call_Detail.RecoveryKey
-- read. RecoveryKey increases in the order rows are written, so rows that reach the TCD
-- database late, or whose DateTime repeats after a DST change, are no longer skipped.
-- FetchedTill keeps the DateTime of that row for reference, and UpdatedTime is the heartbeat
-- written by the active node.
--
-- No backfill is needed: on its first run the connector starts from the existing DateTime
-- cursor (FetchedTill, or UpdatedTime in rows written by older versions) and stores the
-- RecoveryKey cursor from then on.

-- FetchedTill: DateTime of the last TCD row read (older releases stored the cursor here)
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID(N'[dbo].[TCDProcessStatus]') AND name = N'FetchedTill')
BEGIN
    ALTER TABLE [dbo].[TCDProcessStatus]
    ADD [FetchedTill] [datetime] NULL
END
GO

-- FetchedTillRecoveryKey: the TCD cursor
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID(N'[dbo].[TCDProcessStatus]') AND name = N'FetchedTillRecoveryKey')
BEGIN
    ALTER TABLE [dbo].[TCDProcessStatus]
    ADD [FetchedTillRecoveryKey] [float] NULL
END
GO