/*
30 fix all id generator

This script creates a SQL Server stored procedure and runs it immediately after creation.
It purposes is to detect and fix a situation where the last_id_values in the id_generator table are out of sync with the data.
This can happen if the system stops unexpectedly, or if data is inserted into the FootPrints tables without correctly using the id_generator table.

Instructions
------------
1 - Bring down Tomcat.  Tomcat caches the next Ids and must be restarted after the updates are performed so that they take affect.
2 - Backup the Database for safety.
3 - Replace the default schema fpscdb001 to reflect your Footprints database schema
4 - Run this script.  There is a call at bottom that will by default run in Test Mode, and show the intended updates, but it will not perform updates.
	EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Test Only';
5 - Rerun this script in update mode:
	EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Update';


*/


IF object_id('fpscdb001_system.id_generator$fixAll', 'P') IS NOT NULL
BEGIN
	DROP PROCEDURE fpscdb001_system.id_generator$fixAll;
END
GO

CREATE PROCEDURE fpscdb001_system.id_generator$fixAll (@RunMode VARCHAR(50) = 'Need Parameter Here')
AS
BEGIN
	--
	SET NOCOUNT ON;

	DECLARE @db_table_name VARCHAR(255)
		,@last_id_val BIGINT
		,@FullTableName VARCHAR(255)
		,@pk_column_name VARCHAR(255);
	DECLARE @max_id BIGINT
		,@current_id BIGINT
		,@increment_needed BIGINT
		,@NewStartId BIGINT;
	DECLARE @cmd1 NVARCHAR(1000);
	DECLARE @cmd2 NVARCHAR(1000);
	DECLARE @ParmDefinition NVARCHAR(500);
	DECLARE @tableStatusSummary TABLE (
		db_table_name VARCHAR(255)
		,last_id_val BIGINT
		,max_id_seen BIGINT
		,table_status VARCHAR(20)
		);
	DECLARE @badCt BIGINT;
	DECLARE @goodCt BIGINT;

	DECLARE TableCursor CURSOR
	FOR
	SELECT db_table_name
		,last_id_val
		,FullTableName
		,pk_column_name
	FROM fpscdb001_system.v_IdGeneratorPrimaryKey
	WHERE FullTableName IS NOT NULL;

	--
	DECLARE @summary TABLE (
		id INT identity(1, 1)
		,msgLine VARCHAR(150)
		);

	-- repeat inputs
	INSERT INTO @summary (msgLine)
	VALUES ('-- Program to Inspect and Reset all Id Lengths --');

	INSERT INTO @summary (msgLine)
	VALUES ('-- Inputs --');

	IF @RunMode NOT IN (
			'Test Only'
			,'Update'
			)
	BEGIN
		INSERT INTO @summary (msgLine)
		VALUES ('Invalid run mode "' + @RunMode + '".  Please choose ''Test Only'' or ''Update'' ');
	END
	ELSE
	BEGIN
		INSERT INTO @summary (msgLine)
		VALUES ('Run mode "' + @RunMode + '".  ');
	END

	DECLARE @sessionCt INT;

	SELECT @sessionCt = Count(*)
	FROM fpscdb001_system.sys_session;

	INSERT INTO @summary (msgLine)
	VALUES ('Session Count is:' + str(@sessionCt));

	IF @sessionCt = 0
	BEGIN
		INSERT INTO @summary (msgLine)
		VALUES ('Looks Like Tomcat is down.');
	END
	ELSE
	BEGIN
		INSERT INTO @summary (msgLine)
		VALUES ('Looks Like Tomcat is up.');

		INSERT INTO @summary (msgLine)
		VALUES ('Please bring Tomcat Down!');
	END

	INSERT INTO @summary (msgLine)
	VALUES ('-- Running Program --');

	-- load variables with lengths
	--
	SET @badCt = 0;
	SET @goodCt = 0;

	OPEN TableCursor

	FETCH NEXT
	FROM TableCursor
	INTO @db_table_name
		,@last_id_val
		,@FullTableName
		,@pk_column_name;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- check the maxid of the table vs the lastid of the idgenerator
		SET @cmd1 = replace(replace(N'select @max_idOUT = max(a.$1) from $2 a ', '$2', @FullTableName), '$1', @pk_column_name);
		SET @ParmDefinition = N'@max_idOUT bigint OUTPUT';

		EXECUTE sp_executesql @cmd1
			,@ParmDefinition
			,@max_idOUT = @max_id OUTPUT;

		--
		SET @current_id = @last_id_val;

		IF @current_id < @max_id
		BEGIN
			SET @badct = @badCt + 1;

			INSERT @tableStatusSummary (
				db_table_name
				,last_id_val
				,max_id_seen
				,table_status
				)
			VALUES (
				@db_table_name
				,@last_id_val
				,@max_id
				,'*** Needs Reset ***'
				);

			SELECT @increment_needed = @max_id - @current_id;

			IF @RunMode = 'Update'
			BEGIN
				EXEC fpscdb001_system.p_id_generator_getNext @db_table_name
					,@nextId = @current_id OUTPUT
					,@increment = @increment_needed;
			END;
		END
		ELSE
		BEGIN
			INSERT @tableStatusSummary (
				db_table_name
				,last_id_val
				,max_id_seen
				,table_status
				)
			VALUES (
				@db_table_name
				,@last_id_val
				,@max_id
				,'ok'
				);

			SET @goodCt = @goodCt + 1;
		END;

		--
		FETCH NEXT
		FROM TableCursor
		INTO @db_table_name
			,@last_id_val
			,@FullTableName
			,@pk_column_name;
	END;

	INSERT INTO @summary (msgLine)
	VALUES ('Bad Tables Detected Count =' + ltrim(STR(@badCt)));

	INSERT INTO @summary (msgLine)
	VALUES ('Good Tables Detected Count =' + ltrim(STR(@goodCt)));

	IF @badCt = 0
	BEGIN
		INSERT INTO @summary (msgLine)
		VALUES ('No Updates Needed');
	END
	ELSE
	BEGIN
		IF @RunMode = 'Update'
		BEGIN
			INSERT INTO @summary (msgLine)
			VALUES ('Updates Performed, Count =' + ltrim(STR(@goodCt)));

			INSERT INTO @summary (msgLine)
			VALUES ('*** Please Restart Tomcat ****');
		END
		ELSE
		BEGIN
			INSERT INTO @summary (msgLine)
			VALUES ('*** No Updates Performed! ****');

			INSERT INTO @summary (msgLine)
			VALUES ('*** Please Rerun in Update Mode! ***');
		END
	END;

	CLOSE TableCursor;

	DEALLOCATE TableCursor;

	INSERT INTO @summary (msgLine)
	VALUES ('--- end ---');

	SELECT msgLine AS "----------------------------------------------- Messages -------------------------------------------------------------"
	FROM @summary
	ORDER BY id;

	--

		SELECT *
		FROM @tableStatusSummary
		ORDER BY table_status
			,db_table_name;

END;
GO

EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Test Only';
GO

/*

 EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Update';

*/
