

DECLARE 
@VirtualMeterName nvarchar (100),
@IONSourceID bigint,
@NOMsourceID bigint

-- will need to put in name for virtual device to be deleted - in this example the virtual device name is 'test'
SET @VirtualMeterName = 'test'

USE ION_Data
-- get the sourceID

SELECT @IONsourceID = ID FROM Source WHERE Name = @VirtualMeterName

BEGIN TRY

	BEGIN TRAN
	-- delete from data tables
	DELETE from DataLog2 WHERE SourceID = @IONSourceID

	DELETE FROM WaveformLog2 WHERE SourceID = @IONSourceID

	DELETE FROM EventAcknowledgement WHERE EventLogID IN
	(SELECT ID FROM EventLog2 WHERE SourceID = @IONSourceID)

	DELETE FROM EventLog2 WHERE SourceID = @IONSourceID

	DELETE FROM SourceQuantity WHERE SourceID = @IONSourceID

	-- delete from the supporting tables
	DELETE FROM Channel WHERE RecorderID IN (SELECT ID FROM Recorder WHERE SourceId=@IONSourceID) 
	DELETE FROM RecorderState WHERE RecorderId In (SELECT ID FROM Recorder WHERE SourceId = @IONSourceID) 
	DELETE FROM Recorder WHERE SourceId=@IONSourceID
	DELETE FROM SourceQuantity WHERE SourceId=@IONSourceID
	DELETE FROM SourceState WHERE SourceID=@IONSourceID
	DELETE FROM Device WHERE ID =@IONSourceID
	DELETE from PQ_EventsToReprocess where EventId in (select EventId from PQ_Events where SourceId = @IONSourceID)
	DELETE from PQ_IncidentEventMap where EventId in (select EventId from PQ_Events where SourceId = @IONSourceID)
	DELETE from PQ_Events where SourceId = @IONSourceID
	DELETE FROM SourceState WHERE SourceID=@IONSourceID 
	DELETE from SourceQuantity where SourceID = @IONSourceID
	DELETE FROM Source WHERE ID=@IONSourceID 
	
	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	
	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
USE ION_Network
SELECT @NOMsourceID = SourceID FROM SRC_Source WHERE Name = @VirtualMeterName

BEGIN TRY
	
	BEGIN TRAN

	Delete from EVT_EventHistory where SourceId = @NOMSourceID
	DELETE FROM ION_Network..IAS_MeasurementAddress WHERE SourceID = @NOMSourceID
	DELETE FROM ION_Network..SRC_Source WHERE SourceID = @NOMSourceID
	-- 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 Segment2 like @VirtualMeterName + '/%'
	

	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	
	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
Use ApplicationModules

DECLARE
@LogicalID bigint,
@AMDeviceID bigint

-- get the IDs
SELECT @LogicalID = LogicalDeviceID from DataSourceBus.DataSourceDevice where DataSourceRepresentation = @VirtualMeterName
SELECT @AMDeviceID = DatasourceDeviceID from DataSourceBus.DataSourceDevice where DataSourceRepresentation = @VirtualMeterName


-- 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 DataSourceBus.DataSourceDevice WHERE DataSourceRepresentation = @VirtualMeterName

DECLARE @DeviceMetadataTargetTypeId INT
SET @DeviceMetadataTargetTypeId = (SELECT TargetTypeId FROM Metadata.MetadataTargetType WHERE TargetType = N'Device')

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 metadata.MetadataItemValue 
	where MetadataItemId in
	(select MetadataItemId from metadata.MetadataItem 
	where TargetId = @AMDeviceID
	and TargetTypeId = @DeviceMetadataTargetTypeId)

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


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

	-- delete from DataSourceBus tables

	DELETE FROM DataSourceBus.SampleAvailability WHERE DataSourceDeviceId = @AMDeviceID
	DELETE FROM DataSourceBus.Stream WHERE LogicalDeviceId = @LogicalID
	DELETE FROM DataSourceBus.LogicalDevice WHERE LogicalDeviceId = @LogicalID
	DELETE FROM DataSourceBus.DataSourceDevice WHERE DataSourceRepresentation = @VirtualMeterName
	
	COMMIT
END TRY

BEGIN CATCH
	ROLLBACK
	
	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



