--create table to record state
IF NOT EXISTS (
    SELECT 1
    FROM [sys].[tables] AS [T]
    WHERE [T].[name] = 'change_tracking_state'
)
BEGIN
    --DROP TABLE [dbo].[change_tracking_state]
    CREATE TABLE [dbo].[change_tracking_state] (
        [pharmacy_schema]   sysname NOT NULL,
        [table_name]        sysname NOT NULL,
        [last_sync_version] BIGINT  NOT NULL,
        CONSTRAINT PK_change_tracking_state PRIMARY KEY (
            [pharmacy_schema],
            [table_name]
        )
    );
END;

--enable change tracking if missing
IF NOT EXISTS (
    SELECT [d].[name],
           [ctd].[database_id],
           [ctd].[is_auto_cleanup_on],
           [ctd].[retention_period],
           [ctd].[retention_period_units],
           [ctd].[retention_period_units_desc],
           [ctd].[max_cleanup_version]
    FROM [sys].[change_tracking_databases] AS [ctd]
        JOIN [sys].[databases]             AS [d] ON [d].[database_id] = [ctd].[database_id]
    WHERE [d].[name] = 'pharmacy'
)
BEGIN
    ALTER DATABASE [Pharmacy]
    SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
END;

--add tables to change tracking
DECLARE @tpl_ct NVARCHAR(MAX)='ALTER TABLE [@schema@].[@table@] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
'
DECLARE @q NVARCHAR(MAX)='';

SELECT @q = @q + REPLACE(REPLACE(@tpl_ct,'@schema@', s.name),'@table@', t.name)
FROM [sys].[tables]      AS [t]
    JOIN [sys].[schemas] AS [s] ON [s].[schema_id] = [t].[schema_id]
WHERE 1 = 1
  AND [s].[name] LIKE 'ama%'
  AND [t].[name] IN ( N'Account', N'Account_key', N'Account_link', N'Account_text', N'Accounting_turnover_agg',
                      N'Address', N'Address_category', N'Address_category_text', N'Address_contact_person_list',
                      N'Address_Criteria', N'Address_key', N'Batch_run', N'BVR_member', N'CRS_batch_run',
                      N'CRS_document_header', N'DH_Criteria', N'Document_date', N'Document_error', N'Document_header',
                      N'Entry', N'Entry_reconciliation', N'Financial_relation_account'
  )
  AND NOT EXISTS(
    SELECT 1
    FROM sys.change_tracking_tables ct
    WHERE OBJECT_SCHEMA_NAME(ct.object_id) = s.name
    AND OBJECT_NAME(ct.object_id) = t.name

  )
;

EXEC(@q);

--init change tracking
INSERT INTO [dbo].[change_tracking_state] (
    [pharmacy_schema],
    [table_name],
    [last_sync_version]
)
SELECT [s].[name] AS [pharmacy_schema],
       [t].[name] AS [table_name],
       CHANGE_TRACKING_CURRENT_VERSION()
FROM [sys].[tables]      AS [t]
    JOIN [sys].[schemas] AS [s] ON [s].[schema_id] = [t].[schema_id]
WHERE 1 = 1
  AND [s].[name] LIKE 'ama%'
  AND [t].[name] IN ( N'Account', N'Account_key', N'Account_link', N'Account_text', N'Accounting_turnover_agg',
                      N'Address', N'Address_category', N'Address_category_text', N'Address_contact_person_list',
                      N'Address_Criteria', N'Address_key', N'Batch_run', N'BVR_member', N'CRS_batch_run',
                      N'CRS_document_header', N'DH_Criteria', N'Document_date', N'Document_error', N'Document_header',
                      N'Entry', N'Entry_reconciliation', N'Financial_relation_account'
)
  AND NOT EXISTS (
              SELECT 1
              FROM [dbo].[change_tracking_state] AS [cts]
              WHERE [cts].[pharmacy_schema] = [s].[name] AND [cts].[table_name] = [t].[name]
          );