game-sdk/simplewind/extend/huoIdentify/update.sql

#########################################################################
##  玩家实名信息
#########################################################################
DROP TABLE IF EXISTS `h_identify_mem`;
CREATE TABLE `h_identify_mem` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家id',
    `real_name` varchar(128) NOT NULL DEFAULT '' COMMENT '真实姓名',
    `identify_type` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '身份证类型',
    `id_card` varchar(32) NOT NULL DEFAULT '' COMMENT '身份证号',
    `identify_from` varchar(16) NOT NULL DEFAULT '' COMMENT '支付宝 alipay 微信 weixin',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `im_mem_card_index` (`mem_id`,`id_card`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家实名认证信息表';

#########################################################################
##  身份证号在线时长和消费金额统计
#########################################################################
DROP TABLE IF EXISTS `h_identify_idot`;
CREATE TABLE `h_identify_idot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `id_card` varchar(32) NOT NULL DEFAULT '' COMMENT '证件号identify_card',
    `day_online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '今日在线时长s,每日重置',
    `week_online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '本周在线时长s,每周一重置',
    `month_online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '本月在线时长s,每月一日重置',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '总在线时长s,不重置',
    `day_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '今日消费金额,每日重置',
    `week_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '本周消费金额,每周一重置',
    `month_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '本月消费金额,每月一日重置',
    `sum_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '总消费金额,不重置',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `ii_card_index` (`id_card`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实名信息在线时长统计 idot:id_card_online_time';

DROP TABLE IF EXISTS `h_identify_day_idot`;
CREATE TABLE `h_identify_day_idot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `date` date NOT NULL COMMENT '日期',
    `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
    `id_card` varchar(32) NOT NULL DEFAULT '' COMMENT '证件号identify_card',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '在线时长',
    `sum_money` decimal(12,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '消费金额',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `idi_dmi_unique` (`date`,`mem_id`,`id_card`) USING BTREE,
    KEY `idi_card_index` (`id_card`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家实名每日在线时长统计 idot:id_card_online_time';


#########################################################################
##  设备号在线时长统计
#########################################################################
DROP TABLE IF EXISTS `h_identify_dot`;
CREATE TABLE `h_identify_dot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `device_id` varchar(64) DEFAULT '' COMMENT '设备ID android 为imei ios 为idfa h5 为 永久cookie标识 huoh5开头标识',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '在线时长s,下次允许访问时重置',
    `next_allow_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '下次允许访问时间',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `id_device_index` (`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备在线时长统计 dot:device_online_time';

DROP TABLE IF EXISTS `h_identify_day_dot`;
CREATE TABLE `h_identify_day_dot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `date` date NOT NULL COMMENT '日期',
    `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
    `device_id` varchar(64) DEFAULT '' COMMENT '设备ID android 为imei ios 为idfa h5 为 永久cookie标识 huoh5开头标识',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '在线时长',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `idd_dmd_unique` (`date`,`mem_id`,`device_id`) USING BTREE,
    KEY `idd_device_index` (`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家设备每日在线时长统计 dot:device_online_time';

#########################################################################
##  玩家在线时长统计
#########################################################################
DROP TABLE IF EXISTS `h_identify_mot`;
CREATE TABLE `h_identify_mot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '在线时长s,下次允许访问时重置',
    `next_allow_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '下次允许访问时间',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `im_mem_index` (`mem_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家在线时长统计 mot:mem_online_time';

DROP TABLE IF EXISTS `h_identify_day_mot`;
CREATE TABLE `h_identify_day_mot` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `date` date NOT NULL COMMENT '日期',
    `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
    `online_duration` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '在线时长',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `idm_dm_unique` (`date`,`mem_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家每日在线时长统计 mot:mem_online_time';

##################################################################################
## 节假日日期配置
##################################################################################
DROP TABLE IF EXISTS `h_identify_holiday_set`;
CREATE TABLE `h_identify_holiday_set` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `year` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '年份',
    `holiday` text NOT NULL COMMENT '节假日非周六日日期数组',
    `workday` text NOT NULL COMMENT '周六日调班的日期数组',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `hs_year_index` (`year`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='节假日配置';

###############################################################################
## 游戏表新增 是否实名认证字段 , 已有的不需要添加
###############################################################################
ALTER TABLE `h_game`
    ADD COLUMN `is_auth` TINYINT(2) NOT NULL DEFAULT 1 COMMENT '是否需要强制实名认证，1为 关闭实名认证，2为 强制实名认证  3  开启实名认证' after `is_bt`;

###############################################################################
## 渠道实名认证信息表
###############################################################################
CREATE TABLE `h_identify_agent` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
    `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '渠道id',
    `real_name` varchar(128) NOT NULL DEFAULT '' COMMENT '真实姓名',
    `identify_type` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '身份证类型',
    `id_card` varchar(32) NOT NULL DEFAULT '' COMMENT '身份证号',
    `identify_from` varchar(16) NOT NULL DEFAULT '' COMMENT '支付宝 alipay 微信 weixin',
    `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
    `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `im_agent_card_index` (`agent_id`,`id_card`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道实名认证信息表';