import os

import sqlite

def quote_sql(str):

    return str.replace("'", "''")

class IMMSDb:

    def __init__(self, dbname = None):

        if not dbname:

            dbname = os.environ['HOME'] + '/.imms/imms.db'

        # autocommit = 1 disable autocommit!

        self.cx = sqlite.connect(dbname, autocommit = 1,

                                 timeout = 2, encoding = ('utf-8', 'replace'))

        self.cu = self.cx.cursor()

    def get_library_entries(self, **kw):

        qry = "SELECT path, uid, sid FROM Library";

        first = 1

        for key in kw.keys():

            if first:

                qry += " WHERE "

                first = 0

            else:

                qry += " AND "

            if key in ['uid', 'sid']:

                qry += "%s = %d" % (key, kw[key])

            else:

                qry += "%s = '%s'" % (key, quote_sql(kw[key]))

        qry += ";"

        self.cu.execute(qry)

        return self.cu.fetchall()

    def update_filename(self, oldname, newname):

        self.cu.execute("""UPDATE Library

            SET path = '%s'

            WHERE path = '%s';""" % (quote_sql(newname),

                                     quote_sql(oldname)))

    def erase_filename(self, name):

        self.cu.execute("""DELETE FROM Library

            WHERE path = '%s';""" % quote_sql(name))

    def erase_uid(self, uid):

        self.cu.execute("""BEGIN TRANSACTION;

             DELETE FROM Library WHERE uid = %d;

             DELETE FROM Rating WHERE uid = %d;

             DELETE FROM Acoustic WHERE uid = %d;

             COMMIT;""" % (uid, uid, uid))

    def erase_sid(self, sid):

        self.cu.execute("""BEGIN TRANSACTION;

              DELETE FROM Library WHERE sid = %d;

              DELETE FROM Info WHERE sid = %d;

              DELETE FROM Last WHERE sid = %d;

              COMMIT;""")

    def erase_path(self, path):

        self.cu.execute("DELETE FROM Library WHERE path = '%s';" \

                        % quote_sql(path))

    def get_ratings(self, min = 0, max = 250):

        self.cu.execute('''SELECT Rating.uid, Rating.rating 

                   FROM Rating

                   WHERE Rating.rating >= %d

                   AND Rating.rating <= %d

                   ORDER BY Rating.rating;''' % (min, max))

        return cu.fetchall()

    def get_acoustics(self, uids = None):

        qry = "SELECT uid, bpm. spectrum FROM Acoustic"

        first = 1

        for uid in uids:

            if first:

                qry += ' WHERE'

                first = 0

            else:

                qry += ' OR'

            qry += " uid = %d" % uid

        qry += ';'

        self.cu.execute(qry)

        return cu.fetchall()

    def get_infos(self, sids = None):

        qry = "SELECT sid, artist, title FROM Infos"

        first = 1

        for sid in sids:

            if first:

                qry += ' WHERE'

                first = 0

            else:

                qry += ' OR'

            qry += " sid = %d" % id

        qry += ';'

        self.cu.execute(qry)

        return cu.fetchall()

    def get_last(self, sids = None):

        qry = "SELECT sid, last FROM Last"

        first = 1

        for sid in sids:

            if first:

                qry += ' WHERE'

                first = 0

            else:

                qry += ' OR'

            qry += " sid = %d" % id

        qry += ';'

        self.cu.execute(qry)

        return cu.fetchall()

    def get_uid_by_path(self, path):

        entries = self.get_library_entries(path = path)

        return map(lambda x: x[1], entries)

    def get_ratings_and_info(self, uids = None):

        cu = self.cx.cursor()

        qry = '''SELECT l.uid, r.rating, l.path, ls.last

                   FROM Library l, Rating r, Last ls

                   WHERE l.uid = r.uid AND l.sid = ls.sid'''

        if uids:

            qry += ' AND (l.uid = %d' % (uids.pop())

            for uid in uids:

                qry += ' OR l.uid = %d' % uid

            qry += ')'

        qry += ';'

        cu.execute(qry)

        # Better to fetch everything since locking can really mess

        # things in imms plugin.

	results = {}

	tune = cu.fetchone()

        while tune:

            try:

                uid = int(tune[0])

                if results.has_key(uid):

                    results[uid]['path'].append(

                        tune[2].decode('utf-8', 'replace'))

                else:

                    results[uid] = {

                        'rating' : int(tune[1]),

                        'path' : [ tune[2].decode('utf-8', 'replace') ],

                        'last' : int(tune[3])}

            except UnicodeDecodeError:

                print tune[2]

	    tune = cu.fetchone()

        return results