
/****************************************************************************/
/*	Panel Type Version Ranges												*/
/****************************************************************************/

DECLARE @PanelTypeVersionRanges TABLE (
	[PanelDeviceType] [nvarchar](50) NULL,
	[MinimalVersion] [nvarchar](50) NULL,
	[MaximalVersion] [nvarchar](50) NULL,
	[guid] [uniqueidentifier] NOT NULL
)

insert into @PanelTypeVersionRanges values ('C7000','6.4.1',null,'9B493C41-A492-413C-96A9-6F867B8D0333');
insert into @PanelTypeVersionRanges values ('C7000','6.0.0',null,'9B493C41-A492-413C-96A9-6F867B8D0291');
insert into @PanelTypeVersionRanges values ('CP','2.0.0',NULL   ,'4C2FFB6F-B74B-424A-9808-F09D9FA3E8EB');

MERGE INTO [dbo].PanelTypeVersionRanges AS TARGET  
USING @PanelTypeVersionRanges AS SOURCE  
ON TARGET.[guid] = SOURCE.[guid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([PanelDeviceType],[MinimalVersion] ,[MaximalVersion] ,[guid])
VALUES (SOURCE.[PanelDeviceType],SOURCE.[MinimalVersion] ,SOURCE.[MaximalVersion] ,SOURCE.[guid])
WHEN MATCHED THEN    
UPDATE SET	TARGET.[PanelDeviceType] = source.[PanelDeviceType],
			TARGET.[MinimalVersion]  = source.[MinimalVersion] ,
			TARGET.[MaximalVersion]  = source.[MaximalVersion] ,
			TARGET.[guid] 			 = source.[guid] 

OUTPUT $action, SOURCE.*;


/****************************************************************************/
/*	Panel Command Types														*/
/****************************************************************************/
DECLARE @PanelCommandTypes TABLE (
	[Code] [nvarchar](2) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
	[TotalLength] [int] NOT NULL,
	[CommandCodeName] [nvarchar](50) NULL,
	[guid] [uniqueidentifier] NOT NULL
	)

insert into @PanelCommandTypes values ('TZ', 'Modify Panel Time Zone Data', 'Update the control panel’s time zone settings', 26, 'ModifyPanelTimeZoneData', 'F333E1F6-F9E6-4568-A1D3-E622446B357A');
insert into @PanelCommandTypes values ('DS', 'Send Panel Information', 'Request the control panel to send all its device information.', 0, 'DataSynchronisation', '1EAD1AE7-6506-4B5D-9DD8-431D240C9133');
--insert into @PanelCommandTypes values ('PF', 'Program FTP/FOTA server parameters', 'Configure the address and port of the FTP/FOTA server to which the panel should connect and the authentication credentials it should use in order to download files (using the PX command)', 72, 'ConfigureFTPServerSettings', '060452C0-E24D-430F-A4D7-AA45CE5A8FCC');
--insert into @PanelCommandTypes values ('PX', 'Get File by FTP', 'Request the control panel to take a file from an FTP server and process it', 72, 'GetFileByFTP', '4CE03DC0-791B-40C8-B88E-5ACAD6241123');
insert into @PanelCommandTypes values ('97', 'Reset Device', 'request the panel to reset itself or one of its peripheral devices', 4, 'ResetDevice', '64E7F69C-3F76-41F6-92A2-66694FD396F3');
insert into @PanelCommandTypes values ('LS', 'Send events log file', 'Request the control panel to send unsent events from its events log', 0, 'SendEventsLogFile', '7c80a196-87ae-41dd-865a-b90f66ffa93c');
insert into @PanelCommandTypes values ('VA', 'Play announcement', 'Request the control panel to play an announcement', 4, 'PlayAnnouncement', 'F333E1F6-F9E6-4568-A1D3-E622446B3333');

MERGE INTO [dbo].PanelCommandTypes AS TARGET  
USING @PanelCommandTypes AS SOURCE  
ON TARGET.[guid] = SOURCE.[guid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Code], [Name], [Description], [TotalLength], [CommandCodeName], [guid])
VALUES (SOURCE.[Code], SOURCE.[Name], SOURCE.[Description], SOURCE.[TotalLength], SOURCE.[CommandCodeName], SOURCE.[guid])
WHEN MATCHED THEN    
UPDATE SET 
target.[Code] = SOURCE.[Code], 
target.[Name] = SOURCE.[Name], 
target.[Description] = SOURCE.[Description], 
target.[TotalLength] = SOURCE.[TotalLength], 
target.[CommandCodeName]= SOURCE.[CommandCodeName], 
target.[guid]= SOURCE.[guid]


OUTPUT $action, SOURCE.*;


/****************************************************************************/
/*	Panel Command Argument List Values										*/
/****************************************************************************/
DECLARE @PanelCommandTypePanelTypes TABLE (
	[PanelCommandTypeId] [int] NOT NULL,
	[PanelTypeVersionRangeId] [int] NOT NULL
	)

insert into @PanelCommandTypePanelTypes ([PanelCommandTypeId] ,[PanelTypeVersionRangeId] )
select c.id,r.id
from PanelCommandTypes c, PanelTypeVersionRanges r
where c.[guid] in ('F333E1F6-F9E6-4568-A1D3-E622446B357A','1EAD1AE7-6506-4B5D-9DD8-431D240C9133','64E7F69C-3F76-41F6-92A2-66694FD396F3', '7c80a196-87ae-41dd-865a-b90f66ffa93c')--'060452C0-E24D-430F-A4D7-AA45CE5A8FCC','4CE03DC0-791B-40C8-B88E-5ACAD6241123',
  and r.[guid] in ('9B493C41-A492-413C-96A9-6F867B8D0291','4C2FFB6F-B74B-424A-9808-F09D9FA3E8EB')

  insert into @PanelCommandTypePanelTypes ([PanelCommandTypeId] ,[PanelTypeVersionRangeId] )
select c.id,r.id
from PanelCommandTypes c, PanelTypeVersionRanges r
where c.[guid] in ('F333E1F6-F9E6-4568-A1D3-E622446B3333')
  and r.[guid] in ('9B493C41-A492-413C-96A9-6F867B8D0333')


MERGE INTO [dbo].PanelCommandTypePanelTypes AS TARGET  
USING @PanelCommandTypePanelTypes AS SOURCE  
ON TARGET.[PanelCommandTypeId] = SOURCE.[PanelCommandTypeId] and target.paneltypeversionrangeid =source.paneltypeversionrangeid 
WHEN NOT MATCHED BY TARGET THEN
INSERT ([PanelCommandTypeId],[PanelTypeVersionRangeId])
VALUES (SOURCE.[PanelCommandTypeId], SOURCE.[PanelTypeVersionRangeId])
WHEN MATCHED THEN    
UPDATE SET 
target.[PanelCommandTypeId] = SOURCE.[PanelCommandTypeId], 
target.[PanelTypeVersionRangeId] = SOURCE.[PanelTypeVersionRangeId]


OUTPUT $action, SOURCE.*;



/****************************************************************************/
/*	Panel Command Arguments													*/
/****************************************************************************/
DECLARE @PanelCommandArguments TABLE (
	[CommandId] [int] NOT NULL,
	CommandArgumentName [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Type] [nvarchar](50) NOT NULL,
	[StringLength] [int] NULL,
	[MinValue] [int] NULL,
	[MaxValue] [int] NULL,
	[DataSourceName] [nvarchar](100) NULL,
	[IsMandatory] [bit] NOT NULL,
	[Description] [nvarchar](500) NULL,
	[guid] [uniqueidentifier] NOT NULL
)


insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'TimeZoneCode','Time Zone Code','Calculated',null,0,65534,'GetTimeZoneList',1,null,'780233C4-6564-44A0-A9EC-2CBC12317CDA');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'StandardOffsetfromUTC','Standard Offset from UTC','Int',null,-1440,1440,null,1,null,'92034E18-8403-450D-B734-BD09468E11A4');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'DaylightSavings','Daylight Savings','Int',null,0,1440,null,1,null,'DC0C6AC2-9EE7-488A-B64E-871046AE3A25');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperation','Next Daylight Saving Operation','ListOfValues',null,null,null,null,1,null,'61B1CC1B-49CE-49EE-82E7-2CBF93AC87CD');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'IntervalInDays','Interval in days','Int',null,0,255,null,1,null,'33A689CE-2052-4CC9-A3ED-5A8B05516416');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperationUTCTimeYear','Next Daylight Saving Operation UTC Time Year','Int',null,0,99,null,1,null,'8F4400DC-8C6C-4D07-BE2F-6764ED259B80');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperationUTCTimeMonth','Next Daylight Saving Operation UTC Time Month','Int',null,1,12,null,1,null,'62856D2F-C853-4B19-BB98-9B969FC8A376');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperationUTCTimeDay','Next Daylight Saving Operation UTC Time Day','Int',null,1,31,null,1,null,'6B64EDD4-F0C8-4142-9B71-6689452DD76C');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperationUTCTimeHour','Next Daylight Saving Operation UTC Time Hour','Int',null,0,23,null,1,null,'74329EA9-3473-4F1B-9917-328293C20C15');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B357A'),'NextDaylightSavingOperationUTCTimeMinute','Next Daylight Saving Operation UTC Time Minute','Int',null,0,59,null,1,null,'83ABD0BE-FB16-462D-84F3-E889CE82D21A');

--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='060452C0-E24D-430F-A4D7-AA45CE5A8FCC'),'AddressType','Address Type','ListOfValues',null,null,null,null,1,null,'7820B726-9A66-4192-A540-D747E83A5BEE');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='060452C0-E24D-430F-A4D7-AA45CE5A8FCC'),'Address','Address','Url',43,null,null,null,1,'type 1 - valid ip , type 0 - Valid URL','8C478CFB-62D2-4887-9DA5-F4DF39132791');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='060452C0-E24D-430F-A4D7-AA45CE5A8FCC'),'PortNumber','Port Number','Int',null,0,65535,null,1,null,'A530DBC2-3DD5-4742-AA84-6F4814ADC47B');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='060452C0-E24D-430F-A4D7-AA45CE5A8FCC'),'UserName','User Name','String',12,null,null,null,1,null,'FC8A1F26-F1C7-4FA0-9D4F-CAD77BB20808');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='060452C0-E24D-430F-A4D7-AA45CE5A8FCC'),'Password','Password','String',12,null,null,null,1,null,'B3E20DA8-5817-434F-B4A5-6C79833DFA54');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='4CE03DC0-791B-40C8-B88E-5ACAD6241123'),'FileType','File Type','ListOfValues',null,null,null,null,1,null,'4E8098CF-E8EF-4867-84DB-B5A5CCD7139B');
--insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='4CE03DC0-791B-40C8-B88E-5ACAD6241123'),'FullPathofFile','Full Path of File','String',70,null,null,null,1,null,'D6454EEA-1F5E-45B3-947C-68119CEA5BC8');

insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='64E7F69C-3F76-41F6-92A2-66694FD396F3'),'DeviceType','Device Type','ListOfValues',null,null,null,null,1,null,'39642D60-BCFF-4F91-BDEB-97286E8F724C');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='64E7F69C-3F76-41F6-92A2-66694FD396F3'),'DeviceNumber','Device Number','Int',null,0,20,null,1,'0 is cp','E9B8F69E-9A9E-4D42-A8EC-EF81EC48C939');

insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B3333'),'AnnouncementId','Announcement ID','Int',null,0,999,null,1,'ID of announcement in VAT table','E9B8F69E-9A9E-4D42-A8EC-EF81EC484444');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='F333E1F6-F9E6-4568-A1D3-E622446B3333'),'IgnoreVoiceGroup','Ignore Voice Group','Int',null,0,1,null,1,'0 – Play announcement only if related voice group is enabled, 1 – Play announcement even if related voice group is disabled','E9B8F69E-9A9E-4D42-A8EC-EF81EC484445');

MERGE INTO [dbo].PanelCommandArguments AS TARGET  
USING @PanelCommandArguments AS SOURCE  
ON TARGET.[guid] = SOURCE.[guid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([CommandId],[Name],CommandArgumentName,[Type] ,[StringLength],[MinValue] ,[MaxValue] ,[DataSourceName],[IsMandatory] ,[Description] ,[guid])
VALUES (source.[CommandId],source.[Name],source.CommandArgumentName,source.[Type] ,source.[StringLength],source.[MinValue] ,source.[MaxValue] ,source.[DataSourceName],source.[IsMandatory] ,source.[Description],source.[guid])
WHEN MATCHED THEN    
UPDATE SET 
target.[CommandId]		=source.[CommandId],
target.[Name]			=source.[Name],
target.CommandArgumentName = source.CommandArgumentName,
target.[Type] 			=source.[Type] ,
target.[StringLength]	=source.[StringLength],
target.[MinValue] 		=source.[MinValue] ,
target.[MaxValue] 		=source.[MaxValue] ,
target.[DataSourceName]	=source.[DataSourceName],
target.[IsMandatory] 	=source.[IsMandatory] ,
target.[Description] 	=source.[Description] ,
target.[guid]			=source.[guid]

OUTPUT $action, SOURCE.*;




/****************************************************************************/
/*	Panel Command Type Panel Types											*/
/****************************************************************************/
DECLARE @PanelCommandArgumentListValues TABLE (
	[PanelCommandArgumentId] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Value] [nvarchar](100) NOT NULL
	)

--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='61B1CC1B-49CE-49EE-82E7-2CBF93AC87CD'),'Start DST','0')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='61B1CC1B-49CE-49EE-82E7-2CBF93AC87CD'),'End DST','1')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='7820B726-9A66-4192-A540-D747E83A5BEE'),'Domain Name','0')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='7820B726-9A66-4192-A540-D747E83A5BEE'),'Ipv4 Address','1')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'control panel firmware','00')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'PIR','01')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'IPD (camera)','26')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'SPB (on-wall panic button)','2B')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'SK2 (smoke detector)','37')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'MGLS (magnetic contact)','39')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'EP (pendant panic button)','41')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'VPD (voice panic detector) – main firmware (TI)','44')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'EPA (active pendant emergency button)','45')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'EPP (emergency pendant plus)','49')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'SPD (stationary panic detector)','4A')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'C7000 control panel firmware','4B')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'Essence Smart Lock (TI)','4C')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'VPD2 (voice panic detector) – main firmware (TI)','4D')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'ESL Mansion','EM')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'configurations file','F0')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'voice file','F1')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'VPD2 (voice panic detector 2) – secondary firmware (NXP 114)','V2')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'VPD (voice panic detector) – secondary firmware (NXP)','VP')
--insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='4E8098CF-E8EF-4867-84DB-B5A5CCD7139B'),'GSM module','ZZ')
insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='39642D60-BCFF-4F91-BDEB-97286E8F724C'),'CP','00')
insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='39642D60-BCFF-4F91-BDEB-97286E8F724C'),'Activity Sensor','01')
insert into @PanelCommandArgumentListValues values ((select id from PanelCommandArguments where guid='39642D60-BCFF-4F91-BDEB-97286E8F724C'),'Safety & SOS Device','04')

MERGE INTO [dbo].PanelCommandArgumentListValues AS TARGET  
USING @PanelCommandArgumentListValues AS SOURCE  
ON TARGET.PanelCommandArgumentId = SOURCE.PanelCommandArgumentId and target.description=source.description
WHEN NOT MATCHED BY TARGET THEN
INSERT ([PanelCommandArgumentId], [Value], [Description])
VALUES (SOURCE.[PanelCommandArgumentId], SOURCE.[Value], SOURCE.[Description])
WHEN MATCHED THEN    
UPDATE SET 
target.[PanelCommandArgumentId] = SOURCE.[PanelCommandArgumentId], 
target.[Value] 					= SOURCE.[Value], 
target.[Description]			= SOURCE.[Description]

OUTPUT $action, SOURCE.*;