if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE [dbo].[Employee]
	END
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE [dbo].[Code]
	END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE [dbo].[CodeCategory]
	END
GO

CREATE TABLE [dbo].[CodeCategory] (

	CodeCategoryUUID [uniqueidentifier] not null default NEWSEQUENTIALID() , 
	CodeCategoryName varchar(64) not null

) 
GO

ALTER TABLE dbo.CodeCategory ADD CONSTRAINT PK_CodeCategory_CodeCategoryUUID
PRIMARY KEY CLUSTERED (CodeCategoryUUID)
GO

ALTER TABLE dbo.CodeCategory ADD CONSTRAINT CK_CodeCategory_CodeCategoryName_UNIQUE 
UNIQUE (CodeCategoryName)
GO




 


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE [dbo].[Code]
	END
GO

CREATE TABLE [dbo].[Code] (

	[CodeUUID] [uniqueidentifier] not null default NEWSEQUENTIALID() , 

	CodeKey smallint not null , 

	[CodeCategoryUUID] [uniqueidentifier] ,

	CodeName varchar(64) not null , 
	CodeDescription varchar(128) not null

) 


GO

ALTER TABLE [dbo].[Code]
	ADD CONSTRAINT PK_Code_CodeUUID
	PRIMARY KEY CLUSTERED (CodeUUID)
	 
GO

ALTER TABLE dbo.Code
ADD CONSTRAINT CodeToCodeCategoryFK
	FOREIGN KEY ( CodeCategoryUUID )
		REFERENCES dbo.CodeCategory ( CodeCategoryUUID  )
GO

ALTER TABLE dbo.Code
ADD CONSTRAINT Code_Category_And_CodeName_UNIQUE UNIQUE (CodeCategoryUUID , CodeName)
GO

ALTER TABLE dbo.Code
ADD CONSTRAINT Code_CodeKey_UNIQUE UNIQUE (CodeKey)
GO















SET NOCOUNT ON

DECLARE @CodeCategoryUpdate table ( CodeCategoryUUID uniqueidentifier ,  CodeCategoryName varchar(256) )


INSERT INTO @CodeCategoryUpdate ( CodeCategoryUUID ,  CodeCategoryName ) values ( 'CC000000-0000-0000-0000-000000000101' , 'OrderStatus' ) 
INSERT INTO @CodeCategoryUpdate ( CodeCategoryUUID ,  CodeCategoryName ) values ( 'CC000000-0000-0000-0000-000000000102' , 'Country' ) 
INSERT INTO @CodeCategoryUpdate ( CodeCategoryUUID ,  CodeCategoryName ) values ( 'CC000000-0000-0000-0000-000000000103' , 'Priority' ) 




/*
print '/@CodeCategoryUpdate/'
select * from @CodeCategoryUpdate
print ''
*/

INSERT INTO dbo.CodeCategory
	Select CodeCategoryUUID ,  CodeCategoryName From @CodeCategoryUpdate vt 
		Where NOT EXISTS ( Select null from dbo.CodeCategory innerCC where innerCC.CodeCategoryUUID = vt.CodeCategoryUUID  )

Update dbo.CodeCategory
	Set CodeCategoryName = vt.CodeCategoryName
From dbo.CodeCategory cc, @CodeCategoryUpdate vt
where cc.CodeCategoryUUID = vt.CodeCategoryUUID

/*
print '/dbo.CodeCategory/'
select * from dbo.CodeCategory
print ''
*/

-----------------

DECLARE @CodeUpdate table ( CodeUUID uniqueidentifier, CodeKey int , CodeCategoryUUID uniqueidentifier ,  CodeName varchar(256) , CodeDescription varchar(256) )


INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000001001' ,  1001 , 'CC000000-0000-0000-0000-000000000101' , 'Created' , 'Order has been created' ) 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000001002' ,  1002 , 'CC000000-0000-0000-0000-000000000101' , 'Filled' , 'Order has been filled' ) 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000001003' ,  1003 , 'CC000000-0000-0000-0000-000000000101' , 'Shipped' , 'Order has been shipping' ) 


INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000002001' ,  2001 , 'CC000000-0000-0000-0000-000000000102' , 'Cananda' , '' ) 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000002002' ,  2002 , 'CC000000-0000-0000-0000-000000000102' , 'United States' , '') 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000002003' ,  2003 , 'CC000000-0000-0000-0000-000000000102' , 'Mexico' , '') 



INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000003001' ,  3001 , 'CC000000-0000-0000-0000-000000000103' , 'High' , 'Really important' ) 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000003002' ,  3002 , 'CC000000-0000-0000-0000-000000000103' , 'Medium', 'Kinda important'  ) 
INSERT INTO @CodeUpdate ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription ) 
	values ( 'CD000000-0000-0000-0000-000000003003' ,  3003 , 'CC000000-0000-0000-0000-000000000103' , 'Low', 'Not important'  ) 







/*
print '/@CodeUpdate'
Select * From @CodeUpdate vt 
print ''
*/


INSERT INTO dbo.Code ( CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription )
	Select CodeUUID , CodeKey, CodeCategoryUUID, CodeName , CodeDescription 
From @CodeUpdate vt 
		Where NOT EXISTS ( Select null from dbo.Code innerCC where innerCC.CodeUUID = vt.CodeUUID  )





Update dbo.Code
	Set CodeName = vt.CodeName , 
		CodeKey = vt.CodeKey , 
		CodeCategoryUUID = vt.CodeCategoryUUID , 
		CodeDescription = vt.CodeDescription
From dbo.Code cc, @CodeUpdate vt
where cc.CodeUUID = vt.CodeUUID




DELETE FROM dbo.Code where NOT EXISTS
(
Select null from dbo.Code cc, @CodeUpdate vt where cc.CodeUUID = vt.CodeUUID
)



/*

select CodeUUID , CodeKey, cc.CodeCategoryName, CodeName from dbo.Code c join dbo.CodeCategory cc on c.CodeCategoryUUID = cc.CodeCategoryUUID
order by cc.CodeCategoryName , c.CodeKey


*/


  



/*
Create a stubbed version if it does not exist... if it exists, this could shouldn't run, thus not violating permissions
*/

if NOT exists (select *  from sysobjects 
	where id = object_id('dbo.udfIsValidCodePerCodeCategory') and sysstat & 0xf = 0)
	BEGIN
		print 'Creating the stubbed version of udfIsValidCodePerCodeCategory'
		EXEC ( 'CREATE FUNCTION dbo.udfIsValidCodePerCodeCategory (@i as uniqueidentifier , @j as int ) RETURNS bit AS BEGIN RETURN 0 END')
	END
GO




ALTER FUNCTION dbo.udfIsValidCodePerCodeCategory (@CodeCategoryUUID uniqueidentifier , @CodeKey int  )
	RETURNS bit AS
	BEGIN
		declare @exists int
		
		select @exists = 0
		
		if exists 
			( 
				select top 1 null
				from	
					dbo.Code WITH (NOLOCK) 
				where 
					CodeCategoryUUID = @CodeCategoryUUID
					and
					CodeKey = @CodeKey
			)
			begin
				select @exists = 1
			end 	
				
		return @exists
	END



GO





if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		DROP TABLE [dbo].[Employee]
	END
GO

CREATE TABLE [dbo].[Employee] 
(
	[EmployeeUUID] [uniqueidentifier] not null default NEWSEQUENTIALID() , 

	LastName varchar(24) not null , 
	FirstName varchar(24) not null , 

	BirthCountryCodeKey smallint , 

)

GO

ALTER TABLE dbo.Employee ADD CONSTRAINT PK_Employee_MQIUUID
PRIMARY KEY CLUSTERED ( EmployeeUUID )
GO



ALTER TABLE dbo.Employee ADD CONSTRAINT FK_Employee_BirthCountryCodeKey
FOREIGN KEY (BirthCountryCodeKey) REFERENCES dbo.Code (CodeKey)
GO




--Here is the all important check constraint using the UDF
ALTER TABLE dbo.Employee
ADD CONSTRAINT [CK_Employee_BirthCountryCodeKey] CHECK ([dbo].[udfIsValidCodePerCodeCategory]('CC000000-0000-0000-0000-000000000102' , [BirthCountryCodeKey] ) != 0)
GO



Insert into dbo.Employee
 ( 	LastName , FirstName , BirthCountryCodeKey ) 
	values ( 'Smith' , 'John' , 2001 )

Insert into dbo.Employee
 ( 	LastName , FirstName , BirthCountryCodeKey ) 
	values ( 'Jones' , 'Mary' , 2002 )

Insert into dbo.Employee
 ( 	LastName , FirstName , BirthCountryCodeKey ) 
	values ( 'Lopez' , 'Rob' , 2003 )

--FAILURE BECAUSE OF CHECK CONSTRAINT
Insert into dbo.Employee
 ( 	LastName , FirstName , BirthCountryCodeKey ) 
	values ( 'Jacques' , 'Cousteau' , 1001 )


select * from dbo.Employee