#!/bin/sh
#
# $Log: romprhist.in,v $
# Revision 1.8  2022-06-20 01:07:05+05:30  Cprogrammer
# use directories set in ./configure
#
# Revision 1.7  2021-04-11 15:00:46+05:30  Cprogrammer
# refactored code
#
# Revision 1.6  2020-08-30 11:35:06+05:30  Cprogrammer
# fixed stats.db path
#
# Revision 1.5  2020-07-22 15:51:43+05:30  Cprogrammer
# removed unused function
#
# Revision 1.4  2020-07-21 15:36:14+05:30  Cprogrammer
# renamed Rate field to Starts
#
# Revision 1.3  2020-07-21 12:33:29+05:30  Cprogrammer
# fixed minor formatting issue with output
#
# Revision 1.2  2020-07-20 22:15:18+05:30  Cprogrammer
# warn if rompr.conf is missing
#
# Revision 1.1  2020-07-20 21:45:03+05:30  Cprogrammer
# Initial revision
#
#
# utility to update 
#
# $Id: romprhist.in,v 1.8 2022-06-20 01:07:05+05:30 Cprogrammer Exp mbhangui $
#
usage()
{
  (
  echo "rompr: incorrect usage"
  echo "USAGE: romprhist [--update-stats] [--update-sticker] [--sql] [--update-checkpoint]"
  echo "options"
  echo " --update-stats    - updates stats  sqlite db"
  echo " --update-sticker  - updates sticker sqlite db"
  echo " --update-chkpoint - reset checkpoint"
  echo " --sql             - generate sql statements in /tmp/stats.sql, /tmp/sticker.sql"
  ) 1>&2
  exit 1
}

get_mpd_conf_value()
{
  grep "^$1" /etc/mpd.conf|awk '{print $2}' | \
  sed -e 's{"{{g'
}

init_rompr()
{
  (
  echo -n "CREATE TABLE IF NOT EXISTS ratingchkpoint "
  echo -n "(id INT NOT NULL PRIMARY KEY, zztimestamp INT NOT NULL);"
  echo -n "INSERT INTO ratingchkpoint (id,zztimestamp) values (1, UNIX_TIMESTAMP(now()))"
  echo -n "   ON DUPLICATE KEY UPDATE zztimestamp=UNIX_TIMESTAMP(now());"
  echo
  echo -n "ALTER TABLE Ratingtable ADD COLUMN zztimestamp TIMESTAMP DEFAULT "
  echo -n "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP not null;"
  echo
  ) | mysql -s -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST $MYSQL_DATABASE 2>/dev/null
  if [ $? -ne 0 ] ; then
    exit 1
  fi
}

chk_ratingchkpoint()
{
  echo "SELECT COUNT(*) from ratingchkpoint;" | mysql -s -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST $MYSQL_DATABASE > /dev/null 2>&1
  if [ $? -ne 0 ] ; then
    echo "initializing"
    init_rompr
  fi
}

update_chkpoint()
{
  (
  if [ $# -eq 1 ] ; then
    echo -n "INSERT INTO ratingchkpoint (id, zztimestamp) VALUES (1, $1)"
    echo " ON DUPLICATE KEY UPDATE zztimestamp=$1;"
  else
    echo -n "INSERT INTO ratingchkpoint (id, zztimestamp) VALUES (1, UNIX_TIMESTAMP(now()))"
    echo " ON DUPLICATE KEY UPDATE zztimestamp=UNIX_TIMESTAMP(now());"
  fi
  ) | mysql -s -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST $MYSQL_DATABASE 2>/dev/null
  if [ $? -ne 0 ] ; then
    (
    if [ $# -eq 1 ] ; then
      echo -n "ERROR1: INSERT INTO ratingchkpoint (id, zztimestamp) VALUES (1, $1)"
      echo " ON DUPLICATE KEY UPDATE zztimestamp=$1;"
    else
      echo -n "ERROR2: INSERT INTO ratingchkpoint (id, zztimestamp) VALUES (1, now())"
      echo " ON DUPLICATE KEY UPDATE zztimestamp=UNIX_TIMESTAMP(now());"
    fi
    ) 1>&2
  fi
}

get_rompr()
{
  (
  echo "SELECT r.TTindex, p.Lastplayed, p.Playcount, r.Rating, t.Uri FROM Ratingtable r, Tracktable t, Playcounttable p, "
  echo "  ratingchkpoint u WHERE r.TTindex = t.TTindex and p.TTindex = t.TTindex AND UNIX_TIMESTAMP(r.zztimestamp) > u.zztimestamp"
  echo "  ORDER BY r.zztimestamp asc;"
  ) | mysql -s -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST $MYSQL_DATABASE 2>/dev/null
  if [ $? -ne 0 ] ; then
    (
    echo "SELECT r.TTindex, r.Rating, t.Uri from Ratingtable r, Tracktable t,"
    echo "  ratingchkpoint u WHERE r.TTindex = t.TTindex AND UNIX_TIMESTAMP(r.zztimestamp) > u.zztimestamp"
    echo "  ORDER BY r.zztimestamp asc;"
    ) 1>&2
  fi
}

update_stats()
{
  uri=`echo "$1" | sed -e "s{'{''{g"`
  if [ $sql_mode -eq 1 ] ; then
    echo "UPDATE song SET rating=$2 WHERE uri='$uri';"
  else
    echo "UPDATE song SET rating=$2 WHERE uri='$uri';" | sqlite3 $stats_file
    if [ $? -ne 0 ] ; then
      echo "error: UPDATE song SET rating=$2 WHERE uri='$uri';" 1>&2
      return 1
    fi
  fi
}

update_sticker()
{
  uri=`echo "$1" | sed -e "s{'{''{g"`
  if [ $sql_mode -eq 1 ] ; then
    echo "UPDATE sticker SET value=$2 WHERE uri='$uri';"
  else
    echo "UPDATE sticker SET value=$2 WHERE uri='$uri';" | sqlite3 $sticker_file
    if [ $? -ne 0 ] ; then
      echo "UPDATE sticker SET value=$2 WHERE uri='$uri';" 1>&2
      return 1
    fi
  fi
}

music_dir=`get_mpd_conf_value music_directory`
if [ $? -ne 0 ] ; then
  echo "could not get music directory from mpd.conf" 1>&2
  exit 1
fi
sticker_file=`get_mpd_conf_value sticker_file`
if [ $? -ne 0 ] ; then
  echo "could not get sticker database from mpd.conf" 1>&2
  exit 1
fi
stats_dir=$(dirname $sticker_file)
stats_file=$stats_dir/stats.db
if [ ! -f $HOME/.mpdev/rompr.conf ] ; then
  echo "$HOME/.mpdev/rompr.conf missing" 1>&2
  exit 1
fi
MYSQL_HOST=`grep MYSQL_HOST $HOME/.mpdev/rompr.conf |awk '{print $2}'`
MYSQL_USER=`grep MYSQL_USER $HOME/.mpdev/rompr.conf |awk '{print $2}'`
MYSQL_PASS=`grep MYSQL_PASS $HOME/.mpdev/rompr.conf |awk '{print $2}'`
MYSQL_DATABASE=`grep MYSQL_DATABASE $HOME/.mpdev/rompr.conf |awk '{print $2}'`

update_stats_table=0
update_sticker_table=0
sql_mode=0
chkpoint=0
verbose=0
while test $# -gt 0; do
  case "$1" in
    -*=*)
    optarg=`echo "$1" | sed 's/[-_a-zA-Z0-9]*=//'`
    optval=`echo "$1" | cut -d'=' -f1`
    ;;
    *)
    optarg=
    ;;
  esac

  case "$1" in
    --sql)
    sql_mode=1
    ;;
    --update-stats)
    update_stats_table=1
    sql_mode=1
    ;;
    --update-sticker)
    update_sticker_table=1
    sql_mode=1
    ;;
    --update-chkpoint)
    chkpoint=1
    ;;
    --verbose)
    verbose=1
    ;;
    *)
    usage
    ;;
  esac
  shift
done

if [ $update_stats_table -eq 0 -a $update_sticker_table -eq 0 ] ; then
  verbose=1
fi

chk_ratingchkpoint
if [ $sql_mode -eq 1 ] ; then
  if [ $update_stats_table -eq 1 ] ; then
    /bin/rm -f /tmp/stats.sql
  fi
  if [ $update_sticker_table -eq 1 ] ; then
    /bin/rm -f /tmp/sticker.sql
  fi
fi
status1=1
status2=1
printf "%-6s %-19s %s %s %s\n" "Index" "End Time" "Count" "Stars" "Filename"
get_rompr | while read line
do
  set $line
  TTindex=$1
  pdate=$2
  ptime=$3
  pcount=$4
  rating=$5
  shift 5
  uri="$*"
  if [ $verbose -eq 1 ] ; then
    printf "%06d %s %s %02d  %d   %s\n" $TTindex $pdate $ptime $pcount $rating "$uri"
  fi
  rating=`expr $rating \* 2`
  if [ $sql_mode -eq 1 ] ; then
    update_stats   "$uri" $rating >> /tmp/stats.sql
    status1=$?
    update_sticker "$uri" $rating >> /tmp/sticker.sql
    status2=$?
  fi
  if [ $chkpoint -eq 1 -a $status1 -eq 0 -a $status2 -eq 0 ] ; then
    update_chkpoint $tmval
  fi
done
if [ $update_stats_table -eq 1 ] ; then
  sqlite3 $stats_file < /tmp/stats.sql
fi
if [ $update_sticker_table -eq 1 ] ; then
  sqlite3 $sticker_file < /tmp/sticker.sql
fi
if [ $chkpoint -eq 1 ] ; then
  update_chkpoint
fi
