2022-10-27 04:04:49 +02:00
|
|
|
package db
|
|
|
|
|
|
|
|
import (
|
|
|
|
"context"
|
2022-10-30 01:08:07 +02:00
|
|
|
"time"
|
2022-10-27 04:04:49 +02:00
|
|
|
|
|
|
|
"gitea.theedgeofrage.com/TheEdgeOfRage/ytrssil-api/models"
|
|
|
|
)
|
|
|
|
|
|
|
|
var getNewVideosQuery = `
|
|
|
|
SELECT
|
|
|
|
video_id
|
|
|
|
, title
|
|
|
|
, published_timestamp
|
|
|
|
, watch_timestamp
|
|
|
|
, name as channel_name
|
|
|
|
FROM user_videos
|
|
|
|
LEFT JOIN videos ON video_id=videos.id
|
|
|
|
LEFT JOIN channels ON channel_id=channels.id
|
|
|
|
WHERE
|
|
|
|
1=1
|
|
|
|
AND watch_timestamp IS NULL
|
|
|
|
AND username=$1
|
|
|
|
ORDER BY published_timestamp
|
|
|
|
`
|
|
|
|
|
2022-10-29 05:09:52 +02:00
|
|
|
func (d *postgresDB) GetNewVideos(ctx context.Context, username string) ([]models.Video, error) {
|
|
|
|
rows, err := d.db.QueryContext(ctx, getNewVideosQuery, username)
|
2022-10-27 04:04:49 +02:00
|
|
|
if err != nil {
|
2022-10-29 05:09:52 +02:00
|
|
|
d.l.Log("level", "ERROR", "function", "db.GetNewVideos", "call", "sql.QueryContext", "error", err)
|
2022-10-27 04:04:49 +02:00
|
|
|
return nil, err
|
|
|
|
}
|
2022-10-29 05:09:52 +02:00
|
|
|
defer rows.Close()
|
|
|
|
|
|
|
|
videos := make([]models.Video, 0)
|
|
|
|
for rows.Next() {
|
|
|
|
var video models.Video
|
|
|
|
err = rows.Scan(
|
|
|
|
&video.ID,
|
|
|
|
&video.Title,
|
|
|
|
&video.PublishedTime,
|
|
|
|
&video.WatchTime,
|
|
|
|
&video.ChannelName,
|
|
|
|
)
|
|
|
|
if err != nil {
|
|
|
|
d.l.Log("level", "ERROR", "function", "db.GetNewVideos", "call", "sql.Scan", "error", err)
|
|
|
|
return nil, err
|
|
|
|
}
|
|
|
|
videos = append(videos, video)
|
|
|
|
}
|
2022-10-27 04:04:49 +02:00
|
|
|
|
|
|
|
return videos, nil
|
|
|
|
}
|
2022-10-29 05:09:52 +02:00
|
|
|
|
2022-10-30 01:08:07 +02:00
|
|
|
var getWatchedVideosQuery = `
|
|
|
|
SELECT
|
|
|
|
video_id
|
|
|
|
, title
|
|
|
|
, published_timestamp
|
|
|
|
, watch_timestamp
|
|
|
|
, name as channel_name
|
|
|
|
FROM user_videos
|
|
|
|
LEFT JOIN videos ON video_id=videos.id
|
|
|
|
LEFT JOIN channels ON channel_id=channels.id
|
|
|
|
WHERE
|
|
|
|
1=1
|
|
|
|
AND watch_timestamp IS NOT NULL
|
|
|
|
AND username=$1
|
2022-10-30 13:57:54 +01:00
|
|
|
ORDER BY watch_timestamp DESC
|
2022-10-30 01:08:07 +02:00
|
|
|
`
|
|
|
|
|
|
|
|
func (d *postgresDB) GetWatchedVideos(ctx context.Context, username string) ([]models.Video, error) {
|
|
|
|
rows, err := d.db.QueryContext(ctx, getWatchedVideosQuery, username)
|
|
|
|
if err != nil {
|
|
|
|
d.l.Log("level", "ERROR", "function", "db.GetWatchedVideos", "call", "sql.QueryContext", "error", err)
|
|
|
|
return nil, err
|
|
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
|
|
|
|
videos := make([]models.Video, 0)
|
|
|
|
for rows.Next() {
|
|
|
|
var video models.Video
|
|
|
|
err = rows.Scan(
|
|
|
|
&video.ID,
|
|
|
|
&video.Title,
|
|
|
|
&video.PublishedTime,
|
|
|
|
&video.WatchTime,
|
|
|
|
&video.ChannelName,
|
|
|
|
)
|
|
|
|
if err != nil {
|
|
|
|
d.l.Log("level", "ERROR", "function", "db.GetWatchedVideos", "call", "sql.Scan", "error", err)
|
|
|
|
return nil, err
|
|
|
|
}
|
|
|
|
videos = append(videos, video)
|
|
|
|
}
|
2022-10-29 05:09:52 +02:00
|
|
|
|
2022-10-30 01:08:07 +02:00
|
|
|
return videos, nil
|
|
|
|
}
|
|
|
|
|
2022-10-31 02:55:50 +01:00
|
|
|
var addVideoQuery = `
|
|
|
|
INSERT INTO videos (
|
|
|
|
id
|
|
|
|
, title
|
|
|
|
, published_timestamp
|
|
|
|
, channel_id
|
|
|
|
) VALUES ($1, $2, $3, $4)
|
|
|
|
ON CONFLICT DO NOTHING
|
|
|
|
`
|
2022-10-30 01:08:07 +02:00
|
|
|
|
|
|
|
func (d *postgresDB) AddVideo(ctx context.Context, video models.Video, channelID string) error {
|
2022-10-31 02:55:50 +01:00
|
|
|
resp, err := d.db.ExecContext(ctx, addVideoQuery, video.ID, video.Title, video.PublishedTime, channelID)
|
2022-10-29 05:09:52 +02:00
|
|
|
if err != nil {
|
2022-10-30 01:08:07 +02:00
|
|
|
d.l.Log("level", "ERROR", "function", "db.AddVideo", "call", "sql.Exec", "error", err)
|
|
|
|
return err
|
|
|
|
}
|
2022-10-31 02:55:50 +01:00
|
|
|
if affected, _ := resp.RowsAffected(); affected == 0 {
|
|
|
|
return ErrVideoExists
|
|
|
|
}
|
2022-10-30 01:08:07 +02:00
|
|
|
|
|
|
|
return nil
|
|
|
|
}
|
|
|
|
|
2022-10-31 02:55:50 +01:00
|
|
|
var addVideoToUserQuery = `INSERT INTO user_videos (username, video_id) VALUES ($1, $2) ON CONFLICT DO NOTHING`
|
2022-10-30 01:08:07 +02:00
|
|
|
|
|
|
|
func (d *postgresDB) AddVideoToUser(ctx context.Context, username string, videoID string) error {
|
|
|
|
_, err := d.db.ExecContext(ctx, addVideoToUserQuery, username, videoID)
|
|
|
|
if err != nil {
|
|
|
|
d.l.Log("level", "ERROR", "function", "db.AddVideoToUser", "error", err)
|
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
return nil
|
|
|
|
}
|
|
|
|
|
|
|
|
var setVideoWatchTimeQuery = `UPDATE user_videos SET watch_timestamp = $1 WHERE username = $2 AND video_id = $3`
|
|
|
|
|
|
|
|
func (d *postgresDB) SetVideoWatchTime(
|
|
|
|
ctx context.Context, username string, videoID string, watchTime *time.Time,
|
|
|
|
) error {
|
|
|
|
_, err := d.db.ExecContext(ctx, setVideoWatchTimeQuery, watchTime, username, videoID)
|
|
|
|
if err != nil {
|
|
|
|
d.l.Log("level", "ERROR", "function", "db.WatchVideo", "error", err)
|
2022-10-29 05:09:52 +02:00
|
|
|
return err
|
|
|
|
}
|
|
|
|
|
|
|
|
return nil
|
|
|
|
}
|