CREATE OR ALTER PROCEDURE [dbo].[ProjectIndexSync]
AS
	DECLARE @FindTableToSync INT
	DECLARE @ProjectId INT
	DECLARE @projectdatabase VARCHAR(255)
	DECLARE @TableName VARCHAR(255)
	DECLARE @SyncType VARCHAR(10)
	DECLARE @FieldNames VARCHAR(MAX)
	DECLARE @CompatibleFields NVARCHAR(MAX)

	DECLARE @SQLString NVARCHAR(MAX)

BEGIN


    DECLARE @LogTextStart VARCHAR(5) = 'Start';
    DECLARE @LogTextEnd VARCHAR(3) = 'End';
	DECLARE @ExecutingText VARCHAR(11) = 'Executing: '

    IF( (SELECT COUNT(1) FROM [dbo].[ProjectSyncLog] WHERE SyncPoint = @LogTextEnd) = 0)
    BEGIN
        
        IF(DATEDIFF(MINUTE, (SELECT TOP 1 DateTimeLog FROM [dbo].[ProjectSyncLog] ORDER BY Id DESC ), GETDATE()) < 30)
        BEGIN
            INSERT INTO [dbo].[ProjectSyncLog](SyncPoint, DateTimeLog, SyncResult)
            VALUES('Sync Request Canceled', GETDATE(), 'Another project synchronization process was requested AND it will not be executed because the current process has not completed.')
            RETURN;
        END
        ELSE
        BEGIN
            DELETE FROM [dbo].[ProjectSyncLog];
            INSERT INTO [dbo].[ProjectSyncLog](SyncPoint, DateTimeLog, SyncResult)
            VALUES('Sync Request Continue', GETDATE(), 'Another project synchronization process was requested AND it will be executed because after 30 minutes the current process has not completed.')
        END
    END 
    ELSE
    BEGIN
        DELETE FROM [dbo].[ProjectSyncLog];
    END

	--SP really start executing FROM here--
	INSERT INTO [dbo].[ProjectSyncLog](SyncPoint, DateTimeLog)
    VALUES (@LogTextStart, GETDATE());

	--find if any project/table to sync
	SELECT @FindTableToSync = COUNT(1) 
    FROM ProjectSync 
    WHERE IsActive = 1;

	IF (@FindTableToSync = 0)
    BEGIN
		INSERT INTO [dbo].[ProjectSyncLog](SyncPoint, DateTimeLog, SyncResult) 
        VALUES (@LogTextStart, GETDATE(), 'No Table To Sync.');
        RETURN;
	END;

    DECLARE @ProjectToIndexTable TABLE
    (
        ProjectId INT,
        ProjectDatabase NVARCHAR(MAX),
        TableName NVARCHAR(255), 
        SyncType NVARCHAR(10), 
        FieldNames NVARCHAR(MAX)
    )

    INSERT INTO @ProjectToIndexTable (ProjectId, ProjectDatabase, TableName, SyncType, FieldNames)
    SELECT 
    ps.ProjectId
    , p.ProjectDatabase
    , ps.TableName
    , ps.SyncType
    , ps.FieldNames 
    FROM ProjectSync AS ps WITH(NOLOCK)
    INNER JOIN Project AS p WITH(NOLOCK) ON ps.ProjectId = p.Id 
    WHERE ps.IsActive = 1 
    AND ps.RequiresIndexing = 1
    AND p.Active = 1; 

		--sync per project/table
    DECLARE c_table CURSOR FOR 
    SELECT ProjectId, ProjectDatabase, TableName, SyncType, FieldNames FROM @ProjectToIndexTable; 

    OPEN c_table  
    FETCH NEXT FROM c_table INTO @ProjectId, @projectdatabase, @TableName, @SyncType, @FieldNames

    WHILE @@FETCH_STATUS = 0  
    BEGIN

        UPDATE ProjectSync SET 
        LastSyncStatus = 'Incomplete', 
        LastSync = GETDATE() 
        WHERE ProjectId = @ProjectId 
        AND TableName = @TableName;

		BEGIN TRY

			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
			VALUES ('Table', @ProjectId, @TableName, 'Table Sync Start');

			IF @SyncType = 'TABLE' 
			BEGIN
				SET @SQLString = '
					SELECT @CompatibleFields = STUFF(
						(SELECT '',['' + rtrim(c.column_name) + '']''
						FROM [ClarityIndex].INFORMATION_SCHEMA.COLUMNS c
						WHERE c.TABLE_NAME = t.TABLE_NAME AND c.column_name <> ''ProjectId''
						AND (
							EXISTS(
									SELECT *
									FROM ' + @projectdatabase + '.INFORMATION_SCHEMA.COLUMNS
									WHERE  TABLE_NAME = @TableName AND COLUMN_NAME = c.column_name
									)
							AND c.DATA_TYPE = (
											SELECT DATA_TYPE 
											FROM ' + @projectdatabase + '.INFORMATION_SCHEMA.COLUMNS 
											WHERE COLUMN_NAME = c.column_name 
											AND TABLE_NAME = @TableName
										)
							) 
						FOR XML PATH('''')), 1, 1, '''')
					FROM [ClarityIndex].INFORMATION_SCHEMA.COLUMNS t 
					WHERE t.TABLE_NAME in (''PQ_'' + @TableName)
					GROUP BY t.TABLE_NAME;
				SET @FieldResults = @CompatibleFields'

				INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
				VALUES ('Table', @ProjectId, @TableName, @ExecutingText + @SQLString);

				EXEC sp_executesql @SQLString, N'@CompatibleFields NVARCHAR(MAX), @TableName NVARCHAR(MAX), @FieldResults NVARCHAR(MAX) OUTPUT', @CompatibleFields, @TableName, @FieldResults=@FieldNames OUTPUT;

			END

			-- DELETE --------------------------------
			SET @SQLString = 'DELETE FROM [ClarityIndex].[dbo].[PQ_' + @TableName + '] WHERE ProjectId = ' + CAST(@ProjectId AS NVARCHAR(10));

			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
            VALUES ('Table', @ProjectId, @TableName, @ExecutingText + @SQLString);

			EXEC sp_executesql @SQLString;

			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
			VALUES ('Table', @ProjectId, @TableName, 'Table deleted');

		-- INSERT --------------------------------
		SET @SQLString = 'INSERT INTO [ClarityIndex].[dbo].[PQ_' + @TableName + '] (ProjectId,' + @FieldNames + ') SELECT ' + CAST(@ProjectId AS NVARCHAR(10)) + ', ' + @FieldNames + ' FROM ' + @projectdatabase + '.dbo.' + @TableName
			
			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
            VALUES ('Table', @ProjectId, @TableName, @ExecutingText + @SQLString);

			EXEC sp_executesql @SQLString

			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
			VALUES ('Table', @ProjectId, @TableName, 'Values inserted');

			-- UPDATE -------------------------------- 
			UPDATE	ProjectSync SET 
			RequiresIndexing = 0, 
			LastSyncStatus = 'Complete', 
			LastSync = GETDATE() 
			WHERE ProjectId = @ProjectId AND TableName = @TableName;

			INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
			VALUES ('Table', @ProjectId, @TableName, 'Table Sync End');

		END TRY
        BEGIN CATCH
            INSERT INTO ProjectSyncLog(SyncPoint, ProjectId, TableName, SyncResult) 
            VALUES ('Error', @ProjectId, @TableName, ERROR_MESSAGE());
        END CATCH

        FETCH NEXT FROM c_table INTO @ProjectId, @projectdatabase, @TableName, @SyncType, @FieldNames 
    END 

    CLOSE c_table  
    DEALLOCATE c_table 

	--SP completed --
	INSERT INTO ProjectSyncLog(SyncPoint, SyncResult) 
	VALUES (@LogTextEnd, 'OK');
END
