mojiahui/webman-buildadmin
1
1
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master-game-number
webman-buildadmin/database/dfw_36zihua_schema_v1.sql
zhenhui 66c14eacb3 数据库原版文件
2026-04-15 10:03:54 +08:00

412 lines
27 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- =====================================================================
-- 「36字花」数据库 DDL V1
-- 字符集：utf8mb4 | 引擎：InnoDB | 金额：DECIMAL(18,4)（游戏币）
-- 执行前请全库备份。若库中已存在同名列/表，请按需删减对应语句后执行。
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ---------------------------------------------------------------------
-- 一、新渠道表：channel（替代 game_channel）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `channel`;
CREATE TABLE `channel` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`top_admin_id` int(10) unsigned DEFAULT NULL COMMENT '顶级代理管理员ID（admin.id）',
`code` varchar(255) NOT NULL DEFAULT '' COMMENT '渠道标识',
`invite_code` varchar(64) DEFAULT NULL COMMENT '渠道主邀请码',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT '渠道名',
`agent_mode` varchar(32) NOT NULL DEFAULT 'turnover' COMMENT '代理模式：turnover=普通刷水代理 affiliate=联营代理',
`user_count` int(10) DEFAULT NULL COMMENT '用户数',
`profit_amount` decimal(18,4) DEFAULT NULL COMMENT '渠道当期利润快照（游戏币）',
`total_profit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '渠道累计总利润（游戏币）',
`commission_pool_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '可分红资金池（游戏币）',
`turnover_share_rate` decimal(9,6) DEFAULT NULL COMMENT '普通刷水代理分红比例（按流水分桶后再乘该比例）',
`affiliate_share_rate` decimal(9,6) DEFAULT NULL COMMENT '联营代理占成比例',
`affiliate_fee_rate` decimal(9,6) DEFAULT NULL COMMENT '联营代理成本扣除比例',
`carryover_balance` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '联营负结转余额（可负）',
`status` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT '状态:0=禁用,1=启用',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
`admin_id` int(10) unsigned DEFAULT NULL COMMENT '创建管理员',
`admin_group_id` int(10) unsigned DEFAULT NULL COMMENT '渠道角色组ID（admin_group.id）',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
UNIQUE KEY `uk_invite_code` (`invite_code`),
KEY `idx_agent_mode` (`agent_mode`),
KEY `idx_top_admin_id` (`top_admin_id`),
KEY `idx_admin_group_id` (`admin_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='渠道表（替代game_channel）';
-- 可选数据迁移（仅当历史存在 game_channel 且字段兼容时执行）
-- INSERT INTO `channel` (`id`,`code`,`name`,`user_count`,`profit_amount`,`status`,`remark`,`admin_id`,`admin_group_id`,`create_time`,`update_time`)
-- SELECT `id`,`code`,`name`,`user_count`,CAST(`profit_amount` AS DECIMAL(18,4)),`status`,`remark`,`admin_id`,`admin_group_id`,`create_time`,`update_time`
-- FROM `game_channel`;
-- ---------------------------------------------------------------------
-- 二、既有表适配：game_user（C 端用户）
-- ---------------------------------------------------------------------
ALTER TABLE `game_user`
MODIFY COLUMN `coin` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '游戏币余额',
ADD COLUMN `email` varchar(255) NOT NULL DEFAULT '' COMMENT '邮箱，可与手机号二选一注册' AFTER `phone`,
ADD COLUMN `channel_id` int(10) unsigned DEFAULT NULL COMMENT '所属渠道ID（channel.id）' AFTER `game_channel_id`,
ADD COLUMN `register_invite_code` varchar(64) NOT NULL DEFAULT '' COMMENT '注册时使用的邀请码快照' AFTER `channel_id`,
ADD COLUMN `total_deposit_coin` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '累计充值入账游戏币（提现流水校验分子/分母用）' AFTER `register_invite_code`,
ADD COLUMN `total_valid_bet_coin` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '累计有效投注游戏币' AFTER `total_deposit_coin`,
ADD COLUMN `risk_flags` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '风控位标记：可按位表示禁止登录/下注/提现等' AFTER `total_valid_bet_coin`,
ADD COLUMN `current_streak` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '当前连胜场次（DB兜底，权威状态可放Redis）' AFTER `risk_flags`,
ADD COLUMN `last_bet_period_no` varchar(64) NOT NULL DEFAULT '' COMMENT '上一笔有效下注所在期号，用于连胜连续期校验' AFTER `current_streak`;
ALTER TABLE `game_user`
ADD KEY `idx_channel_id` (`channel_id`),
ADD KEY `idx_register_invite_code` (`register_invite_code`),
ADD KEY `idx_last_bet_period_no` (`last_bet_period_no`);
-- ---------------------------------------------------------------------
-- 三、既有表适配：admin（子代理管理）
-- ---------------------------------------------------------------------
ALTER TABLE `admin`
ADD COLUMN `parent_admin_id` int(11) unsigned DEFAULT NULL COMMENT '上级代理管理员ID，顶级为空' AFTER `id`,
ADD COLUMN `channel_id` int(10) unsigned DEFAULT NULL COMMENT '所属渠道ID（channel.id）' AFTER `parent_admin_id`,
ADD COLUMN `invite_code` varchar(64) DEFAULT NULL COMMENT '子代理邀请码（用于发展玩家）' AFTER `mobile`,
ADD COLUMN `agent_role` varchar(32) NOT NULL DEFAULT 'sub_agent' COMMENT 'agent_admin|sub_agent|staff（均无开奖权限）' AFTER `status`;
ALTER TABLE `admin`
ADD UNIQUE KEY `uk_invite_code` (`invite_code`),
ADD KEY `idx_parent_admin_id` (`parent_admin_id`),
ADD KEY `idx_channel_id` (`channel_id`);
-- ---------------------------------------------------------------------
-- 四、既有表适配：admin_group（角色组按渠道隔离）
-- ---------------------------------------------------------------------
ALTER TABLE `admin_group`
ADD COLUMN `channel_id` int(10) unsigned DEFAULT NULL COMMENT '所属渠道ID（channel.id），NULL表示系统级角色组' AFTER `pid`,
ADD COLUMN `commission_rate` decimal(6,2) NOT NULL DEFAULT 0.00 COMMENT '角色组分红比例（百分比）' AFTER `channel_id`;
ALTER TABLE `admin_group`
ADD KEY `idx_channel_id` (`channel_id`);
-- ---------------------------------------------------------------------
-- 五、系统参数（KV，禁止业务写死）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_config`;
CREATE TABLE `game_config` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`config_key` varchar(64) NOT NULL DEFAULT '' COMMENT '参数键，全局唯一',
`config_value` text COMMENT '参数值，可为JSON',
`value_type` varchar(16) NOT NULL DEFAULT 'string' COMMENT 'string|int|decimal|json',
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '说明',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_config_key` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='游戏/平台动态参数';
-- ---------------------------------------------------------------------
-- 四、期号与状态机
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_period`;
CREATE TABLE `game_period` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`period_no` varchar(64) NOT NULL DEFAULT '' COMMENT '业务期号，唯一，如 20260414-123045',
`period_start_at` bigint(16) unsigned NOT NULL DEFAULT 0 COMMENT '本期开始时间戳',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=下注开放 1=已封盘 2=算票中 3=派彩中 4=已结束 5=已作废',
`draw_mode` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=自动AI 1=手动预设',
`preset_number` tinyint(3) unsigned DEFAULT NULL COMMENT '手动模式预设开奖号码 1-36',
`result_number` tinyint(3) unsigned DEFAULT NULL COMMENT '实际开奖号码 1-36',
`void_reason` varchar(255) NOT NULL DEFAULT '' COMMENT '作废原因',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_period_no` (`period_no`),
KEY `idx_status_start` (`status`,`period_start_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='游戏期号与状态';
-- ---------------------------------------------------------------------
-- 五、注单
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_bet_order`;
CREATE TABLE `game_bet_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`period_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '关联 game_period.id',
`period_no` varchar(64) NOT NULL DEFAULT '' COMMENT '冗余期号，便于对账与查询',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`channel_id` int(10) unsigned DEFAULT NULL COMMENT '下单时归属渠道',
`pick_numbers` json DEFAULT NULL COMMENT '选中号码 JSON 数组，如 [1,8,16]',
`unit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '单号金额（各号一致）',
`pick_count` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '选中号码个数',
`total_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '本单总金额',
`streak_at_bet` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '下注时连胜次数（用于派彩倍率）',
`is_auto` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=手动 1=托管',
`win_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '常规派彩金额（含连赢倍率后）',
`jackpot_extra_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT 'Jackpot 额外派彩',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '1=已扣款待开奖 2=已结算 3=已作废已退款',
`idempotency_key` varchar(64) DEFAULT NULL COMMENT '幂等键，防重复下单；未使用则为NULL（允许多条NULL）',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_idempotency` (`idempotency_key`),
KEY `idx_period_user` (`period_id`,`user_id`),
KEY `idx_user_ctime` (`user_id`,`create_time`),
KEY `idx_period_no` (`period_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='游戏注单';
-- ---------------------------------------------------------------------
-- 六、自动托管
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_bet_auto`;
CREATE TABLE `game_bet_auto` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`remaining_rounds` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '剩余托管局数',
`pick_numbers` json DEFAULT NULL COMMENT '选号 JSON',
`unit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '单号金额',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=停止 1=运行中',
`next_trigger_before` bigint(16) unsigned NOT NULL DEFAULT 0 COMMENT '下次允许自动下注前的时间戳上界（配合0-10秒抖动）',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_user_status` (`user_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='自动托管任务';
-- ---------------------------------------------------------------------
-- 七、用户钱包流水账
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `user_wallet_ledger`;
CREATE TABLE `user_wallet_ledger` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`biz_type` varchar(32) NOT NULL DEFAULT '' COMMENT '业务类型：deposit|bet|payout|withdraw_freeze|withdraw|fee|manual|void_refund 等',
`direction` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '1=入金 2=出金',
`amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '变动额，恒正',
`balance_after` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '变动后余额',
`ref_type` varchar(32) NOT NULL DEFAULT '' COMMENT '关联类型：period|bet_order|deposit_order|withdraw_order',
`ref_id` bigint(20) unsigned DEFAULT NULL COMMENT '关联主键',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_ctime` (`user_id`,`create_time`),
KEY `idx_ref` (`ref_type`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户游戏币账本流水';
-- ---------------------------------------------------------------------
-- 八、充值订单
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `deposit_order`;
CREATE TABLE `deposit_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`order_no` varchar(64) NOT NULL DEFAULT '' COMMENT '内部订单号',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`fiat_currency` varchar(16) NOT NULL DEFAULT '' COMMENT '法币币种代码',
`fiat_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '法币金额',
`fx_rate` decimal(18,8) NOT NULL DEFAULT 0.00000000 COMMENT '成交汇率',
`coin_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '入账游戏币',
`gateway` varchar(32) NOT NULL DEFAULT '' COMMENT '支付网关标识',
`gateway_trade_no` varchar(128) NOT NULL DEFAULT '' COMMENT '第三方流水号',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=待支付 1=成功 2=失败 3=关闭',
`paid_at` bigint(16) unsigned DEFAULT NULL COMMENT '支付成功时间',
`notify_payload` mediumtext COMMENT '回调原文存档',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_ctime` (`user_id`,`create_time`),
KEY `idx_gateway_trade` (`gateway`,`gateway_trade_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='充值订单';
-- ---------------------------------------------------------------------
-- 九、提现订单
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `withdraw_order`;
CREATE TABLE `withdraw_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`order_no` varchar(64) NOT NULL DEFAULT '' COMMENT '内部订单号',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`apply_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '申请提现游戏币',
`fee_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '手续费游戏币',
`actual_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '应出账游戏币（扣费后）',
`fiat_currency` varchar(16) NOT NULL DEFAULT '' COMMENT '出款法币币种',
`need_audit` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=免审 1=人工审核',
`audit_status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=待审 1=通过 2=驳回',
`from_jackpot` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=否 1=Jackpot相关拦截',
`reject_reason` varchar(500) NOT NULL DEFAULT '' COMMENT '驳回原因',
`paid_at` bigint(16) unsigned DEFAULT NULL COMMENT '出款完成时间',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_ctime` (`user_id`,`create_time`),
KEY `idx_audit` (`need_audit`,`audit_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='提现订单';
-- ---------------------------------------------------------------------
-- 十、代理钱包（每渠道/代理线一条）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_agent_wallet`;
CREATE TABLE `game_agent_wallet` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`channel_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'channel.id，一对一',
`balance` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '可用佣金余额',
`frozen` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '冻结',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_channel` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='代理佣金钱包';
-- ---------------------------------------------------------------------
-- 十一、代理钱包流水
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `game_agent_wallet_ledger`;
CREATE TABLE `game_agent_wallet_ledger` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`channel_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'channel.id',
`biz_type` varchar(32) NOT NULL DEFAULT '' COMMENT 'commission_payout|withdraw|manual 等',
`direction` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '1=入金 2=出金',
`amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '变动额，恒正',
`balance_after` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '变动后余额',
`ref_type` varchar(32) NOT NULL DEFAULT '' COMMENT '关联类型',
`ref_id` bigint(20) unsigned DEFAULT NULL COMMENT '关联主键',
`remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_channel_ctime` (`channel_id`,`create_time`),
KEY `idx_ref` (`ref_type`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='代理钱包流水';
-- ---------------------------------------------------------------------
-- 十二、代理结算周期（大盘快照）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `agent_settlement_period`;
CREATE TABLE `agent_settlement_period` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`period_type` varchar(16) NOT NULL DEFAULT '' COMMENT 'daily|weekly|monthly',
`period_key` varchar(32) NOT NULL DEFAULT '' COMMENT '周期唯一键，如 2026-W16',
`platform_ggr` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '平台当期总客损/毛利（可负）',
`platform_turnover` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '全网有效投注流水',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=未结算 1=已计算 2=已发放',
`start_at` bigint(16) unsigned NOT NULL DEFAULT 0 COMMENT '周期开始时间戳',
`end_at` bigint(16) unsigned NOT NULL DEFAULT 0 COMMENT '周期结束时间戳',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_period` (`period_type`,`period_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='代理结算周期';
-- ---------------------------------------------------------------------
-- 十三、代理佣金明细（级差按行记录）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `agent_commission_record`;
CREATE TABLE `agent_commission_record` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`settlement_period_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'agent_settlement_period.id',
`channel_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '获得佣金的渠道',
`commission_mode` varchar(16) NOT NULL DEFAULT 'turnover' COMMENT 'turnover=流水占比 affiliate=联营',
`line_turnover` decimal(18,4) DEFAULT NULL COMMENT '该线当期流水（turnover 模式用）',
`line_turnover_ratio` decimal(18,8) DEFAULT NULL COMMENT '占全网比例',
`pool_amount` decimal(18,4) DEFAULT NULL COMMENT '该线分桶基数',
`rate_snapshot` decimal(9,6) DEFAULT NULL COMMENT '当期有效拨比/级差快照',
`commission_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '本笔应发佣金',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=待入账 1=已入账',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_period_channel` (`settlement_period_id`,`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='代理佣金明细';
-- ---------------------------------------------------------------------
-- 十四、联营契约
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `affiliate_contract`;
CREATE TABLE `affiliate_contract` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`channel_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '联营代理 channel.id',
`title` varchar(128) NOT NULL DEFAULT '' COMMENT '契约标题',
`share_rate` decimal(9,6) NOT NULL DEFAULT 0.000000 COMMENT '客损占成比例',
`admin_fee_rate` decimal(9,6) NOT NULL DEFAULT 0.000000 COMMENT '平台双降费等成本比例快照',
`tier_json` json DEFAULT NULL COMMENT '阶梯条件 JSON，可选',
`effective_from` bigint(16) unsigned NOT NULL DEFAULT 0 COMMENT '生效时间',
`effective_to` bigint(16) unsigned DEFAULT NULL COMMENT '失效时间，空为长期',
`status` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '0=停用 1=有效',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_channel_time` (`channel_id`,`effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='联营契约';
-- ---------------------------------------------------------------------
-- 十五、联营负结转（按代理线 + 周期）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `affiliate_carryover`;
CREATE TABLE `affiliate_carryover` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`settlement_period_id` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'agent_settlement_period.id',
`channel_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '联营代理线',
`opening_carryover` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '期初结转（常为负）',
`period_net_ggr` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '当期辖区净客损',
`period_net_after_fee` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '扣费后净利润基数',
`commission_amount` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '本期应发联营佣金',
`closing_carryover` decimal(18,4) NOT NULL DEFAULT 0.0000 COMMENT '期末结转至下期',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_period_channel` (`settlement_period_id`,`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='联营负结转';
-- ---------------------------------------------------------------------
-- 十六、运营公告
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `operation_notice`;
CREATE TABLE `operation_notice` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
`content` mediumtext COMMENT '正文，可富文本',
`notice_type` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=静默信箱 1=强弹窗',
`status` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0=草稿 1=发布',
`publish_at` bigint(16) unsigned DEFAULT NULL COMMENT '发布时间',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
`update_time` bigint(16) unsigned DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_type_status` (`notice_type`,`status`,`publish_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='运营公告';
-- ---------------------------------------------------------------------
-- 十七、用户公告已读/确认
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `user_notice_read`;
CREATE TABLE `user_notice_read` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`notice_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'operation_notice.id',
`read_at` bigint(16) unsigned DEFAULT NULL COMMENT '阅读时间',
`confirmed` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '强弹窗是否已勾选确认 0/1',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_notice` (`user_id`,`notice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户公告阅读与确认';
-- ---------------------------------------------------------------------
-- 十八、站内信（与公告分离的个性化通知）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `user_site_message`;
CREATE TABLE `user_site_message` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'game_user.id',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
`body` mediumtext COMMENT '内容',
`category` varchar(32) NOT NULL DEFAULT 'system' COMMENT '分类',
`read_at` bigint(16) unsigned DEFAULT NULL COMMENT '已读时间，空未读',
`create_time` bigint(16) unsigned DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_read` (`user_id`,`read_at`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户站内信';
SET FOREIGN_KEY_CHECKS = 1;
-- =====================================================================
-- 可选：game_config 初始键（按需导入，值由运营最终确定）
-- INSERT INTO game_config (config_key, config_value, value_type, remark, create_time, update_time) VALUES
-- ('period_seconds','30','int','单局总时长秒', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
-- ('bet_seconds','20','int','下注开放秒数', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
-- =====================================================================
Reference in New Issue View Git Blame Copy Permalink