#!/usr/bin/env python
#
# playlistport.pl - Program to import and export Mixxx playlists from an XML file.
# Apr 15, 2012 - Joe Hartley (jh@brainiac.com)
#    Based on crateport.pl by Phillip Whelan - https://github.com/pwhelan/mixxx-crateport
#
# Command line options:
#    -i, --import
#    -e, --export (default)
#    -d, --dbname <dbfilename>
#    -p, --playlist <playlist_name> (for export only)
#
# Note that minidom treats each newline as a separate child entity,
# so a 'pretty' XML file may need to be filtered through xmllint before it
# can be imported, like so:
#   xmllint --noblanks pretty.xml | python playlistport.py -i
# Also, this exports raw XML, so for readability you may want to pipe the output
# through xmllint like so:
#   python playlistport.py | xmllint --format

import sqlite3
import os
import sys
import xml.dom
import xml.dom.minidom
import platform
import fileinput
from optparse import OptionParser


def generatePlaylistXML(Playlists):
    dom = xml.dom.getDOMImplementation()
    document = dom.createDocument(None, None, None)
    nPlaylists = document.createElement('Playlists')
    document.appendChild(nPlaylists)
    
    for Playlistname in Playlists:
        nPlaylist = document.createElement('Playlist')
        nPlaylists.appendChild(nPlaylist)
        nPlaylist.setAttribute('name', Playlistname)
        
        for track in Playlists[Playlistname]:
            ntrack = document.createElement('track')
            nPlaylist.appendChild(ntrack)
            for key in track.keys():
                ntrack.setAttribute(key, unicode(track[key]))
    
    return document.toxml()

def getPlaylists(conn, plname):
    cursor = conn.cursor()
    Playlists = {}

    if plname == None:
        cursor.execute("SELECT id, name FROM Playlists")
    else:
        cursor.execute("""SELECT id, name FROM Playlists WHERE name = ?
                       """, [str(plname)])    
    row = cursor.fetchone()
    while row:
        Playlists[row['name']] = []
        
        cur2 = conn.cursor()
        ## JH - the ORDER BY clause was added
        cur2.execute("""
            SELECT
                library.artist AS artist,
                library.title AS title,
                track_locations.location,
                track_locations.filename
            
            FROM PlaylistTracks
                INNER JOIN library
                    ON PlaylistTracks.track_id = library.id
                INNER JOIN track_locations
                    ON library.location = track_locations.id
            WHERE
                PlaylistTracks.Playlist_id = ?
            ORDER BY track_locations.filename
            
            """, str(row['id']))
        
        track = cur2.fetchone()
        
        while track:
            Playlists[row['name']].append(track)
            track = cur2.fetchone()
        
        row = cursor.fetchone()
    
    return Playlists

def findTrack(conn, ntrack):
    location = ntrack.getAttribute('location')
    artist = ntrack.getAttribute('artist')
    title = ntrack.getAttribute('title')
    filename = ntrack.getAttribute('filename')
    
    cursor = conn.cursor()
    
    cursor.execute("""
        SELECT
            l.id,
            l.filetype
            FROM library l
            INNER JOIN track_locations tl
                ON l.location = tl.id
            WHERE 
                (tl.location = ?)
        """, (location,))
    
    track = cursor.fetchone()
    if track != None:
        return track
    
    cursor.execute("""
        SELECT
            l.id,
            l.filetype
            FROM library l
            INNER JOIN track_locations tl
                ON l.location = tl.id
            WHERE 
                (tl.filename = ?)
        """, (filename,))
    
    track = cursor.fetchone()
    if track != None:
        return track
    
    cursor.execute("""
        SELECT
            l.id,
            l.filetype
            FROM library l
            WHERE 
                (l.artist = ? AND l.title = ?)
        """, (artist, title))
    
    track = cursor.fetchone()
    if track != None:
        return track
    
    return None

def importPlaylistXML(conn, dPlaylist):
    cursor = conn.cursor()
    nPlaylists = dPlaylist.documentElement
    print(dPlaylist.documentElement) 
    if nPlaylists.tagName != 'Playlists':
        raise Exception('Not a Playlists XML File')
    
    for nPlaylist in nPlaylists.childNodes:
        if nPlaylist.tagName != 'Playlist':
            raise Exception('Not a Playlist')
        
        try:
            cursor.execute("INSERT INTO Playlists(name) VALUES(?)", 
                (nPlaylist.getAttribute('name'),))
            print "Creating new Playlist:", nPlaylist.getAttribute('name')
        except sqlite3.IntegrityError:
            print "Already Created:", nPlaylist.getAttribute('name')
        
        cursor.execute("SELECT id FROM Playlists WHERE name = ?", 
            (nPlaylist.getAttribute('name'),))
        Playlist = cursor.fetchone()
        
        for ntrack in nPlaylist.childNodes:
            if nPlaylist.tagName != 'Playlist':
                raise Exception('Not a Playlist')
            
            track = findTrack(conn, ntrack)
            if track != None:
                try:
                    print "Adding a Track"
                    cursor.execute("""
                        INSERT INTO PlaylistTracks(Playlist_id, track_id)
                        VALUES(?, ?)
                    """, (str(Playlist['id']), track['id']))
                except sqlite3.IntegrityError:
                    print "Track already in Playlist"

def main():
    home = os.path.expanduser('~')
    uname = platform.uname()
    if uname[0] == 'Darwin':
        cfgdir = home + '/Library/Application Support/Mixxx'
    elif uname[0] == 'Linux':
        cfgdir = home + '/.mixxx'
    
    defdb = cfgdir + '/mixxxdb.sqlite'

    opt = OptionParser(description='Import and Export Playlists from Mixxx')
    opt.add_option('-i', '--import', dest='export', action='store_false')
    opt.add_option('-e', '--export', dest='export', action='store_true')
    opt.add_option('-d', '--dbname', dest='dbname', default=defdb)
    opt.add_option('-p', '--playlist', dest='plname', default=None)
    
    (options, args) = opt.parse_args()

    if options.export == None: options.export = True;    
    conn = sqlite3.connect(options.dbname)
    conn.row_factory = sqlite3.Row
    
    if options.export == True:
        output = open(args[0], "w")  if len(args) > 0 else sys.stdout
        Playlists = getPlaylists(conn, options.plname)
        output.write(generatePlaylistXML(Playlists).encode('utf8') + "\n")
    else:
        input = open(args[0], "r") if len(args) > 0 else sys.stdin
        Playlists = xml.dom.minidom.parse(input)
        importPlaylistXML(conn, Playlists)
    
    conn.commit()
    conn.close()

if __name__ == '__main__':
    main()