USE [VIGIL-Analytics]
GO

-- Update table schema and database parameters here.

IF (SELECT version FROM table_version WHERE entity = 'alarmevents') < 1
	ALTER TABLE alarmevents ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_daily_user_summary') < 1
	ALTER TABLE audit_daily_user_summary ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_details') < 1
	ALTER TABLE audit_details ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_details_info') < 1
	ALTER TABLE audit_details_info ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_details_info_types') < 1
	ALTER TABLE audit_details_info_types ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_event_types') < 1
	ALTER TABLE audit_event_types ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_events') < 1
	ALTER TABLE audit_events ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_report_criteria_types') < 1
	ALTER TABLE audit_report_criteria_types ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_report_criterias') < 1
	ALTER TABLE audit_report_criterias ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_reports') < 1
	ALTER TABLE audit_reports ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'audit_users') < 1
	ALTER TABLE audit_users ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'grouppermission') < 1
	ALTER TABLE grouppermission ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'groups') < 1
	ALTER TABLE groups ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'perms') < 1
	ALTER TABLE perms ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'pos_camera_mapping') < 1
	ALTER TABLE pos_camera_mapping ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'pos_camera_mapping') < 2
	ALTER TABLE pos_camera_mapping ADD preset int NOT NULL DEFAULT (-1)

	ALTER TABLE pos_camera_mapping DROP CONSTRAINT uc_pos_camera_mapping

	ALTER TABLE pos_camera_mapping ADD CONSTRAINT [uc_pos_camera_mapping] UNIQUE NONCLUSTERED
	(	[vcm_dvr_id] ASC,
		[terminal] ASC,
		[register] ASC,
		[camera] ASC,
		[preset] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

IF (SELECT version FROM table_version WHERE entity = 'posdata') < 1
	ALTER TABLE posdata ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'usergroup') < 1
	ALTER TABLE usergroup ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'usergroup') < 2
	ALTER TABLE usergroup ADD CONSTRAINT [uc_vcm_dvr_id_username_groupname] UNIQUE NONCLUSTERED
	(
		[vcm_dvr_id] ASC,
		[username] ASC,
		[groupname] 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 table_version WHERE entity = 'users') < 1
	ALTER TABLE users ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'va_alarms') < 1
	ALTER TABLE va_alarms ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'va_alarms') < 2
	ALTER TABLE va_alarms ADD details nvarchar(2000) NULL
GO

IF (SELECT version FROM table_version WHERE entity = 'va_faces') < 1
	ALTER TABLE va_faces ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'va_pos') < 1
	ALTER TABLE va_pos ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'vacounter') < 1
	ALTER TABLE vacounter ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'vacounter') < 2
	ALTER TABLE vacounter ADD details nvarchar(2000) NULL
GO

IF (SELECT version FROM table_version WHERE entity = 'vpos_alarm') < 1
	ALTER TABLE vpos_alarm ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'vpos_exception') < 1
	ALTER TABLE vpos_exception ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'vpos_exceptionrecipient') < 1
	ALTER TABLE vpos_exceptionrecipient ADD vcm_insert_time datetime NOT NULL DEFAULT GETDATE()
GO

IF (SELECT version FROM table_version WHERE entity = 'DVR') < 1
	ALTER TABLE DVR ADD group_path nvarchar(512) NULL
GO
