USE [<#databaseName>]
GO;
SET ANSI_NULLS ON;
GO;

SET QUOTED_IDENTIFIER ON;
GO;

SET ANSI_PADDING ON;
GO;

-- MAIN IMAGE Database Table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ImageData')
BEGIN
CREATE TABLE [dbo].[ImageData](
	[SeqNumber] [int] IDENTITY(1,1) NOT NULL,
	[DocDefn] [varchar](50) NULL,
	[ReqID] [varchar](50) NULL,
	[FileFormat] [varchar](10) NULL,
	[ScanTime] [datetime] NULL,
	[ViewTime] [datetime] NULL,
	[RemoteViewTime] [datetime] NULL,
	[CDWriteTime] [datetime] NULL,
	[CDLabel] [varchar](50) NULL,
	[ZipName] [varchar](50) NULL,
	[ImageInfo] [text] NULL,
	[BarcodeData] [text] NULL,
	[OCRData] [text] NULL,
	[SentToSystem] [smallint] NOT NULL,
	[OriginalSeqNumber] [int] NULL,
	[Verified] [smallint] NULL,
	[Replicated] [smallint] NULL,
	[ScannerID] [varchar](10) NULL,
	[Archived] [smallint] NULL,
 CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED 
(
	[SeqNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'RemoteImage')
BEGIN
-- REMOTE Images working Table.
CREATE TABLE [dbo].[RemoteImage](
	[SeqNumber] [int] NOT NULL,
	[Image] [varbinary](max) NULL
) ON [PRIMARY]
END;
GO; 


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TaskQueue')
BEGIN
-- Tasks Table for Remote Management.
CREATE TABLE [dbo].[TaskQueue](
	[TaskID] [int] IDENTITY(1,1) NOT NULL,
	[Task] [int] NULL,
	[SeqNumber] [int] NULL,
	[ScannerID] [varchar](10) NULL,
	[TaskFrom] [varchar](50) NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TaskTypes')
BEGIN
-- Types of Task
CREATE TABLE [dbo].[TaskTypes](
	[TaskType] [smallint] NULL,
	[TaskCode] [varchar](10) NULL,
	[TaskDescription] [varchar](50) NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AutoscanUsersAndDomainGroups')
BEGIN
-- Users and Domain Groups
CREATE TABLE [dbo].[AutoscanUsersAndDomainGroups](
	[intUserId] [smallint] NOT NULL,
	[GroupCode] [smallint] NOT NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'AutoScanUsersAndDomains')
BEGIN
-- Actual Users and Domains
CREATE TABLE [dbo].[AutoScanUsersAndDomains](
	[intUserID] [smallint] IDENTITY(1,1) NOT NULL,
	[Type] [smallint] NOT NULL,
	[DomainName] [varchar](50) NULL,
	[UserName] [varchar](50) NOT NULL,
	[Description] [varchar](250) NULL,
	[PasswordHash] [varchar](32) NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'EventTypes')
BEGIN
-- Types of Events 
CREATE TABLE [dbo].[EventTypes](
	[EventType] [smallint] NULL,
	[EventCode] [varchar](10) NULL,
	[EventDescription] [varchar](50) NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ImageEvents')
BEGIN
-- Actual events table.
CREATE TABLE [dbo].[ImageEvents](
	[EventID] [int] IDENTITY(1,1) NOT NULL,
	[SeqNumber] [int] NULL,
	[EventType] [smallint] NULL,
	[ImageSystemUser] [varchar](50) NULL,
	[DomainUser] [varchar](50) NULL,
	[EventTime] [datetime] NULL,
	[EventInfo] [varchar](250) NULL,
 CONSTRAINT [PK_ImageEvents] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'LabelGroups')
BEGIN
-- Barcode Printing Labels.
CREATE TABLE [dbo].[LabelGroups](
	[id] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[PaperName] [varchar](50) NULL,
	[PaperOrientation] [smallint] NULL,
	[Units] [smallint] NULL,
	[MarginLeft] [real] NULL,
	[MarginTop] [real] NULL,
	[MarginRight] [real] NULL,
	[MarginBottom] [real] NULL,
	[LabelWidth] [real] NULL,
	[LabelHeight] [real] NULL,
	[InterLabelGapHorizontal] [real] NULL,
	[InterLabelGapVertical] [real] NULL
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'DocumentDetection')
BEGIN
-- Document Detection Types
CREATE TABLE [dbo].[DocumentDetection](
	[id] [smallint] IDENTITY(1,1) NOT NULL,
	[CodeLabel] [varchar](50) NOT NULL,
	[FileCode] [varchar](5) NULL,
	[Description] [varchar](max) NULL,
	[DetectionMethod] [smallint] NULL,
	[ViewOrientation] [smallint] NULL,
	[PageLimit] [smallint] NULL,
	[ReferenceMap] [smallint] NULL,
 CONSTRAINT [PK_DocumentDetection] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END;
GO;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'DetectionTypes')
BEGIN
-- Types of Detection
CREATE TABLE [dbo].[DetectionTypes](
	[id] [smallint] NOT NULL,
	[DetectionTypeName] [varchar](50) NULL,
	[Description] [varchar](250) NULL,
 CONSTRAINT [PK_DetectionTypes] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END;
GO;

--drop view [dbo].[V_ImageData_noDup]
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_ImageData_noDup')
  -- No Duplicate view
begin 
  execute(N'create VIEW [dbo].[V_ImageData_noDup] AS SELECT COUNT(ReqID + ''~'' + DocDefn) AS SeqCount, MIN(SeqNumber) AS SeqNumber FROM dbo.ImageData GROUP BY ReqID + ''~'' + DocDefn');
END; 
GO;


SET ANSI_PADDING OFF;
GO;




