USE [VIGIL-DVR]
GO


-- DROP CONSTRAINTS

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_usergroup_users]') AND parent_object_id = OBJECT_ID(N'[dbo].[usergroup]'))
ALTER TABLE [dbo].[usergroup] DROP CONSTRAINT [FK_usergroup_users]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_usergroup_groups]') AND parent_object_id = OBJECT_ID(N'[dbo].[usergroup]'))
ALTER TABLE [dbo].[usergroup] DROP CONSTRAINT [FK_usergroup_groups]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_grouppermission_permissions]') AND parent_object_id = OBJECT_ID(N'[dbo].[grouppermission]'))
ALTER TABLE [dbo].[grouppermission] DROP CONSTRAINT [FK_grouppermission_permissions]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_grouppermission_groups]') AND parent_object_id = OBJECT_ID(N'[dbo].[grouppermission]'))
ALTER TABLE [dbo].[grouppermission] DROP CONSTRAINT [FK_grouppermission_groups]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dio_mailsettings_dio_alarms]') AND parent_object_id = OBJECT_ID(N'[dbo].[dio_mailsettings]'))
ALTER TABLE [dbo].[dio_mailsettings] DROP CONSTRAINT [FK_dio_mailsettings_dio_alarms]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dio_ipaddresses_dio_alarms]') AND parent_object_id = OBJECT_ID(N'[dbo].[dio_ipaddresses]'))
ALTER TABLE [dbo].[dio_ipaddresses] DROP CONSTRAINT [FK_dio_ipaddresses_dio_alarms]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dio_cameras_dio_alarms]') AND parent_object_id = OBJECT_ID(N'[dbo].[dio_cameras]'))
ALTER TABLE [dbo].[dio_cameras] DROP CONSTRAINT [FK_dio_cameras_dio_alarms]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dio_audiochannels_dio_alarms]') AND parent_object_id = OBJECT_ID(N'[dbo].[dio_audiochannels]'))
ALTER TABLE [dbo].[dio_audiochannels] DROP CONSTRAINT [FK_dio_audiochannels_dio_alarms]
GO


-- DROP VIEWS

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

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


-- DROP TABLES

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
DROP TABLE [dbo].[users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usergroup]') AND type in (N'U'))
DROP TABLE [dbo].[usergroup]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tagged_footage]') AND type in (N'U'))
DROP TABLE [dbo].[tagged_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_version]') AND type in (N'U'))
DROP TABLE [dbo].[table_version]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[schedule]') AND type in (N'U'))
DROP TABLE [dbo].[schedule]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[marked_footage]') AND type in (N'U'))
DROP TABLE [dbo].[marked_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[email]') AND type in (N'U'))
DROP TABLE [dbo].[email]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[email_notification]') AND type in (N'U'))
DROP TABLE [dbo].[email_notification]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[perms]') AND type in (N'U'))
DROP TABLE [dbo].[perms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[perfmon]') AND type in (N'U'))
DROP TABLE [dbo].[perfmon]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[groups]') AND type in (N'U'))
DROP TABLE [dbo].[groups]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[grouppermission]') AND type in (N'U'))
DROP TABLE [dbo].[grouppermission]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[events]') AND type in (N'U'))
DROP TABLE [dbo].[events]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[drivestatus]') AND type in (N'U'))
DROP TABLE [dbo].[drivestatus]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[drive_path]') AND type in (N'U'))
DROP TABLE [dbo].[drive_path]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[directory]') AND type in (N'U'))
DROP TABLE [dbo].[directory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dio_mailsettings]') AND type in (N'U'))
DROP TABLE [dbo].[dio_mailsettings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dio_ipaddresses]') AND type in (N'U'))
DROP TABLE [dbo].[dio_ipaddresses]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dio_cameras]') AND type in (N'U'))
DROP TABLE [dbo].[dio_cameras]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dio_audiochannels]') AND type in (N'U'))
DROP TABLE [dbo].[dio_audiochannels]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dio_alarms]') AND type in (N'U'))
DROP TABLE [dbo].[dio_alarms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cam_name]') AND type in (N'U'))
DROP TABLE [dbo].[cam_name]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit]') AND type in (N'U'))
DROP TABLE [dbo].[audit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audioevents]') AND type in (N'U'))
DROP TABLE [dbo].[audioevents]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audio_directory]') AND type in (N'U'))
DROP TABLE [dbo].[audio_directory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alarmsuppression]') AND type in (N'U'))
DROP TABLE [dbo].[alarmsuppression]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[alarmevents]') AND type in (N'U'))
DROP TABLE [dbo].[alarmevents]
GO


-- CREATE TABLES

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarmevents](
	[idx] bigint IDENTITY(1,1) NOT NULL,
	[alarm_type] int NOT NULL,
	[alarm_guid] nvarchar(40) NOT NULL,
	[alarm_input] int NOT NULL,
	[alarm_name] nvarchar(50) NULL,
	[camera_numbers] nvarchar(640) NULL,
	[start_time] datetime NOT NULL,
	[end_time] datetime NULL,
	[acknowledged] bit NOT NULL CONSTRAINT [DF_alarmevents_acknowledged] DEFAULT ((0)),
	[custom_text] text NULL,
	[server_local_alarm] bit NOT NULL CONSTRAINT [DF_alarmevents_server_local_alarm] DEFAULT ((0)),
	[priority] int NOT NULL DEFAULT((1)),
	[notes] nvarchar(3000) NULL,
 CONSTRAINT [PK_alarmevents] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarmsuppression](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[alarm_type] [int] NOT NULL,
	[alarm_input] [int] NOT NULL,
	[expiration_utc] [datetime] NULL,
 CONSTRAINT [PK_alarmsuppression] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audio_directory](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[directory] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_audio_directory] PRIMARY KEY CLUSTERED
(
	[directory] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audioevents](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[channel_number] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[drive_path_id] [int] NOT NULL,
	[directory_id] [int] NOT NULL,
	[footage_filename] [nvarchar](20) NOT NULL,
	[footage_sample_rates] [int] NOT NULL,
	[footage_number_clips] [int] NOT NULL,
 CONSTRAINT [PK_audioevents] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audit](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[source] [nvarchar](64) NOT NULL,
	[message] [nvarchar](512) NULL,
	[audit_time] [datetime] NOT NULL,
	[error_level] [int] NULL,
	[remote_ip] [nvarchar](256) NULL,
	[function_name] [nvarchar](64) NULL,
	[error_code] [int] NULL,
	[error_description] [nvarchar](256) NULL,
	[state] [nvarchar](512) NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cam_name](
	[cam_num] [int] NOT NULL,
	[name] [nvarchar](20) NULL,
 CONSTRAINT [PK_cam_name] PRIMARY KEY CLUSTERED
(
	[cam_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dio_alarms](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[alarm_input] [int] NOT NULL,
	[dwell_timer] [int] NOT NULL,
	[normal_open] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_normal_open]  DEFAULT ((0)),
	[dio_enabled] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_dio_enabled]  DEFAULT ((1)),
	[alarm_popup] [bit] NOT NULL,
	[monitor_out] [int] NOT NULL,
	[audio_notify_type] [int] NOT NULL,
	[alarm_audio_file] [nvarchar](256) NOT NULL,
	[auto_ack_timeout] [int] NOT NULL,
	[schedule_enabled] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_schedule_enabled]  DEFAULT ((0)),
	[server_push_path] [nvarchar](256) NOT NULL CONSTRAINT [DF_dio_alarms_server_push_path]  DEFAULT (''),
	[server_push_overlay_text] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_server_push_overlay_text]  DEFAULT ((0)),
	[server_push_ftp_user_name] [nvarchar](256) NOT NULL CONSTRAINT [DF_dio_alarms_server_push_ftp_user_name]  DEFAULT (''),
	[server_push_ftp_password] [nvarchar](256) NOT NULL CONSTRAINT [DF_dio_alarms_server_push_ftp_password]  DEFAULT (''),
	[server_push_ftp_timeout] [int] NOT NULL CONSTRAINT [DF_dio_alarms_server_push_ftp_timeout]  DEFAULT ((5)),
	[server_push_enabled] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_server_push_enabled]  DEFAULT ((0)),
	[suppress_email] [bit] NOT NULL,
	[do_number] [int] NOT NULL CONSTRAINT [DF_dio_alarms_do_number]  DEFAULT ((0)),
	[do_pulse] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_do_pulse]  DEFAULT ((0)),
	[predicate_input] [int] NOT NULL CONSTRAINT [DF_dio_alarms_predicate_input]  DEFAULT ((0)),
	[is_local_alarm] [bit] NOT NULL CONSTRAINT [DF_dio_alarms_is_local_alarm]  DEFAULT ((0)),
	[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,
	[alarm_priority] [int] NOT NULL DEFAULT((1)),
 CONSTRAINT [PK_dio_alarms] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dio_audiochannels](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[dio_alarms_fk] [int] NOT NULL,
	[dio_channel] [int] NOT NULL,
 CONSTRAINT [PK_dio_audiochannels] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dio_cameras](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[dio_alarms_fk] [int] NOT NULL,
	[dio_camera] [int] NOT NULL,
 CONSTRAINT [PK_dio_cameras] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dio_ipaddresses](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[dio_alarms_fk] [int] NOT NULL,
	[dio_ipaddress] [char](24) NOT NULL,
	[dio_dialup_name] [char](64) NOT NULL,
	[dio_connect_all] [bit] NOT NULL,
	[dio_guid] [char](64) NULL,
	[dio_sequence_number] [smallint] NOT NULL,
	[dio_sitename] [char](30) NOT NULL,
 CONSTRAINT [PK_dio_ipaddresses] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dio_mailsettings](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[dio_alarms_fk] [int] NOT NULL,
	[dio_mail_sender] [text] NULL,
	[dio_mail_sendername] [text] NULL,
	[dio_mail_recipient] [text] NULL,
	[dio_mail_ccrecipient] [text] NULL,
	[dio_mail_subject] [text] NULL,
	[dio_mail_body] [text] NULL,
	[dio_mail_bccrecipient] [text] NULL,
	[dio_mail_stillshot] [int] NULL,
	[dio_mail_video] [int] NULL,
	[dio_mail_prealarm_time] [int] NULL
 CONSTRAINT [PK_dio_mailsettings] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[directory](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[directory] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_directory] PRIMARY KEY CLUSTERED
(
	[directory] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[drive_path](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[path] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_drive_path] PRIMARY KEY CLUSTERED
(
	[path] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[drivestatus](
	[drive_path_id] [int] NOT NULL,
	[is_backup_drive] [bit] NOT NULL,
	[suspect] [bit] NOT NULL,
	[partition_alarm] [int] NOT NULL CONSTRAINT [DF_drivestatus_partition_alarm]  DEFAULT ((0)),
	[partition_pos] [int] NOT NULL CONSTRAINT [DF_drivestatus_partition_pos]  DEFAULT ((0)),
 CONSTRAINT [PK_drivestatus] PRIMARY KEY CLUSTERED
(
	[drive_path_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[events](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[drive_path_id] [int] NOT NULL,
	[directory_id] [int] NOT NULL,
	[footage_filename] [nvarchar](20) NOT NULL,
	[footage_number_images] [int] NOT NULL,
	[record_constant] [bit] NOT NULL CONSTRAINT [DF_events_record_constant]  DEFAULT ((0)),
	[record_motion] [bit] NOT NULL CONSTRAINT [DF_events_record_motion]  DEFAULT ((0)),
	[record_alarm] [bit] NOT NULL CONSTRAINT [DF_events_record_alarm]  DEFAULT ((0)),
	[file_size] [bigint] NOT NULL CONSTRAINT [DF_events_file_size]  DEFAULT ((0)),
	[partition_type] [int] NOT NULL CONSTRAINT [DF_events_partition_type]  DEFAULT ((0)),
	[alarm_mask] [int] NOT NULL CONSTRAINT [DF_events_alarm_mask]  DEFAULT ((0)),
 CONSTRAINT [PK_events] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[grouppermission](
	[permission_id] [int] NOT NULL,
	[groupname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_grouppermission] PRIMARY KEY CLUSTERED
(
	[permission_id] ASC,
	[groupname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[groups](
	[groupname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_groups] PRIMARY KEY CLUSTERED
(
	[groupname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[perfmon](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[server_time] [datetime] NULL CONSTRAINT [DF_perfmon_server_time]  DEFAULT (getdate()),
	[cpu_usage] [int] NULL,
	[mem_usage] [int] NULL,
	[mem_available] [int] NULL,
	[mem_total] [int] NULL,
	[vigil_cpu_usage] [int] NULL,
	[vigil_private_bytes] [int] NULL,
	[vigil_handle_count] [int] NULL,
	[vigil_thread_count] [int] NULL,
	[vigil_virtual_bytes] [int] NULL,
 CONSTRAINT [PK_perfmon] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[perms](
	[permission_id] [int] NOT NULL,
	[permission_desc_id] [int] NOT NULL,
	[permission_sort_order] [int] NOT NULL,
 CONSTRAINT [PK_perms] PRIMARY KEY CLUSTERED
(
	[permission_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[email](
	[address] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[email_notification](
	[address] [nvarchar](100) NOT NULL,
	[type] [int] NOT NULL,
	[notification_type] [int] NOT NULL,
	[enabled] [bit] NOT NULL,
	[camera] [int] NOT NULL  DEFAULT ((-1)),
	[dio_input] [int] NOT NULL  DEFAULT ((-1)),
	[vpos_exception] [int] NOT NULL  DEFAULT ((-1)),
	[va_rule] [int] NOT NULL  DEFAULT ((-1)),
	[vpos_alarm_flag] [int] NOT NULL DEFAULT ((-1))
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[schedule](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[dayofweek] [int] NOT NULL,
	[start_time] [int] NOT NULL,
	[stop_time] [int] NOT NULL,
	[record_mode] [int] NOT NULL,
	[schedule_type] [int] NOT NULL,
 CONSTRAINT [PK_schedule] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table_version](
	[entity] [nvarchar](50) NOT NULL,
	[version] [int] NOT NULL,
 CONSTRAINT [PK_table_version] PRIMARY KEY CLUSTERED
(
	[entity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tagged_footage](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[cue_time] [datetime] NOT NULL,
	[description] [nvarchar](100) NULL,
	[notes] [nvarchar](3000) NULL,
	[username] [nvarchar](50) NOT NULL,
	[tag_time] [datetime] NOT NULL,
	[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),
 CONSTRAINT [PK_tagged_footage] PRIMARY KEY CLUSTERED
(
	[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[usergroup](
	[username] [nvarchar](50) NOT NULL,
	[groupname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_usergroup] PRIMARY KEY CLUSTERED
(
	[username] ASC,
	[groupname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
	[account_username] [nvarchar](50) NOT NULL,
	[account_hashpass] [binary](16) NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
	[account_username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO


-- CREATE VIEWS

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_alarmsuppression]
AS
	SELECT
		alarm_type t,
		alarm_input i,
		CAST(((CAST(expiration_utc AS FLOAT) * 86400000) + 9435484800000) AS BIGINT) e
	FROM
		alarmsuppression
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

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


-- CREATE CONSTRAINTS

ALTER TABLE [dbo].[dio_audiochannels]  WITH CHECK ADD  CONSTRAINT [FK_dio_audiochannels_dio_alarms] FOREIGN KEY([dio_alarms_fk])
REFERENCES [dbo].[dio_alarms] ([idx])
GO

ALTER TABLE [dbo].[dio_cameras]  WITH CHECK ADD  CONSTRAINT [FK_dio_cameras_dio_alarms] FOREIGN KEY([dio_alarms_fk])
REFERENCES [dbo].[dio_alarms] ([idx])
GO

ALTER TABLE [dbo].[dio_ipaddresses]  WITH CHECK ADD  CONSTRAINT [FK_dio_ipaddresses_dio_alarms] FOREIGN KEY([dio_alarms_fk])
REFERENCES [dbo].[dio_alarms] ([idx])
GO

ALTER TABLE [dbo].[dio_mailsettings]  WITH CHECK ADD  CONSTRAINT [FK_dio_mailsettings_dio_alarms] FOREIGN KEY([dio_alarms_fk])
REFERENCES [dbo].[dio_alarms] ([idx])
GO

ALTER TABLE [dbo].[grouppermission]  WITH CHECK ADD  CONSTRAINT [FK_grouppermission_groups] FOREIGN KEY([groupname])
REFERENCES [dbo].[groups] ([groupname])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[grouppermission]  WITH CHECK ADD  CONSTRAINT [FK_grouppermission_permissions] FOREIGN KEY([permission_id])
REFERENCES [dbo].[perms] ([permission_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[usergroup]  WITH CHECK ADD  CONSTRAINT [FK_usergroup_groups] FOREIGN KEY([groupname])
REFERENCES [dbo].[groups] ([groupname])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[usergroup]  WITH CHECK ADD  CONSTRAINT [FK_usergroup_users] FOREIGN KEY([username])
REFERENCES [dbo].[users] ([account_username])
ON UPDATE CASCADE
ON DELETE CASCADE
GO


-- CREATE INDEXES

CREATE NONCLUSTERED INDEX [ix_audio_directory_id] ON [dbo].[audioevents]
(
	[directory_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_audio_drive_path_id] ON [dbo].[audioevents]
(
	[drive_path_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_audio_end_time] ON [dbo].[audioevents]
(
	[end_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_audio_start_time] ON [dbo].[audioevents]
(
	[start_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [ix_directory_id] ON [dbo].[directory]
(
	[idx] 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]
GO

CREATE UNIQUE NONCLUSTERED INDEX [ix_drive_path_id] ON [dbo].[drive_path]
(
	[idx] 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]
GO

CREATE UNIQUE NONCLUSTERED INDEX [ix_email] ON [dbo].[email]
(
	[address] 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]
GO

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]
GO

CREATE NONCLUSTERED INDEX [ix_events_directory_id] ON [dbo].[events]
(
	[directory_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_events_drive_path_id] ON [dbo].[events]
(
	[drive_path_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_events_end_time] ON [dbo].[events]
(
	[end_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_events_partition_type] ON [dbo].[events]
(
	[partition_type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_events_start_time] ON [dbo].[events]
(
	[start_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_grouppermission] ON [dbo].[grouppermission]
(
	[permission_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix_server_time] ON [dbo].[perfmon]
(
	[server_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_schedule] ON [dbo].[schedule]
(
	[camera_number] ASC,
	[dayofweek] ASC,
	[start_time] ASC,
	[stop_time] ASC,
	[record_mode] ASC,
	[schedule_type] 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]
GO
