USE [VIGIL-Settings]
GO

-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_registry_key]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_set_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_set_registry_key]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_delete_registry_key
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_registry_value]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_delete_registry_value
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_clear_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_clear_registry_key
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_check_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_check_registry_key
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_enum_registry_key]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_enum_registry_key
GO


-- CREATE

CREATE PROCEDURE [dbo].[sp_get_registry_key]
@user_name nvarchar(512),
@path_name nvarchar(1024),
@key_name nvarchar(512),
@default_value nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int
	DECLARE @key_value nvarchar(4000)

	SET @path_id = -1
	SET @user_id = -1
	SET @key_value = @default_value
	SELECT @path_id = idx FROM registry_paths WHERE path_name = @path_name
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name

	IF (@path_id != -1) AND (@user_id != -1)
	BEGIN
		SELECT @key_value = key_value from registry_keys where user_id = @user_id AND path_id = @path_id AND key_name = @key_name
	END

	select @key_value as key_value
END
GO

CREATE PROCEDURE [dbo].[sp_set_registry_key]
@user_name nvarchar(512),
@path_name nvarchar(1024),
@key_name nvarchar(512),
@key_value nvarchar(4000)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int

	SET @path_id = -1
	SELECT @path_id = idx FROM registry_paths WHERE path_name = @path_name
	IF (@path_id = -1)
	BEGIN
		BEGIN
			INSERT INTO registry_paths (path_name)
			VALUES (@path_name)

			SET @path_id = SCOPE_IDENTITY()
		END
	END

	SET @user_id = -1
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name
	IF (@user_id = -1)
	BEGIN
		BEGIN
			INSERT INTO registry_users (user_name)
			VALUES (@user_name)

			SET @user_id = SCOPE_IDENTITY()
		END
	END


	IF (@path_id != -1) AND (@user_id != -1)
	BEGIN
		IF EXISTS(SELECT * FROM registry_keys WHERE user_id = @user_id AND path_id = @path_id AND key_name = @key_name)
		BEGIN
			UPDATE registry_keys SET key_value = @key_value WHERE user_id = @user_id AND path_id = @path_id AND key_name = @key_name
		END
		ELSE
		BEGIN
			INSERT INTO registry_keys(user_id, path_id, key_name, key_value, key_type) VALUES (@user_id, @path_id, @key_name, @key_value, 1)
		END
	END
END

GO

CREATE PROCEDURE [dbo].[sp_delete_registry_key]
@user_name nvarchar(512),
@path_name nvarchar(1024)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int

	SET @user_id = -1
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name

	-- TODOVIGIL: check if necessary to emit error if we have nested subkeys
	IF (@user_id != -1)
	BEGIN
		DELETE FROM registry_keys WHERE user_id = @user_id AND path_id in (SELECT idx from registry_paths where path_name like @path_name + '%')
	END
END
GO

CREATE PROCEDURE [dbo].[sp_delete_registry_value]
@user_name nvarchar(512),
@path_name nvarchar(1024),
@key_name nvarchar(512)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int

	SET @path_id = -1
	SELECT @path_id = idx FROM registry_paths WHERE path_name = @path_name

	SET @user_id = -1
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name

	IF (@path_id != -1) AND (@user_id != -1)
	BEGIN
		DELETE FROM registry_keys WHERE user_id = @user_id AND path_id = @path_id AND key_name = @key_name
	END
END
GO

CREATE PROCEDURE [dbo].[sp_clear_registry_key]
@user_name nvarchar(512),
@path_name nvarchar(1024)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int

	SET @user_id = -1
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name

	IF (@user_id != -1)
	BEGIN
		DELETE FROM registry_keys WHERE user_id = @user_id AND path_id in (SELECT idx from registry_paths where path_name like @path_name + '%')
	END
END
GO

CREATE PROCEDURE [dbo].[sp_check_registry_key]
@user_name nvarchar(512),
@path_name nvarchar(1024),
@key_name nvarchar(512)
AS
BEGIN
SET NOCOUNT ON
	DECLARE @path_id int
	DECLARE @user_id int
	DECLARE @key_value nvarchar(4000);

	SET @path_id = -1
	SET @user_id = -1
	SET @key_value = '0';

	SELECT @path_id = idx FROM registry_paths WHERE path_name = @path_name
	SELECT @user_id = idx FROM registry_users WHERE user_name = @user_name

	IF (@path_id != -1) AND (@user_id != -1)
	BEGIN
		IF EXISTS(SELECT * from registry_keys where user_id = @user_id AND path_id = @path_id AND key_name = @key_name)
			Set @key_value = 1
	END

	select @key_value as key_value
END
GO

CREATE PROCEDURE [dbo].[sp_enum_registry_key]
@user_name nvarchar(512), -- TODOVIGIL: join by user_name
@sub_key nvarchar(1024),
@index int
AS
BEGIN
SET NOCOUNT ON
	DECLARE @key_value nvarchar(4000);

	SET @key_value = '0';

	SELECT @key_value = key_value
	FROM   (SELECT ROW_NUMBER() OVER(ORDER BY idx ASC) AS row_number,
		           SUBSTRING(path_name, LEN(@sub_key) + 1, LEN(path_name) - LEN(@sub_key) - 1) AS key_value
		  FROM   registry_paths
		  WHERE  path_name LIKE CONCAT(@sub_key, '%')
			     AND LEN(path_name) - LEN(@sub_key) > 1
			     AND CHARINDEX('\', SUBSTRING(path_name, LEN(@sub_key) + 1, LEN(path_name) - LEN(@sub_key) - 1)) = 0) AS key_list
	WHERE  row_number = ( @index + 1 )

	SELECT @key_value AS key_value
END
GO
