def get_group_list(params, cuser_id):
    group_dict = { group.id : group.name for group, ug in DBSession.query(Group, UserGroup).filter(UserGroup.user_id == cuser_id).filter(Group.id == UserGroup.group_id).all()}
    for group in DBSession.query(Group).filter(Group.owner_id == cuser_id).all():
       group_dict[group.id] = group.name
    group_list = [{'name':group_dict[group_id], 'id':group_id} for group_id in group_dict]
    group_list.sort(key=lambda a: a['name'])
    return group_list

def setShowLimits(problem_id, show_limits):
    problem = DBSession.query(Problem).filter(Problem.id == problem_id).first()

    problem.show_limits = show_limits
    with transaction.manager:
        DBSession.merge(problem)
    return "Ok"

def problem_generate_samples(request):
    try:
        checkCapability(request)
        problem = DBSession.query(EjudgeProblem).filter(EjudgeProblem.id == request.matchdict['problem_id']).first()
        problem.generateSamples()
#        res = ""
#        if problem.sample_tests != '':
#            res = "<div class='problem-statement'><div class='sample-tests'><div class='section-title'>Примеры</div>"
#        
#            for i in problem.sample_tests.split(","):
#                res += "<div class='sample-test'>"
#                res += "<div class='input'><div class='title'>Входные данные</div><pre class='content'>"
#                res += get_test(problem, i)
#                res += "</pre></div><div class='output'><div class='title'>Выходные данные</div><pre class='content'>"
#                res += get_corr(problem, i)
#                res += "</pre></div></div>"
#        
#            res += "</div></div>"
#
#        problem.sample_tests_html = res
        with transaction.manager:
           DBSession.merge(problem)
        return {"result" : "ok", "content" : problem.sample_tests}
    except Exception as e:
        return {"result" : "error", "content" : e.__str__(), "stack" : traceback.format_exc()}

def get_queries_by_params(params):
    current_selection = DBSession.query(User).filter(User.deleted == False)
    current_count_selection = DBSession.query(func.count(User.id)).filter(User.deleted == False)

    if params.group_filter not in [None, 2]: #2 - group_id for group with all users, we don't need to filter by that
        current_selection = current_selection.filter(UserGroup.group_id  == params.group_filter).filter(UserGroup.user_id == User.id)
        current_count_selection = current_count_selection.filter(UserGroup.group_id  == params.group_filter).filter(UserGroup.user_id == User.id)

    if params.city is not None:
        current_selection = current_selection.filter(User.city.like('%' + params.city + '%'))
        current_count_selection = current_count_selection.filter(User.city.like('%' + params.city + '%'))
    
    if params.school is not None:
        current_selection = current_selection.filter(User.school.like('%' + params.school + '%'))
        current_count_selection = current_count_selection.filter(User.school.like('%' + params.school + '%'))

    if None not in (params.solved_from_filter, params.solved_to_filter):
        current_selection = current_selection.filter(User.problems_solved.between(params.solved_from_filter, params.solved_to_filter))
        current_count_selection = current_count_selection.filter(User.problems_solved.between(params.solved_from_filter, params.solved_to_filter))

    if None not in (params.week_solved_from_filter, params.week_solved_to_filter):
        current_selection = current_selection.filter(User.problems_week_solved.between(params.week_solved_from_filter, params.week_solved_to_filter))
        current_count_selection = current_count_selection.filter(User.problems_week_solved.between(params.week_solved_from_filter, params.week_solved_to_filter))

    if params.name is not None:
        current_selection = current_selection.filter(User.lastname.like('%' + params.name + '%'))
        current_count_selection = current_count_selection.filter(User.lastname.like('%' + params.name + '%'))
 
    return current_selection, current_count_selection

def add(request):
    try:
        if (not RequestCheckUserCapability(request, 'moodle/ejudge_submits:comment')):
            raise Exception("Auth Error")

        author_id = RequestGetUserId(request)

        run_id = request.params['run_id']
        user_id = request.params['user_id']

        # Это XSS
        lines = html.escape(request.params['lines'])
        comment = html.escape(request.params['comment'])

        date = datetime.datetime.now()

        commentary = Comment(date=date,
                             run_id=0,  # TODO: 0 - заглушка. Новые run_id теперь кладуться в py_run_id.
                             contest_id=0,  # TODO: Теперь contest_id всегда 0.
                             user_id=user_id,
                             author_user_id=author_id,
                             lines=lines,
                             comment=comment,
                             is_read=False,
                             py_run_id=run_id)

        with transaction.manager:
            DBSession.add(commentary)
        return {"result": "ok"}
    except Exception as e:
        return {"result": "error", "message": e.__str__(), "stack": traceback.format_exc()}

def clone_contest(request):
    """
    Копирует контест с contest_id из url, дает ему первый свободный id
    Все moodle задачи ассоциированные с копируемым контестом, перенаправляет на контест копию
    !!!Текущая версия невалидно работает с контестами, которые уже являются копиями

    Требуется наличие папки empty_contest в /home/judges в которой хранится шаблон для пустого контеста
    """
    try:
        contest_id = int(request.matchdict["contest_id"])
    except ValueError:
        return {"status": "error", "message": "contest_id must be int"}

    contests = {int(c_id) for c_id in all_contests()}

    if contest_id not in contests:
        return {"status": "error", "message": "this contest does not exist"}        

    # скопируем xml
    new_contest_id = max(contests) + 1
    shutil.copyfile(get_contest_xml_config_path(contest_id), get_contest_xml_config_path(new_contest_id))
    os.chmod(get_contest_xml_config_path(new_contest_id), 0o777)
    # изменим в нем имя и id
    with open(get_contest_xml_config_path(new_contest_id), encoding='utf8') as new_xml_file:
        soup = BeautifulSoup(new_xml_file.read())
    contest_tag = soup.find("contest")
    contest_tag["id"] = str(new_contest_id)
    name_tag = soup.find("name")
    name_tag.string = name_tag.string + " (Копия 1)"
    with open(get_contest_xml_config_path(new_contest_id), "w", encoding='utf8') as xml_file:
        print(soup, file=xml_file, end="")

    # создаем каталог контеста и копируем все кроме var
    shutil.copytree(get_contest_path(contest_id), get_contest_path(new_contest_id), ignore=lambda src, names: ["var"])
    # копируем пустой var
    shutil.copytree("/home/judges/empty_contest/var", get_contest_path(new_contest_id) + "/var")

    # изменяем contest_id в serve.cfg
    with open(get_contest_path_conf(new_contest_id) + "serve.cfg", "r", encoding='utf8') as cfg_file:
        serv_cfg_lines = cfg_file.readlines()
    for i, line in enumerate(serv_cfg_lines):
        if line.startswith("contest_id"):
            serv_cfg_lines[i] = "contest_id = {}\n".format(new_contest_id)

    with open(get_contest_path_conf(new_contest_id) + "serve.cfg", "w", encoding='utf8') as cfg_file:
        cfg_file.write("".join(serv_cfg_lines))

    # вносим изменения в базу
    DBSession.query(EjudgeProblem)\
                    .filter(EjudgeProblem.ejudge_contest_id==contest_id)\
                    .update({"ejudge_contest_id": new_contest_id, "secondary_ejudge_contest_id": contest_id}) # переподвешиваем задачи
    DBSession.query(EjudgeContest)\
                    .filter(EjudgeContest.ejudge_int_id == contest_id)\
                    .update({"cloned": 1}) # проставляем флаг cloned
    
    return {"result": "OK", "new_contest_id": new_contest_id, "contest_id": contest_id}

def problem_submits(request):
    user_id = RequestGetUserId(request)
    user = DBSession.query(SimpleUser).filter(SimpleUser.id == user_id).first()
    lang_id = request.params["lang_id"]
    problem_id = request.matchdict["problem_id"]
    problem = DBSession.query(EjudgeProblem).filter(EjudgeProblem.id == problem_id).first()
    input_file = request.POST['file'].file
    filename = request.POST['file'].filename
    ejudge_url = request.registry.settings['ejudge.new_client_url']
    return {'res' : submit(input_file, problem.ejudge_contest_id, problem.problem_id, lang_id, user.login, user.password, filename, ejudge_url, user_id)}

def approve(request):
    try:
        id = int(html.escape(request.params['id'])) 
        ideal = DBSession.query(Ideal).filter_by(id=id).first()
        ideal.status = html.escape(request.params['status']) 
        with transaction.manager:
            DBSession.merge(ideal)
        return HTTPFound(location="/mod/statements/view3.php?chapterid=" + str(ideal.problem_id))
    except Exception as e: 
        return {"result" : "error", "message" : e.__str__(), "stack" : traceback.format_exc()}

def delete(request):
    try:
        link = html.escape(request.params['link'])
        user_id = DBSession.query(User).filter(User.id == RequestGetUserId(request)).first()
        stars = DBSession.query(Stars).filter_by(user_id=user_id.id).filter_by(link=link).all()
        for star in stars:
            DBSession.delete(star)
        return {"result" : "ok"}
    except Exception as e: 
        return {"result" : "error", "message" : e.__str__(), "stack" : traceback.format_exc()}

def get_by_problem(request):
    try:
        user_id = int(RequestGetUserId(request)) # TODO: 0 - not logged in, 1 - guest
        problem_id = html.escape(request.params['problem_id']) 
        ok_runs = get_ok_runs(user_id, problem_id)
        ideals = DBSession.query(Ideal).filter_by(problem_id=problem_id).filter_by(status=1).all()
        future_ideals = DBSession.query(Ideal).filter_by(problem_id=problem_id).filter_by(status=0).all()
        return {"result": "ok", "ideals":ideals, "future_ideals":future_ideals, "problem_id": problem_id, "ok_runs": ok_runs, 'is_admin': is_admin(request)}
    except Exception as e: 
        return {"result": "error", "message": e.__str__(), "stack": traceback.format_exc()}

def problem_set_preliminary(request):
    try:
        checkCapability(request)
        problem = DBSession.query(Problem).filter(Problem.id == request.matchdict['problem_id']).first()

        problem.sample_tests = request.params['sample_tests']
        with transaction.manager:
           DBSession.merge(problem)
        return {"result" : "ok", "content" : problem.sample_tests}
    except Exception as e:
        return {"result" : "error", "content" : e.__str__(), "stack" : traceback.format_exc()}

def add_hint(request):
    try:
        problem_id = int(html.escape(request.params['problem_id'])) 
        contest_id = int(html.escape(request.params['contest_id']))
        signature = html.escape(request.params['signature'])
        comment = html.escape(request.params['comment']) 
        hint = Hint(problem_id, contest_id, 0, signature, comment)
        with transaction.manager:
            DBSession.add(hint)
        return {"result" : "ok"}
    except Exception as e: 
        return {"result" : "error", "message" : e.__str__(), "stack" : traceback.format_exc()}

def get_ok_runs(user_id, problem_id):
    user = DBSession.query(User).filter_by(id=user_id).one()
    problem = DBSession.query(EjudgeProblem).filter_by(id=problem_id).one()
    user_ejudge_id = user.ejudge_id
    pr_id = problem.problem_id
    contest_id = problem.ejudge_contest_id

    ok_runs = DBSession.query(Run).filter_by(user_id=user_ejudge_id,
        prob_id=pr_id, contest_id=contest_id, status=0).all()
    ac_runs = DBSession.query(Run).filter_by(user_id=user_ejudge_id,
        prob_id=pr_id, contest_id=contest_id, status=8).all()
    return ok_runs + ac_runs

 def __init__(self, problem_id, run_id, contest_id, author_id, comment, status=0):
     self.problem_id = problem_id
     self.run_id = run_id
     self.contest_id = contest_id
     run = DBSession.query(Run).filter_by(contest_id=contest_id).filter_by(run_id=run_id).one()
     user = DBSession.query(User).filter_by(id=author_id).one()
     self.lang_id = run.lang_id
     self.lang = LANG[self.lang_id]
     self.code = get_run_code(run_id, contest_id)
     self.author_id = author_id
     self.author_name = user.firstname + " " + user.lastname
     self.comment = comment
     self.status = status

def get_team_monitor(request):
    try:
        statement_id = int(request.matchdict['statement_id'])

        user = DBSession.query(User).filter(User.id == RequestGetUserId(request)).first()
        statement = DBSession.query(Statement).filter(Statement.id == statement_id).first()
#        checkCapability(request)
        res = ""
        for k, v in statement.problems.items():
            res = res + "[" + str(k) + "] " + v.name
        return statement.name + " " + res
    except Exception as e:
        return {"result" : "error", "message" : e.__str__(), "stack" : traceback.format_exc()}

def getOrCreateContest(request, ejudge_contest_id):
    strId = getContestStrId(ejudge_contest_id);
    ejudgeCfg = EjudgeContestCfg("/home/judges/" + strId + "/conf/serve.cfg");
    contest = DBSession.query(EjudgeContest).filter(EjudgeContest.ejudge_int_id == ejudge_contest_id).first()
        
    tree = ElementTree()
    tree.parse("/home/judges/data/contests/" + strId + ".xml")
    contestName = tree.find("name").text
        
    if (contest == None):
        contest = EjudgeContest(contestName, request.matchdict['contest_id']);
        with transaction.manager:
            DBSession.add(contest);
    return [contest, ejudgeCfg]

def problem_add_test(request):
    try:
        checkCapability(request)
        s = xmlrpc.client.ServerProxy('http://localhost:7080/')

        problem = DBSession.query(EjudgeProblem).filter(EjudgeProblem.id == request.matchdict['problem_id']).first()
        conf = EjudgeContestCfg(number = problem.ejudge_contest_id)
        prob = conf.getProblem(problem.problem_id)

        cnt = 0
        flag = False
        while True:
            cnt += 1
            test_file_name = (prob.tests_dir + prob.test_pat) % cnt
            corr_file_name = (prob.tests_dir + prob.corr_pat) % cnt
            if not os.path.exists(test_file_name):
                flag = True
                break

        if flag:
            s.add_file(test_file_name, request.params['input_data'])
            s.add_file(corr_file_name, request.params['output_data'])
        return {"result" : "ok", "content" : test_file_name}
    except Exception as e:
        return {"result" : "error", "content" : e.__str__(), "stack" : traceback.format_exc()}

def get_suggested(request):
    try:
        suggested = DBSession.query(Ideal).filter_by(status=0).all()
        problems = sorted(list(set(suggest.problem_id for suggest in suggested)))
        return {"result": "ok", "problems": problems, 'suggested':suggested}
    except Exception as e: 
        return {"result": "error", "message": e.__str__(), "stack": traceback.format_exc()}

    def _get_monitor(self):
        author_id = RequestGetUserId(self.request)
        if author_id == -1:
            return {"result": "error", "message": 'Unauthorised'}

        link = self.request.matchdict['link']

        monitor = DBSession.query(MonitorLink) \
            .filter(MonitorLink.link == link)\
            .one_or_none()

        if monitor is None:
            return {"result": "error", "message": 'Monitor is not found'}

        query_params = monitor.internal_link

        url = 'http://localhost:12346/monitor?{}'.format(query_params)

        try:
            resp = requests.get(url, timeout=30)
            context = resp.json()
        except Exception as e:
            print('Request to :12346 failed!')
            raise

        return context

def problem_ant_submits(request):
    user_id = RequestGetUserId(request)
    user = DBSession.query(SimpleUser).filter(SimpleUser.id == user_id).first()
    run_id1 = request.params["run_id1"]
    run_id2 = request.params["run_id2"]
    run_id3 = request.params["run_id3"]
    run_id4 = request.params["run_id4"]
    json_names = request.params["json_names"]
    problem_id = request.matchdict["problem_id"]
    problem = DBSession.query(EjudgeProblem).filter(EjudgeProblem.id == problem_id).first()
    #input_file = request.POST['file'].file
    #filename = request.POST['file'].filename
    filename = "input_file.txt"
    input_file = io.StringIO("{0}\n{1}\n{2}\n{3}\n{4}".format(run_id1, run_id2, run_id3, run_id4, json_names))
    ejudge_url = request.registry.settings['ejudge.new_client_url']
    return {'res' : submit(input_file, problem.ejudge_contest_id, problem.problem_id, lang_id, user.login, user.password, filename, ejudge_url, user_id)}