go-music-player/gmus-backend/pkg/repository/player.go

package repository

import (
	"database/sql"

	"github.com/jmoiron/sqlx"
)

func getSongIdOrZero(db *sqlx.DB, query string, songId int64) (assocSongId int64, err error) {
	err = db.QueryRowx(query, songId).Scan(&assocSongId)
	if err != nil && err == sql.ErrNoRows {
		err = nil
		assocSongId = 0
	}
	return
}

func GetNextSongId(db *sqlx.DB, prevSongId int64) (nextSongId int64, err error) {
	nextSongId, err = getSongIdOrZero(
		db,
		`
    select coalesce(id_next, 0) as id from (
      select
        prev.track_number, prev.title, prev.artist, prev.album
          ,coalesce(next_artist.id, prev.id_next) as id_next
      from (
        select
          prev.track_number, prev.title, prev.artist, prev.album
            ,coalesce(next_album.id, prev.id_next) as id_next
        from (
          select
            prev.track_number, prev.title, prev.artist, prev.album
              ,coalesce(next_title.id, prev.id_next) as id_next
          from (
            select
              prev.track_number, prev.title, prev.artist, prev.album
                ,next_track_number.id as id_next
            from (
              select track_number, title, artist, album from songs
              where id = $1
            ) prev

            left join songs next_track_number on 1=1
              and next_track_number.artist = prev.artist
              and next_track_number.album = prev.album
              and next_track_number.track_number > prev.track_number

            order by next_track_number.track_number
            limit 1

          ) prev

          left join songs next_title on 1=1
            and prev.id_next is null
            and next_title.artist = prev.artist
            and next_title.album = prev.album
            and next_title.track_number is null
            and next_title.title > prev.title

          order by next_title.title
          limit 1

        ) prev

        left join songs next_album on 1=1
          and prev.id_next is null
          and next_album.artist = prev.artist
          and next_album.album > prev.album

        order by next_album.album, next_album.track_number, next_album.title
        limit 1
      ) prev

      left join songs next_artist on 1=1
        and prev.id_next is null
        and next_artist.artist > prev.artist

      order by next_artist.artist, next_artist.album, next_artist.track_number, next_artist.title
      limit 1
    ) result
    `,
		prevSongId,
	)
	return
}

func GetPrevSongId(db *sqlx.DB, nextSongId int64) (prevSongId int64, err error) {
	prevSongId, err = getSongIdOrZero(
		db,
		`
    select coalesce(id_prev, 0) as id from (
      select
        next.track_number, next.title, next.artist, next.album
          ,coalesce(prev_artist.id, next.id_prev) as id_prev
      from (
        select
          next.track_number, next.title, next.artist, next.album
            ,coalesce(prev_album.id, next.id_prev) as id_prev
        from (
          select
            next.track_number, next.title, next.artist, next.album
              ,coalesce(prev_title.id, next.id_prev) as id_prev
          from (
            select
              next.track_number, next.title, next.artist, next.album
                ,prev_track_number.id as id_prev
            from (
              select track_number, title, artist, album from songs
              where id = $1
            ) next

            left join songs prev_track_number on 1=1
              and prev_track_number.artist = next.artist
              and prev_track_number.album = next.album
              and prev_track_number.track_number < next.track_number

            order by prev_track_number.track_number desc
            limit 1

          ) next

          left join songs prev_title on 1=1
            and next.id_prev is null
            and prev_title.artist = next.artist
            and prev_title.album = next.album
            and prev_title.track_number is null
            and prev_title.title < next.title

          order by prev_title.title desc
          limit 1

        ) next

        left join songs prev_album on 1=1
          and next.id_prev is null
          and prev_album.artist = next.artist
          and prev_album.album < next.album

        order by prev_album.album desc, prev_album.track_number desc, prev_album.title desc
        limit 1
      ) next

      left join songs prev_artist on 1=1
        and next.id_prev is null
        and prev_artist.artist < next.artist

      order by prev_artist.artist desc, prev_artist.album desc, prev_artist.track_number desc, prev_artist.title desc
      limit 1
    ) result
    `,
		nextSongId,
	)
	return
}