#!/usr/local/bin/python
import sys, os, re
import getopt, time, socket
import xml.dom.minidom, Debug

dbName = "iTunesTracks"
tableName = "tracks"
outputFileName = "analysis"

def main():
    if (os.path.exists(outputFileName)):
        os.remove(outputFileName)
    outputFile = open(outputFileName, 'a')
    outputFile.write("Date of analysis: %s\n" % time.asctime())
    outputFile.close()
    executeCommand("SELECT foo.averagerating, foo.stdDev, foo.numRatedTracks, bar.totalTracks, baz.trackstorate FROM (SELECT AVG(rating/20.0) AS averagerating, STDDEV(rating/20.0) AS stdDev, COUNT(*) AS numRatedTracks FROM tracks WHERE rating > 20) AS foo, (SELECT COUNT(*) AS totalTracks FROM tracks) AS bar, (SELECT COUNT(*) AS trackstorate FROM tracks WHERE rating = 0 AND (genre IS NULL OR genre<>'Books & Spoken')) AS baz;", "Average rating of all tracks")
    executeCommand("SELECT rating/20 AS rating, COUNT(*) AS numTracks FROM tracks GROUP BY rating ORDER BY rating;", "Number of rated tracks")
    executeCommand("SELECT SUM(playcount*length)/(3600*24) AS days, (SUM(playcount*length)-((3600*24)*(SUM(playcount*length)/(3600*24))))/3600 AS hours, (SUM(playcount*length)-(3600*(SUM(playcount*length)/3600)))/60 AS minutes, (SUM(playcount*length)-(60*(SUM(playcount*length)/60))) AS seconds FROM tracks;", "Total time listened to music")
    executeCommand("SELECT SUM(length)/(3600*24) AS days, (SUM(length)-((3600*24)*(SUM(length)/(3600*24))))/3600 AS hours, (SUM(length)-(3600*(SUM(length)/3600)))/60 AS minutes, (SUM(length)-(60*(SUM(length)/60))) AS seconds FROM tracks;", "Total time of music in library")
    executeCommand("SELECT SUM(length)/(3600*24) AS days, (SUM(length)-((3600*24)*(SUM(length)/(3600*24))))/3600 AS hours, (SUM(length)-(3600*(SUM(length)/3600)))/60 AS minutes, (SUM(length)-(60*(SUM(length)/60))) AS seconds FROM tracks WHERE rating = 0 AND (genre IS NULL OR genre <> 'Books & Spoken');", "Total time of unrated music")
    executeCommand("SELECT * FROM (SELECT artist, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM tracks WHERE rating > 20 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM tracks GROUP BY artist) AS bar USING (artist) WHERE numTracks > 4 ORDER BY foo.averageRating DESC, foo.numTracks DESC;", "Highest average rated artists with more than four tracks rated")
    executeCommand("SELECT * FROM (SELECT artist, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM tracks WHERE rating > 20 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM tracks GROUP BY artist) AS bar USING (artist) WHERE numTracks > 1 AND numTracks < 5 ORDER BY foo.averageRating DESC, foo.numTracks DESC;", "Highest average rated artists with two to four tracks rated")
    executeCommand("SELECT * FROM (SELECT album, COUNT(*) AS numTracks, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev FROM tracks WHERE rating > 20 GROUP BY album) AS foo INNER JOIN (SELECT album, COUNT(*) AS totalTracks, TOPARTIST(album) AS mainartist FROM tracks GROUP BY album) AS bar USING (album) WHERE numTracks > 1 ORDER BY foo.averageRating DESC, foo.numTracks DESC;", "Highest average rated albums with at least one tracks rated") 
    executeCommand("SELECT bar.genre, foo.averageRating, foo.stdDev, foo.numRatedTracks, bar.totalTracks FROM (SELECT genre, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev, COUNT(*) AS numRatedTracks FROM tracks WHERE rating > 20 GROUP BY genre) AS foo RIGHT JOIN (SELECT genre, COUNT(*) AS totalTracks FROM tracks GROUP BY genre) AS bar ON (foo.genre=bar.genre OR (foo.genre IS NULL AND bar.genre IS NULL)) ORDER BY averageRating DESC;", "Genres in rating order")
    executeCommand("SELECT * FROM (SELECT artist, COUNT(*) AS num5StarTracks FROM tracks WHERE rating = 100 GROUP BY artist) AS foo INNER JOIN (SELECT artist, COUNT(*) AS totalTracks FROM tracks GROUP BY artist) AS bar USING (artist) ORDER BY num5StarTracks DESC, totalTracks;", "Artists with most 5 star tracks")
    executeCommand("SELECT * FROM (SELECT album, COUNT(*) AS num5StarTracks FROM tracks WHERE rating = 100 GROUP BY album) AS foo INNER JOIN (SELECT album, COUNT(*) AS totalTracks, TOPARTIST(album) AS mainartist FROM tracks GROUP BY album) AS bar USING (album) ORDER BY num5StarTracks DESC, totalTracks;", "Albums with most 5 star tracks")
    executeCommand("SELECT bar.comments, foo.averageRating, foo.stdDev, foo.numRatedTracks, bar.totalTracks FROM (SELECT comments, ROUND(AVG(rating/20.0),5) AS averageRating, ROUND(STDDEV(rating/20.0),5) AS stdDev, COUNT(*) AS numRatedTracks FROM tracks WHERE rating > 20 GROUP BY comments) AS foo RIGHT JOIN (SELECT comments, COUNT(*) AS totalTracks FROM tracks GROUP BY comments) AS bar ON (foo.comments=bar.comments OR (foo.comments IS NULL AND bar.comments IS NULL)) WHERE foo.numRatedTracks > 0 ORDER BY averageRating DESC;", "Comments in rating order")
    executeCommand("SELECT * FROM (SELECT artist, COUNT(*) AS numTracks FROM tracks GROUP BY artist) AS foo WHERE numTracks > 1 ORDER BY numTracks DESC;", "Artists with more than one song in order of number of songs")
    executeCommand("SELECT SUM(playcount) FROM tracks;", "Total number of tracks played")
    executeCommand("SELECT SUM(playcount*length)/(3600*24) AS days, (SUM(playcount*length)-((3600*24)*(SUM(playcount*length)/(3600*24))))/3600 AS hours, (SUM(playcount*length)-(3600*(SUM(playcount*length)/3600)))/60 AS minutes, (SUM(playcount*length)-(60*(SUM(playcount*length)/60))) AS seconds FROM tracks;", "Total time listened to music")

def executeCommand(sqlQuery, description):
    outputFile = open(outputFileName, 'a')
    outputFile.write(description + "\n")
    outputFile.close()
    commandLine = "psql -d %s -c \"%s\" >> %s" % (dbName, sqlQuery, outputFileName)
    os.system(commandLine)


if (__name__ == '__main__'):
    main()