import asyncio
import dashi.config
import dashi.git
import dashi.jenkins
import logging
import os
import sqlite3

LOGGER = logging.getLogger(__name__)

def connection():
    return sqlite3.connect('dashi.db')

@asyncio.coroutine
def load():
    config = dashi.config.parse()

    if os.path.exists('dashi.db'):
        os.remove('dashi.db')

    conn = connection()

    _create_tables(conn)

    cursor = conn.cursor()
    for repo in config['repositories']:
        commits = yield from dashi.git.get_commits(config, repo)
        simplified = [(
            commit['date'],
            commit['hash'],
            commit['author']['user']['display_name'] if 'user' in commit['author'] else commit['author']['raw'],
            repo)
        for commit in commits]
        cursor.executemany("INSERT INTO commits VALUES (?,?,?,?)", simplified)
        conn.commit()
        LOGGER.debug("Inserted %s rows into commits for %s", len(simplified), repo)

def get_all_authors(connection):
    cursor = connection.cursor()
    result = cursor.execute("SELECT DISTINCT author FROM commits")
    return [r[0] for r in result]

def get_commit_counts_by_time_periods(connection, periods, user):
    return [{
        'start' : period[0],
        'end'   : period[1],
        'total' : _total_commits(connection, period[0], period[1]),
        'mine'  : _commits_for(connection, period[0], period[1], user),
    } for period in periods]

def _total_commits(connection, start, end):
    cursor = connection.cursor()
    result = cursor.execute(
        "SELECT hash FROM commits WHERE datetime(date) > datetime(?) AND datetime(date) < (?)",
        (start.isoformat(), end.isoformat())
    )
    return len(result.fetchall())

def _as_list(l):
    items = ["'{}'".format(i) for i in l]
    return "({})".format(','.join(items))

def _commits_for(connection, start, end, user):
    cursor = connection.cursor()
    result = cursor.execute(
        "SELECT hash FROM commits WHERE datetime(date) > datetime(?) AND datetime(date) < (?) AND author IN {}".format(_as_list(user.aliases)),
        (start.isoformat(), end.isoformat())
    )
    return len(result.fetchall())

def _create_tables(conn):
    c = conn.cursor()
    c.execute("""CREATE TABLE commits
        (date TEXT, hash TEXT, author TEXT, repository TEXT)""")

    conn.commit()

@@ -385,16 +385,161 @@
 s(conn)%0A
+    yield from _load_commits(conn, config)%0A    #yield from _load_jenkins(conn, config)%0A%0A@asyncio.coroutine%0Adef _load_commits(connection, config):
 %0A    cur
@@ -544,24 +544,30 @@
 ursor = conn
+ection
 .cursor()%0A  
@@ -874,20 +874,44 @@
         
-repo
+commit%5B'repository'%5D%5B'name'%5D
 )%0A      
@@ -982,16 +982,56 @@
  commits
+ (date, hash, author, repository, lines)
  VALUES 
@@ -1058,32 +1058,38 @@
 ed)%0A        conn
+ection
 .commit()%0A      
@@ -1167,16 +1167,664 @@
  repo)%0A%0A
+@asyncio.coroutine%0Adef _load_jenkins(connection, config):%0A    cursor = connection.cursor()%0A    server = dashi.jenkins.connect(config)%0A    for repo in config%5B'repositories'%5D:%0A        results = dashi.jenkins.get_test_results_for_repo(server, repo)%0A        simplified = %5B(%0A            result%5B'name'%5D,%0A            result%5B'build'%5D,%0A            result%5B'date'%5D,%0A            len(result%5B'tests'%5D),%0A        ) for result in results%5D%0A        cursor.executemany(%22INSERT INTO tests (name, build, date, tests) VALUES (?, ?, ?, ?)%22, simplified)%0A        connection.commit()%0A        LOGGER.debug(%22Inserted %25s rows into tests for %25s%22, len(simplified), repo%5B'name'%5D)%0A%0A
 def get_
@@ -3170,15 +3170,127 @@
 TEXT
+, lines NUMBER)%22%22%22)%0A    c.execute(%22%22%22CREATE TABLE tests%0A        (name TEXT, build NUMBER, date TEXT, tests NUMBER
 )%22%22%22)%0A
-%0A
     

Add code for loading up Jenkins builds in the DB