431 lines
14 KiB
Go
431 lines
14 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"time"
|
|
)
|
|
|
|
type Ticket struct {
|
|
ID int64
|
|
TicketNumber int
|
|
UserID string
|
|
Panel string
|
|
Type string
|
|
ChannelID string
|
|
GuildID string
|
|
ClaimChannelID string
|
|
LogChannelID string
|
|
StaffRoleID string
|
|
ClaimReupMinutes int
|
|
OpenedAt time.Time
|
|
ClaimedBy sql.NullString
|
|
ClaimedAt sql.NullTime
|
|
ClosedAt sql.NullTime
|
|
ClosedBy sql.NullString
|
|
Reason sql.NullString
|
|
TranscriptPath sql.NullString
|
|
Status string
|
|
TicketTitle sql.NullString
|
|
TicketDescription sql.NullString
|
|
}
|
|
|
|
type TicketRepo struct{ db *sql.DB }
|
|
|
|
func NewTicketRepo(db *sql.DB) *TicketRepo { return &TicketRepo{db: db} }
|
|
|
|
// nextNumber returns the next ticket_number for the given type within a transaction.
|
|
func (r *TicketRepo) nextNumber(ctx context.Context, tx *sql.Tx, ticketType string) (int, error) {
|
|
var n int
|
|
err := tx.QueryRowContext(ctx,
|
|
`SELECT COALESCE(MAX(ticket_number),0)+1 FROM tickets WHERE type=?`, ticketType,
|
|
).Scan(&n)
|
|
return n, err
|
|
}
|
|
|
|
func (r *TicketRepo) Insert(ctx context.Context, t *Ticket) error {
|
|
tx, err := r.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback() //nolint
|
|
|
|
n, err := r.nextNumber(ctx, tx, t.Type)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
t.TicketNumber = n
|
|
|
|
res, err := tx.ExecContext(ctx, `
|
|
INSERT INTO tickets(ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,status,ticket_title,ticket_description)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
n, t.UserID, t.Panel, t.Type, t.ChannelID, t.GuildID, t.ClaimChannelID, t.LogChannelID,
|
|
t.StaffRoleID, t.ClaimReupMinutes, t.OpenedAt.UTC(), "open", t.TicketTitle, t.TicketDescription,
|
|
)
|
|
if err != nil {
|
|
return fmt.Errorf("insert ticket: %w", err)
|
|
}
|
|
t.ID, _ = res.LastInsertId()
|
|
t.Status = "open"
|
|
return tx.Commit()
|
|
}
|
|
|
|
// InsertWithNumber inserts a ticket where TicketNumber is already set (e.g. convocations).
|
|
func (r *TicketRepo) InsertWithNumber(ctx context.Context, t *Ticket) error {
|
|
res, err := r.db.ExecContext(ctx, `
|
|
INSERT INTO tickets(ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,status,ticket_title,ticket_description)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
t.TicketNumber, t.UserID, t.Panel, t.Type, t.ChannelID, t.GuildID, t.ClaimChannelID, t.LogChannelID,
|
|
t.StaffRoleID, t.ClaimReupMinutes, t.OpenedAt.UTC(), "open", t.TicketTitle, t.TicketDescription,
|
|
)
|
|
if err != nil {
|
|
return fmt.Errorf("insert ticket with number: %w", err)
|
|
}
|
|
t.ID, _ = res.LastInsertId()
|
|
t.Status = "open"
|
|
return nil
|
|
}
|
|
|
|
func (r *TicketRepo) GetByChannelID(ctx context.Context, channelID string) (*Ticket, error) {
|
|
row := r.db.QueryRowContext(ctx, `
|
|
SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
|
|
claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
|
|
FROM tickets WHERE channel_id=?`, channelID)
|
|
return scanTicket(row)
|
|
}
|
|
|
|
func (r *TicketRepo) GetByID(ctx context.Context, id int64) (*Ticket, error) {
|
|
row := r.db.QueryRowContext(ctx, `
|
|
SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
|
|
claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
|
|
FROM tickets WHERE id=?`, id)
|
|
return scanTicket(row)
|
|
}
|
|
|
|
func (r *TicketRepo) HasOpenTicket(ctx context.Context, userID, ticketType string) (*Ticket, error) {
|
|
row := r.db.QueryRowContext(ctx, `
|
|
SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
|
|
claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
|
|
FROM tickets WHERE user_id=? AND type=? AND status IN('open','claimed')
|
|
LIMIT 1`, userID, ticketType)
|
|
t, err := scanTicket(row)
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
return t, err
|
|
}
|
|
|
|
// CountByStatus returns the number of tickets with the given status.
|
|
func (r *TicketRepo) CountByStatus(ctx context.Context, status string) (int, error) {
|
|
var n int
|
|
err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM tickets WHERE status=?`, status).Scan(&n)
|
|
return n, err
|
|
}
|
|
|
|
// CountClosedSince returns the number of tickets closed on or after `since`.
|
|
func (r *TicketRepo) CountClosedSince(ctx context.Context, since time.Time) (int, error) {
|
|
var n int
|
|
err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM tickets WHERE status='closed' AND closed_at>=?`, since).Scan(&n)
|
|
return n, err
|
|
}
|
|
|
|
// AvgClaimMinutes returns the average minutes between opened_at and claimed_at for tickets claimed since `since`.
|
|
func (r *TicketRepo) AvgClaimMinutes(ctx context.Context, since time.Time) (int, error) {
|
|
var avg sql.NullFloat64
|
|
err := r.db.QueryRowContext(ctx,
|
|
`SELECT AVG((julianday(claimed_at)-julianday(opened_at))*1440)
|
|
FROM tickets WHERE claimed_at IS NOT NULL AND claimed_at>=?`, since).Scan(&avg)
|
|
if err != nil || !avg.Valid {
|
|
return 0, err
|
|
}
|
|
return int(avg.Float64), nil
|
|
}
|
|
|
|
// AvgResolutionMinutes returns the average minutes between opened_at and closed_at for tickets closed since `since`.
|
|
func (r *TicketRepo) AvgResolutionMinutes(ctx context.Context, since time.Time) (int, error) {
|
|
var avg sql.NullFloat64
|
|
err := r.db.QueryRowContext(ctx,
|
|
`SELECT AVG((julianday(closed_at)-julianday(opened_at))*1440)
|
|
FROM tickets WHERE closed_at IS NOT NULL AND closed_at>=?`, since).Scan(&avg)
|
|
if err != nil || !avg.Valid {
|
|
return 0, err
|
|
}
|
|
return int(avg.Float64), nil
|
|
}
|
|
|
|
func (r *TicketRepo) SetClaimed(ctx context.Context, id int64, staffID string, at time.Time) error {
|
|
_, err := r.db.ExecContext(ctx,
|
|
`UPDATE tickets SET status='claimed', claimed_by=?, claimed_at=? WHERE id=?`,
|
|
staffID, at.UTC(), id)
|
|
return err
|
|
}
|
|
|
|
func (r *TicketRepo) SetClosed(ctx context.Context, id int64, closedBy, reason, transcriptPath string, at time.Time) error {
|
|
_, err := r.db.ExecContext(ctx, `
|
|
UPDATE tickets SET status='closed', closed_at=?, closed_by=?, reason=?, transcript_path=?
|
|
WHERE id=?`,
|
|
at.UTC(), closedBy, reason, transcriptPath, id)
|
|
return err
|
|
}
|
|
|
|
func (r *TicketRepo) SetClosedByChannel(ctx context.Context, channelID, reason string) error {
|
|
_, err := r.db.ExecContext(ctx, `
|
|
UPDATE tickets SET status='closed', closed_at=?, reason=?
|
|
WHERE channel_id=? AND status IN('open','claimed')`,
|
|
time.Now().UTC(), reason, channelID)
|
|
return err
|
|
}
|
|
|
|
func (r *TicketRepo) ListOpen(ctx context.Context) ([]*Ticket, error) {
|
|
rows, err := r.db.QueryContext(ctx, `
|
|
SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
|
|
claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
|
|
FROM tickets WHERE status='open'`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
return scanTickets(rows)
|
|
}
|
|
|
|
func (r *TicketRepo) UpdateChannelID(ctx context.Context, id int64, newChannelID string) error {
|
|
_, err := r.db.ExecContext(ctx, `UPDATE tickets SET channel_id=? WHERE id=?`, newChannelID, id)
|
|
return err
|
|
}
|
|
|
|
func (r *TicketRepo) NextConvocationNumber(ctx context.Context) (int, error) {
|
|
tx, err := r.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
defer tx.Rollback() //nolint
|
|
var n int
|
|
if err := tx.QueryRowContext(ctx, `UPDATE convocation_counter SET count=count+1 WHERE id=1 RETURNING count`).Scan(&n); err != nil {
|
|
return 0, err
|
|
}
|
|
return n, tx.Commit()
|
|
}
|
|
|
|
// DailyCount holds the ticket count for one day.
|
|
type DailyCount struct {
|
|
Day string // "2006-01-02"
|
|
Count int
|
|
}
|
|
|
|
// DailyTicketCounts returns the count of tickets opened per day for the last n days.
|
|
func (r *TicketRepo) DailyTicketCounts(ctx context.Context, days int) ([]DailyCount, error) {
|
|
since := time.Now().AddDate(0, 0, -days+1).Truncate(24 * time.Hour)
|
|
rows, err := r.db.QueryContext(ctx, `
|
|
SELECT strftime('%Y-%m-%d', opened_at) AS day, COUNT(*) AS cnt
|
|
FROM tickets
|
|
WHERE opened_at >= ?
|
|
GROUP BY day
|
|
ORDER BY day`, since.UTC())
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []DailyCount
|
|
for rows.Next() {
|
|
var dc DailyCount
|
|
if err := rows.Scan(&dc.Day, &dc.Count); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, dc)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// StaffStat holds per-staff ticket stats.
|
|
type StaffStat struct {
|
|
StaffID string
|
|
Claimed int
|
|
Closed int
|
|
AvgClaimMinutes int
|
|
AvgResolutionMinutes int
|
|
}
|
|
|
|
// StaffStats returns per-staff statistics for tickets claimed in the last n days.
|
|
func (r *TicketRepo) StaffStats(ctx context.Context, days int) ([]StaffStat, error) {
|
|
since := time.Now().AddDate(0, 0, -days)
|
|
rows, err := r.db.QueryContext(ctx, `
|
|
SELECT
|
|
claimed_by,
|
|
COUNT(*) AS claimed,
|
|
SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END) AS closed,
|
|
CAST(AVG(CASE WHEN claimed_at IS NOT NULL
|
|
THEN (julianday(claimed_at) - julianday(opened_at)) * 1440 END) AS INTEGER) AS avg_claim,
|
|
CAST(AVG(CASE WHEN closed_at IS NOT NULL AND claimed_at IS NOT NULL
|
|
THEN (julianday(closed_at) - julianday(opened_at)) * 1440 END) AS INTEGER) AS avg_resolution
|
|
FROM tickets
|
|
WHERE claimed_by IS NOT NULL AND claimed_at >= ?
|
|
GROUP BY claimed_by
|
|
ORDER BY claimed DESC`, since.UTC())
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []StaffStat
|
|
for rows.Next() {
|
|
var s StaffStat
|
|
var avgClaim, avgRes sql.NullInt64
|
|
if err := rows.Scan(&s.StaffID, &s.Claimed, &s.Closed, &avgClaim, &avgRes); err != nil {
|
|
return nil, err
|
|
}
|
|
s.AvgClaimMinutes = int(avgClaim.Int64)
|
|
s.AvgResolutionMinutes = int(avgRes.Int64)
|
|
out = append(out, s)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ListFiltered returns tickets matching optional filters with pagination.
|
|
type TicketFilter struct {
|
|
Status string
|
|
Type string
|
|
StaffID string
|
|
GuildID string
|
|
Search string
|
|
From, To time.Time
|
|
Page int
|
|
PageSize int
|
|
}
|
|
|
|
func (r *TicketRepo) ListFiltered(ctx context.Context, f TicketFilter) ([]*Ticket, int, error) {
|
|
if f.PageSize <= 0 {
|
|
f.PageSize = 20
|
|
}
|
|
if f.Page <= 0 {
|
|
f.Page = 1
|
|
}
|
|
offset := (f.Page - 1) * f.PageSize
|
|
|
|
args := []any{}
|
|
where := "1=1"
|
|
if f.Status != "" {
|
|
where += " AND status=?"
|
|
args = append(args, f.Status)
|
|
}
|
|
if f.Type != "" {
|
|
where += " AND type=?"
|
|
args = append(args, f.Type)
|
|
}
|
|
if f.StaffID != "" {
|
|
where += " AND claimed_by=?"
|
|
args = append(args, f.StaffID)
|
|
}
|
|
if f.GuildID != "" {
|
|
where += " AND guild_id=?"
|
|
args = append(args, f.GuildID)
|
|
}
|
|
if f.Search != "" {
|
|
where += " AND (ticket_title LIKE ? OR user_id LIKE ?)"
|
|
args = append(args, "%"+f.Search+"%", "%"+f.Search+"%")
|
|
}
|
|
if !f.From.IsZero() {
|
|
where += " AND opened_at >= ?"
|
|
args = append(args, f.From.UTC())
|
|
}
|
|
if !f.To.IsZero() {
|
|
where += " AND opened_at <= ?"
|
|
args = append(args, f.To.UTC())
|
|
}
|
|
|
|
var total int
|
|
countArgs := make([]any, len(args))
|
|
copy(countArgs, args)
|
|
if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM tickets WHERE "+where, countArgs...).Scan(&total); err != nil {
|
|
return nil, 0, err
|
|
}
|
|
|
|
args = append(args, f.PageSize, offset)
|
|
rows, err := r.db.QueryContext(ctx, `
|
|
SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
|
|
claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
|
|
FROM tickets WHERE `+where+` ORDER BY opened_at DESC LIMIT ? OFFSET ?`, args...)
|
|
if err != nil {
|
|
return nil, 0, err
|
|
}
|
|
defer rows.Close()
|
|
list, err := scanTickets(rows)
|
|
return list, total, err
|
|
}
|
|
|
|
// ListDistinctTypes returns all distinct ticket types present in the tickets table.
|
|
func (r *TicketRepo) ListDistinctTypes(ctx context.Context) ([]string, error) {
|
|
rows, err := r.db.QueryContext(ctx, `SELECT DISTINCT type FROM tickets ORDER BY type`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []string
|
|
for rows.Next() {
|
|
var t string
|
|
if err := rows.Scan(&t); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, t)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ListDistinctStaff returns all distinct staff IDs (claimed_by) from the tickets table.
|
|
func (r *TicketRepo) ListDistinctStaff(ctx context.Context) ([]string, error) {
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT DISTINCT claimed_by FROM tickets WHERE claimed_by IS NOT NULL ORDER BY claimed_by`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []string
|
|
for rows.Next() {
|
|
var s string
|
|
if err := rows.Scan(&s); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, s)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// NullStr returns a valid NullString for non-empty strings, invalid (NULL) for empty.
|
|
func NullStr(s string) sql.NullString {
|
|
return sql.NullString{String: s, Valid: s != ""}
|
|
}
|
|
|
|
// SetTitleDescription updates the title and description of a ticket after modal submission.
|
|
func (r *TicketRepo) SetTitleDescription(ctx context.Context, id int64, title, description string) error {
|
|
_, err := r.db.ExecContext(ctx,
|
|
`UPDATE tickets SET ticket_title=?, ticket_description=? WHERE id=?`,
|
|
title, description, id)
|
|
return err
|
|
}
|
|
|
|
func scanTicket(row *sql.Row) (*Ticket, error) {
|
|
var t Ticket
|
|
err := row.Scan(&t.ID, &t.TicketNumber, &t.UserID, &t.Panel, &t.Type, &t.ChannelID,
|
|
&t.GuildID, &t.ClaimChannelID, &t.LogChannelID, &t.StaffRoleID, &t.ClaimReupMinutes,
|
|
&t.OpenedAt, &t.ClaimedBy, &t.ClaimedAt, &t.ClosedAt, &t.ClosedBy,
|
|
&t.Reason, &t.TranscriptPath, &t.Status, &t.TicketTitle, &t.TicketDescription)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return &t, nil
|
|
}
|
|
|
|
func scanTickets(rows *sql.Rows) ([]*Ticket, error) {
|
|
var result []*Ticket
|
|
for rows.Next() {
|
|
var t Ticket
|
|
if err := rows.Scan(&t.ID, &t.TicketNumber, &t.UserID, &t.Panel, &t.Type, &t.ChannelID,
|
|
&t.GuildID, &t.ClaimChannelID, &t.LogChannelID, &t.StaffRoleID, &t.ClaimReupMinutes,
|
|
&t.OpenedAt, &t.ClaimedBy, &t.ClaimedAt, &t.ClosedAt, &t.ClosedBy,
|
|
&t.Reason, &t.TranscriptPath, &t.Status, &t.TicketTitle, &t.TicketDescription); err != nil {
|
|
return nil, err
|
|
}
|
|
result = append(result, &t)
|
|
}
|
|
return result, rows.Err()
|
|
}
|