/*

Pentana Audit - Move an Identity to SYSTEM.sql

Written by Ideagen (email: support.pentana-audit@ideagen.com)

Purpose of script:
	In early versions of Pentana Audit, the Robot would be set up as a real user (as a Staff person).  In newer versions we do not do this, the 
	Robot's Identity (i.e. Windows user) is mapped to the application's hidden SYSTEM user in App Manager.  This script 'moves' a specified Identity from a Person to the SYSTEM user,
	in effect changing the Robot setup from the old config method to the newer way of working. 

	If the Person user has only one Identity, it will then be deactivated.
	
To run:

- stop the Retain Server service (where present)
- stop the Pentana Audit robot service
- stop the Pentana application pools
- back up the Pentana database and verify
- back up the Retain database and verify (where present)
- edit this script, changing 'yourdomain\robotusername' for the Windows login of the Robot service
- run this script - it should run without error
- restart the Retain service (where present)
- restart the Pentana app pools
- restart the Pentana Audit robot service
- verify effect of change:
	- In Pentana Audit, the Identity should now not appear against its Person
	- If the Person had just that one Identity, it should be Inactive
	- The Identity should appear in App Manager (Tasks > Manage System Identities)
 
*/

-- Get the name of the account to move
	DECLARE @RobotIdentity VARCHAR(200) = 'yourdomain\robotusername';		-- CHANGE HERE

-- Check that there's no duplication of this Identity in the system, and print a useful message if there is
	IF (SELECT COUNT(*) FROM [IDENTITY] WHERE UniqueID = @RobotIdentity) = 1
		BEGIN
			-- Get the PersonID
			DECLARE @PersonID INT = (SELECT PERSONID FROM [IDENTITY] WHERE UniqueID = @RobotIdentity);

			-- If this is the only Identity for that person, then deactivate them
			IF (SELECT IdentityCount FROM Person WHERE ID = @PersonID) = 1
				UPDATE PERSON SET ACTIVE = 0 WHERE ID = @PersonID;

			-- Subtract an IdentityCount
			UPDATE PERSON SET IdentityCount = IdentityCount-1 WHERE ID = @PersonID;
					
			-- Move the Identity
			UPDATE [Identity] SET PersonID = 1, PersonGUID = 'EE25CDE7-A0D1-44AD-80E9-1E4423302AC7' WHERE UniqueID = @RobotIdentity;		
			
		END
	ELSE
		PRINT 'The Identity exists more than once or does not exist, could not continue';
		
-- Register this in the Installlog
	-- Find whether the last entry was an install or upgrade and manage the FromVersion and ToVersion appropiately
	DECLARE @FromVersion NVARCHAR(25) = (SELECT TOP 1 FromVersion FROM InstallLog ORDER BY ID DESC);
	DECLARE @ToVersion NVARCHAR(25) = (SELECT TOP 1 ToVersion FROM InstallLog ORDER BY ID DESC);

	IF @ToVersion IS NULL
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Move an Identity to SYSTEM.sql', 'Script', GETDATE(), @FromVersion, NULL, SYSTEM_USER);
	ELSE
		INSERT INTO InstallLog (GUID, Name, Type, Installed, FromVersion, ToVersion, [User]) VALUES (NewID(), 'Pentana Audit - Move an Identity to SYSTEM.sql', 'Script', GETDATE(), @ToVersion, NULL, SYSTEM_USER);
	GO