music-agregator/internal/database/album_repository.go

package database

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Album struct {
	ID           string
	ExternalID   string
	ArtistID     string
	Title        string
	AlbumType    string
	ReleaseDate  *time.Time
	TotalTracks  int
	TotalDiscs   int
	Label        string
	Genres       []string
	CoverURL     string
	MonitorState MonitorState
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

type AlbumRepository struct {
	pool *pgxpool.Pool
}

func NewAlbumRepository(pool *pgxpool.Pool) *AlbumRepository {
	return &AlbumRepository{pool: pool}
}

func (r *AlbumRepository) Create(ctx context.Context, a *Album) error {
	_, err := r.pool.Exec(ctx,
		`INSERT INTO albums (external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state)
		 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
		 ON CONFLICT (external_id) DO UPDATE SET
		   title = EXCLUDED.title,
		   album_type = EXCLUDED.album_type,
		   release_date = EXCLUDED.release_date,
		   total_tracks = EXCLUDED.total_tracks,
		   total_discs = EXCLUDED.total_discs,
		   label = EXCLUDED.label,
		   genres = EXCLUDED.genres,
		   cover_url = EXCLUDED.cover_url,
		   monitor_state = CASE
		     WHEN albums.monitor_state = 'excluded' THEN albums.monitor_state
		     WHEN albums.monitor_state = 'monitored' THEN albums.monitor_state
		     ELSE EXCLUDED.monitor_state
		   END,
		   updated_at = NOW()`,
		a.ExternalID, a.ArtistID, a.Title, a.AlbumType, a.ReleaseDate, a.TotalTracks, a.TotalDiscs, a.Label, a.Genres, a.CoverURL, a.MonitorState,
	)
	if err != nil {
		return fmt.Errorf("creating album: %w", err)
	}
	return nil
}

func (r *AlbumRepository) CreateBatch(ctx context.Context, albums []*Album) error {
	if len(albums) == 0 {
		return nil
	}

	batch := &pgx.Batch{}
	for _, a := range albums {
		batch.Queue(
			`INSERT INTO albums (external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state)
			 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
			 ON CONFLICT (external_id) DO UPDATE SET
			   title = EXCLUDED.title,
			   album_type = EXCLUDED.album_type,
			   release_date = EXCLUDED.release_date,
			   total_tracks = EXCLUDED.total_tracks,
			   total_discs = EXCLUDED.total_discs,
			   label = EXCLUDED.label,
			   genres = EXCLUDED.genres,
			   cover_url = EXCLUDED.cover_url,
			   monitor_state = CASE
			     WHEN albums.monitor_state = 'excluded' THEN albums.monitor_state
			     WHEN albums.monitor_state = 'monitored' THEN albums.monitor_state
			     ELSE EXCLUDED.monitor_state
			   END,
			   updated_at = NOW()`,
			a.ExternalID, a.ArtistID, a.Title, a.AlbumType, a.ReleaseDate, a.TotalTracks, a.TotalDiscs, a.Label, a.Genres, a.CoverURL, a.MonitorState,
		)
	}

	results := r.pool.SendBatch(ctx, batch)
	defer results.Close()

	for range albums {
		if _, err := results.Exec(); err != nil {
			return fmt.Errorf("batch creating album: %w", err)
		}
	}
	return nil
}

func (r *AlbumRepository) GetByExternalID(ctx context.Context, externalID string) (*Album, error) {
	a := &Album{}
	err := r.pool.QueryRow(ctx,
		`SELECT id, external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state, created_at, updated_at
		 FROM albums WHERE external_id = $1`, externalID,
	).Scan(&a.ID, &a.ExternalID, &a.ArtistID, &a.Title, &a.AlbumType, &a.ReleaseDate, &a.TotalTracks, &a.TotalDiscs, &a.Label, &a.Genres, &a.CoverURL, &a.MonitorState, &a.CreatedAt, &a.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("getting album: %w", err)
	}
	return a, nil
}

func (r *AlbumRepository) GetByID(ctx context.Context, id string) (*Album, error) {
	a := &Album{}
	err := r.pool.QueryRow(ctx,
		`SELECT id, external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state, created_at, updated_at
		 FROM albums WHERE id = $1`, id,
	).Scan(&a.ID, &a.ExternalID, &a.ArtistID, &a.Title, &a.AlbumType, &a.ReleaseDate, &a.TotalTracks, &a.TotalDiscs, &a.Label, &a.Genres, &a.CoverURL, &a.MonitorState, &a.CreatedAt, &a.UpdatedAt)
	if err != nil {
		return nil, fmt.Errorf("getting album: %w", err)
	}
	return a, nil
}

func (r *AlbumRepository) GetByArtistID(ctx context.Context, artistID string) ([]*Album, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state, created_at, updated_at
		 FROM albums WHERE artist_id = $1 ORDER BY release_date DESC`, artistID,
	)
	if err != nil {
		return nil, fmt.Errorf("listing albums: %w", err)
	}
	defer rows.Close()

	var albums []*Album
	for rows.Next() {
		a := &Album{}
		if err := rows.Scan(&a.ID, &a.ExternalID, &a.ArtistID, &a.Title, &a.AlbumType, &a.ReleaseDate, &a.TotalTracks, &a.TotalDiscs, &a.Label, &a.Genres, &a.CoverURL, &a.MonitorState, &a.CreatedAt, &a.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scanning album: %w", err)
		}
		albums = append(albums, a)
	}
	return albums, nil
}

func (r *AlbumRepository) GetMonitored(ctx context.Context) ([]*Album, error) {
	rows, err := r.pool.Query(ctx,
		`SELECT id, external_id, artist_id, title, album_type, release_date, total_tracks, total_discs, label, genres, cover_url, monitor_state, created_at, updated_at
		 FROM albums WHERE monitor_state = 'monitored' ORDER BY release_date DESC`,
	)
	if err != nil {
		return nil, fmt.Errorf("listing monitored albums: %w", err)
	}
	defer rows.Close()

	var albums []*Album
	for rows.Next() {
		a := &Album{}
		if err := rows.Scan(&a.ID, &a.ExternalID, &a.ArtistID, &a.Title, &a.AlbumType, &a.ReleaseDate, &a.TotalTracks, &a.TotalDiscs, &a.Label, &a.Genres, &a.CoverURL, &a.MonitorState, &a.CreatedAt, &a.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scanning album: %w", err)
		}
		albums = append(albums, a)
	}
	return albums, nil
}

func (r *AlbumRepository) SetMonitorState(ctx context.Context, id string, state MonitorState) error {
	_, err := r.pool.Exec(ctx,
		`UPDATE albums SET monitor_state = $1, updated_at = NOW() WHERE id = $2`, state, id,
	)
	if err != nil {
		return fmt.Errorf("updating monitor state: %w", err)
	}
	return nil
}