USE [VIGIL-Audit]
GO

-- Update table schema and database parameters here.

IF (SELECT version FROM table_version WHERE entity = 'audit_details') < 1
BEGIN
	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 = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END
GO

-- Shift Analysis report
IF NOT EXISTS (SELECT * FROM audit_reports WHERE idx = 14)
BEGIN
	INSERT INTO audit_reports VALUES(14, 'Shift Analysis Tags Viewed', 'SHIFT_ANALYSIS_TAGS_VIEWED_SUM', 1, 'Tags')
END
GO

-- Logged in Failed report
IF NOT EXISTS (SELECT * FROM audit_reports WHERE idx = 15)
BEGIN
	INSERT INTO audit_reports VALUES(15, 'Logged in Failed', 'LOGGED_IN_FAILED', 0, '')
END
GO


-- RECREATE VIEWS

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

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


