-- =============================================
-- Application: Sample 61 - gsqlcmd Sync
-- Version 6.13, April 28, 2025
--
-- Copyright 2019-2025 Gartle LLC
--
-- License: MIT
-- =============================================

SET NOCOUNT ON
GO

CREATE SCHEMA [s61];
GO

CREATE TABLE [s61].[deleted_guids] (
      [id] uniqueidentifier NOT NULL
    , [source] nvarchar(128) NOT NULL
    , [last_update] datetime NOT NULL
    , CONSTRAINT [PK_deleted_guids] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_deleted_guids_last_update] ON [s61].[deleted_guids] ([last_update]);
GO

CREATE TABLE [s61].[deleted_ints] (
      [id] int NOT NULL
    , [source] nvarchar(128) NOT NULL
    , [source_table] nvarchar(128) NOT NULL
    , [source_row_id] int NOT NULL
    , [last_update] datetime NOT NULL
    , CONSTRAINT [PK_deleted_ints] PRIMARY KEY ([id], [source])
);
GO

CREATE INDEX [IX_deleted_ints_last_update] ON [s61].[deleted_ints] ([last_update]);
GO

CREATE INDEX [IX_deleted_ints_source_row] ON [s61].[deleted_ints] ([source_table], [source_row_id]);
GO

CREATE TABLE [s61].[sync_last_updates] (
      [source] nvarchar(128) NOT NULL
    , [target] nvarchar(128) NOT NULL
    , [last_update] datetime NOT NULL
    , [prev_update] datetime NULL
    , CONSTRAINT [PK_sync_last_updates] PRIMARY KEY ([source], [target])
);
GO

CREATE TABLE [s61].[sync_timestamps] (
      [source] nvarchar(128) NOT NULL
    , [target] nvarchar(128) NOT NULL
    , [last_ts] binary(8) NOT NULL
    , [prev_ts] binary(8) NULL
    , CONSTRAINT [PK_sync_timestamps] PRIMARY KEY ([source], [target])
);
GO

CREATE TABLE [s61].[table11] (
      [id] int IDENTITY(1,1) NOT NULL
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table11] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table11_name] ON [s61].[table11] ([name]);
GO

CREATE TABLE [s61].[table12] (
      [id] int IDENTITY(1,1) NOT NULL
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table12] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table12_name] ON [s61].[table12] ([name]);
GO

CREATE TABLE [s61].[table13] (
      [id] int NOT NULL
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table13] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table13_name] ON [s61].[table13] ([name]);
GO

CREATE TABLE [s61].[table14] (
      [id] int IDENTITY(1,1) NOT NULL
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table14] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table14_name] ON [s61].[table14] ([name]);
GO

CREATE TABLE [s61].[table21] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table21_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table21] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table21_name] ON [s61].[table21] ([name]);
GO

CREATE TABLE [s61].[table22] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table22_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , CONSTRAINT [PK_table22] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table22_name] ON [s61].[table22] ([name]);
GO

CREATE TABLE [s61].[table31] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table31_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [ts] timestamp NOT NULL
    , CONSTRAINT [PK_table31] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table31_name] ON [s61].[table31] ([name]);
GO

CREATE INDEX [IX_table31_ts] ON [s61].[table31] ([ts]);
GO

CREATE TABLE [s61].[table32] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table32_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [ts] timestamp NOT NULL
    , CONSTRAINT [PK_table32] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table32_name] ON [s61].[table32] ([name]);
GO

CREATE INDEX [IX_table32_ts] ON [s61].[table32] ([ts]);
GO

CREATE TABLE [s61].[table41] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table41_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [last_update] datetime NOT NULL CONSTRAINT [DF_table41_last_update] DEFAULT(getutcdate())
    , CONSTRAINT [PK_table41] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table41_name] ON [s61].[table41] ([name]);
GO

CREATE INDEX [IX_table41_last_update] ON [s61].[table41] ([last_update]);
GO

CREATE TABLE [s61].[table42] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table42_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [last_update] datetime NOT NULL CONSTRAINT [DF_table42_last_update] DEFAULT(getutcdate())
    , CONSTRAINT [PK_table42] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table42_name] ON [s61].[table42] ([name]);
GO

CREATE INDEX [IX_table42_last_update] ON [s61].[table42] ([last_update]);
GO

CREATE TABLE [s61].[table51] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table51_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [last_update] datetime NOT NULL CONSTRAINT [DF_table51_last_update] DEFAULT(getutcdate())
    , CONSTRAINT [PK_table51] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table51_name] ON [s61].[table51] ([name]);
GO

CREATE INDEX [IX_table51_last_update] ON [s61].[table51] ([last_update]);
GO

CREATE TABLE [s61].[table52] (
      [id] uniqueidentifier NOT NULL CONSTRAINT [DF_table52_id] DEFAULT(newid())
    , [name] nvarchar(50) NOT NULL
    , [last_update] datetime NOT NULL CONSTRAINT [DF_table52_last_update] DEFAULT(getutcdate())
    , CONSTRAINT [PK_table52] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table52_name] ON [s61].[table52] ([name]);
GO

CREATE INDEX [IX_table52_last_update] ON [s61].[table52] ([last_update]);
GO

CREATE TABLE [s61].[table71] (
      [id] int IDENTITY(1,1) NOT NULL
    , [name] nvarchar(50) NOT NULL
    , [source_table] nvarchar(128) NULL
    , [source_row_id] int NULL
    , [last_update] datetime NULL
    , CONSTRAINT [PK_table71] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table71_name] ON [s61].[table71] ([name]);
GO

CREATE INDEX [IX_table71_last_update] ON [s61].[table71] ([last_update]);
GO

CREATE INDEX [IX_table71_source_row] ON [s61].[table71] ([source_table], [source_row_id]);
GO

CREATE TABLE [s61].[table72] (
      [id] int IDENTITY(1,1) NOT NULL
    , [name] nvarchar(50) NOT NULL
    , [source_table] nvarchar(128) NULL
    , [source_row_id] int NULL
    , [last_update] datetime NULL
    , CONSTRAINT [PK_table72] PRIMARY KEY ([id])
);
GO

CREATE INDEX [IX_table72_name] ON [s61].[table72] ([name]);
GO

CREATE INDEX [IX_table72_last_update] ON [s61].[table72] ([last_update]);
GO

CREATE INDEX [IX_table72_source_row] ON [s61].[table72] ([source_table], [source_row_id]);
GO

-- =============================================
-- Author:      Gartle LLC
-- Release:     6.0, 2022-07-05
-- Description: Commits sync_last_updates in the final sync step
-- =============================================

CREATE PROCEDURE [s61].[usp_commit_last_updates]
    @source nvarchar(128) = NULL
    , @target nvarchar(128) = NULL
AS
BEGIN

UPDATE s61.sync_last_updates SET prev_update = last_update WHERE [source] = @source AND [target] = @target

END


GO

-- =============================================
-- Author:      Gartle LLC
-- Release:     6.0, 2022-07-05
-- Description: Selects rows to delete
-- =============================================

CREATE PROCEDURE [s61].[usp_select_delete_rows]
    @source nvarchar(128) = NULL
    , @target nvarchar(128) = NULL
AS
BEGIN

SELECT
    source_table, source_row_id
FROM
    s61.deleted_ints
WHERE
    last_update > COALESCE((
        SELECT
            TOP 1 prev_update
        FROM
            s61.sync_last_updates
        WHERE
            [source] = 'deleted_ints' AND [target] = @target
        ), 0x)
    AND [source] = @source

END


GO

-- =============================================
-- Author:      Gartle LLC
-- Release:     6.0, 2022-07-05
-- Description: Selects rows to update and insert
-- =============================================

CREATE PROCEDURE [s61].[usp_select_upsert_rows]
    @source nvarchar(128) = NULL
    , @target nvarchar(128) = NULL
AS
BEGIN

DECLARE @schema nvarchar(128) = 's61'

DECLARE @object_id int = OBJECT_ID(@schema + '.' + @source)

IF @object_id IS NULL
    RAISERROR ('Object %s not found', 10, 1, @source)

DECLARE @columns nvarchar(max) = ''

SET @columns = STUFF((
        SELECT
            ', ' + c.name
        FROM
            sys.columns c
        WHERE
            c.[object_id] = @object_id
            AND c.is_identity = 0 AND c.is_computed = 0
        ORDER BY
            c.column_id
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '')

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT
    ' + @columns + '
FROM
    ' + QUOTENAME(@schema) + '.' + QUOTENAME(@source) + '
WHERE
    last_update > COALESCE((
        SELECT
            TOP 1 prev_update
        FROM
            ' + @schema + '.sync_last_updates
        WHERE
            [source] = ''' + @source + ''' AND [target] = @target
        ), 0x)'

-- PRINT @sql

EXEC sp_executesql @sql, N'@target nvarchar(128)', @target = @target;

END


GO

-- =============================================
-- Author:      Gartle LLC
-- Release:     6.0, 2022-07-05
-- Description: Updates sync_last_updates in the first sync step
-- =============================================

CREATE PROCEDURE [s61].[usp_update_last_updates]
    @source nvarchar(128) = NULL
    , @target nvarchar(128) = NULL
AS
BEGIN

DECLARE @schema nvarchar(128) = 's61'

DECLARE @object_id int = OBJECT_ID(@schema + '.' + @source)

IF @object_id IS NULL
    RAISERROR ('Object %s not found', 10, 1, @source)

DECLARE @sql nvarchar(max)

SET @sql = 'MERGE ' + @schema + '.sync_last_updates AS t
USING (SELECT ''' + @source + ''' AS [source], @target AS [target], COALESCE(MAX(last_update), 0) AS last_update FROM ' + @schema + '.' + QUOTENAME(@source) + ') AS s ([source], [target], last_update)
ON (t.[source] = s.[source] AND t.[target] = s.[target])
WHEN MATCHED AND t.last_update = t.prev_update THEN
    UPDATE SET last_update = s.last_update
WHEN NOT MATCHED THEN
    INSERT ([source], [target], last_update) VALUES (s.[source], s.[target], s.last_update);'

-- PRINT @sql

EXEC sp_executesql @sql, N'@target nvarchar(128)', @target = @target;

MERGE s61.sync_last_updates AS t
USING (SELECT 'deleted_ints' AS [source], @target AS [target], COALESCE(MAX(last_update), 0) AS last_update FROM s61.deleted_ints) AS s ([source], [target], last_update)
ON (t.[source] = s.[source] AND t.[target] = s.[target])
WHEN MATCHED AND t.last_update = t.prev_update THEN
    UPDATE SET last_update = s.last_update
WHEN NOT MATCHED THEN
    INSERT ([source], [target], last_update) VALUES (s.[source], s.[target], s.last_update);

END


GO

CREATE TRIGGER [s61].[trigger_table41_after_update]
    ON [s61].[table41]
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

UPDATE s61.table41
SET
    last_update = GETUTCDATE()
FROM
    s61.table41 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table42_after_update]
    ON [s61].[table42]
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

UPDATE s61.table42
SET
    last_update = GETUTCDATE()
FROM
    s61.table42 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table51_after_delete]
    ON [s61].[table51]
    AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

INSERT INTO deleted_guids (id, [source], last_update)
SELECT
    deleted.id
    , 'table51' AS [source]
    , GETUTCDATE() AS last_update
FROM
    deleted

END


GO

CREATE TRIGGER [s61].[trigger_table51_after_insert]
    ON [s61].[table51]
    AFTER INSERT
AS
BEGIN

SET NOCOUNT ON

UPDATE s61.table51
SET
    last_update = GETUTCDATE()
FROM
    s61.table51 t
    INNER JOIN inserted ON inserted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table51_after_update]
    ON [s61].[table51]
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

IF @@NESTLEVEL > 1
    RETURN;

UPDATE s61.table51
SET
    last_update = GETUTCDATE()
FROM
    s61.table51 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table52_after_delete]
    ON [s61].[table52]
    AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

INSERT INTO deleted_guids (id, [source], last_update)
SELECT
    deleted.id
    , 'table52' AS [source]
    , GETUTCDATE() AS last_update
FROM
    deleted

END


GO

CREATE TRIGGER [s61].[trigger_table52_after_insert]
    ON [s61].[table52]
    AFTER INSERT
AS
BEGIN

SET NOCOUNT ON

UPDATE s61.table52
SET
    last_update = GETUTCDATE()
FROM
    s61.table52 t
    INNER JOIN inserted ON inserted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table52_after_update]
    ON [s61].[table52]
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

IF @@NESTLEVEL > 1
    RETURN;

UPDATE s61.table52
SET
    last_update = DATEADD(DAY, 1, GETUTCDATE())
FROM
    s61.table52 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table71_after_delete]
    ON s61.table71
    AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

INSERT INTO deleted_ints (id, [source], source_table, source_row_id, last_update)
SELECT
    deleted.id
    , 'table71' AS [source]
    , source_table
    , source_row_id
    , GETUTCDATE() AS last_update
FROM
    deleted

END


GO

CREATE TRIGGER [s61].[trigger_table71_after_insert]
    ON [s61].[table71]
    AFTER INSERT
AS
BEGIN

SET NOCOUNT ON

IF USER_NAME() IN ('sample61_user2')
    RETURN

UPDATE s61.table71
SET
    source_table = 'table71'
    , source_row_id = t.id
    , last_update = GETUTCDATE()
FROM
    s61.table71 t
    INNER JOIN inserted ON inserted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table71_after_update]
    ON s61.table71
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

IF @@NESTLEVEL > 1
    RETURN;

IF USER_NAME() IN ('sample61_user2')
    RETURN

UPDATE s61.table71
SET
    source_table = deleted.source_table
    , source_row_id = deleted.source_row_id
    , last_update = GETUTCDATE()
FROM
    s61.table71 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table72_after_delete]
    ON s61.table72
    AFTER DELETE
AS
BEGIN

SET NOCOUNT ON

INSERT INTO deleted_ints (id, [source], source_table, source_row_id, last_update)
SELECT
    deleted.id
    , 'table72' AS [source]
    , source_table
    , source_row_id
    , GETUTCDATE() AS last_update
FROM
    deleted

END


GO

CREATE TRIGGER [s61].[trigger_table72_after_insert]
    ON [s61].[table72]
    AFTER INSERT
AS
BEGIN

SET NOCOUNT ON

IF USER_NAME() IN ('sample61_user2')
    RETURN

UPDATE s61.table72
SET
    source_table = 'table72'
    , source_row_id = t.id
    , last_update = GETUTCDATE()
FROM
    s61.table72 t
    INNER JOIN inserted ON inserted.id = t.id

END


GO

CREATE TRIGGER [s61].[trigger_table72_after_update]
    ON s61.table72
    AFTER UPDATE
AS
BEGIN

SET NOCOUNT ON

IF @@NESTLEVEL > 1
    RETURN;

IF USER_NAME() IN ('sample61_user2')
    RETURN

UPDATE s61.table72
SET
    source_table = deleted.source_table
    , source_row_id = deleted.source_row_id
    , last_update = GETUTCDATE()
FROM
    s61.table72 t
    INNER JOIN deleted ON deleted.id = t.id

END


GO

SET IDENTITY_INSERT [s61].[table11] ON;
INSERT INTO [s61].[table11] ([id], [name]) VALUES (1, N'Customer C1');
INSERT INTO [s61].[table11] ([id], [name]) VALUES (2, N'Customer C2');
INSERT INTO [s61].[table11] ([id], [name]) VALUES (3, N'Customer C3');
INSERT INTO [s61].[table11] ([id], [name]) VALUES (4, N'Customer C4');
INSERT INTO [s61].[table11] ([id], [name]) VALUES (5, N'Customer C5');
SET IDENTITY_INSERT [s61].[table11] OFF;
GO

INSERT INTO [s61].[table21] ([id], [name]) VALUES ('d122cc0e-0176-43b1-8013-9f2a3f71d28b', N'Customer C1');
INSERT INTO [s61].[table21] ([id], [name]) VALUES ('fbe5f705-7e38-460a-8c70-694603f51e94', N'Customer C2');
INSERT INTO [s61].[table21] ([id], [name]) VALUES ('c2a7c18c-93d8-4770-8de2-5d6c32168c98', N'Customer C3');
INSERT INTO [s61].[table21] ([id], [name]) VALUES ('a340f87a-a5ad-4004-b306-ce26cc325bf7', N'Customer C4');
INSERT INTO [s61].[table21] ([id], [name]) VALUES ('f13f9a14-c5b2-490d-8109-b7cf347ac2f9', N'Customer C5');
GO

INSERT INTO [s61].[table31] ([id], [name]) VALUES ('d122cc0e-0176-43b1-8013-9f2a3f71d28b', N'Customer C1');
INSERT INTO [s61].[table31] ([id], [name]) VALUES ('fbe5f705-7e38-460a-8c70-694603f51e94', N'Customer C2');
INSERT INTO [s61].[table31] ([id], [name]) VALUES ('c2a7c18c-93d8-4770-8de2-5d6c32168c98', N'Customer C3');
INSERT INTO [s61].[table31] ([id], [name]) VALUES ('a340f87a-a5ad-4004-b306-ce26cc325bf7', N'Customer C4');
INSERT INTO [s61].[table31] ([id], [name]) VALUES ('f13f9a14-c5b2-490d-8109-b7cf347ac2f9', N'Customer C5');
GO

INSERT INTO [s61].[table41] ([id], [name]) VALUES ('d122cc0e-0176-43b1-8013-9f2a3f71d28b', N'Customer C1');
INSERT INTO [s61].[table41] ([id], [name]) VALUES ('fbe5f705-7e38-460a-8c70-694603f51e94', N'Customer C2');
INSERT INTO [s61].[table41] ([id], [name]) VALUES ('c2a7c18c-93d8-4770-8de2-5d6c32168c98', N'Customer C3');
INSERT INTO [s61].[table41] ([id], [name]) VALUES ('a340f87a-a5ad-4004-b306-ce26cc325bf7', N'Customer C4');
INSERT INTO [s61].[table41] ([id], [name]) VALUES ('f13f9a14-c5b2-490d-8109-b7cf347ac2f9', N'Customer C5');
GO

INSERT INTO [s61].[table51] ([id], [name]) VALUES ('d122cc0e-0176-43b1-8013-9f2a3f71d28b', N'Customer C1');
INSERT INTO [s61].[table51] ([id], [name]) VALUES ('fbe5f705-7e38-460a-8c70-694603f51e94', N'Customer C2');
INSERT INTO [s61].[table51] ([id], [name]) VALUES ('c2a7c18c-93d8-4770-8de2-5d6c32168c98', N'Customer C3');
INSERT INTO [s61].[table51] ([id], [name]) VALUES ('a340f87a-a5ad-4004-b306-ce26cc325bf7', N'Customer C4');
INSERT INTO [s61].[table51] ([id], [name]) VALUES ('f13f9a14-c5b2-490d-8109-b7cf347ac2f9', N'Customer C5');
GO

SET IDENTITY_INSERT [s61].[table71] ON;
INSERT INTO [s61].[table71] ([id], [name]) VALUES (1, N'Customer C1');
INSERT INTO [s61].[table71] ([id], [name]) VALUES (2, N'Customer C2');
INSERT INTO [s61].[table71] ([id], [name]) VALUES (3, N'Customer C3');
INSERT INTO [s61].[table71] ([id], [name]) VALUES (4, N'Customer C4');
INSERT INTO [s61].[table71] ([id], [name]) VALUES (5, N'Customer C5');
SET IDENTITY_INSERT [s61].[table71] OFF;
GO

print 'Application installed';
