Product Entitlements Repository #

General #

The Product Entitlements repository is hosted as an Azure SQL as a Service database.

Security #

This repository has been locked down to allow access only from the Azure Function layer within the ProductEntitlements NSG (Network Security Group). No other access is enabled.

In addition to the caller reztrictions, the database can only be interacted with via the use of stored procedures - direct database access is blocked with the default user accounts created.

Entity Relationship Diagram #

The following diagram represnets the basic data storage diagram for the Product Entitlements system.

erd

It is a simple structure based on the requirements defined for the product. Each entity type is based on a primary key (uuid) for each of the elements. The remaining elements are either descriptive in nature or provide metadata context around the objects themselves.

The Product and Company tables are simple singular entity tables - containing one entry for each each of their respective types. The CompanyProducts table (logically, a license table) maps a company to a collection of products that a company has access enabled for.

The actual primary entitlements query peforms a multistep approach to meet its defined requirements.

  1. A unique list of all products is retrieved.
  2. An secondary query retrieves all the license information for a specified company id.
  3. The results, at the database level, are returned as two datatable objects in a signle query. The API then merges these two sets together to retrieve a master list of all products each one that is licensed then has a license file inswerted into its appropriate node.

Entity Descriptions #

Company #

The Companies table consists of three fields:

Field PK Format Description
id X UUID unique identifier for the company
name nvarchar (2000) the name of the company
deleted bit a flag field indicating whether the company is active or not (used to control whether this object is returned as part of a standard query)

The only index on this table is that of the primary key (id).

Products #

The Products table consists of the following fields:

Field PK Format Description
id X UUID unique identifier for the product
name nvarchar (1000) name of the product
description nvarchar (4000) product description
title nvarchar (1000) display title of product
marketingUrl nvarchar (2000) default marketing page
marketingImage nvarchar (2000) default marketing image path or url
deleted bit active flag

The only index on this table is the primary key ondex on id. No other indexes are defined because this is the primary query key used in all queries.

Users #

The Companies table consists of three fields:

Field PK Format Description
id X UUID unique identifier for the user
b2c_id UUID unique identifier for b2c id (SSO)
companyId UUID unique identifier for the users company

The primary index on this table is that of the primary key (id). There is a secondary index on the 2c_id as it is likely that this will be the primary queried value key.

The companyid is a foreign key to the Companies table.

CompanyProducts #

The CompanyProducts table* represents a license that a company has for a particular product. It consists of the following fields:

Field PK Format Description
id X UUID unique identifier for the license
companyid UUID unique identifier for the company
productid UUID unique identifier for the product
linkUrl nvarchar (2000) license-specific link to product
linkImage nvarchar (2000) license-specific image parh or url
startDate datetime datetime license period starts
endDate datetime datetime license period ends
deleted bit active flag

There are additional indexes beyond the primary key (id) for this table. These include an index on comapnyid and one on productid used to optimize query performance when retrieving license details for a specific company.

The companyid is a foreign key to the Companies table.

The productid is a foreign key to the Products table.

UserProducts #

The UserProducts table* represents a a collection of user=specific applications where SSO integration has been enabled. It consists of the following fields:

Field PK Format Description
id X UUID unique identifier for the record
userId UUID unique identifier for the user
productid UUID unique identifier for the product
productName nvarchar (50) name of the product
productUrl nvarchar (200) url link to the user-specific product instance
isDefault datetime flag indicating whether this is the users primary product

There are additional indexes beyond the primary key (id) for this table. These include an index on userid used to optimize query performance when retrieving product details for a specific user.

The userid is a foreign key to the Companies table.

The productid is a foreign key to the Products table.

Database Creation Scripts #

The database creation scripts for the product entitlements service can be downloaded from here.

USE [ProductEntitlements]
GO
/****** Object:  Table [dbo].[Companies]    Script Date: 4/20/2020 1:20:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Companies](
	[id] [uniqueidentifier] NOT NULL,
	[name] [nvarchar](2000) NOT NULL,
	[deleted] [bit] NOT NULL,
	[sor_fk] [nvarchar](50) NULL,
	[sc_fk] [int] NULL,
 CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CompanyProducts]    Script Date: 4/20/2020 1:20:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CompanyProducts](
	[id] [uniqueidentifier] NOT NULL,
	[companyId] [uniqueidentifier] NOT NULL,
	[productId] [uniqueidentifier] NOT NULL,
	[linkUrl] [nvarchar](2000) NOT NULL,
	[linkImage] [nvarchar](2000) NOT NULL,
	[startDate] [datetime] NULL,
	[endDate] [datetime] NULL,
	[deleted] [bit] NOT NULL,
 CONSTRAINT [PK_CompanyProducts] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 4/20/2020 1:20:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[id] [uniqueidentifier] NOT NULL,
	[name] [nvarchar](1000) NOT NULL,
	[description] [nvarchar](4000) NOT NULL,
	[title] [nvarchar](1000) NOT NULL,
	[marketingUrl] [nvarchar](2000) NOT NULL,
	[marketingImage] [nvarchar](2000) NOT NULL,
	[deleted] [bit] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[sysdiagrams]    Script Date: 4/20/2020 1:20:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sysdiagrams](
	[name] [sysname] NOT NULL,
	[principal_id] [int] NOT NULL,
	[diagram_id] [int] IDENTITY(1,1) NOT NULL,
	[version] [int] NULL,
	[definition] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED 
(
	[diagram_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_principal_name] UNIQUE NONCLUSTERED 
(
	[principal_id] ASC,
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserProducts]    Script Date: 4/20/2020 1:20:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserProducts](
	[id] [uniqueidentifier] NOT NULL,
	[userId] [uniqueidentifier] NOT NULL,
	[productId] [uniqueidentifier] NOT NULL,
	[productName] [nvarchar](50) NOT NULL,
	[productUrl] [nvarchar](200) NOT NULL,
	[isDefault] [bit] NULL,
 CONSTRAINT [PK_UserProducts] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[id] [uniqueidentifier] NOT NULL,
	[b2c_id] [uniqueidentifier] NOT NULL,
	[companyId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Index [IX_CompanyProducts]    Script Date: 4/20/2020 1:20:41 PM ******/
CREATE NONCLUSTERED INDEX [IX_CompanyProducts] ON [dbo].[CompanyProducts]
(
	[companyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_UserProducts_UserId]    Script Date: 4/20/2020 1:20:41 PM ******/
CREATE NONCLUSTERED INDEX [IX_UserProducts_UserId] ON [dbo].[UserProducts]
(
	[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_B2C_Id]    Script Date: 4/20/2020 1:20:41 PM ******/
CREATE NONCLUSTERED INDEX [IX_B2C_Id] ON [dbo].[Users]
(
	[b2c_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Users_CompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
CREATE NONCLUSTERED INDEX [IX_Users_CompanyId] ON [dbo].[Users]
(
	[companyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Companies] ADD  CONSTRAINT [DF_Companies_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Companies] ADD  CONSTRAINT [DF_Companies_deleted]  DEFAULT ((0)) FOR [deleted]
GO
ALTER TABLE [dbo].[CompanyProducts] ADD  CONSTRAINT [DF_CompanyProducts_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[CompanyProducts] ADD  CONSTRAINT [DF_CompanyProducts_deleted]  DEFAULT ((0)) FOR [deleted]
GO
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_deleted]  DEFAULT ((0)) FOR [deleted]
GO
ALTER TABLE [dbo].[UserProducts] ADD  CONSTRAINT [DF_UserProducts_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[CompanyProducts]  WITH CHECK ADD  CONSTRAINT [FK_CompanyProducts_Companies] FOREIGN KEY([companyId])
REFERENCES [dbo].[Companies] ([id])
GO
ALTER TABLE [dbo].[CompanyProducts] CHECK CONSTRAINT [FK_CompanyProducts_Companies]
GO
ALTER TABLE [dbo].[CompanyProducts]  WITH CHECK ADD  CONSTRAINT [FK_CompanyProducts_Product] FOREIGN KEY([productId])
REFERENCES [dbo].[Product] ([id])
GO
ALTER TABLE [dbo].[CompanyProducts] CHECK CONSTRAINT [FK_CompanyProducts_Product]
GO
ALTER TABLE [dbo].[UserProducts]  WITH CHECK ADD  CONSTRAINT [FK_UserProducts_Product] FOREIGN KEY([productId])
REFERENCES [dbo].[Product] ([id])
GO
ALTER TABLE [dbo].[UserProducts] CHECK CONSTRAINT [FK_UserProducts_Product]
GO
ALTER TABLE [dbo].[UserProducts]  WITH CHECK ADD  CONSTRAINT [FK_UserProducts_Users] FOREIGN KEY([userId])
REFERENCES [dbo].[Users] ([id])
GO
ALTER TABLE [dbo].[UserProducts] CHECK CONSTRAINT [FK_UserProducts_Users]
GO
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Companies] FOREIGN KEY([companyId])
REFERENCES [dbo].[Companies] ([id])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Companies]
GO
/****** Object:  StoredProcedure [dbo].[sp_alterdiagram]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_alterdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid ARG', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_creatediagram]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_creatediagram]
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N'db_owner');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N'E_INVALIDARG', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_dropdiagram]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_dropdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_helpdiagramdefinition]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_helpdiagramdefinition]
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_helpdiagrams]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_helpdiagrams]
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_renamediagram]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_renamediagram]
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END
	
GO
/****** Object:  StoredProcedure [dbo].[sp_upgraddiagrams]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

	CREATE PROCEDURE [dbo].[sp_upgraddiagrams]
	AS
	BEGIN
		IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N'dbo'),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' 
			return 2;
		end
		return 1;
	END
	
GO
/****** Object:  StoredProcedure [dbo].[usp_Companies_Active_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Companies_Active_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [id],
         [name]
    FROM [dbo].[Companies]
	WHERE [deleted] <> 1
    ORDER BY name
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*) as totalcount 
	FROM dbo.Companies 
	WHERE deleted <> 1);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Companies_Insert]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Companies_Insert]
(
    @deleted bit,
    @name nvarchar( 2000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with INSERT statements.
    SET NOCOUNT ON;
	DECLARE @out_id UNIQUEIDENTIFIER
	SET @out_id = NEWID()

    INSERT INTO [dbo].[Companies]
    (
		 [id],
         [deleted],
         [name]
    )
    VALUES
    (
		 @out_id,
         @deleted,
         @name
    )
	SELECT @out_id as id
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Companies_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Companies_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [id],
         [name]
    FROM [dbo].[Companies]
    ORDER BY name
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.Companies);

	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Companies_SelectById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Companies_SelectById]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [id],
         [name]
    FROM [dbo].[Companies]
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Companies_Update]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Companies_Update]
(
    @id uniqueidentifier,
    @deleted bit,
    @name nvarchar( 2000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[Companies]
    SET
         [deleted]=@deleted,
         [id]=@id,
         [name]=@name
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Company_Delete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Company_Delete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[Companies]
	SET [deleted] = 1
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Company_FullDelete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Company_FullDelete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;
    -- Additonal logic should exist here which prevents orphaned license data for company
    DELETE FROM [dbo].[Companies]
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_Active_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_Active_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
	WHERE [deleted] <> 1
	ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*) as totalcount 
	FROM dbo.CompanyProducts 
	WHERE deleted <> 1);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_Active_SelectByCompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_Active_SelectByCompanyId]
(
    @companyId uniqueidentifier,
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([companyId] = @companyId) AND [deleted] <> 1
    )
    ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.CompanyProducts 
	WHERE deleted <> 1 AND [companyId] = @companyId);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_AllDetails_SelectByCompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_AllDetails_SelectByCompanyId]
(
    @companyId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([companyId] = @companyId) 
    )
    ORDER BY productId

	    SELECT
         p.[deleted],
         p.[description],
         p.[id],
         p.[marketingImage],
         p.[marketingUrl],
         p.[name],
         p.[title]
    FROM [dbo].[Product] p INNER JOIN [dbo].[CompanyProducts] cp ON p.[id] = cp.[productId]
	WHERE cp.[companyId] = @companyId
    ORDER BY p.[id]

END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_Delete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_Delete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[CompanyProducts]
SET [deleted] = 1
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_FullDelete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_FullDelete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;
    -- Additonal logic should exist here which prevents orphaned license data for company
    DELETE FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([id] = @id) 
    )
END 

GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_Insert]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_Insert]
(
    @companyId uniqueidentifier,
    @deleted bit,
    @endDate datetime,
    @linkImage nvarchar( 2000) ,
    @linkUrl nvarchar( 2000) ,
    @productId uniqueidentifier,
    @startDate datetime
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with INSERT statements.
    SET NOCOUNT ON;

DECLARE @out_id UNIQUEIDENTIFIER
SET @out_id = newid()

    INSERT INTO [dbo].[CompanyProducts]
    (
[id],
         [companyId],
         [deleted],
         [endDate],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    )
    VALUES
    (
@out_id,
         @companyId,
         @deleted,
         @endDate,
         @linkImage,
         @linkUrl,
         @productId,
         @startDate
    )

SELECT @out_id as id
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_SelectAll]
(
	@limit INT = 100,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
	ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*) as totalcount 
	FROM dbo.CompanyProducts);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_SelectByCompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_SelectByCompanyId]
(
    @companyId uniqueidentifier,
	@limit INT = 100,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([companyId] = @companyId) 
    )
    ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.CompanyProducts);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_SelectByEntitlementId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_SelectByEntitlementId]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_CompanyProducts_Update]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CompanyProducts_Update]
(
    @deleted bit,
    @endDate datetime,
    @startDate datetime,
    @id uniqueidentifier,
    @companyId uniqueidentifier,
    @productId uniqueidentifier,
    @linkImage nvarchar( 2000) ,
    @linkUrl nvarchar( 2000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[CompanyProducts]
    SET
         [companyId]=@companyId,
         [deleted]=@deleted,
         [endDate]=@endDate,
         [linkImage]=@linkImage,
         [linkUrl]=@linkUrl,
         [productId]=@productId,
         [startDate]=@startDate
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Entitlements_Active_SelectByCompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Entitlements_Active_SelectByCompanyId]
(
    @companyId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	SELECT
		[id],
		[name],
		[deleted]
	FROM [dbo].[Companies]
	WHERE [id] = @companyId

    SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
	WHERE [deleted] <> 1
    ORDER BY id

    SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([companyId] = @companyId) AND ([deleted] <> 1)
    )
    ORDER BY productId

	


END 

GO
/****** Object:  StoredProcedure [dbo].[usp_Entitlements_SelectByCompanyId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Entitlements_SelectByCompanyId]
(
    @companyId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	SELECT
		[id],
		[name],
		[deleted]
	FROM [dbo].[Companies]
	WHERE [id] = @companyId

	SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
    ORDER BY id

	SELECT
         [companyId],
         [deleted],
         [endDate],
         [id],
         [linkImage],
         [linkUrl],
         [productId],
         [startDate]
    FROM [dbo].[CompanyProducts]
    WHERE
    (
         ([companyId] = @companyId) 
    )
    ORDER BY productId
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Product_Delete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Product_Delete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[Product]
	SET [deleted] = 1
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Product_FullDelete]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Product_FullDelete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    DELETE FROM [dbo].[Product]
	WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Product_Insert]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Product_Insert]
(
    @deleted bit,
    @description nvarchar(MAX) ,
    @marketingImage nvarchar( 2000) ,
    @marketingUrl nvarchar( 2000) ,
    @name nvarchar( 1000) ,
    @title nvarchar( 1000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with INSERT statements.
    SET NOCOUNT ON;

DECLARE @out_id UNIQUEIDENTIFIER
SET @out_id = newid()

    INSERT INTO [dbo].[Product]
    (
[id],
         [deleted],
         [description],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    )
    VALUES
    (
		 @out_id,
         @deleted,
         @description,
         @marketingImage,
         @marketingUrl,
         @name,
         @title
    )

SELECT @out_id as id
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Product_SelectByName]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Product_SelectByName]
(
    @name nvarchar( 1000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
    WHERE
    (
         ([name] = @name) 
    )
    ORDER BY name

END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Product_Update]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Product_Update]
(
    @deleted bit,
    @description nvarchar(MAX) ,
    @title nvarchar( 1000) ,
    @id uniqueidentifier,
    @name nvarchar( 1000) ,
    @marketingImage nvarchar( 2000) ,
    @marketingUrl nvarchar( 2000) 
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[Product]
    SET
         [deleted]=@deleted,
         [description]=@description,
         [marketingImage]=@marketingImage,
         [marketingUrl]=@marketingUrl,
         [name]=@name,
         [title]=@title
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Products_Active_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Products_Active_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
	WHERE [deleted] <> 1
    ORDER BY name
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.Product 
	WHERE deleted <> 1);
	RETURN @TOTALROWS

END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Products_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Products_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
    ORDER BY name
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.Product);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_Products_SelectById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Products_SelectById]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [deleted],
         [description],
         [id],
         [marketingImage],
         [marketingUrl],
         [name],
         [title]
    FROM [dbo].[Product]
    WHERE
    (
         ([id] = @id) 
    )
    ORDER BY name

END 
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_DeleteById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_DeleteById]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    DELETE
    FROM [dbo].[UserProducts]
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_Insert]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_Insert]
(
    @isDefault bit,
    @productId uniqueidentifier,
    @productName nvarchar( 50) ,
    @productUrl nvarchar( 200) ,
    @userId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with INSERT statements.
    SET NOCOUNT ON;
	DECLARE @out_id UNIQUEIDENTIFIER
	SET @out_id = newid()
	-- TODO: this probably should have a constraint that only allows entries where the users company actually is licensed for a product

    INSERT INTO [dbo].[UserProducts]
    (
		 [id],
         [isDefault],
         [productId],
         [productName],
         [productUrl],
         [userId]
    )
    VALUES
    (
		 @out_id,
         @isDefault,
         @productId,
         @productName,
         @productUrl,
         @userId
    )

	SELECT @out_id as id
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_SelectAll]
(
	@limit INT = 100,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [id],
         [isDefault],
         [productId],
         [productName],
         [productUrl],
         [userId]
    FROM [dbo].[UserProducts]
    ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*) as totalcount 
	FROM dbo.UserProducts);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[usp_UserProducts_SelectByB2CId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      JShaughnessy
-- Create Date: 2020-04-14
-- Description: selects all products for a specific user based on the supplied B2C UserId
-- =============================================
CREATE PROCEDURE [dbo].[usp_UserProducts_SelectByB2CId]
(
    @b2c_id uniqueidentifier	
)
AS
BEGIN
    SET NOCOUNT ON

	SELECT
		u.id,
		u.b2c_id,
		u.companyId,
		c.name as companyName,
		c.sc_fk as fk_companyId
	FROM 
		dbo.Users AS u INNER JOIN
        dbo.Companies AS c ON u.companyId = c.id
	WHERE 
		u.b2c_id = @b2c_id


    SELECT      
		up.id,
		up.userId,  
		up.productName, 
		up.productUrl, 
		up.productId, 
		up.isDefault
	FROM            
		dbo.UserProducts AS up INNER JOIN
        dbo.Users AS u ON up.userId = u.id
	WHERE 
		u.b2c_id = @b2c_id
END
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_SelectById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_SelectById]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [id],
         [isDefault],
         [productId],
         [productName],
         [productUrl],
         [userId]
    FROM [dbo].[UserProducts]
    WHERE
    (
         ([id] = @id) 
    )
    ORDER BY productId

END 
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_SelectByUserId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_SelectByUserId]
(
    @userId uniqueidentifier,
	@limit INT = 100,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [id],
         [isDefault],
         [productId],
         [productName],
         [productUrl],
         [userId]
    FROM [dbo].[UserProducts]
    WHERE
    (
         ([userId] = @userId) 
    )
	ORDER BY productId
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.UserProducts);
	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_UserProducts_UpdateById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_UserProducts_UpdateById]
(
    @productName nvarchar( 50) ,
    @id uniqueidentifier,
    @isDefault bit,
    @productUrl nvarchar( 200) ,
    @productId uniqueidentifier,
    @userId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[UserProducts]
    SET
         [id]=@id,
         [isDefault]=@isDefault,
         [productId]=@productId,
         [productName]=@productName,
         [productUrl]=@productUrl,
         [userId]=@userId
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_Delete_ByB2CId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_Delete_ByB2CId]
(
    @b2c_id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    DELETE
    FROM [dbo].[Users]
    WHERE
    (
         ([b2c_id] = @b2c_id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_Delete_ById]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_Delete_ById]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with DELETE statements.
    SET NOCOUNT ON;

    DELETE
    FROM [dbo].[Users]
    WHERE
    (
         ([id] = @id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_Insert]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_Insert]
(
    @b2c_id uniqueidentifier,
    @companyId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with INSERT statements.
    SET NOCOUNT ON;
	DECLARE @out_id UNIQUEIDENTIFIER
	SET @out_id = NEWID()

    INSERT INTO [dbo].[Users]
    (
		 [id],
         [b2c_id],
         [companyId]
    )
    VALUES
    (
		 @out_id,
         @b2c_id,
         @companyId
    )
	SELECT @out_id as id
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_Select_ByB2CId]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_Select_ByB2CId]
(
    @b2c_id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [b2c_id],
         [companyId],
         [id]
    FROM [dbo].[Users]
    WHERE
    (
         ([b2c_id] = @b2c_id) 
    )
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_SelectAll]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_SelectAll]
(
	@limit INT = 10,
	@skip INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
         [b2c_id],
         [companyId],
         [id]
    FROM [dbo].[Users]
	ORDER BY id
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = (SELECT COUNT(*)
	FROM dbo.Users);

	RETURN @TOTALROWS
END 
GO
/****** Object:  StoredProcedure [dbo].[USP_Users_Update]    Script Date: 4/20/2020 1:20:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_Users_Update]
    @id uniqueidentifier,
    @b2c_id uniqueidentifier,
    @companyId uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;

    UPDATE [dbo].[Users]
    SET
         [b2c_id]=@b2c_id,
         [companyId]=@companyId
	WHERE
         [id]=@id
END 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'company table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Companies'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'company products collection table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CompanyProducts'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'product table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Product'
GO
EXEC sys.sp_addextendedproperty @name=N'microsoft_database_tools_support', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sysdiagrams'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user-specific products' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserProducts'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'users table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Users'
GO