USE [VIGIL-DVR]
GO

-- Update table schema and database parameters here.

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'tagged_footage') < 1
	ALTER TABLE [tagged_footage] ADD
		[digital_preset] [int] NOT NULL CONSTRAINT [DF_tagged_footage_digital_preset] DEFAULT (-1),
		[physical_preset] [int] NOT NULL CONSTRAINT [DF_tagged_footage_physical_preset] DEFAULT (-1)
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'dio_alarms') < 1
	ALTER TABLE [dio_alarms] ADD
		[email_sleep_enabled] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_email_sleep_enabled]  DEFAULT ((0)),
		[email_sleep_timeout] [int] NOT NULL CONSTRAINT [DF_dio_alarms_email_sleep_timeout]  DEFAULT ((10)),
		[email_last_sent] [datetime] NULL
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'dio_alarms') < 2
	ALTER TABLE [dio_alarms] ADD
		[alarm_priority] [int] NOT NULL DEFAULT((1))
GO

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('marked_footage') AND [name] = 'name')
	ALTER TABLE [marked_footage] ADD
		[name] [nvarchar](50) NULL,
		[notes] [nvarchar](3000) NULL
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'dio_mailsettings') < 1
	ALTER TABLE [dio_mailsettings] ADD
		[dio_mail_video] [int] NULL,
		[dio_mail_prealarm_time] [int] NULL
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'email_notification') < 1
BEGIN
	ALTER TABLE [email_notification] ADD
		[vpos_alarm_flag] [int] NOT NULL DEFAULT ((-1))

	DROP INDEX [email_notification].[ix_email_notification]

	CREATE UNIQUE NONCLUSTERED INDEX [ix_email_notification] ON [dbo].[email_notification]
	(
		[address] ASC,
		[type] ASC,
		[notification_type] ASC,
		[camera] ASC,
		[dio_input] ASC,
		[vpos_exception] ASC,
		[va_rule] ASC,
		[vpos_alarm_flag] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'alarmevents') < 1
	ALTER TABLE [alarmevents] ADD
		[priority] [int] NOT NULL DEFAULT((1))
GO

IF (SELECT [version] FROM [table_version] WHERE [entity] = 'alarmevents') < 2
	ALTER TABLE [alarmevents] ADD
		[notes] nvarchar(3000) NULL
GO

-- Update permissions

-- Add 12300 permission ID before add group permission (fix foreign key problem
IF (SELECT COUNT(*) FROM perms WHERE permission_id = 12300) = 0
    INSERT INTO perms (permission_id, permission_desc_id, permission_sort_order)
    VALUES (12300, 18568, 1720)
GO

IF (SELECT version FROM table_version WHERE entity = 'perms') < 2
	INSERT INTO grouppermission(groupname, permission_id)
	SELECT DISTINCT groupname, 12300 FROM groups
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marked_footage]') AND type in (N'U'))
	CREATE TABLE [dbo].[marked_footage](
		[idx] [bigint] IDENTITY(1,1) NOT NULL,
		[channel] [int] NOT NULL,
		[start_date] [datetime] NOT NULL,
		[end_date] [datetime] NOT NULL,
		[username] [nvarchar](50) NULL,
		[restricted] [bit] NOT NULL CONSTRAINT [DF_marked_footage_restricted]  DEFAULT ((0)),
		[locked] [bit] NOT NULL CONSTRAINT [DF_marked_footage_locked]  DEFAULT ((0)),
		[created_date] [datetime] NOT NULL CONSTRAINT [DF_marked_footage_created_date]  DEFAULT (getdate()),
		[name] [nvarchar](50) NULL,
		[notes] [nvarchar](3000) NULL
	) ON [PRIMARY]
GO


-- Update login
IF (SELECT version FROM table_version WHERE entity = 'db') < 11000000
BEGIN
	ALTER LOGIN [vigil] WITH PASSWORD = 0x0200A7812BAF7EBB20C2E8E56BB762BD4A1355CEBDC136415A33BDEAED7A3EAFC79A955ED0F34C340D036E149790DD1CF0644E564A74B387CC64F05EB0EF7E68C9F4C083FEA5 HASHED
END
GO

-- Update views

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_locked_events]'))
	DROP VIEW [dbo].[vw_locked_events]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_locked_events]
AS
	SELECT e.*
	FROM [events] e WITH (NOLOCK)
	JOIN marked_footage mf ON e.camera_number = mf.channel AND e.start_time <= mf.end_date AND e.end_time >= mf.start_date
	WHERE mf.locked = 1
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_locked_audioevents]'))
	DROP VIEW [dbo].[vw_locked_audioevents]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_locked_audioevents]
AS
	SELECT e.*
	FROM [audioevents] e WITH (NOLOCK)
	JOIN marked_footage mf ON e.start_time <= mf.end_date and e.end_time >= mf.start_date
	WHERE mf.locked = 1
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_alarmevents]'))
	DROP VIEW [dbo].[vw_alarmevents]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_alarmevents]
AS
	SELECT
		alarm_type t,
		alarm_input i,
		alarm_name an,
		alarm_guid ag,
		CAST(((CAST(start_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) d,
		CAST(((CAST(end_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) e,
		camera_numbers c,
		custom_text ct,
		server_local_alarm AS sl,
		[priority] AS p,
		[notes] AS n
	FROM
		alarmevents
GO
