IF OBJECT_ID('dbo.[ItemProperty]', 'U') IS NOT NULL
	DROP TABLE [ItemProperty]

IF OBJECT_ID('dbo.[SubCategoryProperty]', 'U') IS NOT NULL
	DROP TABLE [SubCategoryProperty]

IF OBJECT_ID('dbo.[CategoryProperty]', 'U') IS NOT NULL
	DROP TABLE [CategoryProperty]

IF OBJECT_ID('dbo.[PropertyValue]', 'U') IS NOT NULL
	DROP TABLE [PropertyValue]

IF OBJECT_ID('ItemComponent', 'U') IS NOT NULL
	DROP TABLE [ItemComponent]

IF OBJECT_ID('dbo.[Item]', 'U') IS NOT NULL
	DROP TABLE [Item]

IF OBJECT_ID('dbo.[SubCategory]', 'U') IS NOT NULL
	DROP TABLE [SubCategory]

IF OBJECT_ID('dbo.[Category]', 'U') IS NOT NULL
	DROP TABLE [Category]

IF OBJECT_ID('dbo.[Property]', 'U') IS NOT NULL
	DROP TABLE [Property]
	
IF OBJECT_ID('dbo.[PropertyType]', 'U') IS NOT NULL
	DROP TABLE [PropertyType]
	
CREATE TABLE [PropertyType]
(
	[Property Type ID] INT NOT NULL IDENTITY(1, 1),
	[Property Type] VARCHAR(32) NOT NULL UNIQUE,
	
	PRIMARY KEY([Property Type ID])
)

INSERT INTO [PropertyType] ([Property Type]) VALUES ('Text')
INSERT INTO [PropertyType] ([Property Type]) VALUES ('DateTime')
INSERT INTO [PropertyType] ([Property Type]) VALUES ('Money')
INSERT INTO [PropertyType] ([Property Type]) VALUES ('Integer')

CREATE TABLE [Category]
(
    [Category ID] INT NOT NULL IDENTITY(1, 1),
    [Category Name] NVARCHAR(512) NOT NULL UNIQUE,

    PRIMARY KEY([Category ID])
)

INSERT INTO [Category] ([Category Name]) VALUES ('Books')
INSERT INTO [Category] ([Category Name]) VALUES ('Electronics')
INSERT INTO [Category] ([Category Name]) VALUES ('Appliances')
INSERT INTO [Category] ([Category Name]) VALUES ('Kitchenware')

CREATE TABLE [SubCategory]
(
    [Sub Category ID] INT NOT NULL IDENTITY(1, 1),
    [Sub Category Name] NVARCHAR(512) NOT NULL UNIQUE,
    [Category ID] INT NOT NULL,

    FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
    PRIMARY KEY([Sub Category ID])
)

INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('PCIe Cards', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Laptops', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Desktops', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('CPUs', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Hard Drives', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Switches', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Routers', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('CPU Coolers', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('USB Devices', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Oscilloscopes', 2)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Heaters', 3)
INSERT INTO [SubCategory]([Sub Category Name], [Category ID]) VALUES ('Eletric Kettles', 4)

CREATE TABLE [Property]
(
    [Property ID] INT NOT NULL IDENTITY(1, 1),
    [Name] NVARCHAR(128) NOT NULL,
	[Property Type ID] INT NOT NULL,

	FOREIGN KEY([Property Type ID]) REFERENCES [PropertyType]([Property Type ID]),
    PRIMARY KEY([Property ID])
)

INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Lanewidth', 1)
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Battery Watt Hours', 1)
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('ASIN', 1)
INSERT INTO [Property] ([Name], [Property Type ID]) VALUES ('Capacity (GiB)', 4)

CREATE TABLE [SubCategoryProperty]
(
    [Sub Category Property ID] INT NOT NULL IDENTITY(1, 1),
    [Sub Category ID] INT NOT NULL,
    [Property ID] INT NOT NULL,

    FOREIGN KEY([Sub Category ID]) REFERENCES [SubCategory]([Sub Category ID]),
    FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
    PRIMARY KEY([Sub Category Property ID])
)

INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(1, 1)
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(2, 2)
INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES(5, 4)

CREATE TABLE [CategoryProperty]
(
	[Category Property ID] INT NOT NULL IDENTITY(1, 1),
    [Category ID] INT NOT NULL,
    [Property ID] INT NOT NULL,

    FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
    FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
    PRIMARY KEY([Category Property ID])
)

INSERT INTO [CategoryProperty] ([Category ID], [Property ID]) VALUES (1, 3)

CREATE TABLE [Item]
(
    [Item ID] INT NOT NULL IDENTITY(1, 1),
    [Name] NVARCHAR(2048) NOT NULL,
    [Value] MONEY DEFAULT 0,
    [Bought For] MONEY DEFAULT 0,
    [Date Aquired] DATETIME,
    [Comment] NVARCHAR(3072),
    [Note] NVARCHAR(3072),
    [Sold For] MONEY DEFAULT 0,
    [Sold] BIT DEFAULT 0,
    [Date Sold] DATETIME,
    [Sold To] NVARCHAR(1024), -- Maybe could point to a table of comapies, or contacts
    [Category ID] INT NOT NULL,
    [Sub Category ID] INT DEFAULT 0,

    FOREIGN KEY([Category ID]) REFERENCES [Category]([Category ID]),
    FOREIGN KEY([Sub Category ID]) REFERENCES [SubCategory]([Sub Category ID]),
    PRIMARY KEY([Item ID])
)
-- Allows grouping items together, i.e. Alienware Desktop -> CPU, HDDs / SSDs, Motherboard, etc.
CREATE TABLE [ItemComponent]
(
	[Item Component ID] INT NOT NULL IDENTITY(1, 1),
	[Item ID] INT NOT NULL,
	[Component Item ID] INT NOT NULL,

	FOREIGN KEY([Item ID]) REFERENCES [Item]([Item ID]),
	FOREIGN KEY([Component Item ID]) REFERENCES [Item]([Item ID]),
	PRIMARY KEY([Item Component ID])
)

CREATE TABLE [PropertyValue]
(
	[Property Value ID] INT NOT NULL IDENTITY(1, 1),
	[Item ID] INT NOT NULL,
	[Property ID] INT NOT NULL,
	[Value] NVARCHAR(2048) NOT NULL,

    FOREIGN KEY([Item ID]) REFERENCES [Item]([Item ID]),
    FOREIGN KEY([Property ID]) REFERENCES [Property]([Property ID]),
	PRIMARY KEY([Property Value ID])
)
GO
/****** Object:  UserDefinedFunction [dbo].[CategoryExists]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   FUNCTION [dbo].[CategoryExists]
(
	@Category NVARCHAR(512)
)
RETURNS BIT
AS
BEGIN
	
	DECLARE @Exists BIT = 0

	IF EXISTS ( SELECT 1 FROM [Category] WHERE [Category Name] = @Category )
	BEGIN
		SET @Exists = 1;
	END

	RETURN @Exists;
END
GO
/****** Object:  UserDefinedFunction [dbo].[PropertyExists]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   FUNCTION [dbo].[PropertyExists]
(
	@PropertyName NVARCHAR(512)
)
RETURNS BIT
AS
BEGIN
	
	DECLARE @Exists BIT = 0

	IF EXISTS ( SELECT 1 FROM [Property] WHERE [Name] = @PropertyName )
	BEGIN
		SET @Exists = 1;
	END

	RETURN @Exists;
END
GO
/****** Object:  UserDefinedFunction [dbo].[SubCategoryExists]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   FUNCTION [dbo].[SubCategoryExists]
(
	@SubCategory NVARCHAR(512)
)
RETURNS BIT
AS
BEGIN
	
	DECLARE @Exists BIT = 0

	IF EXISTS ( SELECT 1 FROM [SubCategory] WHERE [Sub Category Name] = @SubCategory )
	BEGIN
		SET @Exists = 1;
	END

	RETURN @Exists;
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetCategoryProperties]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetCategoryProperties]
(	
	@CategoryID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT [Property].[Name], [Property].[Property ID], [Category Property ID], [PropertyType].[Property Type ID], [PropertyType].[Property Type]
	FROM [CategoryProperty]
	INNER JOIN [Property] ON
		[Property].[Property ID] = [CategoryProperty].[Property ID]
	INNER JOIN [PropertyType] ON
		[Property].[Property Type ID] = [PropertyType].[Property Type ID]
	WHERE [CategoryProperty].[Category ID] = @CategoryID
)
GO
/****** Object:  UserDefinedFunction [dbo].[GetCategorySubCategories]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   FUNCTION [dbo].[GetCategorySubCategories]
(
    @CategoryID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT [Sub Category ID] AS ID, [Sub Category Name] AS Name
    FROM [SubCategory]
    WHERE [Category ID] = @CategoryID
)
GO
/****** Object:  UserDefinedFunction [dbo].[GetPropertyTypes]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetPropertyTypes]
(	
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT [Property Type ID], [Property Type]
	FROM [PropertyType]
)
GO
/****** Object:  UserDefinedFunction [dbo].[GetSubCategoryProperties]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   FUNCTION [dbo].[GetSubCategoryProperties]
(	
	@SubCategoryID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT [Property].[Name], [Property].[Property ID], [Sub Category Property ID], [PropertyType].[Property Type ID], [PropertyType].[Property Type]
	FROM [SubCategoryProperty]
	INNER JOIN [Property] ON
		[Property].[Property ID] = [SubCategoryProperty].[Property ID]
	INNER JOIN [PropertyType] ON
		[Property].[Property Type ID] = [PropertyType].[Property Type ID]
	WHERE [SubCategoryProperty].[Sub Category ID] = @SubCategoryID
)
GO
/****** Object:  StoredProcedure [dbo].[CreateCategory]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE [dbo].[CreateCategory]
	@Category NVARCHAR(512)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;
				IF dbo.CategoryExists(@Category) = 1
				BEGIN;
					THROW 70000, 'Duplicate category names not allowed.', 1;
				END;

				INSERT INTO [Category] ([Category Name]) VALUES (@Category)

				SELECT SCOPE_IDENTITY() AS ID
			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[CreateCategoryProperty]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateCategoryProperty]
	@PropertyName NVARCHAR(128),
	@PropertyTypeID INT,
	@CategoryID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @PropertyID INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;

				INSERT INTO [Property] ([Name], [Property Type ID]) VALUES (@PropertyName, @PropertyTypeID)

				SET @PropertyID = SCOPE_IDENTITY()

				INSERT INTO [CategoryProperty] ([Category ID], [Property ID]) VALUES (@CategoryID, @PropertyID)

			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[CreateSubCategory]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE [dbo].[CreateSubCategory]
	@SubCategory NVARCHAR(512),
	@CategoryID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;
				IF dbo.SubCategoryExists(@SubCategory) = 1
				BEGIN;
					THROW 51001, 'Duplicate sub-category names not allowed.', 1;
				END;

				INSERT INTO [SubCategory] ([Sub Category Name], [Category ID]) VALUES (@SubCategory, @CategoryID)

                SELECT SCOPE_IDENTITY() AS ID
				
			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[CreateSubCategoryProperty]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateSubCategoryProperty]
	@PropertyName NVARCHAR(128),
	@PropertyTypeID INT,
	@SubCategoryID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @PropertyID INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;

				INSERT INTO [Property] ([Name], [Property Type ID]) VALUES (@PropertyName, @PropertyTypeID)

				SET @PropertyID = SCOPE_IDENTITY()

				INSERT INTO [SubCategoryProperty] ([Sub Category ID], [Property ID]) VALUES (@SubCategoryID, @PropertyID)

			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteCategory]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteCategory]
	@CategoryID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @ItemIDs TABLE
	(
		RowNumber INT PRIMARY KEY IDENTITY(1, 1),
		ID INT
	)
	DECLARE @SubCategoryIDs TABLE
	(
		RowNumber INT PRIMARY KEY IDENTITY(1, 1),
		ID INT
	)
	DECLARE @PropertyIDs TABLE
	(
		RowNumber INT PRIMARY KEY IDENTITY(1, 1),
		ID INT
	)
	DECLARE @RowCount INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;
				-- Gather all Items that are associated with this category.
				INSERT INTO @ItemIDs ([ID])
				SELECT [Item ID]
				FROM [Item]
				WHERE [Item].[Category ID] = @CategoryID

				SET @RowCount=@@ROWCOUNT

				DECLARE @CurrentRow INT = 0
				DECLARE @ID INT
				-- Clear all information about the selected items.
				WHILE @CurrentRow < @RowCount
				BEGIN
					SET @CurrentRow = @CurrentRow + 1

					SELECT @ID = ID FROM @ItemIDs WHERE [RowNumber] = @CurrentRow

					DELETE FROM [ItemComponent] WHERE [Item ID] = @ID

					DELETE FROM [PropertyValue] WHERE [Item ID] = @ID

					DELETE FROM [Item] WHERE [Item ID] = @ID
				END
				-- Gather all child categories that are associated with this category.
				INSERT INTO @SubCategoryIDs ([ID])
				SELECT [Sub Category ID]
				FROM [SubCategory]
				WHERE [Category ID] = @CategoryID

				SET @RowCount = @@ROWCOUNT
				SET @CurrentRow = 0

				WHILE @CurrentRow < @RowCount
				BEGIN
					SET @CurrentRow = @CurrentRow + 1

					SELECT @ID = ID FROM @SubCategoryIDs WHERE [RowNumber] = @CurrentRow

					INSERT INTO @PropertyIDs ([ID])
					SELECT [Property ID]
					FROM [SubCategoryProperty]
					WHERE [Sub Category ID] = @ID

					DELETE FROM [SubCategoryProperty] WHERE [Sub Category ID] = @ID

					DELETE FROM [SubCategory] WHERE [Sub Category ID] = @ID
				END
				-- Clear properties from the sub-categories that were deleted and the category that is being deleted.
				INSERT INTO @PropertyIDs([ID])
				SELECT [Property ID]
				FROM [CategoryProperty]
				WHERE [Category ID] = @CategoryID

				DELETE FROM [CategoryProperty] WHERE [Category ID] = @CategoryID

				SELECT @RowCount = COUNT([ID]) FROM @PropertyIDs
				SET @CurrentRow = 0

				WHILE @CurrentRow < @RowCount
				BEGIN
					SET @CurrentRow = @CurrentRow + 1

					SELECT @ID = ID FROM @PropertyIDs WHERE [RowNumber] = @CurrentRow

					DELETE FROM [Property] WHERE [Property ID] = @ID
				END
				-- Finally, delete the category entirely.
				DELETE FROM [Category] WHERE [Category ID] = @CategoryID

			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteCategoryProperty]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteCategoryProperty]
	@CategoryPropertyID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @PropertyID INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;

				SELECT @PropertyID = [Property ID] FROM [CategoryProperty] WHERE [Category Property ID] = @CategoryPropertyID
				
				DELETE FROM [CategoryProperty] WHERE [Category Property ID] = @CategoryPropertyID

				DELETE FROM [Property] WHERE [Property ID] = @PropertyID
				
			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteSubCategory]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteSubCategory]
	@SubCategoryID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @ItemIDs TABLE
	(
		RowNumber INT PRIMARY KEY IDENTITY(1, 1),
		ID INT
	)
	DECLARE @PropertyIDs TABLE
	(
		RowNumber INT PRIMARY KEY IDENTITY(1, 1),
		ID INT
	)
	DECLARE @RowCount INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;
				-- Gather all Items that are associated with this sub-category.
				INSERT INTO @ItemIDs ([ID])
				SELECT [Item ID]
				FROM [Item]
				WHERE [Item].[Sub Category ID] = @SubCategoryID

				SET @RowCount=@@ROWCOUNT

				DECLARE @CurrentRow INT = 0
				DECLARE @ID INT
				-- Clear all information about the selected items.
				WHILE @CurrentRow < @RowCount
				BEGIN
					SET @CurrentRow = @CurrentRow + 1

					SELECT @ID = ID FROM @ItemIDs WHERE [RowNumber] = @CurrentRow

					DELETE FROM [ItemComponent] WHERE [Item ID] = @ID

					DELETE FROM [PropertyValue] WHERE [Item ID] = @ID

					DELETE FROM [Item] WHERE [Item ID] = @ID
				END
				-- Clear properties from the sub-categories that were deleted and the category that is being deleted.
				INSERT INTO @PropertyIDs([ID])
				SELECT [Property ID]
				FROM [SubCategoryProperty]
				WHERE [Sub Category ID] = @SubCategoryID

				DELETE FROM [SubCategoryProperty] WHERE [Sub Category ID] = @SubCategoryID

				SELECT @RowCount = COUNT([ID]) FROM @PropertyIDs
				SET @CurrentRow = 0

				WHILE @CurrentRow < @RowCount
				BEGIN
					SET @CurrentRow = @CurrentRow + 1

					SELECT @ID = ID FROM @PropertyIDs WHERE [RowNumber] = @CurrentRow

					DELETE FROM [Property] WHERE [Property ID] = @ID
				END
				-- Finally, delete the sub-category entirely.
				DELETE FROM [SubCategory] WHERE [Sub Category ID] = @SubCategoryID

			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteSubCategoryProperty]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteSubCategoryProperty]
	@SubCategoryPropertyID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @PropertyID INT

	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;

				SELECT @PropertyID = [Property ID] FROM [SubCategoryProperty] WHERE [Sub Category Property ID] = @SubCategoryPropertyID
				
				DELETE FROM [SubCategoryProperty] WHERE [Sub Category Property ID] = @SubCategoryPropertyID

				DELETE FROM [Property] WHERE [Property ID] = @PropertyID
				
			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateProperty]    Script Date: 4/13/2021 7:21:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateProperty]
	@PropertyID INT,
	@PropertyName NVARCHAR(128),
	@PropertyTypeID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT, XACT_ABORT ON;

		BEGIN TRY

			BEGIN TRANSACTION;

				UPDATE [Property] SET [Name] = @PropertyName, [Property Type ID] = @PropertyTypeID WHERE [Property ID] = @PropertyID

			COMMIT TRANSACTION;

		END TRY
		BEGIN CATCH 

		   IF @@TRANCOUNT > 0
		   BEGIN
			  ROLLBACK TRANSACTION
		   END;

		   THROW;

		END CATCH;

	SET NOCOUNT, XACT_ABORT OFF;
END
GO