USE [VIGIL-Audit]
GO


-- DROP CONSTRAINTS

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

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

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

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

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

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

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

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


-- DROP VIEWS

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


-- DROP TABLES

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].[audit_users]') AND type in (N'U'))
DROP TABLE [dbo].[audit_users]
GO

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

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

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

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

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

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

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

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

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

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

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


-- CREATE TABLES

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audit_daily_performance_cache](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[username] [nvarchar](50) NOT NULL,
	[day] [datetime] NOT NULL,
	[perf] [money] NOT NULL,
 CONSTRAINT [PK_audit_daily_performance_cache] 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_daily_user_summary](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[date] [datetime] NOT NULL,
	[user] [nvarchar](50) NOT NULL,
	[item] [nvarchar](30) NOT NULL,
	[value] [float] NOT NULL,
	[valid] [bit] NOT NULL CONSTRAINT [DF_audit_daily_user_summary_valid]  DEFAULT ((1)),
 CONSTRAINT [PK_audit_daily_user_summary] 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_details](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[event_id] [smallint] NOT NULL,
	[user_id] [smallint] NOT NULL,
	[event_time] [datetime] NOT NULL CONSTRAINT [DF_audit_details_event_time]  DEFAULT (getdate()),
	[guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_audit_details_guid]  DEFAULT (newid()),
 CONSTRAINT [PK_audit_details] 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_details_info](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[audit_details_id] [int] NOT NULL,
	[data] [nvarchar](64) NOT NULL,
	[data_type_id] [int] NULL,
 CONSTRAINT [PK_audit_details_info] 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_details_info_types](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_audit_details_info_types] 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_event_types](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](30) NOT NULL,
	[active] [bit] NOT NULL CONSTRAINT [DF_audit_event_types_active]  DEFAULT ((1)),
 CONSTRAINT [PK_audit_event_types] 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_events](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[type_id] [smallint] NOT NULL,
	[name] [nvarchar](30) NOT NULL,
	[active] [bit] NOT NULL CONSTRAINT [DF_audit_events_active]  DEFAULT ((1)),
 CONSTRAINT [PK_audit_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].[audit_exclusion_dates](
	[date] [datetime] NOT NULL,
	[username] [nvarchar](50) NOT NULL,
	[groupname] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[audit_report_criteria_types](
	[idx] [tinyint] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](30) NOT NULL,
	[tag] [nvarchar](64) NULL,
 CONSTRAINT [PK_audit_report_criteria_types] 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_report_criterias](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[report_id] [tinyint] NOT NULL,
	[user_id] [smallint] NULL,
	[group_name] [nvarchar](50) NULL,
	[type_id] [tinyint] NOT NULL,
	[val] [int] NOT NULL,
 CONSTRAINT [PK_audit_report_criterias] 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_reports](
	[idx] [tinyint] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[keyword] [nvarchar](30) NOT NULL,
	[summary] [bit] NOT NULL CONSTRAINT [DF_audit_reports_summary]  DEFAULT ((0)),
	[units] [nvarchar](10) NULL,
 CONSTRAINT [PK_audit_reports] 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_users](
	[idx] [smallint] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[active] [bit] NOT NULL CONSTRAINT [DF_audit_users_active]  DEFAULT ((1)),
 CONSTRAINT [PK_audit_users] 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


-- CREATE VIEWS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_vpos_activity]
AS
	SELECT TOP (100) PERCENT d.idx, d.event_time, u.name AS username, e.name AS event, dit.name AS datatype, di.data
	FROM
		dbo.audit_details AS d INNER JOIN
		dbo.audit_events AS e ON d.event_id = e.idx INNER JOIN
		dbo.audit_details_info AS di ON di.audit_details_id = d.idx INNER JOIN
		dbo.audit_details_info_types AS dit ON dit.idx = di.data_type_id INNER JOIN
		dbo.audit_users AS u ON d.user_id = u.idx
	WHERE (e.name LIKE 'vpos_%') AND (dit.name NOT IN ('LoginSession'))
GO


-- CREATE CONSTRAINTS

ALTER TABLE [dbo].[audit_details]  WITH CHECK ADD  CONSTRAINT [FK_audit_events] FOREIGN KEY([event_id])
REFERENCES [dbo].[audit_events] ([idx])
GO

ALTER TABLE [dbo].[audit_details]  WITH CHECK ADD  CONSTRAINT [FK_audit_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[audit_users] ([idx])
GO

ALTER TABLE [dbo].[audit_details_info]  WITH CHECK ADD  CONSTRAINT [FK_audit_details] FOREIGN KEY([audit_details_id])
REFERENCES [dbo].[audit_details] ([idx])
GO

ALTER TABLE [dbo].[audit_details_info]  WITH CHECK ADD  CONSTRAINT [FK_audit_details_info_types] FOREIGN KEY([data_type_id])
REFERENCES [dbo].[audit_details_info_types] ([idx])
GO

ALTER TABLE [dbo].[audit_events]  WITH CHECK ADD  CONSTRAINT [FK_audit_event_types] FOREIGN KEY([type_id])
REFERENCES [dbo].[audit_event_types] ([idx])
GO

ALTER TABLE [dbo].[audit_report_criterias]  WITH CHECK ADD  CONSTRAINT [FK_audit_report_criterias_audit_report_criteria_types] FOREIGN KEY([type_id])
REFERENCES [dbo].[audit_report_criteria_types] ([idx])
GO

ALTER TABLE [dbo].[audit_report_criterias]  WITH CHECK ADD  CONSTRAINT [FK_audit_report_criterias_audit_reports] FOREIGN KEY([report_id])
REFERENCES [dbo].[audit_reports] ([idx])
GO

ALTER TABLE [dbo].[audit_report_criterias]  WITH NOCHECK ADD  CONSTRAINT [FK_audit_report_criterias_audit_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[audit_users] ([idx])
NOT FOR REPLICATION
GO


-- CREATE INDEXES

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [uc_audit_daily_performance_cache] ON [dbo].[audit_daily_performance_cache]
(
	[username] ASC,
	[day] 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_audit_details_event_id] ON [dbo].[audit_details]
(
	[event_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_audit_details_event_time] ON [dbo].[audit_details]
(
	[event_time] ASC
)
INCLUDE ([event_id], [user_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_details_guid] ON [dbo].[audit_details]
(
	[guid] 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_audit_details_user_id] ON [dbo].[audit_details]
(
	[user_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_audit_details_info_audit_details_id] ON [dbo].[audit_details_info]
(
	[audit_details_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

SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_audit_details_info_data] ON [dbo].[audit_details_info]
(
	[data] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_details_info_types_name] ON [dbo].[audit_details_info_types]
(
	[name] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_event_types_name] ON [dbo].[audit_event_types]
(
	[name] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_events_name_type_id] ON [dbo].[audit_events]
(
	[name] ASC,
	[type_id] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_report_criteria_types_name] ON [dbo].[audit_report_criteria_types]
(
	[name] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_report_citerias_report_id_user_id_type_id_group_name] ON [dbo].[audit_report_criterias]
(
	[report_id] ASC,
	[user_id] ASC,
	[type_id] ASC,
	[group_name] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_reports_keyword] ON [dbo].[audit_reports]
(
	[keyword] 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

SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_audit_reports_name] ON [dbo].[audit_reports]
(
	[name] 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

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_audit_users_name] ON [dbo].[audit_users]
(
	[name] 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


-- POPULATE DATA

SET NOCOUNT ON
GO

INSERT INTO audit_reports([idx], [name], [keyword], summary, units)
SELECT
	1 as [idx]
	, 'Time Logged In' as report_name
	, 'TIME_LOGGED_IN' as keyword
	, 0 as summary
	, '' as units
UNION
SELECT 2, 'Time Logged In', 'TIME_LOGGED_IN_SUM', 1, 'Minutes'
UNION
SELECT 3, 'Footage Viewed', 'FOOTAGE_VIEWED', 0, ''
UNION
SELECT 4, 'Searches Done', 'SEARCHES_DONE', 0, ''
UNION
SELECT 5, 'POS Query','POS_QUERY', 0, ''
UNION
SELECT 6, 'Idle Time', 'IDLE_TIME_SUM', 1, 'Minutes'
UNION
SELECT 7, 'Footage Viewed', 'FOOTAGE_VIEWED_SUM',1, 'Minutes'
UNION
SELECT 8, 'Frames Viewed', 'FRAME_VIEWED_SUM', 1, 'Frames'
UNION
SELECT 9, 'Searches Done','SEARCHES_DONE_SUM', 1, 'Searches'
UNION
SELECT 10, 'POS Query', 'POS_QUERY_SUM', 1, 'Queries'
UNION
SELECT 11, 'Active Time', 'ACTIVE_TIME_SUM', 1, 'Minutes'
UNION
SELECT 12, 'POS/ATM Events Flagged', 'VPOS_EVENTS_FLAGGED_SUM', 1, 'Events'
UNION
SELECT 13, 'POS/ATM Events Flagged %', 'VPOS_EVENTS_FLAGGED_PERCENT', 1, 'Percent'
UNION
SELECT 14, 'Shift Analysis Tags Viewed', 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM', 1, 'Tags'
UNION
SELECT 15, 'Logged in Failed', 'LOGGED_IN_FAILED', 0, ''
GO

SET NOCOUNT OFF
GO
