kknongchang_2506/app/Module/Point/Databases/GenerateSql/point_tables.sql

-- Point模块数据库表结构
-- 专注于整数型积分逻辑处理

-- 积分类型表
CREATE TABLE `kku_point_currency` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `identification` varchar(50) NOT NULL DEFAULT '' COMMENT '积分标识',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型，关联POINT_CURRENCY_TYPE枚举',
  `icon` varchar(100) NOT NULL DEFAULT '' COMMENT '积分图标',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '积分名称',
  `display_attributes` text COMMENT '显示属性，如图标、颜色等',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_identification` (`identification`),
  UNIQUE KEY `uk_type` (`type`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分类型表';

-- 积分配置表
CREATE TABLE `kku_point_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '积分名称',
  `currency_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联的积分类型ID，外键关联kku_point_currency表',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '积分账户类型，关联POINT_TYPE枚举',
  `display_attributes` text COMMENT '显示属性，如图标、颜色等',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_type` (`type`),
  KEY `idx_currency_id` (`currency_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分配置表';

-- 用户积分表
CREATE TABLE `kku_point` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `point_id` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型ID',
  `balance` bigint(20) NOT NULL DEFAULT '0' COMMENT '积分余额（整数）',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_point` (`user_id`, `point_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_point_id` (`point_id`),
  KEY `idx_balance` (`balance`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分表';

-- 积分日志表
CREATE TABLE `kku_point_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `point_id` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型ID',
  `amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作积分数量,正值为收入,负值为支出',
  `operate_id` varchar(100) NOT NULL DEFAULT '' COMMENT '上游操作ID',
  `operate_type` int(11) NOT NULL DEFAULT '0' COMMENT '上游操作类型',
  `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `create_ip` varchar(50) NOT NULL DEFAULT '' COMMENT '创建IP',
  `later_balance` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作后余额',
  `before_balance` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作前余额',
  `date_key` int(11) NOT NULL DEFAULT '0' COMMENT '日期key（用于分表）',
  `hash` varchar(100) NOT NULL DEFAULT '' COMMENT '防篡改哈希值',
  `prev_hash` varchar(100) NOT NULL DEFAULT '' COMMENT '上一条记录的哈希值',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_point_id` (`point_id`),
  KEY `idx_operate_type` (`operate_type`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_date_key` (`date_key`),
  KEY `idx_user_point_time` (`user_id`, `point_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分日志表';

-- 积分管理表
CREATE TABLE `kku_point_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `point_id` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型ID',
  `admin_id` int(11) NOT NULL DEFAULT '0' COMMENT '管理员ID',
  `total_points` bigint(20) NOT NULL DEFAULT '0' COMMENT '操作积分数量',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-待处理，1-已完成，2-已失败',
  `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_point_id` (`point_id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分管理表';

-- 积分流转记录表
CREATE TABLE `kku_point_circulation` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `from_point_id` int(11) NOT NULL DEFAULT '0' COMMENT '源积分类型ID',
  `to_point_id` int(11) NOT NULL DEFAULT '0' COMMENT '目标积分类型ID',
  `amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '流转积分数量',
  `re_id` int(11) NOT NULL DEFAULT '0' COMMENT '关联ID',
  `re_type` varchar(50) NOT NULL DEFAULT '' COMMENT '关联类型',
  `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-待处理，1-已完成，2-已失败',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_from_point_id` (`from_point_id`),
  KEY `idx_to_point_id` (`to_point_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_re_id_type` (`re_id`, `re_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分流转记录表';

-- 积分转账记录表
CREATE TABLE `kku_point_transfer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `from_user_id` int(11) NOT NULL DEFAULT '0' COMMENT '转出用户ID',
  `to_user_id` int(11) NOT NULL DEFAULT '0' COMMENT '转入用户ID',
  `point_id` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型ID',
  `amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '转账积分数量',
  `remark` varchar(500) NOT NULL DEFAULT '' COMMENT '备注',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '状态：0-待处理，1-已完成，2-已失败',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_from_user_id` (`from_user_id`),
  KEY `idx_to_user_id` (`to_user_id`),
  KEY `idx_point_id` (`point_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分转账记录表';

-- 积分订单表
CREATE TABLE `kku_point_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增',
  `order_no` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `point_id` int(11) NOT NULL DEFAULT '0' COMMENT '积分类型ID',
  `amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '积分数量',
  `order_type` varchar(50) NOT NULL DEFAULT '' COMMENT '订单类型',
  `title` varchar(200) NOT NULL DEFAULT '' COMMENT '订单标题',
  `description` varchar(500) NOT NULL DEFAULT '' COMMENT '订单描述',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `extra_data` text COMMENT '额外数据（JSON格式）',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_point_id` (`point_id`),
  KEY `idx_order_type` (`order_type`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分订单表';

-- 插入初始数据

-- 积分类型数据
INSERT INTO `kku_point_currency` (`id`, `identification`, `type`, `icon`, `name`, `display_attributes`, `create_time`, `update_time`) VALUES
(1, 'EXP', 1, '⭐', '经验积分', '{"icon":"⭐","color":"#1890ff","background":"#f0f9ff"}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(2, 'ACHIEVEMENT', 2, '🏆', '成就积分', '{"icon":"🏆","color":"#52c41a","background":"#f6ffed"}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(3, 'ACTIVITY', 3, '🎯', '活动积分', '{"icon":"🎯","color":"#fa8c16","background":"#fff7e6"}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(4, 'CHECKIN', 4, '📅', '签到积分', '{"icon":"📅","color":"#722ed1","background":"#f9f0ff"}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(5, 'REFERRAL', 5, '👥', '推荐积分', '{"icon":"👥","color":"#eb2f96","background":"#fff0f6"}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());

-- 积分配置数据
INSERT INTO `kku_point_config` (`id`, `name`, `currency_id`, `type`, `display_attributes`, `create_time`, `update_time`) VALUES
(1, '经验积分账户', 1, 1, '{"icon":"⭐","color":"#1890ff","show_in_list":true}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(2, '成就积分账户', 2, 2, '{"icon":"🏆","color":"#52c41a","show_in_list":true}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(3, '活动积分账户', 3, 3, '{"icon":"🎯","color":"#fa8c16","show_in_list":true}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(4, '签到积分账户', 4, 4, '{"icon":"📅","color":"#722ed1","show_in_list":true}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()),
(5, '推荐积分账户', 5, 5, '{"icon":"👥","color":"#eb2f96","show_in_list":true}', UNIX_TIMESTAMP(), UNIX_TIMESTAMP());