USE [VIGIL-VA]
GO


-- DROP VIEWS

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

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

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

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


-- DROP TABLES

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

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

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

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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[va_alarms]') AND type in (N'U'))
DROP TABLE [dbo].[va_alarms]
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


-- CREATE TABLES

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].[va_alarms](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[rule_id] [int] NOT NULL,
	[rule_type] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[counter] [int] NOT NULL,
	[period] [int] NULL,
	[rule_name] [nvarchar](100) NULL,
	[description] [nvarchar](256) NULL,
	[is_private] [bit] NOT NULL CONSTRAINT [DF_va_alarms_is_private]  DEFAULT ((0)),
	[details] [nvarchar](2000) NULL,
 CONSTRAINT [PK_va_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].[va_faces](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[rule_id] [int] NOT NULL,
	[rule_type] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[gender] [int] NOT NULL,
	[age] [int] NOT NULL,
	[age_deviation] [int] NOT NULL,
	[happy] [int] NOT NULL,
	[sad] [int] NOT NULL,
	[surprised] [int] NOT NULL,
	[angry] [int] NOT NULL,
	[rule_name] [nvarchar](100) NULL,
	[is_private] [bit] NOT NULL CONSTRAINT [DF_va_faces_is_private]  DEFAULT ((0)),
 CONSTRAINT [PK_va_faces] 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].[va_pos](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[register_num] [int] NOT NULL,
	[rule_name] [nvarchar](100) NULL,
	[dwell] [int] NOT NULL CONSTRAINT [DF_va_pos_dwell]  DEFAULT ((0)),
	[is_private] [bit] NOT NULL CONSTRAINT [DF_va_pos_is_private]  DEFAULT ((0)),
 CONSTRAINT [PK_va_pos] 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].[va_rule_type](
	[idx] [bigint] NOT NULL,
	[description] [nvarchar](100) NULL,
 CONSTRAINT [PK_va_rule_type] 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].[vacounter](
	[idx] [bigint] IDENTITY(1,1) NOT NULL,
	[camera_number] [int] NULL,
	[rule_id] [int] NOT NULL,
	[rule_type] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[insert_time] [datetime] NOT NULL,
	[counter] [int] NULL,
	[rule_name] [nvarchar](100) NULL,
	[is_private] [bit] NOT NULL CONSTRAINT [DF_vacounter_is_private]  DEFAULT ((0)),
	[details] [nvarchar](2000) NULL,
 CONSTRAINT [PK_vacounter] 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


-- CREATE VIEWS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_va_alarms] as select * from va_alarms
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_va_faces] as select * from va_faces
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_va_pos] as select * from va_pos
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vw_vacounter] as select * from vacounter
GO


-- CREATE INDEXES

CREATE NONCLUSTERED INDEX [ix_va_alarms_start_time] ON [dbo].[va_alarms]
(
	[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 [idx_va_pos_end_time] ON [dbo].[va_pos]
(
	[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 [idx_va_pos_start_time] ON [dbo].[va_pos]
(
	[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_va_counter_start_time] ON [dbo].[vacounter]
(
	[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
