USE [VIGIL-POS]
GO


-- DROP CONSTRAINTS

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


-- DROP VIEWS

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


-- DROP TABLES

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


-- CREATE TABLES

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[action_code_dictionary](
	[uid] [bigint] IDENTITY(1,1) NOT NULL,
	[parser_type] [smallint] NOT NULL,
	[original_item] [nvarchar](100) NULL,
	[original_value_char] [nvarchar](50) NULL,
	[original_value_num] [money] NULL,
	[original_quantity] [smallmoney] NULL,
	[adjusted_item] [nvarchar](512) NULL,
	[adjusted_value_char] [nvarchar](512) NULL,
	[adjusted_value_num] [nvarchar](512) NULL,
	[adjusted_quantity] [nvarchar](512) NULL,
	[action_code_num] [smallint] NULL,
 CONSTRAINT [PK_action_code_dictionary] PRIMARY KEY CLUSTERED
(
	[uid] 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].[action_code_lookup](
	[action_code_num] [smallint] NOT NULL,
	[action_code_str] [nvarchar](10) NULL,
	[action_code_description] [nvarchar](255) NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Alarm](
	[alarm_idx] [bigint] IDENTITY(1,1) NOT NULL,
	[exception_idx] [int] NOT NULL,
	[acknowledged] [bit] NOT NULL CONSTRAINT [DF_Alarm_acknowledged]  DEFAULT ((0)),
	[pos_idx] [bigint] NOT NULL,
	[va_checked] [bit] NOT NULL CONSTRAINT [DF_Alarm_va_checked]  DEFAULT ((0)),
	[alarm_flag_idx] [int] NOT NULL CONSTRAINT [DF_Alarm_alarm_flag_idx]  DEFAULT ((-1)),
	[notes] [nvarchar](256) NOT NULL CONSTRAINT [DF_Alarm_notes]  DEFAULT (''),
	[visible] [bit] NOT NULL CONSTRAINT [DF_Alarm_visible]  DEFAULT ((1)),
 CONSTRAINT [PK_Alarm] PRIMARY KEY CLUSTERED
(
	[alarm_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].[AlarmFlag](
	[idx] [int] NOT NULL,
	[name] [nvarchar](20) NOT NULL,
	[color] [int] NOT NULL CONSTRAINT [DF_AlarmFlag_color]  DEFAULT ((0)),
	[email_enabled] bit NOT NULL DEFAULT ((0)),
	[email_recipients] [nvarchar](1000) NULL
 CONSTRAINT [PK_AlarmFlag] 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].[Exception](
	[exception_idx] [int] IDENTITY(1,1) NOT NULL,
	[exception_guid] [nvarchar](50) NOT NULL CONSTRAINT [DF_Exception_exception_guid]  DEFAULT (newid()),
	[exception_description] [nvarchar](50) NULL,
	[item] [nvarchar](50) NULL,
	[value_num] [money] NULL,
	[value_num_max] [money] NULL,
	[compare_type] [int] NULL,
	[quantity] [smallmoney] NULL,
	[quantity_compare_type] [int] NULL,
	[register_num] [int] NULL,
	[cashier] [nvarchar](50) NULL,
	[value_char] [nvarchar](20) NULL,
	[whole_dollar_amounts] [bit] NULL,
	[void_nosale_transactions] [bit] NULL,
	[start_hour] [int] NULL,
	[end_hour] [int] NULL,
	[alert_enabled] [bit] NOT NULL CONSTRAINT [DF_Exception_alert_enabled]  DEFAULT ((1)),
	[email_enabled] [bit] NOT NULL CONSTRAINT [DF_Exception_email_enabled]  DEFAULT ((0)),
	[email_is_html] [bit] NOT NULL CONSTRAINT [DF_Exception_email_is_html]  DEFAULT ((0)),
	[email_period] [int] NOT NULL CONSTRAINT [DF_Exception_email_period]  DEFAULT ((1)),
	[email_period_type] [int] NOT NULL CONSTRAINT [DF_Exception_email_period_type]  DEFAULT ((0)),
	[email_specific_day_enabled] [bit] NOT NULL CONSTRAINT [DF_Exception_email_specific_day_enabled]  DEFAULT ((0)),
	[email_specific_day] [int] NOT NULL CONSTRAINT [DF_Exception_email_specific_day]  DEFAULT ((0)),
	[email_specific_hour_enabled] [bit] NOT NULL CONSTRAINT [DF_Exception_email_specific_hour_enabled]  DEFAULT ((0)),
	[email_specific_hour] [int] NOT NULL CONSTRAINT [DF_Exception_email_specific_hour]  DEFAULT ((0)),
	[email_exception_threshold] [int] NOT NULL CONSTRAINT [DF_Exception_email_exception_threshold]  DEFAULT ((0)),
	[where_clause] [nvarchar](1000) NULL,
	[filter_conjunction_AND] [bit] NOT NULL CONSTRAINT [DF_Exception_filter_conjunction_AND]  DEFAULT ((1)),
	[show_exception_in_list] [bit] NULL,
	[va_enabled] [bit] NOT NULL CONSTRAINT [DF_Exception_va_enabled]  DEFAULT ((0)),
	[va_camera] [int] NOT NULL CONSTRAINT [DF_Exception_va_camera]  DEFAULT ((0)),
	[va_rulename] [nvarchar](30) NOT NULL CONSTRAINT [DF_Exception_va_rulename]  DEFAULT (''),
	[va_compare] [int] NOT NULL CONSTRAINT [DF_Exception_va_compare]  DEFAULT ((0)),
	[va_value] [int] NOT NULL CONSTRAINT [DF_Exception_va_value]  DEFAULT ((0)),
	[item_case_sensitive] [bit] NOT NULL CONSTRAINT [DF_Exception_item_case_sensitive]  DEFAULT ((0)),
	[terminal_num] [int] NULL,
	[item2] [nvarchar](50) NULL,
	[item2_in_next_receipt] [bit] NULL,
	[rank] [int] NOT NULL CONSTRAINT [DF_Exception_rank]  DEFAULT ((0)),
	[trigger_server_alarm] [bit] NOT NULL CONSTRAINT [DF_Exception_trigger_server_alarm]  DEFAULT ((0)),
	[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)),
	[alarm_priority] [int] NOT NULL DEFAULT((1)),
 CONSTRAINT [PK_Exception] PRIMARY KEY CLUSTERED
(
	[exception_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].[ExceptionRecipient](
	[exception_idx] [int] NOT NULL,
	[email_address] [nvarchar](50) NOT NULL,
	[email_name] [nvarchar](50) NULL,
	[recipient_type] [int] NOT NULL  DEFAULT((0)),
	[enabled] [bit] NOT NULL  DEFAULT((1))
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GlobalSettings](
	[field_name] [nvarchar](50) NOT NULL,
	[field_value] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_GlobalSettings] PRIMARY KEY CLUSTERED
(
	[field_name] 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].[last_inserted_posdata_idx](
	[idx] [bigint] NOT NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POSData](
	[idx] [bigint] NOT NULL,
	[receipt_no] [nvarchar](20) NULL,
	[register_time] [datetime] NOT NULL,
	[server_time] [datetime] NOT NULL,
	[item] [nvarchar](50) NULL,
	[value_char] [nvarchar](20) NULL,
	[value_num] [money] NULL,
	[sequence_num] [int] NULL,
	[register_num] [int] NULL,
	[terminal_num] [int] NOT NULL,
	[void] [bit] NOT NULL,
	[nosale] [bit] NOT NULL,
	[quantity] [smallmoney] NULL,
	[cashier] [nvarchar](50) NULL,
	[action_code_num] [smallint] NULL,
	[parser_type] [smallint] NULL,
	[is_standardized] [bit] NULL,
 CONSTRAINT [PK_POSData] 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].[posignoretext](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[pos_type] [int] NOT NULL,
	[ignore_field] [int] NOT NULL,
	[ignore_text] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_posignoretext] 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].[pos_items](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[item_code] [nvarchar](12) NOT NULL,
	[name] [nvarchar](18) NOT NULL,
 CONSTRAINT [PK_pos_items] 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].[ScheduledReportLog](
	[srl_idx] [bigint] IDENTITY(1,1) NOT NULL,
	[exception_idx] [int] NOT NULL,
	[report_timestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_ScheduledReportLog] PRIMARY KEY CLUSTERED
(
	[srl_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].[seq_data](
	[index] [int] NOT NULL,
 CONSTRAINT [PK_seq_data] PRIMARY KEY CLUSTERED
(
	[index] 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].[TableVersion](
	[entity] [nvarchar](50) NOT NULL,
	[version] [int] NOT NULL,
 CONSTRAINT [PK_TableVersion] 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].[tmp_posdata](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[receipt_no] [nvarchar](20) NULL,
	[register_time] [datetime] NOT NULL,
	[server_time] [datetime] NOT NULL,
	[item] [nvarchar](50) NULL,
	[value_char] [nvarchar](20) NULL,
	[value_num] [money] NULL,
	[sequence_num] [int] NULL,
	[register_num] [int] NULL,
	[terminal_num] [int] NOT NULL,
	[void] [bit] NOT NULL,
	[nosale] [bit] NOT NULL,
	[quantity] [smallmoney] NULL,
	[cashier] [nvarchar](50) NULL,
	[action_code_num] [smallint] NULL,
	[parser_type] [smallint] NULL,
	[is_standardized] [bit] NULL,
 CONSTRAINT [PK_tmp_posdata] 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].[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


-- CREATE VIEWS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_posdata]
AS
	SELECT *
	FROM posdata (nolock)
	UNION ALL
	SELECT *
	FROM tmp_posdata (nolock)
GO


-- CREATE CONSTRAINTS

ALTER TABLE [dbo].[Alarm]  WITH CHECK ADD  CONSTRAINT [FK_Alarm_POSData] FOREIGN KEY([pos_idx])
REFERENCES [dbo].[POSData] ([idx])
ON DELETE CASCADE
GO


-- CREATE INDEXES

CREATE NONCLUSTERED INDEX [ix_alarm_pos_idx] ON [dbo].[Alarm]
(
	[pos_idx] 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_exception_idx] ON [dbo].[Alarm]
(
	[exception_idx] 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_posdata_receipt_no] ON [dbo].[POSData]
(
	[receipt_no] 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_posdata_server_time] ON [dbo].[POSData]
(
	[server_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

SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx_ignore_text] ON [dbo].[posignoretext]
(
	[ignore_text] 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_postype] ON [dbo].[posignoretext]
(
	[pos_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

SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [idx_item_code] ON [dbo].[pos_items]
(
	[item_code] 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 TRIGGERS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[REAL_TIME_ALARMS] ON [dbo].[POSData]
FOR INSERT
AS
	DECLARE @where_clause nvarchar(1000)
	DECLARE @exception_idx int
	DECLARE @insertSQL nvarchar(4000)
	DECLARE @minInsertedIdx bigint
	DECLARE @va_enabled bit
	DECLARE @va_checked nvarchar(10)

	DECLARE WhereClause_Cursor CURSOR FOR
	SELECT where_clause, exception_idx, va_enabled
	FROM dbo.Exception
	WHERE alert_enabled = 1
	AND ISNULL(where_clause, '') <> ''

	OPEN WhereClause_Cursor

	SELECT @minInsertedIdx = MIN(idx) FROM inserted

	FETCH NEXT FROM WhereClause_Cursor
	INTO @where_clause, @exception_idx, @va_enabled

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @where_clause = replace(@where_clause, '@start_time', '''1980-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@end_time', '''2099-01-01 12:00''')
		SELECT @where_clause = replace(@where_clause, '@idx', CAST(@minInsertedIdx - 100 AS nvarchar(21)))

		IF @va_enabled = 1
			SET @va_checked = '0'
		ELSE
			SET @va_checked = '1'

		SELECT @insertSQL = N'INSERT INTO Alarm (pos_idx, exception_idx, va_checked) SELECT idx, ' + CAST(@exception_idx AS nvarchar(21)) + N', ' + @va_checked + N' FROM POSData WHERE idx >= ' + CAST(@minInsertedIdx AS nvarchar(21)) + N' AND ' + CAST(@where_clause AS nvarchar(1000))

		EXEC sp_executesql @insertSQL

		FETCH NEXT FROM WhereClause_Cursor
		INTO @where_clause, @exception_idx, @va_enabled
	END

	CLOSE WhereClause_Cursor

	DEALLOCATE WhereClause_Cursor
GO


-- POPULATE DATA

SET NOCOUNT ON
GO

INSERT INTO last_inserted_posdata_idx(idx) VALUES(0)
GO

DECLARE @i int
SELECT @i = 1
WHILE @i <= 8000
BEGIN
	INSERT INTO seq_data([index]) VALUES(@i)
	SELECT @i = @i + 1
END
GO

SET NOCOUNT OFF
GO
