import sqlite3
import json

def get_db_connection():
    conn = sqlite3.connect('mldb.db')
    conn.row_factory = sqlite3.Row
    return conn

def get_all():
    conn = get_db_connection()
    jobs = conn.execute('SELECT * FROM Jobs').fetchall()
    conn.close()
    return jobs

def get_job(JobId):
    conn = get_db_connection()
    job = conn.execute('SELECT * FROM Jobs WHERE Id = ?', (JobId,)).fetchone()
    conn.close()
    return job

def add_job(RunId, AUC, Accuracy, Arguments, TrainingContext, RunStatus):
    conn = get_db_connection()
    cur = conn.cursor()

    cur.execute("INSERT INTO Jobs (RunId, AUC, Accuracy, Arguments, TrainingContext, RunStatus) VALUES (?, ?, ?, ?, ?, ?)", (RunId, AUC, Accuracy, Arguments, TrainingContext, RunStatus))
    conn.commit()
    conn.close()
    return cur.lastrowid

def update_job(JobId, RunId, AUC, Accuracy, Arguments, TrainingContext, RunStatus):
    conn = get_db_connection()
    cur = conn.cursor()

    cur.execute("UPDATE Jobs SET RunId = ?, AUC = ?, Accuracy = ?, Arguments = ?, TrainingContext = ?, RunStatus = ? WHERE Id = ?", (RunId, AUC, Accuracy, Arguments, TrainingContext, RunStatus, JobId))
    conn.commit()
    conn.close()

    return get_job(JobId)

connection = sqlite3.connect('mldb.db')

with open('database/schema.sql') as f:
    connection.executescript(f.read())
    connection.close()
    print('Database registered')
