#!/usr/bin/python3

import sqlite3

db = sqlite3.connect('misc/solutions.db')
db.row_factory = sqlite3.Row
db.text_factory = bytes
cursor = db.cursor()

def b_to_utf8(bytestring):
    return str(bytestring, encoding='utf-8')

def get_problem_ids():
    cursor.execute('SELECT id FROM problems ORDER BY id ASC')
    return [row['id'] for row in cursor]

def get_problem(pid):
    cursor.execute('SELECT name, solution, library FROM problems WHERE id=?', (pid,))
    row = cursor.fetchone()
    name = b_to_utf8(row['name'])
    solution = b_to_utf8(row['solution']).replace('\r', '')
    lib_id = row['library'] if row['library'] else None
    return name, solution, lib_id

def get_depends(pid):
    cursor.execute('SELECT dependency FROM depends WHERE problem=?', (pid,))
    return [r['dependency'] for r in cursor.fetchall()]

def get_library(lid):
    cursor.execute('SELECT facts FROM libraries WHERE id=?', (lid,))
    row = cursor.fetchone()
    return b_to_utf8(row['facts']).replace('\r', '') if row else None

def get_tests(pid):
    cursor.execute('SELECT query FROM tests WHERE problem=?', (pid,))
    return [b_to_utf8(row['query']) for row in cursor]

def get_traces(pid):
    cursor.execute('SELECT * FROM attempts WHERE problem=? AND done=1 ORDER BY id ASC', (pid,))
    return {(pid, attempt['user']): attempt['log'] for attempt in cursor}

def get_solved(uid):
    cursor.execute('SELECT problem FROM attempts WHERE user=? AND done=1 ORDER BY problem ASC', (uid,))
    return [row['problem'] for row in cursor.fetchall()]