vibesonic/internal/database/shares.go

package database

import (
	"database/sql"
	"errors"
	"time"

	"git.noroutes.net/NoRoutes/vibetunes/internal/idgen"
)

type Share struct {
	ID            string
	OwnerID       string
	Token         string
	Description   string
	ExpiresAt     sql.NullString
	CreatedAt     time.Time
	LastVisitedAt sql.NullString
	VisitCount    int64
}

const shareSelect = `SELECT id, owner_id, token, description, expires_at, created_at, last_visited_at, visit_count FROM shares`

func CreateShare(db *sql.DB, ownerID, token, description string, expiresAt sql.NullString) (*Share, error) {
	id := idgen.New("sh")
	var exp any
	if expiresAt.Valid {
		exp = expiresAt.String
	}
	_, err := db.Exec(`INSERT INTO shares (id, owner_id, token, description, expires_at) VALUES (?, ?, ?, ?, ?)`, id, ownerID, token, description, exp)
	if err != nil {
		return nil, err
	}
	return GetShare(db, id)
}

func GetShare(db *sql.DB, id string) (*Share, error) {
	return scanShare(db.QueryRow(shareSelect+` WHERE id = ?`, id))
}

func GetShareByToken(db *sql.DB, token string) (*Share, error) {
	return scanShare(db.QueryRow(shareSelect+` WHERE token = ?`, token))
}

func scanShare(row *sql.Row) (*Share, error) {
	var s Share
	var createdAt string
	err := row.Scan(&s.ID, &s.OwnerID, &s.Token, &s.Description, &s.ExpiresAt, &createdAt, &s.LastVisitedAt, &s.VisitCount)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	s.CreatedAt, _ = parseTime(createdAt)
	return &s, nil
}

func ListSharesByOwner(db *sql.DB, ownerID string) ([]*Share, error) {
	rows, err := db.Query(shareSelect+` WHERE owner_id = ? ORDER BY created_at DESC`, ownerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanShares(rows)
}

func ListAllShares(db *sql.DB) ([]*Share, error) {
	rows, err := db.Query(shareSelect + ` ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanShares(rows)
}

func scanShares(rows *sql.Rows) ([]*Share, error) {
	var out []*Share
	for rows.Next() {
		var s Share
		var createdAt string
		if err := rows.Scan(&s.ID, &s.OwnerID, &s.Token, &s.Description, &s.ExpiresAt, &createdAt, &s.LastVisitedAt, &s.VisitCount); err != nil {
			return nil, err
		}
		s.CreatedAt, _ = parseTime(createdAt)
		out = append(out, &s)
	}
	return out, rows.Err()
}

func UpdateShare(db *sql.DB, id, description string, expiresAt sql.NullString) error {
	var exp any
	if expiresAt.Valid {
		exp = expiresAt.String
	}
	_, err := db.Exec(`UPDATE shares SET description = ?, expires_at = ? WHERE id = ?`, description, exp, id)
	return err
}

func DeleteShare(db *sql.DB, id string) error {
	_, err := db.Exec(`DELETE FROM shares WHERE id = ?`, id)
	return err
}

func RecordShareVisit(db *sql.DB, id string) error {
	_, err := db.Exec(`UPDATE shares SET last_visited_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'), visit_count = visit_count + 1 WHERE id = ?`, id)
	return err
}

// IsExpired reports whether a share's expiry has passed.
func (s *Share) IsExpired() bool {
	if !s.ExpiresAt.Valid {
		return false
	}
	t, err := parseTime(s.ExpiresAt.String)
	if err != nil {
		return false
	}
	return time.Now().After(t)
}

// --- Share items ---

type ShareItem struct {
	ID       string
	ShareID  string
	SongID   sql.NullString
	AlbumID  sql.NullString
	Position int
}

func AddShareItem(db *sql.DB, shareID string, songID, albumID sql.NullString, position int) error {
	var song, album any
	if songID.Valid {
		song = songID.String
	}
	if albumID.Valid {
		album = albumID.String
	}
	_, err := db.Exec(`INSERT INTO share_items (id, share_id, song_id, album_id, position) VALUES (?, ?, ?, ?, ?)`,
		idgen.New("shi"), shareID, song, album, position)
	return err
}

func ListShareItems(db *sql.DB, shareID string) ([]*ShareItem, error) {
	rows, err := db.Query(`SELECT id, share_id, song_id, album_id, position FROM share_items WHERE share_id = ? ORDER BY position`, shareID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*ShareItem
	for rows.Next() {
		var si ShareItem
		if err := rows.Scan(&si.ID, &si.ShareID, &si.SongID, &si.AlbumID, &si.Position); err != nil {
			return nil, err
		}
		out = append(out, &si)
	}
	return out, rows.Err()
}

// ShareSongIDs resolves a share's items to a flat list of song IDs,
// expanding any shared albums into their songs.
func ShareSongIDs(db *sql.DB, shareID string) ([]string, error) {
	items, err := ListShareItems(db, shareID)
	if err != nil {
		return nil, err
	}
	var out []string
	for _, item := range items {
		if item.SongID.Valid {
			out = append(out, item.SongID.String)
			continue
		}
		if item.AlbumID.Valid {
			songs, err := ListSongsByAlbum(db, item.AlbumID.String)
			if err != nil {
				return nil, err
			}
			for _, s := range songs {
				out = append(out, s.ID)
			}
		}
	}
	return out, nil
}