USE [VIGIL-POS]
GO

-- Update table schema and database parameters here.

-- Secondary item filter
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 1
	ALTER TABLE [Exception] ADD
		[item2] [nvarchar](50) NULL,
		[item2_in_next_receipt] [bit] NULL
GO

-- Migration for secondary item filter fix for non-unique receipt nos.
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 2
BEGIN
	UPDATE Exception
	SET where_clause = replace(where_clause, 'AND p1.receipt_no = p2.receipt_no) p3', 'AND p1.receipt_no = p2.receipt_no AND abs(datediff(minute, p1.server_time, p2.server_time)) <= 60) p1jp2')
	WHERE where_clause like '%AND p1.receipt_no = p2.receipt_no) p3%'

	UPDATE Exception
	SET where_clause = replace(where_clause, 'dbo.fn_get_previous_receipt_no(p1.receipt_no, p1.terminal_num, p1.register_num)', 'dbo.fn_get_previous_receipt_no(p1.receipt_no, p1.terminal_num, p1.register_num, p1.server_time) AND abs(datediff(minute, p1.server_time, p2.server_time)) <= 60')
	WHERE where_clause like '%dbo.fn_get_previous_receipt_no(p1.receipt_no, p1.terminal_num, p1.register_num)%'
END
GO

-- Exception rank
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 3
	ALTER TABLE [Exception]
		ADD [rank] [int] NOT NULL CONSTRAINT [DF_Exception_rank]  DEFAULT ((0))
GO

-- Exception trigger_server_alarm
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 4
	ALTER TABLE [Exception]
		ADD [trigger_server_alarm] [bit] NOT NULL CONSTRAINT [DF_Exception_trigger_server_alarm]  DEFAULT ((0))
GO

-- Exception start_minute, end_minute, start_second, end_second
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 5
	ALTER TABLE [Exception]
		ADD [start_minute] [int] NOT NULL DEFAULT((0)),
		[end_minute] [int] NOT NULL DEFAULT((0)),
		[start_second] [int] NOT NULL DEFAULT((0)),
		[end_second] [int] NOT NULL DEFAULT((0))
GO

-- Exception alarm_priority
IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'Exception') < 6
	ALTER TABLE [Exception]
		ADD [alarm_priority] [int] NOT NULL DEFAULT ((1))
GO

-- Shift Analysis Tag Info
IF NOT EXISTS (SELECT [version] FROM [TableVersion] WHERE [entity] = 'shift_analysis_tag_info')
	CREATE TABLE [dbo].[shift_analysis_tag_info](
		[idx] [bigint] IDENTITY(1,1) NOT NULL,
		[rule_id] [nvarchar](50) NOT NULL,
		[cashier] [nvarchar](50) NOT NULL,
		[tag_date] [date] NOT NULL,
		[acknowledged] [bit] NOT NULL DEFAULT(0),
		[notes] [nvarchar](1000) NULL,
		[last_update_utc] [datetime] NOT NULL DEFAULT(getdate()),
		[pos_idx][bigint] NOT NULL DEFAULT(0),
		[event_flag_id] [nvarchar](50) NULL
	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],
	CONSTRAINT [UC_tag] UNIQUE NONCLUSTERED 
	(
		[rule_id] ASC,
		[pos_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

IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'shift_analysis_tag_info') < 1
	ALTER TABLE [shift_analysis_tag_info]
		ADD [pos_idx] [bigint] NOT NULL DEFAULT(0),
		[event_flag_id] [nvarchar](50) NULL

	ALTER TABLE [shift_analysis_tag_info]
		DROP CONSTRAINT [UC_tag]

	ALTER TABLE [shift_analysis_tag_info]
		ADD CONSTRAINT [UC_tag] UNIQUE NONCLUSTERED
		(
			[rule_id] ASC,
			[pos_idx] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

IF (SELECT [version] FROM [TableVersion] WHERE [entity] = 'AlarmFlag') < 1
	ALTER TABLE [AlarmFlag] ADD
		[email_enabled] [bit] NOT NULL DEFAULT(0),
		[email_recipients] [nvarchar](1000) NULL
GO




-- Restore [VIGIL-DVR] permissions for vpos
USE [VIGIL-DVR]
GO

GRANT EXECUTE ON [dbo].[sp_insert_email] TO [vpos]
GO

GRANT EXECUTE ON [dbo].[sp_insert_email_notification] TO [vpos]
GO

GRANT EXECUTE ON [dbo].[sp_delete_vpos_email_notification] TO [vpos]
GO

GRANT EXECUTE ON [dbo].[sp_save_alarm_event] to [vpos]
GO
