/****** 2018-01-05 Version 11.0.4 ******/

/****** EXISTINGDATABASE is an existing database ******/
/****** NEWDATABASE will be created and data will be copied from EXISTINGDATABASE ******/
/****** Search and replace NEWDATABASE with the name of new database ******/
/****** Search and replace EXISTINGDATABASE with the name of the existing database ******/

USE [master]  
GO 
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'NEWDATABASE') 
DROP DATABASE [NEWDATABASE] 
GO
CREATE DATABASE [NEWDATABASE]  
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
USE [NEWDATABASE]  
GO 
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserDetailedLogTemp](
	[ServerName] [int] NULL,
	[UserName] [int] NULL,
	[UserHost] [int] NULL,
	[AppName] [int] NULL,
	[DateField] [datetime] NULL,
	[LingerValue] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp]
(
	[DateField] ASC
)
INCLUDE ( 	[ServerName],
	[UserName],
	[UserHost],
	[AppName],
	[LingerValue],
	[Licenses],
	[ProjectID]) 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 [IDIndex] ON [dbo].[UserDetailedLogTemp]
(
	[ID] 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_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp]
(
	[ServerName] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[Licenses] ASC,
	[LingerValue] ASC,
	[UserHost] ASC,
	[UserName] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SamplingInterval](
	[SamplingInterval] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserNameHostGroup](
	[HostName] [int] NOT NULL,
	[HostGroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameHostGroup]
(
	[HostName] ASC,
	[HostGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserNameGroup](
	[UserName] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameGroup]
(
	[UserName] ASC,
	[GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserDetails](
	[UserName] [nvarchar](100) NOT NULL,
	[UserDescription1] [nvarchar](255) NULL,
	[UserDescription2] [nvarchar](255) NULL,
	[UserDescription3] [nvarchar](255) NULL,
	[UserID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblUserDetails_1] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED 
(
	[UserName] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserDetailedLogPrev](
	[ServerName] [nvarchar](100) NULL,
	[UserName] [nvarchar](100) NULL,
	[UserHost] [nvarchar](100) NULL,
	[AppName] [nvarchar](100) NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL,
	[CheckedOutDateOrg] [datetime] NULL,
	[LicenseSystem] [nvarchar](100) NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLogPrev]
(
	[CheckedOutDate] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserDetailedLog](
	[ServerName] [nvarchar](100) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[UserHost] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserDetailedLog]
(
	[ServerName] ASC,
	[UserName] ASC,
	[UserHost] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[CheckedOutDate] ASC,
	[LicHandle] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLog]
(
	[CheckedOutDate] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUserCompleteDetailedLog](
	[ServerName] [nvarchar](100) NULL,
	[UserName] [nvarchar](100) NULL,
	[UserHost] [nvarchar](100) NULL,
	[AppName] [nvarchar](100) NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [defindex] ON [dbo].[tblUserCompleteDetailedLog]
(
	[DateField] ASC,
	[ServerName] ASC,
	[UserName] ASC,
	[UserHost] ASC,
	[AppName] ASC,
	[CheckedOutDate] ASC,
	[LicHandle] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblServerNames](
	[ServerName] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[EmailNotificationPing] [bit] NULL,
	[ServerID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblServerNames_1] PRIMARY KEY CLUSTERED 
(
	[ServerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblServerNames] UNIQUE NONCLUSTERED 
(
	[ServerName] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblServerGroupsJunction](
	[ServerGroupName] [nvarchar](100) NOT NULL,
	[ServerName] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblServerGroupsJunction]
(
	[ServerGroupName] ASC,
	[ServerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblServerGroups](
	[ServerGroupName] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[ServerGroupID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblServerGroups_1] PRIMARY KEY CLUSTERED 
(
	[ServerGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblServerGroups] UNIQUE NONCLUSTERED 
(
	[ServerGroupName] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblPingedHosts](
	[ServerName] [nvarchar](100) NOT NULL,
	[UserName] [nvarchar](100) NOT NULL,
	[UserHost] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[CheckedOutDate] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[PingDate] [datetime] NULL,
	[PingResult] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblHostGroups](
	[HostGroupId] [int] IDENTITY(1,1) NOT NULL,
	[HostGroupName] [nvarchar](100) NOT NULL,
	[HostGroupDescription] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
 CONSTRAINT [PK_tblHostGroups] PRIMARY KEY CLUSTERED 
(
	[HostGroupId] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblHostDetails](
	[HostName] [nvarchar](100) NOT NULL,
	[HostDescription1] [nvarchar](255) NULL,
	[HostDescription2] [nvarchar](255) NULL,
	[HostIPv4] [nvarchar](15) NULL,
	[HostIPv6] [nvarchar](39) NULL,
	[HostID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblHostDetails_1] PRIMARY KEY CLUSTERED 
(
	[HostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblHostDetails] UNIQUE NONCLUSTERED 
(
	[HostName] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblGroups](
	[GroupId] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [nvarchar](100) NOT NULL,
	[GroupDescription] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
 CONSTRAINT [PK_tblGroups] PRIMARY KEY CLUSTERED 
(
	[GroupId] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblDeniedUserLog](
	[UserName] [nvarchar](100) NOT NULL,
	[HostName] [nvarchar](100) NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[FirstDenial] [datetime] NULL,
	[LastDenial] [datetime] NULL,
	[OUT] [datetime] NULL,
	[Denials] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblDeniedUserLog]
(
	[UserName] ASC,
	[HostName] ASC,
	[AppName] ASC,
	[FirstDenial] ASC,
	[LastDenial] ASC,
	[OUT] ASC,
	[Denials] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAppName](
	[AppName] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[NoLog] [bit] NOT NULL,
	[EmailNotificationPercentageLevel] [int] NULL,
	[EmailNotificationLicensesLeft] [int] NULL,
	[EmailNotificationLicensesMax] [int] NULL,
	[EmailNotificationDenials] [bit] NOT NULL,
	[MaxLic] [int] NULL,
	[PriceFactorPerHour] [real] NULL,
	[DisplayAsUser] [bit] NOT NULL,
	[AppId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblAppName] PRIMARY KEY CLUSTERED 
(
	[AppId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblAppName] UNIQUE NONCLUSTERED 
(
	[AppName] 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
ALTER TABLE [dbo].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_NoLog]  DEFAULT ((0)) FOR [NoLog]
GO
ALTER TABLE [dbo].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_EmailNotificationDenials]  DEFAULT ((0)) FOR [EmailNotificationDenials]
GO
ALTER TABLE [dbo].[tblAppName] ADD  CONSTRAINT [DF_tblAppName_DisplayAsUser]  DEFAULT ((0)) FOR [DisplayAsUser]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAppGroupsJunction](
	[AppGroupName] [nvarchar](100) NOT NULL,
	[AppName] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblAppGroupsJunction]
(
	[AppGroupName] ASC,
	[AppName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAppGroups](
	[AppGroupName] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](255) NULL,
	[MaxLic] [int] NULL,
	[AppGroupID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblAppGroups_1] PRIMARY KEY CLUSTERED 
(
	[AppGroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_tblAppGroups] UNIQUE NONCLUSTERED 
(
	[AppGroupName] 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_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[log](
	[ServerName] [nvarchar](100) NOT NULL,
	[DateTime] [datetime] NOT NULL,
	[AppName] [nvarchar](100) NOT NULL,
	[MaxLic] [int] NULL,
	[UsedLic] [int] NULL,
	[Linger] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[log]
(
	[DateTime] 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 UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[log]
(
	[ServerName] ASC,
	[DateTime] ASC,
	[AppName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblWildcard](
	[ItemType] [nvarchar](10) NOT NULL,
	[Wildcard] [nvarchar](100) NOT NULL,
	[ItemGroup] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblProjects](
	[ProjectID] [int] IDENTITY(1,1) NOT NULL,
	[Project] [nvarchar](50) NOT NULL,
	[IsActive] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProjects] ADD  CONSTRAINT [DF_tblProjects_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblProjects]
(
	[Project] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

USE [EXISTINGDATABASE]  
GO

CREATE TABLE [dbo].[UserDetailedLogTemp](
	[ServerName] [int] NULL,
	[UserName] [int] NULL,
	[UserHost] [int] NULL,
	[AppName] [int] NULL,
	[DateField] [datetime] NULL,
	[LingerValue] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp]
(
	[DateField] ASC
)
INCLUDE ( 	[ServerName],
	[UserName],
	[UserHost],
	[AppName],
	[LingerValue],
	[Licenses],
	[ProjectID]) 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 [IDIndex] ON [dbo].[UserDetailedLogTemp]
(
	[ID] 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_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp]
(
	[ServerName] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[Licenses] ASC,
	[LingerValue] ASC,
	[UserHost] ASC,
	[UserName] 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 TABLE tblWildcard (ItemType nvarchar(10) NOT NULL,Wildcard nvarchar(100) NOT NULL,ItemGroup nvarchar(100) NOT NULL)
GO

CREATE TABLE SamplingInterval (SamplingInterval int NULL)
GO

ALTER TABLE tblHostDetails ADD HostIPv4 nvarchar(15)
GO

ALTER TABLE tblHostDetails ADD HostIPv6 nvarchar(39)
GO

CREATE TABLE tblProjects (ProjectID int IDENTITY(1,1) NOT NULL CONSTRAINT PKeyMyId PRIMARY KEY,Project nvarchar(50) NOT NULL,IsActive smallint NOT NULL)
GO
ALTER TABLE tblProjects ADD  CONSTRAINT DF_tblProjects_IsActive  DEFAULT ((1)) FOR IsActive
GO
CREATE UNIQUE INDEX unique1 ON tblProjects (Project)
GO

CREATE TABLE tblUserCompleteDetailedLog (ServerName nvarchar(100), UserName nvarchar(100), UserHost nvarchar(100), AppName nvarchar(100), DateField datetime, LingerDate datetime, CheckedOutDate datetime, LicHandle INTEGER, Licenses INTEGER)
GO
CREATE INDEX defindex ON tblUserCompleteDetailedLog (DateField, ServerName, UserName, UserHost, AppName, CheckedOutDate,LicHandle)
GO

CREATE INDEX dt ON dbo.log(DateTime)
GO

ALTER TABLE tblUserDetailedLog ADD ProjectID int
GO

CREATE INDEX dt ON dbo.tblUserDetailedLog(CheckedOutDate)
GO

ALTER TABLE tblUserDetailedLogPrev ADD ProjectID int
GO

CREATE INDEX dt ON dbo.tblUserDetailedLogPrev(CheckedOutDate)
GO

ALTER TABLE tblAppName ADD DisplayAsUser bit DEFAULT 0
GO

ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_NoLog  DEFAULT ((0)) FOR NoLog
GO

ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_EmailNotificationDenials  DEFAULT ((0)) FOR EmailNotificationDenials
GO

ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_DisplayAsUser  DEFAULT ((0)) FOR DisplayAsUser
GO

ALTER TABLE tblHostGroups ADD MaxLic int
GO

Alter Table tblGroups Drop CONSTRAINT PK_tblGroups
GO
Alter Table tblGroups Drop Column GroupId
GO
ALTER TABLE tblGroups ADD GroupId int IDENTITY(1,1) NOT NULL
GO
ALTER TABLE tblGroups WITH NOCHECK ADD CONSTRAINT PK_tblGroups PRIMARY KEY CLUSTERED (GroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Alter Table tblHostGroups Drop CONSTRAINT PK_tblHostGroups
GO
Alter Table tblHostGroups Drop Column HostGroupId
GO
ALTER TABLE tblHostGroups ADD HostGroupId int IDENTITY(1,1) NOT NULL
GO
ALTER TABLE tblHostGroups WITH NOCHECK ADD CONSTRAINT PK_tblHostGroups PRIMARY KEY CLUSTERED (HostGroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp] ([ID])
GO

CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp] ([ServerName] ASC,[AppName] ASC,[DateField] ASC,[Licenses] ASC,[LingerValue] ASC,[UserHost] ASC,[UserName] ASC)
GO

CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp] ([DateField]) INCLUDE ([ServerName],[UserName],[UserHost],[AppName],[LingerValue],[Licenses],[ProjectID])
GO

USE [NEWDATABASE]  
GO 

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.log') AND type in (N'U'))
INSERT dbo.log
SELECT [ServerName]
      ,[DateTime]
      ,[AppName]
      ,MAX(MaxLic) AS Expr1
      ,MAX(UsedLic) AS Expr2
      ,MAX(Linger) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[log]
  GROUP BY ServerName, DateTime, AppName
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppGroups') AND type in (N'U'))
INSERT dbo.tblAppGroups
SELECT [AppGroupName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblAppGroups]
  GROUP BY [AppGroupName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppGroupsJunction') AND type in (N'U'))
INSERT dbo.tblAppGroupsJunction
SELECT [AppGroupName]
      ,[AppName]
  FROM [EXISTINGDATABASE].[dbo].[tblAppGroupsJunction]
  GROUP BY [AppGroupName],[AppName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblAppName') AND type in (N'U'))
INSERT dbo.tblAppName
SELECT [AppName]
      ,MAX([Description]) AS Expr1
      ,CAST(MAX(CAST([NoLog] as INT)) AS BIT) AS Expr2
      ,MAX([EmailNotificationPercentageLevel]) AS Expr3
      ,MAX([EmailNotificationLicensesLeft]) AS Expr4
      ,MAX([EmailNotificationLicensesMax]) AS Expr5
      ,CAST(MAX(CAST([EmailNotificationDenials] as INT)) AS BIT) AS Expr6
      ,MAX([MaxLic]) AS Expr7
      ,MAX([PriceFactorPerHour]) AS Expr8
      ,CAST(MAX(CAST([DisplayAsUser] as INT)) AS BIT) AS Expr9
  FROM [EXISTINGDATABASE].[dbo].[tblAppName]
  GROUP BY [AppName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblDeniedUserLog') AND type in (N'U'))
INSERT dbo.tblDeniedUserLog
SELECT DISTINCT [UserName]
      ,[HostName]
      ,[AppName]
      ,[FirstDenial]
      ,[LastDenial]
      ,[OUT]
      ,[Denials]
  FROM [EXISTINGDATABASE].[dbo].[tblDeniedUserLog]
GO

SET IDENTITY_INSERT dbo.tblGroups ON
GO
IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblGroups') AND type in (N'U'))
INSERT dbo.tblGroups
([GroupId]
,[GroupName]
,[GroupDescription]
,[MaxLic]
)
SELECT [GroupId]
      ,[GroupName]
      ,[GroupDescription]
      ,[MaxLic]
  FROM [EXISTINGDATABASE].[dbo].[tblGroups]
GO
SET IDENTITY_INSERT dbo.tblGroups OFF
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblHostDetails') AND type in (N'U'))
INSERT dbo.tblHostDetails
SELECT [HostName]
      ,MAX([HostDescription1]) AS Expr1
      ,MAX([HostDescription2]) AS Expr2
      ,MAX([HostIPv4]) AS Expr3
      ,MAX([HostIPv6]) AS Expr4
  FROM [EXISTINGDATABASE].[dbo].[tblHostDetails]
  GROUP BY [HostName]
GO

SET IDENTITY_INSERT dbo.tblHostGroups ON
GO
IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblHostGroups') AND type in (N'U'))
INSERT dbo.tblHostGroups
([HostGroupId]
,[HostGroupName]
,[HostGroupDescription]
,[MaxLic]
)
SELECT [HostGroupId]
      ,[HostGroupName]
      ,[HostGroupDescription]
      ,[MaxLic]
  FROM [EXISTINGDATABASE].[dbo].[tblHostGroups]
GO
SET IDENTITY_INSERT dbo.tblHostGroups OFF
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblPingedHosts') AND type in (N'U'))
INSERT dbo.tblPingedHosts
SELECT DISTINCT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[CheckedOutDate]
      ,[LingerDate]
      ,[LicHandle]
      ,[PingDate]
      ,[PingResult]
  FROM [EXISTINGDATABASE].[dbo].[tblPingedHosts]
GO

SET IDENTITY_INSERT dbo.tblProjects ON
GO
IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblProjects') AND type in (N'U'))
INSERT dbo.tblProjects
([ProjectID]
,[Project]
,[IsActive]
)
SELECT MAX([ProjectID]) AS Expr1
      ,[Project]
      ,MAX([IsActive]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblProjects]
  GROUP BY [Project]
GO
SET IDENTITY_INSERT dbo.tblProjects OFF
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerGroups') AND type in (N'U'))
INSERT dbo.tblServerGroups
SELECT [ServerGroupName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblServerGroups]
  GROUP BY [ServerGroupName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerGroupsJunction') AND type in (N'U'))
INSERT dbo.tblServerGroupsJunction
SELECT [ServerGroupName]
      ,[ServerName]
  FROM [EXISTINGDATABASE].[dbo].[tblServerGroupsJunction]
  GROUP BY [ServerGroupName],[ServerName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblServerNames') AND type in (N'U'))
INSERT dbo.tblServerNames
SELECT [ServerName]
      ,MAX([Description]) AS Expr1
      ,MAX([MaxLic]) AS Expr2
      ,CAST(MAX(CAST([EmailNotificationPing] as INT)) AS BIT) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[tblServerNames]
  GROUP BY [ServerName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserCompleteDetailedLog') AND type in (N'U'))
INSERT dbo.tblUserCompleteDetailedLog
SELECT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,MAX([LingerDate]) AS Expr1
      ,[CheckedOutDate]
      ,[LicHandle]
      ,MAX([Licenses]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblUserCompleteDetailedLog]
  GROUP BY [DateField],[ServerName],[UserName],[UserHost],[AppName],[CheckedOutDate],[LicHandle]
GO

SET IDENTITY_INSERT dbo.tblUserDetailedLog ON
GO
IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetailedLog') AND type in (N'U'))
INSERT dbo.tblUserDetailedLog
([ServerName]
,[UserName]
,[UserHost]
,[AppName]
,[DateField]
,[LingerDate]
,[CheckedOutDate]
,[LicHandle]
,[Licenses]
,[ID]
,[ProjectID]
)
SELECT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,MAX([LingerDate]) AS Expr1
      ,[CheckedOutDate]
      ,[LicHandle]
      ,MAX([Licenses]) AS Expr2
      ,MAX([ID]) AS Expr3
      ,MAX([ProjectID]) AS Expr4
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLog]
  GROUP BY [ServerName],[UserName],[UserHost],[AppName],[DateField],[CheckedOutDate],[LicHandle]
GO
SET IDENTITY_INSERT dbo.tblUserDetailedLog OFF
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetailedLogPrev') AND type in (N'U'))
INSERT dbo.tblUserDetailedLogPrev
SELECT DISTINCT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,[LingerDate]
      ,[CheckedOutDate]
      ,[LicHandle]
      ,[Licenses]
      ,[CheckedOutDateOrg]
      ,[LicenseSystem]
      ,[ProjectID]
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetailedLogPrev]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserDetails') AND type in (N'U'))
INSERT dbo.tblUserDetails
SELECT [UserName]
      ,MAX([UserDescription1]) AS Expr1
      ,MAX([UserDescription2]) AS Expr2
      ,MAX([UserDescription3]) AS Expr3
  FROM [EXISTINGDATABASE].[dbo].[tblUserDetails]
  GROUP BY [UserName]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserNameGroup') AND type in (N'U'))
INSERT dbo.tblUserNameGroup
SELECT [UserName]
      ,[GroupId]
      ,MAX([FromDate]) AS Expr1
      ,MAX([ToDate]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblUserNameGroup]
  GROUP BY [UserName],[GroupId]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblUserNameHostGroup') AND type in (N'U'))
INSERT dbo.tblUserNameHostGroup
SELECT [HostName]
      ,[HostGroupId]
      ,MAX([FromDate]) AS Expr1
      ,MAX([ToDate]) AS Expr2
  FROM [EXISTINGDATABASE].[dbo].[tblUserNameHostGroup]
  GROUP BY [HostName],[HostGroupId]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.tblWildcard') AND type in (N'U'))
INSERT dbo.tblWildcard
SELECT DISTINCT [ItemType]
      ,[Wildcard]
      ,[ItemGroup]
  FROM [EXISTINGDATABASE].[dbo].[tblWildcard]
GO

IF EXISTS (SELECT * FROM EXISTINGDATABASE.sys.objects WHERE object_id = OBJECT_ID(N'EXISTINGDATABASE.dbo.UserDetailedLogTemp') AND type in (N'U'))
INSERT dbo.UserDetailedLogTemp
SELECT DISTINCT [ServerName]
      ,[UserName]
      ,[UserHost]
      ,[AppName]
      ,[DateField]
      ,[LingerValue]
      ,[Licenses]
      ,[ID]
      ,[ProjectID]
  FROM [EXISTINGDATABASE].[dbo].[UserDetailedLogTemp]
GO

update agj set agj.AppName = ApNme.appID from tblAppGroupsJunction agj join tblAppName as ApNme on agj.AppName = ApNme.AppID
GO

update sgj set sgj.ServerName = srvr.serverID from tblServerGroupsJunction sgj join tblServerNames as srvr on sgj.ServerName = srvr.ServerID
GO

update ugj set ugj.UserName = usr.UserID from tblUserNameGroup ugj join tblUserDetails as usr on ugj.UserName = usr.UserID
GO

update hgj set hgj.HostName = hst.HostID from tblUserNameHostGroup hgj join tblHostDetails as hst on hgj.HostName = hst.HostID
GO