udeServer/internal/model/tsystemlogmodel.go

package model

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/zeromicro/go-zero/core/stores/sqlx"
)

// TSystemLog is the audit log entry of one request/operation. It has no
// json tags on purpose: the HMAC-SM3 chain signs the marshalled Go field
// names, which must stay stable (see internal/audit/hmac.go).
type TSystemLog struct {
	Id                  int64          `db:"id"`                     // 自增主键
	Iden                string         `db:"iden"`                   // 日志编号
	TraceId             string         `db:"trace_id"`               // 全链路唯一Trace ID
	SpanId              string         `db:"span_id"`                // 本次跨度唯一Span ID
	ParentSpanId        sql.NullString `db:"parent_span_id"`         // 父SpanID,用于构建调用树
	Type                int64          `db:"type"`                   // 日志类型:1.系统日志;2.接口日志
	ServiceName         string         `db:"service_name"`           // 所属服务或模块名
	UriPath             sql.NullString `db:"uri_path"`               // 接口请求路径
	HttpMethod          sql.NullString `db:"http_method"`            // 请求方法:GET/POST/PUT/DELETE
	Method              string         `db:"method"`                 // 请求业务方法
	Level               string         `db:"level"`                  // 日志级别:DEBUG/INFO/WARN/ERROR
	AppKey              sql.NullString `db:"app_key"`                // 调用接口应用标识
	UserId              sql.NullString `db:"user_id"`                // 系统用户ID
	UserName            sql.NullString `db:"user_name"`              // 系统用户名
	ClientIp            sql.NullString `db:"client_ip"`              // 客户端IP
	RequestHeader       sql.NullString `db:"request_header"`         // 请求头,脱敏后
	RequestBodyDigest   string         `db:"request_body_digest"`    // 请求体摘要(SM3)
	RequestBodyFileKey  string         `db:"request_body_file_key"`  // 请求体FileKey
	ResponseCode        int64          `db:"response_code"`          // 响应业务码
	ResponseBodyDigest  string         `db:"response_body_digest"`   // 响应体摘要(SM3)
	ResponseBodyFileKey string         `db:"response_body_file_key"` // 响应体FileKey
	ErrorMessage        sql.NullString `db:"error_message"`          // 错误提示，仅 ERROR 时填
	DurationMs          int64          `db:"duration_ms"`            // 本 Span 耗时（毫秒）
	CreatedAt           time.Time      `db:"created_at"`             // 写入时间
	HMac                string         `db:"h_mac"`                  // Hmac值
	AuditStatus         int64          `db:"audit_status"`           // 审计状态:1.未审计;2.审计通过;3.审计异常
}

// tSystemLogRows is the full column list.
var tSystemLogRows = strings.Join([]string{
	"`id`", "`iden`", "`trace_id`", "`span_id`", "`parent_span_id`", "`type`",
	"`service_name`", "`uri_path`", "`http_method`", "`method`", "`level`",
	"`app_key`", "`user_id`", "`user_name`", "`client_ip`", "`request_header`",
	"`request_body_digest`", "`request_body_file_key`", "`response_code`",
	"`response_body_digest`", "`response_body_file_key`", "`error_message`",
	"`duration_ms`", "`created_at`", "`h_mac`", "`audit_status`",
}, ",")

// SystemLogFilter mirrors SystemLogListReq; empty fields mean no filtering.
// TypeValid marks that Type was provided (so type=0 can be an explicit
// invalid request instead of "unfiltered").
type SystemLogFilter struct {
	Iden             string
	UserName         string
	AppKey           string
	UriPath          string
	HttpMethod       string
	CreatedStartTime string
	CreatedEndTime   string
	ClientIp         string
	ErrorMsg         string
	AuditStatus      int64
	Type             int64
	TypeValid        bool
}

type TSystemLogModel interface {
	// Insert stores a new audit entry; id and created_at are db generated
	// (created_at default CURRENT_TIMESTAMP, same as sdf-serve).
	Insert(ctx context.Context, data *TSystemLog) (sql.Result, error)
	// FindOneByIden returns the entry with the given iden.
	FindOneByIden(ctx context.Context, iden string) (*TSystemLog, error)
	// FindPage lists entries with optional filters and returns the page and
	// the total count, newest first (created_at DESC).
	FindPage(ctx context.Context, page, pageSize int64, f SystemLogFilter) ([]TSystemLog, int64, error)
	// FindByTraceId returns every entry of one trace, system-log first.
	FindByTraceId(ctx context.Context, traceId string) ([]TSystemLog, error)
	// FindByIdens returns the entries whose iden is contained in idens.
	FindByIdens(ctx context.Context, idens []string) ([]TSystemLog, error)
	// FindForArchive returns entries selected by the id list and/or the
	// created_at range (closed interval), ordered by created_at.
	FindForArchive(ctx context.Context, ids []int64, start, end string) ([]TSystemLog, error)
	// FindAllWithLimit returns up to threshold oldest entries for the
	// automatic archive job.
	FindAllWithLimit(ctx context.Context, threshold int) ([]*TSystemLog, error)
	// DeleteByIds batch-deletes rows by id; meant to run inside a tx.
	DeleteByIds(ctx context.Context, session sqlx.Session, ids []int64) error
}

type customTSystemLogModel struct {
	conn  sqlx.SqlConn
	table string
}

var _ TSystemLogModel = (*customTSystemLogModel)(nil)

// NewTSystemLogModel returns the model for the t_system_log table.
func NewTSystemLogModel(conn sqlx.SqlConn) TSystemLogModel {
	return &customTSystemLogModel{
		conn:  conn,
		table: "`t_system_log`",
	}
}

// tSystemLogInsertColumns is the insert column list; id and created_at are
// generated by the database.
func tSystemLogInsertColumns() string {
	return strings.Join([]string{
		"`iden`", "`trace_id`", "`span_id`", "`parent_span_id`", "`type`",
		"`service_name`", "`uri_path`", "`http_method`", "`method`", "`level`",
		"`app_key`", "`user_id`", "`user_name`", "`client_ip`", "`request_header`",
		"`request_body_digest`", "`request_body_file_key`", "`response_code`",
		"`response_body_digest`", "`response_body_file_key`", "`error_message`",
		"`duration_ms`", "`h_mac`", "`audit_status`",
	}, ",")
}

func (m *customTSystemLogModel) Insert(ctx context.Context, data *TSystemLog) (sql.Result, error) {
	query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
		m.table, tSystemLogInsertColumns())
	ret, err := m.conn.ExecCtx(ctx, query,
		data.Iden, data.TraceId, data.SpanId, data.ParentSpanId, data.Type,
		data.ServiceName, data.UriPath, data.HttpMethod, data.Method, data.Level,
		data.AppKey, data.UserId, data.UserName, data.ClientIp, data.RequestHeader,
		data.RequestBodyDigest, data.RequestBodyFileKey, data.ResponseCode,
		data.ResponseBodyDigest, data.ResponseBodyFileKey, data.ErrorMessage,
		data.DurationMs, data.HMac, data.AuditStatus)
	return ret, err
}

func (m *customTSystemLogModel) FindOneByIden(ctx context.Context, iden string) (*TSystemLog, error) {
	var resp TSystemLog
	query := fmt.Sprintf("select %s from %s where `iden` = ? limit 1", tSystemLogRows, m.table)
	err := m.conn.QueryRowCtx(ctx, &resp, query, iden)
	switch err {
	case nil:
		return &resp, nil
	case sqlx.ErrNotFound:
		return nil, ErrNotFound
	default:
		return nil, err
	}
}

func (m *customTSystemLogModel) FindPage(ctx context.Context, page, pageSize int64, f SystemLogFilter) ([]TSystemLog, int64, error) {
	var conds []string
	var args []any

	if f.Iden != "" {
		conds = append(conds, "`iden` = ?")
		args = append(args, f.Iden)
	}
	if f.UserName != "" {
		conds = append(conds, "`user_name` like ? escape '\\\\'")
		args = append(args, "%"+escapeLike(f.UserName)+"%")
	}
	if f.AppKey != "" {
		conds = append(conds, "`app_key` = ?")
		args = append(args, f.AppKey)
	}
	if f.CreatedStartTime != "" {
		conds = append(conds, "`created_at` >= ?")
		args = append(args, f.CreatedStartTime)
	}
	if f.CreatedEndTime != "" {
		conds = append(conds, "`created_at` <= ?")
		args = append(args, f.CreatedEndTime)
	}
	if f.UriPath != "" {
		conds = append(conds, "`uri_path` = ?")
		args = append(args, f.UriPath)
	}
	if f.HttpMethod != "" {
		conds = append(conds, "`http_method` = ?")
		args = append(args, f.HttpMethod)
	}
	if f.ClientIp != "" {
		conds = append(conds, "`client_ip` = ?")
		args = append(args, f.ClientIp)
	}
	if f.ErrorMsg != "" {
		conds = append(conds, "`error_message` like ? escape '\\\\'")
		args = append(args, "%"+escapeLike(f.ErrorMsg)+"%")
	}
	if f.AuditStatus != 0 {
		conds = append(conds, "`audit_status` = ?")
		args = append(args, f.AuditStatus)
	}
	if f.TypeValid {
		conds = append(conds, "`type` = ?")
		args = append(args, f.Type)
	}

	where := ""
	if len(conds) > 0 {
		where = " where " + strings.Join(conds, " and ")
	}

	var total int64
	if err := m.conn.QueryRowCtx(ctx, &total, fmt.Sprintf("select count(*) from %s%s", m.table, where), args...); err != nil {
		return nil, 0, err
	}

	if total == 0 {
		return nil, 0, nil
	}

	offset := (page - 1) * pageSize
	args = append(args, pageSize, offset)
	query := fmt.Sprintf("select %s from %s%s order by `created_at` desc limit ? offset ?", tSystemLogRows, m.table, where)

	var resp []TSystemLog
	if err := m.conn.QueryRowsCtx(ctx, &resp, query, args...); err != nil {
		return nil, 0, err
	}

	return resp, total, nil
}

func (m *customTSystemLogModel) FindByTraceId(ctx context.Context, traceId string) ([]TSystemLog, error) {
	var resp []TSystemLog
	query := fmt.Sprintf(
		"select %s from %s where `trace_id` = ? order by case `type` when 1 then 0 when 2 then 1 else 2 end, `id` asc",
		tSystemLogRows, m.table)
	if err := m.conn.QueryRowsCtx(ctx, &resp, query, traceId); err != nil {
		return nil, err
	}
	return resp, nil
}

func (m *customTSystemLogModel) FindByIdens(ctx context.Context, idens []string) ([]TSystemLog, error) {
	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(idens)), ",")
	args := make([]any, len(idens))
	for i, v := range idens {
		args[i] = v
	}
	var resp []TSystemLog
	query := fmt.Sprintf("select %s from %s where `iden` in (%s)", tSystemLogRows, m.table, placeholders)
	if err := m.conn.QueryRowsCtx(ctx, &resp, query, args...); err != nil {
		return nil, err
	}
	return resp, nil
}

func (m *customTSystemLogModel) FindForArchive(ctx context.Context, ids []int64, start, end string) ([]TSystemLog, error) {
	var conds []string
	var args []any

	if len(ids) > 0 {
		placeholders := strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")
		conds = append(conds, "`id` in ("+placeholders+")")
		for _, v := range ids {
			args = append(args, v)
		}
	}
	if start != "" && end != "" {
		conds = append(conds, "`created_at` >= ? and `created_at` <= ?")
		args = append(args, start, end)
	}

	where := ""
	if len(conds) > 0 {
		where = " where " + strings.Join(conds, " and ")
	}

	var resp []TSystemLog
	query := fmt.Sprintf("select %s from %s%s order by `created_at` asc", tSystemLogRows, m.table, where)
	if err := m.conn.QueryRowsCtx(ctx, &resp, query, args...); err != nil {
		return nil, err
	}
	return resp, nil
}

func (m *customTSystemLogModel) FindAllWithLimit(ctx context.Context, threshold int) ([]*TSystemLog, error) {
	var resp []*TSystemLog
	query := fmt.Sprintf("select %s from %s order by `created_at` asc limit ?", tSystemLogRows, m.table)
	if err := m.conn.QueryRowsCtx(ctx, &resp, query, threshold); err != nil {
		return nil, err
	}
	return resp, nil
}

func (m *customTSystemLogModel) DeleteByIds(ctx context.Context, session sqlx.Session, ids []int64) error {
	const deleteBatchSize = 1000

	for i := 0; i < len(ids); i += deleteBatchSize {
		end := i + deleteBatchSize
		if end > len(ids) {
			end = len(ids)
		}
		batchIds := ids[i:end]
		placeholders := strings.TrimSuffix(strings.Repeat("?,", len(batchIds)), ",")

		query := fmt.Sprintf("delete from t_system_log where id in (%s)", placeholders)

		args := make([]interface{}, len(batchIds))
		for j, v := range batchIds {
			args[j] = v
		}

		if _, err := session.ExecCtx(ctx, query, args...); err != nil {
			return err
		}
	}

	return nil
}