/*
init at 20170501
*/
DROP TABLE IF EXISTS accessory;
CREATE TABLE accessory (
	accessory_id char(32) NOT NULL,
	accessory_src varchar(255) NOT NULL,
	operation_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	create_time char(14) NOT NULL,
	accessory_name varchar(255)
);
COMMENT ON TABLE accessory IS '资源交易附件表（目前没用）';
COMMENT ON COLUMN accessory.accessory_id IS '附件ID';
COMMENT ON COLUMN accessory.accessory_src IS '附件路径';
COMMENT ON COLUMN accessory.operation_id IS '资源交易ID';
COMMENT ON COLUMN accessory.professor_id IS '用户ID';
COMMENT ON COLUMN accessory.create_time IS '创建时间';
COMMENT ON COLUMN accessory.accessory_name IS '附件名称';
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS article;
CREATE TABLE article (
	article_id char(32) NOT NULL,
	professor_id char(32),
	article_title text,
	article_content text,
	subject text,
	industry text,
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL,
	article_img varchar(255),
	org_id char(32),
	article_type char(1),
	article_agree int4 NOT NULL,
	page_views int4,
	status char(1),
	publish_time char(14)
);
COMMENT ON TABLE article IS '科研文章表';
COMMENT ON COLUMN article.article_id IS '文章ID';
COMMENT ON COLUMN article.professor_id IS '发布人ID';
COMMENT ON COLUMN article.article_title IS '文章标题';
COMMENT ON COLUMN article.article_content IS '文章内容';
COMMENT ON COLUMN article.subject IS '关键词';
COMMENT ON COLUMN article.industry IS '应用行业';
COMMENT ON COLUMN article.create_time IS '创建时间';
COMMENT ON COLUMN article.modify_time IS '修改时间';
COMMENT ON COLUMN article.article_img IS '文章封面图片';
COMMENT ON COLUMN article.org_id IS '企业ID';
COMMENT ON COLUMN article.article_type IS '文章类型(1-个人文章，2-企业文章)';
COMMENT ON COLUMN article.article_agree IS '文章被点赞数量';
COMMENT ON COLUMN article.page_views IS '浏览量';
COMMENT ON COLUMN article.status IS '文章状态(0-草稿，1-发布，2-定时发布，3-删除，4-关闭)';
COMMENT ON COLUMN article.publish_time IS '文章发布时间';
-- ----------------------------
-- Table structure for article_agree
-- ----------------------------
DROP TABLE IF EXISTS article_agree;
CREATE TABLE article_agree (
	operate_id char(32) NOT NULL,
	article_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE article_agree IS '文章点赞表';
COMMENT ON COLUMN article_agree.operate_id IS '点赞人的ID';
COMMENT ON COLUMN article_agree.article_id IS '被点赞的文章ID';
COMMENT ON COLUMN article_agree.create_time IS '点赞时间';



-- ----------------------------
-- Table structure for assess
-- ----------------------------
DROP TABLE IF EXISTS assess;
CREATE TABLE assess (
	assess_id char(32) NOT NULL,
	star_level int2 NOT NULL,
	descp text,
	operation_id char(32) NOT NULL,
	resource_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE assess IS '交易评价表（暂时没用）';
COMMENT ON COLUMN assess.assess_id IS '评价ID';
COMMENT ON COLUMN assess.star_level IS '评价星级';
COMMENT ON COLUMN assess.descp IS '详细评价';
COMMENT ON COLUMN assess.operation_id IS '交易ID';
COMMENT ON COLUMN assess.resource_id IS '资源ID';
COMMENT ON COLUMN assess.professor_id IS '用户ID';
COMMENT ON COLUMN assess.create_time IS '创建时间';

-- ----------------------------
-- Table structure for auth_apply
-- ----------------------------
DROP TABLE IF EXISTS auth_apply;
CREATE TABLE auth_apply (
	auth_apply_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	solve_status int2 DEFAULT 0 NOT NULL,
	create_time char(14) NOT NULL,
	apply_type int2 NOT NULL
);
COMMENT ON TABLE auth_apply IS '认证申请表';
COMMENT ON COLUMN auth_apply.auth_apply_id IS '认证申请ID';
COMMENT ON COLUMN auth_apply.professor_id IS '申请人ID（可以是个人或企业用户）';
COMMENT ON COLUMN auth_apply.solve_status IS '解决状态(-1-认证失败，1-待处理，2-处理中，3-认证通过)';
COMMENT ON COLUMN auth_apply.create_time IS '申请时间';
COMMENT ON COLUMN auth_apply.apply_type IS '申请认证类型(1-实名认证,2-专家认证,3-企业认证)';

DROP TABLE IF EXISTS auth_image;
CREATE TABLE auth_image (
	auth_image_id char(32) NOT NULL,
	auth_apply_id char(32) NOT NULL,
	auth_src varchar(255) NOT NULL
);
COMMENT ON TABLE auth_image IS '认证申请图片表';
COMMENT ON COLUMN auth_image.auth_image_id IS '认证图片ID';
COMMENT ON COLUMN auth_image.auth_apply_id IS '认证申请ID';
COMMENT ON COLUMN auth_image.auth_src IS '认证图片路径';

DROP TABLE IF EXISTS complain;
CREATE TABLE complain (
	complain_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	complain_contant varchar(255) NOT NULL,
	appellee char(32),
	create_time char(14) NOT NULL,
	complain_type int2
);
COMMENT ON TABLE complain IS '投诉表';
COMMENT ON COLUMN complain.complain_id IS '投诉ID';
COMMENT ON COLUMN complain.professor_id IS '投诉人ID';
COMMENT ON COLUMN complain.complain_contant IS '投诉内容';
COMMENT ON COLUMN complain.appellee IS '被投诉ID';
COMMENT ON COLUMN complain.create_time IS '投诉时间';
COMMENT ON COLUMN complain.complain_type IS '投诉类型(0-建议，1-投诉)';

-- ----------------------------
-- Table structure for consult
-- ----------------------------
DROP TABLE IF EXISTS consult;
CREATE TABLE consult (
	consult_id char(32) NOT NULL,
	consult_type varchar(255) NOT NULL,
	consult_title varchar(255) NOT NULL,
	consult_contant text NOT NULL,
	create_time char(14) NOT NULL,
	professor_id char(32),
	consultant_id char(32) NOT NULL,
	consult_status int2,
	finish_time char(14),
	assess_status int2,
	assess_star int2,
	assess_contant text,
	assess_time char(14),
	thanks_status int2,
	thanks_money numeric(24,2),
	thanks_time char(14),
	revovery_time char(14),
	read_status int2 NOT NULL,
	demand_id char(32)
);
COMMENT ON TABLE consult IS '咨询表';
COMMENT ON COLUMN consult.consult_id IS '咨询ID';
COMMENT ON COLUMN consult.consult_type IS '咨询类型';
COMMENT ON COLUMN consult.consult_title IS '咨询主题';
COMMENT ON COLUMN consult.consult_contant IS '咨询内容';
COMMENT ON COLUMN consult.create_time IS '创建时间';
COMMENT ON COLUMN consult.professor_id IS '专家ID';
COMMENT ON COLUMN consult.consultant_id IS '咨询者ID';
COMMENT ON COLUMN consult.consult_status IS '咨询状态(0-进行中,1-已完成,2-待回复,3-已谢绝)';
COMMENT ON COLUMN consult.finish_time IS '完成时间';
COMMENT ON COLUMN consult.assess_status IS '评价状态(0-待评价,1-已评价)';
COMMENT ON COLUMN consult.assess_star IS '评价星级';
COMMENT ON COLUMN consult.assess_contant IS '评价内容';
COMMENT ON COLUMN consult.assess_time IS '评价时间';
COMMENT ON COLUMN consult.thanks_status IS '感谢状态(0-未感谢，1-以感谢)';
COMMENT ON COLUMN consult.thanks_money IS '感谢金额';
COMMENT ON COLUMN consult.thanks_time IS '感谢时间';
COMMENT ON COLUMN consult.revovery_time IS '最后回复时间';
COMMENT ON COLUMN consult.read_status IS '查看状态(0-未查看，1-已查看)';
COMMENT ON COLUMN consult.demand_id IS '需求ID';

-- ----------------------------
-- Table structure for consult_reject
-- ----------------------------
DROP TABLE IF EXISTS consult_reject;
CREATE TABLE consult_reject (
	reject_id char(32) NOT NULL,
	rejector char(32) NOT NULL,
	consult_id char(32) NOT NULL,
	sort int4 NOT NULL,
	reject_reason varchar(255) NOT NULL
);
COMMENT ON TABLE consult_reject IS '咨询谢绝表（谢绝理由）';
COMMENT ON COLUMN consult_reject.reject_id IS '拒绝id';
COMMENT ON COLUMN consult_reject.rejector IS '拒绝者';
COMMENT ON COLUMN consult_reject.consult_id IS '咨询ID';
COMMENT ON COLUMN consult_reject.sort IS '排序字段';
COMMENT ON COLUMN consult_reject.reject_reason IS '谢绝理由';

-- ----------------------------
-- Table structure for data_dict
-- ----------------------------
DROP TABLE IF EXISTS data_dict;
CREATE TABLE data_dict (
	dict_code varchar(32) NOT NULL,
	actived char(1) NOT NULL,
	code varchar(32) NOT NULL,
	caption varchar(255) NOT NULL,
	parent_code varchar(32),
	descp text
);
COMMENT ON TABLE data_dict IS '数据字典';
COMMENT ON COLUMN data_dict.dict_code IS '属性分类（学术领域-SUBJECT，应用行业-INDUSTRY，省份-PROVINCE，城市-ADDRESS，研究方向-RESEARCH_AREA）';
COMMENT ON COLUMN data_dict.actived IS '激活状态';
COMMENT ON COLUMN data_dict.code IS '唯一ID';
COMMENT ON COLUMN data_dict.caption IS '属性名称';
COMMENT ON COLUMN data_dict.parent_code IS '父ID';
COMMENT ON COLUMN data_dict.descp IS '详细描述';

-- ----------------------------
-- Table structure for demand
-- ----------------------------
DROP TABLE IF EXISTS demand;
CREATE TABLE demand (
	demand_id char(32) NOT NULL,
	demander char(32) NOT NULL,
	demand_aim char(1) NOT NULL,
	demand_type char(1) NOT NULL,
	demand_title varchar(255) NOT NULL,
	demand_content text,
	demand_status char(1) NOT NULL,
	order_key int8 NOT NULL,
	create_time char(14) NOT NULL,
	close_time char(14),
	org_id char(32)
);
COMMENT ON TABLE demand IS '需求表';
COMMENT ON COLUMN demand.demand_id IS '需求ID';
COMMENT ON COLUMN demand.demander IS '需求者';
COMMENT ON COLUMN demand.demand_aim IS '需求目的(1-技术,2-资源,3-其他)';
COMMENT ON COLUMN demand.demand_type IS '需求类型(1-个人,2-企业)';
COMMENT ON COLUMN demand.demand_title IS '需求主题';
COMMENT ON COLUMN demand.demand_content IS '需求内容';
COMMENT ON COLUMN demand.demand_status IS '需求状态(0-关闭,1-发布中)';
COMMENT ON COLUMN demand.order_key IS '排序字段';
COMMENT ON COLUMN demand.create_time IS '发布时间';
COMMENT ON COLUMN demand.close_time IS '关闭时间';
COMMENT ON COLUMN demand.org_id IS '企业ID';

-- ----------------------------
-- Table structure for demand_sub_or_indus
-- ----------------------------
DROP TABLE IF EXISTS demand_sub_or_indus;
CREATE TABLE demand_sub_or_indus (
	id char(32) NOT NULL,
	sort int4 NOT NULL,
	sub_or_indus varchar(255) NOT NULL,
	demand_id char(32) NOT NULL
);
COMMENT ON TABLE demand_sub_or_indus IS '需求的学术领域或应用行业';
COMMENT ON COLUMN demand_sub_or_indus.id IS '主键ID';
COMMENT ON COLUMN demand_sub_or_indus.sort IS '排序字段';
COMMENT ON COLUMN demand_sub_or_indus.sub_or_indus IS '学术领域或应用行业';
COMMENT ON COLUMN demand_sub_or_indus.demand_id IS '需求ID';

-- ----------------------------
-- Table structure for dict_city
-- ----------------------------
DROP TABLE IF EXISTS dict_city;
CREATE TABLE dict_city (
	dict_code varchar(32) NOT NULL,
	caption varchar(255) NOT NULL,
	parent_code varchar(32)
);
COMMENT ON TABLE dict_city IS '省份-城市二级数据字典';
COMMENT ON COLUMN dict_city.dict_code IS '城市代码';
COMMENT ON COLUMN dict_city.caption IS '城市名称';
COMMENT ON COLUMN dict_city.parent_code IS '上级代码';

-- ----------------------------
-- Table structure for dict_meta
-- ----------------------------
DROP TABLE IF EXISTS dict_meta;
CREATE TABLE dict_meta (
	readonly char(1) NOT NULL,
	code varchar(32) NOT NULL,
	name varchar(255) NOT NULL,
	tree char(1) NOT NULL,
	descp text,
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL
);

-- ----------------------------
-- Table structure for growth_log
-- ----------------------------
DROP TABLE IF EXISTS growth_log;
CREATE TABLE growth_log (
	id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	operate varchar(4),
	score int4,
	sign_in_days int4,
	create_time char(14) NOT NULL,
	invite_pro char(32)
);
COMMENT ON TABLE growth_log IS '用户积分成长记录表';
COMMENT ON COLUMN growth_log.id IS '主键ID';
COMMENT ON COLUMN growth_log.professor_id IS '用户ID';
COMMENT ON COLUMN growth_log.operate IS '操作代码（详细见积分规则）';
COMMENT ON COLUMN growth_log.score IS '分值';
COMMENT ON COLUMN growth_log.sign_in_days IS '签到天数';
COMMENT ON COLUMN growth_log.create_time IS '创建时间';
COMMENT ON COLUMN growth_log.invite_pro IS '邀请的用户';

-- ----------------------------
-- Table structure for honor
-- ----------------------------
DROP TABLE IF EXISTS honor;
CREATE TABLE honor (
	id char(32) NOT NULL,
	year char(4),
	name text NOT NULL,
	professor_id char(32) NOT NULL,
	descp text
);
COMMENT ON TABLE honor IS '获得奖项';
COMMENT ON COLUMN honor.year IS '获奖年份';
COMMENT ON COLUMN honor.name IS '获奖名称';
COMMENT ON COLUMN honor.professor_id IS '用户ID';
COMMENT ON COLUMN honor.descp IS '详细描述';

-- ----------------------------
-- Table structure for image
-- ----------------------------
DROP TABLE IF EXISTS image;
CREATE TABLE image (
	image_id char(32) NOT NULL,
	image_src varchar(255) NOT NULL,
	resource_id char(32) NOT NULL
);
COMMENT ON TABLE image IS '资源图片表';
COMMENT ON COLUMN image.image_id IS '图片ID';
COMMENT ON COLUMN image.image_src IS '图片路径';
COMMENT ON COLUMN image.resource_id IS '资源ID';


-- ----------------------------
-- Table structure for leave_word
-- ----------------------------
DROP TABLE IF EXISTS leave_word;
CREATE TABLE leave_word (
	id char(32) NOT NULL,
	content text NOT NULL,
	sender char(32) NOT NULL,
	article_id char(32) NOT NULL,
	order_key int8 NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE leave_word IS '文章留言表';
COMMENT ON COLUMN leave_word.content IS '留言内容';
COMMENT ON COLUMN leave_word.sender IS '留言人ID';
COMMENT ON COLUMN leave_word.article_id IS '文章ID';
COMMENT ON COLUMN leave_word.order_key IS '排序字段';
COMMENT ON COLUMN leave_word.create_time IS '留言时间';

-- ----------------------------
-- Table structure for luser
-- ----------------------------
DROP TABLE IF EXISTS luser;
CREATE TABLE luser (
	id char(32) NOT NULL,
	email varchar(50),
	mobile_phone char(11),
	passwd char(32) NOT NULL,
	user_type char(1) NOT NULL,
	create_time char(14),
	invite_code varchar(32),
	send_mail_status int2 DEFAULT 0,
	inviter_id char(32),
	active_time char(14)
);
COMMENT ON TABLE luser IS '个人用户表';
COMMENT ON COLUMN luser.id IS '个人用户ID';
COMMENT ON COLUMN luser.email IS '注册邮箱';
COMMENT ON COLUMN luser.mobile_phone IS '注册手机';
COMMENT ON COLUMN luser.passwd IS '密码';
COMMENT ON COLUMN luser.user_type IS '用户类型(0-个人用户，1-企业用户)';
COMMENT ON COLUMN luser.create_time IS '创建时间';
COMMENT ON COLUMN luser.invite_code IS '邀请码';
COMMENT ON COLUMN luser.send_mail_status IS '是否发送邀请邮件(0-未发送,1-已发送)';
COMMENT ON COLUMN luser.inviter_id IS '邀请人ID';
COMMENT ON COLUMN luser.active_time IS '激活时间';

-- ----------------------------
-- Table structure for messages
-- ----------------------------
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
	message_id char(32) NOT NULL,
	operation_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	leave_word text,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE messages IS '交易留言信息（目前没用）';
COMMENT ON COLUMN messages.message_id IS '信息ID';
COMMENT ON COLUMN messages.operation_id IS '交易ID';
COMMENT ON COLUMN messages.professor_id IS '留言用户ID';
COMMENT ON COLUMN messages.leave_word IS '留言内容';
COMMENT ON COLUMN messages.create_time IS '创建时间';

-- ----------------------------
-- Table structure for operate_service_file
-- ----------------------------
DROP TABLE IF EXISTS operate_service_file;
CREATE TABLE operate_service_file (
	service_file_id char(32) NOT NULL,
	service_log_id char(32) NOT NULL,
	file_src varchar(255) NOT NULL,
	file_name varchar(255) NOT NULL
);
COMMENT ON TABLE operate_service_file IS '服务记录附件表';
COMMENT ON COLUMN operate_service_file.service_file_id IS '服务附件ID';
COMMENT ON COLUMN operate_service_file.service_log_id IS '服务记录ID';
COMMENT ON COLUMN operate_service_file.file_src IS '文件路径';
COMMENT ON COLUMN operate_service_file.file_name IS '文件名称';

-- ----------------------------
-- Table structure for operate_service_log
-- ----------------------------
DROP TABLE IF EXISTS operate_service_log;
CREATE TABLE operate_service_log (
	service_log_id char(32) NOT NULL,
	service_user char(32) NOT NULL,
	servant varchar(255),
	service_content varchar(255),
	create_time char(14) NOT NULL
);
COMMENT ON TABLE operate_service_log IS '后台服务记录表';
COMMENT ON COLUMN operate_service_log.service_log_id IS '服务记录ID';
COMMENT ON COLUMN operate_service_log.service_user IS '服务用户';
COMMENT ON COLUMN operate_service_log.servant IS '服务者';
COMMENT ON COLUMN operate_service_log.service_content IS '服务内容';
COMMENT ON COLUMN operate_service_log.create_time IS '服务时间';

-- ----------------------------
-- Table structure for operation
-- ----------------------------
DROP TABLE IF EXISTS operation;
CREATE TABLE operation (
	operation_id char(32) NOT NULL,
	operation_status int2 NOT NULL,
	demand_id char(32) NOT NULL,
	detail_demand text,
	deliver_time char(8),
	resource_id char(32) NOT NULL,
	deal_price numeric(24,2),
	deal_time char(14),
	pay_method varchar(255),
	reply_notes varchar(255),
	create_time char(14) NOT NULL,
	applysquare varchar(255)
);
COMMENT ON TABLE operation IS '资源交易表（目前没用）';
COMMENT ON COLUMN operation.operation_id IS '交易ID';
COMMENT ON COLUMN operation.operation_status IS '交易状态';
COMMENT ON COLUMN operation.demand_id IS '需求方ID（需要资源的用户ID）';
COMMENT ON COLUMN operation.detail_demand IS '详细需求';
COMMENT ON COLUMN operation.deliver_time IS '交付时间';
COMMENT ON COLUMN operation.resource_id IS '资源ID';
COMMENT ON COLUMN operation.deal_price IS '成交价格';
COMMENT ON COLUMN operation.deal_time IS '成交时间';
COMMENT ON COLUMN operation.pay_method IS '支付方式';
COMMENT ON COLUMN operation.reply_notes IS '回复备注';
COMMENT ON COLUMN operation.create_time IS '创建时间';
COMMENT ON COLUMN operation.applysquare IS '申请方';

-- ----------------------------
-- Table structure for operation_status
-- ----------------------------
DROP TABLE IF EXISTS operation_status;
CREATE TABLE operation_status (
	status_id int2 NOT NULL,
	status_name varchar(255) NOT NULL
);
COMMENT ON TABLE operation_status IS '交易状态（目前没用）';
COMMENT ON COLUMN operation_status.status_id IS '交易状态ID';
COMMENT ON COLUMN operation_status.status_name IS '交易状态名称';

-- ----------------------------
-- Table structure for org_res_staff
-- ----------------------------
DROP TABLE IF EXISTS org_res_staff;
CREATE TABLE org_res_staff (
	resource_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE org_res_staff IS '企业资源负责人';
COMMENT ON COLUMN org_res_staff.resource_id IS '资源ID';
COMMENT ON COLUMN org_res_staff.professor_id IS '用户ID';
COMMENT ON COLUMN org_res_staff.create_time IS '创建时间';

-- ----------------------------
-- Table structure for org_retrieve
-- ----------------------------
DROP TABLE IF EXISTS org_retrieve;
CREATE TABLE org_retrieve (
	id char(32) NOT NULL,
	name varchar(255),
	org_id char(32),
	email varchar(50),
	passwd char(32),
	solve_status varchar(2) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE org_retrieve IS '企业账号找回申请表';
COMMENT ON COLUMN org_retrieve.name IS '企业名称';
COMMENT ON COLUMN org_retrieve.org_id IS '企业ID';
COMMENT ON COLUMN org_retrieve.email IS '企业邮箱';
COMMENT ON COLUMN org_retrieve.passwd IS '密码';
COMMENT ON COLUMN org_retrieve.solve_status IS '解决状态(-1-认证失败，1-待处理，2-处理中，3-认证通过)';
COMMENT ON COLUMN org_retrieve.create_time IS '创建时间';

-- ----------------------------
-- Table structure for org_retrieve_img
-- ----------------------------
DROP TABLE IF EXISTS org_retrieve_img;
CREATE TABLE org_retrieve_img (
	id char(32) NOT NULL,
	org_retrieve_id char(32) NOT NULL,
	src varchar(255) NOT NULL
);
COMMENT ON TABLE org_retrieve_img IS '企业账号找回申请图片表';
COMMENT ON COLUMN org_retrieve_img.org_retrieve_id IS '企业账号找回申请ID';
COMMENT ON COLUMN org_retrieve_img.src IS '图片路径';

-- ----------------------------
-- Table structure for org_subject
-- ----------------------------
DROP TABLE IF EXISTS org_subject;
CREATE TABLE org_subject (
	organization_id char(32) NOT NULL,
	subject_id int2 NOT NULL
);

-- ----------------------------
-- Table structure for org_user
-- ----------------------------
DROP TABLE IF EXISTS org_user;
CREATE TABLE org_user (
	id char(32) NOT NULL,
	email varchar(50),
	passwd char(32) NOT NULL,
	user_type char(1),
	inviter_id char(32),
	create_time char(14) NOT NULL
);
COMMENT ON TABLE org_user IS '企业用户表';
COMMENT ON COLUMN org_user.id IS '企业用户ID';
COMMENT ON COLUMN org_user.email IS '注册邮箱';
COMMENT ON COLUMN org_user.passwd IS '密码';
COMMENT ON COLUMN org_user.user_type IS '用户类型(0-个人用户，1-企业用户)';
COMMENT ON COLUMN org_user.inviter_id IS '邀请人ID（目前没用）';
COMMENT ON COLUMN org_user.create_time IS '创建时间';

-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS organization;
CREATE TABLE organization (
	org_attr varchar(32),
	id char(32) NOT NULL,
	name varchar(100) COLLATE zh_CN NOT NULL,
	org_type varchar(32),
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL,
	descp text,
	org_url varchar(255),
	found_time char(8),
	province varchar(255),
	city varchar(255),
	subject text,
	industry text,
	org_size char(1),
	qualification text,
	is_join char(1),
	auth_status varchar(2),
	for_short varchar(20)
);
COMMENT ON TABLE organization IS '企业信息表';
COMMENT ON COLUMN organization.org_attr IS '字段类型待修改，逗号分隔，多个属性';
COMMENT ON COLUMN organization.name IS '企业名称';
COMMENT ON COLUMN organization.org_type IS '企业类型(2-上市企业，3-国有企业，4-合资企业，5-私人企业，6-外资企业，7-初创企业)';
COMMENT ON COLUMN organization.create_time IS '创建时间';
COMMENT ON COLUMN organization.modify_time IS '修改时间';
COMMENT ON COLUMN organization.descp IS '企业简介';
COMMENT ON COLUMN organization.org_url IS '企业官网';
COMMENT ON COLUMN organization.found_time IS '创立时间';
COMMENT ON COLUMN organization.province IS '所在省份';
COMMENT ON COLUMN organization.city IS '所在城市';
COMMENT ON COLUMN organization.subject IS '学术领域';
COMMENT ON COLUMN organization.industry IS '应用行业';
COMMENT ON COLUMN organization.org_size IS '企业规模(1:50人以内，2:50-100人，3:100-200人，4:200-500人，5:500-1000人，6:1000人以上)';
COMMENT ON COLUMN organization.qualification IS '企业资质';
COMMENT ON COLUMN organization.is_join IS '是否入驻(0-未入驻，1-已入驻)';
COMMENT ON COLUMN organization.auth_status IS '企业认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN organization.for_short IS '企业简称';

-- ----------------------------
-- Table structure for paper
-- ----------------------------
DROP TABLE IF EXISTS paper;
CREATE TABLE paper (
	professor_id char(32) NOT NULL,
	id char(32) NOT NULL,
	year char(4),
	name text NOT NULL,
	url text,
	descp text
);
COMMENT ON TABLE paper IS '专家发表的书刊杂志记录表';
COMMENT ON COLUMN paper.professor_id IS '用户ID';
COMMENT ON COLUMN paper.year IS '年份';
COMMENT ON COLUMN paper.name IS '书刊杂志名';
COMMENT ON COLUMN paper.url IS '链接';
COMMENT ON COLUMN paper.descp IS '详细描述';

-- ----------------------------
-- Table structure for part_time_job
-- ----------------------------
DROP TABLE IF EXISTS part_time_job;
CREATE TABLE part_time_job (
	id char(32) NOT NULL,
	start_month char(6),
	stop_month char(6),
	professor_id char(32) NOT NULL,
	company varchar(255) NOT NULL,
	title varchar(255) NOT NULL,
	descp text,
	department varchar(255)
);
COMMENT ON TABLE part_time_job IS '工作经历表';
COMMENT ON COLUMN part_time_job.start_month IS '入职时间（年月）';
COMMENT ON COLUMN part_time_job.stop_month IS '离职时间（年月）';
COMMENT ON COLUMN part_time_job.professor_id IS '用户ID';
COMMENT ON COLUMN part_time_job.company IS '所在公司';
COMMENT ON COLUMN part_time_job.title IS '职务';
COMMENT ON COLUMN part_time_job.descp IS '详细描述';
COMMENT ON COLUMN part_time_job.department IS '所在部门';

-- ----------------------------
-- Table structure for patent
-- ----------------------------
DROP TABLE IF EXISTS patent;
CREATE TABLE patent (
	id char(32) NOT NULL,
	year char(4),
	name text NOT NULL,
	professor_id char(32) NOT NULL,
	url varchar(255),
	descp text
);
COMMENT ON TABLE patent IS '专利表';
COMMENT ON COLUMN patent.year IS '年份';
COMMENT ON COLUMN patent.name IS '专利名';
COMMENT ON COLUMN patent.professor_id IS '用户ID';
COMMENT ON COLUMN patent.url IS '链接';
COMMENT ON COLUMN patent.descp IS '详细描述';

-- ----------------------------
-- Table structure for pay_history
-- ----------------------------
DROP TABLE IF EXISTS pay_history;
CREATE TABLE pay_history (
	pay_history_id char(32) NOT NULL,
	operation_id char(32) NOT NULL,
	paid_money numeric(24,2),
	create_time char(14) NOT NULL,
	remark text,
	isonline int2,
	return_money numeric(24,2),
	pay_type int2,
	pay_status int2,
	professor_id char(32) NOT NULL
);
COMMENT ON TABLE pay_history IS '支付历史记录（暂时没用）';

-- ----------------------------
-- Table structure for professor
-- ----------------------------
DROP TABLE IF EXISTS professor;
CREATE TABLE professor (
	office text,
	subject text,
	industry text,
	department varchar(255),
	org_id char(32),
	title varchar(255),
	id char(32) NOT NULL,
	name varchar(255) NOT NULL,
	descp text,
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL,
	address varchar(255),
	star_level numeric(4,2) DEFAULT 0 NOT NULL,
	consult_count int2 DEFAULT 0 NOT NULL,
	province varchar(255),
	star_avg numeric(4,2) DEFAULT 0 NOT NULL,
	authentication int2,
	auth_type int2 DEFAULT 0 NOT NULL,
	auth_status int2 DEFAULT 0 NOT NULL,
	phone varchar(20),
	email varchar(50),
	sort_first int2 DEFAULT 0 NOT NULL,
	auth_status_expert int2 DEFAULT 0,
	org_auth char(1),
	score_value int4,
	growth_value int4,
	score_percent varchar(10),
	id_card char(18),
	cuser_id char(32),
	professor_state int2 DEFAULT 0 NOT NULL
);
COMMENT ON TABLE professor IS '个人用户信息表';
COMMENT ON COLUMN professor.office IS '职位';
COMMENT ON COLUMN professor.subject IS '学术领域';
COMMENT ON COLUMN professor.industry IS '应用行业';
COMMENT ON COLUMN professor.department IS '所属部门';
COMMENT ON COLUMN professor.org_id IS '所属企业ID';
COMMENT ON COLUMN professor.title IS '职称';
COMMENT ON COLUMN professor.id IS '用户ID';
COMMENT ON COLUMN professor.name IS '姓名';
COMMENT ON COLUMN professor.descp IS '个人简介';
COMMENT ON COLUMN professor.create_time IS '创建时间';
COMMENT ON COLUMN professor.modify_time IS '修改时间';
COMMENT ON COLUMN professor.address IS '所在城市';
COMMENT ON COLUMN professor.star_level IS '星级';
COMMENT ON COLUMN professor.consult_count IS '接受的咨询次数（完成状态）';
COMMENT ON COLUMN professor.province IS '所在省份';
COMMENT ON COLUMN professor.star_avg IS '平均星级';
COMMENT ON COLUMN professor.authentication IS '用户身份(0-普通用户,1-科研工作者,2-企业高管,3-在校生,4-技术人员,5-HR/猎头,6-销售人员,7-投资方,8-咨询顾问)';
COMMENT ON COLUMN professor.auth_type IS '用户类型(0-普通用户,1-专家用户)';
COMMENT ON COLUMN professor.auth_status IS '实名认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN professor.phone IS '联系电话';
COMMENT ON COLUMN professor.email IS '联系邮箱';
COMMENT ON COLUMN professor.sort_first IS '第一排序字段(运营人员可修改)';
COMMENT ON COLUMN professor.auth_status_expert IS '专家认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN professor.org_auth IS '企业认证状态(0-未认证，1-已认证)';
COMMENT ON COLUMN professor.score_value IS '积分值';
COMMENT ON COLUMN professor.growth_value IS '成长值';
COMMENT ON COLUMN professor.score_percent IS '积分超过的用户百分比';
COMMENT ON COLUMN professor.id_card IS '身份证号';
COMMENT ON COLUMN professor.cuser_id IS '录入员id（运营人员id）';
COMMENT ON COLUMN professor.professor_state IS '专家录入审核状态（0=审核通过2=未审核1=审核失败）';

-- ----------------------------
-- Table structure for professor_edu_bg
-- ----------------------------
DROP TABLE IF EXISTS professor_edu_bg;
CREATE TABLE professor_edu_bg (
	id char(32) NOT NULL,
	year char(4),
	student char(1) NOT NULL,
	degree varchar(255),
	professor_id char(32) NOT NULL,
	school varchar(255) NOT NULL,
	college varchar(255),
	major varchar(255),
	descp text
);
COMMENT ON TABLE professor_edu_bg IS '用户教育背景';
COMMENT ON COLUMN professor_edu_bg.year IS '年份';

-- ----------------------------
-- Table structure for project
-- ----------------------------
DROP TABLE IF EXISTS project;
CREATE TABLE project (
	id char(32) NOT NULL,
	start_month char(6),
	stop_month char(6),
	professor_id char(32) NOT NULL,
	name text NOT NULL,
	descp text
);
COMMENT ON TABLE project IS '项目经验表';

-- ----------------------------
-- Table structure for research_area
-- ----------------------------
DROP TABLE IF EXISTS research_area;
CREATE TABLE research_area (
	sort_num int4 NOT NULL,
	descp text,
	count int4 NOT NULL,
	professor_id char(32) NOT NULL,
	caption varchar(255) NOT NULL
);
COMMENT ON TABLE research_area IS '研究方向表';
COMMENT ON COLUMN research_area.count IS '被赞次数';
COMMENT ON COLUMN research_area.professor_id IS '用户ID';
COMMENT ON COLUMN research_area.caption IS '研究方向';

-- ----------------------------
-- Table structure for research_area_log
-- ----------------------------
DROP TABLE IF EXISTS research_area_log;
CREATE TABLE research_area_log (
	oprete_professor_id char(32) NOT NULL,
	create_time char(14) NOT NULL,
	professor_id char(32) NOT NULL,
	caption varchar(255) NOT NULL
);
COMMENT ON TABLE research_area_log IS '研究方向点赞记录表';
COMMENT ON COLUMN research_area_log.oprete_professor_id IS '点赞用户ID';
COMMENT ON COLUMN research_area_log.create_time IS '点赞时间';
COMMENT ON COLUMN research_area_log.professor_id IS '被赞用户ID';
COMMENT ON COLUMN research_area_log.caption IS '被赞的研究方向';

-- ----------------------------
-- Table structure for resource
-- ----------------------------
DROP TABLE IF EXISTS resource;
CREATE TABLE resource (
	resource_id char(32) NOT NULL,
	resource_name varchar(255) COLLATE zh_CN NOT NULL,
	supported_services varchar(255) NOT NULL,
	descp text,
	professor_id char(32) NOT NULL,
	ascription varchar(255),
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL,
	hope_pay_method varchar(255),
	cooperation_notes text,
	subject text,
	industry text
);
COMMENT ON TABLE resource IS '资源表';
COMMENT ON COLUMN resource.resource_id IS '资源ID';
COMMENT ON COLUMN resource.resource_name IS '资源名称';
COMMENT ON COLUMN resource.supported_services IS '应用用途';
COMMENT ON COLUMN resource.descp IS '详细描述';
COMMENT ON COLUMN resource.professor_id IS '专家id';
COMMENT ON COLUMN resource.ascription IS '暂时没有用（所属人）';
COMMENT ON COLUMN resource.create_time IS '创建时间';
COMMENT ON COLUMN resource.modify_time IS '修改时间';
COMMENT ON COLUMN resource.hope_pay_method IS '期望付款方式（暂时没有用）';
COMMENT ON COLUMN resource.cooperation_notes IS '合作备注';
COMMENT ON COLUMN resource.subject IS '关键词';
COMMENT ON COLUMN resource.industry IS '暂时没有用（应用行业）';
COMMENT ON COLUMN resource.page_views IS '浏览量';
COMMENT ON COLUMN resource.comp IS '所在单位';
COMMENT ON COLUMN resource.resource_type IS '资源类型(1-个人资源，2-企业资源)';

-- ----------------------------
-- Table structure for resource_tmp
-- ----------------------------
DROP TABLE IF EXISTS resource_tmp;
CREATE TABLE resource_tmp (
	id varchar(255) NOT NULL,
	name text NOT NULL,
	domain text,
	comp text,
	spec text,
	img text,
	parameter text,
	major_func text,
	cost float8,
	enabled_time text,
	communication text,
	org_name text,
	corporation text,
	linkman text,
	tel text,
	email text,
	addr text,
	zip_code text,
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL
);
COMMENT ON COLUMN resource_tmp.name IS '资源名称(resource_name)';
COMMENT ON COLUMN resource_tmp.domain IS '关键词(subject)';
COMMENT ON COLUMN resource_tmp.comp IS '所在单位(comp)';
COMMENT ON COLUMN resource_tmp.spec IS '厂商及型号规格(spec)';
COMMENT ON COLUMN resource_tmp.img IS '图片路径';
COMMENT ON COLUMN resource_tmp.parameter IS '性能参数(paremeter)';
COMMENT ON COLUMN resource_tmp.major_func IS '应用用途(supported_services、desc)';
COMMENT ON COLUMN resource_tmp.cost IS '仪器原值（万元）';
COMMENT ON COLUMN resource_tmp.enabled_time IS '启用时间';
COMMENT ON COLUMN resource_tmp.communication IS '联系方式';
COMMENT ON COLUMN resource_tmp.org_name IS '机构名称';
COMMENT ON COLUMN resource_tmp.corporation IS '负责人';
COMMENT ON COLUMN resource_tmp.linkman IS '联系人 ';
COMMENT ON COLUMN resource_tmp.tel IS '电话';
COMMENT ON COLUMN resource_tmp.email IS '邮箱';
COMMENT ON COLUMN resource_tmp.addr IS '地址';
COMMENT ON COLUMN resource_tmp.zip_code IS '邮编';

-- ----------------------------
-- Table structure for resource_tmp_log
-- ----------------------------
DROP TABLE IF EXISTS resource_tmp_log;
CREATE TABLE resource_tmp_log (
	owner_id char(32) NOT NULL,
	resource_id char(32) NOT NULL,
	resource_tmp_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE resource_tmp_log IS '资源导入记录表';
COMMENT ON COLUMN resource_tmp_log.owner_id IS '操作的用户ID（个人或企业）';
COMMENT ON COLUMN resource_tmp_log.resource_id IS '资源ID';
COMMENT ON COLUMN resource_tmp_log.resource_tmp_id IS '临时资源ID';
COMMENT ON COLUMN resource_tmp_log.create_time IS '导入时间';

-- ----------------------------
-- Table structure for tidings
-- ----------------------------
DROP TABLE IF EXISTS tidings;
CREATE TABLE tidings (
	tidings_id char(32) NOT NULL,
	tidings_contant text NOT NULL,
	sender_id char(32) NOT NULL,
	consult_id char(32) NOT NULL,
	create_time char(14) NOT NULL,
	read_status int2 NOT NULL
);
COMMENT ON COLUMN tidings.tidings_id IS '咨询消息ID';
COMMENT ON COLUMN tidings.tidings_contant IS '消息内容';
COMMENT ON COLUMN tidings.sender_id IS '发送者ID';
COMMENT ON COLUMN tidings.consult_id IS '咨询ID';
COMMENT ON COLUMN tidings.create_time IS '创建时间';
COMMENT ON COLUMN tidings.read_status IS '查看状态（0-未查看，1-已查看）';

-- ----------------------------
-- Table structure for user_open_id
-- ----------------------------
DROP TABLE IF EXISTS user_open_id;
CREATE TABLE user_open_id (
	userid char(32) NOT NULL,
	oauth_type text NOT NULL,
	openid text NOT NULL,
	create_time char(14) NOT NULL,
	modify_time char(14) NOT NULL
);

-- ----------------------------
-- Table structure for watch
-- ----------------------------
DROP TABLE IF EXISTS watch;
CREATE TABLE watch (
	professor_id char(32) NOT NULL,
	watch_object char(32) NOT NULL,
	create_time char(14) NOT NULL,
	watch_type int2 NOT NULL
);
COMMENT ON TABLE watch IS '关注表';
COMMENT ON COLUMN watch.professor_id IS '专家ID';
COMMENT ON COLUMN watch.watch_object IS '关注对象ID';
COMMENT ON COLUMN watch.create_time IS '关注时间';
COMMENT ON COLUMN watch.watch_type IS '关注类型(专家-1，资源-2，3-文章)';

ALTER TABLE accessory ADD PRIMARY KEY (accessory_id);

-- ----------------------------
-- Primary Key structure for table article
-- ----------------------------
ALTER TABLE article ADD PRIMARY KEY (article_id);

-- ----------------------------
-- Primary Key structure for table article_agree
-- ----------------------------
ALTER TABLE article_agree ADD PRIMARY KEY (operate_id, article_id);



-- ----------------------------
-- Primary Key structure for table assess
-- ----------------------------
ALTER TABLE assess ADD PRIMARY KEY (assess_id);

-- ----------------------------
-- Primary Key structure for table auth_apply
-- ----------------------------
ALTER TABLE auth_apply ADD PRIMARY KEY (auth_apply_id);

-- ----------------------------
-- Primary Key structure for table auth_image
-- ----------------------------
ALTER TABLE auth_image ADD PRIMARY KEY (auth_image_id);

-- ----------------------------
-- Primary Key structure for table complain
-- ----------------------------
ALTER TABLE complain ADD PRIMARY KEY (complain_id);

-- ----------------------------
-- Primary Key structure for table consult
-- ----------------------------
ALTER TABLE consult ADD PRIMARY KEY (consult_id);

-- ----------------------------
-- Primary Key structure for table consult_reject
-- ----------------------------
ALTER TABLE consult_reject ADD PRIMARY KEY (reject_id);

-- ----------------------------
-- Uniques structure for table data_dict
-- ----------------------------
ALTER TABLE data_dict ADD UNIQUE (dict_code, caption);

-- ----------------------------
-- Primary Key structure for table data_dict
-- ----------------------------
ALTER TABLE data_dict ADD PRIMARY KEY (code, dict_code);

-- ----------------------------
-- Primary Key structure for table demand
-- ----------------------------
ALTER TABLE demand ADD PRIMARY KEY (demand_id);

-- ----------------------------
-- Primary Key structure for table demand_sub_or_indus
-- ----------------------------
ALTER TABLE demand_sub_or_indus ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table dict_city
-- ----------------------------
ALTER TABLE dict_city ADD PRIMARY KEY (dict_code);

-- ----------------------------
-- Primary Key structure for table dict_meta
-- ----------------------------
ALTER TABLE dict_meta ADD PRIMARY KEY (code);

-- ----------------------------
-- Primary Key structure for table growth_log
-- ----------------------------
ALTER TABLE growth_log ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table honor
-- ----------------------------
ALTER TABLE honor ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table image
-- ----------------------------
ALTER TABLE image ADD PRIMARY KEY (image_id);

-- ----------------------------
-- Primary Key structure for table leave_word
-- ----------------------------
ALTER TABLE leave_word ADD PRIMARY KEY (id);

-- ----------------------------
-- Uniques structure for table luser
-- ----------------------------
ALTER TABLE luser ADD UNIQUE (email);
ALTER TABLE luser ADD UNIQUE (mobile_phone);

-- ----------------------------
-- Primary Key structure for table luser
-- ----------------------------
ALTER TABLE luser ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table messages
-- ----------------------------
ALTER TABLE messages ADD PRIMARY KEY (message_id);

-- ----------------------------
-- Primary Key structure for table operate_service_file
-- ----------------------------
ALTER TABLE operate_service_file ADD PRIMARY KEY (service_file_id);

-- ----------------------------
-- Primary Key structure for table operate_service_log
-- ----------------------------
ALTER TABLE operate_service_log ADD PRIMARY KEY (service_log_id);

-- ----------------------------
-- Primary Key structure for table operation
-- ----------------------------
ALTER TABLE operation ADD PRIMARY KEY (operation_id);

-- ----------------------------
-- Primary Key structure for table operation_status
-- ----------------------------
ALTER TABLE operation_status ADD PRIMARY KEY (status_id);

-- ----------------------------
-- Uniques structure for table org_res_staff
-- ----------------------------
ALTER TABLE org_res_staff ADD UNIQUE (resource_id, professor_id);

-- ----------------------------
-- Primary Key structure for table org_retrieve
-- ----------------------------
ALTER TABLE org_retrieve ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table org_retrieve_img
-- ----------------------------
ALTER TABLE org_retrieve_img ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table org_subject
-- ----------------------------
ALTER TABLE org_subject ADD PRIMARY KEY (organization_id, subject_id);

-- ----------------------------
-- Uniques structure for table org_user
-- ----------------------------
ALTER TABLE org_user ADD UNIQUE (email);

-- ----------------------------
-- Primary Key structure for table org_user
-- ----------------------------
ALTER TABLE org_user ADD PRIMARY KEY (id);

-- ----------------------------
-- Indexes structure for table organization
-- ----------------------------
CREATE UNIQUE INDEX organization_name_key ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key1 ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key2 ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key3 ON organization USING btree (name COLLATE zh_CN);

-- ----------------------------
-- Uniques structure for table organization
-- ----------------------------
ALTER TABLE organization ADD UNIQUE (name);

-- ----------------------------
-- Primary Key structure for table organization
-- ----------------------------
ALTER TABLE organization ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table paper
-- ----------------------------
ALTER TABLE paper ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table part_time_job
-- ----------------------------
ALTER TABLE part_time_job ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table patent
-- ----------------------------
ALTER TABLE patent ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table pay_history
-- ----------------------------
ALTER TABLE pay_history ADD PRIMARY KEY (pay_history_id);

-- ----------------------------
-- Primary Key structure for table professor
-- ----------------------------
ALTER TABLE professor ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table professor_edu_bg
-- ----------------------------
ALTER TABLE professor_edu_bg ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table project
-- ----------------------------
ALTER TABLE project ADD PRIMARY KEY (id);

-- ----------------------------
-- Primary Key structure for table research_area
-- ----------------------------
ALTER TABLE research_area ADD PRIMARY KEY (professor_id, caption);

-- ----------------------------
-- Primary Key structure for table resource
-- ----------------------------
ALTER TABLE resource ADD PRIMARY KEY (resource_id);

-- ----------------------------
-- Primary Key structure for table resource_tmp
-- ----------------------------
ALTER TABLE resource_tmp ADD PRIMARY KEY (id);

-- ----------------------------
-- Uniques structure for table resource_tmp_log
-- ----------------------------
ALTER TABLE resource_tmp_log ADD UNIQUE (resource_id, resource_tmp_id);

-- ----------------------------
-- Primary Key structure for table tidings
-- ----------------------------
ALTER TABLE tidings ADD PRIMARY KEY (tidings_id);

-- ----------------------------
-- Uniques structure for table user_open_id
-- ----------------------------
ALTER TABLE user_open_id ADD UNIQUE (userid, oauth_type);
ALTER TABLE user_open_id ADD UNIQUE (oauth_type, openid);

-- ----------------------------
-- Primary Key structure for table user_open_id
-- ----------------------------
ALTER TABLE user_open_id ADD PRIMARY KEY (oauth_type, openid, userid);

-- ----------------------------
-- Uniques structure for table watch
-- ----------------------------
ALTER TABLE watch ADD UNIQUE (professor_id, watch_object, watch_type);

-- ----------------------------
-- Primary Key structure for table watch
-- ----------------------------
ALTER TABLE watch ADD PRIMARY KEY (professor_id, watch_object);


/*
 * modify 20170607
 * 
 * 
 * 
 */


ALTER TABLE resource ADD COLUMN org_name VARCHAR (255);

ALTER TABLE resource ADD COLUMN spec VARCHAR (255);

COMMENT ON COLUMN resource.spec IS '厂商及型号规格';

ALTER TABLE resource ADD COLUMN PARAMETER TEXT;

COMMENT ON COLUMN resource. PARAMETER IS '性能参数';

ALTER TABLE resource ADD COLUMN publish_time CHAR (14);

COMMENT ON COLUMN resource.publish_time IS '发布时间';

ALTER TABLE resource ADD COLUMN status CHAR (1);

COMMENT ON COLUMN resource.status IS '资源状态(0-草稿，1-发布，3-删除，4-关闭)';

ALTER TABLE image ADD COLUMN sort INTEGER NULL;

UPDATE image SET sort = 1;

ALTER TABLE image ALTER COLUMN sort SET NOT NULL;

UPDATE image SET image_src = REPLACE (	image_src,	'/kexiu/www/html/images/resource','201704');

ALTER TABLE resource ADD COLUMN page_views INTEGER;

COMMENT ON COLUMN resource.page_views IS '浏览量';

UPDATE resource SET page_views = 0, status = '1', publish_time = modify_time;

ALTER TABLE resource ADD COLUMN comp VARCHAR (255);

COMMENT ON COLUMN resource.comp IS '所在单位';

ALTER TABLE resource ADD COLUMN resource_type CHAR (1);

COMMENT ON COLUMN resource.resource_type IS '资源类型(1-个人资源，2-企业资源)';

ALTER TABLE resource ALTER COLUMN professor_id DROP NOT NULL;

ALTER TABLE resource ADD COLUMN org_id CHAR (32);

CREATE TABLE ORG_RES_STAFF (
	RESOURCE_ID CHAR (32) NOT NULL,
	PROFESSOR_ID CHAR (32) NOT NULL,
	CREATE_TIME CHAR (14) NOT NULL
);

ALTER TABLE ORG_RES_STAFF ADD UNIQUE (RESOURCE_ID, PROFESSOR_ID);

CREATE TABLE RESOURCE_TMP_LOG (
	OWNER_ID CHAR (32) NOT NULL,
	RESOURCE_ID CHAR (32) NOT NULL,
	RESOURCE_TMP_ID CHAR (32) NOT NULL,
	CREATE_TIME CHAR (14) NOT NULL
);

ALTER TABLE RESOURCE_TMP_LOG ADD UNIQUE (
	RESOURCE_ID,
	RESOURCE_TMP_ID
);

ALTER TABLE article ADD COLUMN page_views INT4;

COMMENT ON COLUMN article.page_views IS '浏览量';

ALTER TABLE article ADD COLUMN status CHAR (1);

COMMENT ON COLUMN article.status IS '文章状态(0-草稿，1-发布，2-删除，3关闭)';

ALTER TABLE article ADD COLUMN publish_time CHAR(14);

COMMENT ON COLUMN article.publish_time IS '文章发布时间';

UPDATE article SET page_views = 0,status = '1',publish_time = modify_time;

-- ----------------------------
-- Table structure for article_pro
-- ----------------------------
DROP TABLE IF EXISTS article_pro;
CREATE TABLE article_pro (
	article_id char(32) NOT NULL,
	professor_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE article_pro IS '文章相关专家';
COMMENT ON COLUMN article_pro.article_id IS '文章ID';
COMMENT ON COLUMN article_pro.professor_id IS '用户ID';
COMMENT ON COLUMN article_pro.create_time IS '创建时间';

DROP TABLE IF EXISTS article_res;
CREATE TABLE article_res (
	article_id char(32) NOT NULL,
	resource_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE article_res IS '文章相关资源';
COMMENT ON COLUMN article_res.article_id IS '文章ID';
COMMENT ON COLUMN article_res.resource_id IS '资源ID';
COMMENT ON COLUMN article_res.create_time IS '创建时间';

-- ----------------------------
-- Uniques structure for table article_res
-- ----------------------------
ALTER TABLE article_res ADD PRIMARY KEY (article_id, resource_id);
-- ----------------------------
-- Uniques structure for table article_pro
-- ----------------------------
ALTER TABLE article_pro ADD PRIMARY KEY (article_id, professor_id);

update resource set resource_type='1';

-----begin version 1.9------------------------
ALTER TABLE organization ADD COLUMN addr  text COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.addr IS '企为地址';
ALTER TABLE organization ADD COLUMN email  text;
COMMENT ON COLUMN organization.email IS '联系邮箱';
ALTER TABLE organization ADD COLUMN contact_num  text;
COMMENT ON COLUMN organization.contact_num IS '联系电话';
ALTER TABLE organization ADD COLUMN field_of_customer  text  COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.field_of_customer IS '客户领域';
ALTER TABLE organization ADD COLUMN field_of_supplier  text  COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.field_of_supplier IS '供应商领域';
ALTER TABLE organization ADD COLUMN sort_num  int8 default 0 not null;
COMMENT ON COLUMN organization.sort_num IS '排序值';
ALTER TABLE organization ADD COLUMN page_views  int8 default 0 not null;
COMMENT ON COLUMN organization.page_views IS '浏览量';


COMMENT ON COLUMN watch.watch_type IS '关注类型(专家-1，资源-2，3-文章,4-专利 ，5-论文,6-企业)';
CREATE TABLE ppatent (
id char(32)  NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
page_views int8 DEFAULT 0 NOT NULL,
code text  NOT NULL,
req_code text NOT NULL,
req_person text COLLATE "zh_CN.utf8",
req_day char(8) NOT NULL,
pub_day char(8) NOT NULL,
summary text COLLATE "zh_CN.utf8" NOT NULL,
ref_param text ,
authors text COLLATE "zh_CN.utf8" NOT NULL,
sort_num int8 DEFAULT 0 NOT NULL,
create_time char(14)  NOT NULL,
keywords text COLLATE "zh_CN.utf8"
);
COMMENT ON COLUMN ppatent.name IS '名称';
COMMENT ON COLUMN ppatent.page_views IS '浏览量';
COMMENT ON COLUMN ppatent.code IS '专利号';
COMMENT ON COLUMN ppatent.req_code IS '申请号';
COMMENT ON COLUMN ppatent.req_person IS '申请人';
COMMENT ON COLUMN ppatent.summary IS '摘要';
COMMENT ON COLUMN ppatent.keywords IS '关键词';
COMMENT ON COLUMN ppatent.authors IS '作者列表，以英文逗号分隔';
COMMENT ON COLUMN ppatent.sort_num IS '排序字段，人工权重';
CREATE UNIQUE INDEX ppatent_pkey ON ppatent USING btree (id);
ALTER TABLE ppatent ADD PRIMARY KEY (id);

CREATE TABLE patent_author (
patent_id char(32) NOT NULL,
professor_id char(32) DEFAULT '################################'::bpchar NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
ass_time char(14) DEFAULT '00010101010101'::bpchar NOT NULL);
COMMENT ON COLUMN patent_author.patent_id IS '专利ID';
COMMENT ON COLUMN patent_author.professor_id IS '专家id(=32个#时，还没有关专家)';
COMMENT ON COLUMN patent_author.name IS '作者姓名';
COMMENT ON COLUMN patent_author.ass_time IS '关联专家时间';
CREATE UNIQUE INDEX patent_author_pkey ON patent_author USING btree (patent_id,name COLLATE "zh_CN.utf8");
ALTER TABLE patent_author ADD PRIMARY KEY (patent_id,name);

CREATE TABLE ppaper (
id char(32)  NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
page_views int8 DEFAULT 0 NOT NULL,
cn4periodical text,
en4periodical text,
periodicaltype text,
pub_day text,
summary text COLLATE "zh_CN.utf8" NOT NULL,
keywords text COLLATE "zh_CN.utf8",
authors text COLLATE "zh_CN.utf8" NOT NULL,
sort_num int8 DEFAULT 0 NOT NULL,
create_time char(14) NOT NULL,
ref_param text);
COMMENT ON COLUMN ppaper.name IS '名称';
COMMENT ON COLUMN ppaper.page_views IS '浏览量';
COMMENT ON COLUMN ppaper.cn4periodical IS '期刊中文名';
COMMENT ON COLUMN ppaper.en4periodical IS '期刊英文名';
COMMENT ON COLUMN ppaper.pub_day IS '发表期数';
COMMENT ON COLUMN ppaper.summary IS '摘要';
COMMENT ON COLUMN ppaper.keywords IS '关键词';
COMMENT ON COLUMN ppaper.authors IS '作者列表，以英文逗号分隔';
COMMENT ON COLUMN ppaper.sort_num IS '排序字段，人工权重';
CREATE UNIQUE INDEX ppaper_pkey ON ppaper USING btree (id);
ALTER TABLE ppaper ADD PRIMARY KEY (id);

CREATE TABLE paper_author (
paper_id char(32) NOT NULL,
professor_id char(32) DEFAULT '################################'::bpchar NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
ass_time char(14) DEFAULT '00010101010101'::bpchar NOT NULL);
COMMENT ON COLUMN paper_author.paper_id IS '专利ID';
COMMENT ON COLUMN paper_author.professor_id IS '专家id(=32个#时，还没有关专家)';
COMMENT ON COLUMN paper_author.name IS '作者姓名';
COMMENT ON COLUMN paper_author.ass_time IS '关联专家时间';
CREATE UNIQUE INDEX paper_author_pkey ON paper_author USING btree (paper_id,name COLLATE "zh_CN.utf8");
ALTER TABLE paper_author ADD PRIMARY KEY (paper_id,name);
CREATE TABLE ORG_REG_INFO (
NAME TEXT  COLLATE "zh_CN.utf8" NOT NULL,
NUM TEXT ,
CODE TEXT,
CREDIT_CODE TEXT,
TYPE TEXT,
INDUSTRY TEXT,
OPERATING_PERIOD TEXT,
DAY_OF_APPROVAL TEXT,
MANAGER TEXT,
ADDR TEXT,
SCOPE_OF_BUSINESS TEXT);
ALTER TABLE ORG_REG_INFO ADD PRIMARY KEY (NAME);
COMMENT ON COLUMN ORG_REG_INFO.NAME IS '注册名称';
COMMENT ON COLUMN ORG_REG_INFO.NUM IS '工商注册号';
COMMENT ON COLUMN ORG_REG_INFO.CODE IS '组织机构代码';
COMMENT ON COLUMN ORG_REG_INFO.CREDIT_CODE IS '统一信用代码';
COMMENT ON COLUMN ORG_REG_INFO.TYPE IS '企业类型';
COMMENT ON COLUMN ORG_REG_INFO.INDUSTRY IS '行业';
COMMENT ON COLUMN ORG_REG_INFO.OPERATING_PERIOD IS '营业期限';
COMMENT ON COLUMN ORG_REG_INFO.DAY_OF_APPROVAL IS '核准日期';
COMMENT ON COLUMN ORG_REG_INFO.MANAGER IS '登记机关';
COMMENT ON COLUMN ORG_REG_INFO.ADDR IS '注册地址';
COMMENT ON COLUMN ORG_REG_INFO.SCOPE_OF_BUSINESS IS '经营范围';


CREATE TABLE feedback (
id bigserial NOT NULL,
title text,
category int4 NOT NULL,
"schema" int4 NOT NULL,
param text NOT NULL,
content text NOT NULL,
create_time char(14) NOT NULL,
state char(1) NOT NULL,
user_id text,
PRIMARY KEY (id)
);
COMMENT ON TABLE feedback IS '反馈信息';
COMMENT ON COLUMN feedback.id IS '反馈信息ID';
COMMENT ON COLUMN feedback.title IS '标题';
COMMENT ON COLUMN feedback.category IS '大类  1：纠错';
COMMENT ON COLUMN feedback."schema" IS '小类  1：论文，2：专利, 3 专家 4 机构 5 资源 6 文章';
COMMENT ON COLUMN feedback.content IS '内容';
COMMENT ON COLUMN feedback.param IS '参数：如 论文ID....';
COMMENT ON COLUMN feedback.state IS '状态，0：初始状态   ，其他由不同大类业务确定';
COMMENT ON COLUMN feedback.user_id IS '返馈人ID';


create sequence professor_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE professor ADD COLUMN share_id int8 DEFAULT nextval('professor_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN professor.share_id IS '共享ID';
create sequence organization_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE organization ADD COLUMN share_id int8 DEFAULT nextval('organization_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN organization.share_id IS '共享ID';
create sequence article_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE article ADD COLUMN share_id int8 DEFAULT nextval('article_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN article.share_id IS '共享ID';
create sequence resource_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE resource ADD COLUMN share_id int8 DEFAULT nextval('resource_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN resource.share_id IS '共享ID';
create sequence ppaper_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE ppaper ADD COLUMN share_id int8 DEFAULT nextval('ppaper_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN ppaper.share_id IS '共享ID';
create sequence ppatent_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
ALTER TABLE ppatent ADD COLUMN share_id int8 DEFAULT nextval('ppatent_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN ppatent.share_id IS '共享ID';



update organization set org_type = null;
ALTER TABLE  organization ADD COLUMN linkman text;
COMMENT ON COLUMN  organization.linkman IS '联系人';
ALTER TABLE article ADD COLUMN sort_num  int8 default 0 not null;
COMMENT ON COLUMN article.sort_num IS '排序字段，人工权重';

ALTER TABLE resource ADD COLUMN sort_num  int8 default 0 not null;
COMMENT ON COLUMN resource.sort_num IS '排序字段，人工权重';

--begin v1.9.1
ALTER TABLE professor ADD COLUMN page_views  int8 default 0 not null;
COMMENT ON COLUMN professor.page_views IS '浏览量';

CREATE TABLE art_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE art_key_word IS '文章关键字';
COMMENT ON COLUMN art_key_word.id IS '文章ID';
COMMENT ON COLUMN art_key_word.kw IS '关键字';
CREATE TABLE res_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE res_key_word IS '资源关键字';
COMMENT ON COLUMN res_key_word.id IS '资源ID';
COMMENT ON COLUMN res_key_word.kw IS '关键字';
CREATE TABLE pro_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pro_key_word IS '用户关键字';
COMMENT ON COLUMN pro_key_word.id IS '用户ID';
COMMENT ON COLUMN pro_key_word.kw IS '关键字';
CREATE TABLE org_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE org_key_word IS '企业关键字';
COMMENT ON COLUMN org_key_word.id IS '企业ID';
COMMENT ON COLUMN org_key_word.kw IS '关键字';
CREATE TABLE pap_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pap_key_word IS '论文关键字';
COMMENT ON COLUMN pap_key_word.id IS '论文ID';
COMMENT ON COLUMN pap_key_word.kw IS '关键字';
CREATE TABLE pat_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pat_key_word IS '专利关键字';
COMMENT ON COLUMN pat_key_word.id IS '专利ID';
COMMENT ON COLUMN pat_key_word.kw IS '关键字';

CREATE TABLE ARTICLE_ORG (ARTICLE_ID CHAR(32) NOT NULL,ORG_ID CHAR(32) NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE ARTICLE_ORG ADD UNIQUE (ARTICLE_ID,ORG_ID);
COMMENT ON COLUMN ARTICLE_ORG.ARTICLE_ID IS '文章ID';
COMMENT ON COLUMN ARTICLE_ORG.ORG_ID IS '企业ID';

alter table leave_word add column  category char(1) not null default '1';
comment on column leave_word.category is '留言分类  1： 文章  2：论文   3：专利';

ALTER TABLE leave_word RENAME article_id TO obj_id;
comment on column leave_word.obj_id is '留言对象ID';

CREATE TABLE paper_agree (
	op_id char(32) NOT NULL,
	paper_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE paper_agree IS '论文点赞表';
COMMENT ON COLUMN paper_agree.op_id IS '点赞人的ID';
COMMENT ON COLUMN paper_agree.paper_id IS '被点赞的论文ID';
COMMENT ON COLUMN paper_agree.create_time IS '点赞时间';

CREATE TABLE patent_agree (
	op_id char(32) NOT NULL,
	patent_id char(32) NOT NULL,
	create_time char(14) NOT NULL
);
COMMENT ON TABLE patent_agree IS '专利点赞表';
COMMENT ON COLUMN patent_agree.op_id IS '点赞人的ID';
COMMENT ON COLUMN patent_agree.patent_id IS '被点赞的专利ID';
COMMENT ON COLUMN patent_agree.create_time IS '点赞时间';

ALTER TABLE patent_agree ADD PRIMARY KEY (op_id,patent_id);
ALTER TABLE paper_agree ADD PRIMARY KEY (op_id,paper_id);

ALTER TABLE organization ALTER COLUMN is_join SET DEFAULT '0';
UPDATE organization set is_join='0' WHERE is_join is null;
ALTER TABLE organization ALTER COLUMN is_join SET NOT NULL;

drop view view_user_count;
drop view view_user_pictable;
drop view view_allcustomer;
drop view view_customer;
drop view view_my_customer;
ALTER TABLE professor ALTER COLUMN phone TYPE varchar(50);

CREATE OR REPLACE VIEW view_allcustomer AS 
 SELECT professor.id,professor.email,professor.phone,luser.mobile_phone,luser.create_time,luser.active_time,
    luser.send_mail_status,luser.invite_code,luser.inviter_id,luser.user_type,professor.subject,professor.industry,
    professor.department,professor.office,professor.org_id,professor.title,professor.name,professor.address,
    professor.authentication,professor.auth_type,professor.auth_status,professor.sort_first,professor.auth_status_expert,
    organization.name AS orgname
   FROM ((luser RIGHT JOIN professor ON ((luser.id = professor.id)))
                LEFT JOIN organization ON ((professor.org_id = organization.id)));;
CREATE OR REPLACE VIEW view_customer AS 
 SELECT professor.id,professor.email,professor.phone,luser.mobile_phone,luser.create_time,luser.active_time,luser.send_mail_status,
    luser.invite_code,luser.inviter_id,luser.user_type,professor.subject,professor.industry,professor.department,professor.office,
    professor.org_id,professor.title,professor.name,professor.address,professor.authentication,professor.auth_type,professor.auth_status,
    professor.sort_first,professor.auth_status_expert,organization.name AS orgname
   FROM ((luser
     RIGHT JOIN professor ON ((luser.id = professor.id)))
     LEFT JOIN organization ON ((professor.org_id = organization.id)))
  WHERE (((luser.inviter_id IS NULL) OR (luser.active_time IS NULL)) AND (NOT (EXISTS ( SELECT customer_power.professor_id
           FROM customer_power
          WHERE ((customer_power.professor_id = luser.id) AND ((customer_power.pow_type = '0'::bpchar) OR (customer_power.pow_type = '1'::bpchar)))))));;

CREATE OR REPLACE VIEW view_my_customer AS 
 SELECT customer_power.power_id,professor.id,professor.email,professor.subject,professor.industry,professor.department,professor.office,professor.org_id,
 professor.title,professor.name,professor.address,professor.authentication,professor.auth_type,professor.auth_status,professor.sort_first,professor.auth_status_expert,
 organization.name AS orgname,customer_power.descp,customer_power.create_time,customer_power.pow_type,customer_power.modify_time,customer_power.cuser_id,
 luser.mobile_phone AS phone,cuser.name AS cuser_name,luser.invite_code
   FROM ((((customer_power
     JOIN professor ON ((customer_power.professor_id = professor.id)))
     JOIN organization ON ((professor.org_id = organization.id)))
     JOIN luser ON ((professor.id = luser.id)))
     JOIN cuser ON ((customer_power.cuser_id = cuser.id)));;

CREATE OR REPLACE VIEW view_user_count AS 
 SELECT to_date(cc.create_time, 'yyyymmdd'::text) AS create_time,
    COALESCE(znum.num, (0)::bigint) AS znum,
    COALESCE(jnum.jnum, (0)::bigint) AS jnum,
    dn.ljnum,
    dn.lznum
   FROM (((( SELECT to_char(generate_series((date_trunc('month'::text, now()) - '1 mon'::interval), now(), '1 day'::interval), 'yyyymmdd'::text) AS create_time) cc
     LEFT JOIN ( SELECT num.create_time,count(num.id) AS num
           FROM ( SELECT luser.id,
                    "substring"((luser.create_time)::text, 1, 8) AS create_time
                   FROM luser
                  WHERE (to_date((luser.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (now() - '30 days'::interval))) num
          GROUP BY num.create_time) znum ON ((cc.create_time = znum.create_time)))
     LEFT JOIN ( SELECT num1.create_time,
            count(num1.id) AS jnum
           FROM ( SELECT luser.id,
                    "substring"((luser.active_time)::text, 1, 8) AS create_time
                   FROM luser
                  WHERE (to_date((luser.active_time)::text, 'YYYYMMDDHH24MISS'::text) >= (now() - '30 days'::interval))) num1
          GROUP BY num1.create_time) jnum ON ((cc.create_time = jnum.create_time)))
     LEFT JOIN ( SELECT d1.create_time,
            0 AS num,
            0 AS jnum,
            d1.ljnum,
            d1.lznum
           FROM ( SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time
                UNION
                 SELECT lj1.create_time,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            0 AS ljnum,
                            count(luser.id) AS lznum
                           FROM luser
                          WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))
                        UNION
                         SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            count(luser.id) AS ljnum,
                            0 AS lznum
                           FROM luser
                          WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))) lj1
                  GROUP BY lj1.create_time) d1) dn ON ((cc.create_time = dn.create_time)));;

CREATE OR REPLACE VIEW  view_user_pictable AS 
 SELECT dn.create_time,
    dn.cuser_id,
    sum(dn.znum) AS znum, sum(dn.jnum) AS jnum,
    sum(dn.ljnum) AS ljnum,
    sum(dn.lznum) AS lznum
   FROM ( SELECT to_date(u.create_time, 'YYYYMMDD'::text) AS create_time,
            u.cuser_id,
            0 AS znum,
            0 AS jnum,
            0 AS ljnum,
            0 AS lznum
           FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser
                UNION
                 SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
                    cuser.id AS cuser_id
                   FROM cuser) u
        UNION
         SELECT to_date(n1.create_time, 'YYYYMMDD'::text) AS create_time,
            n1.cuser_id,
            n1.znum,
            0 AS jnum,
            (0)::numeric AS ljnum,
            (0)::numeric AS lznum
           FROM ( SELECT "substring"((view_my_customer.create_time)::text, 1, 8) AS create_time,
                    view_my_customer.cuser_id,
                    count(view_my_customer.power_id) AS znum
                   FROM view_my_customer
                  WHERE (to_date((view_my_customer.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (('now'::text)::date - '6 days'::interval))
                  GROUP BY "substring"((view_my_customer.create_time)::text, 1, 8), view_my_customer.cuser_id) n1
        UNION
         SELECT to_date(j1.create_time, 'YYYYMMDD'::text) AS create_time,
            j1.cuser_id,
            0 AS znum,
            j1.jnum,
            (0)::numeric AS ljnum,
            (0)::numeric AS lznum
           FROM ( SELECT "substring"((view_my_customer.create_time)::text, 1, 8) AS create_time,
                    view_my_customer.cuser_id,
                    count(view_my_customer.power_id) AS jnum
                   FROM view_my_customer
                  WHERE ((to_date((view_my_customer.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (('now'::text)::date - '6 days'::interval)) AND (view_my_customer.pow_type = '1'::bpchar))
                  GROUP BY "substring"((view_my_customer.create_time)::text, 1, 8), view_my_customer.cuser_id) j1
        UNION
         SELECT to_date(d1.create_time, 'YYYYMMDD'::text) AS create_time,
            d1.cuser_id,
            0 AS znum,
            0 AS jnum,
            d1.ljnum,
            d1.lznum
           FROM ( SELECT lj1.create_time,
                    lj1.cuser_id,
                    sum(lj1.ljnum) AS ljnum,
                    sum(lj1.lznum) AS lznum
                   FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj1
                  GROUP BY lj1.create_time, lj1.cuser_id
                UNION
                 SELECT lj2.create_time,
                    lj2.cuser_id,
                    sum(lj2.ljnum) AS ljnum,
                    sum(lj2.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj2
                  GROUP BY lj2.create_time, lj2.cuser_id
                UNION
                 SELECT lj3.create_time,
                    lj3.cuser_id,
                    sum(lj3.ljnum) AS ljnum,
                    sum(lj3.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj3
                  GROUP BY lj3.create_time, lj3.cuser_id
                UNION
                 SELECT lj4.create_time,
                    lj4.cuser_id,
                    sum(lj4.ljnum) AS ljnum,
                    sum(lj4.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj4
                  GROUP BY lj4.create_time, lj4.cuser_id
                UNION
                 SELECT lj5.create_time,
                    lj5.cuser_id,
                    sum(lj5.ljnum) AS ljnum,
                    sum(lj5.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj5
                  GROUP BY lj5.create_time, lj5.cuser_id
                UNION
                 SELECT lj6.create_time,
                    lj6.cuser_id,
                    sum(lj6.ljnum) AS ljnum,
                    sum(lj6.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj6
                  GROUP BY lj6.create_time, lj6.cuser_id
                UNION
                 SELECT lj7.create_time,
                    lj7.cuser_id,
                    sum(lj7.ljnum) AS ljnum,
                    sum(lj7.lznum) AS lznum
                   FROM ( SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            0 AS ljnum,
                            count(view_my_customer.power_id) AS lznum
                           FROM view_my_customer
                          WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))
                          GROUP BY view_my_customer.cuser_id
                        UNION
                         SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
                            view_my_customer.cuser_id,
                            count(view_my_customer.power_id) AS ljnum,
                            0 AS lznum
                           FROM view_my_customer
                          WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
                          GROUP BY view_my_customer.cuser_id) lj7
                  GROUP BY lj7.create_time, lj7.cuser_id) d1) dn
  GROUP BY dn.create_time, dn.cuser_id
  ORDER BY dn.create_time;;

  
  --- modify begin at version 1.9.2
-- ALTER TABLE ARTICLE ADD COLUMN COL_NUM int4 not null default 0;
-- COMMENT ON COLUMN article.col_num IS '栏目号  <=0:没有栏目号，1：个人自建 2：企业自发  其它：由业务定义';
-- update article set col_num=1 where professor_id is not null;
-- update article set col_num=2 where org_id is not null;
ALTER TABLE organization ADD COLUMN col_mgr char(1) NOT NULL DEFAULT '0';
COMMENT ON COLUMN organization.col_mgr IS '是否是管理者 0：不是  1:是';

CREATE TABLE discover_banner (
  id bigserial PRIMARY KEY NOT NULL, -- 编号id
  title CHARACTER VARYING(50) NOT NULL, -- 外显标题
  banner SMALLINT NOT NULL, -- 栏目
  begin_time CHARACTER(14), -- 上架时间
  end_time CHARACTER(14), -- 下架时间
  clicks BIGINT NOT NULL DEFAULT 0, -- 点击量
  descp TEXT, -- 备注
  article_id CHARACTER(32) NOT NULL, -- 文章ID
  modify_time CHARACTER(14) NOT NULL, -- 修改时间
  modifier CHARACTER VARYING(32), -- 最后修改人ID
  create_time CHARACTER(14) NOT NULL, -- 创建时间
  creator CHARACTER VARYING(32) NOT NULL, -- 创建人ID
  location SMALLINT
);
COMMENT ON COLUMN discover_banner.id IS '编号id';
COMMENT ON COLUMN discover_banner.title IS '外显标题';
COMMENT ON COLUMN discover_banner.banner IS '栏目';
COMMENT ON COLUMN discover_banner.begin_time IS '上架时间';
COMMENT ON COLUMN discover_banner.end_time IS '下架时间';
COMMENT ON COLUMN discover_banner.clicks IS '点击量';
COMMENT ON COLUMN discover_banner.descp IS '备注';
COMMENT ON COLUMN discover_banner.article_id IS '文章ID';
COMMENT ON COLUMN discover_banner.modify_time IS '修改时间';
COMMENT ON COLUMN discover_banner.modifier IS '最后修改人ID';
COMMENT ON COLUMN discover_banner.create_time IS '创建时间';
COMMENT ON COLUMN discover_banner.creator IS '创建人ID';
COMMENT ON COLUMN discover_banner.location IS '显示位置';

  --- modify begin at version 2.0
  drop table DEMAND_SUB_OR_INDUS;
  drop table DEMAND;
  CREATE TABLE DEMAND (
  	ID CHAR(32) NOT NULL,
  	TITLE TEXT NOT NULL,
  	PROVINCE TEXT NOT NULL,
  	CITY TEXT,
  	COST CHAR(1) NOT NULL,
  	DURATION CHAR(1) NOT NULL,
  	INVALID_DAY CHAR(8) NOT NULL,
  	CONTACT_NUM TEXT NOT NULL,
  	STATE CHAR(1) NOT NULL,
  	ORG_ID CHAR(32) NOT NULL,
  	MODIFIER CHAR(32) NOT NULL,
  	CREATOR CHAR(32) NOT NULL,
  	DESCP TEXT NOT NULL,
  	PAGE_VIEWS INT8 NOT NULL,
  	SHARE_ID bigserial NOT NULL,
  	CREATE_TIME CHAR(14) NOT NULL,
  	MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE DEMAND ADD PRIMARY KEY (ID);

COMMENT ON TABLE DEMAND IS '需求';
COMMENT ON COLUMN  DEMAND.ID          IS '需求ID';
COMMENT ON COLUMN  DEMAND.TITLE       IS '主题';
COMMENT ON COLUMN  DEMAND.PROVINCE    IS '省份';
COMMENT ON COLUMN  DEMAND.CITY        IS '城市';
COMMENT ON COLUMN  DEMAND.COST        IS '费用预算';
COMMENT ON COLUMN  DEMAND.DURATION    IS '预计时长';
COMMENT ON COLUMN  DEMAND.INVALID_DAY IS '有效期';
COMMENT ON COLUMN  DEMAND.CONTACT_NUM IS '联系电话';
COMMENT ON COLUMN  DEMAND.STATE       IS '状态';
COMMENT ON COLUMN  DEMAND.ORG_ID      IS '发布人发布时机构ID';
COMMENT ON COLUMN  DEMAND.MODIFIER    IS '最后修改人';
COMMENT ON COLUMN  DEMAND.CREATOR     IS '发布人';
COMMENT ON COLUMN  DEMAND.DESCP       IS '内容';
COMMENT ON COLUMN  DEMAND.PAGE_VIEWS  IS '浏览量';
COMMENT ON COLUMN  DEMAND.SHARE_ID    IS '分享ID';
COMMENT ON COLUMN  DEMAND.CREATE_TIME IS '发布时间';
COMMENT ON COLUMN  DEMAND.MODIFY_TIME IS '最后修改时间，=完成时间 =关闭时间';
ALTER TABLE organization ADD COLUMN res_mgr char(1) NOT NULL DEFAULT '0';
COMMENT ON COLUMN organization.res_mgr IS '是否是资源管理者 0：不是  1:是';

--- modify begin at version 2.1

CREATE TABLE WEB_MSG_IDX (
	OWNER TEXT NOT NULL,
	ACTOR TEXT NOT NULL,
	LAST_TIME BIGINT NOT NULL,
	NUM_OF_OWNER INTEGER NOT NULL,
	NUM_OF_ACTOR INTEGER NOT NULL,
	LAST_CNT TEXT NOT NULL,
	SHOW_OF_OWNER CHAR(1) NOT NULL,
	SHOW_OF_ACTOR CHAR(1) NOT NULL);
ALTER TABLE WEB_MSG_IDX ADD PRIMARY KEY (OWNER,ACTOR);
CREATE TABLE WEB_MSG_CNT (CNT TEXT NOT NULL,SEND_TIME TEXT NOT NULL,SENDER TEXT NOT NULL,RECIVER TEXT NOT NULL,READED CHAR(1) NOT NULL);
ALTER TABLE WEB_MSG_CNT ADD PRIMARY KEY (SENDER,RECIVER,SEND_TIME);
COMMENT ON TABLE WEB_MSG_IDX is '消息会话表';
COMMENT ON COLUMN WEB_MSG_IDX.OWNER IS '会话所有人ID(两个人中ID小的为会话所有人)';
COMMENT ON COLUMN WEB_MSG_IDX.ACTOR IS '会话参与人ID(两个人中ID大的为会话参与人)';
COMMENT ON COLUMN WEB_MSG_IDX.LAST_TIME IS '会话最后活动时间';
COMMENT ON COLUMN WEB_MSG_IDX.NUM_OF_OWNER IS '会话所有人未读消息数';
COMMENT ON COLUMN WEB_MSG_IDX.NUM_OF_ACTOR IS '会话参与人未读消息数';
COMMENT ON COLUMN WEB_MSG_IDX.LAST_CNT IS '会话最后内容';
COMMENT ON COLUMN WEB_MSG_IDX.SHOW_OF_OWNER IS '是否存在与所有的人会话列表中';
COMMENT ON COLUMN WEB_MSG_IDX.SHOW_OF_ACTOR IS '是否存在与参与的人会话列表中';
COMMENT ON TABLE WEB_MSG_CNT IS '消息内容表';
COMMENT ON COLUMN WEB_MSG_CNT.SENDER is '消息发送人ID';
COMMENT ON COLUMN WEB_MSG_CNT.RECIVER is '消息接收人ID';
COMMENT ON COLUMN WEB_MSG_CNT.CNT is '消息内容';
COMMENT ON COLUMN WEB_MSG_CNT.SEND_TIME is '消息发送时间';
COMMENT ON COLUMN WEB_MSG_CNT.READED is '消息是否已读';


--modify begin at version2.20
CREATE TABLE LEAVE_MSG (ID CHAR(32) NOT NULL,CNT TEXT NOT NULL,REF_ID TEXT NOT NULL,REF_TYPE CHAR(1) NOT NULL,
TOP TEXT NOT NULL,PARENT TEXT,SENDER TEXT NOT NULL,RECIVER TEXT,STATE CHAR(1) NOT NULL,AGREE_COUNT BIGINT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL,MODIFY_TIME CHAR(14) NOT NULL,TIME_DESC TEXT NOT NULL);
ALTER TABLE LEAVE_MSG ADD PRIMARY KEY (ID);
COMMENT ON TABLE LEAVE_MSG is '留言表（新）';
COMMENT ON COLUMN LEAVE_MSG.ID is '留言ID';
COMMENT ON COLUMN LEAVE_MSG.CNT is '留言内容';
COMMENT ON COLUMN LEAVE_MSG.REF_ID is '留言对象ID';
COMMENT ON COLUMN LEAVE_MSG.REF_TYPE is '留言对象分类 1：文章 2：论文 3：专利 4:回答';
COMMENT ON COLUMN LEAVE_MSG.TOP is '顶级留言（只针对对象不针对留言）';
COMMENT ON COLUMN LEAVE_MSG.PARENT is '回复的留言ID';
COMMENT ON COLUMN LEAVE_MSG.SENDER is '留言人ID';
COMMENT ON COLUMN LEAVE_MSG.RECIVER is '被回复的留言人ID';
COMMENT ON COLUMN LEAVE_MSG.STATE is '状态 1：发布中';
COMMENT ON COLUMN LEAVE_MSG.AGREE_COUNT is '被点赞数量';

CREATE TABLE LEAVE_MSG_AGREE_REC (ID TEXT NOT NULL,UID TEXT NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE LEAVE_MSG_AGREE_REC ADD PRIMARY KEY (ID,UID);
COMMENT ON TABLE LEAVE_MSG_AGREE_REC is '留言点赞记录表';
COMMENT ON COLUMN LEAVE_MSG_AGREE_REC.ID is '留言ID';
COMMENT ON COLUMN LEAVE_MSG_AGREE_REC.UID is '点赞人ID';


CREATE TABLE QUESTION (ID CHAR(32) NOT NULL,TITLE TEXT NOT NULL,
CNT TEXT,IMG TEXT,KEYS TEXT NOT NULL,
UID TEXT NOT NULL,LAST_REPLY_TIME CHAR(14),PAGE_VIEWS BIGINT NOT NULL,
REPLY_COUNT BIGINT NOT NULL,STATE CHAR(1) NOT NULL,CREATE_TIME CHAR(14) NOT NULL,
TIME_DESC TEXT NOT NULL,MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE QUESTION ADD PRIMARY KEY (ID);
COMMENT ON TABLE QUESTION is '提问表';
COMMENT ON COLUMN QUESTION.ID is '提问ID';
COMMENT ON COLUMN QUESTION.TITLE is '提问标题';
COMMENT ON COLUMN QUESTION.CNT is '提问描述';
COMMENT ON COLUMN QUESTION.IMG is '提问图片（英文逗号分隔）';
COMMENT ON COLUMN QUESTION.KEYS is '提问关键词（英文逗号分隔）';
COMMENT ON COLUMN QUESTION.UID is '提问人ID';
COMMENT ON COLUMN QUESTION.LAST_REPLY_TIME is '最后回答时间';
COMMENT ON COLUMN QUESTION.REPLY_COUNT is '回答次数';
COMMENT ON COLUMN QUESTION.STATE is '状态 1：发布中';



CREATE TABLE ANSWER (ID CHAR(32) NOT NULL,QID TEXT NOT NULL,UID TEXT NOT NULL,STATE CHAR(1) NOT NULL,
CNT TEXT NOT NULL,AGREE BIGINT NOT NULL,BALLOT BIGINT NOT NULL,CREATE_TIME CHAR(14) NOT NULL,
TIME_DESC TEXT NOT NULL,MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE ANSWER ADD PRIMARY KEY (ID);
ALTER TABLE ANSWER ADD UNIQUE (QID,UID);
COMMENT ON TABLE ANSWER is '回答表';
COMMENT ON COLUMN ANSWER.ID is '回答ID';
COMMENT ON COLUMN ANSWER.QID is '提问ID';
COMMENT ON COLUMN ANSWER.CNT is '回答内容';
COMMENT ON COLUMN ANSWER.UID is '回答人ID';
COMMENT ON COLUMN ANSWER.AGREE is '赞同票数';
COMMENT ON COLUMN ANSWER.BALLOT is '总投票数';
COMMENT ON COLUMN ANSWER.STATE is '状态 1：发布中';



CREATE TABLE ANSWER_AGREE_REC (UID TEXT NOT NULL,AID TEXT NOT NULL,FLAG CHAR(1) NOT NULL);
ALTER TABLE ANSWER_AGREE_REC ADD PRIMARY KEY (UID,AID);
COMMENT ON TABLE ANSWER_AGREE_REC is '回答投票表';
COMMENT ON COLUMN ANSWER_AGREE_REC.AID is '回答ID';
COMMENT ON COLUMN ANSWER_AGREE_REC.UID is '投票人ID';
COMMENT ON COLUMN ANSWER_AGREE_REC.FLAG is '1：赞同票  0：反对票';

CREATE TABLE QET_KEY_WORD (ID TEXT NOT NULL,KW TEXT NOT NULL);
ALTER TABLE QET_KEY_WORD ADD PRIMARY KEY (ID,KW);

CREATE TABLE NOTIFY_MSG_IDX (RECIVER TEXT NOT NULL,LAST_CNT TEXT NOT NULL,LAST_TIME TEXT NOT NULL,UN_READ INTEGER NOT NULL);
ALTER TABLE NOTIFY_MSG_IDX ADD PRIMARY KEY (RECIVER);
COMMENT ON TABLE NOTIFY_MSG_IDX is '通知索引表';
COMMENT ON COLUMN NOTIFY_MSG_IDX.RECIVER is '接收人ID';
COMMENT ON COLUMN NOTIFY_MSG_IDX.LAST_CNT is '最后通知内容';
COMMENT ON COLUMN NOTIFY_MSG_IDX.LAST_TIME is '最后通知时间';
COMMENT ON COLUMN NOTIFY_MSG_IDX.UN_READ is '未读通知数';

CREATE TABLE NOTIFY_MSG_CNT (ID CHAR(32) NOT NULL,CNT TEXT NOT NULL,RECIVER TEXT NOT NULL,READED CHAR(1) NOT NULL,
PID TEXT NOT NULL,UID TEXT NOT NULL,CREATE_TIME TEXT NOT NULL,OP_TYPE INTEGER NOT NULL,TIME_DESC TEXT NOT NULL);
ALTER TABLE NOTIFY_MSG_CNT ADD PRIMARY KEY (ID);
COMMENT ON TABLE NOTIFY_MSG_CNT is '通知内容表';
COMMENT ON COLUMN NOTIFY_MSG_CNT.ID is '通知ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.CNT is '通知内容';
COMMENT ON COLUMN NOTIFY_MSG_CNT.RECIVER is '接收人ID';

COMMENT ON COLUMN NOTIFY_MSG_CNT.READED is '是否已读，1：已读  0:未读';
COMMENT ON COLUMN NOTIFY_MSG_CNT.PID is '参数ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.UID is '通知触发人ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.UID is '通知类型(详见文档)';

CREATE TABLE QUESTION_INVITE_REC (UID TEXT NOT NULL,QID TEXT NOT NULL,PID TEXT NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE QUESTION_INVITE_REC ADD PRIMARY KEY (UID,QID,PID);
COMMENT ON TABLE QUESTION_INVITE_REC is '邀请记录表';
COMMENT ON COLUMN QUESTION_INVITE_REC.UID is '邀请人ID';
COMMENT ON COLUMN QUESTION_INVITE_REC.PID is '被邀请人ID';
COMMENT ON COLUMN QUESTION_INVITE_REC.QID is '提问ID';

--modify begin at version2.30
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='3';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='4';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='25';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='26';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='27';
ALTER TABLE PROFESSOR ADD COLUMN SEX text, ADD COLUMN BIRTHDAY text;

create sequence ware_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;     
CREATE TABLE WARE (
	ID CHAR(32) NOT NULL,
	NAME TEXT NOT NULL,
	KEYWORDS TEXT,
	CNT TEXT,
	COOPERATION TEXT,
	DESCP TEXT,
	CATEGORY CHAR(1) NOT NULL,
	OWNER TEXT,
	IMAGES TEXT,
	STATE CHAR(1) NOT NULL,
	SHARE_ID INT8 DEFAULT nextval('ware_share_id_seq'::regclass) NOT NULL,
	PAGE_VIEWS INT8 NOT NULL, 
	SORT_FIRST INT8 NOT NULL,
	CREATE_TIME CHAR(14) NOT NULL,
	MODIFY_TIME CHAR(14) NOT NULL);
	
ALTER TABLE WARE ADD PRIMARY KEY (ID);

COMMENT ON TABLE WARE is '服务表';
COMMENT ON COLUMN WARE.NAME is '服务名称';
COMMENT ON COLUMN WARE.KEYWORDS is '服务关键词（逗号分隔）';
COMMENT ON COLUMN WARE.CNT is '服务内容';
COMMENT ON COLUMN WARE.COOPERATION is '合作备注';
COMMENT ON COLUMN WARE.DESCP is '详细描述';
COMMENT ON COLUMN WARE.CATEGORY is '类型：  1：个人   2：企业';
COMMENT ON COLUMN WARE.OWNER is '发布人ID   professorId  orgId';
COMMENT ON COLUMN WARE.IMAGES is '图片列表（逗号分隔）';
COMMENT ON COLUMN WARE.STATE is '状态 0：删除的  1： 发布中的，2：草稿状态的';
COMMENT ON COLUMN WARE.SHARE_ID is '分享ID';
COMMENT ON COLUMN WARE.CREATE_TIME is '创建时间';
COMMENT ON COLUMN WARE.MODIFY_TIME is '最后修改时间';

CREATE TABLE WRE_KEY_WORD(
	ID TEXT NOT NULL,
	KW TEXT NOT NULL
);
COMMENT ON TABLE WRE_KEY_WORD is '服务关键词';
CREATE TABLE WARE_PRO(
	ID TEXT NOT NULL,
	PROFESSOR TEXT NOT NULL
);
COMMENT ON TABLE WARE_PRO is '服务联系人表';
CREATE TABLE WARE_RES(
	ID TEXT NOT NULL,
	RESOURCE TEXT NOT NULL
);
COMMENT ON TABLE WARE is '服务关联资源表';
CREATE TABLE ARTICLE_WARE(
	ID TEXT NOT NULL,
	WARE TEXT NOT NULL
);
COMMENT ON TABLE WARE is '文章关联服务表';
ALTER TABLE PPATENT ADD COLUMN COOPERATION text;
COMMENT ON COLUMN PPATENT.COOPERATION is '合作备注';

--modify begin at version2.40
CREATE TABLE PLATFORM_USER (
	ID CHAR(32) NOT NULL,
	EMAIL TEXT NOT NULL,
	PHONE TEXT,
	PASSWD TEXT NOT NULL,
	STATE CHAR(1) NOT NULL,
	CREATE_TIME CHAR(14) NOT NULL,
	MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE PLATFORM_USER ADD PRIMARY KEY (ID);
ALTER TABLE PLATFORM_USER ADD UNIQUE (EMAIL);
ALTER TABLE PLATFORM_USER ADD UNIQUE (PHONE);

COMMENT ON TABLE PLATFORM_USER is '平台用户表';
COMMENT ON COLUMN PLATFORM_USER.EMAIL is '登录邮箱';
COMMENT ON COLUMN PLATFORM_USER.PHONE is '登录手机号（未户用）';
COMMENT ON COLUMN PLATFORM_USER.PASSWD is '登录密码';
COMMENT ON COLUMN PLATFORM_USER.PHONE is '状态 1：启用  0：未启用';

CREATE TABLE PLATFORM_INFO (
	ID CHAR(32) NOT NULL,
	NAME TEXT NOT NULL,
	LOGO TEXT,
	LINKMAN TEXT,
	LINKPHONE TEXT,
	LINKEMAIL TEXT,
	PROVINCE TEXT,
	CITY TEXT,
	ADDR TEXT,
	URL TEXT,
	INDUSTRY TEXT,
	DESCP TEXT,
	CREATE_TIME CHAR(14) NOT NULL,
	MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE PLATFORM_INFO ADD PRIMARY KEY (ID);
ALTER TABLE PLATFORM_INFO ADD UNIQUE (name);

COMMENT ON TABLE PLATFORM_INFO is '平台信息表';
COMMENT ON COLUMN PLATFORM_INFO.NAME is '平台名称';
COMMENT ON COLUMN PLATFORM_INFO.LOGO is '平台LOGO';
COMMENT ON COLUMN PLATFORM_INFO.LINKMAN is '联系人';
COMMENT ON COLUMN PLATFORM_INFO.LINKPHONE is '联系电话';
COMMENT ON COLUMN PLATFORM_INFO.LINKEMAIL is '联系邮箱';
COMMENT ON COLUMN PLATFORM_INFO.PROVINCE is '省';
COMMENT ON COLUMN PLATFORM_INFO.CITY is '市';
COMMENT ON COLUMN PLATFORM_INFO.ADDR is '地址';
COMMENT ON COLUMN PLATFORM_INFO.URL is '官网';
COMMENT ON COLUMN PLATFORM_INFO.INDUSTRY is '面向行业';
COMMENT ON COLUMN PLATFORM_INFO.DESCP is '平台介绍';


 CREATE TABLE REFERENCED_ARTICLE (
 	PID TEXT NOT NULL,
 	AID TEXT NOT NULL,
 	CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE REFERENCED_ARTICLE ADD PRIMARY KEY (PID,AID);
COMMENT ON TABLE REFERENCED_ARTICLE is '企业动态表';
COMMENT ON COLUMN REFERENCED_ARTICLE.PID is '平台ID';
COMMENT ON COLUMN REFERENCED_ARTICLE.AID is '文章ID';
COMMENT ON COLUMN REFERENCED_ARTICLE.CREATE_TIME is '关联时间';

 CREATE TABLE RESIDENT_ORG (
 	PID TEXT NOT NULL,
 	OID TEXT NOT NULL,
 	CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE RESIDENT_ORG ADD PRIMARY KEY (PID,OID);
COMMENT ON TABLE RESIDENT_ORG is '平台入驻企业表';
COMMENT ON COLUMN RESIDENT_ORG.PID is '平台ID';
COMMENT ON COLUMN RESIDENT_ORG.OID is '企业ID';
COMMENT ON COLUMN RESIDENT_ORG.CREATE_TIME is '入驻时间';


 CREATE TABLE BUTTED_ORG (
 	PID TEXT NOT NULL,
 	OID TEXT NOT NULL,
 	CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE BUTTED_ORG ADD PRIMARY KEY (PID,OID);
COMMENT ON TABLE BUTTED_ORG is '对接的平台机构表';
COMMENT ON COLUMN BUTTED_ORG.PID is '平台ID';
COMMENT ON COLUMN BUTTED_ORG.OID is '对接的机构ID';
COMMENT ON COLUMN BUTTED_ORG.CREATE_TIME is '对接时间';


 CREATE TABLE BUTTED_PROFESSOR (
 	PID TEXT NOT NULL,
 	UID TEXT NOT NULL,
 	CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE BUTTED_PROFESSOR ADD PRIMARY KEY (PID,UID);
COMMENT ON TABLE BUTTED_PROFESSOR is '对接的平台专家表';
COMMENT ON COLUMN BUTTED_PROFESSOR.PID is '平台ID';
COMMENT ON COLUMN BUTTED_PROFESSOR.UID is '对接的专家ID';
COMMENT ON COLUMN BUTTED_PROFESSOR.CREATE_TIME is '对接时间';

--ALTER TABLE RESOURCE ADD COLUMN CATEGORY TEXT DEFAULT '0';
--COMMENT ON COLUMN resource.CATEGORY IS '资源分类';
--ALTER TABLE RESOURCE ALTER COLUMN CATEGORY SET NOT NULL;

ALTER TABLE DEMAND ADD COLUMN ORG_NAME TEXT DEFAULT '1';
update DEMAND set ORG_NAME =(SELECT NAME FROM ORGANIZATION WHERE ID =  DEMAND.ORG_ID);
ALTER TABLE DEMAND DROP COLUMN ORG_ID;
ALTER TABLE DEMAND ALTER COLUMN ORG_NAME SET  NOT NULL;
ALTER TABLE DEMAND ADD COLUMN SOURCE TEXT DEFAULT 'ekexiuWeb';
ALTER TABLE DEMAND ALTER COLUMN SOURCE SET  NOT NULL;

ALTER TABLE public.article ADD owner_id CHAR(32) NULL;

UPDATE public.article set owner_id = professor_id WHERE article_type = '1'

UPDATE public.article SET owner_id = org_id WHERE article_type = '2'

ALTER TABLE public.article DROP professor_id;

ALTER TABLE public.article DROP org_id





