USE [VIGIL-DVR]
GO


-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_settings_snapshot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_settings_snapshot]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_set_misc_data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_set_misc_data]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vigil_del_misc_data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[vigil_del_misc_data]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_shrink_db]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_shrink_db]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_reindex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_reindex]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_get_db_indexes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_get_db_indexes]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_validate_password]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_validate_password]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_validate_logon]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_validate_logon]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_drive_status]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_drive_status]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_alarm_suppression]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_alarm_suppression]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_untag_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_untag_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_timeline_getdaysofvideo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_timeline_getdaysofvideo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_timeline_getdata]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_timeline_getdata]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_timeline_countdaysofvideo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_timeline_countdaysofvideo]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_tag_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_tag_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_email_notification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_email_notification]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_email]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_email]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_schedule_insert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_schedule_insert]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_schedule_get]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_schedule_get]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_schedule_delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_schedule_delete]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_total_records]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_total_records]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_oldest_directory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_oldest_directory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_oldest_audio_directory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_oldest_audio_directory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_directories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_directories]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_audio_directories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_audio_directories]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_save_alarm_event]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_save_alarm_event]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_tagged_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_tagged_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_table]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_table]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_alarms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_alarms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_alarms_no_video]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_alarms_no_video]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_perfmon]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_perfmon]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_event]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_event]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_dio_mailsettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_dio_mailsettings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_dio_ipaddress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_dio_ipaddress]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_dio_camera]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_dio_camera]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_dio_audiochannel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_dio_audiochannel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_dio_alarm]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_dio_alarm]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_audit]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_audit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_audio_event]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_audio_event]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_user_vigilconnect_right]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_user_vigilconnect_right]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_tagged_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_tagged_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_oldest_unacknowledged_err]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_oldest_unacknowledged_err]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_latest_alarmevents]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_latest_alarmevents]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_footage_date_range]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_footage_date_range]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_detailed_footage_date_range]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_detailed_footage_date_range]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_oldest_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_oldest_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_newest_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_newest_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_files_size]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_files_size]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_drive_path_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_drive_path_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_directory_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dio_settings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dio_settings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dio_mailsettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dio_mailsettings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dio_ipaddresses]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dio_ipaddresses]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dio_alarm_cameras]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dio_alarm_cameras]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dio_alarm_audiochannels]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dio_alarm_audiochannels]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_db_version]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_db_version]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_audio_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_audio_directory_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_find_usergroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_find_usergroup]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_find_user]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_find_user]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_vpos_email_notification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_vpos_email_notification]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_invalid_records]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_invalid_records]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_events]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_events]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_directory_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_data_drive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_data_drive]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_audio_events]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_audio_events]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_audio_directory_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_audio_directory_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_clear_dio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_clear_dio]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_check_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_check_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_purge_users]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_purge_users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_purge_groups]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_purge_groups]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_get_users]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_get_users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_get_usergroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_get_usergroup]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_get_perms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_get_perms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_get_groups]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_get_groups]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_get_grouppermission]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_get_grouppermission]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_del_user]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_del_user]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_del_group_perm]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_del_group_perm]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_del_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_del_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_assign_user_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_assign_user_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_assign_group_permission]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_assign_group_permission]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_add_user]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_add_user]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_acc_ctl_add_group]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_acc_ctl_add_group]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_marked_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_marked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_marked_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_marked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_restricted_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_restricted_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_locked_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_locked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_locked_files]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_locked_files]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_dio_email_last_sent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_dio_email_last_sent]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_orphaned_records]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_orphaned_records]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_restricted_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_restricted_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_locked_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_locked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_marked_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_marked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_locked_footage_disk_usage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_locked_footage_disk_usage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_check_tables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_check_tables]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_channel_oldest_events]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_channel_oldest_events]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_channel_oldest_files]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_channel_oldest_files]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_channel_data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_channel_data]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_add_group]
@groupname nvarchar(255)
AS
	SET NOCOUNT ON
	INSERT INTO groups (groupname)
	VALUES (@groupname)

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_add_user]
@username nvarchar(255),
@password nvarchar(255),
@bIsHashed bit = 0
AS
	SET NOCOUNT ON
	DECLARE @user_count int
	DECLARE @hashpass binary(16)

	SELECT @user_count = COUNT(*) FROM users WHERE account_username = @username

	IF @bIsHashed = 0
		SELECT @hashpass = dbo.CSIHASH(@password)
	ELSE
		SELECT @hashpass = CAST(@password AS BINARY(16))

	IF @user_count > 0
		UPDATE users SET account_hashpass = @hashpass WHERE account_username = @username
	ELSE
		INSERT INTO users (account_username, account_hashpass)
		VALUES (@username, @hashpass)

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_assign_group_permission]
@groupname nvarchar(255),
@permission_id int
AS
	SET NOCOUNT ON
	INSERT INTO grouppermission (groupname, permission_id)
	VALUES (@groupname, @permission_id);

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_assign_user_group]
@username nvarchar(255),
@groupname nvarchar(255)
AS
	SET NOCOUNT ON

	DECLARE @usercount int

	SELECT @usercount = COUNT(*) FROM usergroup WHERE username = @username

	IF @usercount > 0
		UPDATE usergroup SET groupname = @groupname WHERE username = @username
	ELSE
		INSERT INTO usergroup (username, groupname)	VALUES (@username, @groupname)

	SELECT @@ERROR AS LastError

	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_del_group]
@groupname nvarchar(255)
AS
	SET NOCOUNT ON
	IF @groupname <> N'Administrators'
		DELETE FROM groups
		WHERE groupname = @groupname;

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_del_group_perm]
@groupname nvarchar(255),
@permission_id int
AS
	SET NOCOUNT ON
	DELETE FROM grouppermission
	WHERE groupname = @groupname
	AND permission_id = @permission_id

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_del_user]
@username nvarchar(255)
AS
	SET NOCOUNT ON
	DELETE FROM users
	WHERE account_username = @username

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_get_grouppermission]
	@filterType int,
	@filterValue nvarchar(255)
	AS
		SELECT permission_id, groupname FROM grouppermission
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_get_groups]
	@filterType int,
	@filterValue nvarchar(255)
	AS
		IF @filterType = 0
			SELECT groupname
			FROM groups
			ORDER BY groupname;
		ELSE IF @filterType = 1
			SELECT groupname
			FROM usergroup
			WHERE username = @filterValue
			ORDER BY groupname;
		ELSE IF @filterType = 2
			SELECT groupname
			FROM grouppermission
			WHERE permission_id = cast(@filterValue AS int)
			ORDER BY groupname;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_get_perms]
@filterType int,
@filterValue nvarchar(255)
AS
	IF @filterType = 0 or @filterValue = 'Administrators'
		SELECT *
		FROM perms
		ORDER BY permission_sort_order
	ELSE IF @filterType = 1
		SELECT DISTINCT p.*
		FROM perms p
		JOIN grouppermission gp on p.permission_id = gp.permission_id
		WHERE gp.groupname IN (
			SELECT groupname
			FROM usergroup
			WHERE username = @filterValue
		)
		ORDER BY p.permission_sort_order
	ELSE IF @filterType = 2
		SELECT p.*
		FROM perms p
		JOIN grouppermission gp on p.permission_id = gp.permission_id
		WHERE gp.groupname = @filterValue
		ORDER BY p.permission_sort_order
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_get_usergroup]
	@filterType int,
	@filterValue nvarchar(255)
	AS
		SELECT username, groupname FROM usergroup
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_get_users]
	@filterType int,
	@filterValue nvarchar(255)
	AS
		IF @filterType = 0
			SELECT DISTINCT account_username, account_hashpass
			FROM users;
		ELSE IF @filterType = 1
			SELECT DISTINCT account_username, account_hashpass
			FROM users
			WHERE account_username IN (
				SELECT DISTINCT username
				FROM usergroup
				WHERE groupname = @filterValue
			);
		ELSE IF @filterType = 2
			SELECT DISTINCT account_username, account_hashpass
			FROM users
			WHERE account_username IN (
				SELECT DISTINCT username
				FROM usergroup
				WHERE groupname IN (
					SELECT groupname
					FROM grouppermission
					WHERE permission_id = cast(@filterValue AS int)
				)
			);
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_purge_groups]
AS
	SET NOCOUNT ON
	DELETE FROM groups

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_acc_ctl_purge_users]
AS
	SET NOCOUNT ON
	DELETE FROM users

	SELECT @@ERROR AS LastError
	SET NOCOUNT OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_check_footage]
@threshold_hours int = 24
AS
	DECLARE @ret as INT
	IF EXISTS(SELECT top 1 idx FROM events WITH (NOLOCK) WHERE end_time between DateAdd(hour, -@threshold_hours, getDate()) and getDate())
	BEGIN
		SET @ret = 1
	END
	ELSE
	BEGIN
		IF EXISTS(SELECT top 1 idx FROM events WITH (NOLOCK))
			SET @ret = 0	-- Make sure there is record in there, not DVR first run
		ELSE
			SET @ret = 1
	END
	SELECT @ret as ret
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_clear_dio]
AS
	DELETE FROM dio_cameras
	DELETE FROM dio_audiochannels
	DELETE FROM dio_ipaddresses
	DELETE FROM dio_mailsettings
	DELETE FROM dio_alarms
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_audio_directory_id]
@id int
AS
	IF (SELECT COUNT(*) FROM audioevents WHERE directory_id = @id) = 0
	BEGIN
		delete from audio_directory where idx = @id
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_audio_directory_id]
@Directory nvarchar(255),
@AllowInsert INT = 0
AS
	SET NOCOUNT ON
	DECLARE @id int
	SELECT @id = 0
	SELECT @id = idx FROM audio_directory WHERE directory = @directory
	IF (@id = 0)
	BEGIN
		IF @AllowInsert > 0
		BEGIN
			INSERT INTO audio_directory (directory) VALUES (@directory)
			SELECT @@identity AS new_idx

			SELECT @id = idx FROM audio_directory WHERE directory = @directory
		END
	END

	return @id
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_audio_events]
@path nvarchar(255)
AS
	DECLARE @directory_id int

	EXECUTE @directory_id = sp_get_audio_directory_id @path
	if @directory_id > 0
	BEGIN
		DELETE
		FROM audioevents
		WHERE directory_id = @directory_id
		AND idx NOT IN (SELECT ae.idx FROM audioevents ae JOIN marked_footage mf ON ae.start_time <= mf.end_date AND ae.end_time >= mf.start_date WHERE mf.locked = 1)

		EXECUTE sp_delete_audio_directory_id @directory_id
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_data_drive]
@drive nvarchar(255),
@bAudio BIT,
@chunk_size INT = 1000,
@start_time DATETIME = NULL,
@end_time DATETIME = NULL
AS
BEGIN
	SET NOCOUNT ON

	IF @bAudio = 1
	BEGIN
		DELETE TOP(@chunk_size) FROM audioevents
		WHERE directory_id in (SELECT idx FROM audio_directory WHERE LEFT(directory, LEN(@drive)) = @drive)
		AND (start_time > @start_time OR @start_time IS NULL)
		AND (start_time < @end_time OR @end_time IS NULL)
	END
	ELSE
	BEGIN
		DELETE TOP(@chunk_size) FROM events
		WHERE directory_id in (SELECT idx FROM directory WHERE LEFT(directory, LEN(@drive)) = @drive)
		AND (start_time > @start_time OR @start_time IS NULL)
		AND (start_time < @end_time OR @end_time IS NULL)
	END

	SELECT @@ROWCOUNT AS DeleteCount
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_directory_id]
@id int
AS
	IF (SELECT COUNT(*) FROM events WHERE directory_id = @id) = 0
	BEGIN
		delete from directory where idx = @id
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_directory_id]
@Directory nvarchar(255),
@AllowInsert INT = 0
AS
	SET NOCOUNT ON
	DECLARE @id int
	SELECT @id = 0
	SELECT @id = idx FROM directory WHERE directory = @directory
	IF (@id = 0)
	BEGIN
		IF @AllowInsert > 0
		BEGIN
			INSERT INTO directory (directory) VALUES (@directory)
			SELECT @@identity AS new_idx

			SELECT @id = idx FROM directory WHERE directory = @directory
		END
	END

	return @id
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_events]
@path nvarchar(255)
AS
	DECLARE @directory_id int

	EXECUTE @directory_id = sp_get_directory_id @path

	if @directory_id > 0
	BEGIN
		BEGIN TRANSACTION
			DELETE
			FROM events
			WHERE directory_id = @directory_id
			AND idx NOT IN (SELECT e.idx FROM events e 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)

			EXECUTE sp_delete_directory_id @directory_id
		COMMIT TRANSACTION
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_invalid_records]
AS
BEGIN
	SET NOCOUNT ON

	declare @countEvents int
	declare @countAudioEvents int
	declare @countMarkedFootage int

	select @countEvents = count(*) from [events] where start_time < '1900-01-01' or end_time < '1900-01-01'
	select @countAudioEvents = count(*) from audioevents where start_time < '1900-01-01' or end_time < '1900-01-01'
	select @countMarkedFootage = count(*) from marked_footage where start_date < '1900-01-01' or end_date < '1900-01-01'

	if @countEvents > 0
		delete from [events] where start_time < '1900-01-01' OR end_time < '1900-01-01'

	if @countAudioEvents > 0
		delete from audioevents where start_time < '1900-01-01' OR end_time < '1900-01-01'

	if @countMarkedFootage > 0
		delete from marked_footage where start_date < '1900-01-01' OR end_date < '1900-01-01'

	select * from
	(
		values
			('events', @countEvents),
			('audioevents', @countAudioEvents),
			('marked_footage', @countMarkedFootage)
	) as t([table], invalid_record_count)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_vpos_email_notification]
@vpos_exception int = -1
AS
	IF @vpos_exception = -1
	BEGIN
		DELETE FROM email_notification WHERE notification_type = 5
	END
	ELSE
	BEGIN
		DELETE FROM email_notification WHERE notification_type = 5 AND vpos_exception = @vpos_exception
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_find_user]
@username nvarchar(50)
AS
	DECLARE @user nvarchar(50)
	SELECT account_username
	FROM users
	WHERE account_username = @username
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_find_usergroup]
@username nvarchar(50)
AS
	DECLARE @user nvarchar(50)
	SELECT groupname
	FROM usergroup
	WHERE username = @username
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_db_version]
AS
	SELECT version
	FROM table_version
	WHERE entity = 'db'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dio_alarm_audiochannels]
@DIO integer
AS
	SELECT dio_audiochannels.dio_channel
	FROM dio_alarms,
		 dio_audiochannels
	WHERE dio_audiochannels.dio_alarms_fk=dio_alarms.idx
	AND dio_alarms.alarm_input = @DIO
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dio_alarm_cameras]
@DIO integer
AS
SELECT dio_cameras.dio_camera AS dio_channel
FROM dio_alarms,
		dio_cameras
WHERE dio_cameras.dio_alarms_fk=dio_alarms.idx
AND dio_alarms.alarm_input = @DIO
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dio_ipaddresses]
@DIO integer
AS
	SELECT dio_ipaddresses.idx AS dio_index,
			dio_ipaddresses.dio_ipaddress,
			dio_ipaddresses.dio_dialup_name,
			dio_ipaddresses.dio_connect_all,
			dio_ipaddresses.dio_guid,
			dio_ipaddresses.dio_sitename,
			dio_ipaddresses.dio_sequence_number
	FROM dio_alarms,
			dio_ipaddresses
	WHERE dio_ipaddresses.dio_alarms_fk=dio_alarms.idx
	AND dio_alarms.alarm_input=@DIO
	ORDER BY dio_ipaddresses.dio_sequence_number
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dio_mailsettings]
@DIO integer
as
	SELECT 	dio_mailsettings.idx as dio_index,
			dio_mailsettings.dio_mail_sender,
			dio_mailsettings.dio_mail_sendername,
			dio_mailsettings.dio_mail_recipient,
			dio_mailsettings.dio_mail_ccrecipient,
			dio_mailsettings.dio_mail_subject,
			dio_mailsettings.dio_mail_body,
			dio_mailsettings.dio_mail_bccrecipient,
			dio_mailsettings.dio_mail_stillshot,
			dio_mailsettings.dio_mail_video,
			dio_mailsettings.dio_mail_prealarm_time
	FROM 		dio_alarms,
			dio_mailsettings
	WHERE 	dio_mailsettings.dio_alarms_fk=dio_alarms.idx
	AND 		dio_alarms.alarm_input=@DIO
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dio_settings]
@DIO integer
AS
	SELECT
		alarm_input,
		dwell_timer,
		normal_open,
		dio_enabled,
		alarm_popup,
		monitor_out,
		audio_notify_type,
		alarm_audio_file,
		auto_ack_timeout,
		schedule_enabled,
		server_push_path,
		server_push_overlay_text,
		server_push_ftp_user_name,
		server_push_ftp_password,
		server_push_ftp_timeout,
		server_push_enabled,
		suppress_email,
		do_number,
		do_pulse,
		predicate_input,
		is_local_alarm,
		email_sleep_enabled,
		email_sleep_timeout,
		email_last_sent,
		alarm_priority
	FROM dio_alarms
	WHERE alarm_input = @DIO
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_drive_path_id]
@path nvarchar(255),
@AllowInsert INT = 0
AS
	SET NOCOUNT ON
	DECLARE @id int
	SELECT @id = 0
	SELECT @id = idx FROM drive_path WHERE path = @path
	IF (@id = 0)
	BEGIN
		IF @AllowInsert > 0
		BEGIN
			INSERT INTO drive_path (path) VALUES (@path)
			SELECT @@identity AS new_idx

			SELECT @id = idx from drive_path WHERE path = @path
		END
	END

	return @id
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_files_size]
@drive_path nvarchar(50),
@partition_type INT
AS
	DECLARE @drive_path_id INT

	EXECUTE @drive_path_id = sp_get_drive_path_id @drive_path
	SELECT (SUM(file_size) / 1024) as TotalFileSize
	FROM events WITH (NOLOCK)
	WHERE drive_path_id = @drive_path_id
	AND partition_type = @partition_type
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_footage_date_range]
@partition_type int,
@include_locked bit = 1
AS
BEGIN
	-- @partition_type corresponds to ATLVigil.EnumPartitionType

	IF @partition_type = 0
		SELECT MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		JOIN drivestatus ds on e.drive_path_id = ds.drive_path_id
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 0
		AND ds.suspect = 0
		AND (@include_locked = 1 OR le.idx is null)
	ELSE IF @partition_type = 1
		SELECT MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 1
		AND (@include_locked = 1 OR le.idx is null)
	ELSE IF @partition_type = 2
		SELECT MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 2
		AND (@include_locked = 1 OR le.idx is null)
	ELSE IF @partition_type = 3
		SELECT MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM audioevents e WITH (NOLOCK)
		JOIN drivestatus ds ON e.drive_path_id = ds.drive_path_id
		LEFT JOIN vw_locked_audioevents le ON e.idx = le.idx
		WHERE ds.suspect = 0
		AND (@include_locked = 1 OR le.idx is null)
	ELSE IF @partition_type = 8
		SELECT MIN(start_date) AS start_time, MAX(end_date) AS end_time
		FROM marked_footage
		WHERE locked = 1
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_detailed_footage_date_range]
@partition_type int,
@include_locked bit = 1
AS
BEGIN
	-- @partition_type corresponds to ATLVigil.EnumPartitionType

	IF @partition_type = 0
		SELECT e.camera_number as channel, MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		JOIN drivestatus ds on e.drive_path_id = ds.drive_path_id
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 0
		AND ds.suspect = 0
		AND (@include_locked = 1 OR le.idx is null)
		GROUP BY e.camera_number
		ORDER BY e.camera_number
	ELSE IF @partition_type = 1
		SELECT e.camera_number as channel, MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 1
		AND (@include_locked = 1 OR le.idx is null)
		GROUP BY e.camera_number
		ORDER BY e.camera_number
	ELSE IF @partition_type = 2
		SELECT e.camera_number as channel, MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM [events] e WITH (NOLOCK)
		LEFT JOIN vw_locked_events le on e.idx = le.idx
		WHERE e.partition_type = 2
		AND (@include_locked = 1 OR le.idx is null)
		GROUP BY e.camera_number
		ORDER BY e.camera_number
	ELSE IF @partition_type = 3
		SELECT e.channel_number as channel, MIN(e.start_time) as start_time, MAX(e.end_time) as end_time
		FROM audioevents e WITH (NOLOCK)
		JOIN drivestatus ds ON e.drive_path_id = ds.drive_path_id
		LEFT JOIN vw_locked_audioevents le ON e.idx = le.idx
		WHERE ds.suspect = 0
		AND (@include_locked = 1 OR le.idx is null)
		GROUP BY e.channel_number
		ORDER BY e.channel_number
	ELSE IF @partition_type = 8
		SELECT channel, MIN(start_date) AS start_time, MAX(end_date) AS end_time
		FROM marked_footage
		WHERE locked = 1
		GROUP BY channel
		ORDER BY channel
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_oldest_footage]
@partition_type int,
@include_locked bit = 1,
@max_rows int = 50
AS
BEGIN
	-- video partitions
	IF @partition_type >= 0 AND @partition_type <= 2
	BEGIN
		SELECT start_time, end_time, e.idx AS event_idx, (d.directory + e.footage_filename) AS footage_file
		FROM [events] e WITH (NOLOCK)
		JOIN drivestatus ds ON e.drive_path_id = ds.drive_path_id
		JOIN directory d ON e.directory_id = d.idx
		WHERE e.partition_type = @partition_type
		AND ds.suspect = 0
		AND (@include_locked = 1 OR e.idx NOT IN (SELECT e.idx 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))
		ORDER BY start_time
		OFFSET 0 ROWS
		FETCH NEXT @max_rows ROWS ONLY
	END

	-- audio partition
	IF @partition_type = 3
	BEGIN
		SELECT start_time, end_time, ae.idx AS event_idx, (ad.directory + ae.footage_filename) AS footage_file
		FROM [audioevents] ae WITH (NOLOCK)
		JOIN drivestatus ds ON ae.drive_path_id = ds.drive_path_id
		JOIN audio_directory ad ON ae.directory_id = ad.idx
		WHERE ds.suspect = 0
		AND (@include_locked = 1 OR ae.idx NOT IN (SELECT ae.idx FROM audioevents ae WITH (NOLOCK) JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1))
		ORDER BY start_time
		OFFSET 0 ROWS
		FETCH NEXT @max_rows ROWS ONLY
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_newest_footage]
@partition_type int,
@include_locked bit = 1,
@max_rows int = 50
AS
BEGIN
	-- video partitions
	IF @partition_type >= 0 AND @partition_type <= 2
	BEGIN
		SELECT start_time, end_time, e.idx AS event_idx, (d.directory + e.footage_filename) AS footage_file
		FROM [events] e WITH (NOLOCK)
		JOIN drivestatus ds ON e.drive_path_id = ds.drive_path_id
		JOIN directory d ON e.directory_id = d.idx
		WHERE e.partition_type = @partition_type
		AND ds.suspect = 0
		AND (@include_locked = 1 OR e.idx NOT IN (SELECT e.idx 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))
		ORDER BY end_time DESC
		OFFSET 0 ROWS
		FETCH NEXT @max_rows ROWS ONLY
	END

	-- audio partition
	IF @partition_type = 3
	BEGIN
		SELECT start_time, end_time, ae.idx AS event_idx, (ad.directory + ae.footage_filename) AS footage_file
		FROM [audioevents] ae WITH (NOLOCK)
		JOIN drivestatus ds ON ae.drive_path_id = ds.drive_path_id
		JOIN audio_directory ad ON ae.directory_id = ad.idx
		WHERE ds.suspect = 0
		AND (@include_locked = 1 OR ae.idx NOT IN (SELECT ae.idx FROM audioevents ae WITH (NOLOCK) JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1))
		ORDER BY end_time DESC
		OFFSET 0 ROWS
		FETCH NEXT @max_rows ROWS ONLY
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_latest_alarmevents]
@lastIdx AS BIGINT = 0,
@include_server_local AS BIT = 0
AS
	SET NOCOUNT ON

	DECLARE @cutoff_dt DATETIME
	DECLARE @lastget_dt DATETIME
  	IF @lastIdx = 0
	BEGIN
		SELECT @lastIdx = MAX(idx) - 16 FROM alarmevents
		SET @cutoff_dt = DATEADD(minute, -2, GetUTCDate())
		SET @lastget_dt = GETUTCDate();
	END
	ELSE
	BEGIN
		SET @cutoff_dt = DATEADD(minute, -60, GetUTCDate())
		SELECT @lastget_dt = start_time FROM alarmevents WHERE idx = @lastIdx
	END

	IF @include_server_local = 1
    BEGIN
		SELECT  idx,
				alarm_type,
				alarm_guid,
				alarm_input,
				alarm_name,
				camera_numbers,
				start_time AS new_start_time,
				end_time AS new_end_time,
				CAST(((CAST(start_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) * 10000 AS old_start_time, -- convert to FILETIME
				CAST(((CAST(end_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) * 10000 AS old_end_time,
				acknowledged,
				custom_text,
				server_local_alarm,
				[priority],
				[notes]
		FROM alarmevents WITH (NOLOCK)
		WHERE (idx > @lastIdx and start_time >= @cutoff_dt) OR end_time >= @lastget_dt ORDER BY idx
    END
    ELSE
	BEGIN
		SELECT  idx,
				alarm_type,
				alarm_guid,
				alarm_input,
				alarm_name,
				camera_numbers,
				start_time AS new_start_time,
				end_time AS new_end_time,
				CAST(((CAST(start_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) * 10000 AS old_start_time, -- convert to FILETIME
				CAST(((CAST(end_time AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) * 10000 AS old_end_time,
				acknowledged,
				custom_text,
				server_local_alarm,
				[priority],
				[notes]
		FROM alarmevents WITH (NOLOCK)
		WHERE ((idx > @lastIdx and start_time >= @cutoff_dt) OR end_time >= @lastget_dt) AND (server_local_alarm <> 1 )  ORDER BY idx
    END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_oldest_unacknowledged_err]
@last_ack_idx INT
AS
	SELECT MIN(audit_time) AS audit_time FROM Audit WHERE error_level >= 30 and idx > @last_ack_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_tagged_footage]
@username nvarchar(50),
@limit int = 1000,
@max_idx bigint = 9223372036854775807
AS
BEGIN
	IF @limit = 0 SET @limit = 1000
	IF @max_idx = 0 SET @max_idx = 9223372036854775807

	DECLARE @total_records int

	IF LEN(@username) > 0
		BEGIN
			SELECT @total_records = COUNT(idx) FROM tagged_footage WHERE username = @username
			SELECT TOP(@limit) *, @total_records as total_records FROM tagged_footage WHERE username = @username AND idx <= @max_idx ORDER BY idx DESC
		END
	ELSE
		BEGIN
			SELECT @total_records = COUNT(idx) FROM tagged_footage
			SELECT TOP(@limit) *, @total_records as total_records FROM tagged_footage WHERE idx <= @max_idx ORDER BY idx DESC
		END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_user_vigilconnect_right]
AS
BEGIN
	SET NOCOUNT ON

	SELECT ug.username, (CASE WHEN ug.groupname = 'Administrators' THEN 1 ELSE ISNULL(gp.permission_id, 0) END) AS permission
	FROM usergroup ug LEFT JOIN grouppermission gp
	ON ug.groupname = gp.groupname AND gp.permission_id = 11700
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_audio_event]
@ChannelNumber integer,
@StartTime datetime,
@EndTime datetime,
@TheFilename nvarchar(20),
@FileDirectory nvarchar(200),
@NumClips integer,
@SampleRates integer
AS
    SET NOCOUNT ON

	DECLARE @drive_path nvarchar(200)
	DECLARE @directory_id int
	DECLARE @drive_path_id int
	SELECT @drive_path = LEFT(@FileDirectory, patindex('%\Audio\%', @FileDirectory))

	IF @drive_path = ''
	SELECT @drive_path = LEFT(@FileDirectory, patindex('%/Audio/%', @FileDirectory))
	
	EXECUTE @drive_path_id = sp_get_drive_path_id @drive_path, 1
	EXECUTE @directory_id = sp_get_audio_directory_id @FileDirectory, 1

	INSERT INTO audioevents
	(channel_number, start_time, end_time, footage_filename, drive_path_id, directory_id, footage_sample_rates, footage_number_clips)
	VALUES
	(@ChannelNumber, @StartTime, @EndTime, @TheFilename, @drive_path_id, @directory_id, @SampleRates, @NumClips)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_audit]
@source NVARCHAR(64),
@message NVARCHAR(512),
@audit_time DATETIME,
@error_level INT,
@remote_ip NVARCHAR(256),
@function_name NVARCHAR(64),
@error_code INT,
@error_description NVARCHAR(256),
@state NVARCHAR(512)
AS
	SET @remote_ip = RTRIM(LTRIM(@remote_ip))

	IF @remote_ip = '' SET @remote_ip = '127.0.0.1'

	INSERT INTO [VIGIL-DVR].[dbo].[Audit]
	(source, message, audit_time, error_level, remote_ip, function_name, error_code, error_description, state)
	VALUES
	(@source, @message, @audit_time, @error_level, @remote_ip, @function_name, @error_code, @error_description, @state)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_dio_alarm]
@Alarm integer,
@Dwell integer,
@NormalOpen bit,
@DioEnabled bit,
@AlarmPopup bit,
@MonitorOut integer,
@AudioNotifyType integer,
@AlarmAudioFile nvarchar(256),
@AutoAckTimeout integer,
@ScheduleEnabled bit,
@ServerPushPath nvarchar(256),
@ServerPushOverlayText bit,
@ServerPushFTPUserName nvarchar(256),
@ServerPushFTPPassword nvarchar(256),
@ServerPushFTPTimeout int,
@ServerPushEnabled bit,
@SuppressEmail bit,
@DO_Number integer,
@DO_Pulse bit,
@predicate_input integer,
@LocalAlarm bit,
@EmailSleepEnabled bit = 0,
@EmailSleepTimeout int = 10,
@AlarmPriority int = 1
AS
    SET NOCOUNT ON

	INSERT INTO dio_alarms
		(alarm_input, dwell_timer, normal_open, dio_enabled, alarm_popup, monitor_out, audio_notify_type, alarm_audio_file, auto_ack_timeout, schedule_enabled, server_push_path, server_push_overlay_text, server_push_ftp_user_name, server_push_ftp_password, server_push_ftp_timeout, server_push_enabled, suppress_email, do_number, do_pulse, predicate_input, is_local_alarm, email_sleep_enabled, email_sleep_timeout, alarm_priority)
	VALUES
		(@Alarm, @Dwell, @NormalOpen, @DioEnabled, @AlarmPopup, @MonitorOut, @AudioNotifyType, @AlarmAudioFile, @AutoAckTimeout, @ScheduleEnabled, @ServerPushPath, @ServerPushOverlayText, @ServerPushFTPUserName, @ServerPushFTPPassword, @ServerPushFTPTimeout, @ServerPushEnabled, @SuppressEmail, @DO_Number, @DO_Pulse, @predicate_input, @LocalAlarm, @EmailSleepEnabled, @EmailSleepTimeout, @AlarmPriority)

	SELECT @@identity AS new_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_dio_audiochannel]
@AlarmIdx integer,
@Channel integer
AS
    SET NOCOUNT ON

	INSERT INTO dio_audiochannels (dio_alarms_fk, dio_channel)
			VALUES(@AlarmIdx, @Channel)
	SELECT @@identity AS new_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_dio_camera]
@AlarmIdx integer,
@Channel integer
AS
    SET NOCOUNT ON

	INSERT INTO dio_cameras (dio_alarms_fk, dio_camera)
			VALUES(@AlarmIdx, @Channel)
	SELECT @@identity AS new_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_dio_ipaddress]
@AlarmIdx integer,
@IpAddress nvarchar(64),
@IpDialupName nvarchar(64),
@ConnectAll bit,
@SequenceNumber integer,
@guid nvarchar(64),
@SiteName nvarchar(64)
AS
    SET NOCOUNT ON

	INSERT INTO dio_ipaddresses (dio_alarms_fk, dio_ipaddress, dio_dialup_name, dio_connect_all, dio_sequence_number, dio_guid, dio_sitename)
			VALUES(@AlarmIdx, @IpAddress, @ipDialupName, @ConnectAll, @SequenceNumber, @guid, @SiteName)
	SELECT @@identity AS new_idx
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_dio_mailsettings]
@AlarmIdx integer,
@MailSender text,
@MailSenderName text,
@MailRecipient text,
@MailCCRecipient text,
@MailSubject text,
@MailBody text,
@MailBccRecipient text,
@MailStillShot integer,
@MailVideo integer,
@PreAlarmTime integer
as
BEGIN
	DECLARE @new_idx BIGINT

	SET NOCOUNT ON

	INSERT INTO dio_mailsettings (dio_alarms_fk, dio_mail_sender, dio_mail_sendername,dio_mail_recipient, dio_mail_ccrecipient, dio_mail_subject, dio_mail_body, dio_mail_bccrecipient, dio_mail_stillshot, dio_mail_video, dio_mail_prealarm_time)
			VALUES(@AlarmIdx, @MailSender, @MailSenderName,@MailRecipient, @MailCCRecipient, @MailSubject, @MailBody, @MailBccRecipient, @MailStillShot, @MailVideo, @PreAlarmTime)

	SET @new_idx = @@identity

	SELECT @new_idx AS new_idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_email]
@address nvarchar(100)
AS
BEGIN
	BEGIN TRY
		INSERT INTO email (address) VALUES (@address)
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_email_notification]
@address nvarchar(100),
@type int,
@notification_type int,
@enabled bit,
@camera int = -1,
@dio_input int = -1,
@vpos_exception int = -1,
@va_rule int = -1,
@vpos_alarm_flag int = -1
AS
BEGIN
	BEGIN TRY
		INSERT INTO email_notification (address, type, notification_type, enabled, camera, dio_input, vpos_exception, va_rule, vpos_alarm_flag)
			VALUES (@address, @type, @notification_type, @enabled, @camera, @dio_input, @vpos_exception, @va_rule, @vpos_alarm_flag)
	END TRY
	BEGIN CATCH
	END CATCH
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_event]
@CameraNumber integer,
@StartTime DATETIME,
@EndTime DATETIME,
@TheFilename nvarchar(20),
@FileDirectory nvarchar(200),
@NumImages integer,
@RecordConstant bit,
@RecordMotion bit,
@RecordAlarm bit,
@FileSize bigint,
@PartitionType integer,
@AlarmMask integer
AS
	SET NOCOUNT ON

	DECLARE @directory_id int
	DECLARE @drive_path_id int
	DECLARE @drive_path nvarchar(200)
	SELECT @drive_path = LEFT(@FileDirectory, patindex('%\Data\%', @FileDirectory))
	
	IF @drive_path = ''  -- Linux file path
		SELECT @drive_path = LEFT(@FileDirectory, patindex('%/Data/%', @FileDirectory))

	EXECUTE @drive_path_id = sp_get_drive_path_id @drive_path, 1

	BEGIN TRANSACTION
		EXECUTE @directory_id = sp_get_directory_id @FileDirectory, 1
		INSERT INTO [events]
		(camera_number, start_time, end_time, footage_filename, drive_path_id, directory_id, footage_number_images, record_constant, record_motion, record_alarm, file_size, partition_type, alarm_mask)
		VALUES
		(@CameraNumber, @StartTime, @EndTime, @TheFilename, @drive_path_id, @directory_id, @NumImages, @RecordConstant, @RecordMotion, @RecordAlarm, @FileSize, @PartitionType, @AlarmMask)
	COMMIT TRANSACTION
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_perfmon]
@server_Time DateTime,
@cpu_usage int,
@mem_usage int,
@mem_available int,
@mem_total int,
@vigil_cpu_usage int,
@vigil_private_bytes int,
@vigil_handle_count int,
@vigil_thread_count int,
@vigil_virtual_bytes int
AS
BEGIN TRANSACTION
	SET NOCOUNT ON

	DECLARE @min INT
	DECLARE @max INT
	DECLARE @count INT

	SELECT @min = MIN(idx) FROM [VIGIL-DVR].[dbo].[perfmon] WITH (TABLOCKX)
	SELECT @max = MAX(idx) FROM [VIGIL-DVR].[dbo].[perfmon]
	SELECT @count = @max - @min + 1

	IF @count >= 50000
	BEGIN
		DELETE FROM [VIGIL-DVR].[dbo].[perfmon] WHERE idx <= (@max - 49000)
	END

	INSERT INTO [VIGIL-DVR].[dbo].[perfmon]
	(server_time, cpu_usage, mem_usage, mem_available, mem_total, vigil_cpu_usage, vigil_private_bytes, vigil_handle_count, vigil_thread_count, vigil_virtual_bytes)
	VALUES
	(@server_time, @cpu_usage, @mem_usage, @mem_available, @mem_total, @vigil_cpu_usage, @vigil_private_bytes, @vigil_handle_count, @vigil_thread_count, @vigil_virtual_bytes)

	SELECT @@identity AS new_idx
COMMIT TRANSACTION
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_alarms]
@delete_from_time DATETIME = NULL,
@delete_to_time DATETIME = NULL
AS
	IF @delete_from_time IS NULL AND @delete_to_time IS  NULL
	BEGIN
		TRUNCATE TABLE alarmevents
	END
	ELSE IF @delete_from_time IS NULL
	BEGIN
		DELETE FROM alarmevents
		WHERE start_time <= @delete_to_time
	END
	ELSE IF @delete_to_time IS NULL
	BEGIN
		DELETE FROM alarmevents
		WHERE start_time >= @delete_from_time
	END
	ELSE
	BEGIN
		DELETE FROM alarmevents
		WHERE start_time >= @delete_from_time AND start_time <= @delete_to_time
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_prune_alarms_no_video
	@start_time datetime = null,
	@end_time datetime = null
AS
BEGIN
	-- Delete alarmevent records that fall outside of recorded footage range.
	-- Optionally delete alarmevent records for a specified date range if no video exists.
	
	DELETE FROM alarmevents WHERE dateadd(minute, datediff(minute, getutcdate(), getdate()), end_time) < (SELECT coalesce(min(start_time), '3000-01-01') FROM [events])
	DELETE FROM alarmevents WHERE dateadd(minute, datediff(minute, getutcdate(), getdate()), start_time) > (SELECT coalesce(max(end_time), '1900-01-01') FROM [events])

	DECLARE @start_time_utc datetime
	DECLARE @end_time_utc datetime

	IF @start_time IS NOT NULL AND @end_time IS NOT NULL AND @start_time <= @end_time
	BEGIN
		SET @start_time_utc = dateadd(minute, datediff(minute, getdate(), getutcdate()), @start_time)
		SET @end_time_utc = dateadd(minute, datediff(minute, getdate(), getutcdate()), @end_time)

		IF NOT EXISTS (SELECT * FROM [events] WHERE @start_time <= end_time AND @end_time >= start_time)
		BEGIN
			DELETE FROM alarmevents WHERE start_time >= @start_time_utc AND end_time <= @end_time_utc
		END
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_table]
@table nvarchar(30),
@maxcount INT
AS
BEGIN
	DECLARE @max BIGINT
	DECLARE @min BIGINT
	DECLARE @count BIGINT

	IF @table = 'audit'
	BEGIN
		SELECT @count = COUNT(idx) FROM audit

		IF @count > @maxcount
		BEGIN
			DELETE FROM audit WHERE idx IN (SELECT TOP (@count - @maxcount) idx FROM audit ORDER BY idx)
		END
	END
	ELSE IF @table = 'alarmevents'
	BEGIN
		SELECT @count = COUNT(idx) FROM alarmevents

		IF @count > @maxcount
		BEGIN
			DELETE FROM alarmevents WHERE idx IN (SELECT TOP (@count - @maxcount) idx FROM alarmevents ORDER BY idx)
		END
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_tagged_footage]
AS
BEGIN
DELETE tagged_footage WHERE idx NOT IN
	(
		SELECT DISTINCT tf.idx
		FROM tagged_footage tf
		JOIN [events] e ON tf.camera_number = e.camera_number
		WHERE tf.start_time <= e.end_time AND tf.end_time >= e.start_time
	)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_save_alarm_event]
@alarm_guid NVARCHAR(40),
@alarm_type INT,
@alarm_input INT,
@alarm_name NVARCHAR(50),
@camera_numbers NVARCHAR(640),
@acknowledged BIT,
@start_time DATETIME,
@end_time DATETIME = NULL,
@custom_text TEXT = NULL,
@server_local_alarm BIT = 0,
@priority INT = 1
AS
	SET NOCOUNT ON

	IF (SELECT COUNT(idx) FROM alarmevents WHERE alarm_guid = @alarm_guid) = 0
	BEGIN
		BEGIN
			INSERT INTO alarmevents
				(alarm_type, alarm_guid, alarm_input, alarm_name, camera_numbers, acknowledged, start_time, end_time, custom_text, server_local_alarm, [priority])
			VALUES
				(@alarm_type, @alarm_guid, @alarm_input, @alarm_name, @camera_numbers, @acknowledged, @start_time, @end_time, @custom_text, @server_local_alarm, @priority)
		END
	END
	ELSE
	BEGIN
		UPDATE alarmevents SET
			alarm_type = @alarm_type,
			alarm_input = @alarm_input,
			alarm_name = @alarm_name,
			camera_numbers = @camera_numbers,
			acknowledged = @acknowledged,
			start_time = @start_time,
			end_time = @end_time,
			custom_text = @custom_text,
			server_local_alarm = @server_local_alarm,
			[priority] = @priority
		WHERE alarm_guid = @alarm_guid
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_audio_directories]
@dir nvarchar(200),
@bOnBackupDrive bit
AS
	DECLARE @drive_path_id INT

	EXECUTE @drive_path_id = sp_get_drive_path_id @dir

	if (SELECT Count(*) FROM drivestatus ds
		WHERE ds.drive_path_id = @drive_path_id
		AND ds.is_backup_drive = @bOnBackupDrive) > 0
	BEGIN
		SELECT DISTINCT d.directory AS directory
		FROM audioevents e, audio_directory d
		WHERE e.directory_id = d.idx
		AND e.drive_path_id = @drive_path_id
		ORDER BY d.directory
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_directories]
@dir nvarchar(200),
@bOnBackupDrive bit
AS
	DECLARE @drive_path_id INT

	EXECUTE @drive_path_id = sp_get_drive_path_id @dir

	if (SELECT Count(*) FROM drivestatus ds
		WHERE ds.drive_path_id = @drive_path_id
		AND ds.is_backup_drive = @bOnBackupDrive) > 0
	BEGIN
		SELECT DISTINCT d.directory AS directory
		FROM events e, directory d
		WHERE e.directory_id = d.idx
		AND e.drive_path_id = @drive_path_id
		ORDER BY d.directory
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_oldest_audio_directory]
AS
	SELECT TOP(1) d.directory, ae.start_time
	FROM audioevents ae JOIN drivestatus ds ON ae.drive_path_id = ds.drive_path_id
	JOIN audio_directory d on d.idx = ae.directory_id
	WHERE ds.suspect = 0
	AND ae.idx NOT IN (SELECT ae.idx FROM audioevents ae JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1)
	ORDER BY ae.start_time
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_oldest_directory]
@partition_type INT,
@drive_path nvarchar(255)
AS
	IF @partition_type = 0 AND len(@drive_path) > 0
	BEGIN
		DECLARE @drive_path_id INT

		EXECUTE @drive_path_id = sp_get_drive_path_id @drive_path

		SELECT TOP(1) d.directory, e.start_time
		FROM events e JOIN directory d ON e.directory_id = d.idx
		JOIN drivestatus ds on e.drive_path_id = ds.drive_path_id
		WHERE e.partition_type = 0
		AND ds.suspect = 0
		AND ds.drive_path_id = @drive_path_id
		AND e.idx NOT IN (SELECT e.idx FROM events e 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)
		ORDER BY e.start_time
	END
	ELSE
	BEGIN
		SELECT TOP(1) d.directory, e.start_time
		FROM events e JOIN drivestatus ds on e.drive_path_id = ds.drive_path_id
		JOIN directory d on e.directory_id = d.idx
		WHERE e.partition_type = @partition_type
		AND ds.suspect = 0
		AND e.idx NOT IN (SELECT e.idx FROM events e 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)
		ORDER BY e.start_time
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_total_records]
@partition_type INT
AS
	IF @partition_type = 3
	BEGIN
		SELECT count(*) as records
		FROM audioevents
	END
	ELSE
	BEGIN
		SELECT count(*) as records
		FROM events
		WHERE partition_type = @partition_type
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_schedule_delete]
@nCam INT,
@nType INT
AS
    DELETE FROM schedule WHERE camera_number = @nCam AND schedule_type = @nType;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_schedule_get]
@camera INT,
@type INT
AS
	SELECT idx, dayofweek, start_time, stop_time, record_mode
	FROM schedule
	WHERE camera_number = @camera AND schedule_type = @type
	ORDER BY dayofweek, start_time
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_schedule_insert]
@nCam int,
@nDay int,
@nStart int,
@nStop int,
@nMode int,
@nType int
AS
	INSERT INTO schedule
		(camera_number, dayofweek, start_time, stop_time, record_mode, schedule_type)
	VALUES
		(@nCam, @nDay, @nStart, @nStop, @nMode, @nType);
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_tag_footage]
@idx bigint,
@camera_number int,
@start_time datetime,
@end_time datetime,
@cue_time datetime,
@description nvarchar(100),
@notes nvarchar(3000),
@username nvarchar(50),
@digital_preset int = -1,
@physical_preset int = -1
AS
BEGIN
	SET NOCOUNT ON

	IF @idx = 0
		BEGIN
			INSERT INTO tagged_footage(camera_number, start_time, end_time, cue_time, description, notes, username, tag_time, digital_preset, physical_preset) VALUES(@camera_number, @start_time, @end_time, @cue_time, @description, @notes, @username, GETDATE(), @digital_preset, @physical_preset)
			SELECT @@identity as idx
		END
	ELSE
		BEGIN
			UPDATE tagged_footage set camera_number = @camera_number, start_time = @start_time, cue_time = @cue_time, description = @description, notes = @notes, username = @username, digital_preset = @digital_preset, physical_preset = @physical_preset WHERE idx = @idx
			SELECT @idx as idx
		END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_timeline_countdaysofvideo]
AS
BEGIN
	select COUNT(distinct DateAdd(dd, DateDiff(dd, 0, start_time), 0)) as [count]
	from events
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_timeline_getdata]
@date datetime,
@camera int = -1
AS
BEGIN
	declare @start datetime, @end datetime

    	set @start = DateAdd(dd, DateDiff(dd, 0, @date), 0)
    	set @end = DateAdd(dd, 1, DateAdd(ss, -1, @start))

	select camera_number, start_time, end_time
	from events
    	where (@camera = -1 or camera_number = @camera)
	and start_time <= @end
   	and end_time >= @start
    	order by camera_number, start_time
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_timeline_getdaysofvideo]
AS
BEGIN
	select distinct DateAdd(dd, DateDiff(dd, 0, start_time), 0) as [date]
	from events
	order by [date]
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_untag_footage]
@idx bigint
AS
BEGIN
	DELETE FROM tagged_footage WHERE idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_alarm_suppression]
@alarm_type INT,
@alarm_input INT,
@expiration_utc DATETIME,
@remove BIT
AS
	SET NOCOUNT ON

	IF @remove = 0
	BEGIN
		IF (SELECT COUNT(idx) FROM alarmsuppression WHERE alarm_type = @alarm_type AND alarm_input = @alarm_input) = 0
		BEGIN
			BEGIN
				INSERT INTO alarmsuppression
					(alarm_type, alarm_input, expiration_utc)
				VALUES
					(@alarm_type, @alarm_input, @expiration_utc)
			END
		END
		ELSE
		BEGIN
			UPDATE alarmsuppression
			SET expiration_utc = @expiration_utc
			WHERE alarm_type = @alarm_type
			AND alarm_input = @alarm_input
		END
	END
	ELSE
	BEGIN
		DELETE FROM alarmsuppression WHERE alarm_type = @alarm_type AND alarm_input = @alarm_input
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_drive_status]
@drive_path nvarchar (255),
@is_backup_drive BIT,
@suspect BIT,
@partition_alarm INT,
@partition_pos INT
AS
	BEGIN TRANSACTION

	DECLARE @drive_path_id INT
	DECLARE @partition_alarm_old INT
	DECLARE @partition_pos_old INT

	EXECUTE @drive_path_id = sp_get_drive_path_id @drive_path, 1
	SELECT @partition_alarm_old = partition_alarm, @partition_pos_old = partition_pos
	FROM drivestatus
	WHERE drive_path_id = @drive_path_id

	DELETE FROM drivestatus
	WITH (ROWLOCK, UPDLOCK)
	WHERE drive_path_id = @drive_path_id

	INSERT INTO drivestatus
	(drive_path_id, is_backup_drive, suspect, partition_alarm, partition_pos)
	VALUES
	(@drive_path_id, @is_backup_drive, @suspect, @partition_alarm, @partition_pos)

	COMMIT TRANSACTION

	-- Partition type changed
	IF @partition_alarm_old > 0 AND @partition_alarm = 0
	BEGIN
		UPDATE events SET partition_type = 0
		WHERE partition_type = 1 AND drive_path_id = @drive_path_id
	END
	IF @partition_pos_old > 0 AND @partition_pos = 0
	BEGIN
		UPDATE events SET partition_type = 0
		WHERE partition_type = 2 AND drive_path_id = @drive_path_id
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_validate_logon]
	@username nvarchar(50),
	@password nvarchar(50),
	@ipaddress nvarchar(50),
	@guid nvarchar(50),
	@computername nvarchar(50),
	@ishashed bit = 0    --- 1 if @password is hashed
AS
    SET NOCOUNT ON

	DECLARE @user nvarchar(50)
	declare @hash nvarchar(16), @i int, @table_h nvarchar(16), @pwd nvarchar(50)
	if @ishashed = 1
	begin
		--- Convert HEX to varbinary(16)
		select @i = 1
			, @hash = N''
			, @table_h = N'0123456789ABCDEF'
			, @pwd = @password

		while @i < datalength(@pwd)
		begin
			select @hash = @hash + nchar((charindex(substring(@pwd,@i,1),@table_h)-1)*16+charindex(substring(@pwd,@i+1,1),@table_h)-1)
			select @i= @i + 2
		end

		SELECT @user = (SELECT account_username
				FROM users
				WHERE account_username = @username
				AND account_hashpass = convert(varbinary(16),@hash))
	end
	else
		SELECT @user = (SELECT account_username
				FROM users
				WHERE account_username = @username
				AND account_hashpass = dbo.CSIHASH(@password))
	-- IF logon succeeded UPDATE the ipaddress table with the clients guid.
	IF @guid != ''
	BEGIN
		UPDATE dio_ipaddresses
		SET dio_guid = @guid
		WHERE dio_ipaddress = @ipaddress
		OR dio_ipaddress = @computername
	END
	IF (SELECT COUNT(*)
	FROM grouppermission gp, perms p
	WHERE gp.permission_id = p.permission_id AND
		gp.groupname IN (
			SELECT groupname
			FROM usergroup
			WHERE username = @user
		)
	) > 0
	BEGIN
		SELECT gp.groupname, gp.permission_id, p.permission_desc_id
		FROM grouppermission gp, perms p
		WHERE gp.permission_id = p.permission_id AND
			groupname IN (
				SELECT groupname
				FROM usergroup
				WHERE username = @user
			);
	END
	ELSE
	BEGIN
		IF @user IS NOT NULL
		BEGIN
			IF (SELECT COUNT(*) FROM usergroup WHERE groupname = 'Administrators' AND username = @user ) > 0
				SELECT 'Administrators' AS groupname, permission_id, permission_desc_id FROM perms
			ELSE
				SELECT ISNULL((SELECT groupname FROM usergroup WHERE username = @user), '') AS groupname
					, -1 AS permission_id
					, -1 AS permission_desc_id
		END
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_validate_password]
	@username nvarchar(50),
	@password nvarchar(50),
	@ishashed bit = 0    --- 1 if @password is hashed
AS
BEGIN
    	SET NOCOUNT ON
	declare @hash nvarchar(16), @i int, @table_h nvarchar(16), @pwd nvarchar(50)
	if @ishashed = 1
	begin
		--- Convert HEX to varbinary(16)
		select @i = 1
			, @hash = N''
			, @table_h = N'0123456789ABCDEF'
			, @pwd = @password

		while @i < datalength(@pwd)
		begin
			select @hash = @hash + nchar((charindex(substring(@pwd,@i,1),@table_h)-1)*16+charindex(substring(@pwd,@i+1,1),@table_h)-1)
			select @i= @i + 2
		end

		SELECT account_username
				FROM users
				WHERE account_username = @username
				AND account_hashpass = convert(varbinary(16),@hash)
	end
	else
	BEGIN
		SELECT account_username
				FROM users
				WHERE account_username = @username
				AND account_hashpass = dbo.CSIHASH(@password)
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_get_db_indexes]
AS
BEGIN
	SET NOCOUNT ON

	create table #results (
		[table] varchar(100),
		[index] varchar(100)
	)

	DECLARE @database varchar(100)
	DECLARE @table varchar(100)
	DECLARE @index varchar(100)

	SELECT @database = DB_NAME()

	DECLARE tableCursor CURSOR FOR
		SELECT name FROM sys.objects
		WHERE [type] = 'U'

	OPEN tableCursor

	FETCH NEXT FROM tableCursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE indexCursor CURSOR FOR
			SELECT name FROM sys.indexes
			WHERE [object_id] = object_id(@table)
			AND (name LIKE 'ix_%' OR name LIKE 'pk_%')

		OPEN indexCursor

		FETCH NEXT FROM indexCursor INTO @index

		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO #results([table], [index]) VALUES(@table, @index)

			FETCH NEXT FROM indexCursor INTO @index
		END

		CLOSE indexCursor
		DEALLOCATE indexCursor

		FETCH NEXT FROM tableCursor INTO @table
	END

	CLOSE tableCursor
	DEALLOCATE tableCursor

	SELECT * FROM #results
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_reindex]
AS
BEGIN
	DECLARE @TableName sysname
	DECLARE @sql nvarchar(1000)
	DECLARE #Table_Name CURSOR FOR SELECT name FROM sys.objects WHERE [type] = 'u' AND OBJECTPROPERTY ([object_id], 'ismsshipped') = 0 AND OBJECTPROPERTY([object_id], 'OwnerID') = USER_ID('dbo')

	OPEN #Table_Name

	FETCH NEXT FROM #Table_Name INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = 'ALTER INDEX ALL ON ' + quotename(@TableName) + ' REBUILD'
		PRINT @sql
		EXEC(@sql)
		FETCH NEXT FROM #Table_Name INTO @TableName
	END

	CLOSE #Table_Name
	DEALLOCATE #Table_Name
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_shrink_db]
AS
BEGIN
	DBCC SHRINKDATABASE ('VIGIL-DVR')
	DBCC SHRINKFILE('VIGIL-DVR', 1)
	DBCC SHRINKFILE('VIGIL-DVR_LOG', 1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_del_misc_data]
	@code_operation nvarchar(30)
	, @table_name nvarchar(50) = NULL
	, @datetime_val datetime = NULL
AS
DECLARE @sql nvarchar(4000), @filter nvarchar(4000)
IF @code_operation = N'DELETE_ALL'
BEGIN
	IF @table_name IS NULL
	BEGIN
		RAISERROR('Vigil_del_misc_data: Table name is missed.', 16, 1)
		RETURN
	END
	IF OBJECT_ID(@table_name) IS NULL
	BEGIN
		SELECT @sql = N'Vigil_del_misc_data: ''' + LTRIM(@table_name) + N''' table doesn''t exist in current database.'
		RAISERROR(@sql, 16, 1)
		RETURN
	END
	SELECT @sql = N'DELETE '+
			N' FROM ' + @table_name
		, @filter = N''
	EXEC(@sql)
	RETURN
END
IF @code_operation = N'DELETE_ALARMSUPPRESSION'
BEGIN
	IF @datetime_val IS NULL
	BEGIN
		RAISERROR('Vigil_del_misc_data: Expiration date is missed.', 16, 1)
		RETURN
	END
	SELECT @sql = N'DELETE'+
			N' FROM [alarmsuppression]'+
			N' WHERE expiration_utc < ''' + CONVERT(nvarchar(30), @datetime_val, 109) + ''''+
			N' AND NOT expiration_utc IS NULL'
		, @filter = N''
	EXEC(@sql)
	RETURN
END
IF @code_operation = N'DELETE_ALARMSUPPRESSION_ALL'
BEGIN
	SELECT @sql = N'DELETE'+
			N' FROM [alarmsuppression]'
		, @filter = N''
	EXEC(@sql)
	RETURN
END
RAISERROR('Vigil_del_misc_data: Wrong code operation is used', 16, 1)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_set_misc_data]
	@code_operation nvarchar(30)
	, @table_name nvarchar(50) = NULL
	, @int_val int = NULL
	, @char_val nvarchar(512) = NULL
AS
IF @code_operation = N'INSERT_CAM_NAME'
BEGIN
	INSERT INTO cam_name (cam_num, name)
	VALUES (@int_val, @char_val)
	RETURN
END
RAISERROR('Vigil_set_misc_data: Wrong code operation is used', 16, 1)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[vigil_settings_snapshot]
	@operation nvarchar(50)
	, @guid uniqueidentifier
	, @user nvarchar(50)
	, @table nvarchar(128) = N''
AS
set nocount on
declare @snapshot nvarchar(64), @table_name nvarchar(64), @sql nvarchar(4000)
if @operation = 'GET_SNAPSHOT'
begin
	declare tmp_list cursor for
	select 'dio_alarms_snapshot' as snaphot, 'dio_alarms' as [table_name] union
	select 'dio_cameras_snapshot', 'dio_cameras' union
	select 'dio_audiochannels_snapshot', 'dio_audiochannels' union
	select 'dio_ipaddresses_snapshot', 'dio_ipaddresses' union
	select 'dio_mailsettings_snapshot', 'dio_mailsettings' union
	select 'users_snapshot', 'users' union
	select 'groups_snapshot', 'groups' union
	select 'usergroup_snapshot', 'usergroup' union
	select 'grouppermission_snapshot', 'grouppermission' union
	select 'schedule_snapshot', 'schedule' union
	select 'email_notification_snapshot', 'email_notification' union
	select 'audit_report_criterias_snapshot', '[VIGIL-Audit].dbo.audit_report_criterias' union
	select 'audit_report_criteria_types_snapshot', '[VIGIL-Audit].dbo.audit_report_criteria_types' union
	select 'audit_users_snapshot', '[VIGIL-Audit].dbo.audit_users'
	open tmp_list
	fetch next from tmp_list
	into @snapshot, @table_name
	while @@fetch_status = 0
	begin
		select @sql = N'
if object_id(''' + @snapshot + N''') is not null
	drop table ' + @snapshot + N'
select *, client_id = ''' + convert(varchar(64),@guid) + N'''
into ' + @snapshot + N'
from '+ @table_name
		exec(@sql)
		fetch next from tmp_list
		into @snapshot, @table_name
	end
	close tmp_list
	deallocate tmp_list
	return
end
if @operation = N'COMPARE_SNAPSHOT'
begin
	declare @where nvarchar(4000)
	select @where = N''
	declare tmp_list cursor for
	select 'dio_alarms_snapshot' as snaphot, 'dio_alarms' as [table_name] union
	select 'dio_cameras_snapshot', 'dio_cameras' union
	select 'dio_audiochannels_snapshot', 'dio_audiochannels' union
	select 'dio_ipaddresses_snapshot', 'dio_ipaddresses' union
	select 'dio_mailsettings_snapshot', 'dio_mailsettings' union
	select 'users_snapshot', 'users' union
	select 'groups_snapshot', 'groups' union
	select 'usergroup_snapshot', 'usergroup' union
	select 'grouppermission_snapshot', 'grouppermission' union
	select 'schedule_snapshot', 'schedule' union
	select 'email_notification_snapshot', 'email_notification'
	open tmp_list
	fetch next from tmp_list
	into @snapshot, @table_name
	while @@fetch_status = 0
	begin
		select @sql = N'
declare @cnt int
if object_id(N''' + @snapshot + N''') is NULL
	return
select @cnt = count(*) from ' + @snapshot + N'
if @cnt != (select count(*) from ' + @table_name + N')
	exec [vigil-audit].[dbo].[vigil_audit_set_details] N''' + convert(nvarchar(64),@guid) + N''', N''' + @user + ''',N''VIGIL'',N''settings_change'',N''' + @table_name + N''',N''TableName''
else
	if @cnt != (select count(*)
			from ' + @table_name + N' a, ' + @snapshot + N' b
			where '
		select @sql = @sql + N'(' +
			(case
				when system_type_id=35 then 'substring(a.'+[name] + ',1,4000)=substring(b.'+[name]+',1,4000)'
				else 'a.'+[name] + '=b.'+[name]
			end) +
			case
				when is_nullable <> 0 then ' or ((a.'+[name]+' is NULL) and (b.'+[name]+' is NULL))'
				else ''
			end +
			')'+ nchar(13) +' and '
		from sys.columns
		where [object_id] = object_id(@table_name)
		and is_identity = 0

		select @Sql = left(@sql, len(@sql) - 5 ) + N')
		exec [vigil-audit].[dbo].[vigil_audit_set_details] N'''+convert(nvarchar(64),@guid)+ N''', N''' + @user + N''',N''VIGIL'',N''settings_change'',N''' + @table_name + N''',N''TableName''
'
		exec(@sql)
		fetch next from tmp_list
		into @snapshot, @table_name
	end
	close tmp_list
	deallocate tmp_list
	return
end
IF @operation = N'DELETE_SNAPSHOT'
begin
	declare tmp_list cursor for
	select 'dio_alarms_snapshot' as snaphot union
	select 'dio_cameras_snapshot' union
	select 'dio_audiochannels_snapshot' union
	select 'dio_ipaddresses_snapshot' union
	select 'dio_mailsettings_snapshot' union
	select 'users_snapshot' union
	select 'groups_snapshot' union
	select 'usergroup_snapshot' union
	select 'grouppermission_snapshot' union
	select 'schedule_snapshot' union
	select 'email_notification_snapshot' union
	select 'audit_report_criterias_snapshot' union
	select 'audit_report_criteria_types_snapshot' union
	select 'audit_users_snapshot'
	open tmp_list
	fetch next from tmp_list
	into @snapshot
	while @@fetch_status = 0
	begin
		select @sql = N'
if object_id(N'''+@snapshot+''') is not null
	drop table '+@snapshot+'
'
		exec(@sql)
		fetch next from tmp_list
		into @snapshot
	end
	close tmp_list
	deallocate tmp_list
	return

end
IF @operation = N'COMPARE_SNAPSHOT_TABLE'
BEGIN
declare @eventId smallint
	select @eventId = 0
	select @eventId = idx from [VIGIL-Audit].[dbo].[audit_events] where name = N'settings_change'
	select case when exists(select 1
				from [VIGIL-Audit].[dbo].[audit_details] a
				left join [VIGIL-Audit].[dbo].[audit_details_info] d on d.[audit_details_id]=a.[idx]
				where @guid = a.guid AND d.data = @table AND a.event_id = @eventId) then 1
		else 0 end as value
	return
END
IF @operation = N'GET_ADDED_USERS'
BEGIN
	SELECT account_username FROM users WHERE account_username NOT IN (SELECT account_username FROM users_snapshot)
	RETURN
END
IF @operation = N'GET_DELETED_USERS'
BEGIN
	SELECT account_username FROM users_snapshot WHERE account_username NOT IN (SELECT account_username FROM users)
	RETURN
END
IF @operation = N'GET_ADDED_GROUPS'
BEGIN
	SELECT groupname FROM groups WHERE groupname NOT IN (SELECT groupname FROM groups_snapshot)
	RETURN
END
IF @operation = N'GET_DELETED_GROUPS'
BEGIN
	SELECT groupname FROM groups_snapshot WHERE groupname NOT IN (SELECT groupname FROM groups)
	RETURN
END
IF @operation = N'GET_ADDED_EMAIL_NOTIFICATIONS'
BEGIN
	SELECT t1.* FROM email_notification t1
	LEFT JOIN email_notification_snapshot t2
	ON t1.address = t2.address AND t1.camera = t2.camera AND t1.dio_input = t2.dio_input AND t1.enabled = t2.enabled AND t1.notification_type = t2.notification_type AND t1.type = t2.type AND t1.va_rule = t2.va_rule AND t1.vpos_exception = t2.vpos_exception
	WHERE t2.address IS NULL
	RETURN
END
IF @operation = N'GET_DELETED_EMAIL_NOTIFICATIONS'
BEGIN
	SELECT t1.* FROM email_notification_snapshot t1
	LEFT JOIN email_notification t2
	ON t1.address = t2.address AND t1.camera = t2.camera AND t1.dio_input = t2.dio_input AND t1.enabled = t2.enabled AND t1.notification_type = t2.notification_type AND t1.type = t2.type AND t1.va_rule = t2.va_rule AND t1.vpos_exception = t2.vpos_exception
	WHERE t2.address IS NULL
	RETURN
END
IF @operation = N'GET_ADDED_AUDIT_CRITERIA'
BEGIN
	SELECT t1.entity, t1.type, t1.value, t1.interval
	FROM
		(
			SELECT
				CASE
					WHEN isnull(u.name, '') <> '' THEN 'User: ' + u.name
					WHEN isnull(rc.group_name, '') <> '' THEN 'Group: ' + rc.group_name
					ELSE 'All Users'
				END AS [entity],
				r.name AS [type],
				rc.val AS [value],
				rct.name AS [interval]
			FROM
				[VIGIL-Audit].dbo.audit_report_criterias rc
				JOIN [VIGIL-Audit].dbo.audit_reports r ON rc.report_id = r.idx
				JOIN [VIGIL-Audit].dbo.audit_report_criteria_types rct ON rct.idx = rc.type_id
				LEFT JOIN [VIGIL-Audit].dbo.audit_users u ON rc.user_id = u.idx
		) t1
		LEFT JOIN
		(
			SELECT
				CASE
					WHEN isnull(u.name, '') <> '' THEN 'User: ' + u.name
					WHEN isnull(rc.group_name, '') <> '' THEN 'Group: ' + rc.group_name
					ELSE 'All Users'
				END AS [entity],
				r.name AS [type],
				rc.val AS [value],
				rct.name AS [interval]
			FROM
				audit_report_criterias_snapshot rc
				JOIN [VIGIL-Audit].dbo.audit_reports r ON rc.report_id = r.idx
				JOIN audit_report_criteria_types_snapshot rct ON rct.idx = rc.type_id
				LEFT JOIN audit_users_snapshot u ON rc.user_id = u.idx
		) t2
		ON t1.entity = t2.entity AND t1.type = t2.type AND t1.value = t2.value AND t1.interval = t2.interval
	WHERE
		t2.entity IS NULL

	RETURN
END
IF @operation = N'GET_DELETED_AUDIT_CRITERIA'
BEGIN
	SELECT t1.entity, t1.type, t1.value, t1.interval
	FROM
		(
			SELECT
				CASE
					WHEN isnull(u.name, '') <> '' THEN 'User: ' + u.name
					WHEN isnull(rc.group_name, '') <> '' THEN 'Group: ' + rc.group_name
					ELSE 'All Users'
				END AS [entity],
				r.name AS [type],
				rc.val AS [value],
				rct.name AS [interval]
			FROM
				audit_report_criterias_snapshot rc
				JOIN [VIGIL-Audit].dbo.audit_reports r ON rc.report_id = r.idx
				JOIN audit_report_criteria_types_snapshot rct ON rct.idx = rc.type_id
				LEFT JOIN audit_users_snapshot u ON rc.user_id = u.idx
		) t1
		LEFT JOIN
		(
			SELECT
				CASE
					WHEN isnull(u.name, '') <> '' THEN 'User: ' + u.name
					WHEN isnull(rc.group_name, '') <> '' THEN 'Group: ' + rc.group_name
					ELSE 'All Users'
				END AS [entity],
				r.name AS [type],
				rc.val AS [value],
				rct.name aS [interval]
			FROM
				[VIGIL-Audit].dbo.audit_report_criterias rc
				JOIN [VIGIL-Audit].dbo.audit_reports r ON rc.report_id = r.idx
				JOIN [VIGIL-Audit].dbo.audit_report_criteria_types rct on rct.idx = rc.type_id
				LEFT JOIN [VIGIL-Audit].dbo.audit_users u ON rc.user_id = u.idx
		) t2
		ON t1.entity = t2.entity AND t1.type = t2.type AND t1.value = t2.value AND t1.interval = t2.interval
	WHERE
		t2.entity IS NULL
	RETURN
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_marked_footage]
@channel int,
@start_date datetime,
@end_date datetime,
@username nvarchar(50) = NULL,
@restricted bit = 0,
@locked bit = 0,
@name nvarchar(50) = NULL,
@notes nvarchar(3000) = NULL
AS
BEGIN
	IF NOT EXISTS(SELECT * FROM marked_footage
		WHERE channel = @channel
		AND [start_date] = @start_date
		AND end_date = @end_date
		AND (username = @username OR COALESCE(username, @username) IS NULL)
		AND restricted = @restricted
		AND locked = @locked
		AND (name = @name OR COALESCE(name, @name) IS NULL)
		AND (notes = @notes OR COALESCE(notes, @notes) IS NULL)
	)
	BEGIN
		INSERT INTO marked_footage ([channel], [start_date], [end_date], [username], [restricted], [locked], [name], [notes])
		VALUES (@channel, @start_date, @end_date, @username, @restricted, @locked, @name, @notes)
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_marked_footage]
@idx bigint
AS
BEGIN
	DELETE FROM marked_footage WHERE idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_restricted_footage]
@channel int = -1,
@start_date datetime = NULL,
@end_date datetime = NULL
AS
BEGIN
	SELECT * FROM marked_footage
	WHERE [restricted] = 1
	AND (@channel < 0 OR [channel] = @channel)
	AND (@end_date is NULL OR [start_date] <= @end_date)
	AND (@start_date is NULL OR [end_date] >= @start_date)
	ORDER BY
	created_date DESC
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_locked_footage]
@channel int = -1,
@start_date datetime = NULL,
@end_date datetime = NULL
AS
BEGIN
	SELECT
		mf.idx,
		mf.channel,
		mf.[start_date],
		mf.end_date,
		mf.username,
		mf.restricted,
		mf.locked,
		mf.created_date,
		mf.name,
		mf.notes,
		sum(e.file_size) as disk_space_kb
	FROM
		marked_footage mf
		LEFT JOIN [events] e ON e.start_time <= mf.end_date AND e.end_time >= mf.[start_date] AND e.camera_number = mf.channel
	WHERE
		mf.locked = 1
		AND (@channel < 0 OR mf.[channel] = @channel)
		AND (@end_date is NULL OR mf.[start_date] <= @end_date)
		AND (@start_date is NULL OR mf.[end_date] >= @start_date)
	GROUP BY
		mf.idx,
		mf.channel,
		mf.[start_date],
		mf.end_date,
		mf.username,
		mf.restricted,
		mf.locked,
		mf.created_date,
		mf.name,
		mf.notes
	ORDER BY
		mf.created_date DESC
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_locked_files]
@directory nvarchar(255)
AS
BEGIN
	if @directory LIKE '%\AUDIO\%' OR @directory LIKE '%/AUDIO/%'
	BEGIN
		SELECT ae.footage_filename as [filename]
		FROM audioevents ae JOIN audio_directory ad ON ae.directory_id = ad.idx
		JOIN marked_footage mf ON ae.start_time <= mf.end_date AND ae.end_time >= mf.start_date
		WHERE ad.directory = @directory
		AND  mf.locked = 1
	END
	ELSE
	BEGIN
		SELECT e.footage_filename as [filename]
		FROM events e JOIN directory d ON e.directory_id = d.idx
		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 d.directory = @directory
		AND  mf.locked = 1
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_dio_email_last_sent]
@input int,
@last_sent datetime
AS
BEGIN
	UPDATE dio_alarms SET email_last_sent = @last_sent WHERE alarm_input = @input
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_orphaned_records]
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @events_rows_deleted INT
	DECLARE @audioevents_rows_deleted INT

	DELETE FROM [events] WHERE directory_id NOT IN (SELECT idx FROM directory)

	SET @events_rows_deleted = @@ROWCOUNT

	DELETE FROM audioevents WHERE directory_id NOT IN (SELECT idx FROM audio_directory)

	SET @audioevents_rows_deleted = @@ROWCOUNT

	SELECT @events_rows_deleted AS events_rows_deleted, @audioevents_rows_deleted AS audioevents_rows_deleted

	SET NOCOUNT OFF
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_restricted_footage]
AS
BEGIN
	DELETE marked_footage WHERE restricted = 1 AND idx NOT IN
	(
		SELECT DISTINCT mf.idx
		FROM marked_footage mf
		JOIN [events] e ON mf.channel = e.camera_number
		WHERE mf.restricted = 1 AND mf.start_date <= e.end_time AND mf.end_date >= e.start_time
	)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_locked_footage]
AS
BEGIN
	DELETE marked_footage WHERE locked = 1 AND idx NOT IN
	(
		SELECT DISTINCT mf.idx
		FROM marked_footage mf
		JOIN [events] e ON mf.channel = e.camera_number
		WHERE mf.locked = 1 AND  mf.start_date <= e.end_time AND mf.end_date >= e.start_time
	)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_update_marked_footage]
@idx bigint,
@name nvarchar(50) = NULL,
@notes nvarchar(3000) = NULL
AS
BEGIN
	UPDATE marked_footage SET name = @name, notes = @notes WHERE idx = @idx
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_get_locked_footage_disk_usage]
AS
BEGIN
	SELECT
		sum(file_size) AS disk_space_kb
	FROM
		(SELECT DISTINCT
			e.idx, e.file_size
		FROM
			marked_footage mf
			JOIN [events] e ON e.start_time <= mf.end_date AND e.end_time >= mf.start_date AND e.camera_number = mf.channel
		WHERE
			mf.locked = 1
		) e
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_check_tables]
AS
BEGIN
	-- Perform simple query on each table in database.
	-- Output a list of tables for which queries failed.

	SET NOCOUNT ON 

	CREATE TABLE #suspect
	(
		suspect_table sysname
	)

	DECLARE @table sysname

	DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables
	OPEN table_cursor
	FETCH NEXT FROM table_cursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC('SELECT TOP 1 * INTO #tmp FROM [' + @table + ']')
		END TRY
		BEGIN CATCH
			INSERT INTO #suspect VALUES (@table)
		END CATCH

		FETCH NEXT FROM table_cursor INTO @table
	END

	CLOSE table_cursor
	DEALLOCATE table_cursor

	SELECT * FROM #suspect
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_channel_oldest_events]
@audio bit,
@include_locked bit
AS
	IF @audio = 0
		SELECT camera_number as channel_number, min(end_time) as end_time
		FROM [events] WITH (NOLOCK)
		WHERE @include_locked = 1 OR idx NOT IN (SELECT e.idx 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)
		GROUP BY camera_number
		ORDER BY camera_number
	ELSE
		SELECT channel_number, min(end_time) as end_time
		FROM audioevents WITH (NOLOCK)
		WHERE @include_locked = 1 OR idx NOT IN (SELECT ae.idx FROM audioevents ae WITH (NOLOCK) JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1)
		GROUP BY channel_number
		ORDER BY channel_number
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_channel_oldest_files]
@channel_number int,
@cutoff_time datetime,
@audio bit,
@include_locked bit
AS
	IF @audio = 0
		SELECT
			d.directory, e.footage_filename
		FROM
			[events] e WITH (NOLOCK)
			JOIN directory d on e.directory_id = d.idx
		WHERE
			e.camera_number = @channel_number
			AND e.end_time < @cutoff_time
			AND (@include_locked = 1 OR e.idx NOT IN (SELECT e.idx 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))
		ORDER BY
			d.directory + e.footage_filename
	ELSE
		SELECT
			ad.directory, ae.footage_filename
		FROM
			audioevents ae WITH (NOLOCK)
			JOIN audio_directory ad on ae.directory_id = ad.idx
		WHERE
			ae.channel_number = @channel_number
			AND ae.end_time < @cutoff_time
			AND (@include_locked = 1 OR ae.idx NOT IN (SELECT ae.idx FROM audioevents ae WITH (NOLOCK) JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1))
		ORDER BY
			ad.directory + ae.footage_filename
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_channel_data]
@channel_number int,
@cutoff_time datetime,
@audio bit,
@include_locked bit
AS
BEGIN
	IF @audio = 0
		DELETE FROM [events] WHERE camera_number = @channel_number AND end_time < @cutoff_time
		AND (@include_locked = 1 OR idx NOT IN (SELECT e.idx 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))
	ELSE
		DELETE FROM audioevents WHERE channel_number = @channel_number AND end_time < @cutoff_time
		AND (@include_locked = 1 OR idx NOT IN (SELECT ae.idx FROM audioevents ae WITH (NOLOCK) JOIN marked_footage mf ON ae.start_time <= mf.end_date and ae.end_time >= mf.start_date WHERE mf.locked = 1))
END
GO
