SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects WHERE id = object_id(N'[Testing].[MxOrderGuide_TestSetup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [Testing].[MxOrderGuide_TestSetup]
GO

CREATE PROCEDURE [Testing].[MxOrderGuide_TestSetup]
	@EntityId BIGINT,
	@VendorId1 BIGINT,
	@VendorId2 BIGINT
AS
BEGIN

	-- Make sure VendorEntity relationships exist 
	IF NOT EXISTS (select * from tbVendorEntity where EntityId = @EntityId and VendorId = @VendorId1)
		INSERT INTO tbVendorEntity VALUES(
			@VendorId1, @EntityId, 0, NULL, 0, 1, 0, 0, NULL, 0, 0, 0, 'VendorCode', NULL, 
			0, 0, 1, NULL, NULL, 0, NULL, 0, NULL, 0, '04:00:00', NULL)
			

	IF NOT EXISTS (select * from tbVendorEntity where EntityId = @EntityId and VendorId = @VendorId2)
		INSERT INTO tbVendorEntity VALUES(
			@VendorId2, @EntityId, 0, NULL, 0, 1, 0, 0, NULL, 0, 0, 0, 'VendorCode', NULL, 
			0, 0, 1, NULL, NULL, 0, NULL, 0, NULL, 0, '04:00:00', NULL)

	-- Make sure Distributor Name Zones exist and contain stores
	DECLARE @DNZoneTypeId BIGINT = (select ZoneTypeId from tbZoneType where ZoneType like 'Location - DistributorName')
	IF @DNZoneTypeId IS NULL RAISERROR('Could not find Location - DistributorName zone type', 18, 1)

	DECLARE @DNZoneId1 BIGINT = (select zoneId from tbZone where zone like 'MBM%' and zoneTypeId = @DNZoneTypeId)
	DECLARE @DNZoneId2 BIGINT = (select zoneId from tbZone where zone like 'QCD%' and zoneTypeId = @DNZoneTypeId)
	
	IF @DNZoneId1 IS NULL RAISERROR('Could not find Distributor Name Zone 1.', 18, 1)
	IF @DNZoneId2 IS NULL RAISERROR('Could not find Distributor Name Zone 2.', 18, 1)

	IF NOT EXISTS (select * from tbEntityZone where zoneId = @DNZoneId1 and entityId = @EntityId)
		INSERT INTO tbEntityZone VALUES (@EntityId, @DNZoneId1)

	IF NOT EXISTS (select * from tbEntityZone where zoneId = @DNZoneId2 and entityId = @EntityId)
		INSERT INTO tbEntityZone VALUES (@EntityId, @DNZoneId2)

	-- Create or Find an Operator Team zone
	DECLARE @OPZoneTypeId BIGINT = (select zoneTypeId from tbZoneType where ZoneType like 'Location - OperatorTeamName')	
	IF @OPZoneTypeId IS NULL RAISERROR('Could not find Location - OperatorTeamName zone type.', 18, 1)
	
	-- Look for existing Operator Team zone with this store
	DECLARE @OPZoneId BIGINT = (select top 1 z.ZoneId from tbEntityZone ez 
								inner join tbZone z on ez.ZoneId = z.ZoneId 
								where entityId = @EntityId and zonetypeid = @OpZoneTypeId)
	
	-- If there isn't an existing Operator Team Zone with this store, create one	
	DECLARE @OPZoneName VARCHAR(30) = 'OpTeam - Rules Test'
	IF @OPZoneId IS NULL
	BEGIN
		INSERT INTO tbZone VALUES (@OPZoneName, @OPZoneTypeId, NULL)
		SET @OpZoneId = @@Identity
		INSERT INTO tbEntityZone VALUES (@EntityId, @OPZoneId)
	END
	
	-- Put test store into new Operator Team zone
	IF NOT EXISTS (select * from tbEntityZone where entityId = @EntityId and ZoneId = @OPZoneId)
		INSERT INTO tbEntityZone VALUES (@EntityId, @OPZoneId)

	-- Create Distribution Center zones
	DECLARE @DCZoneTypeId BIGINT = (select ZoneTypeId from tbZoneType where ZoneType like 'Location - DistributionCenter')
	IF @DCZoneTypeId IS NULL RAISERROR('Could not find Location - DistributionCenter zone type.', 18, 1)

	DECLARE @DCZoneName1 VARCHAR(30) = 'MBM - Rules Test'
	DECLARE @DCZoneName2 VARCHAR(30) = 'QCD - Rules Test'

	IF NOT EXISTS (select * from tbZone where Zone like @DCZoneName1)
		INSERT INTO tbZone VALUES (@DCZoneName1, @DCZoneTypeId, NULL)

	IF NOT EXISTS (select * from tbZone where Zone like @DCZoneName2)
		INSERT INTO tbZone VALUES (@DCZoneName2, @DCZoneTypeId, NULL)

	DECLARE @DCZoneId1 BIGINT = (select zoneId from tbZone where zone like @DCZoneName1)
	DECLARE @DCZoneID2 BIGINT = (select zoneId from tbZone where zone like @DCZoneName2)
	
	IF @DCZoneID1 IS NULL RAISERROR('Error creating Distribution Center 1.', 18, 1)
	IF @DCZoneID2 IS NULL RAISERROR('Error creating Distribution Center 2.', 18, 1)
	
	-- Associated new DC Zones with their corresponding DCs
	IF NOT EXISTS(select * from tbVendorZone where VendorId = @VendorId1 and ZoneId = @DCZoneId1)
		INSERT INTO tbVendorZone VALUES (@VendorId1, @DCZoneId1, GetDate(), '', 1)
	
	IF NOT EXISTS(select * from tbVendorZone where VendorId = @VendorId2 and ZoneId = @DCZoneId2)
		INSERT INTO tbVendorZone VALUES (@VendorId2, @DCZoneId2, GetDate(), '', 1)
	
	-- Put test store into new Distribution Center zones
	IF NOT EXISTS (select * from tbEntityZone where entityId = @EntityId and ZoneId = @DCZoneId1)
		INSERT INTO tbEntityZone VALUES (@EntityId, @DCZoneId1)
	
	IF NOT EXISTS (select * from tbEntityZone where entityId = @EntityId and ZoneId = @DCZoneId2)
		INSERT INTO tbEntityZone VALUES (@EntityId, @DCZoneId2)

END
GO