import sqlite3
from hashlib import sha256

class Database(object):
    def __init__(self, db):
        self.db = db  # sqlite3 filename
        # FIXME check that database exists

    # get results from previous tests of a student
    def student_grades(self, uid):
        with sqlite3.connect(self.db) as c:
            grades = c.execute('SELECT test_id,grade,finish_time FROM tests WHERE student_id==?', [uid])
            return grades.fetchall()

    # return list of students and their results for a given test
    def test_grades(self, test_id):
        with sqlite3.connect(self.db) as c:
            # with all tests done by each student:
            # cmd = 'SELECT student_id,name,grade FROM students INNER JOIN tests ON students.number=tests.student_id WHERE test_id==? ORDER BY grade DESC;'

            # only the best result for each student
            cmd = '''
                SELECT student_id, name, MAX(grade), finish_time
                FROM students INNER JOIN tests
                ON students.number=tests.student_id
                WHERE test_id==? AND student_id!=0
                GROUP BY student_id
                ORDER BY grade DESC, finish_time DESC;'''
            return c.execute(cmd, [test_id]).fetchall()

    # return list of students and their results for a given test
    def test_grades2(self, test_id):
        with sqlite3.connect(self.db) as c:
            # with all tests done by each student:
            # cmd = 'SELECT student_id,name,grade FROM students INNER JOIN tests ON students.number=tests.student_id WHERE test_id==? ORDER BY grade DESC;'

            # only the best result for each student
            cmd = '''
                SELECT student_id, name, grade, start_time, finish_time
                FROM students INNER JOIN tests
                ON students.number=tests.student_id
                WHERE test_id==?
                ORDER BY finish_time ASC;'''
            return c.execute(cmd, [test_id]).fetchall()

    # get list of students in the database
    def get_students(self):
        with sqlite3.connect(self.db) as c:
            students = c.execute('SELECT number,name,password FROM students ORDER BY number ASC;')
            return students.fetchall()

    # the following methods update de database data

    def save_test(self, t):
        with sqlite3.connect(self.db) as c:
            # store result of the test
            values = (t['ref'], t['number'], t['grade'], str(t['start_time']), str(t['finish_time']))
            c.execute('INSERT INTO tests VALUES (?,?,?,?,?)', values)

            # store grade of every question in the test
            try:
                ans = [(t['ref'], q['ref'], t['number'], q['grade'], str(t['finish_time'])) for q in t['questions']]
            except KeyError as e:
                print('  * Questions {0} do not have grade defined.'.format(tuple(q['ref'] for q in t['questions'] if 'grade' not in q)))
                raise e
            c.executemany('INSERT INTO questions VALUES (?,?,?,?,?)', ans)

    def student_reset_pw(self, d):
        # d = {'12345': 'mypassword', ...}
        with sqlite3.connect(self.db) as c:
            for num, pw in d.items():
                if pw != '':
                    pw = sha256(pw.encode('utf-8')).hexdigest()
                cmd = 'UPDATE students SET password=? WHERE number=?'
                c.execute(cmd, (pw, num))

    def student_add(self, number, name, password=''):  # FIXME testar...
        with sqlite3.connect(self.db) as c:
            if password != '':
                password = sha256(password.encode('utf-8')).hexdigest()
            cmd = 'INSERT INTO students VALUES (?, ?, ?);'
            c.execute(cmd, number, name, password)