channels.go
3839 bytes
1package db
2
3import (
4 "context"
5 "fmt"
6
7 "github.com/TheEdgeOfRage/ytrssil-api/models"
8)
9
10func (db *postgresDB) SubscribeToChannel(ctx context.Context, channel models.Channel) error {
11 const query = `
12 INSERT INTO channels (id, name, subscribed, image_url, enable_shorts) VALUES ($1, $2, $3, $4, $5)
13 ON CONFLICT (id) DO UPDATE SET
14 subscribed = CASE WHEN EXCLUDED.subscribed THEN true ELSE channels.subscribed END,
15 image_url = CASE WHEN EXCLUDED.subscribed THEN EXCLUDED.image_url ELSE channels.image_url END,
16 enable_shorts = CASE WHEN EXCLUDED.subscribed THEN EXCLUDED.enable_shorts ELSE channels.enable_shorts END
17 `
18 resp, err := db.db.Exec(ctx, query, channel.ID, channel.Name, channel.Subscribed,
19 channel.ImageURL, channel.EnableShorts)
20 if err != nil {
21 db.l.Error("Failed to subscribe to channel", "call", "sql.ExecContext", "error", err)
22 return err
23 }
24
25 if resp.RowsAffected() == 0 {
26 db.l.Error("Failed to subscribe to channel, no rows affected", "call", "sql.RowsAffected")
27 return fmt.Errorf("failed to subscribe to channel")
28 }
29
30 return nil
31}
32
33func (db *postgresDB) ListChannels(ctx context.Context) ([]models.Channel, error) {
34 const query = `
35 SELECT
36 channels.id,
37 channels.name,
38 channels.subscribed,
39 COALESCE(channels.image_url, '') as image_url,
40 COALESCE(channels.enable_shorts, true) as enable_shorts,
41 COUNT(videos.id) FILTER (WHERE videos.watch_timestamp IS NULL AND videos.is_discarded = false) as unwatched_count
42 FROM channels
43 LEFT JOIN videos ON channels.id = videos.channel_id
44 WHERE channels.subscribed = true
45 GROUP BY channels.id, channels.name, channels.subscribed, channels.image_url, channels.enable_shorts
46 ORDER BY channels.name
47 `
48 rows, err := db.db.Query(ctx, query)
49 if err != nil {
50 db.l.Error("Failed to list channels", "call", "sql.QueryContext", "error", err)
51 return nil, err
52 }
53 defer rows.Close()
54
55 channels := make([]models.Channel, 0)
56 for rows.Next() {
57 var channel models.Channel
58 err = rows.Scan(&channel.ID, &channel.Name, &channel.Subscribed, &channel.ImageURL,
59 &channel.EnableShorts, &channel.UnwatchedCount)
60 if err != nil {
61 db.l.Error("Failed to scan rows to list channels", "call", "sql.Scan", "error", err)
62 return nil, err
63 }
64 channels = append(channels, channel)
65 }
66 if err := rows.Err(); err != nil {
67 return nil, err
68 }
69
70 return channels, nil
71}
72
73func (db *postgresDB) UnsubscribeFromChannel(ctx context.Context, channelID string) error {
74 const query = `UPDATE channels SET subscribed = false WHERE id = $1`
75 resp, err := db.db.Exec(ctx, query, channelID)
76 if err != nil {
77 db.l.Error("Failed to unsubscribe from channel", "call", "sql.ExecContext", "error", err)
78 return err
79 }
80
81 if resp.RowsAffected() != 1 {
82 return ErrChannelNotFound
83 }
84
85 return nil
86}
87
88func (db *postgresDB) ToggleChannelShorts(ctx context.Context, channelID string, enableShorts bool) error {
89 const query = `UPDATE channels SET enable_shorts = $1 WHERE id = $2`
90 resp, err := db.db.Exec(ctx, query, enableShorts, channelID)
91 if err != nil {
92 db.l.Error("Failed to toggle channel shorts", "call", "sql.ExecContext", "error", err)
93 return err
94 }
95
96 if resp.RowsAffected() != 1 {
97 return ErrChannelNotFound
98 }
99
100 return nil
101}
102
103func (db *postgresDB) GetChannelByID(ctx context.Context, channelID string) (*models.Channel, error) {
104 const query = `
105 SELECT
106 id,
107 name,
108 subscribed,
109 COALESCE(image_url, '') as image_url,
110 COALESCE(enable_shorts, true) as enable_shorts
111 FROM channels
112 WHERE id = $1
113 `
114 row := db.db.QueryRow(ctx, query, channelID)
115 var channel models.Channel
116 err := row.Scan(&channel.ID, &channel.Name, &channel.Subscribed, &channel.ImageURL, &channel.EnableShorts)
117 if err != nil {
118 db.l.Error("Failed to query channel by ID", "call", "sql.QueryRowContext", "error", err)
119 return nil, err
120 }
121
122 return &channel, nil
123}