--EXEC MIG_SYNC_TASKS_Sp
CREATE OR ALTER PROCEDURE [dbo].[MIG_SYNC_TASKS_Sp]
AS 
BEGIN
	-- The purpose of this stored procedure is to generate/populate the data in [MIG_SYNC_TASKS] table.

	DECLARE @MainSite NVARCHAR(MAX), @SITE_COUNT INT

	SELECT @SITE_COUNT = COUNT(*) FROM SITE 

	SELECT @MainSite = SITE_KEY 
	FROM SITE
	WHERE SITE.DEFAULT_SITE = '1'

	DELETE FROM MIG_SYNC_TASKS

	--First, create entries in MIG_SYS_TASKS table, assuming that the file is up-to-date in all the sites.
	-- up-to-date => 1 by default 
	-- To-be-synced => 2  
	-- on-demand-sync => 3  
	-- Not-Permitted => 0 

	INSERT INTO MIG_SYNC_TASKS
	(
		DSET_KEY, 
		REVISION, 
		LAST_UPDATED_VAULT,
		s001,
		s002,
		s003,
		s004,
		s005,
		s006,
		s007,
		s008,
		s009,
		s010,
		s011,
		s012,
		s013,
		s014,
		s015,
		s016,
		s017,
		s018,
		s019,
		s020
	)
	SELECT 
		DSET_KEY, 
		REVISION, 
		NULL, 
		CASE WHEN @SITE_COUNT > 0 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 1 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 2 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 3 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 4 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 5 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 6 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 7 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 8 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 9 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 10 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 11 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 12 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 13 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 14 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 15 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 16 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 17 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 18 THEN 1 ELSE 3 END,
		CASE WHEN @SITE_COUNT > 19 THEN 1 ELSE 3 END 
	FROM DATASET
	WHERE DATASET.OBJECT_TYPE IN (12, 14) 

	-- Next, update the entres of MIG_SYNC_TASKS table based on the entries of following tables:
	--		SYNC_TASKS (to be synched)
	--		SYNC_TASKS_P (On-demand)
	--		SYNC_TASKS_R (Not permitted)
	-- Note: 
	--		1)	If no entry is found in the above 3 tables for a given Site, it means the file is up-to-date 
	--			in the given site)
	--		2)	Most recent vault is evaluated based on the value of "SOURCE_SITE_KEY" field in the SYNC_TASKS table.
	--			If no entry is found in the SYNC_TASKS table, then system will assume that the file is up-to-date in
	--			the default site.


	-- Update flags for "To be synched - automatically"
	UPDATE mig
	SET mig.s001 = CASE WHEN sit.SYNC_TASK_FIELD = 's001' THEN 2 ELSE mig.s001  END,
		mig.s002 = CASE WHEN sit.SYNC_TASK_FIELD = 's002' THEN 2 ELSE mig.s002  END,
		mig.s003 = CASE WHEN sit.SYNC_TASK_FIELD = 's003' THEN 2 ELSE mig.s003  END,
		mig.s004 = CASE WHEN sit.SYNC_TASK_FIELD = 's004' THEN 2 ELSE mig.s004  END,
		mig.s005 = CASE WHEN sit.SYNC_TASK_FIELD = 's005' THEN 2 ELSE mig.s005  END,
		mig.s006 = CASE WHEN sit.SYNC_TASK_FIELD = 's006' THEN 2 ELSE mig.s006  END,
		mig.s007 = CASE WHEN sit.SYNC_TASK_FIELD = 's007' THEN 2 ELSE mig.s007  END,
		mig.s008 = CASE WHEN sit.SYNC_TASK_FIELD = 's008' THEN 2 ELSE mig.s008  END,
		mig.s009 = CASE WHEN sit.SYNC_TASK_FIELD = 's009' THEN 2 ELSE mig.s009  END,
		mig.s010 = CASE WHEN sit.SYNC_TASK_FIELD = 's010' THEN 2 ELSE mig.s010  END,
		mig.s011 = CASE WHEN sit.SYNC_TASK_FIELD = 's011' THEN 2 ELSE mig.s011  END,
		mig.s012 = CASE WHEN sit.SYNC_TASK_FIELD = 's012' THEN 2 ELSE mig.s012  END,
		mig.s013 = CASE WHEN sit.SYNC_TASK_FIELD = 's013' THEN 2 ELSE mig.s013  END,
		mig.s014 = CASE WHEN sit.SYNC_TASK_FIELD = 's014' THEN 2 ELSE mig.s014  END,
		mig.s015 = CASE WHEN sit.SYNC_TASK_FIELD = 's015' THEN 2 ELSE mig.s015  END,
		mig.s016 = CASE WHEN sit.SYNC_TASK_FIELD = 's016' THEN 2 ELSE mig.s016  END,
		mig.s017 = CASE WHEN sit.SYNC_TASK_FIELD = 's017' THEN 2 ELSE mig.s017  END,
		mig.s018 = CASE WHEN sit.SYNC_TASK_FIELD = 's018' THEN 2 ELSE mig.s018  END,
		mig.s019 = CASE WHEN sit.SYNC_TASK_FIELD = 's019' THEN 2 ELSE mig.s019  END,
		mig.s020 = CASE WHEN sit.SYNC_TASK_FIELD = 's020' THEN 2 ELSE mig.s020  END
	FROM MIG_SYNC_TASKS mig
	INNER JOIN SYNC_TASKS syn
	  ON syn.DSET_KEY = mig.DSET_KEY
	 AND syn.REVISION = mig.REVISION
	INNER JOIN MIG_SITE sit
	  ON syn.SITE_KEY = sit.SITE_KEY 
	WHERE syn.TASK_ID = ''


	-- Update flags for "To be synched - On demand"
	UPDATE mig
	SET mig.s001 = CASE WHEN sit.SYNC_TASK_FIELD = 's001' THEN 3 ELSE mig.s001  END,
		mig.s002 = CASE WHEN sit.SYNC_TASK_FIELD = 's002' THEN 3 ELSE mig.s002  END,
		mig.s003 = CASE WHEN sit.SYNC_TASK_FIELD = 's003' THEN 3 ELSE mig.s003  END,
		mig.s004 = CASE WHEN sit.SYNC_TASK_FIELD = 's004' THEN 3 ELSE mig.s004  END,
		mig.s005 = CASE WHEN sit.SYNC_TASK_FIELD = 's005' THEN 3 ELSE mig.s005  END,
		mig.s006 = CASE WHEN sit.SYNC_TASK_FIELD = 's006' THEN 3 ELSE mig.s006  END,
		mig.s007 = CASE WHEN sit.SYNC_TASK_FIELD = 's007' THEN 3 ELSE mig.s007  END,
		mig.s008 = CASE WHEN sit.SYNC_TASK_FIELD = 's008' THEN 3 ELSE mig.s008  END,
		mig.s009 = CASE WHEN sit.SYNC_TASK_FIELD = 's009' THEN 3 ELSE mig.s009  END,
		mig.s010 = CASE WHEN sit.SYNC_TASK_FIELD = 's010' THEN 3 ELSE mig.s010  END,
		mig.s011 = CASE WHEN sit.SYNC_TASK_FIELD = 's011' THEN 3 ELSE mig.s011  END,
		mig.s012 = CASE WHEN sit.SYNC_TASK_FIELD = 's012' THEN 3 ELSE mig.s012  END,
		mig.s013 = CASE WHEN sit.SYNC_TASK_FIELD = 's013' THEN 3 ELSE mig.s013  END,
		mig.s014 = CASE WHEN sit.SYNC_TASK_FIELD = 's014' THEN 3 ELSE mig.s014  END,
		mig.s015 = CASE WHEN sit.SYNC_TASK_FIELD = 's015' THEN 3 ELSE mig.s015  END,
		mig.s016 = CASE WHEN sit.SYNC_TASK_FIELD = 's016' THEN 3 ELSE mig.s016  END,
		mig.s017 = CASE WHEN sit.SYNC_TASK_FIELD = 's017' THEN 3 ELSE mig.s017  END,
		mig.s018 = CASE WHEN sit.SYNC_TASK_FIELD = 's018' THEN 3 ELSE mig.s018  END,
		mig.s019 = CASE WHEN sit.SYNC_TASK_FIELD = 's019' THEN 3 ELSE mig.s019  END,
		mig.s020 = CASE WHEN sit.SYNC_TASK_FIELD = 's020' THEN 3 ELSE mig.s020  END
	FROM MIG_SYNC_TASKS mig
	INNER JOIN SYNC_TASKS_P syn
	  ON syn.DSET_KEY = mig.DSET_KEY
	 AND syn.REVISION = mig.REVISION
	INNER JOIN MIG_SITE sit
	  ON syn.SITE_KEY = sit.SITE_KEY 
	WHERE syn.TASK_ID = ''


	-- Update flags for "Synch Not Permited"
	UPDATE mig
	SET mig.s001 = CASE WHEN sit.SYNC_TASK_FIELD = 's001' THEN 0 ELSE mig.s001  END,
		mig.s002 = CASE WHEN sit.SYNC_TASK_FIELD = 's002' THEN 0 ELSE mig.s002  END,
		mig.s003 = CASE WHEN sit.SYNC_TASK_FIELD = 's003' THEN 0 ELSE mig.s003  END,
		mig.s004 = CASE WHEN sit.SYNC_TASK_FIELD = 's004' THEN 0 ELSE mig.s004  END,
		mig.s005 = CASE WHEN sit.SYNC_TASK_FIELD = 's005' THEN 0 ELSE mig.s005  END,
		mig.s006 = CASE WHEN sit.SYNC_TASK_FIELD = 's006' THEN 0 ELSE mig.s006  END,
		mig.s007 = CASE WHEN sit.SYNC_TASK_FIELD = 's007' THEN 0 ELSE mig.s007  END,
		mig.s008 = CASE WHEN sit.SYNC_TASK_FIELD = 's008' THEN 0 ELSE mig.s008  END,
		mig.s009 = CASE WHEN sit.SYNC_TASK_FIELD = 's009' THEN 0 ELSE mig.s009  END,
		mig.s010 = CASE WHEN sit.SYNC_TASK_FIELD = 's010' THEN 0 ELSE mig.s010  END,
		mig.s011 = CASE WHEN sit.SYNC_TASK_FIELD = 's011' THEN 0 ELSE mig.s011  END,
		mig.s012 = CASE WHEN sit.SYNC_TASK_FIELD = 's012' THEN 0 ELSE mig.s012  END,
		mig.s013 = CASE WHEN sit.SYNC_TASK_FIELD = 's013' THEN 0 ELSE mig.s013  END,
		mig.s014 = CASE WHEN sit.SYNC_TASK_FIELD = 's014' THEN 0 ELSE mig.s014  END,
		mig.s015 = CASE WHEN sit.SYNC_TASK_FIELD = 's015' THEN 0 ELSE mig.s015  END,
		mig.s016 = CASE WHEN sit.SYNC_TASK_FIELD = 's016' THEN 0 ELSE mig.s016  END,
		mig.s017 = CASE WHEN sit.SYNC_TASK_FIELD = 's017' THEN 0 ELSE mig.s017  END,
		mig.s018 = CASE WHEN sit.SYNC_TASK_FIELD = 's018' THEN 0 ELSE mig.s018  END,
		mig.s019 = CASE WHEN sit.SYNC_TASK_FIELD = 's019' THEN 0 ELSE mig.s019  END,
		mig.s020 = CASE WHEN sit.SYNC_TASK_FIELD = 's020' THEN 0 ELSE mig.s020  END
	FROM MIG_SYNC_TASKS mig
	INNER JOIN SYNC_TASKS_R syn
	  ON syn.DSET_KEY = mig.DSET_KEY
	 AND syn.REVISION = mig.REVISION
	INNER JOIN MIG_SITE sit
	  ON syn.SITE_KEY = sit.SITE_KEY
	WHERE syn.TASK_ID = ''


	-- Set the value of field [LAST_UPDATED_VAULT].

	-- When SOURCE_SITE_KEY is other than the "Default Site".
	UPDATE mig 
		SET 
			mig.LAST_UPDATED_VAULT = syn.SOURCE_SITE_KEY
	FROM MIG_SYNC_TASKS mig
	INNER JOIN 
	(
		SELECT DISTINCT DSET_KEY, REVISION, SOURCE_SITE_KEY 
		FROM SYNC_TASKS_VIEW
		WHERE SOURCE_SITE_KEY <> @MainSite
		 AND TASK_ID = ''
	) syn
	ON syn.DSET_KEY = mig.DSET_KEY
	 AND syn.REVISION = mig.REVISION
	WHERE mig.LAST_UPDATED_VAULT IS NULL

	-- When SOURCE_SITE_KEY is the "Default Site".
	UPDATE mig 
		SET 
			mig.LAST_UPDATED_VAULT = syn.SOURCE_SITE_KEY
	FROM MIG_SYNC_TASKS mig
	INNER JOIN 
	(
		SELECT DISTINCT DSET_KEY, REVISION, SOURCE_SITE_KEY 
		FROM SYNC_TASKS_VIEW
		WHERE SOURCE_SITE_KEY = @MainSite
		 AND TASK_ID = ''
	) syn
	ON syn.DSET_KEY = mig.DSET_KEY
	 AND syn.REVISION = mig.REVISION
	WHERE mig.LAST_UPDATED_VAULT IS NULL

	-- If still LAST_UPDATED_VAULT is empty, means the file is up-to-date in the "Main Site"
	UPDATE mig 
		SET 
			mig.LAST_UPDATED_VAULT = @MainSite
	FROM MIG_SYNC_TASKS mig
	WHERE mig.LAST_UPDATED_VAULT IS NULL

END

-- exec MIG_GENERATE_SYNC_TASKS_Sp
-- SELECT * FROM MIG_SYNC_TASKS


