use master
go
if exists(select * from sysdatabases where name='shopping450')
	drop database shopping450
go
create database shopping450
go
use shopping450
go
create table [dbo].[affiliates] (
	[affid] [int] identity (1, 1) not null ,
	[firstname] [varchar] (50) null ,
	[lastname] [varchar] (50) null ,
	[address] [varchar] (255) null ,
	[city] [varchar] (100) null ,
	[state] [varchar] (100) null ,
	[postcode] [varchar] (20) null ,
	[country] [varchar] (100) null ,
	[company] [varchar] (255) null ,
	[phone] [varchar] (50) null ,
	[website] [varchar] (50) null ,
	[workphone] [varchar] (30) null ,
	[mobilephone] [varchar] (30) null ,
	[fax] [varchar] (30) null ,
	[email] [varchar] (50) null ,
	[comment] [varchar] (255) null ,
	[logincount] [int] null ,
	[logindate] [datetime] null ,
	[logintime] [datetime] null ,
	[affother1] [varchar] (100) null ,
	[affother2] [varchar] (255) null ,
	[affother3] [varchar] (100) null ,
	[commissionrate] [real] null ,
	[orders] [int] null ,
	[totalcommission] [money] null ,
	[typeofpayment] [varchar] (50) null ,
	[bank] [varchar] (100) null ,
	[bankaccount] [varchar] (50) null ,
	[bankbranch] [varchar] (100) null ,
	[bankaddress] [varchar] (255) null ,
	[bankother] [varchar] (255) null ,
	[password] [varchar] (50) null ,
	[directurl] [varchar] (255) null,
	[referralid] [int] null,
	[mastercommissionrate] [real]
 
) on [primary]
go
create table [dbo].[suppliers] (
	[supplierid] [int] identity (1, 1) not null ,
	[supplieruserid] [varchar] (50) null ,
	[name] [varchar] (255) null ,
	[firstname] [varchar] (10) null ,
	[lastname] [varchar] (100) null ,
	[address] [varchar] (255) null ,
	[city] [varchar] (100) null ,
	[state] [varchar] (100) null ,
	[postcode] [varchar] (20) null ,
	[country] [varchar] (100) null ,
	[company] [varchar] (255) null ,
	[phone] [varchar] (50) null ,
	[website] [varchar] (50) null ,
	[workphone] [varchar] (30) null ,
	[mobilephone] [varchar] (30) null ,
	[fax] [varchar] (30) null ,
	[email] [varchar] (50) null ,
	[comment] [varchar] (255) null ,
	[logincount] [int] null ,
	[logindate] [datetime] null ,
	[logintime] [datetime] null ,
	[supplierpassword] [varchar] (50) null ,
	[other1] [varchar] (100) null ,
	[commissionrate] [real] null ,
	[orders] [int] null ,
	[template] [varchar] (255) null ,
	[account] [varchar] (50) null 
) on [primary]
go

create table [dbo].[categories] (
	[categoryid] [int] identity (1, 1) not null ,
	[catdescription] [varchar] (255) null ,
	[highercategoryid] [int]  null ,
	[hassubcategory] [varchar] (10) null ,
	[catimage] [varchar] (100) null,
	[cathide] [varchar] (10) null , 
	[catextra] [varchar] (255) null,
	[customermatch] [varchar] (100) null,
	[productmatch] [varchar] (100) null,
	[catmemo] varchar (2000) NULL
) on [primary]
go

create table [dbo].[coupons] (
	[couponid] [int] identity (1, 1) not null ,
	[couponname] [varchar] (50) null ,
	[couponpercent] [real] null ,
	[couponamount] [money] null ,
	[couponstartdate] [datetime] null ,
	[couponenddate] [datetime] null ,
	[couponlimit] [int] null ,
	[couponcategories] [varchar] (255) null ,
	[couponproducts] [varchar] (255) null ,
	[couponusedcount] [int] null ,
	[couponlastuseddate] [datetime] null ,
	[couponcomment] [varchar] (255) null ,
	[couponother] [varchar] (100) null 
) on [primary]
go

create table [dbo].[customerprices] (
	[id] [int] identity (1, 1) not null ,
	[customerid] [int] null ,
	[categoryid] [int] null ,
	[catalogid] [int] null ,
	[discountpercent] [float] null ,
	[discountamount] [money] null 
) on [primary]
go

create table [dbo].[customers] (
	[contactid] [int] identity (1, 1) not null ,
	[firstname] [varchar] (50) null ,
	[lastname] [varchar] (50) null ,
	[address] [varchar] (255) null ,
	[city] [varchar] (100) null ,
	[state] [varchar] (100) null ,
	[postcode] [varchar] (20) null ,
	[country] [varchar] (100) null ,
	[company] [varchar] (255) null ,
	[phone] [varchar] (50) null ,
	[workphone] [varchar] (30) null ,
	[mobilephone] [varchar] (30) null ,
	[fax] [varchar] (30) null ,
	[email] [varchar] (50) null ,
	[contacttypeid] [int] null ,
	[comments] [varchar] (255) null ,
	[contactreason] [varchar] (20) null ,
	[logincount] [int] null ,
	[lastlogindate] [datetime] null ,
	[website] [varchar] (200) null ,
	[password] [varchar] (50) null ,
	[discount] [float] null ,
	[custother1] [varchar] (100) null ,
	[accountno] [varchar] (50) null ,
	[cardno] [varchar] (20) null ,
	[cardexpires] [varchar] (10) null ,
	[cardname] [varchar] (100) null ,
	[cardaddress] [varchar] (200) null ,
	[cardcvn] [varchar] (50) null,
	[maillist] [bit] null,
	[productgroup] [varchar](100),
	[cookiequestion] [bit],
	[pointstotal] int NULL,
	[pointsremaining] int NULL,
	[vatnumber] varchar (50) NULL
) on [primary]
go

create table [dbo].[gifts] (
	[giftid] [int] identity (1, 1) not null ,
	[giftnumber] [varchar] (100) null ,
	[giftissuedate] [datetime] null ,
	[giftauthorized] [varchar] (10) null ,
	[giftamount] [money] null ,
	[giftamountremaining] [money] null ,
	[giftorderid] [int] null ,
	[giftcustomerid] [int] null ,
	[gifttoname] [varchar] (255) null ,
	[giftfromname] [varchar] (255) null ,
	[gifttoemail] [varchar] (255) null ,
	[giftmessage] [varchar] (255) null ,
	[giftfromemail] [varchar] (255) null ,
	[giftuseddate] [datetime] null ,
	[giftusedcustomerid] [int] null ,
	[giftusedlastamount] [money] null ,
	[giftusedorderid] [int] null ,
	[giftother] [varchar] (255) null ,
	[giftexpirydate] [datetime] null ,
	[giftusedcount] [int] null ,
	[giftusedtrace] [varchar] (255) null 
) on [primary]
go

create table [dbo].[mycompany] (
	[setupid] [int] identity (1, 1) not null ,
	[companyname] [varchar] (255) null ,
	[address] [varchar] (255) null ,
	[city] [varchar] (100) null ,
	[state] [varchar] (50) null ,
	[postalcode] [varchar] (50) null ,
	[country] [varchar] (50) null ,
	[phonenumber] [varchar] (50) null ,
	[faxnumber] [varchar] (50) null ,
	[myemail] [varchar] (255) null ,
	[other1] [varchar] (150) null ,
	[other2] [varchar] (150) null ,
	[other3] [varchar] (150) null ,
	[other4] [varchar] (150) null 
) on [primary] 
go

create table [dbo].[oitems] (
	[orderitemid] [int] identity (1, 1) not null ,
	[orderid] [int] null ,
	[catalogid] [int] null ,
	[numitems] [int] null ,
	[itemname] [varchar] (1000) null ,
	[unitprice] [money] null ,
	[dualprice] [float] null,
	[supplierid] [int] null,  
	[address] [varchar](1000) null,
	[features] [varchar](255) null  
) on [primary]
go

create table [dbo].[orders] (
	[orderid] [int] identity (1, 1) not null ,
	[ocustomerid] [int] null ,
	[odate] [datetime] null ,
	[orderamount] [money] null ,
	[ofirstname] [varchar] (100) null ,
	[olastname] [varchar] (100) null ,
	[oemail] [varchar] (100) null ,
	[oaddress] [varchar] (200) null ,
	[ocity] [varchar] (100) null ,
	[opostcode] [varchar] (15) null ,
	[ostate] [varchar] (100) null ,
	[ocountry] [varchar] (50) null ,
	[ophone] [varchar] (30) null ,
	[ofax] [varchar] (30) null ,
	[ocompany] [varchar] (255) null ,
	[ocardtype] [varchar] (50) null ,
	[ocardno] [varchar] (20) null ,
	[ocardname] [varchar] (200) null ,
	[ocardexpires] [varchar] (10) null ,
	[ocardaddress] [varchar] (150) null ,
	[oprocessed] [bit] not null ,
	[ocomment] [varchar] (255) null ,
	[otax] [money] null ,
	[opromisedshipdate] [datetime] null ,
	[oshippeddate] [datetime] null ,
	[oshipmethod] [int] null ,
	[oshipcost] [money] null ,
	[oshipname] [varchar] (100) null ,
	[oshipcompany] [varchar] (200) null ,
	[oshipemail] [varchar] (100) null ,
	[oshipmethodtype] [varchar] (100) null ,
	[oshipaddress] [varchar] (255) null ,
	[oshiptown] [varchar] (50) null ,
	[oshipzip] [varchar] (20) null ,
	[oshipstate] [varchar] (50) null ,
	[oshipcountry] [varchar] (50) null ,
	[opaymethod] [int] null ,
	[other1] [varchar] (50) null ,
	[other2] [varchar] (50) null ,
	[otime] [datetime] null ,
	[oauthorization] [varchar] (255) null ,
	[oerrors] [varchar] (255) null ,
	[odiscount] [float] null ,
	[ostatus] [varchar] (255) null ,
	[oaffid] [int] null ,
	[odualtotal] [real] null ,
	[odualtaxes] [real] null ,
	[odualshipping] [real] null ,
	[odualdiscount] [real] null ,
	[ohandling] [real] null ,
	[odualhandling] [real] null ,
	[coupon] [varchar] (100) null ,
	[coupondiscount] [money] null ,
	[coupondiscountdual] [real] null ,
	[giftcertificate] [varchar] (100) null ,
	[giftamountused] [money] null ,
	[giftamountuseddual] [real] null,
	[canceled] [bit] null,
	[ipaddress] [varchar] (100) null,  
	[hackeryesno] [bit] null,
	[customercancel] [bit] null,
	[opoints] int NULL,
	[vatnumber] varchar (50) NULL,
	[commission] money NULL
) on [primary]
go

create table [dbo].[prodfeatures] (
	[id] [int] identity (1, 1) not null ,
	[featurenum] [int] null ,
	[featurecaption] [varchar] (50) null ,
	[featurename] [varchar] (100) null ,
	[featureprice] [money] null ,
	[featureother] [varchar] (100) null ,
	[featuretype] [varchar] (20) null ,
	[featuremulti] [varchar] (20) null, 
	[featurerequired] [bit]  null,
	[featureother1] [varchar] (50) null  
) on [primary]
go

create table [dbo].[products] (
	[catalogid] [int] identity (1, 1) not null ,
	[ccode] [varchar] (50) null ,
	[cname] [varchar] (255) null ,
	[cdescription] [varchar] (1000) null ,
	[cprice] [money] null ,
	[ccategory] [int] null ,
	[cdescurl] [varchar] (100) null ,
	[features] [varchar] (100) null ,
	[cimageurl] [varchar] (100) null ,
	[cstock] [int] null ,
	[weight] [varchar] (20) null ,
	[mfg] [varchar] (255) null ,
	[pother1] [varchar] (50) null ,
	[pother2] [varchar] (100) null ,
	[pother3] [varchar] (50) null ,
	[subcategoryid] [int] null ,
	[retailprice] [money] null ,
	[specialoffer] [varchar] (100) null ,
	[category] [varchar] (50) null ,
	[buttonimage] [varchar] (100) null ,
	[cdateavailable] [datetime] null ,
	[allowusertext] [varchar] (100) null ,
	[pother4] [varchar] (255) null ,
	[pother5] [varchar] (50) null ,
	[userid] [varchar] (50) null ,
	[keywords] [varchar] (255) null ,
	[template] [varchar] (100) null ,
	[extendedimage] [varchar] (100) null ,
	[extendeddesc] [varchar] (1000) null ,
	[selectlist] [varchar] (255) null ,
	[level3] [varchar] (255) null ,
	[level4] [varchar] (255) null ,
	[level5] [varchar] (255) null ,
	[minimumquantity] [int] null,
	[supplierid] [int] null,
	[crossselling] [varchar] (255) null,
	[hide] [bit] null,
	[productmatch] [varchar] (100) null,
	[customermatch] [varchar] (100) null,
	[orderattachment] [varchar] (255) null,
	[orderdownload] [varchar] (255) null,
	[groupfordiscount] [varchar] (50) null,
	[clanguage] [varchar] (50) null,
	[points] int NULL,
	[pointstobuy] int NULL,
	[price2] money NULL,
	[price3] money NULL
) on [primary]
go

create table [dbo].[quantitydiscounts] (
	[id] [int] identity (1, 1) not null ,
	[categoryid] [int] null ,
	[catalogid] [int] null ,
	[minquantity] [int] null ,
	[quantity1] [int] null ,
	[discount1] [real] null ,
	[quantity2] [int] null ,
	[discount2] [real] null ,
	[quantity3] [int] null ,
	[discount3] [real] null ,
	[quantity4] [int] null ,
	[discount4] [real] null ,
	[quantity5] [int] null ,
	[discount5] [real] null,
	[groupfordiscount] [varchar] (50) null, 
	[other] [varchar] (50) null 
) on [primary]
go

create table [dbo].[searchresults] (
	[id] [int] identity (1, 1) not null ,
	[rdate] [datetime] null ,
	[rtime] [datetime] null ,
	[categories] [varchar] (200) null ,
	[subcategories] [varchar] (200) null ,
	[words] [varchar] (255) null ,
	[lastname] [varchar] (50) null ,
	[customerid] [int] null ,
	[ipaddress] [varchar] (50) null ,
	[rother1] [varchar] (100) null ,
	[rother2] [varchar] (100) null 
) on [primary]
go

create table [dbo].[reviews] (
	[id] [int] identity (1, 1) not null ,
	[catalogid] [int] null ,
	[reviewdate] [datetime] null ,
	[reviewtime] [datetime] null ,
	[rating] [int] null ,
	[title] [varchar] (255) null ,
	[comment] [varchar] (2000) null ,
	[location] [varchar] (100) null ,
	[name] [varchar] (100) null ,
	[email] [varchar] (100) null ,
	[other1] [varchar] (100) null ,
	[other2] [varchar] (100) null,
	[authorized] [varchar] (10) null
) on [primary]
go


create table [dbo].[shipmethods] (
	[shipmethodid] [int] identity (1, 1) not null ,
	[shipmethod] [varchar] (50) null ,
	[smprice] [money] null ,
	[shipbasecost] [money] null ,
	[shipextracost] [money] null ,
	[shipother1] [varchar] (50) null ,
	[shipother2] [varchar] (50) null ,
	[shipcost1] [money] null ,
	[shipcost2] [money] null,
	[shiproutine] [varchar] (255)  
) on [primary]
go


create table [dbo].[tblaccess] (
	[fldauto] [int] identity (1, 1) not null ,
	[fldname] [varchar] (255) null ,
	[fldurl] [varchar] (255) null ,
	[fldcomment] [varchar] (255) null ,
	[flddatabase] [varchar] (50) null 
) on [primary]
go

create table [dbo].[tbllog] (
	[fldauto] [int] identity (1, 1) not null ,
	[fldusername] [varchar] (50) null ,
	[fldtime] [datetime] null ,
	[flddate] [datetime] null ,
	[fldinout] [varchar] (50) null,
	[fldipaddress] varchar(40) NULL   
) on [primary]
go

create table [dbo].[tbluser] (
	[fldauto] [int] identity (1, 1) not null ,
	[fldusername] [varchar] (50) null ,
	[fldpassword] [varchar] (50) null ,
	[fldaccess] [varchar] (255) null ,
	[flddatabase] [varchar] (50) null ,
	[tablesallowed] [varchar] (1000) null ,
	[admintype] [varchar] (10) null ,
	[uploaddirectory] [varchar] (100) null ,
	[adminother1] [varchar] (100) null 
) on [primary]
go
	
create table [dbo].[prodcategories] (
	[id] [int] identity (1, 1) not null ,
	[intcategoryid] [int] null ,
	[intcatalogid] [int]  null
) on [primary]
go

create table [dbo].[ordertracking] (
	[trackid] [int] identity (1, 1) not null ,
	[orderid] [int] null ,
	[trackcomment] [varchar] (2000) null ,
	[trackname] [varchar] (100) null ,
	[trackdate] [datetime] null ,
	[tracktime] [datetime] null ,
	[trackview] [bit]  ,
	[trackemail] [varchar] (100) null ,
	[trackother] [varchar] (100) null 
	
) on [primary] 
go

create table [dbo].[configuration] (
	[id] [int] identity (1, 1) not null ,
	[fieldname] [varchar] (100) null ,
	[fieldvalue] [varchar] (100) null ,
	[fieldgroup] [varchar] (50)  null ,
	[fieldyesno] [bit] 
	
) on [primary] 
go

create table [dbo].[projects] (
	[pid] [int] identity (1, 1) not null ,
	[pdate] [datetime] null ,
	[description] [varchar] (255) null ,
	[customer] [varchar] (100) null ,
	[customeremail] [varchar] (100) null ,
	[price] [money] null ,
	[dualprice] [money] null ,
	[paid] [varchar] (100) null ,
	[datepaid] [datetime] null ,
	[processed] [bit]  null, 
	[orderid] int null,
	[canceled] bit null,
	[comments] [varchar] (1000) null,
	[other1] [varchar] (100) null ,
	[other2] [varchar] (100) null ,
	[other3] [varchar] (100) null 
	
) on [primary]
go

create table [dbo].[registrant] (
 [registrantid] [int] identity (1, 1) not null ,
 [firstname] [varchar] (100) null ,
 [lastname] [varchar] (100) null,
 [address] [varchar] (255) null ,
 [city] [varchar] (100) null ,
 [state] [varchar] (100) null ,
 [postcode] [varchar] (50) null ,
 [country] [varchar] (100) null ,
 [company] [varchar] (100) null ,
 [phone] [varchar] (50) null ,
 [workphone] [varchar] (50) null ,
 [mobilephone] [varchar] (50) null ,
 [fax] [varchar] (50) null ,
 [email] [varchar] (255)  null ,
 [comments] [varchar] (255) null ,
 [logincount] [int] null ,
 [lastlogindate] [datetime] null,
 [regpassword] [varchar] (50) null ,
 [publicpassword] [varchar] (50) null ,
 [eventdate] [datetime] null ,
 [eventtype] [varchar] (255) null ,
 [eventdateend] [datetime] null ,
 [cofirstname] [varchar] (100) null ,
 [colastname] [varchar] (100)  null,
 [coaddress] [varchar] (255) null ,
 [cocity] [varchar] (100) null ,
 [costate] [varchar] (100) null ,
 [copostcode] [varchar] (50) null ,
 [cocountry] [varchar] (100) null ,
 [cophone] [varchar] (50) null ,
 [coemail] [varchar] (255) null ,
 [sendfirstname] [varchar] (50) null ,
 [sendlastname] [varchar] (50)  null,
 [sendaddress] [varchar] (255) null ,
 [sendcity] [varchar] (100) null ,
 [sendstate] [varchar] (100) null ,
 [sendpostcode] [varchar] (50) null ,
 [sendcountry] [varchar] (100) null ,
 [sendphone] [varchar] (50) null ,
 [sendemail] [varchar] (255) null ,
 [regdate] [datetime] null,
 [regtime] [datetime] null,
 [thankyounote] [varchar] (2000) null,
 [shippingtype] [varchar] (50) null,
 [regorderid] [int] null,
 [publicallowed] [varchar] (10) null,
 [regother1] [varchar] (100) null,
 [regother2] [varchar] (100) null
) on [primary]
go

create table [dbo].[registryitems] (
 [regitemid] [int] identity (1, 1) not null ,
 [registrantid] [int] null ,
 [orderid] [int] null,
 [regcatalogid] [int] null,
 [numitems] [int] null,
 [itemname] [varchar] (2000) null,
 [unitprice] [money] null,
 [dualprice] [real] null,
 [supplierid] [int] null,
 [address] [varchar] (255) null,
 [numpurchased] [int] null,
 [purchaseorderid] [int] null

) on [primary]
go
	
alter table [dbo].[affiliates] with nocheck add 
	constraint [df_affiliates_logincount] default (0) for [logincount],
	constraint [df_affiliates_commissionrate] default (0) for [commissionrate],
	constraint [df_affiliates_orders] default (0) for [orders],
	constraint [df_affiliates_totalcommission] default (0) for [totalcommission],
	constraint [pk_affiliates] primary key  nonclustered 
	(
		[affid]
	)  on [primary] 
go
alter table [dbo].[suppliers] with nocheck add 
	constraint [df_suppliers_logincount] default (0) for [logincount],
	constraint [df_suppliers_commissionrate] default (0) for [commissionrate],
	constraint [pk_suppliers] primary key  nonclustered 
	(
		[supplierid]
	)  on [primary] 
go


alter table [dbo].[categories] with nocheck add 
	constraint [pk_categories] primary key  nonclustered 
	(
		[categoryid]
	)  on [primary] 
go

alter table [dbo].[coupons] with nocheck add 
	constraint [df_coupons_couponpercent] default (0) for [couponpercent],
	constraint [df_coupons_couponamount] default (0) for [couponamount],
	constraint [df_coupons_couponlimit] default (0) for [couponlimit],
	constraint [df_coupons_couponusedcount] default (0) for [couponusedcount],
	constraint [pk_coupons] primary key  nonclustered 
	(
		[couponid]
	)  on [primary] 
go

alter table [dbo].[customerprices] with nocheck add 
	constraint [df_customerprices_catalogid] default (0) for [catalogid],
	constraint [df_customerprices_discountamount] default (0) for [discountamount],
	constraint [pk_customerprices] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go

alter table [dbo].[customers] with nocheck add 
	constraint [df_customers_logincount] default (1) for [logincount],
	constraint [df_customers_lastlogindate] default (getdate()) for [lastlogindate],
	constraint [df_customers_discount] default (0) for [discount],
	constraint [pk_customers] primary key  nonclustered 
	(
		[contactid]
	)  on [primary] 
go

alter table [dbo].[gifts] with nocheck add 
	constraint [df_gifts_giftamount] default (0) for [giftamount],
	constraint [df_gifts_giftamountremaining] default (0) for [giftamountremaining],
	constraint [df_gifts_giftorderid] default (0) for [giftorderid],
	constraint [df_gifts_giftcustomerid] default (0) for [giftcustomerid],
	constraint [df_gifts_giftusedcustomerid] default (0) for [giftusedcustomerid],
	constraint [df_gifts_giftusedlastamount] default (0) for [giftusedlastamount],
	constraint [df_gifts_giftusedorderid] default (0) for [giftusedorderid],
	constraint [df_gifts_giftusedcount] default (0) for [giftusedcount],
	constraint [pk_gifts] primary key  nonclustered 
	(
		[giftid]
	)  on [primary] 
go

alter table [dbo].[mycompany] with nocheck add 
	
	constraint [pk_mycompany] primary key  nonclustered 
	(
		[setupid]
	)  on [primary] 
go

alter table [dbo].[oitems] with nocheck add 
	constraint [df_oitems_orderid] default (0) for [orderid],
	constraint [df_oitems_catalogid] default (0) for [catalogid],
	constraint [df_oitems_numitems] default (0) for [numitems],
	constraint [df_oitems_unitprice] default (0) for [unitprice],
	constraint [df_oitems_dualprice] default (0) for [dualprice],
	constraint [df_oitems_supplierid] default (0) for [supplierid],
	constraint [pk_oitems] primary key  nonclustered 
	(
		[orderitemid]
	)  on [primary] 
go

alter table [dbo].[orders] with nocheck add 
	constraint [df_orders_ocustomerid] default (0) for [ocustomerid],
	constraint [df_orders_orderamount] default (0) for [orderamount],
	constraint [df_orders_oprocessed] default (0) for [oprocessed],
	constraint [df_orders_otax] default (0) for [otax],
	constraint [df_orders_oshipmethod] default (0) for [oshipmethod],
	constraint [df_orders_oshipcost] default (0) for [oshipcost],
	constraint [df_orders_opaymethod] default (0) for [opaymethod],
	constraint [df_orders_otime] default (getdate()) for [otime],
	constraint [df_orders_odiscount] default (0) for [odiscount],
	constraint [df_orders_oaffid] default (0) for [oaffid],
	constraint [df_orders_odualtotal] default (0) for [odualtotal],
	constraint [df_orders_odualtaxes] default (0) for [odualtaxes],
	constraint [df_orders_odualshipping] default (0) for [odualshipping],
	constraint [df_orders_odualdiscount] default (0) for [odualdiscount],
	constraint [df_orders_ohandling] default (0) for [ohandling],
	constraint [df_orders_odualhandling] default (0) for [odualhandling],
	constraint [df_orders_coupondiscount] default (0) for [coupondiscount],
	constraint [df_orders_coupondiscountdual] default (0) for [coupondiscountdual],
	constraint [df_orders_giftamountused] default (0) for [giftamountused],
	constraint [df_orders_giftamountuseddual] default (0) for [giftamountuseddual],
        constraint [df_orders_commission] default (0) for [commission],
	constraint [pk_orders] primary key  nonclustered 
	(
		[orderid]
	)  on [primary] 
go

alter table [dbo].[prodfeatures] with nocheck add 
	constraint [df_prodfeatures_featurenum] default (0) for [featurenum],
	constraint [df_prodfeatures_featureprice] default (0) for [featureprice],
	constraint [pk_prodfeatures] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go

alter table [dbo].[products] with nocheck add 
	constraint [df_products_cprice] default (0) for [cprice],
	constraint [df_products_ccategory] default (0) for [ccategory],
	constraint [df_products_cstock] default (0) for [cstock],
	constraint [df_products_subcategoryid] default (0) for [subcategoryid],
	constraint [df_products_minimumquantity] default (0) for [minimumquantity],
	constraint [df_products_supplierid] default (0) for [supplierid],
	constraint [df_products_cdateavailable] default (getdate()) for [cdateavailable],
	constraint [pk_products] primary key  nonclustered 
	(
		[catalogid]
	)  on [primary] 
go

alter table [dbo].[quantitydiscounts] with nocheck add 
	constraint [df_quantitydiscounts_catalogid] default (0) for [catalogid],
	constraint [df_quantitydiscounts_minquantity] default (0) for [minquantity],
	constraint [df_quantitydiscounts_quantity1] default (0) for [quantity1],
	constraint [df_quantitydiscounts_discount1] default (0) for [discount1],
	constraint [df_quantitydiscounts_quantity2] default (0) for [quantity2],
	constraint [df_quantitydiscounts_discount2] default (0) for [discount2],
	constraint [df_quantitydiscounts_quantity3] default (0) for [quantity3],
	constraint [df_quantitydiscounts_discount3] default (0) for [discount3],
	constraint [df_quantitydiscounts_quantity4] default (0) for [quantity4],
	constraint [df_quantitydiscounts_discount4] default (0) for [discount4],
	constraint [df_quantitydiscounts_quantity5] default (0) for [quantity5],
	constraint [df_quantitydiscounts_discount5] default (0) for [discount5],
	constraint [pk_quantitydiscounts] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go

alter table [dbo].[searchresults] with nocheck add 
	constraint [df_searchresults_rdate] default (getdate()) for [rdate],
	constraint [df_searchresults_rtime] default (getdate()) for [rtime],
	constraint [df_searchresults_customerid] default (0) for [customerid],
	constraint [pk_searchresults] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go

alter table [dbo].[shipmethods] with nocheck add 
	constraint [df_shipmethods_smprice] default (0) for [smprice],
	constraint [pk_shipmethods] primary key  nonclustered 
	(
		[shipmethodid]
	)  on [primary] 
go


alter table [dbo].[tbllog] with nocheck add 
	constraint [pk_tbllog] primary key  nonclustered 
	(
		[fldauto]
	)  on [primary] 
go

alter table [dbo].[tbluser] with nocheck add 
	constraint [pk_tbluser] primary key  nonclustered 
	(
		[fldauto]
	)  on [primary] 
go

alter table [dbo].[prodcategories] with nocheck add 
	constraint [df_prodcategories_intcategoryid] default (0) for [intcategoryid],
	constraint [df_prodcategories_intcatalogid] default (0) for [intcatalogid],
	constraint [pk_prodcategories] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go
alter table [dbo].[reviews] with nocheck add 
	constraint [df_reviews_catalogid] default (0) for [catalogid],
	constraint [pk_reviews] primary key  nonclustered 
	(
		[id]
	)  on [primary] 
go
alter table [dbo].[projects] with nocheck add 
	constraint [df_projects_orderid] default (0) for [orderid],
	constraint [df_projects_price] default (0) for [price],
	constraint [df_projects_dualprice] default (0) for [dualprice],
	constraint [df_projects_processed] default (0) for [processed],
	constraint [df_projects_pdate] default (getdate()) for [pdate],
	constraint [pk_projects] primary key  nonclustered 
	(
		[pid]
	)  on [primary] 
go


alter table [dbo].[registrant] with nocheck add 
	constraint [df_registrant_logincount] default (1) for [logincount],
	constraint [df_registrant_lastlogindate] default (getdate()) for [lastlogindate],
	constraint [pk_registrant] primary key  nonclustered 
	(
		[registrantid]
	)  on [primary] 
go


alter table [dbo].[registryitems] with nocheck add 
	constraint [df_registryitems_orderid] default (0) for [orderid],
	constraint [df_registryitems_regcatalogid] default (0) for [regcatalogid],
	constraint [df_registryitems_numitems] default (0) for [numitems],
	constraint [df_registryitems_unitprice] default (0) for [unitprice],
	constraint [df_registryitems_dualprice] default (0) for [dualprice],
	constraint [df_registryitems_supplierid] default (0) for [supplierid],
	constraint [pk_registryitems] primary key  nonclustered 
	(
		[regitemid]
	)  on [primary] 
go

 create  index [ix_customers] on [dbo].[customers]([lastname]) on [primary]
go

 create  index [ix_customers_3] on [dbo].[customers]([email]) on [primary]
go

 create  index [ix_oitems] on [dbo].[oitems]([orderid]) on [primary]
go

 create  index [ix_oitems_1] on [dbo].[oitems]([catalogid]) on [primary]
go

 create  index [ix_orders] on [dbo].[orders]([ocustomerid]) on [primary]
go

 create  index [ix_prodfeatures] on [dbo].[prodfeatures]([featurenum]) on [primary]
go

 create  index [ix_products_4] on [dbo].[products]([keywords]) on [primary]
go


set identity_insert tblAccess on
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(1,'Display Orders','shopa_displayorders.asp','Display orders');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(2,'Edit Categories','shopa_editdisplay.asp?table=categories','Categories');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(3,'Edit Products','shopa_editdisplay.asp?table=products','Products');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(4,'Configure Shopping Cart','shopa_config.asp','Configure Shopping Cart');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(5,'Edit VP-ASP Tables','shopa_tableselect.asp','View and Edit tables');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(6,'Edit Shipping','shopa_editdisplay.asp?table=shipmethods','Shipping Methods');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(7,'Sales Reports','shopa_reports.asp','Sales Reports by Date');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(8,'Product Report','shopa_productreport.asp','Display products sold');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(9,'Edit Customers','shopa_editdisplay.asp?table=customers','Customers');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(10,'Edit Orders','shopa_editdisplay.asp?table=orders','Orders');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(11,'Edit Product Features','shopa_editdisplay.asp?table=prodfeatures','Product Features');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(12,'Edit Coupons','shopa_editdisplay.asp?table=coupons','Coupons');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(13,'Edit Gift Certificates','shopa_editdisplay.asp?table=gifts','Gift Certificates');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(14,'Edit Projects','shopa_editdisplay.asp?table=projects','Projects');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(15,'Edit Reviews','shopa_editdisplay.asp?table=reviews','Product Reviews');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(16,'Edit Affiliates','shopa_editdisplay.asp?table=affiliates','Affiliates');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(17,'Edit Customer Prices','shopa_editdisplay.asp?table=customerprices','Customer Prices');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(18,'Edit Quantity Discounts','shopa_editdisplay.asp?table=quantitydiscounts','Quantity Discounts');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(19,'Edit Suppliers','shopa_editdisplay.asp?table=suppliers','Suppliers');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(20,'Stock Reports','shopa_stock.asp','Stock Low Reports');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(21,'Search Report','shopa_searchreports.asp','Display search keywords');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(22,'Affiliate Reports','shopa_affreports.asp','Affiliate Reports');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(23,'Bulk Product update','shopa_editstock.asp','Bulk update of Stock');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(24,'Product Import','shopa_import.asp','Bulk Import from a file');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(25,'Project Report','shopa_projectdisplay.asp','Projects');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(26,'Advanced Query','shopa_query.asp','Advanced Query');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(27,'User Control','shopa_user_control.asp','Add/Delete Users');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(28,'Menu Control','shopa_menu_control.asp','Menus for administrators');
Insert into tblaccess(fldauto,fldname,fldurl,fldComment) values(29,'Log History','shopa_loghist.asp','View Login history');
set identity_insert tblAccess off
go

set identity_insert tbluser on
Insert into tbluser(fldauto,fldusername,fldpassword,fldaccess,tablesallowed) values(1,'admin','admin','1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29','affiliates,categories,configuration,coupons,customers,customerprices,gifts,mycompany,oitems,orders,ordertracking,prodcategories,prodfeatures,products,quantitydiscounts,projects,searchresults,suppliers,reviews,shipmethods,tblaccess,tbllog,tbluser,registrant,registryitems');
Insert into tbluser(fldauto,fldusername,fldpassword,fldaccess,tablesallowed) values(2,'vpasp','vpasp','1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19','affiliates,categories,configuration,coupons,customers,customerprices,gifts,mycompany,oitems,orders,ordertracking,prodcategories,prodfeatures,products,quantitydiscounts,projects,searchresults,suppliers,reviews,shipmethods,registrant,registryitems');
set identity_insert tbluser off
go

set identity_insert mycompany on
Insert into mycompany(setupid,companyname,address,city,state,postalcode,country,phonenumber,faxnumber,myemail) values(1,'My Company','PO Box xxxx','Somewhere','NY','11720','US','Some Phone','55512121','abc@def.com')
set identity_insert mycompany off
go