USE [ION_Data]
GO

/****** Object:  StoredProcedure [dbo].[TrimRecordsBySource]    Script Date: 12/4/2019 2:13:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spDM_DeleteSourceAndBatchDeleteData]
(
	@SourceSystemName nvarchar (MAX)	
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RetVal INT
SET @RetVal = 0


DECLARE @IONSourceID bigint
DECLARE @NOMsourceID bigint
DECLARE @BatchDeleteNumber INT = 2500 --Number of records to delete at a time.
DECLARE @DelCount INT = 1 --keeps track of when to stop batch deleting a table
DECLARE @LastCheckPoint AS DATETIME = GETUTCDATE()	
DECLARE @Counter INT = 1

-- get the sourceID
SELECT @IONsourceID = ID FROM [ION_Data]..Source WHERE [Name] = @SourceSystemName

--Delete the waveforms for the source.
BEGIN TRY
	BEGIN
		DECLARE @WaveformIdsToDelete TABLE (WaveformID INT NOT NULL)
		
		SET @DelCount = 1
		WHILE @DelCount > 0
		BEGIN
			Print 'Waveform batch:'+ CAST(@Counter as VARCHAR)
			DELETE @WaveformIdsToDelete

			--Insert next batch of waveformIDs to delete
			Insert into @WaveformIdsToDelete 
				select top (@BatchDeleteNumber) ID from waveformLog2 where SourceID = @IONsourceID
                
			BEGIN TRAN
				DELETE  FROM dbo.WaveformLog2 Where ID in (SELECT WaveformID from @WaveformIdsToDelete)				
				SET @DelCount = @@ROWCOUNT					
			COMMIT

			-- Checkpoint every 60 seconds
			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END
			SET @Counter = @Counter +1
		END  
	END

	--Batch Delete events
	BEGIN
				
		DECLARE @EventIdsToDelete TABLE (EventID INT PRIMARY KEY NOT NULL)		
		DECLARE @AlarmIdsToDelete TABLE (AlarmID INT PRIMARY KEY NOT NULL)		
		DECLARE @AlarmDefinitionIdsToModify TABLE (AlarmDefintionID INT PRIMARY KEY NOT NULL)			
		DECLARE @IncidentIdsToModify TABLE (IncidentID INT PRIMARY KEY NOT NULL)
		DECLARE @PQEventIdsToDelete TABLE (EventID INT PRIMARY KEY NOT NULL)
			
		--Reset Delete Counters
		SET @DelCount = 1
		SET @Counter = 1
		SET @LastCheckPoint = GETUTCDATE()
		WHILE @DelCount > 0
		BEGIN
			Print 'Event batch:'+ CAST(@Counter as VARCHAR)			
			DELETE @EventIdsToDelete
			DELETE @AlarmIdsToDelete
			DELETE @AlarmDefinitionIdsToModify
			DELETE @IncidentIdsToModify			

			--Insert Next Batch of EventIDs to delete
			INSERT INTO @EventIdsToDelete 
				SELECT TOP (@BatchDeleteNumber) ID FROM dbo.EventLog2 WHERE SourceID = @IONsourceID
			INSERT INTO @AlarmIdsToDelete
				SELECT DISTINCT AlarmID FROM AlarmEvent WHERE EventID IN (SELECT EventID FROM @EventIdsToDelete)			
			INSERT INTO @AlarmDefinitionIdsToModify
				SELECT DISTINCT AlarmDefinitionID FROM Alarm WHERE ID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
			INSERT INTO @IncidentIdsToModify
				SELECT DISTINCT IncidentID FROM IncidentAlarm WHERE AlarmID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
													
			BEGIN TRAN		
				DELETE FROM EventAcknowledgement WHERE EventLogID IN (SELECT EventID FROM @EventIdsToDelete)
				DELETE FROM IncidentAlarm WHERE AlarmID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
				DELETE FROM Incident WHERE ID IN (SELECT IncidentID FROM @IncidentIdsToModify WHERE IncidentID NOT IN (SELECT DISTINCT IncidentID FROM IncidentAlarm))
				DELETE FROM AlarmStates WHERE AlarmID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
				DELETE FROM AlarmEvent WHERE AlarmID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
				DELETE FROM Alarm WHERE ID IN (SELECT AlarmID FROM @AlarmIdsToDelete)
				DELETE FROM AlarmAcknowledgement WHERE ID NOT IN (SELECT DISTINCT AcknowledgementID FROM Alarm WHERE AcknowledgementID IS NOT NULL)


				UPDATE AlarmDefinition
				SET
					AlarmDefinition.Occurrences = (SELECT COUNT(*) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID),
					AlarmDefinition.Unacknowledged = (SELECT COUNT(*) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID AND Alarm.AcknowledgementID IS NULL),
					AlarmDefinition.IsActive = ISNULL((SELECT TOP 1 (IsActive+0) FROM dbo.Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID ORDER BY StartTimestampUTC DESC), 0),
					AlarmDefinition.FirstOccurrenceUTC = (SELECT MIN(StartTimestampUTC) FROM Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID),
					AlarmDefinition.LastOccurrenceUTC = (SELECT MAX(StartTimestampUTC) FROM Alarm WHERE AlarmDefinitionID = AlarmDefinition.ID),
					AlarmDefinition.LastModifiedUTC = GETUTCDATE()
				FROM @AlarmDefinitionIdsToModify updatedAlarmDefs
				WHERE AlarmDefinition.ID = updatedAlarmDefs.AlarmDefintionID
					
			-- Trim the EventLog2 table
				DELETE FROM EventLog2 WHERE ID IN (SELECT EventID FROM @EventIdsToDelete)
				SET @DelCount = @@ROWCOUNT

			COMMIT
			
			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END	
			SET @Counter = @counter +1
		END

		--Trim PQ_Events
		SET @DelCount = 1
		SET @LastCheckPoint = GETUTCDATE()	
		SET @Counter =1
		WHILE @DelCount > 0
		BEGIN
			
			Print 'PQ_Event batch:'+ CAST(@Counter as VARCHAR)
			DELETE @PQEventIdsToDelete
			INSERT INTO @PQEventIdsToDelete
			SELECT TOP (@BatchDeleteNumber) EventId FROM PQ_Events WHERE SourceID = @IONsourceID

			BEGIN TRAN		
				UPDATE Incident SET PQEventID = NULL WHERE PQEventID IN (SELECT EventID FROM @PQEventIdsToDelete)
				UPDATE Alarm SET PQEventID = NULL WHERE PQEventID IN (SELECT EventID FROM @PQEventIdsToDelete)
				-- Trim the PQ_Events table
				DELETE FROM PQ_Events WHERE EventId IN (SELECT EventID FROM @PQEventIdsToDelete)
				SET @DelCount = @@ROWCOUNT
			COMMIT

			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END
			SET @Counter = @Counter +1
		END							
	END
	

--Batch Delete Datalog2
BEGIN

		-- Create a list of Burst IDs that will be deleted.  This will be used in a few places.
		DECLARE @BurstIdsToDelete TABLE (BurstID INT PRIMARY KEY NOT NULL)
		DECLARE @DatalogAuditIdsToDelete TABLE (AuditID INT PRIMARY KEY NOT NULL)
		DECLARE @DataLog2DuplicateIdsToDelete TABLE (ID BIGINT PRIMARY KEY NOT NULL)
		DECLARE @DeleteIDs TABLE (DataLogID BIGINT NOT NULL)	

		SET @DelCount = 1
		SET @LastCheckPoint = GETUTCDATE()
		SET @Counter = 1
		WHILE @DelCount > 0
		BEGIN
			Print 'BurstData batch:'+ CAST(@Counter as VARCHAR)
			DELETE @BurstIdsToDelete
			INSERT INTO @BurstIdsToDelete
				SELECT TOP (@BatchDeleteNumber) ID FROM Burst WHERE  SourceID = @IONsourceID
			BEGIN TRAN
				DELETE FROM BurstDataLog WHERE BurstID IN (SELECT BurstID FROM @BurstIdsToDelete)
				DELETE FROM Burst WHERE ID IN (SELECT BurstID FROM @BurstIdsToDelete)
				SET @DelCount = @@ROWCOUNT
			COMMIT

			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END
			SET @Counter = @Counter +1
		END


		SET @DelCount = 1
		SET @LastCheckPoint = GETUTCDATE()
		SET @Counter = 1
		WHILE @DelCount > 0
		BEGIN			
			Print 'DatalogAudit batch:'+ CAST(@Counter as VARCHAR)
			DELETE @DatalogAuditIdsToDelete
			INSERT INTO @DatalogAuditIdsToDelete
				SELECT TOP (@BatchDeleteNumber) AuditID FROM DataLogAuditEntry WHERE SourceID = @IONsourceID

			BEGIN TRAN				
				DELETE FROM DataLogAudit WHERE ID IN (SELECT AuditID FROM @DatalogAuditIdsToDelete)
				DELETE FROM DataLogAuditEntry WHERE AuditID IN (SELECT AuditID FROM @DatalogAuditIdsToDelete)			
				SET @DelCount = @@ROWCOUNT								
			COMMIT

			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END
			SET @Counter = @Counter +1
		END
		
		SET @DelCount = 1
		SET @Counter = 1
		SET @LastCheckPoint = GETUTCDATE()	
		WHILE @DelCount > 0
		BEGIN
			Print 'DatalogDuplicate batch:'+ CAST(@Counter as VARCHAR)
			DELETE @DataLog2DuplicateIdsToDelete
			INSERT INTO @DataLog2DuplicateIdsToDelete
				SELECT TOP (@BatchDeleteNumber) ID FROM DataLog2Duplicate WHERE SourceID = @IONsourceID

			BEGIN TRAN
				DELETE FROM DataLog2Duplicate WHERE ID in (Select ID from @DataLog2DuplicateIdsToDelete)
				SET @DelCount = @@ROWCOUNT
			COMMIT

			IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
			BEGIN
				CHECKPOINT
				SET @LastCheckPoint = GETUTCDATE()
			END
			SET @Counter = @Counter +1
		END
						
	
		SET @DelCount = 1
		SET @counter =1
		SET @LastCheckPoint = GETUTCDATE()
		WHILE @DelCount > 0
			BEGIN				
				Print 'Datalog2 batch:'+ CAST(@Counter as VARCHAR)
				-- Clear the delete Ids table
				DELETE @DeleteIDs								
				-- Deleting in batches of 2500, trade off between overall duration versus avoiding
				-- lock escalation and exclusively locking DataLog2
				INSERT INTO @DeleteIDs
					SELECT TOP (@BatchDeleteNumber) [ID] FROM dbo.DataLog2 dl where SourceID = @IONSourceID

				
				-- Trim each batch within an individual transaction
				BEGIN TRAN
					DELETE  dl FROM dbo.DataLog2 dl
					INNER JOIN @DeleteIDs d ON (d.DataLogID = dl.ID)			
					SET @DelCount = @@ROWCOUNT					
				COMMIT
				
				-- Checkpoint every 60 seconds
				IF DATEDIFF(second, @LastCheckPoint, GETUTCDATE()) > 60
				BEGIN
					CHECKPOINT
					SET @LastCheckPoint = GETUTCDATE()
				END
				SET @Counter = @Counter +1
			END -- While records to delete
	END

END TRY
	
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
		DECLARE @ErrNum AS INT = ERROR_NUMBER(), @ErrMsg AS NVARCHAR(MAX) = ERROR_MESSAGE()
		RAISERROR('Error # %d in TrimRecords: %s', 16, 1, @ErrNum, @ErrMsg)
	
	END CATCH

Print 'Start Non-Batch Deleting now'
BEGIN TRY

	BEGIN TRAN	
	-- delete from the supporting tables
	DELETE FROM [ION_Data]..Channel WHERE RecorderID IN (SELECT ID FROM [ION_Data]..Recorder WHERE SourceId=@IONSourceID) 
	DELETE FROM [ION_Data]..RecorderState WHERE RecorderId In (SELECT ID FROM [ION_Data]..Recorder WHERE SourceId = @IONSourceID) 
	DELETE FROM [ION_Data]..Recorder WHERE SourceId=@IONSourceID
	DELETE FROM [ION_Data]..SourceQuantity WHERE SourceId=@IONSourceID
	DELETE FROM [ION_Data]..SourceState WHERE SourceID=@IONSourceID
	DELETE FROM [ION_Data]..Device WHERE ID =@IONSourceID
	
	-- Need to do an existence check
	IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'PQ_EventsToReprocess' and xtype = 'U')
		DELETE from [ION_Data]..PQ_EventsToReprocess where EventId in (select EventId from [ION_Data]..PQ_Events where SourceId = @IONSourceID)	
	DELETE from [ION_Data]..PQ_IncidentEventMap where EventId in (select EventId from [ION_Data]..PQ_Events where SourceId = @IONSourceID)
	
	
	-- PME9.0 - need to do an existence check
	IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'AlarmLog' and xtype = 'U')
		DELETE FROM [ION_Data]..AlarmLog WHERE SourceID = @IONSourceID		
		
	DELETE FROM [ION_Data]..AlarmDefinition WHERE SourceID = @IONSourceID
	DELETE from [ION_Data]..SourceQuantity where SourceID = @IONSourceID
	
	-- DeviceSettings is a new table in PME 9.0 - do existence check
	IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'DeviceSettings' and xtype = 'U')
		DELETE from [ION_Data]..DeviceSettings where  SourceId = @IONSourceID

	-- Delete device from ION_Data
	DELETE FROM [ION_Data]..Source WHERE ID=@IONSourceID
	
	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	PRINT N'Transaction rolled back.'
	
	SELECT
	ERROR_NUMBER() AS ErrorNumber,
	ERROR_SEVERITY() AS ErrorSeverity,
	ERROR_STATE() AS ErrorState,
	ERROR_PROCEDURE() AS ErrorProcedure,
	ERROR_LINE() AS ErrorLine,
	ERROR_MESSAGE() AS ErrorMessage;

END CATCH


-- ION_Network

SELECT @NOMsourceID = SourceID FROM [ION_Network]..SRC_Source WHERE Name = @SourceSystemName

BEGIN TRY
	
	BEGIN TRAN

	-- need to do an existence check
	IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'EVT_EventHistory' and xtype = 'U')
		Delete from [ION_Network]..EVT_EventHistory where SourceId = @NOMSourceID

	DELETE FROM [ION_Network]..IAS_MeasurementAddress WHERE SourceID = @NOMSourceID
	DELETE FROM [ION_Network]..SRC_Source WHERE SourceID = @NOMSourceID
	DELETE FROM [ION_Network]..Security WHERE DeviceID in (SELECT ID FROM [ION_Network]..Device WHERE Name = @SourceSystemName)
	DELETE FROM [ION_Network]..Device WHERE [Name] = @SourceSystemName
	DELETE FROM [ION_Network]..CUL_DisplayText where Display = @SourceSystemName
	DELETE from [ION_Network]..ConnectionRequest where PortID in (Select ID from [ION_Network]..Port where Name = @SourceSystemName)
	DELETE from [ION_Network]..Portstate where PortID in (Select ID from [ION_Network]..Port where Name = @SourceSystemName)
	Delete from [ION_Network]..Port WHERE [Name] = @SourceSystemName
	Delete from [ION_Network]..GATE WHERE [Name] = @SourceSystemName
	Delete from [ION_Network]..IRM_DeviceInfo Where [Name] = @SourceSystemName
	--We Currently leave the EVT_SourceGroup and EVT_EventWatcher alone.  If the last SourceName is removed from the SourceGroupID, then you are just left with an empty Event Watcher
	Delete FROM [ION_Network]..EVT_SourceGroupMap where [SourceName] = @SourceSystemName
	Delete FROM [ION_Network]..LogicalDevice where Name = @SourceSystemName



	-- there may be some VIP logged data for this device; the logical device entries are removed when the LoDs are deleted
	DELETE FROM ION_Network..IAS_MeasurementAddress WHERE ID IN 
	(
		SELECT ID FROM [ION_Network]..IAS_MeasurementAddress WHERE Segment2 like CAST(@NOMSourceID AS NVARCHAR(12)) + '/%'
	)

	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	PRINT N'Transaction rolled back.'
	
	SELECT
	ERROR_NUMBER() AS ErrorNumber,
	ERROR_SEVERITY() AS ErrorSeverity,
	ERROR_STATE() AS ErrorState,
	ERROR_PROCEDURE() AS ErrorProcedure,
	ERROR_LINE() AS ErrorLine,
	ERROR_MESSAGE() AS ErrorMessage;

END CATCH	

-- ApplicationModules
DECLARE
@LogicalID bigint,
@AMDeviceID bigint

-- get the IDs
SELECT @LogicalID = LogicalDeviceID from [ApplicationModules].DataSourceBus.DataSourceDevice where DataSourceRepresentation = @SourceSystemName
SELECT @AMDeviceID = DatasourceDeviceID from [ApplicationModules].DataSourceBus.DataSourceDevice where DataSourceRepresentation = @SourceSystemName


-- delete from Metadata tables first (they reference DSB.SataSourceDevice

DECLARE
@TargetID bigint
-- get TargetID for the Group (this assumes that it was the group that was renamed)
SELECT @TargetID = DataSourceDeviceId from [ApplicationModules].DataSourceBus.DataSourceDevice WHERE DataSourceRepresentation = @SourceSystemName

BEGIN TRY
	BEGIN TRAN

	--TargetID FROM Metadata.MetadataView WHERE MetadataName = 'Device.MdName.GROUP' and MetadataValue = 'test'

	-- can't delete from view - have to delete from the underlying tables.

	delete from [ApplicationModules].metadata.MetadataItemValue 
	where MetadataItemId in
	(
		select MetadataItemId from [ApplicationModules].metadata.MetadataItem 
		where TargetId = @AMDeviceID
		AND TargetTypeId = (SELECT TargetTypeId FROM [ApplicationModules].Metadata.MetadataTargetType WHERE TargetType = N'Device')
	)

	delete from [ApplicationModules].Metadata.MetadataValue WHERE MetadataValueId NOT IN
	(select MetadataValueId from [ApplicationModules].Metadata.MetadataItemValue)

	delete from [ApplicationModules].Metadata.MetadataName Where MetadataNameId NOT IN
	(select MetadataNameId from [ApplicationModules].Metadata.MetadataValue)

	-- delete from DataSourceBus tables
	DELETE FROM [ApplicationModules].DataSourceBus.SampleAvailability WHERE DataSourceDeviceId = @AMDeviceID
	DELETE FROM [ApplicationModules].DataSourceBus.Stream WHERE LogicalDeviceId = @LogicalID
	DELETE FROM [ApplicationModules].DataSourceBus.LogicalDevice WHERE LogicalDeviceId = @LogicalID
	DELETE FROM [ApplicationModules].DataSourceBus.DataSourceDevice WHERE DataSourceRepresentation = @SourceSystemName
	
	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	PRINT N'Transaction rolled back.'
	
	SELECT
	ERROR_NUMBER() AS ErrorNumber,
	ERROR_SEVERITY() AS ErrorSeverity,
	ERROR_STATE() AS ErrorState,
	ERROR_PROCEDURE() AS ErrorProcedure,
	ERROR_LINE() AS ErrorLine,
	ERROR_MESSAGE() AS ErrorMessage;

END CATCH

END
DONE:
RETURN @RetVal
GO
