"""Takes care of sqlite interactions with the database."""

import sqlite3
from os.path import isfile

class nNoteCore(object):
    """List and interaction with databases"""
    #change to NoteCore when fully transitioned
    def __init__(self):
        #possible list of dbs
        self.dbs = {}
        self.default= 1
        self.nq = NoteQuery()
        self.tq = TimeQuery()
        self.aq = ArchiveQuery()

    def add_db(self, db, name=None):
        temp = name
        if name is None:
            temp = str(self.default)
            self.default += 1
        self.dbs[temp] = db
        return temp

    def fetch_all(self, name=None, ttype=None, **kwargs):
        query = None
        qlist = None
        if ttype is None or name is None:
            return
        elif ttype == "notes":
            pass
            #use notequery
        elif ttype == "time":
            pass
            #use timequery
        elif ttype == "archive":
            pass
            #use archivequery
        else:
            return
        return self.dbs[name].select(query, qlist)

    def fetch_one(self, name=None):
        pass

    def insert(self, name=None):
        #should be able to handle updates for time and notes too.
        pass

    def archive_delete(self, name=None):
        #only for archives
        pass


class DatabaseHandler(object):
    """DatabaseHandler handles one database"""
    def __init__(self, dbpath=None):
        if dbpath:
            self.dbpath = dbpath
        else:
            print "Need path to database directory."
            return
        #setting up the database
        self.conn = sqlite3.connect(self.dbpath)
        self.conn.text_factory = str
        self.cur = self.conn.cursor()
        #Three tables and their columns.
        tables = {"notes": "(date INTEGER, time INTEGER, project, note)",
                  "times": "(project, date INTEGER, seconds INTEGER)",
                  "archive": "(project)"}
        query = "SELECT name FROM sqlite_master WHERE type='table'"
        qlist = None
        result = [r[0] for r in self.select
(query, qlist)]
        for name,value in tables:
            if name not in result:
                query = "CREATE TABLE " + name + " " + value
                self.insert(query, qlist)

    def select(self, query, qlist):
        try:
            return self.cur.execute(query, qlist)
        except sqlite3.Error, error:
            print "-s- %s" % err
or.args[0]

    def insert(self, query, qlist):
        try:
            self.cur.execute(query, qlist)
            self.save()
        except sqlite3.Error, error:
            print "-s- %s" % error.args[0]

    def query_exists(self, query, qlist):
        """Return query if it exists"""
        previous = self.select(query, qlist)
        prev_note = previous.fetchone()
        if prev_note is None:
            return None
        return prev_note

    def save(self):
        '''Commit the database.'''
        self.conn.commit()


class NoteQuery(object):
    """NoteQuery builds queries and returns values"""
    def __init__(self):
        pass

    def build_select(self, search=None, b_date=None, e_date=None,
                     project=None, date=None, time=None):
        """Take optional arguments, build query and return results.
        - search - term to search for in notes
        - b_date, e_date - beggining and ending date
        - project - title
        - date, time - specific date and/or time
        """
        query = "SELECT"
        qlist = []
        search_str = "WHERE"
        and_ = "" #gets set after the first argument to AND.
        if search is not None:
            search_str = "WHERE note LIKE ?"
            qlist.append('%'+search+'%')
            and_ = "AND"
        if project is None:
            #If no project then simple.
            query = ' '.join([query, "project,date,time,note FROM notes"])
            query = ' '.join([query, search_str])
        elif project.find(",") != -1:
            #Parse comma and space delimited list, print project
            query = ' '.join([query, "project,date,time,note FROM notes"])
            temp_list = project.split(", ")
            p_list = []
            for each in temp_list:
                p_list.append("project=?")
            p_str = " OR ".join(p_list)
            p_str = "(" + p_str + ")"
            query = ' '.join([query, search_str, and_, p_str])
            qlist = qlist + temp_list
            and_ = "AND"
        else:
            #Leave out project and use it to search.
            query = ' '.join([query, "date,time,note FROM notes"])
            query = ' '.join([query, search_str, and_, "project=?"])
            qlist.append(project)
            and_ = "AND"
        if b_date is not None:
            #beginning date
            query = ' '.join([query, and_, "date>=?"])
            qlist.append(b_date)
            and_ = "AND"
        if e_date is not None:
            #ending date
            query = ' '.join([query, and_, "date<=?"])
            qlist.append(e_date)
            and_ = "AND"
        if date is not None:
            #Specific date probably not used with b_date or e_date
            query = ' '.join([query, and_, "date=?"])
            qlist.append(date)
            and_ = "AND"
        if time is not None:
            #Specific time
            query = ' '.join([query, and_, "time=?"])
            qlist.append(time)
            and_ = "AND"
        query = query + " ORDER BY date,time"
        query = ' '.join(query.split()) #remove extra whitespace.
        return (query, qlist)

    def project_day(self, project, date):
        return self.build_select(project=project, date=date)

    def project_time(self, project, date, time):
        return self.build_select(project=project, date=date, time=time)

    def all_projects(self):
        return ("SELECT DISTINCT project FROM notes", [])

    def all_dates(self, project=None):
        '''Return a list of all distinct dates'''
        if project is not None:
            return ("SELECT DISTINCT date FROM notes WHERE project=?", [project])
        else:
            return ("SELECT DISTINCT date FROM notes", [])

    def insert(self, project, date, time, text):
        return ("INSERT INTO notes VALUES (?,?,?,?)",
                [date, time, project, text])

    def update(self, project, date, time, text):
        query = """UPDATE notes SET note=? 
                WHERE project=? AND date=? AND time=?"""
        return (query, [text, project, date, time])


class TimeQuery(object):
    def __init__(self):
        pass

    def build_select(self, b_date=None, e_date=None, project=None, date=None):
        """Take optional arguments, build a query, return results.
        - b_date, e_date - beggining and ending date.
        - project - title
        - date - specific date
        """
        query = "SELECT"
        qlist = []
        and_ = "" #gets set after the first argument to AND.
        if project is None:
            #If no project then include project in output.
            query = ' '.join([query, "project,date,seconds FROM times"])
            query = ' '.join([query, "WHERE"])
        else:
            #Otherwise leave out project and use it to search.
            query = ' '.join([query, "date,seconds FROM times"])
            query = ' '.join([query, "WHERE project=?"])
            qlist.append(project)
            and_ = "AND"
        if b_date is not None:
            #beginning date
            query = ' '.join([query, and_, "date>=?"])
            qlist.append(b_date)
            and_ = "AND"
        if e_date is not None:
            #ending date
            query = ' '.join([query, and_, "date<=?"])
            qlist.append(e_date)
            and_ = "AND"
        if date is not None:
            #Specific date probably not used with b_date or e_date
            query = ' '.join([query, and_, "date=?"])
            qlist.append(date)
            and_ = "AND"
        query = ' '.join(query.split()) #remove extra whitespace.
        return (query, qlist)

    def project_day(self, project, date):
        return self.build_select(pro