SET NOCOUNT ON;
USE HistorySchemes;
GO

-- ============================================================================
-- ============================================================================
-- AccountLink
-- ============================================================================
-- ============================================================================

IF OBJECT_ID('AccountLink_CurrentHistoryID_FK') IS NOT NULL
	ALTER TABLE dbo.AccountLink DROP CONSTRAINT AccountLink_CurrentHistoryID_FK;
GO
IF OBJECT_ID('AccountLinkHistory_AccountLinkID_FK') IS NOT NULL
	ALTER TABLE dbo.AccountLinkHistory DROP CONSTRAINT AccountLinkHistory_AccountLinkID_FK;
GO
IF OBJECT_ID('AccountLinkHistory') IS NOT NULL
	DROP TABLE dbo.AccountLinkHistory;
GO
IF OBJECT_ID('AccountLink') IS NOT NULL
	DROP TABLE dbo.AccountLink;
GO

-- ========== Table: AccountLink
-- Store a linked history record that includes any changeable columns.
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLink' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	PRINT N'Create table AccountLink';
	CREATE TABLE dbo.AccountLink (
		AccountLinkID		int				NOT NULL	IDENTITY,
		CurrentHistoryID	int				NOT NULL,

		CONSTRAINT AccountLink_PK PRIMARY KEY CLUSTERED (AccountLinkID)
	);

	-- Insert a dummy record to allow the double-linked records
	INSERT INTO dbo.AccountLink (
		CurrentHistoryID
	)
	VALUES (
		1
	);
END;
GO

-- This index should be unique but can't be because of the way the records are inserted
-- using the dummy CurrentHistoryID first
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'AccountLink_CurrentHistoryID_IDX') BEGIN
	CREATE /*UNIQUE*/ NONCLUSTERED INDEX AccountLink_CurrentHistoryID_IDX ON dbo.AccountLink (
		CurrentHistoryID
	) WITH FILLFACTOR = 80;
END;
GO

--DENY DELETE, INSERT, REFERENCES, UPDATE ON OBJECT::dbo.AccountLink TO SqlCreditAppSqlRole;
--GO

-- ========== Table: AccountLinkHistory
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLinkHistory' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	PRINT N'Create table AccountLinkHistory';
	CREATE TABLE dbo.AccountLinkHistory (
		AccountLinkHistoryID	int				NOT NULL	IDENTITY,
		AccountLinkID			int				NOT NULL,
		UpdateUserID			int				NOT NULL,		-- The user making the INSERT/UPDATE/DELETE call
		PrimaryCardHolderID		int				NOT NULL,
		Address1				varchar(40)		NOT NULL,
		Address2				varchar(40)		NOT NULL,
		City					varchar(40)		NOT NULL,
		[State]					char(2)			NOT NULL,
		ZipCode					varchar(10)		NOT NULL,
		HistoryDate				datetime		NOT NULL	CONSTRAINT AccountLinkHistory_HistoryDate_DF	DEFAULT GETUTCDATE(),
		StatusCd				tinyint			NOT NULL	CONSTRAINT AccountLinkHistory_StatusCd_DF		DEFAULT 1,

		CONSTRAINT AccountLinkHistory_PK PRIMARY KEY CLUSTERED (AccountLinkHistoryID)
	);

	-- Insert a dummy record to allow the double-linked records
	INSERT INTO dbo.AccountLinkHistory (
		-- AccountLinkHistoryID
		AccountLinkID,
		UpdateUserID,
		PrimaryCardHolderID,
		Address1,
		Address2,
		City,
		State,
		ZipCode,
		-- HistoryDate,
		StatusCd
	)
	VALUES (
		1,
		1,
		1,
		'Address1',
		'Address2',
		'City',
		'ST',
		'ZIPCODE',
		1
	);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'AccountLinkHistory_AccountLinkID_IDX') BEGIN
	CREATE NONCLUSTERED INDEX AccountLinkHistory_AccountLinkID_IDX ON dbo.AccountLinkHistory (
		AccountLinkID
	) WITH FILLFACTOR = 80;
END;
GO

--DENY DELETE, INSERT, REFERENCES, UPDATE ON OBJECT::dbo.AccountLinkHistory TO SqlCreditAppSqlRole;
--GO


-- Now that the AccountLink table exists,
-- add the FK from AccountLinkHistory.AccountLinkID to AccountLink.AccountLinkID
IF    EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLink'        AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE')
  AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLinkHistory' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE')
  AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'AccountLinkHistory_AccountLinkID_FK') BEGIN
	ALTER TABLE dbo.AccountLinkHistory
	ADD CONSTRAINT AccountLinkHistory_AccountLinkID_FK FOREIGN KEY (AccountLinkID)
	REFERENCES dbo.AccountLink (AccountLinkID);
END;
GO

-- Now that the AccountLinkHistory table exists,
-- add the FK from AccountLink.CurrentHistoryID to AccountLinkHistory.AccountLinkHistoryID
IF    EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLink'        AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE')
  AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AccountLinkHistory' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE')
  AND NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'AccountLink_CurrentHistoryID_FK') BEGIN
	ALTER TABLE dbo.AccountLink
	ADD CONSTRAINT AccountLink_CurrentHistoryID_FK FOREIGN KEY (CurrentHistoryID)
	REFERENCES dbo.AccountLinkHistory (AccountLinkHistoryID);
END;
GO

-- =====================================================
PRINT N'AccountLinkCreate';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'AccountLinkCreate' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.AccountLinkCreate;
GO
CREATE PROCEDURE dbo.AccountLinkCreate (
	@AccountLinkID	int						OUTPUT,
	@UpdateUserID	int,
	@Address1		varchar(40),
	@Address2		varchar(40),
	@City			varchar(40),
	@State			char(2),
	@ZipCode		varchar(10),
	@TestMode		int				= NULL		-- Use values between 11 and 20
) AS
-- Create a new AccountLink record
BEGIN
	SET NOCOUNT ON;

	DECLARE @accountLinkHistoryID	int;
	DECLARE @error					int;
	DECLARE @list					nvarchar(255);
	DECLARE @rowcount				int;
	DECLARE @spName					sysname;

	SELECT @spName = Object_Name(@@ProcID);

	-- This must be called from within an explicit transaction
	IF @@TRANCOUNT = 0 BEGIN
		RAISERROR(999007, 10, 1, @spName, N'') WITH SETERROR;
		RETURN(@@error);
	END;

	INSERT INTO dbo.AccountLink (
		-- AccountLinkID
		CurrentHistoryID
	)
	VALUES (
		-- This one for CurrentHistoryID points to a dummy record to satisfy the NOT NULL constraint
		-- until the history record is created.
		1
	);

	SELECT
		@error			= @@error,
		@rowcount		= @@rowcount,
		@AccountLinkID	= SCOPE_IDENTITY();

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (11, 12) BEGIN
		-- Let the calling code do the ROLLBACK
		EXEC dbo.TagValueList @list OUTPUT, N'@Address1', @Address1, N'@Address2', @Address2, N'@City', @City, N'@State', @State, N'@ZipCode', @ZipCode;

		IF @error <> 0 OR @TestMode = 11 BEGIN
			RAISERROR(999001, 10, 1, @spName, @error, N'AccountLink', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 12 BEGIN
			RAISERROR(999002, 10, 1, @spName, N'AccountLink', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	-- History Record
	INSERT INTO dbo.AccountLinkHistory (
		-- AccountLinkHistoryID
		AccountLinkID,
		UpdateUserID,
		PrimaryCardHolderID,
		Address1,
		Address2,
		City,
		[State],
		ZipCode
		-- HistoryDate
		-- StatusCd
	)
	VALUES (
		@AccountLinkID,
		@UpdateUserID,
		@UpdateUserID,		-- PrimaryCardHolderID
		@Address1,
		@Address2,
		@City,
		@State,
		@ZipCode
	);

	SELECT
		@error					= @@error,
		@rowcount				= @@rowcount,
		@accountLinkHistoryID	= SCOPE_IDENTITY();

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (13, 14) BEGIN
		-- Let the calling code do the ROLLBACK
		EXEC dbo.TagValueList @list OUTPUT, N'@AccountLinkID', @AccountLinkID, N'@UpdateUserID', @UpdateUserID, N'@Address1', @Address1, N'@Address2', @Address2, N'@City', @City, N'@State', @State, N'@ZipCode', @ZipCode;

		IF @error <> 0 OR @TestMode = 13 BEGIN
			RAISERROR(999001, 10, 1, @spName, @error, N'AccountLinkHistory', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 14 BEGIN
			RAISERROR(999002, 10, 1, @spName, N'AccountLinkHistory', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	-- Update CurrentHistoryID
	UPDATE dbo.AccountLink
	SET CurrentHistoryID = @accountLinkHistoryID
	WHERE AccountLinkID = @AccountLinkID;

	SELECT
		@error		= @@error,
		@rowcount	= @@rowcount;

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (15, 16) BEGIN
		-- Let the calling code do the ROLLBACK
		EXEC dbo.TagValueList @list OUTPUT, N'@AccountLinkID', @AccountLinkID, N'@Address1', @Address1, N'@Address2', @Address2, N'@City', @City, N'@State', @State, N'@ZipCode', @ZipCode;

		IF @error <> 0 OR @TestMode = 15 BEGIN
			RAISERROR(999003, 10, 1, @spName, @error, N'AccountLink', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 16 BEGIN
			RAISERROR(999004, 10, 1, @spName, N'AccountLink', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	RETURN(0);
END;
GO

--GRANT EXEC ON dbo.AccountLinkCreate TO SqlCreditAppSqlRole;
--GO

-- =====================================================
PRINT N'AccountLinkReadByID';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'AccountLinkReadByID' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.AccountLinkReadByID;
GO
CREATE PROCEDURE dbo.AccountLinkReadByID (
	@AccountLinkID		int,
	@ExcludeDeleted		bit		= 1		-- By default, do not return deleted records
) AS
-- Read a single AccountLink record by AccountLinkID
BEGIN
	SET NOCOUNT ON;

	IF @ExcludeDeleted = 1 BEGIN
		SELECT
			l.AccountLinkID,
			h.PrimaryCardHolderID,
			h.Address1,
			h.Address2,
			h.City,
			h.[State],
			h.ZipCode,
			h.StatusCd
		FROM dbo.AccountLink		AS l
		JOIN dbo.AccountLinkHistory	AS h
		ON l.AccountLinkID = h.AccountLinkID
		WHERE l.AccountLinkID		= @AccountLinkID
		  AND l.CurrentHistoryID	= h.AccountLinkHistoryID
		  AND h.StatusCd <> 4;			-- 4 = 'DELETED'
	END;
	ELSE BEGIN
		SELECT
			l.AccountLinkID,
			h.PrimaryCardHolderID,
			h.Address1,
			h.Address2,
			h.City,
			h.[State],
			h.ZipCode,
			h.StatusCd
		FROM dbo.AccountLink		AS l
		JOIN dbo.AccountLinkHistory	AS h
		ON l.AccountLinkID = h.AccountLinkID
		WHERE l.AccountLinkID		= @AccountLinkID
		  AND l.CurrentHistoryID	= h.AccountLinkHistoryID;
	END;

	RETURN(0);
END;
GO

--GRANT EXEC ON dbo.AccountLinkReadByID TO SqlCreditAppSqlRole;
--GO

-- =====================================================
PRINT N'AccountLinkUpdate';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'AccountLinkUpdate' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.AccountLinkUpdate;
GO
CREATE PROCEDURE dbo.AccountLinkUpdate (
	@AccountLinkID			int,
	@UpdateUserID			int,
	@PrimaryCardHolderID	int,
	@Address1				varchar(40),
	@Address2				varchar(40),
	@City					varchar(40),
	@State					char(2),
	@ZipCode				varchar(10),
	@StatusCd				tinyint,
	@TestMode				int				= NULL
) AS
-- Update an AccountLink record
-- Do not allow updates of deleted records
BEGIN
	SET NOCOUNT ON;

	DECLARE @accountLinkHistoryID	int;
	DECLARE @error					int;
	DECLARE @list					nvarchar(255);
	DECLARE @localTran				bit;
	DECLARE @rowcount				int;
	DECLARE @spName					sysname;

	SELECT @spName = Object_Name(@@ProcID);

	-- If record is deleted, RAISERROR and RETURN
	IF EXISTS (
		SELECT 1
		FROM dbo.AccountLinkHistory
		WHERE AccountLinkID	= @AccountLinkID
		  AND StatusCd		= 4		-- 4 = 'DELETED'
	) BEGIN
		EXEC dbo.TagValueList @list OUTPUT, N'@AccountLinkID', @AccountLinkID;
		RAISERROR(999500, 10, 1, @list) WITH SETERROR;
		RETURN(@@error);
	END;

	SELECT @localTran = 0;
	IF @@TRANCOUNT = 0 BEGIN
		BEGIN TRAN;
		SELECT @localTran = 1;
	END;

	-- History Record
	INSERT INTO dbo.AccountLinkHistory (
		-- AccountLinkHistoryID
		AccountLinkID,
		UpdateUserID,
		PrimaryCardHolderID,
		Address1,
		Address2,
		City,
		[State],
		ZipCode,
		-- HistoryDate
		StatusCd
	)
	VALUES (
		@AccountLinkID,
		@UpdateUserID,
		@PrimaryCardHolderID,
		@Address1,
		@Address2,
		@City,
		@State,
		@ZipCode,
		@StatusCd
	);

	SELECT
		@error					= @@error,
		@rowcount				= @@rowcount,
		@accountLinkHistoryID	= SCOPE_IDENTITY();

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (1, 2) BEGIN
		IF @localTran = 1 BEGIN
			ROLLBACK TRAN;
		END;

		EXEC dbo.TagValueList @list OUTPUT, N'AccountLinkID', @AccountLinkID, N'@UpdateUserID', @UpdateUserID, N'@PrimaryCardHolderID', @PrimaryCardHolderID, N'@Address1', @Address1, N'@Address2', @Address2, N'@City', @City, N'@State', @State, N'@ZipCode', @ZipCode, N'@StatusCd', @StatusCd;

		IF @error <> 0 OR @TestMode = 1 BEGIN
			RAISERROR(999001, 10, 1, @spName, @error, N'AccountLinkHistory', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 2 BEGIN
			RAISERROR(999002, 10, 1, @spName, N'AccountLinkHistory', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	UPDATE dbo.AccountLink
	SET CurrentHistoryID = @accountLinkHistoryID
	WHERE AccountLinkID = @AccountLinkID;

	SELECT
		@error		= @@error,
		@rowcount	= @@rowcount;

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (3, 4) BEGIN
		IF @localTran = 1 BEGIN
			ROLLBACK TRAN;
		END;

		EXEC dbo.TagValueList @list OUTPUT, N'@AccountLinkID', @AccountLinkID, N'@PrimaryCardHolderID', @PrimaryCardHolderID, N'@Address1', @Address1, N'@Address2', @Address2, N'@City', @City, N'@State', @State, N'@ZipCode', @ZipCode, N'@StatusCd', @StatusCd;

		IF @error <> 0 OR @TestMode = 3 BEGIN
			RAISERROR(999003, 10, 1, @spName, @error, N'AccountLink', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 4 BEGIN
			RAISERROR(999004, 10, 1, @spName, N'AccountLink', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	IF @localTran = 1 BEGIN
		COMMIT TRAN;
	END;

	RETURN(0);
END;
GO

--GRANT EXEC ON dbo.AccountLinkUpdate TO SqlCreditAppSqlRole;
--GO

-- =====================================================
PRINT N'AccountLinkUpdateStatusCd';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'AccountLinkUpdateStatusCd' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.AccountLinkUpdateStatusCd;
GO
CREATE PROCEDURE dbo.AccountLinkUpdateStatusCd (
	@AccountLinkID	int,
	@UpdateUserID	int,
	@StatusCd		tinyint,
	@TestMode		int			= NULL
) AS
-- Update only the StatusCd of an AccountLink record
BEGIN
	SET NOCOUNT ON;

	DECLARE @accountLinkHistoryID	int;
	DECLARE @error					int;
	DECLARE @list					nvarchar(255);
	DECLARE @localTran				bit;
	DECLARE @rowcount				int;
	DECLARE @spName					sysname;

	SELECT @spName = Object_Name(@@ProcID);

	-- If record is deleted, RAISERROR and RETURN
	IF EXISTS (
		SELECT 1
		FROM dbo.AccountLinkHistory
		WHERE AccountLinkID	= @AccountLinkID
		  AND StatusCd		= 4		-- 4 = 'DELETED'
	) BEGIN
		EXEC dbo.TagValueList @list OUTPUT, N'@AccountLinkID', @AccountLinkID;
		RAISERROR(999500, 10, 1, '@list') WITH SETERROR;
		RETURN(@@error);
	END;

	SELECT @localTran = 0;
	IF @@TRANCOUNT = 0 BEGIN
		BEGIN TRAN;
		SELECT @localTran = 1;
	END;

	-- Inserting the history record first makes the coding much easier,
	-- and it's all in a transaction, so consistency is guaranteed.

	-- History Record
	INSERT INTO dbo.AccountLinkHistory (
		-- AccountLinkHistoryID
		AccountLinkID,
		UpdateUserID,
		PrimaryCardHolderID,
		Address1,
		Address2,
		City,
		[State],
		ZipCode,
		-- HistoryDate
		StatusCd
	)
	SELECT
		h.AccountLinkID,
		@UpdateUserID,
		h.PrimaryCardHolderID,
		h.Address1,
		h.Address2,
		h.City,
		h.State,
		h.ZipCode,
		@StatusCd		-- New status
	FROM dbo.AccountLink		AS l
	JOIN dbo.AccountLinkHistory	AS h
	ON    l.AccountLinkID		= h.AccountLinkID
	  AND l.CurrentHistoryID	= h.AccountLinkHistoryID
	WHERE l.AccountLinkID = @AccountLinkID;

	SELECT
		@error					= @@error,
		@rowcount				= @@rowcount,
		@accountLinkHistoryID	= SCOPE_IDENTITY();

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (1, 2) BEGIN
		IF @localTran = 1 BEGIN
			ROLLBACK TRAN;
		END;

		EXEC dbo.TagValueList @list OUTPUT, N'AccountLinkID', @AccountLinkID, N'@UpdateUserID', @UpdateUserID, N'@StatusCd', @StatusCd;

		IF @error <> 0 OR @TestMode = 1 BEGIN
			RAISERROR(999001, 10, 1, @spName, @error, N'AccountLinkHistory', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 2 BEGIN
			RAISERROR(999002, 10, 1, @spName, N'AccountLinkHistory', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	-- Update the CurrentHistoryID
	UPDATE dbo.AccountLink
	SET CurrentHistoryID = @accountLinkHistoryID
	WHERE AccountLinkID = @AccountLinkID;		-- We already know the record is not deleted

	SELECT
		@error		= @@error,
		@rowcount	= @@rowcount;

	IF @error <> 0 OR @rowcount <> 1 OR @TestMode IN (3, 4) BEGIN
		IF @localTran = 1 BEGIN
			ROLLBACK TRAN;
		END;

		EXEC dbo.TagValueList @list OUTPUT, N'AccountLinkID', @AccountLinkID, N'@UpdateUserID', @UpdateUserID, N'@StatusCd', @StatusCd;

		IF @error <> 0 OR @TestMode = 3 BEGIN
			RAISERROR(999003, 10, 1, @spName, @error, N'AccountLink', @list);
			RETURN(@error);
		END;
		ELSE IF @rowcount <> 1 OR @TestMode = 4 BEGIN
			RAISERROR(999004, 10, 1, @spName, N'AccountLink', @rowcount, @list) WITH SETERROR;
			RETURN(@@error);
		END;
	END;

	IF @localTran = 1 BEGIN
		COMMIT TRAN;
	END;

	RETURN(0);
END;
GO

--GRANT EXEC ON dbo.AccountLinkUpdateStatusCd TO SqlCreditAppSqlRole;
--GO

-- =====================================================
PRINT N'AccountLinkDelete';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'AccountLinkDelete' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.AccountLinkDelete;
GO
CREATE PROCEDURE dbo.AccountLinkDelete (
	@AccountLinkID	int,
	@UpdateUserID	int,
	@TestMode		int			= NULL
) AS
-- Delete an AccountLink record
-- Deleting a record involves just updating its status, so call AccountLinkUpdateStatusCd
BEGIN
	SET NOCOUNT ON;

	DECLARE @returnValue	int;

	SELECT @returnValue = NULL;
	EXEC @returnValue = dbo.AccountLinkUpdateStatusCd
		@AccountLinkID	= @AccountLinkID,
		@UpdateUserID	= @UpdateUserID,
		@StatusCd		= 4,				-- 4 = DELETED
		@TestMode		= @TestMode;

	IF @returnValue IS NULL OR @returnValue <> 0 BEGIN
		RETURN ISNULL(@returnValue, -1);
	END;

	RETURN(0);
END;
GO

--GRANT EXEC ON dbo.AccountLinkDelete TO SqlCreditAppSqlRole;
--GO

PRINT N'<< DONE >>'
GO

-- ============================================================================
-- TESTS
-- ============================================================================
-- These are quick validation tests, not complete unit tests

DECLARE @acctID1	int;
DECLARE @rtn		int;

BEGIN TRAN;

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkCreate
	@AccountLinkID	= @acctID1			OUTPUT,
	@UpdateUserID	= 1000,
	@Address1		= 'Address1',
	@Address2		= 'Address2',
	@City			= 'City',
	@State			= 'OR',
	@ZipCode		= '97124';

COMMIT TRAN;

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdateStatusCd
	@AccountLinkID	= @acctID1,
	@UpdateUserID	= 99,
	@StatusCd		= 3;		-- 3 = 'LOCKED'

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdateStatusCd
	@AccountLinkID	= @acctID1,
	@UpdateUserID	= 99,
	@StatusCd		= 1;		-- 1 = 'ACTIVE'

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdate
	@AccountLinkID			= @acctID1,
	@UpdateUserID			= 1001,
	@PrimaryCardholderID	= 1000,
	@Address1				= 'Address1x',
	@Address2				= 'Address2x',
	@City					= 'Cityx',
	@State					= 'WA',
	@ZipCode				= '97125',
	@StatusCd				= 1;		-- 1 = 'ACTIVE'

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

-- Call update again, changing only the PrimaryCardholdID
SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdate
	@AccountLinkID			= @acctID1,
	@PrimaryCardholderID	= 1001,
	@UpdateUserID			= 1000,
	@Address1				= 'Address1x',
	@Address2				= 'Address2x',
	@City					= 'Cityx',
	@State					= 'WA',
	@ZipCode				= '97125',
	@StatusCd				= 1;		-- 1 = 'ACTIVE'

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdateStatusCd
	@AccountLinkID	= @acctID1,
	@UpdateUserID	= 99,
	@StatusCd		= 2;		-- 2 = 'INACTIVE'

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkDelete
	@AccountLinkID		= @acctID1,
	@UpdateUserID		= 1;

SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkReadByID
	@AccountLinkID		= @acctID1;

SELECT *
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

PRINT 'Expect failure updating deleted record'
SELECT @rtn = NULL;
EXEC @rtn = dbo.AccountLinkUpdate
	@AccountLinkID			= @acctID1,
	@UpdateUserID			= 1001,
	@PrimaryCardholderID	= 1000,
	@Address1				= 'Address1x',
	@Address2				= 'Address2x',
	@City					= 'Cityx',
	@State					= 'WA',
	@ZipCode				= '97125',
	@StatusCd				= 2;		-- 2 = 'INACTIVE'

SELECT UpdateUserID, PrimaryCardholderID, StatusCd --, HistoryDate
FROM dbo.AccountLinkHistory
WHERE AccountLinkID = @acctID1
ORDER BY AccountLinkHistoryID

---- ============================================================================
---- TESTS
---- ============================================================================
---- These are quick validation tests, not complete unit tests
--
--DECLARE @acctID1	int;
--DECLARE @rtn		int;
--
--BEGIN TRAN;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkCreate
--	@AccountLinkID	= @acctID1			OUTPUT,
--	@Address1		= 'Address1',
--	@Address2		= 'Address2',
--	@City			= 'City',
--	@State			= 'OR',
--	@ZipCode		= '97124';
--
--COMMIT TRAN;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkReadByID
--	@AccountLinkID		= @acctID1;
--
--SELECT *
--FROM dbo.AccountLinkHistory
--WHERE AccountLinkID = @acctID1
--ORDER BY AccountLinkHistoryID;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkUpdateStatusCd
--	@AccountLinkID	= @acctID1,
--	@StatusCd		= 3;		-- 3 = 'LOCKED'
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkReadByID
--	@AccountLinkID	= @acctID1;
--
--SELECT *
--FROM dbo.AccountLinkHistory
--WHERE AccountLinkID = @acctID1
--ORDER BY AccountLinkHistoryID;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkUpdate
--	@AccountLinkID			= @acctID1,
--	@UpdateUserID			= ...
--	@PrimaryCardholderID	= 1,
--	@Address1				= 'Address1x',
--	@Address2				= 'Address2x',
--	@City					= 'Cityx',
--	@State					= 'WA',
--	@ZipCode				= '97125',
--	@StatusCd				= 2;		-- 2 = 'INACTIVE'
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkReadByID
--	@AccountLinkID		= @acctID1;
--
--SELECT *
--FROM dbo.AccountLinkHistory
--WHERE AccountLinkID = @acctID1
--ORDER BY AccountLinkHistoryID;
--
--SELECT CurrentHistoryID
--FROM dbo.AccountLink
--WHERE AccountLinkID = @acctID1;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkDelete
--	@AccountLinkID		= @acctID1;
--
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkReadByID
--	@AccountLinkID		= @acctID1;
--
--SELECT *
--FROM dbo.AccountLinkHistory
--WHERE AccountLinkID = @acctID1
--ORDER BY AccountLinkHistoryID;
--
--PRINT 'Expect failure updating deleted record'
--SELECT @rtn = NULL;
--EXEC @rtn = dbo.AccountLinkUpdate
--	@AccountLinkID			= @acctID1,
--	@PrimaryCardholderID	= 1,
--	@Address1				= 'Address1x',
--	@Address2				= 'Address2x',
--	@City					= 'Cityx',
--	@State					= 'WA',
--	@ZipCode				= '97125',
--	@StatusCd				= 2;		-- 2 = 'INACTIVE'
--
--SELECT @@TRANCOUNT AS 'Ending Trancount';