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
