database/botstaff.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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
package database

import (
	"context"

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

type BotStaffTier string

const (
	BotStaffTierAdmin  BotStaffTier = "admin"
	BotStaffTierHelper BotStaffTier = "helper"
)

type BotStaffEntry struct {
	UserId     uint64       `json:"id,string"`
	Tier       BotStaffTier `json:"tier"`
	GlobalView bool         `json:"global_view"`
}

type BotStaff struct {
	*pgxpool.Pool
}

func newBotStaff(db *pgxpool.Pool) *BotStaff {
	return &BotStaff{
		db,
	}
}

func (s BotStaff) Schema() string {
	return `
CREATE TABLE IF NOT EXISTS bot_staff(
	"user_id" int8 NOT NULL UNIQUE,
	"tier" TEXT NOT NULL DEFAULT 'helper',
	PRIMARY KEY("user_id")
);

ALTER TABLE bot_staff ADD COLUMN IF NOT EXISTS "global_view" bool NOT NULL DEFAULT false;`
}

func (s *BotStaff) IsStaff(ctx context.Context, userId uint64) (isStaff bool, err error) {
	query := `
SELECT EXISTS (
	SELECT 1
	FROM bot_staff
	WHERE "user_id" = $1
);
`

	err = s.QueryRow(ctx, query, userId).Scan(&isStaff)
	return
}

func (s *BotStaff) GetTier(ctx context.Context, userId uint64) (BotStaffTier, error) {
	query := `SELECT "tier" FROM bot_staff WHERE "user_id" = $1`

	var tier BotStaffTier
	err := s.QueryRow(ctx, query, userId).Scan(&tier)
	if err == pgx.ErrNoRows {
		return "", nil
	}

	return tier, err
}

// Tier is in the query so no caller can report true for a helper row.
func (s *BotStaff) HasGlobalView(ctx context.Context, userId uint64) (bool, error) {
	query := `SELECT "global_view" FROM bot_staff WHERE "user_id" = $1 AND "tier" = 'admin'`

	var globalView bool
	err := s.QueryRow(ctx, query, userId).Scan(&globalView)
	if err == pgx.ErrNoRows {
		return false, nil
	}

	return globalView, err
}

func (s *BotStaff) SetGlobalView(ctx context.Context, userId uint64, enabled bool) (bool, error) {
	query := `
UPDATE bot_staff
SET "global_view" = $2
WHERE "user_id" = $1 AND "tier" = 'admin';`

	tag, err := s.Exec(ctx, query, userId, enabled)
	if err != nil {
		return false, err
	}

	return tag.RowsAffected() > 0, nil
}

func (s *BotStaff) GetAll(ctx context.Context) ([]BotStaffEntry, error) {
	query := `SELECT "user_id", "tier", "global_view" FROM bot_staff ORDER BY "tier", "user_id";`

	rows, err := s.Query(ctx, query)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var entries []BotStaffEntry
	for rows.Next() {
		var entry BotStaffEntry
		if err = rows.Scan(&entry.UserId, &entry.Tier, &entry.GlobalView); err != nil {
			return nil, err
		}

		entries = append(entries, entry)
	}

	return entries, nil
}

func (s *BotStaff) Add(ctx context.Context, userId uint64, tier BotStaffTier) (err error) {
	query := `
INSERT INTO bot_staff("user_id", "tier")
VALUES($1, $2)
ON CONFLICT("user_id") DO UPDATE
SET "tier" = $2,
    "global_view" = (bot_staff."global_view" AND $2::text = 'admin');
`

	_, err = s.Exec(ctx, query, userId, tier)
	return
}

func (s *BotStaff) UpdateTier(ctx context.Context, userId uint64, tier BotStaffTier) (err error) {
	query := `
UPDATE bot_staff
SET "tier" = $2,
    "global_view" = ("global_view" AND $2::text = 'admin')
WHERE "user_id" = $1;`

	_, err = s.Exec(ctx, query, userId, tier)
	return
}

func (s *BotStaff) Delete(ctx context.Context, userId uint64) (err error) {
	query := `
DELETE FROM bot_staff
WHERE "user_id" = $1;`

	_, err = s.Exec(ctx, query, userId)
	return
}