videos.go
10454 bytes
1package db
2
3import (
4 "context"
5 "time"
6
7 "github.com/TheEdgeOfRage/ytrssil-api/models"
8)
9
10func (db *postgresDB) GetNewVideos(ctx context.Context, sortDesc bool) ([]models.Video, error) {
11 query := `
12 SELECT
13 videos.id
14 , title
15 , published_timestamp
16 , is_short
17 , is_live
18 , duration
19 , progress
20 , is_discarded
21 , downloaded_at
22 , file_path
23 , download_status
24 , download_error
25 , channels.name
26 , channels.id
27 FROM videos
28 LEFT JOIN channels ON videos.channel_id=channels.id
29 WHERE watch_timestamp IS NULL
30 AND videos.is_discarded = false
31 ORDER BY published_timestamp
32 `
33 if sortDesc {
34 query += " DESC"
35 }
36 query += ", videos.id DESC"
37
38 rows, err := db.db.Query(ctx, query)
39 if err != nil {
40 db.l.Error("Failed to query new videos", "call", "sql.QueryContext", "error", err)
41 return nil, err
42 }
43 defer rows.Close()
44
45 videos := make([]models.Video, 0)
46 for rows.Next() {
47 var video models.Video
48 err = rows.Scan(
49 &video.ID,
50 &video.Title,
51 &video.PublishedTime,
52 &video.IsShort,
53 &video.IsLive,
54 &video.DurationSeconds,
55 &video.ProgressSeconds,
56 &video.IsDiscarded,
57 &video.DownloadedAt,
58 &video.FilePath,
59 &video.DownloadStatus,
60 &video.DownloadError,
61 &video.ChannelName,
62 &video.ChannelID,
63 )
64 if err != nil {
65 db.l.Error("Failed to scan rows for get new videos", "call", "sql.Scan", "error", err)
66 return nil, err
67 }
68 videos = append(videos, video)
69 }
70 if err := rows.Err(); err != nil {
71 return nil, err
72 }
73
74 return videos, nil
75}
76
77func (db *postgresDB) GetWatchedVideos(
78 ctx context.Context, sortDesc bool, limit int, offset int,
79) ([]models.Video, error) {
80 query := `
81 SELECT
82 videos.id
83 , title
84 , published_timestamp
85 , watch_timestamp
86 , is_short
87 , is_live
88 , duration
89 , progress
90 , is_discarded
91 , downloaded_at
92 , file_path
93 , download_status
94 , download_error
95 , channels.name
96 , channels.id
97 FROM videos
98 LEFT JOIN channels ON channels.id=videos.channel_id
99 WHERE watch_timestamp IS NOT NULL
100 AND videos.is_discarded = false
101 ORDER BY watch_timestamp
102 `
103 if sortDesc {
104 query += " DESC"
105 }
106 query += ", videos.id DESC LIMIT $1 OFFSET $2"
107
108 rows, err := db.db.Query(ctx, query, limit, offset)
109 if err != nil {
110 db.l.Error("Failed to query for watched videos", "call", "sql.QueryContext", "error", err)
111 return nil, err
112 }
113 defer rows.Close()
114
115 videos := make([]models.Video, 0)
116 for rows.Next() {
117 var video models.Video
118 err = rows.Scan(
119 &video.ID,
120 &video.Title,
121 &video.PublishedTime,
122 &video.WatchTime,
123 &video.IsShort,
124 &video.IsLive,
125 &video.DurationSeconds,
126 &video.ProgressSeconds,
127 &video.IsDiscarded,
128 &video.DownloadedAt,
129 &video.FilePath,
130 &video.DownloadStatus,
131 &video.DownloadError,
132 &video.ChannelName,
133 &video.ChannelID,
134 )
135 if err != nil {
136 db.l.Error("Failed to scan rows for watched videos", "call", "sql.Scan", "error", err)
137 return nil, err
138 }
139 videos = append(videos, video)
140 }
141 if err := rows.Err(); err != nil {
142 return nil, err
143 }
144
145 return videos, nil
146}
147
148func (db *postgresDB) HasVideo(ctx context.Context, videoID string) (bool, error) {
149 query := `SELECT COUNT(1) FROM videos WHERE id = $1`
150 row := db.db.QueryRow(ctx, query, videoID)
151
152 var count int
153 err := row.Scan(&count)
154 if err != nil {
155 db.l.Error("Failed to query for video", "call", "sql.QueryRowContext", "error", err)
156 return false, err
157 }
158
159 return count == 1, nil
160}
161
162func (db *postgresDB) AddVideo(ctx context.Context, video models.Video, channelID string, isDiscarded bool) error {
163 query := `
164 INSERT INTO videos (
165 id
166 , title
167 , published_timestamp
168 , duration
169 , is_short
170 , is_live
171 , channel_id
172 , is_discarded
173 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
174 ON CONFLICT DO NOTHING
175 `
176
177 resp, err := db.db.Exec(
178 ctx,
179 query,
180 video.ID,
181 video.Title,
182 video.PublishedTime,
183 video.DurationSeconds,
184 video.IsShort,
185 video.IsLive,
186 channelID,
187 isDiscarded,
188 )
189 if err != nil {
190 db.l.Error("Failed to add video", "call", "sql.Exec", "error", err)
191 return err
192 }
193 if resp.RowsAffected() == 0 {
194 return ErrVideoExists
195 }
196
197 return nil
198}
199
200func (db *postgresDB) DiscardVideo(ctx context.Context, videoID string) error {
201 query := `UPDATE videos SET is_discarded = true WHERE id = $1`
202 _, err := db.db.Exec(ctx, query, videoID)
203 if err != nil {
204 db.l.Error("Failed to discard video", "call", "sql.Exec", "error", err)
205 return err
206 }
207 return nil
208}
209
210func (db *postgresDB) SetVideoWatchTime(
211 ctx context.Context,
212 videoID string,
213 watchTime *time.Time,
214) error {
215 const query = `UPDATE videos SET watch_timestamp = $1 WHERE id = $2`
216 _, err := db.db.Exec(ctx, query, watchTime, videoID)
217 if err != nil {
218 db.l.Error("", "error", err)
219 return err
220 }
221
222 return nil
223}
224
225func (db *postgresDB) SetVideoProgress(ctx context.Context, videoID string, progress int) (*models.Video, error) {
226 const query = `
227 WITH updated AS (
228 UPDATE videos SET progress = $1 WHERE id = $2 RETURNING *
229 )
230 SELECT
231 updated.id
232 , title
233 , published_timestamp
234 , is_short
235 , is_live
236 , duration
237 , progress
238 , watch_timestamp
239 , is_discarded
240 , downloaded_at
241 , file_path
242 , download_status
243 , download_error
244 , channels.name
245 , channels.id
246 FROM updated
247 LEFT JOIN channels ON updated.channel_id = channels.id
248 `
249
250 row := db.db.QueryRow(ctx, query, progress, videoID)
251 var video models.Video
252 err := row.Scan(
253 &video.ID,
254 &video.Title,
255 &video.PublishedTime,
256 &video.IsShort,
257 &video.IsLive,
258 &video.DurationSeconds,
259 &video.ProgressSeconds,
260 &video.WatchTime,
261 &video.IsDiscarded,
262 &video.DownloadedAt,
263 &video.FilePath,
264 &video.DownloadStatus,
265 &video.DownloadError,
266 &video.ChannelName,
267 &video.ChannelID,
268 )
269 if err != nil {
270 db.l.Error("Failed to scan rows for get new videos", "call", "sql.Scan", "error", err)
271 return nil, err
272 }
273
274 return &video, nil
275}
276
277func (db *postgresDB) GetVideo(ctx context.Context, videoID string) (*models.Video, error) {
278 query := `
279 SELECT
280 videos.id
281 , title
282 , published_timestamp
283 , is_short
284 , is_live
285 , duration
286 , progress
287 , watch_timestamp
288 , is_discarded
289 , downloaded_at
290 , file_path
291 , download_status
292 , download_error
293 , channels.name
294 , channels.id
295 FROM videos
296 LEFT JOIN channels ON videos.channel_id=channels.id
297 WHERE videos.id = $1
298 `
299
300 row := db.db.QueryRow(ctx, query, videoID)
301 var video models.Video
302 err := row.Scan(
303 &video.ID,
304 &video.Title,
305 &video.PublishedTime,
306 &video.IsShort,
307 &video.IsLive,
308 &video.DurationSeconds,
309 &video.ProgressSeconds,
310 &video.WatchTime,
311 &video.IsDiscarded,
312 &video.DownloadedAt,
313 &video.FilePath,
314 &video.DownloadStatus,
315 &video.DownloadError,
316 &video.ChannelName,
317 &video.ChannelID,
318 )
319 if err != nil {
320 db.l.Error("Failed to query video", "call", "sql.QueryRow", "error", err)
321 return nil, err
322 }
323
324 return &video, nil
325}
326
327func (db *postgresDB) SetVideoDownloadStatus(ctx context.Context, videoID string, status string) error {
328 query := `UPDATE videos SET download_status = $1 WHERE id = $2`
329 _, err := db.db.Exec(ctx, query, status, videoID)
330 if err != nil {
331 db.l.Error("Failed to set video download status", "error", err)
332 return err
333 }
334 return nil
335}
336
337func (db *postgresDB) SetVideoDownloadCompleted(ctx context.Context, videoID string, filePath string) error {
338 query := `
339 UPDATE videos
340 SET
341 downloaded_at = $1,
342 file_path = $2,
343 download_status = $3,
344 download_error = NULL
345 WHERE id = $4
346 `
347 now := time.Now()
348 _, err := db.db.Exec(ctx, query, now, filePath, "completed", videoID)
349 if err != nil {
350 db.l.Error("Failed to mark video as downloaded", "error", err)
351 return err
352 }
353 return nil
354}
355
356func (db *postgresDB) SetVideoDownloadFailed(ctx context.Context, videoID string, errorMsg string) error {
357 query := `UPDATE videos SET download_status = $1, download_error = $2 WHERE id = $3`
358 _, err := db.db.Exec(ctx, query, "failed", errorMsg, videoID)
359 if err != nil {
360 db.l.Error("Failed to mark video download as failed", "error", err)
361 return err
362 }
363 return nil
364}
365
366func (db *postgresDB) GetVideosForCleanup(ctx context.Context, olderThan time.Duration) ([]models.Video, error) {
367 query := `
368 SELECT
369 id
370 , file_path
371 , downloaded_at
372 , watch_timestamp
373 FROM videos
374 WHERE downloaded_at IS NOT NULL
375 AND file_path IS NOT NULL
376 AND download_status = 'completed'
377 AND watch_timestamp IS NOT NULL
378 AND watch_timestamp < $1
379 AND is_discarded = false
380 `
381 cutoffTime := time.Now().Add(-olderThan)
382
383 rows, err := db.db.Query(ctx, query, cutoffTime)
384 if err != nil {
385 db.l.Error("Failed to query videos for cleanup", "error", err)
386 return nil, err
387 }
388 defer rows.Close()
389
390 videos := make([]models.Video, 0)
391 for rows.Next() {
392 var video models.Video
393 err = rows.Scan(&video.ID, &video.FilePath, &video.DownloadedAt, &video.WatchTime)
394 if err != nil {
395 db.l.Error("Failed to scan cleanup video", "error", err)
396 continue
397 }
398 videos = append(videos, video)
399 }
400 if err := rows.Err(); err != nil {
401 return nil, err
402 }
403
404 return videos, nil
405}
406
407func (db *postgresDB) GetLiveVideos(ctx context.Context) ([]models.Video, error) {
408 query := `SELECT id FROM videos WHERE is_live = true AND watch_timestamp IS NULL`
409
410 rows, err := db.db.Query(ctx, query)
411 if err != nil {
412 db.l.Error("Failed to query live videos", "error", err)
413 return nil, err
414 }
415 defer rows.Close()
416
417 videos := make([]models.Video, 0)
418 for rows.Next() {
419 var video models.Video
420 err = rows.Scan(&video.ID)
421 if err != nil {
422 db.l.Error("Failed to scan live video", "error", err)
423 return nil, err
424 }
425 videos = append(videos, video)
426 }
427 if err := rows.Err(); err != nil {
428 return nil, err
429 }
430
431 return videos, nil
432}
433
434func (db *postgresDB) UpdateVideoLiveStatus(ctx context.Context, videoID string, isLive bool, duration int) error {
435 query := `UPDATE videos SET is_live = $1, duration = $2 WHERE id = $3`
436 _, err := db.db.Exec(ctx, query, isLive, duration, videoID)
437 if err != nil {
438 db.l.Error("Failed to update video live status", "error", err)
439 return err
440 }
441 return nil
442}
443
444func (db *postgresDB) DeleteVideoFile(ctx context.Context, videoID string) error {
445 query := `
446 UPDATE videos
447 SET
448 downloaded_at = NULL,
449 file_path = NULL,
450 download_status = NULL,
451 download_error = NULL
452 WHERE id = $1
453 `
454 _, err := db.db.Exec(ctx, query, videoID)
455 if err != nil {
456 db.l.Error("Failed to clear video download fields", "error", err)
457 return err
458 }
459 return nil
460}