velox-bot/internal/db/repos/twitchrepo/repo.go

package twitchrepo

import (
	"context"
	"database/sql"
)

type Repo struct {
	db *sql.DB
}

func NewRepo(db *sql.DB) *Repo {
	return &Repo{db: db}
}

func (r *Repo) ListGuilds(ctx context.Context) ([]int64, error) {
	const q = `SELECT DISTINCT guild_id FROM twitch`
	rows, err := r.db.QueryContext(ctx, q)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ids []int64
	for rows.Next() {
		var gid int64
		if err := rows.Scan(&gid); err != nil {
			return nil, err
		}
		ids = append(ids, gid)
	}
	return ids, rows.Err()
}

func (r *Repo) ListUsersForGuild(ctx context.Context, guildID int64) ([]string, error) {
	const q = `SELECT twitch_user FROM twitch WHERE guild_id = $1`
	rows, err := r.db.QueryContext(ctx, q, guildID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []string
	for rows.Next() {
		var u string
		if err := rows.Scan(&u); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func (r *Repo) GetStatus(ctx context.Context, guildID int64, username string) (string, bool, error) {
	const q = `SELECT status FROM twitch WHERE guild_id = $1 AND twitch_user = $2`
	var status string
	err := r.db.QueryRowContext(ctx, q, guildID, username).Scan(&status)
	if err == sql.ErrNoRows {
		return "", false, nil
	}
	if err != nil {
		return "", false, err
	}
	return status, true, nil
}

func (r *Repo) UpsertStreamer(ctx context.Context, guildID int64, username string) error {
	const q = `
		INSERT INTO twitch (twitch_user, guild_id, status)
		VALUES ($1, $2, 'not live')
		ON CONFLICT (twitch_user, guild_id)
		DO NOTHING
	`
	_, err := r.db.ExecContext(ctx, q, username, guildID)
	return err
}

func (r *Repo) RemoveStreamer(ctx context.Context, guildID int64, username string) error {
	const q = `DELETE FROM twitch WHERE guild_id = $1 AND twitch_user = $2`
	_, err := r.db.ExecContext(ctx, q, guildID, username)
	return err
}

func (r *Repo) UpdateStatus(ctx context.Context, guildID int64, username, status string) error {
	const q = `UPDATE twitch SET status = $1 WHERE guild_id = $2 AND twitch_user = $3`
	_, err := r.db.ExecContext(ctx, q, status, guildID, username)
	return err
}

func (r *Repo) GetNotificationChannel(ctx context.Context, guildID int64) (int64, bool, error) {
	const q = `SELECT twitch_channel_id FROM twitch_config WHERE guild_id = $1`
	var chID sql.NullInt64
	if err := r.db.QueryRowContext(ctx, q, guildID).Scan(&chID); err != nil {
		if err == sql.ErrNoRows {
			return 0, false, nil
		}
		return 0, false, err
	}
	if !chID.Valid {
		return 0, false, nil
	}
	return chID.Int64, true, nil
}

// SetNotificationChannel sets where live alerts go. Setting it for the first
// time turns alerts on (they start off); after that, the on/off switch is
// left alone.
func (r *Repo) SetNotificationChannel(ctx context.Context, guildID, channelID int64) error {
	const q = `
		INSERT INTO twitch_config (guild_id, twitch_channel_id, enabled)
		VALUES ($1, $2, TRUE)
		ON CONFLICT (guild_id)
		DO UPDATE SET twitch_channel_id = EXCLUDED.twitch_channel_id
	`
	_, err := r.db.ExecContext(ctx, q, guildID, channelID)
	return err
}

// IsEnabled reports whether Twitch notifications are enabled for guildID.
// A guild with no twitch_config row yet is off: modules start off until
// the server sets them up.
func (r *Repo) IsEnabled(ctx context.Context, guildID int64) (bool, error) {
	const q = `SELECT enabled FROM twitch_config WHERE guild_id = $1`
	var enabled bool
	err := r.db.QueryRowContext(ctx, q, guildID).Scan(&enabled)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return enabled, nil
}

func (r *Repo) SetEnabled(ctx context.Context, guildID int64, enabled bool) error {
	const q = `
		INSERT INTO twitch_config (guild_id, enabled)
		VALUES ($1, $2)
		ON CONFLICT (guild_id)
		DO UPDATE SET enabled = EXCLUDED.enabled
	`
	_, err := r.db.ExecContext(ctx, q, guildID, enabled)
	return err
}