type UserPoint struct {
    UserID    int64   `json:"user_id"`
    Points    int64   `json:"points"`     // 当前可用积分
    UpdatedAt int64   `json:"updated_at"` // 最后更新时间
}

type PointRecord struct {
    ID        int64  `json:"id"`
    UserID    int64  `json:"user_id"`
    Change    int64  `json:"change"`       // 积分变化值（正为增加，负为减少）
    Reason    string `json:"reason"`       // 变更原因，如签到、消费等
    CreatedAt int64  `json:"created_at"`
}

func (s *PointService) AddPoints(userID int64, change int64, reason string) error {
    if change == 0 {
        return nil
    }

    // 获取当前用户积分
    var userPoint UserPoint
    err := s.db.QueryRow("SELECT points FROM user_points WHERE user_id = ?", userID).
        Scan(&userPoint.Points)
    
    if err != nil && err != sql.ErrNoRows {
        return err
    }

    newPoints := userPoint.Points + change
    if newPoints < 0 {
        return errors.New("积分不足，无法扣除")
    }

    // 使用事务保证余额和记录的一致性
    tx, err := s.db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    stmt1, _ := tx.Prepare("INSERT OR REPLACE INTO user_points (user_id, points, updated_at) VALUES (?, ?, ?)")
    _, err = stmt1.Exec(userID, newPoints, time.Now().Unix())
    stmt1.Close()
    if err != nil {
        return err
    }

    stmt2, _ := tx.Prepare("INSERT INTO point_records (user_id, change, reason, created_at) VALUES (?, ?, ?, ?)")
    _, err = stmt2.Exec(userID, change, reason, time.Now().Unix())
    stmt2.Close()
    if err != nil {
        return err
    }

    return tx.Commit()
}

err := tx.QueryRow("SELECT points FROM user_points WHERE user_id = ? FOR UPDATE", userID).Scan(&current)

CREATE TABLE user_points (
    user_id INTEGER PRIMARY KEY,
    points INTEGER NOT NULL DEFAULT 0,
    updated_at INTEGER
);

CREATE TABLE point_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    change INTEGER NOT NULL,
    reason TEXT,
    created_at INTEGER
);

CREATE INDEX idx_records_user ON point_records(user_id);