#!/usr/bin/python
import sqlite3
import random
import sys
import signal
from datetime import datetime

def update_metrics(conn, question, answer, questioned_at, is_answer_correct):
    conn.execute(
        "insert into metrics (question_id, answer, questioned_at, answered_at, is_answer_correct) values (?,?,?,?,?)",
        [question['id'], answer, questioned_at, datetime.now(), is_answer_correct]
    )
    conn.commit()

def query_not_answered_questions(cursor):
    cursor.execute("select * from question where id not in (select distinct question_id from metrics)")
    questions = cursor.fetchall()
    random.shuffle(questions)

    return questions

def query_last_incorrectly_answered_questions(cursor):
    cursor.execute(
        """
        select * from question join (
            select question_id, max(answered_at) as answered_at
            from metrics
            group by question_id
            having is_answer_correct = 0
        ) as latest_incorrect_answers
        on id = latest_incorrect_answers.question_id
        """
    )

    questions = cursor.fetchall()
    random.shuffle(questions)
    return questions

def query_last_correctly_answered_questions(cursor, min_hours_since_last_correct_answer = 72):
    cursor.execute(
            """
            select * from question join (
               select question_id,
               min(
                 (
                   strftime('%s', CURRENT_TIMESTAMP) -
                   strftime('%s', answered_at)
                 ) / 3600
               ) as hours_since_last_correct_answer
               from metrics
               group by question_id
               having is_answer_correct = 1
               and hours_since_last_correct_answer >= ?
            ) as correct_answers_in_last_hours
            on id = correct_answers_in_last_hours.question_id
            """,
            [min_hours_since_last_correct_answer]
    )
    questions = cursor.fetchall()
    random.shuffle(questions)
    return questions

def query_answers_by_question(question_id):
    cursor.execute("select * from answer where question_id = ?", [question_id])
    return cursor.fetchall()

def verify_answer_correct(cursor, question_id, answer):
    correct_answers = query_answers_by_question(question_id)
    return len(
        list(
            filter(
                lambda correct_answer:
                correct_answer["text"].rstrip() == answer.rstrip(), correct_answers
            )
        )
    ) > 0

def interrupt_handler(signal, handler):
    print("\nBye!")
    sys.exit(0)

signal.signal(signal.SIGINT, interrupt_handler)

conn = sqlite3.connect('tq.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

questions = query_not_answered_questions(cursor)
questions.extend(query_last_incorrectly_answered_questions(cursor))
questions.extend(query_last_correctly_answered_questions(cursor))

while questions:
    for question in questions[:]:
        print("Question: ", question["text"])
        questioned_at = datetime.now()

        while True:
            answer = sys.stdin.read()
            if answer:
                break

        if verify_answer_correct(cursor, question["id"], answer):
            print("Correct!")
            questions.remove(question)
            update_metrics(conn, question, answer, questioned_at, 1)
        else:
            print("Incorrect")
            update_metrics(conn, question, answer, questioned_at, 0)

conn.close()
