/* Lab 4
   Philip Bock */

use L4;

-- Delete old tables
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Invoices_Items')
	drop table Invoices_Items;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Invoices')
	drop table Invoices;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Customers')
	drop table Customers;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Items')
	drop table Items;

-- Create tables
create table Customers (
	CustomerID	int identity(1,1) not null constraint PK_CustomerID primary key,
	LastName	varchar(100) not null,
	FirstName	varchar(100) not null,
	Phone		char(14) null constraint CK_Phone check (Phone Like '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
)

create table Invoices (
	InvoiceID	int identity(1,1) not null constraint PK_InvoiceID primary key,
	InvoiceDate	smalldatetime not null constraint CK_InvoiceDate check (InvoiceDate > getdate()),
	CustomerID	int not null constraint FK_CustomerID references Customers (CustomerID),
	Subtotal	money not null constraint CK_Subtotal check (Subtotal > 0),
	GST		tinyint not null constraint CK_GST check (GST >= 7),
	Total		as (Subtotal + (GST / 100 * Subtotal))
)

create table Items (
	ItemID		int identity(1,1) not null,
	ItemDescription	varchar(100) not null,
	CurrentPrice	smallmoney not null,
	constraint PK_ItemID primary key (ItemID)
)

create table Invoices_Items (
	InvoiceID	int not null constraint FK_InvoiceID references Invoices (InvoiceID),
	ItemID		int not null constraint FK_ItemID references Items (ItemID),
	Quantity	smallint not null,
	PriceChargedPerItem	smallmoney not null,
	AmountChargedForItems	smallmoney null,
	constraint PK_InvoiceID_ItemID primary key (InvoiceID, ItemID)
)
