# -*- coding: utf-8 -*-
import codecs, getopt, MySQLdb, os, random
import string, subprocess, sys, time

AMAROK_MUSIC_HOME = "/home/pub/audio"

VERSION  = "0.0.1"
DEBUG_FLAG = "false"
VERBOSE_FLAG = "false"
PRETEND_FLAG = "false"
SCRIPT_NAME = "pyCorrectStats-" + VERSION + ".py"

#######################################################################
# 
# Class for tweaking Amarok statistics
#
class CorrectAmarokStatistics:
  #######################################################################
  #
  # Update the statistics table
  #
  # For the given (Amarok) database connection, increase the playcount by
  # the given bump number
  #
  def correctAudioPlaycountByRemovingOldDuplicates(self, dbConnection):
    cursor = dbConnection.cursor()
    try:     
      # http://www.hashmysql.org/wiki/Select_rows_in_table_A_that_are_not_in_table_B
      #
      # How do I select all rows in table `a` that is not in table `b`:
      # SELECT a.id FROM a LEFT JOIN b ON a.id = b.aid WHERE b.aid IS NULL;
      #  
      
      # SELECT statistics.playcount,statistics.score,statistics.rating FROM statistics 
      # LEFT JOIN urls ON statistics.url = urls.id WHERE statistics.deleted = 0 AND 
      # urls.rpath = './home/pub/audio/100dbs/100dBs - 40 Minutes to Freedom, The Sublime Mix.mp3';
      
      findMismatchOfStatisticsAndUrl = """ SELECT urls.rpath,statistics.id FROM urls LEFT JOIN """ +\
      """statistics ON urls.id = statistics.url WHERE statistics.deleted = 0 AND urls.rpath REGEXP '^\.""" + \
      AMAROK_MUSIC_HOME + """'"""
      
      self.executeSelectStatement(cursor, findMismatchOfStatisticsAndUrl)
      verbose("Number of Mismatched Items : " + str(cursor.rowcount))
      
      # Look at the URL and see if the rpath does NOT start with ./home/pub.
      # If that is the case, the check if there is statistic for the same path
      # but does have ./home/pub/audio at the beginning.
      
      itemsToCheck =  cursor.fetchall()
      for item in itemsToCheck:
        oldRpath = str(item[0])
        oldRpath = str.replace(oldRpath, "'", "\\'.")
        oldStatsId = str(item[1])
        debug("Examining: " + oldRpath)

        newStylePath = str.replace(oldRpath, "." + AMAROK_MUSIC_HOME + "/", "./")
        
        findOldStatisticNumbers = """SELECT statistics.playcount,statistics.score,statistics.rating,urls.deviceid FROM statistics """ \
        + """LEFT JOIN urls ON statistics.url = urls.id WHERE statistics.deleted = 0 AND """ \
        + """urls.rpath = '""" + oldRpath + """'"""
        self.executeSelectStatement(cursor, findOldStatisticNumbers)
        if (0 < cursor.rowcount):
          verbose("Number of old stats for '" + oldRpath + "' : " + str(cursor.rowcount))
          oldStatsToMerge =  cursor.fetchall()
          oldPlaycount = oldStatsToMerge[0][0]
          oldScore = oldStatsToMerge[0][1]
          oldRating = oldStatsToMerge[0][2]
          oldDeviceId = oldStatsToMerge[0][3]
          
          findNewStatisticNumbers = """SELECT statistics.playcount,statistics.score,statistics.rating,statistics.id FROM statistics """ \
          + """LEFT JOIN urls ON statistics.url = urls.id WHERE statistics.deleted = 0 AND """ \
          + """urls.rpath = '""" + newStylePath + """'"""
          self.executeSelectStatement(cursor, findNewStatisticNumbers)
          
          if (1 == cursor.rowcount):
            verbose("Number of new stats for '" + newStylePath + "' : " + str(cursor.rowcount))
            newStatsToMerge =  cursor.fetchall()
            newPlaycount = newStatsToMerge[0][0]
            newScore = newStatsToMerge[0][1]
            newRating = newStatsToMerge[0][2]
            newId = newStatsToMerge[0][3]
            verbose("Number of new stats for '" + newStylePath + "' : " + str(cursor.rowcount))
            
            #
            # 1) Update the old stat to be deleted
            #
            markOldStatAsDeleted = """UPDATE statistics SET statistics.deleted = 1 WHERE statistics.id = """ + str(oldStatsId)
            self.executeUpdateStatement(cursor, markOldStatAsDeleted)
            
            #
            # 2) Merge the statistics into the new stat
            #
            finalPlaycount = oldPlaycount + newPlaycount
            finalScore = 10
            if (oldScore != None):
              finalScore = oldScore
            if (newScore != None):
              finalScore = (finalScore + newScore)/2
            finalRating = (oldRating + newRating)/2
              
            mergeStatsIntoNewRecord = """UPDATE statistics SET statistics.playcount = """ + str(finalPlaycount) 
            mergeStatsIntoNewRecord = mergeStatsIntoNewRecord + """, statistics.score = """ + str(finalScore)
            mergeStatsIntoNewRecord = mergeStatsIntoNewRecord + """, statistics.rating = """ + str(finalRating)
            mergeStatsIntoNewRecord = mergeStatsIntoNewRecord + """ WHERE statistics.id = """ + str(newId) + """;"""
            
            self.executeUpdateStatement(cursor, mergeStatsIntoNewRecord)
      
      # outside of loop
      #
      # 3) delete if deviceid equals zero
      #
      deleteDeviceIdNegativeOne = """DELETE urls FROM urls LEFT JOIN statistics ON urls.id = statistics.url WHERE urls.deviceid = -1"""
      self.executeDeleteStatement(cursor, deleteDeviceIdNegativeOne)
      verbose("number of deleted tracks: " + str(cursor.rowcount))
      
    except Exception as detail:
      warning(str(detail))
    finally:
      debug("Closing the cursor")
      cursor.close()
    return
  
  #######################################################################
  #
  # Run a SQL DELETE statement
  #
  def executeDeleteStatement(self, cursor, statement):
    debug("Running DELETE = '" + statement +"'")
    if PRETEND_FLAG == 'false':
      cursor.execute (statement)
      debug("Executed DELETE = '" + statement +"'")
    debug("Number of rows found from DELETE: " + str(cursor.rowcount)) 
  

#######################################################################
  
  #######################################################################
  #
  # Run a SQL INSERT statement
  #
  def executeInsertStatement(self, cursor, statement):
    debug("Running INSERT = '" + statement +"'")
    if PRETEND_FLAG == 'false':
      cursor.execute (statement)
      debug("Executed INSERT = '" + statement +"'")
    debug("Number of rows found from INSERT: " + str(cursor.rowcount))
    return
  
  #######################################################################
  #
  # Run a SQL SELECT statement
  #
  def executeSelectStatement(self, cursor, statement):
    debug("Running SELECT = '" + statement +"'") 
    cursor.execute (statement)
    debug("Executed SELECT = '" + statement +"'") 
    debug("Number of rows found from SELECT: " + str(cursor.rowcount))
    return
  
  #######################################################################
  #
  # Run a SQL UPDATE statement
  #
  def executeUpdateStatement(self, cursor, statement):
    debug("Running UPDATE = '" + statement +"'")
    if PRETEND_FLAG == 'false':
      cursor.execute (statement)
      debug("Executed UPDATE = '" + statement +"'")
    debug("Number of rows found from UPDATE: " + str(cursor.rowcount)) 
  

#######################################################################
#
# Establish a connection to the Amarok database
#
def connectToDatabase(machineName, databaseName, username, password):
  debug("\n\rEntering connectToDatabase()")
  debug("  hostname= " + machineName)
  debug("  databaseName = " + databaseName)
  debug("  username = " + username)
  debug("  password = <Ssssh>")
  return MySQLdb.connect (host = machineName,
                           user = username,
                           passwd = password,
                           db = databaseName) 

#######################################################################
#
# Prints debugging messages (if the debugging flag has been set)
#
def debug(message):
  if DEBUG_FLAG == "true":
    print(message)

#######################################################################
#
# Prints verbose messages (if the debugging flag has been set)
#
def verbose(message):
  if VERBOSE_FLAG == "true" or DEBUG_FLAG == "true":
    print(message)

def correctAmarokStats(connection):
  bump = CorrectAmarokStatistics()
  bump.correctAudioPlaycountByRemovingOldDuplicates(connection)

#######################################################################
#
# Prints warning messages to STDERR
#
def warning(message):
  print >> sys.stderr, "WARNING : " + message

#######################################################################
#
# Print Usage Information
#
def usage():
  print("")
  print("Usage information:")
  print("")
  print("Before proceeding, open the script and replace the variable AMAROK_MUSIC_HOME ")
  print("with your local path on disk.")
  print("")
  print("Also, update the MySQL database connection information.")
  print("")
  print("\t" + SCRIPT_NAME)
  print("")

#######################################################################
#
# Start of the 'main()' function
# 
def main():
  #######################################################################
  #
  # Read Command-Line
  #
  global SCRIPT_NAME
  SCRIPT_NAME = sys.argv[0]
  try:
    opts, args = getopt.getopt(sys.argv[1:], "hdvp", \
                 ["help", "debug", "verbose", "pretend"])
  except getopt.GetoptError, err:
    # print help information and exit:
    warning(str(err))
    usage()
    sys.exit(2)
  #######################################################################
  #
  # Parse Command-Line Options and Arguments
  # 
  playCountBump = 0
  for o, a in opts:
    if o in ("-h", "--help"):
      usage()
      sys.exit()
    elif o in ("-d", "--debug"):
      global DEBUG_FLAG
      DEBUG_FLAG = "true"
    elif o in ("-v", "--verbose"):
      global VERBOSE_FLAG
      VERBOSE_FLAG = "true"
    elif o in ("-p", "--pretend"):
      global PRETEND_FLAG
      PRETEND_FLAG = "true"
    else:
      assert False, "unhandled option"
    
  #######################################################################
  #
  # Sanity checks 
  #
  
  #
  # Remove the following lines before using
  #
  usage()
  sys.exit(0)
  
  debug(str(args))
  if len(args) != 0:
    warning("No non-switch command-line arguments allowed")
    usage()
  else:
    debug("\n\rConnecting to database")
    connection = connectToDatabase("localhost", "amarok_toy", "amarokuser", "amarokpasswd")
    
    try:
      correctAmarokStats(connection)
    finally:
      debug("Closing connection to database")
      connection.close()

if __name__ == "__main__":
    main()