brain-server/doc/sql/头脑王者数据库.sql

CREATE TABLE `conf_address` (
  `address_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '地址编号',
  `parent_address_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级地址编号',
  `level` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '地址层级,见AddressLevel',
  `name` VARCHAR(128) COMMENT '地址名称',
  `cn_name` VARCHAR(128) COMMENT '中文地址名称',
  	PRIMARY KEY (`address_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '地址表';

CREATE TABLE `conf_local_address` (
  `id` int(11) unsigned NOT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '地址名称',
  `parent_id` int(11) unsigned DEFAULT NULL COMMENT '父编号',
  `short_name` varchar(50) DEFAULT NULL COMMENT '简称',
  `level_type` tinyint(2) unsigned zerofill DEFAULT '00' COMMENT '级别',
  `zip_code` int(11) DEFAULT NULL COMMENT '区号',
  `merger_name` varchar(255) DEFAULT NULL COMMENT '完整地址',
  `lng` double DEFAULT NULL COMMENT '经度',
  `lat` double DEFAULT NULL COMMENT '纬度',
  `pinyin` varchar(255) DEFAULT NULL COMMENT '拼音',
  `sort_flag` int(11) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中国省市表';

CREATE TABLE `user` (
  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `phone` VARCHAR(11) COMMENT '手机号码',
  `password` VARCHAR(64) COMMENT '登陆密码',
  `country_id` INT(11) UNSIGNED COMMENT '国家编号',
  `prov_id` INT(11) UNSIGNED COMMENT '省份编号',
  `city_id` INT(11) UNSIGNED COMMENT '城市编号',
  `language` VARCHAR(8) COMMENT '语言',
  `nick_name` VARCHAR(64) COMMENT '昵称',
  `head_img` VARCHAR(256) COMMENT '头像',
  `gender` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别,见Gender',
  `openid` VARCHAR(64) COMMENT '微信openid',
  `unionid` VARCHAR(64) COMMENT '微信unionid',
  `recom_code` VARCHAR(64) COMMENT '邀请码',
  `reg_channel` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '注册渠道,见RegChannel',
  `recom_user_id` INT(11)  UNSIGNED COMMENT '邀请者编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

CREATE TABLE `user_ext` (
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `rank_dan` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '段位,见RankDan',
  `cell` INT(11) UNSIGNED NOT NULL DEFAULT 0  COMMENT '脑细胞值',
  `split_time` DATETIME DEFAULT NULL COMMENT '上次分裂时间',
  `award_fee` INT(11) UNSIGNED  NOT NULL DEFAULT 0 COMMENT '累计奖励金额',
  `pay_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计支付金额',
  `refund_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计退款金额',
  `game_room_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计有奖竞答次数',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  	PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户扩展表';

CREATE TABLE `user_login` (
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `key` VARCHAR(32) NOT NULL COMMENT '登陆key',
  `session_key` VARCHAR(64) COMMENT '微信session_key',
  `client` VARCHAR(64) NOT NULL COMMENT '客户端型号',
  `version` INT(11) UNSIGNED NOT NULL COMMENT '版本号',
  `last_ip` INT(11) UNSIGNED NOT NULL COMMENT '最后登陆IP',
  `last_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后登陆时间',
  	PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户登陆表';

CREATE TABLE `user_friend` (
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `user_friend_id` INT(11) UNSIGNED NOT NULL  COMMENT '好友编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`, `user_friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户好友关系表';

CREATE TABLE `user_share` (
  `share_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分享编号',
  `user_id` INT(11)  UNSIGNED NOT NULL COMMENT '用户编号',
  `type` TINYINT(4) UNSIGNED NOT NULL COMMENT '分享类型,见ShareType',
  `code` VARCHAR(32) COMMENT '分享标识',
  `visit_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '访问数',
  `game_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '关联比赛编号',
  `group_id` VARCHAR(64) COMMENT '群组编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`share_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COMMENT '用户分享登记表';

CREATE TABLE `user_cell_log` (
  `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志编号',
  `user_id` INT(11)  UNSIGNED NOT NULL COMMENT '用户编号',
  `cell` INT(11) NOT NULL  COMMENT '脑细胞值',
  `ref` TINYINT(4) UNSIGNED NOT NULL COMMENT '关联业务,见CellRef',
  `ref_id` VARCHAR(32) COMMENT '关联业务编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COMMENT '用户脑细胞日志表';

CREATE TABLE `sponsor` (
  `sponsor_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '赞助商编号',
  `name` VARCHAR(64) NOT NULL COMMENT '赞助商名称',
  `logo` VARCHAR(256) COMMENT '赞助商logo',
  `desc` VARCHAR(128) COMMENT '描述说明',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`sponsor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COMMENT '赞助商表';


CREATE TABLE `qa_question` (
  `question_id` INT(11)  UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '问题编号',
  `question` VARCHAR(256) NOT NULL COMMENT '问题描述',
  `a` VARCHAR(256) NOT NULL COMMENT '选项A',
  `b` VARCHAR(256) NOT NULL COMMENT '选项B',
  `c` VARCHAR(256) NOT NULL COMMENT '选项C',
  `d` VARCHAR(256) NOT NULL COMMENT '选项D',
  `correct` VARCHAR(8) NOT NULL COMMENT '正确选项',
  `tag` TINYINT(4) UNSIGNED COMMENT '标签',
  `ref` TINYINT(4) UNSIGNED NOT NULL COMMENT '题目来源,QaRef',
  `ref_id` INT(11)  UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源编号',
  `status` TINYINT(4) UNSIGNED NOT NULL COMMENT '题目状态,QaStatus',
  `status_desc` VARCHAR(64) DEFAULT NULL COMMENT '状态描述',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '题目表';

CREATE TABLE `game` (
  `game_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '游戏编号',
  `type` TINYINT(4) UNSIGNED NOT NULL COMMENT '游戏类型，见GameType',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001  DEFAULT CHARSET=utf8 COMMENT '游戏表';


CREATE TABLE `game_question` (
  `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
  `question_id` INT(11) UNSIGNED NOT NULL  COMMENT '问题编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`game_id`, `question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '游戏题目表';


CREATE TABLE `game_question_answer` (
 `answer_id` INT(11) UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT '回答编号',
 `user_id` INT(11) UNSIGNED NOT NULL  COMMENT '用户编号',
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号', 
 `question_id` INT(11) UNSIGNED NOT NULL  COMMENT '问题编号',
 `option` VARCHAR(8) NOT NULL  COMMENT '用户选项',
 `point_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '得分',
 `answer_time` DATETIME NOT NULL COMMENT '回答时间',
  PRIMARY KEY (`answer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '游戏题目回答表';

CREATE TABLE `game_user` (
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
 `user_id` INT(11) UNSIGNED NOT NULL  COMMENT '用户编号',
 `point_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '得分', 
 `rank` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '排名', 
 `award_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励金额', 
 `share_award_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '分享奖励金额', 
 `reg_channel` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '注册渠道,见RegChannel',
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`game_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '游戏用户表';

CREATE TABLE `game_room` (
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
 `title` VARCHAR(32) NOT NULL DEFAULT '趣味问答'  COMMENT '标题',
 `desc` VARCHAR(64)  COMMENT '描述说明',
 `logo` VARCHAR(256)  COMMENT 'logo',
 `ref` TINYINT(4) UNSIGNED NOT NULL COMMENT '赞助商类型',
 `ref_id` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '赞助商编号', 
 `rule_id` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '规则编号', 
 `max_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '最多参与人数', 
 `award_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '最多奖励人数',
 `award_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励金额',
 `question_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '题目数量',
 `count_down` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '房间倒计时',
 `question_count_down` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '问题倒计时',
 `begin_time` DATETIME COMMENT '开始时间', 
 `status` TINYINT(4) UNSIGNED NOT NULL COMMENT '房间状态，见RoomStatus', 
 `poundage_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '平台手续费',
 `real_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '真实用户数',
 `robot_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '机器人数',
 `award_real_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励真实用户数',
 `award_real_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励真实用户钱',
 `award_robot_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励机器人数',
 `award_robot_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励机器人钱',
 `share_award_real_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '分享用户数',
 `share_award_real_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '分享用户钱',
 `order_no` VARCHAR(32) COMMENT '付款订单号',
 `refund_no` VARCHAR(32) COMMENT '退款单号',
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '趣味问答游戏表';


CREATE TABLE `game_room_rule` (
 `rule_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  COMMENT '规则编号',
 `free_user` INT(11) UNSIGNED NOT NULL DEFAULT 1  COMMENT '空闲用户数',
 `robot_add_num` VARCHAR(64) NOT NULL COMMENT '首次机器人添加数量范围',
 `robot_add_second_1` VARCHAR(64) NOT NULL COMMENT '秒区间1',
 `robot_add_num_1` VARCHAR(64) NOT NULL COMMENT '秒区间1机器人添加数量范围',
 `robot_add_second_2` VARCHAR(64) NOT NULL COMMENT '秒区间2',
 `robot_add_num_2` VARCHAR(64) NOT NULL COMMENT '秒区间2机器人添加数量范围',
 `robot_answer_num_1` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '机器人回答1数量', 
 `robot_answer_cost_1` VARCHAR(64) NOT NULL COMMENT '机器人回答1耗时范围',
 `robot_answer_num_2` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '机器人回答2数量', 
 `robot_answer_cost_2` VARCHAR(64) NOT NULL COMMENT '机器人回答2耗时范围',
 `robot_answer_num_3` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '机器人回答3数量', 
 `robot_answer_cost_3` VARCHAR(64) NOT NULL COMMENT '机器人回答3耗时范围',
 `count_down` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '房间倒计时', 
 `max_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '最多参与人数', 
 `award_user` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '最多奖励人数',
 `award_fee` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '奖励金额',
 `question_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '题目数量',
 `question_count_down` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '问题倒计时',
 `award_rule` VARCHAR(256) NOT NULL COMMENT '奖励规则',
 `sponsor_id` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '赞助商编号', 
 `name` VARCHAR(64)  COMMENT '赞助商名称',
 `title` VARCHAR(32) NOT NULL DEFAULT '趣味问答'  COMMENT '标题',
 `logo` VARCHAR(256)  COMMENT 'logo',
 `room_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '总房间数量',
 `gen_room_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '已生成房间数量',
 `gen_room_time` DATETIME DEFAULT NULL COMMENT '最后生成房间时间', 
 `status` TINYINT(4) UNSIGNED NOT NULL  COMMENT '规则状态，见RuleStatus', 
 `status_time` DATETIME DEFAULT NULL COMMENT '状态变化时间', 
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '趣味问答游戏规则表';


CREATE TABLE `order` (
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `user_id` INT(11)  UNSIGNED NOT NULL COMMENT '用户编号',
  `product_type` TINYINT(4) UNSIGNED NOT NULL COMMENT '产品类型,见ProductType',
  `product_id` INT(11)  UNSIGNED COMMENT '产品编号',
  `order_fee` INT(11)  UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单金额',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `pay_channel` TINYINT(4) UNSIGNED COMMENT '支付渠道,PayChannel',
  `pay_status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '支付状态,PayStatus',
  `pay_no` VARCHAR(64) COMMENT '支付流水号',
  `pay_fee` INT(11)  UNSIGNED COMMENT '支付金额',
  `pay_time` DATETIME  COMMENT '支付时间',
  PRIMARY KEY (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '订单表';

CREATE TABLE `order_refund` (
  `refund_no` VARCHAR(32) NOT NULL COMMENT '退款编号',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `user_id` INT(11)  UNSIGNED NOT NULL COMMENT '用户编号',
  `total_fee` INT(11)  UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单金额',
  `refund_fee` INT(11)  UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款金额',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '退款状态,RefundStatus',
  `refund_id` VARCHAR(64) COMMENT '微信退款单号',
  `settlement_refund_fee` INT(11)  UNSIGNED COMMENT '退款金额',
  `success_time` DATETIME  COMMENT '退款时间',
  PRIMARY KEY (`refund_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '退款单表';


CREATE TABLE `wallet` (
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `total_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计获取金额',
  `apply_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请提现中金额',
  `finish_fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '已完成提现金额',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户钱包表';

CREATE TABLE `wallet_log` (
  `log_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志编号',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `fee` INT(11) NOT NULL DEFAULT 0 COMMENT '金额',
  `ref` TINYINT(4) UNSIGNED NOT NULL COMMENT '关联业务,见WalletRef',
  `ref_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT  '关联业务编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户钱包日志表';

CREATE TABLE `wallet_withdraw` (
  `withdraw_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '提现编号',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `fee` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '金额',
  `openid` VARCHAR(64) COMMENT '微信OpenID',
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '提现状态，见WithdrawStatus',
  `status_desc` VARCHAR(64) COMMENT '状态描述',
  `pay_time` DATETIME COMMENT '支付时间',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`withdraw_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001  DEFAULT CHARSET=utf8 COMMENT '用户提现表';


-- 系统管理员表
CREATE TABLE `sys_admin` (
  `adminid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
  `login_name` VARCHAR(32) NOT NULL COMMENT '登陆名',
  `psw` VARCHAR(64) NOT NULL COMMENT '登陆密码',
  `real_name` VARCHAR(32) COMMENT '真实姓名',
  `phone` VARCHAR(13) COMMENT '联系电话(故障后台发送短信通知相关人员)',
  `email` VARCHAR(64) COMMENT '联系邮箱(故障后台发送邮件通知相关人员)',
  `valid` tinyint(4) NOT NULL DEFAULT 1 COMMENT '生效标示 1-生效 2-失效',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近登陆时间',
  	PRIMARY KEY (`adminid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT '系统管理员表';

INSERT INTO `brain`.`sys_admin` (`adminid`, `login_name`, `psw`, `real_name`, `phone`, `email`, `valid`, `create_time`, `last_time`) VALUES ('1', 'admin', 'e10adc3949ba59abbe56e057f20f883e', NULL, NULL, NULL, '1', '2018-01-25 13:18:10', '2018-01-25 13:18:10');

-- 用户表openid添加唯一锁引
update `user` set `openid` = NULL where `reg_channel` = 99;
ALTER TABLE `user` ADD UNIQUE INDEX `idx_user_openid` (`openid`); 


-- 20180303 排位赛需求
-- add by chensenlai

ALTER TABLE `user_ext` DROP COLUMN `rank_dan`;
ALTER TABLE `user_ext` ADD COLUMN `game_pk_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计pk次数' AFTER `game_room_c`;
ALTER TABLE `user_ext` ADD COLUMN `game_rank_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计排位赛次数' AFTER `game_pk_c`;


CREATE TABLE `game_pk` (
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
 `parent_game_id` INT(11) UNSIGNED NOT NULL  COMMENT '父游戏编号(关联排位赛编号)',
 `question_c` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '题目数量',
 `question_count_down` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '答题时限',
 `cost` INT(11) UNSIGNED NOT NULL  DEFAULT 0 COMMENT '脑力值消耗',
 `user_id` INT(11) UNSIGNED NOT NULL COMMENT '发起者编号',
 `join_user_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '参与者编号',
 `type` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1  COMMENT 'PK类型，见PkType', 
 `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1  COMMENT 'PK状态，见PkStatus', 
 `result` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1  COMMENT 'PK结果，见PkResult', 
 `result_time` DATETIME DEFAULT NULL COMMENT '结果时间', 
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'PK赛游戏表';


CREATE TABLE `game_rank` (
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
 `name` VARCHAR(64) NOT NULL COMMENT '赞助商名称',
 `title` VARCHAR(64) NOT NULL COMMENT '排位赛名称',
 `desc` VARCHAR(128) NOT NULL COMMENT '排位赛描述',
 `logo` VARCHAR(256) NOT NULL COMMENT 'logo地址',
 `banner` VARCHAR(256) NOT NULL COMMENT 'banner图地址',
 `poster` VARCHAR(256) NOT NULL COMMENT '海报图地址',
 `begin_time` DATETIME NOT NULL COMMENT '开始时间', 
 `end_time` DATETIME NOT NULL COMMENT '截止时间', 
 `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1  COMMENT '排位赛状态，见RoomStatus', 
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '排位赛表';


CREATE TABLE `game_rank_user` (
 `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
 `user_id` INT(11) UNSIGNED NOT NULL  COMMENT '用户编号',
 `star_c` INT(11) UNSIGNED NOT NULL  COMMENT '星星数',
 `play_time` DATETIME NOT NULL COMMENT '参加时间', 
  PRIMARY KEY (`game_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '排位赛用户表';


CREATE TABLE `game_rank_group` (
  `group_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '组编号',
  `game_id` INT(11) UNSIGNED NOT NULL  COMMENT '游戏编号',
  `user_id` INT(11) UNSIGNED NOT NULL  COMMENT '用户编号',
  `group_code` VARCHAR(256) NOT NULL COMMENT '组标识',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  	PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8 COMMENT '排位赛组表';


CREATE TABLE `game_rank_group_user` (
 `group_id` INT(11) UNSIGNED NOT NULL  COMMENT '组编号',
 `user_id` INT(11) UNSIGNED NOT NULL  COMMENT '用户编号',
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`group_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '排位赛组用户表';

-- 提现补充真实姓名和身份证
ALTER TABLE `user` ADD COLUMN `real_name` VARCHAR(16) DEFAULT NULL COMMENT '真实姓名' AFTER `user_id`;
ALTER TABLE `user` ADD COLUMN `idcard` VARCHAR(32) DEFAULT NULL COMMENT '身份证' AFTER `real_name`;

ALTER TABLE `wallet_withdraw` ADD COLUMN `real_name` VARCHAR(16) DEFAULT NULL COMMENT '真实姓名' AFTER `openid`;
ALTER TABLE `wallet_withdraw` ADD COLUMN `idcard` VARCHAR(32) DEFAULT NULL COMMENT '身份证' AFTER `real_name`;


ALTER TABLE `game_rank` ADD COLUMN `pre_game_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上场排位赛编号' AFTER `game_id`;


-- 20180307 小程序客服消息配置
-- add by chensenlai
CREATE TABLE `kefu_message` (
 `message_id` INT(11) UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT '消息编号',
 `type`  TINYINT(4) UNSIGNED NOT NULL COMMENT '消息类型,见MessageType',
 `content` VARCHAR(1024) DEFAULT NULL COMMENT '内容',
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '后台消息配置';


-- 20180309 脑力值日志数据更新
-- add by chensenlai
UPDATE `user_cell_log` SET `ref`=51 WHERE `ref`=11;
UPDATE `user_cell_log` SET `ref`=52 WHERE `ref`=12;


-- 20180312 统一整改到参数配置表里面
-- add by chensenlai
DROP TABLE `kefu_message` ;

CREATE TABLE `conf_param` (
 `param_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '参数编号',
 `gcode` VARCHAR(64) NOT NULL COMMENT '标识码',
 `data` VARCHAR(1024) NOT NULL COMMENT '实体数据',
 `sort` INT(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '排序(从小到大)',
 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`param_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '参数配置表';


-- add by chensenlai 20180313
-- 脑力值产品可以配置
ALTER TABLE `user_ext` ADD COLUMN `ext` VARCHAR(1024)  DEFAULT NULL COMMENT '扩展数据' AFTER `game_rank_c`;
UPDATE `user_ext` SET `ext`='{"onceCellProductId":"1"}' WHERE `user_id` in (select `user_id` from `order` where product_type = 1 AND product_id  = 1 and pay_status = 3);


-- 模板提醒
CREATE TABLE `template_message` (
  `message_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '模板消息编号',
  `type` TINYINT(4) UNSIGNED NOT NULL COMMENT '模板类型',
  `form_id` VARCHAR(64) NOT NULL COMMENT '微信formId',
  `data` VARCHAR(64) DEFAULT NULL COMMENT '数据编号',
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '模板消息';


-- add by chensenlai 2018-03-22
-- 公众号h5页面支付，公众号openid关联小程序openid
CREATE TABLE `user_openid` (
  `openid` VARCHAR(64) NOT NULL COMMENT '小程序openid',
  `mp_openid` VARCHAR(64) NOT NULL COMMENT '公众号openid',
  	PRIMARY KEY (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户openid关联表';

-- 题目选题
ALTER TABLE `qa_question` ADD COLUMN `code` VARCHAR(32) DEFAULT NULL COMMENT '题目标识码' AFTER `correct`;
ALTER TABLE `qa_question` ADD COLUMN `degree` INT(11) DEFAULT 1 COMMENT '题目难度' AFTER `tag`;

-- 房间规则增加选题规则
ALTER TABLE `game_room_rule` ADD COLUMN `question_rule` VARCHAR(512) DEFAULT NULL COMMENT '选题规则' AFTER `question_count_down`;

-- 排位赛增加选题规则
ALTER TABLE `game_rank` ADD COLUMN `question_rule` VARCHAR(2048) DEFAULT NULL COMMENT '选题规则' AFTER `poster`;


-- add by chensenlai 20180325
-- 黑名单管控
CREATE TABLE `user_blacklist` (
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '用户编号',
  `level` INT(11) UNSIGNED NOT NULL DEFAULT 0  COMMENT '管控级别:999以上禁止登陆，0～998降分处理',
  `desc` VARCHAR(64) DEFAULT NULL COMMENT '说明',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  	PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户黑名单表';

-- 合并题目分类
update `qa_question` SET `tag`=18 WHERE `tag`=5;   -- 生物->健康
update `qa_question` SET `tag`=18 WHERE `tag`=19;  -- 医学->健康

update `qa_question` SET `tag`=7 WHERE `tag`=16;  -- 动漫->娱乐
update `qa_question` SET `tag`=7 WHERE `tag`=17;  -- 音乐->娱乐

update `qa_question` SET `tag`=9 WHERE `tag`=10;  -- 军事->常识
update `qa_question` SET `tag`=9 WHERE `tag`=4;  -- 历史->常识

-- 题目扩展表，记录题目统计信息等扩展数据
-- 应该叫stat_question更加合理，放在stat统计域单独维护，考虑和user_ext同一命名，故还是叫qa_question_ext
CREATE TABLE `qa_question_ext` (
  `question_id` INT(11) UNSIGNED NOT NULL COMMENT '题目编号',
  `correct_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '答对次数',
  `wrong_c` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '答错次数',
  	PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '题目扩展表';

-- 数据迁移
CREATE TABLE `game_user_backup` LIKE `game_user`;
CREATE TABLE `game_question_backup` LIKE `game_question`;
CREATE TABLE `game_question_answer_backup` LIKE `game_question_answer`;

-- add by chensenlai 20180330
-- 音乐配置
ALTER TABLE `game_rank` ADD COLUMN `audio_bg` VARCHAR(512) DEFAULT NULL COMMENT '背景音乐' AFTER `poster`;

ALTER TABLE `game_room_rule` ADD COLUMN `audio_wait` VARCHAR(512) DEFAULT NULL COMMENT '等待倒计时音乐' AFTER `logo`;
ALTER TABLE `game_room_rule` ADD COLUMN `audio_bg` VARCHAR(512) DEFAULT NULL COMMENT '背景音乐' AFTER `audio_wait`;

-- 用户出题（备选题库）
CREATE TABLE `qa_question_candidate` (
  `candidate_id` INT(11)  UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '候选问题编号',
  `question` VARCHAR(256) NOT NULL COMMENT '问题描述',
  `a` VARCHAR(256) NOT NULL COMMENT '选项A',
  `b` VARCHAR(256) NOT NULL COMMENT '选项B',
  `c` VARCHAR(256) NOT NULL COMMENT '选项C',
  `d` VARCHAR(256) NOT NULL COMMENT '选项D',
  `correct` VARCHAR(8) NOT NULL COMMENT '正确选项',
  `tag` TINYINT(4) UNSIGNED COMMENT '标签',
  `degree` INT(11) DEFAULT 11 COMMENT '题目难度',
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '题目状态',
  `adminid` INT(11)  UNSIGNED NOT NULL COMMENT '出题者编号',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`candidate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '候选题目表(用于出题同步正式表)';

-- 管理员增加角色
-- 暂时不设计复杂权限系统，够用就行
ALTER TABLE `sys_admin` ADD COLUMN `role` tinyint(4) UNSIGNED NOT NULL DEFAULT 2 COMMENT '角色' AFTER `email`;


-- 消息通知
CREATE TABLE `msg_notice` (
  `notice_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '通知编号',
  `user_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户编号-0表示全部用户',
  `title` VARCHAR(64) DEFAULT NULL COMMENT '通知标题',
  `cont` VARCHAR(1024) DEFAULT NULL COMMENT '通知内容',
  `status` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
  `begin_time` DATETIME NOT NULL COMMENT '开始时间',
  `end_time` DATETIME NOT NULL COMMENT '截止时间',
  `num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '已通知用户数量',
  PRIMARY KEY (`notice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT '消息通知';
ALTER TABLE `msg_notice` ADD INDEX `idx_user_id` (`user_id`); 

INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '官方公告', '多谢近期用户反馈，冲顶王者先后查封了听风、樱花等十余个开外挂账号。\n\n冲顶王者力争为大家塑造一个客观公正的答题环境，用脑力征服世界！\n\n如果大家还有发现外挂者，可关注[冲顶王者]公众号举报，举报还有奖励哦！',  '2018-03-30 03:00:00', '2018-04-02 23:59:59');
INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '提现说明', '近期用户提现较多。\n\n故冲顶王者再次提醒亲们，提现后请关注提现状态。\n\n状态为提现成功即可在 [微信-服务通知] 收到提现红包，需24小时内领取哦！',  '2018-04-03 06:00:00', '2018-04-04 23:59:59');
INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '新金主入驻', '冲顶王者首页休闲娱乐赛，再添金主赞助商！\n\n英语单词专场，仅限100场！\n\n比拼更垂直的专业能力，开战吧！',  '2018-04-09 03:00:00', '2018-04-09 23:59:59');
INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '赛季倒计时提醒', '冲顶王者第一赛季，将于4月15日23点59分结束！\n\n最后的时间，努力冲刺吧！\n',  '2018-04-14 03:00:00', '2018-04-15 22:59:59');
INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '赛季结束通知', '冲顶王者第一赛季，已于4月15日23点59分结束！\n\n奖励会在今日内发放完毕！\n\n新赛季也会在近日开启，敬请继续关注！\n',  '2018-04-16 03:00:00', '2018-04-16 16:59:59');
INSERT INTO `msg_notice` (`user_id`, `title`, `cont`, `begin_time`, `end_time`)  VALUES
(0, '赛季开始通知', '冲顶王者第二赛季，已于4月17日10点10分开始！\n\n奖励规则采纳了用户反馈的奖励规则！\n\n即高段位一起平分现金大奖！\n',  '2018-04-17 03:00:00', '2018-04-17 23:59:59');





-- 客服会话
CREATE TABLE `msg_message` (
  `message_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '客服素材编号',
  `type` TINYINT(4) UNSIGNED NOT NULL COMMENT '客服素材类型,见MessageType',
  `title` VARCHAR(64) DEFAULT NULL COMMENT '素材标题',
  `cont` VARCHAR(1024) DEFAULT NULL COMMENT '素材内容',
  `img` VARCHAR(256) DEFAULT NULL COMMENT '图片地址',
  `url` VARCHAR(512) DEFAULT NULL COMMENT '链接地址',
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `num` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '已通知用户数量',
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT '客服消息素材';



CREATE TABLE `msg_message_rule` (
  `rule_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '客服规则编号',
  `session_from` VARCHAR(32) NOT NULL COMMENT '微信sessionFrom',
  `desc` VARCHAR(64) NOT NULL COMMENT '会话说明',
  `event` VARCHAR(256) DEFAULT NULL COMMENT '收到事件消息回复素材配置',
  `text` VARCHAR(256) DEFAULT NULL COMMENT '收到文本消息回复素材配置',
  `image` VARCHAR(256) DEFAULT NULL COMMENT '收到图片消息回复素材配置',
  `miniprogrampage` VARCHAR(256) DEFAULT NULL COMMENT '收到小卡片消息回复素材配置',
  `client_title` VARCHAR(64) DEFAULT NULL COMMENT '客户端展示标题',
  `client_cont` VARCHAR(1024) DEFAULT NULL COMMENT '客户端展示内容',
  `client_btn` VARCHAR(64) DEFAULT NULL COMMENT '客户端按扭展示文字',
  `client_miniprogrampage` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '客户端小卡片素材编号',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '客服规则配置表';



-- 房间广告配置
ALTER TABLE `game_room_rule` ADD COLUMN `message_rule` INT(11) DEFAULT 0 COMMENT '客服会话规则' AFTER `audio_bg`;
-- 排位赛广告配置
ALTER TABLE `game_rank` ADD COLUMN `message_rule` INT(11) DEFAULT 0 COMMENT '客服会话规则' AFTER `question_rule`;


-- 娱乐赛房间脑力消耗配置
ALTER TABLE `game_room_rule` ADD COLUMN `cell` INT(11) NOT NULL DEFAULT -1  COMMENT '消耗脑细胞值 -1-按段位配置 0-不消耗脑力值' AFTER `award_rule`;


-- 模板消息表名更换
ALTER  TABLE `template_message` RENAME TO `msg_template`;