database/notifications.go at master · TicketsBot-cloud/database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
package database

import (
	"context"
	_ "embed"
	"time"

	"github.com/jackc/pgx/v4/pgxpool"
)

type NotificationsTable struct {
	*pgxpool.Pool
}

type Notification struct {
	Id        int64     `json:"id"`
	UserId    uint64    `json:"user_id"`
	Category  string    `json:"category"`
	Title     string    `json:"title"`
	Body      string    `json:"body"`
	Link      *string   `json:"link"`
	Read      bool      `json:"read"`
	CreatedAt time.Time `json:"created_at"`
}

var (
	//go:embed sql/notifications/schema.sql
	notificationsSchema string

	//go:embed sql/notifications/create.sql
	notificationsCreate string

	//go:embed sql/notifications/list_by_user_id.sql
	notificationsListByUserId string

	//go:embed sql/notifications/count_unread_by_user_id.sql
	notificationsCountUnreadByUserId string

	//go:embed sql/notifications/count_by_user_id.sql
	notificationsCountByUserId string

	//go:embed sql/notifications/mark_as_read.sql
	notificationsMarkAsRead string

	//go:embed sql/notifications/mark_all_as_read.sql
	notificationsMarkAllAsRead string
)

func newNotificationsTable(pool *pgxpool.Pool) *NotificationsTable {
	return &NotificationsTable{pool}
}

func (NotificationsTable) Schema() string {
	return notificationsSchema
}

func (t *NotificationsTable) Create(ctx context.Context, userId uint64, category, title, body string, link *string) (Notification, error) {
	if len(title) > 200 {
		title = title[:200]
	}
	if len(body) > 2000 {
		body = body[:2000]
	}

	var id int64
	var createdAt time.Time
	if err := t.QueryRow(ctx, notificationsCreate, userId, category, title, body, link).Scan(&id, &createdAt); err != nil {
		return Notification{}, err
	}

	return Notification{
		Id:        id,
		UserId:    userId,
		Category:  category,
		Title:     title,
		Body:      body,
		Link:      link,
		Read:      false,
		CreatedAt: createdAt,
	}, nil
}

func (t *NotificationsTable) ListByUserId(ctx context.Context, userId uint64, categories []string, limit, offset int) ([]Notification, error) {
	rows, err := t.Query(ctx, notificationsListByUserId, userId, categories, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var notifications []Notification
	for rows.Next() {
		var n Notification
		if err := rows.Scan(
			&n.Id, &n.UserId, &n.Category, &n.Title, &n.Body, &n.Link, &n.Read, &n.CreatedAt,
		); err != nil {
			return nil, err
		}
		notifications = append(notifications, n)
	}
	return notifications, nil
}

func (t *NotificationsTable) CountUnreadByUserId(ctx context.Context, userId uint64) (int, error) {
	var count int
	if err := t.QueryRow(ctx, notificationsCountUnreadByUserId, userId).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func (t *NotificationsTable) CountByUserId(ctx context.Context, userId uint64, categories []string) (int, error) {
	var count int
	if err := t.QueryRow(ctx, notificationsCountByUserId, userId, categories).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func (t *NotificationsTable) MarkAsRead(ctx context.Context, id int64, userId uint64) error {
	_, err := t.Exec(ctx, notificationsMarkAsRead, id, userId)
	return err
}

func (t *NotificationsTable) MarkAllAsRead(ctx context.Context, userId uint64) error {
	_, err := t.Exec(ctx, notificationsMarkAllAsRead, userId)
	return err
}