/*
	This script renames the "Custom Count 3" to "PDC Items" for a list of stores and activates that
	count type. It then add a list of items to the new PDC Items count for those same stores.
	
	INSTRUCTIONS:
		1) Manually update the list of stores in this script
		2) Manually update the list of items in this script 
		3) Run the script to test it and confirm the returned results are correct 
		4) Replace the "ROLLBACK" at the end with "COMMIT" 
		5) Run the script again to make the changes to the db
*/

BEGIN TRAN

-- Update this list of StoreNumbers
DECLARE @StoreNumbers TABLE (storeNumber varchar(15)); INSERT @StoreNumbers(storeNumber) VALUES
	('Store1'),('Store2'),('Store3'),('Store4'),('Store5'),('Store6'),('Store7')  -- and so on...

-- Update this list of ItemCodes
DECLARE @ItemCodes TABLE (itemCode varchar(15)); INSERT @ItemCodes(itemCode) VALUES
	('ItemCode1'),('ItemCode2'),('ItemCode3'),('ItemCode4'),('ItemCode5'),('ItemCode6') -- and so on...

-- Convert StoreNumbers to EntityIds
DECLARE @EntityIds TABLE (entityId INT); INSERT @EntityIds(entityId) 
	select distinct EntityID from tbentity where StoreNumber in (select StoreNumber from @StoreNumbers)

-- Convert Items Codes to ItemIds
DECLARE @ItemIds TABLE (itemId INT); INSERT @ItemIds(itemId)
	select distinct ItemID from tbItem where ItemCode in (select ItemCode from @ItemCodes)

-- Some values we'll need later
DECLARE @Custom3CountType INT = 7
DECLARE @CustomCountName VARCHAR(25) = 'PDC Items'
DECLARE @CountFrequency INT = (select POWER(2, @Custom3CountType))

-- Rename, activate any existing CustomCount3 counts
UPDATE tbCustomCountType 
	SET CustomName = @CustomCountName, IsActive = 1
WHERE 
	EntityId in (select entityId from @EntityIds) AND
	CountTypeEnum = @Custom3CountType

-- Insert, activate any missing CustomCount3 counts
INSERT INTO tbCustomCountType (EntityId, CountTypeEnum, CustomName, IsActive)
	select EntityId, @Custom3CountType, @CustomCountName, 1
	from @EntityIds e where not exists (
		select EntityId from tbCustomCountType 
		where CountTypeEnum = @Custom3CountType 
		and entityId = e.entityId)

-- Set the Count Frequency for these entity items 
UPDATE tbEntityItem SET CountTypeSpecific = (CountTypeSpecific + @CountFrequency)
WHERE EntityID in (select entityId from @EntityIds) and 
	ItemID in (select ItemID from @ItemIds)and 
	(CountTypeSpecific & @CountFrequency <> @CountFrequency)	

-- Remove any other items from the PDC Count for these stores
UPDATE tbEntityItem SET CountTypeSpecific = (CountTypeSpecific - @CountFrequency)
WHERE EntityID in (select entityId from @EntityIds) and 
	ItemID not in (select ItemID from @ItemIds)and 
	(CountTypeSpecific & @CountFrequency = @CountFrequency)	

-- Make sure the counts were created correctly (should be one row per entity)
select * from tbCustomCountType 
where 
	EntityId in (select entityId from @EntityIds) AND
	CountTypeEnum = @Custom3CountType

-- Make sure the items' count frequency was set correctly (should be one row per entity item)
select EntityId, ItemId, CountTypeDefault, CountTypeSpecific
from tbEntityItem where 
	EntityID in (select entityId from @EntityIds) and 
	ItemID in (select ItemID from @ItemIds) and 
	(CountTypeSpecific & @CountFrequency = @CountFrequency)	

-- Make sure there are no other items still using the PDC Item frequenct (this should be empty)
select EntityId, ItemId, CountTypeDefault, CountTypeSpecific
from tbEntityItem where 
	EntityID in (select entityId from @EntityIds) and 
	ItemID not in (select ItemID from @ItemIds) and 
	(CountTypeSpecific & @CountFrequency = @CountFrequency)	

ROLLBACK
--COMMIT