IF OBJECT_ID('DSYNC_VERSION') IS NULL
BEGIN
     CREATE TABLE DSYNC_VERSION (
            ID INTEGER IDENTITY(1,1) PRIMARY KEY
            ,CLIENT_ID INTEGER NOT NULL FOREIGN KEY REFERENCES DSYNC_CLIENT(ID)
            ,OBJECT_ID INT NOT NULL
            ,LAST_VERSION BIGINT NOT NULL DEFAULT 0
            ,LAST_TIME DATETIME NOT NULL DEFAULT GETDATE()
    )
    CREATE INDEX IX_DSYNC_VERSION_CLIENT ON DSYNC_VERSION(CLIENT_ID) INCLUDE (OBJECT_ID)
END

GO

IF OBJECT_ID('UDF_DSYNC_GET_VERSION') IS NOT  NULL 
	DROP FUNCTION UDF_DSYNC_GET_VERSION
GO

CREATE FUNCTION UDF_DSYNC_GET_VERSION(@CLIENT_ID INT,@OBJECT_NAME SYSNAME)
RETURNS INT
AS
BEGIN

        DECLARE @OBJECT_ID INT,@MIN_VALID_VERSION BIGINT = 0,@LAST_SYNC_VERSION BIGINT = 0

        SELECT @OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
        SELECT @MIN_VALID_VERSION = MIN_VALID_VERSION FROM SYS.CHANGE_TRACKING_TABLES WHERE OBJECT_ID = @OBJECT_ID
        SELECT @LAST_SYNC_VERSION = LAST_VERSION FROM DSYNC_VERSION WHERE CLIENT_ID = @CLIENT_ID AND OBJECT_ID = @OBJECT_ID

        SELECT @MIN_VALID_VERSION = COALESCE(@MIN_VALID_VERSION,0),@LAST_SYNC_VERSION = COALESCE(@LAST_SYNC_VERSION,0)

        SELECT @LAST_SYNC_VERSION = CASE WHEN @LAST_SYNC_VERSION >= @MIN_VALID_VERSION THEN @LAST_SYNC_VERSION ELSE @MIN_VALID_VERSION END

        RETURN @LAST_SYNC_VERSION

END

GO

IF OBJECT_ID('SP_DSYNC_UPDATE_VERSION') IS NOT NULL
	DROP PROCEDURE SP_DSYNC_UPDATE_VERSION
GO

CREATE PROCEDURE SP_DSYNC_UPDATE_VERSION(@CLIENT_ID INT,@TABLE_NAME SYSNAME)
AS
BEGIN
        UPDATE DSYNC_VERSION SET LAST_TIME = GETDATE(), LAST_VERSION = CHANGE_TRACKING_CURRENT_VERSION() WHERE CLIENT_ID = @CLIENT_ID AND OBJECT_ID = OBJECT_ID(@TABLE_NAME)
        IF (@@ROWCOUNT = 0)
        BEGIN
                INSERT INTO DSYNC_VERSION (CLIENT_ID,OBJECT_ID,LAST_TIME,LAST_VERSION) VALUES (@CLIENT_ID,OBJECT_ID(@TABLE_NAME),GETDATE(),CHANGE_TRACKING_CURRENT_VERSION())
        END
END
