package music

import (
	"arimelody.me/arimelody.me/music/model"
	"github.com/jmoiron/sqlx"
)

// DATABASE

func GetTrack(db *sqlx.DB, id string) (*model.Track, error) {
    var track = model.Track{}

    stmt, _ := db.Preparex("SELECT * FROM musictrack WHERE id=$1")
    err := stmt.Get(&track, id)
	if err != nil {
        return nil, err
	}
    return &track, nil
}

func GetAllTracks(db *sqlx.DB) ([]*model.Track, error) {
    var tracks = []*model.Track{}

	err := db.Select(&tracks, "SELECT * FROM musictrack")
	if err != nil {
        return nil, err
	}

    return tracks, nil
}

func GetOrphanTracks(db *sqlx.DB) ([]*model.Track, error) {
    var tracks = []*model.Track{}

	err := db.Select(&tracks, "SELECT * FROM musictrack WHERE id NOT IN (SELECT track FROM musicreleasetrack)")
	if err != nil {
        return nil, err
	}

    return tracks, nil
}

func GetTracksNotOnRelease(db *sqlx.DB, release *model.Release) ([]*model.Track, error) {
	var tracks = []*model.Track{}

	err := db.Select(&tracks,
        "SELECT * FROM musictrack "+
        "WHERE id NOT IN "+
        "(SELECT track FROM musicreleasetrack WHERE release=$1)",
        release.ID)
	if err != nil {
		return nil, err
	}

	return tracks, nil
}

func GetTrackReleases(db *sqlx.DB, track *model.Track) ([]*model.Release, error) {
    var releases = []*model.Release{}

	err := db.Select(&releases,
        "SELECT musicrelease.* FROM musicrelease "+
        "JOIN musicreleasetrack ON release=id "+
        "WHERE track=$1 "+
        "ORDER BY release_date",
        track.ID,
    )
	if err != nil {
        return nil, err
	}

    return releases, nil
}

func PullOrphanTracks(db *sqlx.DB) ([]*model.Track, error) {
    var tracks = []*model.Track{}

	err := db.Select(&tracks,
        "SELECT id, title, description, lyrics, preview_url FROM musictrack "+
        "WHERE id NOT IN "+
        "(SELECT track FROM musicreleasetrack)",
    )
	if err != nil {
        return nil, err
	}

    return tracks, nil
}

func CreateTrack(db *sqlx.DB, track *model.Track) (string, error) {
    var trackID string
    err := db.QueryRow(
        "INSERT INTO musictrack (title, description, lyrics, preview_url) "+
        "VALUES ($1, $2, $3, $4) "+
        "RETURNING id",
        track.Title,
        track.Description,
        track.Lyrics,
        track.PreviewURL,
    ).Scan(&trackID)
    if err != nil {
        return "", err
    }

    return trackID, nil
}

func UpdateTrack(db *sqlx.DB, track *model.Track) error {
    _, err := db.Exec(
        "UPDATE musictrack "+
        "SET title=$2, description=$3, lyrics=$4, preview_url=$5 "+
        "WHERE id=$1",
        track.ID,
        track.Title,
        track.Description,
        track.Lyrics,
        track.PreviewURL,
    )
    if err != nil {
        return err
    }

    return nil
}

func DeleteTrack(db *sqlx.DB, track *model.Track) error {
    _, err := db.Exec(
        "DELETE FROM musictrack "+
        "WHERE id=$1",
        track.ID,
    )
    if err != nil {
        return err
    }

    return nil
}