drop table inventory;
drop table item;
drop table lineitem
drop table product;
drop table category;
drop table sequence;
drop table orders;
drop table orderstatus;

drop table user_info ;

CREATE TABLE user_info (	
        userid nvarchar (40)  NOT NULL ,
	name nvarchar (40)  NOT NULL ,	        	
	phone nvarchar (40)  NOT NULL ,
        street nvarchar (40)  NOT NULL ,
        state nvarchar (40)  NOT NULL ,
        city nvarchar (40)  NOT NULL ,       
        zipCode nvarchar (40)  NOT NULL ,
        email nvarchar (40)  NULL ,	
        cardNumber nvarchar (40)  NOT NULL ,
	cardType int NOT NULL ,
	userLogCount int NOT NULL ,
	userLastLogTime smalldatetime NULL ,	
	userType int NOT NULL ,
        banneropt int NOT NULL,
        fanvcategory nvarchar (40)  NOT NULL ,
        password nvarchar (40)  NOT NULL ,
	constraint pk_userinfo primary key(userId)
) 

create table orders (
       orderid varchar(30) not null,
      userid varchar(80) not null,
      orderdate datetime not null,
      shipstreet varchar(80) not null,
      shipcity varchar(80) not null,
      shipstate varchar(80) not null,
      shipzip varchar(20) not null,    
      totalprice decimal(10,2) not null,     
      shiptoname varchar(80) not null,
      creditcard varchar(80) not null,    
      cardtype varchar(80) not null,   
      constraint pk_orders primary key (orderid)
);

create table orderstatus (
      orderid varchar(30) not null,
      linenum int not null,
      timestamp datetime not null,
      status varchar(2) not null,
      constraint pk_orderstatus primary key (orderid, linenum)
);

create table lineitem (
      orderid varchar(30) not null,
      linenum int not null,
      itemid varchar(10) not null,
      quantity int not null,
      unitprice decimal(10,2) not null,
      constraint pk_lineitem primary key (orderid, linenum)
);

create table category (
	catid varchar(30) not null,
	name varchar(80) null,
	descn varchar(255) null,
	constraint pk_category primary key (catid)
);

create table product (
    productid varchar(30) not null,
    category varchar(30) not null,
    name varchar(80) null,
    descn varchar(255) null,
    isPromot bit default 0,
    registerTime datetime default getdate(),
    constraint pk_product primary key (productid),
        constraint fk_product_1 foreign key (category)
        references category (catid)
);

create index productCat on product (category);
create index productName on product (name);

create table item (
    itemid varchar(30) not null,
    productid varchar(30) not null,
    listprice decimal(10,2) null,
    unitcost decimal(10,2) null,
    supplier varchar(50)  null,
    status varchar(2) null,
    attr1 varchar(80) null,
    attr2 varchar(80) null,
    attr3 varchar(80) null,
    attr4 varchar(80) null,
    attr5 varchar(80) null,
    constraint pk_item primary key (itemid),
        constraint fk_item_1 foreign key (productid)
        references product (productid),
       
);


create table inventory (
    itemid varchar(30) not null,
    qty int not null,
    constraint pk_inventory primary key (itemid)
);


create table topic(id varchar(30) not null,title varchar(100),author varchar(30),email varchar(50),content varchar(1000),pubtime datetime default getdate(),constraint pk_topic primary key(id));

create table reply(id varchar(30) not null,topicId varchar(30),title varchar(100),author varchar(30),email varchar(50),content varchar(1000),pubtime datetime default getdate(),constraint pk_reply primary key(id),constraint fk_reply_1 foreign key (topicId)references topic(id));

create index topic on topic (id);
create index reply on reply (id);