签到系统技术方案设计

-- 用户基本信息表
DROP TABLE IF EXISTS `userinfo`;

CREATE TABLE `userinfo` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `username` VARCHAR(50) NOT NULL COMMENT '用户名',
    `password` VARCHAR(60) NOT NULL COMMENT '用户密码(bcrypt 加密)',
    `email` VARCHAR(100) COMMENT '用户邮箱',
    `avatar` VARCHAR(256) COMMENT '用户头像',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_id` (`user_id`),
    UNIQUE KEY `uk_username` (`username`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户基本信息表';

-- 用户积分汇总表
DROP TABLE IF EXISTS `user_points`;

CREATE TABLE `user_points` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `points` BIGINT DEFAULT 0 COMMENT '当前可用积分',
    `points_total` BIGINT DEFAULT 0 COMMENT '累计获得积分',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户积分汇总表';

-- 用户积分交易明细表
DROP TABLE IF EXISTS `user_points_transactions`;
CREATE TABLE `user_points_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `points_change` BIGINT NOT NULL COMMENT '积分变动值 (正数为增加，负数为扣除)',
    `current_balance` BIGINT NOT NULL COMMENT '当前余额',
    `transaction_type` TINYINT NOT NULL COMMENT '交易类型(1:签到 2:连续签到 3:补签 4:每日任务 5:福利任务)',
    `description` VARCHAR(100) COMMENT '积分变动说明',
    `ext_json` VARCHAR(1024) NOT NULL DEFAULT '' COMMENT '扩展字段',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户积分明细表';

-- 月度奖励记录表
DROP TABLE IF EXISTS `user_monthly_bonus_log`;
CREATE TABLE `user_monthly_bonus_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `year_month` CHAR(6) NOT NULL COMMENT '年月（YYYYMM）',
    `bonus_type` TINYINT NOT NULL COMMENT '奖励类型（1:连续签到3天 2:连续签到7天 3:连续签到15天 4:月满签）',
    `description` VARCHAR(100) COMMENT '积分变动说明',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_yearmonth_type` (`user_id`, `year_month`, `bonus_type`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '用户月度连续签到奖励记录表';

-- 签到记录表
DROP TABLE IF EXISTS `user_checkin_records`;

CREATE TABLE `user_checkin_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '记录ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `checkin_date` DATE NOT NULL COMMENT '签到日期',
    `checkin_type` TINYINT NOT NULL DEFAULT 1 COMMENT '签到类型：1=正常签到，2=补签',
    `points_awarded_base` INT NOT NULL DEFAULT 1 COMMENT '获得积分',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_date` (`user_id`, `checkin_date`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_checkin_date` (`checkin_date`),
    KEY `idx_checkin_type` (`checkin_type`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '签到记录表';

偏移量:  0         8         16        24    ... 100
         ↓        ↓         ↓         ↓         ↓
字节:   [00000001][00000000][00000000] ... [00000100]
         ↑                                  ↑
      第 0 字节                           第 13 字节 (100/8=12)

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/redis/go-redis/v9"
)

// bitmap 示例代码

func main() {
	rdb := redis.NewClient(&redis.Options{
		Addr: "localhost:6379",
	})
	defer rdb.Close()

	ctx := context.Background()

	// bitmapDemo(ctx, rdb)
	bitFieldDemo(ctx, rdb)

}
func bitmapDemo(ctx context.Context, rdb *redis.Client) {

	key := "test:bitmap:hahaha"
	// setbit
	val1 := rdb.SetBit(ctx, key, 0, 1).Val() // 返回的是原来的值（setbit之前的值）
	fmt.Printf("setbit ret:%v\n", val1)

	// getbit
	val2 := rdb.GetBit(ctx, key, 0).Val()
	fmt.Printf("getbit ret:%v\n", val2)

	// bitcount
	val3 := rdb.BitCount(ctx, key, &redis.BitCount{Start: 0, End: -1}).Val()
	fmt.Printf("bitcount ret:%v\n", val3)

	// bitop
	key2 := "test:bitmap:hahaha2"
	rdb.Del(ctx, key2)
	rdb.SetBit(ctx, key2, 2, 1)
	// 1 0 0
	// 0 0 1
	key3 := "test:bitmap:hahaha3"
	val4 := rdb.BitOpAnd(ctx, key3, key, key2).Val() // 返回的是目标key3的字节数
	fmt.Printf("bitop ret:%v\n", val4)
	val5 := rdb.BitCount(ctx, key3, &redis.BitCount{Start: 0, End: -1}).Val()
	fmt.Printf("bitcount ret:%v\n", val5)

	// get
	// rdb.SetBit(ctx, key2, 0, 1)
	// rdb.SetBit(ctx, key2, 1, 1)
	// val6 := rdb.Get(ctx, key2).Val()  // bitmap 取出的是字符串
	val6 := rdb.BitCount(ctx, key2, &redis.BitCount{Start: 0, End: -1}).Val()
	fmt.Printf("get ret:%v\n", val6)

	// bitpos
	val7 := rdb.BitPos(ctx, key2, 1).Val()
	fmt.Printf("bitpos ret:%v\n", val7)

}

func bitFieldDemo(ctx context.Context, rdb *redis.Client) {
	key := "test:checkin:2026" // 存储2026年整年的打卡情况
	// 假设现在是 2026-05
	// 5.1 打卡
	// 1.1 0
	// 1.2 1
	// 1.3 2
	// ...
	// 12.31 365
	// 计算出5.1号是今年的第几天，索引位就是天数-1
	t, _ := time.Parse("2006-01-02", "2026-05-01")
	offset := t.YearDay() - 1                // 5.1
	offset52 := offset + 1                   // 5.2
	offset53 := offset + 2                   // 5.3
	rdb.SetBit(ctx, key, int64(offset52), 1) // 5.2打卡
	rdb.SetBit(ctx, key, int64(offset53), 1) // 5.3打卡

	// 查看5月的打卡情况
	// 从5.1 到 5.31的数据读取出来
	// 5.1 到 5.31 共多少天？
	// 用多大的整数能存下31天
	ret := rdb.BitField(ctx, key, "GET", "u31", offset, "GET", "u4", offset).Val()
	fmt.Printf("ret:%v\n", ret)
	fmt.Printf("bet:%031b\n", ret[0])
	fmt.Printf("bet:%04b\n", ret[1])
}

user:checkins:daily:userID:年份

user:checkins:retro:userID:月份