#!/bin/bash -
#===============================================================================
#
#         FILE: show_queue
#
#        USAGE: ./show_queue
#
#  DESCRIPTION: Show the pending queue, expanding each album's details from
#               the database.
#
#               / This version calls sqlite3 repeatedly in a loop /
#
#      OPTIONS: ---
# REQUIREMENTS: ---
#         BUGS: ---
#        NOTES: ---
#       AUTHOR: Dave Morriss (djm), Dave.Morriss@gmail.com
#      VERSION: 0.1.1
#      CREATED: 2020-09-15 12:38:03
#     REVISION: 2020-09-15 12:38:11
#
#===============================================================================

set -o nounset                              # Treat unset variables as an error

SCRIPT=${0##*/}
#DIR=${0%/*}

VERSION='0.1.1'

#===  FUNCTION  ================================================================
#          NAME:  cleanup_temp
#   DESCRIPTION:  Cleanup temporary files when a 'trap' command is triggered
#    PARAMETERS:  * - names of temporary files to delete
#       RETURNS:  Nothing
#===============================================================================
function cleanup_temp {
    for tmp in "$@"; do
        [ -e "$tmp" ] && rm --force "$tmp"
    done
    exit 0
}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

STDOUT="/dev/fd/2"

#
# Files and directories
#
BASEDIR="$HOME/MusicDownloads"
DATADIR="$BASEDIR/Magnatune_Data"
SCRIPTDIR="$BASEDIR/magnatune-downloader"

QUEUE="$SCRIPTDIR/pending"
DB="$DATADIR/sqlite_normalized.db"

#
# Sanity checks
#
[ -e "$QUEUE" ] || { echo "$QUEUE not found"; exit 1; }

#
# Check the queue contains data
#
if [[ ! -s $QUEUE ]]; then
    echo "$SCRIPT($VERSION): there is nothing in the queue"
    exit
fi

#
# Make temporary files and set traps to delete them
#
TMP1=$(mktemp) || { echo "$SCRIPT: creation of temporary file failed!" >$STDOUT; exit 1; }
trap 'cleanup_temp $TMP1' SIGHUP SIGINT SIGPIPE SIGTERM EXIT

RE='^http://magnatune.com/artists/albums/([A-Za-z0-9-]+)/?$'

#
# Add a partial SQL query that counts the feeds that match the regex. Store it
# in a temporary file
#
cat > "$TMP1" <<ENDSQL1
SELECT
    ar.name AS Artist,
    al.name AS Album,
    group_concat(distinct ge.name) AS 'Genre',
    group_concat(distinct sg.name) AS 'Subgenre',
    al.sku as Code
FROM albums al
JOIN artists ar ON al.artist_id = ar.artists_id
JOIN genres_albums ga on al.album_id = ga.album_id
JOIN genres ge ON ge.genre_id = ga.genre_id
JOIN subgenres_albums sa on al.album_id = sa.album_id
JOIN subgenres sg ON sg.subgenre_id = sa.subgenre_id
GROUP BY al.album_id
ENDSQL1

#
# Read and report the queue elements
#
n=0
while read -r URL; do
    ((n++))

    if [[ $URL =~ $RE ]]; then
        SKU="${BASH_REMATCH[1]}"
    else
        echo "Problem parsing URL in queue (line $n): $URL"
        continue
    fi

    #
    # Build the query by concatenating the partial SQL with the last line with
    # the variable contents in it. Use a sub-process to send both pieces of
    # text to sqlite3.
    #
    (cat "$TMP1"; echo "HAVING sku = '$SKU';") | sqlite3 -line "$DB"
    echo '--------'

done < "$QUEUE"

exit

# vim: syntax=sh:ts=8:sw=4:ai:et:tw=78:fo=tcrqn21

