tyc-server/deploy/sql/promotion.sql

CREATE TABLE `admin_promotion_link` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  `del_state` tinyint NOT NULL DEFAULT '0',
  `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
  
  /* 业务字段开始 */
  `url` varchar(1024) NOT NULL COMMENT '推广链接URL',
  `name` varchar(100) NOT NULL COMMENT '推广链接名称',
  `admin_user_id` bigint NOT NULL DEFAULT '0' COMMENT '推广者账号ID',
  /* 业务字段结束 */
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_url` (`url`(255)) COMMENT '确保URL唯一',
  KEY `idx_name` (`name`) COMMENT '优化名称查询',
  KEY `idx_admin_user_id` (`admin_user_id`) COMMENT '优化推广者查询'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推广链接表';

-- 总统计表（缓存汇总数据，提升查询性能）
CREATE TABLE `admin_promotion_link_stats_total` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  `del_state` tinyint NOT NULL DEFAULT '0',
  `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
  
  /* 业务字段开始 */
  `link_id` bigint NOT NULL COMMENT '推广链接ID',
  `click_count` bigint NOT NULL DEFAULT '0' COMMENT '总点击数',
  `pay_count` bigint NOT NULL DEFAULT '0' COMMENT '总付费次数',
  `pay_amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '总付费金额',
  `last_click_time` datetime DEFAULT NULL COMMENT '最后点击时间',
  `last_pay_time` datetime DEFAULT NULL COMMENT '最后付费时间',
  /* 业务字段结束 */
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_link_id` (`link_id`) COMMENT '确保每个链接只有一条总统计记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推广链接总统计表（缓存汇总数据，提升查询性能）';

-- 历史统计表（按天统计，包含当天实时统计）
CREATE TABLE `admin_promotion_link_stats_history` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  `del_state` tinyint NOT NULL DEFAULT '0',
  `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
  
  /* 业务字段开始 */
  `link_id` bigint NOT NULL COMMENT '推广链接ID',
  `stats_date` date NOT NULL COMMENT '统计日期',
  `click_count` bigint NOT NULL DEFAULT '0' COMMENT '点击数',
  `pay_count` bigint NOT NULL DEFAULT '0' COMMENT '付费次数',
  `pay_amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '付费金额',
  `last_click_time` datetime DEFAULT NULL COMMENT '最后点击时间',
  `last_pay_time` datetime DEFAULT NULL COMMENT '最后付费时间',
  /* 业务字段结束 */
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_link_date` (`link_id`, `stats_date`) COMMENT '确保每个链接每天只有一条统计记录',
  KEY `idx_stats_date` (`stats_date`) COMMENT '优化日期查询',
  KEY `idx_link_id` (`link_id`) COMMENT '优化链接查询',
  KEY `idx_link_date` (`link_id`, `stats_date`) COMMENT '优化链接日期查询'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推广链接历史统计表';

-- 推广订单表
CREATE TABLE `admin_promotion_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  `del_state` tinyint NOT NULL DEFAULT '0',
  `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
  
  /* 业务字段开始 */
  `link_id` bigint NOT NULL COMMENT '推广链接ID',
  `order_id` bigint NOT NULL COMMENT '订单ID',
  `user_id` bigint NOT NULL COMMENT '下单用户ID',
  `admin_user_id` bigint NOT NULL COMMENT '推广者账号ID',
  /* 业务字段结束 */
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`) COMMENT '确保每个订单只有一条推广记录',
  KEY `idx_link_id` (`link_id`) COMMENT '优化链接查询',
  KEY `idx_user_id` (`user_id`) COMMENT '优化用户查询',
  KEY `idx_admin_user_id` (`admin_user_id`) COMMENT '优化推广者查询'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推广订单表';