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(prev.id_next, next_artist.id) as id_next
      from (
        select
          prev.track_number, prev.title, prev.artist, prev.album
            ,coalesce(prev.id_next, next_album.id) as id_next
        from (
          select
            prev.track_number, prev.title, prev.artist, prev.album
              ,coalesce(prev.id_next, next_title.id) as id_next
          from (
            select
              prev.track_number, prev.title, prev.artist, prev.album
                ,coalesce(prev.id_next, next_track_number.id) as id_next
            from (
              select
                prev.track_number, prev.title, prev.artist, prev.album
                  ,next_no_info.id as id_next

              from (
                select track_number, title, artist, album from songs
                where id = $1
              ) prev

              left join songs next_no_info on 1=1
                and prev.artist = ''
                and prev.album = ''
                and next_no_info.id > $1

              order by
                next_no_info.artist
                ,next_no_info.album
                ,next_no_info.track_number
                ,next_no_info.title
                ,next_no_info.id

              limit 1

            ) prev

            left join songs next_track_number on 1=1
              and prev.id_next is null
              and next_track_number.artist = prev.artist
              and next_track_number.album = prev.album
              and (
                (prev.track_number > 0 and next_track_number.track_number > prev.track_number)
                or (prev.track_number = 0 and next_track_number.id > $1)
              )

            order by next_track_number.track_number, next_track_number.id
            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_next, 0) as id from (
      select
        prev.track_number, prev.title, prev.artist, prev.album
          ,coalesce(prev.id_next, next_no_info.id) as id_next
      from (
        select
          prev.track_number, prev.title, prev.artist, prev.album
            ,coalesce(prev.id_next, next_artist.id) as id_next
        from (
          select
            prev.track_number, prev.title, prev.artist, prev.album
              ,coalesce(prev.id_next, next_album.id) as id_next
          from (
            select
              prev.track_number, prev.title, prev.artist, prev.album
                ,coalesce(prev.id_next, next_title.id) 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 desc
              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 desc
            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 desc
            ,next_album.track_number desc
            ,next_album.title desc
          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 desc
          ,next_artist.album desc
          ,next_artist.track_number desc
          ,next_artist.title desc
        limit 1
      ) prev

      inner join songs from_song on from_song.id = $1

      left join songs next_no_info on 1=1
        and prev.id_next is null
        and next_no_info.artist = ''
        and next_no_info.album = ''
        and next_no_info.id != $1
        and (from_song.artist != '' or from_song.album != '' or next_no_info.id < $1)

      order by next_no_info.id desc
      limit 1

    ) result
    `,
		nextSongId,
	)
	return
}