from database import db_conn

def getanimals(cursor, groupid, wholesection = False):
    if not wholesection:
        cursor.execute("SELECT animalid FROM animals WHERE groupid=?", (groupid, ))
    else:
        cursor.execute("SELECT animalid FROM animals WHERE groupid IN "
                       + "(SELECT groupid FROM groups WHERE section="
                       + "(SELECT section FROM groups WHERE groupid=?))", (groupid, ))
    
    return [i[0] for i in cursor.fetchall()]

def getgroupid(cursor, animalid):
    cursor.execute("SELECT groupid FROM animals WHERE animalid=?", (animalid, ))
    return cursor.fetchall()[0][0]

def getfeeds(cursor, animalid):
    cursor.execute("SELECT timestamp, amount FROM feeds WHERE animalid=?", (animalid, ))
    return cursor.fetchall()

def getdays(cursor, animalid):
    '''Get total amount of feeds for a day for one animal. Returns [(datetime.date, amount), ...]'''
    cursor.execute("SELECT timestamp, SUM(amount) FROM feeds WHERE animalid=? "
                   + "GROUP BY SUBSTR(timestamp, 1, 10)", (animalid, ))
    return [(d.date(), a) for d, a in cursor.fetchall()]

def avggroup(cursor, animalids):
    '''Calculate daily average for a group of animals'''
    amounts = {}
    
    for animalid in animalids:
        data = getdays(cursor, animalid)
        for date, amount in data:
            if amounts.has_key(date):
                amounts[date].append(amount)
            else:
                amounts[date] = [amount]

    result = []
    for date, amountlist in amounts.items():
        result.append((date, sum(amountlist) / len(amountlist)))
    
    return result

def graphdata(animalid, days = True):
    '''Fetch graph data from database for animalid (also group and section avgs), for feeds or days.'''

    cursor = db_conn.cursor()

    if not days:
        feeds = getfeeds(cursor, animalid)
        return [(d.strftime("%d.%m"), a, 0., 0.) for d, a in feeds]

    animal = dict(getdays(cursor, animalid))
    
    # Group average
    groupid = getgroupid(cursor, animalid)
    groupmembers = getanimals(cursor, groupid, False)
    group = dict(avggroup(cursor, groupmembers))
    
    # Section average
    sectionmembers = getanimals(cursor, groupid, True)
    section = dict(avggroup(cursor, sectionmembers))

    dates = animal.keys()
    dates.sort()
    result = []

    for date in dates:    
        result.append((date.strftime("%d.%m"), animal[date], group[date], section[date]))

    return result


if __name__ == "__main__":
    print "Unit testing"
    
    import wx
    from graph import GraphPanel
    
    cursor = db_conn.cursor()
    cursor.execute("SELECT animalid FROM animals LIMIT 1")
    animalid = cursor.fetchall()[0][0]
    
    class MyApp(wx.App):
        def OnInit(self):
            wx.InitAllImageHandlers()
            frame = wx.Frame(None, -1, "Graph unit testing")
            panel = GraphPanel(frame)
            panel.setdata(graphdata(animalid, days = True))
            panel.redraw()
            
            frame.Show(True)
            self.SetTopWindow(frame)
            return True

    app = MyApp(0)
    app.MainLoop()