import os.path
from sys import stderr
from pysqlite2 import dbapi2 as sqlite
from utils import sql_quote, unique

_log = stderr

def rating_to_color(rating):
    i = rating
    red = green = blue = 0
    if i <= 25:
        red = 255
        green = i * 255 / 25
    elif i <= 75:
        red = (75-i) * 255 / 50
        green = 255
    else:
        green = 255
        blue = (i-75) * 255 / 25
    print >>stderr,"Rating:",rating,"R:",red,"G:",green,"B:",blue
    return "#%02x%02x%02x" % (red, green, blue)

class IMMSDb:
    def __init__(self, dbname = None):
        if not dbname:
            dbname = os.environ['HOME'] + '/.imms/imms2.db'
        # autocommit = 1 disable autocommit!
        self.cx = sqlite.connect(dbname, timeout = 5)
        self.cu = self.cx.cursor()
    def get_library_entry(self, **kw):
        qry = "SELECT path, l.uid, l.sid FROM Library l NATURAL INNER JOIN Identify"
        first = 1
        for key in kw.keys():
            if first:
                qry += " WHERE "
                first = 0
            else:
                qry += " AND "
            if key in ['uid', 'sid']:
                qry += "l.%s = %d" % (key, kw[key])
            else:
                qry += "%s = '%s'" % (key, sql_quote(kw[key]))
        qry += ";"
        self.cu.execute(qry)
        return self.cu.fetchall()
        return res
    def update_filename(self, oldname, newname):
        self.cu.execute("""UPDATE Library
            SET path = '%s'
            WHERE path = '%s';""" % (sql_quote(newname),
                                     sql_quote(oldname)))
    def erase_filename(self, name):
        self.cu.execute("""DELETE FROM Library
            WHERE path = '%s';""" % sql_quote(name))
    def erase_uid(self, uid):
        self.cu.execute("""BEGIN TRANSACTION;
             DELETE FROM Library WHERE uid = %d;
             DELETE FROM Ratings 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;""" % (sid, sid, sid))
    def erase_path(self, path):
        self.cu.execute("DELETE FROM Library WHERE path = '%s';" \
                        % sql_quote(path))
    def get_paths(self, uids = None, sids = None):
        qry = "SELECT uid, sid, path FROM Library"
        first = 1
        for uid in uids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " uid = %d" % uid
        for uid in uids:
            if first:
                qry += ' WHERE'
                first = 0
            else:
                qry += ' OR'
            qry += " sid = %d" % uid
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()        
    def get_ratings(self, min = 0, max = 150):
        self.cu.execute('''SELECT Ratings.uid, Ratings.rating 
                   FROM Ratings
                   WHERE Ratings.rating >= %d
                   AND Ratings.rating <= %d
                   ORDER BY Ratings.rating;''' % (min, max))
        return self.cu.fetchall()
    def get_acoustics(self, uids = None):
        qry = "SELECT uid, bpm, spectrum FROM Acoustic"
        first = 1
	if uids:
             for uid in uids:
                 if first:
                     qry += ' WHERE'
                     first = 0
                 else:
                     qry += ' OR'
                 qry += " uid = %d" % uid
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_infos(self, sids = None):
        qry = "SELECT sid, artist, title FROM Info"
        first = 1
        if sids:
            for sid in sids:
                if first:
                    qry += ' WHERE'
                    first = 0
                else:
                    qry += ' OR'
                qry += " sid = %d" % id
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_last(self, sids = None):
        qry = "SELECT sid, last FROM Last"
        first = 1
        if sids:
            for sid in sids:
                if first:
                    qry += ' WHERE'
                    first = 0
                else:
                    qry += ' OR'
                qry += " sid = %d" % id
        qry += ';'
        self.cu.execute(qry)
        return self.cu.fetchall()
    def get_uid_by_path(self, path):
        entries = self.get_library_entry(path = path)
        return map(lambda x: x[1], entries)
    def get_alike(self, uid):
        self.cu.execute('SELECT sid FROM Library WHERE uid = %d;' % uid)
        sids = self.cu.fetchall()
        res = {}
        for sid in sids:
            sid = int(sid)
            self.cu.execute("SELECT uid FROM Library "
                            "WHERE sid = %d;" % sid)
            res[sid] = []
            for uid in self.cu.fetchall():
                res[sid].append(int(uid))
        return res
    def get_ratings_and_paths(self, uids = None):
        qry = '''SELECT i.uid, rating, path, last
               FROM Identify i NATURAL INNER JOIN Ratings NATURAL INNER JOIN Library NATURAL INNER JOIN Last'''
        if uids:
            qry += ' WHERE (i.uid = %d' % (uids.pop())
            for uid in uids:
                qry += ' OR i.uid = %d' % uid
            qry += ')'
        qry += ';'
        self.cu.execute(qry)
        # Better to fetch everything since locking can really mess
        # things in imms plugin.
	results = {}
	tune = self.cu.fetchone()
        while tune:
            uid = int(tune[0])
            if results.has_key(uid):
                results[uid]['path'].append(tune[2])
            else:
                results[uid] = {
                    'rating' : int(tune[1]),
                    'path' : [ tune[2] ],
                    'last' : int(tune[3])}
            tune = self.cu.fetchone()
        return results
    def get_ratings_and_infos(self):
        self.cu.execute('''SELECT r.rating, i.artist, i.title
            FROM Library l, Ratings r, Info i
            WHERE l.uid = r.uid AND l.sid = i.sid;''')
        return self.cu.fetchall()
    def clean_info(self):
        self.cu.execute("""DELETE FROM Info
            WHERE sid NOT IN
            (SELECT sid FROM Library);""")
    def clean_last(self):
        self.cu.execute("""DELETE FROM Last
            WHERE sid NOT IN
            (SELECT sid FROM Library);""")
    def clean_rating(self):
        self.cu.execute("""DELETE FROM Ratings
            WHERE uid NOT IN
            (SELECT uid FROM Library);""")
    def clean_acoustic(self):
        self.cu.execute("""DELETE FROM Acoustic
            WHERE uid NOT IN
            (SELECT uid FROM Library);""")
    def clean_correlations(self):
        self.cu.execute("""DELETE FROM Correlations
            WHERE origin NOT IN (SELECT sid FROM Library)
            OR destination NOT IN (SELECT sid FROM Library);""")
    def clean_all(self):
        self.cu.execute("BEGIN TRANSACTION;")
        self.clean_info()
        self.clean_last()
        self.clean_rating()
        self.clean_acoustic()
        self.clean_correlations()
        self.cu.execute("COMMIT;")

class IMMSCleaner:
    def __init__(self, db):
        self.db = db
    def check_uid(self, uid):
        lib = self.db.get_library_entry(uid = uid)
        if len(lib) == 0:
            print >> _log, "Erased uid = ", uid
            self.db.erase_uid(uid)
    def check_sid(self, sid):
        lib = self.db.get_library_entry(sid = sid)
        if len(lib) == 0:
            print >> _log, "Erased sid = ", sid
            self.db.erase_sid(sid)
    def is_path_in_db(self, path):
        return len(self.db.get_library_entry(path = path))
    # Note: I doesn't much how I handle the two following functions...
    # May be I must just have the second one and handle everything
    # else in the derived class.
    def check_and_edit_path(self, path, uid, sid):
        """Must return the new path, None to remove
        it.  If the new file name is already in the Db,
        it will be skip.  The skip is more efficient if path
        is return.
        This is the default handler which always skip the file by
        returning path directly.
        """
        # The right thing (but not safe) would be to erase the
        # file if it already exist in the db...  But I find it
        # too much unsafe... Erasing a file shouldn't be easy to
        # do.
        return path
    def clean_library(self):
        lib = self.db.get_library_entry()
	print >> _log, "Processing %d entries" % len(lib)
        deleted_uids = []
        deleted_sids = []
        for entry in lib:
            path, uid, sid = entry
            uid = int(uid)
            sid = int(sid)
            newfile = self.check_and_edit_path(path, uid, sid)
            if not newfile:
                print >> _log, "Erasing ", path
                self.db.erase_filename(path)
                deleted_uids.append(uid)
                deleted_sids.append(sid)
            elif (path == newfile):
                pass
            elif self.is_path_in_db(newfile):
                print >> _log, "Skipping ", path
                pass
            else:
                print >> _log, "Renaming ", path, " into ", newfile
                self.db.update_filename(path, newfile)
        map(self.check_uid, unique(deleted_uids))
        map(self.check_sid, unique(deleted_sids))
##     def clean_rating(self):
## 	print >> _log, "Clean Ratings"
##         rates = self.db.get_ratings()
##         rates = unique(map(lambda x: x[0], rates))
##         map(self.check_uid, rates)
##     def clean_acoustic(self):
## 	print >> _log, "Clean Acoustic"
##         uids = self.db.get_acoustics()
## 	uids = map(lambda x: x[0], uids )
##         map(self.check_uid, uids)
##     def clean_info(self):
## 	print >> _log, "Clean Info"
##         sids = map(lambda x: x[0], self.db.get_infos())
##         map(self.check_sid, sids)
##     def clean_last(self):
## 	print >> _log, "Clean Last"
##         sids = map(lambda x: x[0], self.db.get_last())
##         map(self.check_sid, sids)
    def clean_all(self):
        self.clean_library()
##         self.clean_rating()
##         self.clean_acoustic()
##         self.clean_info()
##         self.clean_last()
        self.db.clean_all()