/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNT]') AND type in (N'U'))

CREATE TABLE [dbo].[ACCOUNT](
	[CREATIONDATE] [datetime] NULL,
	[OPENBALANCE] [decimal](12, 2) NULL,
	[LOGOUTCOUNT] [int] NOT NULL,
	[BALANCE] [decimal](12, 2) NULL,
	[ACCOUNTID] [int] IDENTITY(0,1) NOT NULL,
	[LASTLOGIN] [datetime] NULL,
	[LOGINCOUNT] [int] NOT NULL,
	[PROFILE_USERID] [varchar](250) NULL,
 CONSTRAINT [PK_ACCOUNT] PRIMARY KEY CLUSTERED 
(
	[ACCOUNTID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNT]') AND name = N'USERID')
CREATE NONCLUSTERED INDEX [USERID] ON [dbo].[ACCOUNT] 
(
	[PROFILE_USERID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNTPROFILE]') AND type in (N'U'))

CREATE TABLE [dbo].[ACCOUNTPROFILE](
	[ADDRESS] [varchar](250) NULL,
	[PASSWORD] [varchar](250) NULL,
	[USERID] [varchar](250) NOT NULL,
	[EMAIL] [varchar](250) NULL,
	[CREDITCARD] [varchar](250) NULL,
	[FULLNAME] [varchar](250) NULL,
 CONSTRAINT [PK_ACCOUNTPROFILE] PRIMARY KEY CLUSTERED 
(
	[USERID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QUOTE]') AND type in (N'U'))

CREATE TABLE [dbo].[QUOTE](
	[LOW] [decimal](12, 2) NULL,
	[OPEN1] [decimal](12, 2) NULL,
	[VOLUME] [float] NOT NULL,
	[PRICE] [decimal](12, 2) NULL,
	[HIGH] [decimal](12, 2) NULL,
	[COMPANYNAME] [varchar](250) NULL,
	[SYMBOL] [varchar](250) NOT NULL,
	[CHANGE1] [float] NOT NULL,
 CONSTRAINT [PK_QUOTE] PRIMARY KEY CLUSTERED 
(
	[SYMBOL] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HOLDING]') AND type in (N'U'))

CREATE TABLE [dbo].[HOLDING](
	[PURCHASEPRICE] [decimal](12, 2) NULL,
	[HOLDINGID] [int] IDENTITY(0,1) NOT NULL,
	[QUANTITY] [float] NOT NULL,
	[PURCHASEDATE] [datetime] NULL,
	[ACCOUNT_ACCOUNTID] [int] NULL,
	[QUOTE_SYMBOL] [varchar](250) NULL,
 CONSTRAINT [PK_HOLDING] PRIMARY KEY CLUSTERED 
(
	[HOLDINGID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HOLDING]') AND name = N'IX_HOLDINGBYACCOUNT')
CREATE NONCLUSTERED INDEX [IX_HOLDINGBYACCOUNT] ON [dbo].[HOLDING] 
(
	[ACCOUNT_ACCOUNTID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ORDERS]') AND type in (N'U'))

CREATE TABLE [dbo].[ORDERS](
	[ORDERFEE] [decimal](12, 2) NULL,
	[COMPLETIONDATE] [datetime] NULL,
	[ORDERTYPE] [varchar](250) NULL,
	[ORDERSTATUS] [varchar](250) NULL,
	[PRICE] [decimal](12, 2) NULL,
	[QUANTITY] [float] NOT NULL,
	[OPENDATE] [datetime] NULL,
	[ORDERID] [int] IDENTITY(0,1) NOT NULL,
	[ACCOUNT_ACCOUNTID] [int] NULL,
	[QUOTE_SYMBOL] [varchar](250) NULL,
	[HOLDING_HOLDINGID] [int] NULL,
 CONSTRAINT [PK_ORDER] PRIMARY KEY CLUSTERED 
(
	[ORDERID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERS]') AND name = N'IX_ACCOUNT')
CREATE NONCLUSTERED INDEX [IX_ACCOUNT] ON [dbo].[ORDERS] 
(
	[ACCOUNT_ACCOUNTID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ORDERS]') AND name = N'IX_ACCOUNTANDSTATUS')
CREATE NONCLUSTERED INDEX [IX_ACCOUNTANDSTATUS] ON [dbo].[ORDERS] 
(
	[ORDERSTATUS] ASC,
	[ACCOUNT_ACCOUNTID] ASC
)WITH (IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]