USE [@initial.catalog@]

PRINT 'Creating table GLAccounts'
CREATE TABLE [dbo].[GLAccounts] (
	[AccountNo] [int] NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	CONSTRAINT [PK_GLAccounts] PRIMARY KEY  CLUSTERED 
	(
		[AccountNo]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

PRINT 'Creating table InvoiceLineItems'
CREATE TABLE [dbo].[InvoiceLineItems] (
	[InvoiceID] [int] NOT NULL ,
	[InvoiceSequence] [smallint] NOT NULL ,
	[AccountNo] [int] NOT NULL ,
	[Amount] [money] NOT NULL ,
	[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	CONSTRAINT [PK_InvoiceLineItems] PRIMARY KEY  CLUSTERED 
	(
		[InvoiceID],
		[InvoiceSequence]
	)  ON [PRIMARY]
) ON [PRIMARY]
GO

PRINT 'Creating table Invoices'
CREATE TABLE [dbo].[Invoices] (
	[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
	[VendorID] [int] NOT NULL ,
	[InvoiceNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[InvoiceDate] [smalldatetime] NOT NULL ,
	[InvoiceTotal] [money] NOT NULL ,
	[PaymentTotal] [money] NOT NULL CONSTRAINT [DF_Invoices_PaymentTotal] DEFAULT ((0)),
	[CreditTotal] [money] NOT NULL CONSTRAINT [DF_Invoices_CreditTotal] DEFAULT ((0)),
	[TermsID] [int] NOT NULL CONSTRAINT [DF_Invoices_TermsID] DEFAULT ((3)),
	[DueDate] [smalldatetime] NOT NULL ,
	[PaymentDate] [smalldatetime] NULL ,
	CONSTRAINT [PK_Invoices] PRIMARY KEY  CLUSTERED 
	(
		[InvoiceID]
	)  ON [PRIMARY]
) ON [PRIMARY]
GO

PRINT 'Creating table States'
CREATE TABLE [dbo].[States] (
	[StateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StateName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstZipCode] [int] NOT NULL ,
	[LastZipCode] [int] NOT NULL ,
	CONSTRAINT [PK_States] PRIMARY KEY  CLUSTERED 
	(
		[StateCode]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

PRINT 'Creating table Terms'
CREATE TABLE [dbo].[Terms] (
	[TermsID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DueDays] [smallint] NOT NULL ,
	CONSTRAINT [PK_Terms] PRIMARY KEY  CLUSTERED 
	(
		[TermsID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

PRINT 'Creating table Vendors'
CREATE TABLE [dbo].[Vendors] (
	[VendorID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ZipCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Phone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactFName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DefaultTermsID] [int] NOT NULL CONSTRAINT [DF_Vendors_DefaultTermsID] DEFAULT ((3)),
	[DefaultAccountNo] [int] NOT NULL CONSTRAINT [DF_Vendors_DefaultAccountNo] DEFAULT ((570)),
	CONSTRAINT [PK_Vendors] PRIMARY KEY  CLUSTERED 
	(
		[VendorID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO