database/panelteams.go at master · TicketsBot/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
package database

import (
	"context"
	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/pgxpool"
)

type PanelTeamsTable struct {
	*pgxpool.Pool
}

func newPanelTeamsTable(db *pgxpool.Pool) *PanelTeamsTable {
	return &PanelTeamsTable{
		db,
	}
}

func (p PanelTeamsTable) Schema() string {
	return `
CREATE TABLE IF NOT EXISTS panel_teams(
	"panel_id" int NOT NULL,
	"team_id" int NOT NULL,
	FOREIGN KEY("panel_id") REFERENCES panels("panel_id") ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY("team_id") REFERENCES support_team("id") ON DELETE CASCADE ON UPDATE CASCADE,
	PRIMARY KEY("panel_id", "team_id")
);
CREATE INDEX IF NOT EXISTS panel_teams_panel_id ON panel_teams("panel_id");
`
}

func (p *PanelTeamsTable) GetTeams(ctx context.Context, panelId int) (teams []SupportTeam, e error) {
	query := `
SELECT support_team.id, support_team.guild_id, support_team.name, support_team.on_call_role_id
FROM panel_teams
INNER JOIN support_team
ON panel_teams.team_id = support_team.id
WHERE panel_teams.panel_id = $1;
`

	rows, err := p.Query(ctx, query, panelId)
	defer rows.Close()
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var team SupportTeam
		if err := rows.Scan(&team.Id, &team.GuildId, &team.Name, &team.OnCallRole); err != nil {
			return nil, err
		}

		teams = append(teams, team)
	}

	return
}

func (p *PanelTeamsTable) GetTeamIds(ctx context.Context, panelId int) (teamIds []int, e error) {
	query := `SELECT "team_id" FROM panel_teams WHERE "panel_id" = $1;`

	rows, err := p.Query(ctx, query, panelId)
	defer rows.Close()
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var id int
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}

		teamIds = append(teamIds, id)
	}

	return
}

func (p *PanelTeamsTable) Add(ctx context.Context, panelId, teamId int) (err error) {
	query := `INSERT INTO panel_teams("panel_id", "team_id") VALUES($1, $2) ON CONFLICT("panel_id", "team_id") DO NOTHING;`
	_, err = p.Exec(ctx, query, panelId, teamId)
	return
}

func (p *PanelTeamsTable) DeleteAll(ctx context.Context, panelId int) (err error) {
	query := `DELETE FROM panel_teams WHERE "panel_id"=$1;`
	_, err = p.Exec(ctx, query, panelId)
	return
}

func (p *PanelTeamsTable) Delete(ctx context.Context, panelId, teamId int) (err error) {
	query := `DELETE FROM panel_teams WHERE "panel_id"=$1 AND "team_id"=$2;`
	_, err = p.Exec(ctx, query, panelId, teamId)
	return
}

func (p *PanelTeamsTable) Replace(ctx context.Context, panelId int, teamIds []int) error {
	tx, err := p.Begin(ctx)
	if err != nil {
		return err
	}

	defer tx.Rollback(ctx)

	if err := p.ReplaceWithTx(ctx, tx, panelId, teamIds); err != nil {
		return err
	}

	return tx.Commit(ctx)
}

func (p *PanelTeamsTable) ReplaceWithTx(ctx context.Context, tx pgx.Tx, panelId int, teamIds []int) error {
	// Remove existing teams from panel
	if _, err := tx.Exec(ctx, `DELETE FROM panel_teams WHERE "panel_id"=$1;`, panelId); err != nil {
		return err
	}

	// Add each provided team to panel
	for _, teamId := range teamIds {
		query := `INSERT INTO panel_teams("panel_id", "team_id") VALUES($1, $2) ON CONFLICT("panel_id", "team_id") DO NOTHING;`
		if _, err := tx.Exec(ctx, query, panelId, teamId); err != nil {
			return err
		}
	}

	return nil
}