新功能(#73): 新增促销系统数据库迁移

Co-authored-by: multica-agent <github@multica.ai>

apis/admin/user.api

@@ -149,7 +149,7 @@ type (
 		ExpiredAt       int64   `json:"expired_at"`
 		Upload          int64   `json:"upload"`
 		Download        int64   `json:"download"`
-		SpeedLimit      *int64  `json:"speed_limit,omitempty" validate:"omitempty,gte=0"`
+		SpeedLimit      *int64  `json:"speed_limit,omitempty"`
 		TrafficLimit    *string `json:"traffic_limit,omitempty"`
 	}
 	GetUserLoginLogsRequest {

initialize/migrate/database/02154_promo_system.down.sql

@@ -0,0 +1,41 @@
+-- Purpose: Rollback promo system tables and order promo snapshot columns
+
+SET @column_exists = (
+  SELECT COUNT(*)
+  FROM INFORMATION_SCHEMA.COLUMNS
+  WHERE TABLE_SCHEMA = DATABASE()
+    AND TABLE_NAME = 'order'
+    AND COLUMN_NAME = 'promo_discount'
+);
+
+SET @sql = IF(
+  @column_exists = 1,
+  'ALTER TABLE `order` DROP COLUMN `promo_discount`',
+  'SELECT ''Column promo_discount does not exist in order table'''
+);
+
+PREPARE stmt FROM @sql;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @column_exists = (
+  SELECT COUNT(*)
+  FROM INFORMATION_SCHEMA.COLUMNS
+  WHERE TABLE_SCHEMA = DATABASE()
+    AND TABLE_NAME = 'order'
+    AND COLUMN_NAME = 'promo_rule_id'
+);
+
+SET @sql = IF(
+  @column_exists = 1,
+  'ALTER TABLE `order` DROP COLUMN `promo_rule_id`',
+  'SELECT ''Column promo_rule_id does not exist in order table'''
+);
+
+PREPARE stmt FROM @sql;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+DROP TABLE IF EXISTS `promo_usage`;
+DROP TABLE IF EXISTS `subscribe_promo`;
+DROP TABLE IF EXISTS `promo_rule`;

initialize/migrate/database/02154_promo_system.up.sql

@@ -0,0 +1,78 @@
+-- Purpose: Add promo system tables and order promo snapshot columns
+
+CREATE TABLE IF NOT EXISTS `promo_rule` (
+  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规则名称，如"新客7天优惠"',
+  `type` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '规则类型：new_user / inactive_user / campaign',
+  `params` JSON NOT NULL COMMENT '类型专属参数',
+  `priority` INT NOT NULL DEFAULT 0 COMMENT '优先级，数值越大越优先匹配',
+  `enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
+  `start_time` DATETIME DEFAULT NULL COMMENT '生效开始时间，NULL=立即生效',
+  `end_time` DATETIME DEFAULT NULL COMMENT '生效结束时间，NULL=永不过期',
+  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `deleted_at` DATETIME DEFAULT NULL COMMENT '软删除时间',
+  PRIMARY KEY (`id`),
+  KEY `idx_enabled_priority` (`enabled`, `priority` DESC)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='促销规则表';
+
+CREATE TABLE IF NOT EXISTS `subscribe_promo` (
+  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `subscribe_id` BIGINT UNSIGNED NOT NULL COMMENT '套餐规格 ID',
+  `promo_rule_id` BIGINT UNSIGNED NOT NULL COMMENT '促销规则 ID',
+  `promo_price` BIGINT NOT NULL DEFAULT 0 COMMENT '该规格在此规则下的优惠价（分）',
+  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `uk_subscribe_rule` (`subscribe_id`, `promo_rule_id`),
+  KEY `idx_promo_rule_id` (`promo_rule_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='规格促销价表';
+
+CREATE TABLE IF NOT EXISTS `promo_usage` (
+  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
+  `promo_rule_id` BIGINT UNSIGNED NOT NULL COMMENT '使用的规则 ID',
+  `subscribe_id` BIGINT UNSIGNED NOT NULL COMMENT '购买的规格 ID',
+  `order_no` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '关联订单号',
+  `promo_price` BIGINT NOT NULL DEFAULT 0 COMMENT '使用时的促销单价（分）',
+  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (`id`),
+  KEY `idx_user_rule` (`user_id`, `promo_rule_id`),
+  KEY `idx_order_no` (`order_no`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='促销使用记录表';
+
+SET @column_exists = (
+  SELECT COUNT(*)
+  FROM INFORMATION_SCHEMA.COLUMNS
+  WHERE TABLE_SCHEMA = DATABASE()
+    AND TABLE_NAME = 'order'
+    AND COLUMN_NAME = 'promo_rule_id'
+);
+
+SET @sql = IF(
+  @column_exists = 0,
+  'ALTER TABLE `order` ADD COLUMN `promo_rule_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ''促销规则ID, 0=未使用促销''',
+  'SELECT ''Column promo_rule_id already exists in order table'''
+);
+
+PREPARE stmt FROM @sql;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @column_exists = (
+  SELECT COUNT(*)
+  FROM INFORMATION_SCHEMA.COLUMNS
+  WHERE TABLE_SCHEMA = DATABASE()
+    AND TABLE_NAME = 'order'
+    AND COLUMN_NAME = 'promo_discount'
+);
+
+SET @sql = IF(
+  @column_exists = 0,
+  'ALTER TABLE `order` ADD COLUMN `promo_discount` BIGINT NOT NULL DEFAULT 0 COMMENT ''促销优惠金额(分)''',
+  'SELECT ''Column promo_discount already exists in order table'''
+);
+
+PREPARE stmt FROM @sql;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;

internal/handler/admin/user/updateUserSubscribeHandler.go

@@ -1,9 +1,6 @@
 package user
 
 import (
-	"encoding/json"
-	"errors"
-
 	"github.com/gin-gonic/gin"
 	"github.com/perfect-panel/server/internal/logic/admin/user"
 	"github.com/perfect-panel/server/internal/svc"
@@ -21,25 +18,9 @@ func UpdateUserSubscribeHandler(svcCtx *svc.ServiceContext) func(c *gin.Context)
 			result.ParamErrorResult(c, validateErr)
 			return
 		}
-		if err := validateUpdateUserSubscribeTrafficLimit(&req); err != nil {
-			result.ParamErrorResult(c, err)
-			return
-		}
 
 		l := user.NewUpdateUserSubscribeLogic(c.Request.Context(), svcCtx)
 		err := l.UpdateUserSubscribe(&req)
 		result.HttpResult(c, nil, err)
 	}
 }
-
-func validateUpdateUserSubscribeTrafficLimit(req *types.UpdateUserSubscribeRequest) error {
-	if req.TrafficLimit == nil || *req.TrafficLimit == "" {
-		return nil
-	}
-
-	var rules []types.TrafficLimit
-	if err := json.Unmarshal([]byte(*req.TrafficLimit), &rules); err != nil {
-		return errors.New("traffic_limit must be a valid JSON array")
-	}
-	return nil
-}

internal/handler/admin/user/updateUserSubscribeHandler_test.go

@@ -1,59 +0,0 @@
-package user
-
-import (
-	"bytes"
-	"encoding/json"
-	"net/http"
-	"net/http/httptest"
-	"testing"
-
-	"github.com/gin-gonic/gin"
-	"github.com/perfect-panel/server/internal/svc"
-	"github.com/perfect-panel/server/pkg/xerr"
-)
-
-func TestUpdateUserSubscribeHandlerRejectsInvalidLimits(t *testing.T) {
-	gin.SetMode(gin.TestMode)
-
-	tests := []struct {
-		name string
-		body string
-	}{
-		{
-			name: "negative speed limit",
-			body: `{"user_subscribe_id":1,"subscribe_id":1,"traffic":0,"expired_at":4102444800000,"upload":0,"download":0,"speed_limit":-1}`,
-		},
-		{
-			name: "invalid traffic limit json",
-			body: `{"user_subscribe_id":1,"subscribe_id":1,"traffic":0,"expired_at":4102444800000,"upload":0,"download":0,"traffic_limit":"not-json"}`,
-		},
-	}
-
-	for _, tt := range tests {
-		t.Run(tt.name, func(t *testing.T) {
-			router := gin.New()
-			router.PUT("/v1/admin/user/subscribe", UpdateUserSubscribeHandler(&svc.ServiceContext{}))
-
-			req := httptest.NewRequest(http.MethodPut, "/v1/admin/user/subscribe", bytes.NewBufferString(tt.body))
-			req.Header.Set("Content-Type", "application/json")
-			rec := httptest.NewRecorder()
-
-			router.ServeHTTP(rec, req)
-
-			if rec.Code != http.StatusOK {
-				t.Fatalf("expected HTTP 200, got %d", rec.Code)
-			}
-
-			var resp struct {
-				Code uint32 `json:"code"`
-				Msg  string `json:"msg"`
-			}
-			if err := json.Unmarshal(rec.Body.Bytes(), &resp); err != nil {
-				t.Fatalf("unmarshal response: %v", err)
-			}
-			if resp.Code != xerr.InvalidParams {
-				t.Fatalf("expected code %d, got %d (%s)", xerr.InvalidParams, resp.Code, resp.Msg)
-			}
-		})
-	}
-}

internal/logic/admin/user/updateUserSubscribeLogic.go

@@ -39,14 +39,6 @@ func (l *UpdateUserSubscribeLogic) UpdateUserSubscribe(req *types.UpdateUserSubs
 	} else {
 		userSub.Status = 1
 	}
-	speedLimit := userSub.SpeedLimit
-	if req.SpeedLimit != nil {
-		speedLimit = *req.SpeedLimit
-	}
-	trafficLimit := userSub.TrafficLimit
-	if req.TrafficLimit != nil {
-		trafficLimit = *req.TrafficLimit
-	}
 
 	err = l.svcCtx.UserModel.UpdateSubscribe(l.ctx, &user.Subscribe{
 		Id:          userSub.Id,
@@ -58,8 +50,6 @@ func (l *UpdateUserSubscribeLogic) UpdateUserSubscribe(req *types.UpdateUserSubs
 		Traffic:     req.Traffic,
 		Download:    req.Download,
 		Upload:      req.Upload,
-		SpeedLimit:   speedLimit,
-		TrafficLimit: trafficLimit,
 		Token:       userSub.Token,
 		UUID:        userSub.UUID,
 		Status:      userSub.Status,

internal/types/types.go

@@ -3325,7 +3325,7 @@ type UpdateUserSubscribeRequest struct {
 	ExpiredAt       int64   `json:"expired_at"`
 	Upload          int64   `json:"upload"`
 	Download        int64   `json:"download"`
-	SpeedLimit      *int64  `json:"speed_limit,omitempty" validate:"omitempty,gte=0"`
+	SpeedLimit      *int64  `json:"speed_limit,omitempty"`
 	TrafficLimit    *string `json:"traffic_limit,omitempty"`
 }