reprogl

1
package repositories
2

3
import (
4
	"database/sql"
5
	"errors"
6

7
	"xelbot.com/reprogl/models"
8
)
9

10
type CommentRepository struct {
11
	DB *sql.DB
12
}
13

14
func (cr *CommentRepository) GetLastUpdate(articleId int) (string, error) {
15
	var last string
16
	query := `
17
		SELECT
18
			DATE_FORMAT(src.last_update, '%y%j%H%i%s') AS last
19
		FROM (
20
			SELECT
21
				COALESCE(MAX(last_update), '1981-07-18 00:00:00') AS last_update
22
			FROM comments
23
			WHERE (post_id = ?)) AS src`
24

25
	err := cr.DB.QueryRow(query, articleId).Scan(&last)
26

27
	return last, err
28
}
29

30
func (cr *CommentRepository) GetCollectionByArticleId(articleId int) (models.CommentList, error) {
31
	query := `
32
		SELECT
33
			c.id,
34
			COALESCE(t.name, u.display_name, u.username) AS username,
35
			COALESCE(t.mail, u.mail) AS email,
36
			t.website,
37
			COALESCE(t.gender, u.gender) AS gender,
38
			c.commentator_id,
39
			c.user_id,
40
			c.text,
41
			c.tree_depth,
42
			c.time_created,
43
			COALESCE(t.rotten_link, 0) AS rotten_link,
44
			COALESCE(u.avatar_variant, 0) AS avatar_variant,
45
			c.deleted
46
		FROM comments AS c
47
		LEFT JOIN commentators AS t ON c.commentator_id = t.id
48
		LEFT JOIN users AS u ON c.user_id = u.id
49
		INNER JOIN (
50
			SELECT
51
				c1.id
52
			FROM
53
				comments AS c1,
54
				comments AS c2
55
			WHERE
56
				c1.post_id = ?
57
				AND c2.post_id = ?
58
				AND c1.tree_left_key <= c2.tree_left_key
59
				AND c1.tree_right_key >= c2.tree_right_key
60
				AND c2.deleted = 0
61
			GROUP BY c1.id
62
		) AS cc ON c.id = cc.id
63
		ORDER BY c.tree_left_key`
64

65
	rows, err := cr.DB.Query(query, articleId, articleId)
66
	if err != nil {
67
		return nil, err
68
	}
69

70
	defer rows.Close()
71

72
	comments := models.CommentList{}
73

74
	for rows.Next() {
75
		comment := models.Comment{}
76
		err = rows.Scan(
77
			&comment.ID,
78
			&comment.Name,
79
			&comment.Email,
80
			&comment.Website,
81
			&comment.Gender,
82
			&comment.CommentatorID,
83
			&comment.AuthorID,
84
			&comment.Text,
85
			&comment.Depth,
86
			&comment.CreatedAt,
87
			&comment.RottenLink,
88
			&comment.AvatarVariant,
89
			&comment.Deleted)
90

91
		if err != nil {
92
			return nil, err
93
		}
94

95
		comments = append(comments, &comment)
96
	}
97

98
	return comments, nil
99
}
100

101
// GetCollectionWithExtraDataByArticleId TODO use query builder with GetCollectionByArticleId
102
func (cr *CommentRepository) GetCollectionWithExtraDataByArticleId(articleId int) (models.CommentList, error) {
103
	query := `
104
		SELECT
105
			c.id,
106
			COALESCE(t.name, u.display_name, u.username) AS username,
107
			COALESCE(t.mail, u.mail) AS email,
108
			t.website,
109
			COALESCE(t.gender, u.gender) AS gender,
110
			c.commentator_id,
111
			c.user_id,
112
			c.text,
113
			c.tree_depth,
114
			c.time_created,
115
			c.ip_addr,
116
			COALESCE(gco.country_code, '-') AS country_code,
117
			ta.user_agent,
118
			COALESCE(t.rotten_link, 0) AS rotten_link,
119
			COALESCE(u.avatar_variant, 0) AS avatar_variant,
120
			c.deleted
121
		FROM comments AS c
122
		LEFT JOIN commentators AS t ON c.commentator_id = t.id
123
		LEFT JOIN users AS u ON c.user_id = u.id
124
		LEFT JOIN geo_location AS gl ON c.ip_long = gl.ip_long
125
		LEFT JOIN geo_location_city AS gci ON gl.city_id = gci.id
126
		LEFT JOIN geo_location_country AS gco ON gci.country_id = gco.id
127
		LEFT JOIN tracking_agent ta on c.user_agent_id = ta.id
128
		INNER JOIN (
129
			SELECT
130
				c1.id
131
			FROM
132
				comments AS c1,
133
				comments AS c2
134
			WHERE
135
				c1.post_id = ?
136
				AND c2.post_id = ?
137
				AND c1.tree_left_key <= c2.tree_left_key
138
				AND c1.tree_right_key >= c2.tree_right_key
139
				AND c2.deleted = 0
140
			GROUP BY c1.id
141
		) AS cc ON c.id = cc.id
142
		ORDER BY c.tree_left_key`
143

144
	rows, err := cr.DB.Query(query, articleId, articleId)
145
	if err != nil {
146
		return nil, err
147
	}
148

149
	defer rows.Close()
150

151
	comments := models.CommentList{}
152

153
	for rows.Next() {
154
		comment := models.Comment{}
155
		err = rows.Scan(
156
			&comment.ID,
157
			&comment.Name,
158
			&comment.Email,
159
			&comment.Website,
160
			&comment.Gender,
161
			&comment.CommentatorID,
162
			&comment.AuthorID,
163
			&comment.Text,
164
			&comment.Depth,
165
			&comment.CreatedAt,
166
			&comment.IP,
167
			&comment.CountryCode,
168
			&comment.UserAgent,
169
			&comment.RottenLink,
170
			&comment.AvatarVariant,
171
			&comment.Deleted)
172

173
		if err != nil {
174
			return nil, err
175
		}
176

177
		comments = append(comments, &comment)
178
	}
179

180
	return comments, nil
181
}
182

183
func (cr *CommentRepository) GetMostActiveCommentators() (*models.CommentatorList, error) {
184
	query := `
185
		SELECT
186
			src.cnt,
187
			COALESCE(t.name, u.display_name, u.username) AS username,
188
			COALESCE(t.mail, u.mail) AS email,
189
			t.website,
190
			COALESCE(t.gender, u.gender) AS gender,
191
			COALESCE(t.rotten_link, 0) AS rotten_link,
192
			COALESCE(u.avatar_variant, 0) AS avatar_variant,
193
			src.commentator_id,
194
			src.user_id
195
		FROM (
196
			SELECT commentator_id,
197
				c.user_id,
198
				COUNT(c.id)         AS cnt,
199
				MAX(c.time_created) AS last_time
200
			FROM comments AS c
201
			WHERE
202
				NOT (c.deleted = 1 AND c.tree_right_key - c.tree_left_key = 1)
203
				AND (c.user_id IS NULL OR c.user_id <> 1)
204
			GROUP BY commentator_id, user_id) AS src
205
		LEFT JOIN commentators AS t ON src.commentator_id = t.id
206
		LEFT JOIN users AS u ON src.user_id = u.id
207
		ORDER BY src.cnt DESC, src.last_time DESC
208
		LIMIT 8`
209

210
	rows, err := cr.DB.Query(query)
211
	if err != nil {
212
		return nil, err
213
	}
214

215
	defer rows.Close()
216

217
	commentators := models.CommentatorList{}
218

219
	for rows.Next() {
220
		commentator := &models.Commentator{}
221
		err = rows.Scan(
222
			&commentator.CommentsCount,
223
			&commentator.Name,
224
			&commentator.Email,
225
			&commentator.Website,
226
			&commentator.Gender,
227
			&commentator.RottenLink,
228
			&commentator.AvatarVariant,
229
			&commentator.CommentatorID,
230
			&commentator.AuthorID)
231

232
		if err != nil {
233
			return nil, err
234
		}
235

236
		commentators = append(commentators, commentator)
237
	}
238

239
	return &commentators, nil
240
}
241

242
func (cr *CommentRepository) FindForGravatar(id int) (*models.CommentatorForGravatar, error) {
243
	query := `
244
		SELECT
245
			c.id,
246
			c.mail,
247
			c.fake_email
248
		FROM commentators AS c
249
		WHERE (c.id = ?)`
250

251
	commentator := models.CommentatorForGravatar{}
252

253
	err := cr.DB.QueryRow(query, id).Scan(
254
		&commentator.ID,
255
		&commentator.Email,
256
		&commentator.FakeEmail)
257

258
	if err != nil {
259
		if errors.Is(err, sql.ErrNoRows) {
260
			return nil, models.RecordNotFound
261
		} else {
262
			return nil, err
263
		}
264
	}
265

266
	return &commentator, nil
267
}
268