import logging
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from orm import Base, Student, Test, Question

logger = logging.getLogger(__name__)

#----------------------------------------------------------------------------
class Database(object):
    def __init__(self, db):
        self.db = db  # sqlite3 filename

        engine = create_engine('sqlite:///{}'.format(db), echo=False)
        Base.metadata.create_all(engine) # Criate schema if needed
        self.Session = scoped_session(sessionmaker(bind=engine))

    #-------------------------------------------------------------------------
    def get_count_students(self):
        s = self.Session()
        return s.query(Student).filter(Student.id != '0').count()

        # with sqlite3.connect(self.db) as c:
        #     sql = 'SELECT COUNT(*) FROM students'
        #     return c.execute(sql).fetchone()[0]

    #-------------------------------------------------------------------------
    def update_password(self, uid, pw=''):
        s = self.Session()
        try:
            u = s.query(Student).filter(Student.id == uid).one()
        except:
            pass
        else:
            u.password = pw
            s.commit()

        # saves pw as is (should be already hashed)
        # with sqlite3.connect(self.db) as c:
        #     sql = 'UPDATE students SET password=? WHERE id=?'
        #     c.execute(sql, (pw, uid))

    #-------------------------------------------------------------------------
    def get_student(self, uid):
        s = self.Session()
        r =  s.query(Student).filter(Student.id == uid).one_or_none()
        return r.name, r.password

        # with sqlite3.connect(self.db) as c:
        #     sql = 'SELECT name,password FROM students WHERE id=?'
        #     try:
        #         name, pw = c.execute(sql, [uid]).fetchone()
        #     except:
        #         return None
        #     else:
        #         return (name, pw)

    #-------------------------------------------------------------------------
    def get_all_students(self):
        s = self.Session()
        r = s.query(Student).all()
        return [(x.id, x.name, x.password) for x in r]

        # with sqlite3.connect(self.db) as c:
        #     sql = 'SELECT id,name,password FROM students ORDER BY id ASC'
        #     students = c.execute(sql).fetchall()
        #     return students

    # get all results for a particular test. If a student has submited more than
    # one test, returns the highest grade  FIXME not tested, not used
    # def get_students_grades(self, testid):
    #     with sqlite3.connect(self.db) as c:
    #         grades = c.execute('SELECT student_id,MAX(grade) FROM tests WHERE test_id==?', [testid])
    #         return grades.fetchall()

    #-------------------------------------------------------------------------
    # get results from previous tests of a student
    def get_student_grades_from_all_tests(self, uid):
        s = self.Session()
        r = s.query(Test).filter(Student.id == uid).all()
        return [(x.id, x.grade, x.finishtime) for x in r]

        # with sqlite3.connect(self.db) as c:
        #     grades = c.execute('SELECT id,grade,finishtime FROM tests WHERE student_id==?', [uid])
        #     return grades.fetchall()

    #-------------------------------------------------------------------------
    def get_student_grades_from_test(self, uid, testid):
        s = self.Session()
        r = s.query(Test).filter(Test.student_id==uid and Test.id==testid).all()
        return [(x.grade, x.finishtime) for x in r]

        # with sqlite3.connect(self.db) as c:
        #     grades = c.execute('SELECT grade,finishtime FROM tests WHERE student_id==? and id==?', [uid, testid])
        #     return grades.fetchall()

    #-------------------------------------------------------------------------
    def save_test(self, test):
        t = Test(
            ref=test['ref'],
            grade=test['grade'],
            starttime=str(test['start_time']),
            finishtime=str(test['finish_time']),
            student_id=test['student']['number']
            )
        s = self.Session()
        s.add(t)
        s.commit()

        # with sqlite3.connect(self.db) as c:
        #     # save final grade of the test
        #     sql = 'INSERT INTO tests VALUES (?,?,?,?,?)'
        #     test = (t['ref'], t['student']['number'], t['grade'], str(t['start_time']), str(t['finish_time']))
        #     c.execute(sql, test)

    #-------------------------------------------------------------------------
    def save_questions(self, test):
        s = self.Session()
        questions = [Question(
            ref=q['ref'],
            grade=q['grade'],
            starttime='',
            finishtime=str(test['finish_time']),
            student_id=test['student']['number'],
            test_id=test['ref']) for q in test['questions'] if 'grade' in q]
        s.add_all(questions)
        s.commit()

        # with sqlite3.connect(self.db) as c:
        #     # save grades of all the questions (omits questions without grade)
        #     sql = 'INSERT INTO questions VALUES (?,?,?,?,?)'
        #     questions = [(t['ref'], q['ref'], t['student']['number'], q['grade'], str(t['finish_time'])) for q in t['questions'] if 'grade' in q]
        #     c.executemany(sql, questions)




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




    # # return list of students and their results for a given test
    # def get_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()


    # the following methods update de database data