# coding=utf-8

import collections
import json

from . import get_connection, return_connection

__all__ = ['CodeqUser', 'Solution']


class CodeqUser(collections.namedtuple('CodeqUser', ['id', 'username', 'password', 'name', 'email', 'is_admin', 'is_active', 'date_joined', 'last_login'])):
    __sql_prefix = 'select id, username, password, name, email, is_admin, is_active, date_joined, last_login from codeq_user'

    @staticmethod
    def get(**kwargs):
        return _general_get(kwargs, CodeqUser, CodeqUser.__sql_prefix)

    @staticmethod
    def list():
        return _general_list(CodeqUser, CodeqUser.__sql_prefix)

    @staticmethod
    def filter(**kwargs):
        return _general_filter(kwargs, CodeqUser, CodeqUser.__sql_prefix)

    @staticmethod
    def solved_problems(user_id, language):
        return _run_sql('select g.identifier, p.identifier from solution s inner join problem p on p.id = s.problem_id inner join problem_group g on g.id = p.problem_group_id inner join language l on l.id = p.language_id where s.codeq_user_id = %s and l.identifier = %s and s.done = True', (user_id, language), fetch_one=False)

class Problem(collections.namedtuple('Problem', ['id', 'language_id', 'problem_group_id', 'name', 'identifier', 'is_visible'])):
    __sql_prefix = 'select id, language_id, problem_group_id, name, identifier, is_visible from problem'

    @staticmethod
    def get(**kwargs):
        return _general_get(kwargs, Problem, Problem.__sql_prefix)

    @staticmethod
    def list():
        return _general_list(Problem, Problem.__sql_prefix)

    @staticmethod
    def filter(**kwargs):
        return _general_filter(kwargs, Problem, Problem.__sql_prefix)

    @staticmethod
    def identifier(problem_id):
        return _run_sql('select l.identifier, g.identifier, p.identifier from problem p inner join problem_group g on g.id = p.problem_group_id inner join language l on l.id = p.language_id where p.id = %s', (problem_id,), fetch_one=True)

# known as Attempt in the original code
class Solution(collections.namedtuple('Solution', ['id', 'done', 'content', 'problem_id', 'codeq_user_id', 'trace'])):
    __sql_prefix = 'select id, done, content, problem_id, codeq_user_id, trace::text from solution'
    __row_conversion = lambda row: (row[0], row[1], row[2], row[3], row[4], json.loads(row[5]))

    @staticmethod
    def get(**kwargs):
        return _general_get(kwargs, Solution, Solution.__sql_prefix, Solution.__row_conversion)

    @staticmethod
    def list():
        return _general_list(Solution, Solution.__sql_prefix, Solution.__row_conversion)

    @staticmethod
    def filter(**kwargs):
        return _general_filter(kwargs, Solution, Solution.__sql_prefix, Solution.__row_conversion)


def _no_row_conversion(row):
    return row

def _general_get(kwargs_dict, clazz, sql_select, row_conversion_fn=_no_row_conversion):
    conditions = []
    parameters = []
    for field_name, field_value in kwargs_dict.items():
        conditions.append(field_name + ' = %s')
        parameters.append(field_value)
    if len(conditions) == 0:
        return None
    conn = get_connection()
    try:
        cur = conn.cursor('crsr1') # a named cursor: scrolling is done on the server
        cur.arraysize = 1 # scroll unit in the number of rows
        try:
            cur.execute(sql_select + ' where ' + ' and '.join(conditions), parameters)
            row = cur.fetchone()
            if row:
                return clazz(*row_conversion_fn(row))
            return None
        finally:
            cur.close()
    finally:
        return_connection(conn)

def _general_filter(kwargs_dict, clazz, sql_select, row_conversion_fn=_no_row_conversion):
    conditions = []
    parameters = []
    for field_name, field_value in kwargs_dict.items():
        conditions.append(field_name + ' = %s')
        parameters.append(field_value)
    if len(conditions) == 0:
        return _general_list(clazz, sql_select)
    conn = get_connection()
    try:
        cur = conn.cursor('crsr2') # a named cursor: scrolling is done on the server
        cur.arraysize = 10000 # scroll unit in the number of rows
        try:
            cur.execute(sql_select + ' where ' + ' and '.join(conditions) + ' order by id', parameters)
            result = []
            row = cur.fetchone()
            while row:
                result.append(clazz(*row_conversion_fn(row)))
                row = cur.fetchone()
            return result
        finally:
            cur.close()
    finally:
        return_connection(conn)

def _general_list(clazz, sql_select, row_conversion_fn=_no_row_conversion):
    conn = get_connection()
    try:
        cur = conn.cursor('crsr3') # a named cursor: scrolling is done on the server
        cur.arraysize = 10000 # scroll unit in the number of rows
        try:
            cur.execute(sql_select + ' order by id')
            result = []
            row = cur.fetchone()
            while row:
                result.append(clazz(*row_conversion_fn(row)))
                row = cur.fetchone()
            return result
        finally:
            cur.close()
    finally:
        return_connection(conn)

def _run_sql(sql, params, fetch_one=False):
    conn = get_connection()
    try:
        cur = conn.cursor()
        try:
            cur.execute(sql, params)
            if fetch_one:
                return cur.fetchone()
            else:
                return cur.fetchall()
        finally:
            cur.close()
    finally:
        return_connection(conn)