--select getdate()

declare @minId int, @d1 datetime =dateadd(month,-2,getutcdate());
declare @d2 datetime =dateadd(hour,2,@d1);

--get the id of a row somewhere in the time we want to get the data . this is done cause we don't have an index on datetime in the table
select top 1 @minid=x.IdDevStatus from DeviceStatusLog x with(nolock) where datetime between @d1 and @d2


DECLARE @tempDevicesStates TABLE (
	panelid int not null,
	[DeviceId] [int] NOT NULL,
	[StateKey] [nvarchar](1024) NOT NULL,
	[Value] [nvarchar](1024) NULL
);

DECLARE @TempDeviceStatusLog table(
	[CpId] [int] NOT NULL,
	[iInstallationId] [int] NOT NULL,
	[Tampered] [bit] NOT NULL,
	[BatteryLow] [int] NOT NULL,
	[RFLevel] [int] NULL ,
	[BatteryLevel] [int] NULL ,
	[datetime] datetime null,
	unpaired bit null)

--getting all the data from the calculated time window and get only latest row for each device
insert into @TempDeviceStatusLog
select ds.cpid,iInstallationId,Tampered,BatteryLow,RFLevel,BatteryLevel,datetime,(case when da.Id is null then 1 else 0 end ) Unpaird
from DeviceStatusLog ds with(nolock)
	left outer join DeviceStates dd on dd.DeviceId=ds.iInstallationId 
	join (select cpid,max(datetime) md from DeviceStatusLog with(nolock) where IdDevStatus>=@minId group by cpid) m on m.CpId=ds.CpId and m.md=ds.DateTime
	left outer join DeviceAttributes da  with(nolock) on da.DeviceId=ds.iInstallationId and AttributeKey='SoftwareVersion'
where (dd.Id is null or  dd.UpdatedUtc<ds.DateTime)
	and ds.IdDevStatus>=@minId
order by cpid,iInstallationId

--select count(1),getdate() from @TempDeviceStatusLog

--convert the data into devicestate table build
insert into @tempDevicesStates (panelid,deviceid,StateKey,[Value])
select ds.cpid,ds.iInstallationId,'Tamper' statekey,case when Tampered=1 then 'true' else 'false' end [value]
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
	group by cpid,ds.iInstallationId,Tampered

union all
select ds.cpid,ds.iInstallationId,'BatteryLevel' statekey,cast(BatteryLevel as nvarchar(5)) [value]
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog   group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
group by cpid,ds.iInstallationId,BatteryLevel

union all
select ds.cpid,ds.iInstallationId,'BatteryStatus' statekey,
		(case when BatteryLow=0 then 'Ok' when BatteryLow=1 then 'LowBattery' else 'EmptyBattery' end)  [value] 
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog   group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
	group by cpid,ds.iInstallationId,BatteryLow,BatteryLow

union all
select ds.cpid,ds.iInstallationId,'RfStrength' statekey,case when RFLevel=-1 then null else cast(RFLevel as nvarchar(5)) end [value] 
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog   group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
	group by cpid,ds.iInstallationId,RFLevel

union all
select ds.cpid,ds.iInstallationId,'SupervisionLost' statekey,case when RFLevel=-1 then 'true' else 'false' end  [value]
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog   group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
	group by cpid,ds.iInstallationId,RFLevel

union all
select ds.cpid,ds.iInstallationId,'UnPaired' statekey,(case when da.Id is null or da.AttributeValue='00.00.00' then 'true' else 'false' end ) [value]
from @TempDeviceStatusLog ds 
	join (select iInstallationId,max(datetime) md from @TempDeviceStatusLog   group by iInstallationId) m on m.iInstallationId=ds.iInstallationId and m.md=ds.DateTime
	left outer join DeviceAttributes da   on da.DeviceId=ds.iInstallationId and AttributeKey='SoftwareVersion'
	group by cpid,ds.iInstallationId,da.id,da.AttributeValue

--select count(1),getdate() from @tempDevicesStates

-- remove rows with null, can't do that within the insert statement, due to preformance issues there
delete from @tempDevicesStates where Value is null

--select count(1),getdate() from @tempDevicesStates

-- remove the devices that do not exist in the installtion table 
delete from @tempDevicesStates where DeviceId not in (select iInstallationId from InstallationTbl)

--select count(1),getdate() from @tempDevicesStates

--merge everything to the devicestate table
MERGE INTO [dbo].DeviceStates AS TARGET  
USING @tempDevicesStates AS SOURCE  
	ON TARGET.deviceid = SOURCE.deviceid and target.statekey=source.statekey
WHEN NOT MATCHED BY TARGET THEN
	INSERT (DeviceId,StateKey,Value)
	VALUES (source.DeviceId,source.StateKey,source.Value)
WHEN MATCHED THEN    
	UPDATE SET	
		target.DeviceId=source.DeviceId,
		target.StateKey=source.StateKey,
		target.Value=source.Value,
		target.updatedutc=getutcdate()
OUTPUT $action, SOURCE.*;
