type GetTicketsParams struct {
	StartTime sql.NullTime `json:"startTime"`
	EndTime sql.NullTime `json:"endTime"`
	TitlePrefix interface{} `json:"titlePrefix"`
	ProblemID sql.NullInt64 `json:"problemId"`
	ProblemModuleID sql.NullInt64 `json:"problemModuleId"`
}
type GetTicketsRow struct {
	ID int64 `json:"id"`
	Title string `json:"title"`
	TicketStatus int8 `json:"ticketStatus"`
	ProblemTypeID int64 `json:"problemTypeId"`
	ProblemModuleID int64 `json:"problemModuleId"`
	PriorityID int32 `json:"priorityId"`
	SubmitterID int64 `json:"submitterId"`
	CurrentHandlerID int64 `json:"currentHandlerId"`
	CreatedAt time.Time `json:"createdAt"`
	UpdatedAt time.Time `json:"updatedAt"`
	TestConfirmedAt sql.NullTime `json:"testConfirmedAt"`
	MarkedProcessedAt sql.NullTime `json:"markedProcessedAt"`
	ProcessingDuration string `json:"processingDuration"`
	Description sql.NullString `json:"description"`
	PriorityName sql.NullString `json:"priorityName"`
	ProblemTypeName sql.NullString `json:"problemTypeName"`
	ModuleName sql.NullString `json:"moduleName"`
}
func (q *Queries) GetTickets(ctx context.Context, arg *GetTicketsParams) ([]*GetTicketsRow, error) {
	rows, err := q.db.QueryContext(ctx, getTickets,
		arg.StartTime,
		arg.StartTime,
		arg.EndTime,
		arg.EndTime,
		arg.TitlePrefix,
		arg.TitlePrefix,
		arg.ProblemID,
		arg.ProblemID,
		arg.ProblemModuleID,
		arg.ProblemModuleID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*GetTicketsRow
	for rows.Next() {
		var i GetTicketsRow
		if err := rows.Scan(
			&i.ID,
			&i.Title,
			&i.TicketStatus,
			&i.ProblemTypeID,
			&i.ProblemModuleID,
			&i.PriorityID,
			&i.SubmitterID,
			&i.CurrentHandlerID,
			&i.CreatedAt,
			&i.UpdatedAt,
			&i.TestConfirmedAt,
			&i.MarkedProcessedAt,
			&i.ProcessingDuration,
			&i.Description,
			&i.PriorityName,
			&i.ProblemTypeName,
			&i.ModuleName,
		); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

-- 工单表
CREATE TABLE ticket
(
 id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '工单编号',
 ticket_status TINYINT NOT NULL COMMENT '工单状态',
 problem_type_id BIGINT NOT NULL COMMENT '问题类型ID',
 problem_module_id BIGINT NOT NULL COMMENT '问题模块ID',
 priority_id INT NOT NULL COMMENT '优先级ID',
 submitter_id BIGINT NOT NULL COMMENT '提交人ID',
 current_handler_id BIGINT NOT NULL COMMENT '当前处理人ID',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 test_confirmed_at DATETIME NULL COMMENT '测试确认时间',
 marked_processed_at DATETIME NULL COMMENT '标记已处理时间',
 title VARCHAR(255) NOT NULL COMMENT '工单标题',
 description TEXT COMMENT '工单描述',
 processing_duration VARCHAR(255) NOT NULL DEFAULT '' COMMENT '处理时长',
 INDEX idx_ticket_created_at_status (created_at, ticket_status),
 INDEX idx_ticket_type_module_priority (problem_type_id, problem_module_id, priority_id),
 INDEX idx_ticket_handler_submitter (current_handler_id, submitter_id),
 INDEX idx_ticket_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='工单表';
-- 模块名称表
CREATE TABLE problem_module
(
 id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '模块ID',
 parent_module_id INT NOT NULL DEFAULT 0 COMMENT '父模块ID,根模块为0',
 is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否已删除',
 module_name VARCHAR(255) NOT NULL COMMENT '模块名称',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 INDEX idx_module_name (module_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='问题模块表';
-- 优先级表
CREATE TABLE priority
(
 id INT AUTO_INCREMENT PRIMARY KEY COMMENT '优先级ID',
 priority_name VARCHAR(255) NOT NULL COMMENT '优先级名称',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 UNIQUE KEY uk_priority_name (priority_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='优先级表';
-- 问题类型表
CREATE TABLE problem_type
(
 id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '问题类型ID',
 problem_type_name VARCHAR(255) NOT NULL COMMENT '问题类型名称',
 problem_level TINYINT NOT NULL COMMENT '问题等级',
 is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '是否已删除',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 UNIQUE KEY uk_problem_type_name (problem_type_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='问题类型表';

-- name: GetTickets :many
-- 工单查询:按创建时间、工单标题前缀、问题类型、问题模块筛选,并按优先级升序、ID升序排序
SELECT
 t.id,
 t.title,
 t.ticket_status,
 t.problem_type_id,
 t.problem_module_id,
 t.priority_id,
 t.submitter_id,
 t.current_handler_id,
 t.created_at,
 t.updated_at,
 t.test_confirmed_at,
 t.marked_processed_at,
 t.processing_duration,
 t.description,
 p.priority_name,
 pt.problem_type_name,
 pm.module_name
FROM ticket t
LEFT JOIN priority p ON t.priority_id = p.id
LEFT JOIN problem_type pt ON t.problem_type_id = pt.id
LEFT JOIN problem_module pm ON t.problem_module_id = pm.id
WHERE (sqlc.narg(start_time) IS NULL OR t.created_at >= sqlc.narg(start_time))
 AND (sqlc.narg(end_time) IS NULL OR t.created_at <= sqlc.narg(end_time))
 AND (sqlc.narg(title_prefix) IS NULL OR t.title LIKE CONCAT(sqlc.narg(title_prefix), '%'))
 AND (sqlc.narg(problem_id) IS NULL OR t.problem_type_id = sqlc.narg(problem_id))
 AND (sqlc.narg(problem_module_id) IS NULL OR t.problem_module_id = sqlc.narg(problem_module_id))
ORDER BY t.priority_id ASC, t.id ASC;

version: "2"
sql:
 - engine: "mysql"
 # 查询sql存放目录
 queries: "mysql/query"
 # 数据库schema存放目录
 schema: "mysql/schema"
 rules:
 - sqlc/db-prepare
 - mysql-query-too-costly
 gen:
 go:
 package: "dal"
 out: "internal/data/dal"
 emit_json_tags: true
 emit_result_struct_pointers: true
 # 当前只支持postgres,不支持mysql,保留配置等以后使用
 emit_params_struct_pointers: true
 emit_pointers_for_null_types: true
 json_tags_case_style: "camel"