--同行表
Create  TABLE shop_base(
	[userId] bigint primary key,
	[taobaoID] varchar(64),
	[creditGrade] tinyint,
	[shopName] varchar(255),
	[shopId] bigint,
	[industry] integer,
	[industryCn] varchar(32),
	[isB2C] tinyint,
	[shopType] varchar(16),
	[area] varchar(64),
	[online] smallint,
	[goodsList] varchar(800),
	[goodsAmount] integer,
	[shopUrl] varchar(255),
	[logoUrl] varchar(255),
	[classId] integer,
	[check] tinyint default 0);

--同行类别
Create Table shop_class(
	[classId] integer primary key,
	[className] varchar(32),
	[showIndex] integer);

--代码字段对照表
Create  TABLE customer_base(
	[userId] bigint primary key,
	[taobaoID] varchar(64),
	[userLink] varchar(255),
	[creditGrade] tinyint,
	[creditCores] integer,
	[online] smallint,
	[vipGrade] tinyint,
	[rateTime] varchar(32),
	[rateCash] double,
	[rateGoods] varchar(255),
	[email] varchar(128),
	[mobile] varchar(64),
	[QQ] varchar(32),
	[shopName] varchar(64),
	[shopId] varchar(32),
	[shopType] varchar(16),
	[classId] integer,
	[check] tinyint default 0);

--买家类别
Create Table customer_class(
	[classId] integer primary key,
	[className] varchar(32),
	[showIndex] integer);

--买家交易记录
Create  TABLE customer_trade(
	[recordId] bigint primary key,
	[userId] bigint,
	[taobaoID] varchar(64),
	[creditGrade] tinyint,
	[creditCores] integer,
	[vipGrade] tinyint,
	[online] smallint,
	[rateTime] varchar(32),
	[rateCash] double,
	[goodsId] bigint,
	[auctionTitle] varchar(255),
	[auctionSku] varchar(64),
	[auctionLink] varchar(255),
	[rateResult] integer,
	[rateContent] varchar(255),
	[shopName] varchar(64),
	[shopUrl] varchar(255),
	[shopId] varchar(32),
	[industry] integer,
	[industryCn] varchar(32),
	[shopTaobaoID] varchar(64));

--创建交易记录视图
create view v_customer_trade as select t.*,b.email,b.mobile,b.qq,b.classId from customer_trade t 
	left outer join customer_base b on t.userid=b.userId;

--用于添加好友账号
Create TABLE friend_addaccount(
	[taobaoID] varchar(64) PRIMARY KEY,
	[pass] varchar(32),
	[totalsend] integer,
	[todaysend] integer,
	[attentions] integer,
	[istate] tinyint,
	[state] varchar(64),
	[lastsendtime] datetime,
	[check] tinyint default 0);

--正在添加帐号的客户
Create TABLE friend_tobeadd(
	[userId] bigint PRIMARY KEY,
	[taobaoID] varchar(64),
	[creditGrade] tinyint,
	[vipGrade] tinyint,
	[QQ] varchar(16),
	[mobile] varchar(32),
	[email] varchar(64),
	[online] smallint,
	[istate] smallint default 0,
	[state] varchar(64),
	[check] tinyint default 0);

--好友列表账号
Create TABLE friend_account(
	[taobaoID] varchar(64) PRIMARY KEY,
	[pass] varchar(32),
	[creditGrade] tinyint,
	[rank] tinyint,
	[friendcount] integer,
	[istate] tinyint,
	[state] varchar(64),
	[lastUpdate] varchar(32),
	[check] tinyint default 0);

--我的关注列表
Create TABLE friend_myattention(
        [myTaobaoID] varchar(64) not null,
	[taobaoID] varchar(64) not null,
	[userId] bigint,
	[creditGrade] tinyint,
	[vipGrade] tinyint,
	[QQ] varchar(16),
	[mobile] varchar(32),
	[email] varchar(64),
	[spaceUrl] varchar(255),
	[online] smallint,
	[istate] tinyint,
	[state] varchar(64),
	[check] tinyint default 0,
        primary key (myTaobaoID,taobaoID));

--我的好友列表
Create TABLE friend_myfriend(
        [myTaobaoID] varchar(64) not null,
	[taobaoID] varchar(64) not null,
	[userId] bigint,
	[creditGrade] tinyint,
	[vipGrade] tinyint,
	[QQ] varchar(16),
	[mobile] varchar(32),
	[email] varchar(64),
	[spaceUrl] varchar(255),
	[online] smallint,
	[istate] tinyint,
	[state] varchar(64),
	[check] tinyint default 0,
        primary key (myTaobaoID,taobaoID));

--消息
Create  TABLE msg_user(
	[msgId] varchar(32) NOT NULL,
	[msgType] smallint NOT NULL,
	[author] varchar(64),
	[content] text,
	[msgTime] varchar(32), 
	Primary Key(msgId,msgType));

--创建账号信息
Create TABLE sys_user(
	[id] varchar(64) PRIMARY KEY,
	[name] varchar(32),
	[pass] varchar(32),
	[email] varchar(64),
	[wwId] varchar(32),
	[shopUrl] varchar(255),
	[mobile] varchar(32),
	[QQ] varchar(16),
	[pay] smallint,
	[wwPass] varchar(32),
	[state] smallint);

--创建系统参数表
Create TABLE sys_param(
	[id] varchar(64) PRIMARY KEY,
	[value] varchar(128));

--系统参数配置
insert into sys_param([id],[value]) values('ver','V3.11');

--内置可用于添加好友的账号
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (23604159,'大傻酷',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (13535457,'傻酷啦',3);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (872373708,'骂不同',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (829233674,'招客猫',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (34584618,'谋男郎',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (60121501,'鞋X子',1);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (824612276,'喔莫莫',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (824306677,'只生男孩',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (826222904,'把号养大',0);
insert into friend_tobeadd(userId,taobaoID,creditgrade) values (822664761,'客户源',0);