/**
 * The MIT License
 * Copyright (c) 2014-2015 Nick Guletskii
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */
package org.ng200.openolympus.services;

import java.math.BigDecimal;
import java.util.List;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Table;
import org.jooq.impl.DSL;
import org.ng200.openolympus.SecurityExpressionConstants;
import org.ng200.openolympus.dto.UserRanking;
import org.ng200.openolympus.jooq.Tables;
import org.ng200.openolympus.jooq.tables.daos.ContestParticipationDao;
import org.ng200.openolympus.jooq.tables.daos.GroupDao;
import org.ng200.openolympus.jooq.tables.daos.TimeExtensionDao;
import org.ng200.openolympus.jooq.tables.daos.UserDao;
import org.ng200.openolympus.jooq.tables.pojos.User;
import org.ng200.openolympus.jooq.tables.records.UserRecord;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Service;

import com.google.common.collect.ImmutableList;

@Service
public class UserService {

	@Autowired
	private UserDao userDao;

	@Autowired
	private ContestParticipationDao contestParticipationDao;

	@Autowired
	private TimeExtensionDao timeExtensionDao;

	@Autowired
	private GroupDao groupDao;

	@Autowired
	private DSLContext dslContext;

	public UserService() {

	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public long countUnapprovedUsers() {
		// TODO: Check
		return dslContext.selectCount().from(Tables.USER)
				.where(Tables.USER.APPROVED.eq(false)).execute();
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN
			+ SecurityExpressionConstants.OR + '('
			+ SecurityExpressionConstants.IS_USER
			+ SecurityExpressionConstants.AND
			+ SecurityExpressionConstants.NO_CONTEST_CURRENTLY + ')')
	public long countUsers() {
		return userDao.count();
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public void deleteUser(final User user) {
		userDao.delete(user);

	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public void deleteUsers(List<User> users) {
		userDao.delete(users);
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public List<User> findAFewUsersWithNameContaining(final String name) {
		// TODO: use something better for searching...
		String pattern = "%" + name + "%";
		return dslContext
				.select(Tables.USER.fields())
				.from(Tables.USER)
				.where(

				Tables.USER.USERNAME.like(pattern)
						.or(Tables.USER.FIRST_NAME_MAIN.like(pattern))
						.or(Tables.USER.MIDDLE_NAME_MAIN.like(pattern))
						.or(Tables.USER.LAST_NAME_MAIN.like(pattern))
						.or(Tables.USER.FIRST_NAME_LOCALISED.like(pattern))
						.or(Tables.USER.MIDDLE_NAME_LOCALISED.like(pattern))
						.or(Tables.USER.LAST_NAME_LOCALISED.like(pattern))

				).limit(30).fetchInto(User.class);
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN
			+ SecurityExpressionConstants.OR + '('
			+ SecurityExpressionConstants.IS_USER
			+ SecurityExpressionConstants.AND
			+ SecurityExpressionConstants.NO_CONTEST_CURRENTLY + ')')
	public List<UserRanking> getArchiveRankPage(final int page,
			final int pageSize) {

		final Table<?> userTasks = dslContext
				.select(Tables.SOLUTION.USER_ID, Tables.SOLUTION.TASK_ID,
						Tables.SOLUTION.SCORE)
				.distinctOn(Tables.SOLUTION.USER_ID, Tables.SOLUTION.TASK_ID)
				.from(Tables.SOLUTION)
				.groupBy(Tables.SOLUTION.USER_ID, Tables.SOLUTION.TASK_ID,
						Tables.SOLUTION.SCORE)
				.orderBy(Tables.SOLUTION.USER_ID.asc(),
						Tables.SOLUTION.TASK_ID.asc(),
						Tables.SOLUTION.SCORE.desc()).asTable("users_tasks");

		final Field<BigDecimal> user_score = DSL
				.coalesce(DSL.sum(userTasks.field(Tables.SOLUTION.SCORE)),
						DSL.field("0")).as("user_score");
		List<Field<?>> fields = ImmutableList
				.<Field<?>> builder()
				.add(user_score)
				.add(

				DSL.rank()
						.over(DSL.orderBy(DSL.coalesce(
								DSL.sum(userTasks.field(Tables.SOLUTION.SCORE)),
								DSL.field("0")))).as("rank"))
				.add(Tables.USER.fields())

				.build();
		return dslContext
				.select(fields)
				.from(Tables.USER)
				.leftOuterJoin(userTasks)
				.on(Tables.USER.ID.eq(userTasks.field(Tables.SOLUTION.USER_ID)))
				.groupBy(Tables.USER.ID).orderBy(user_score).limit(pageSize)
				.offset(pageSize * (page - 1)).fetchInto(UserRanking.class);
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public List<User> getPendingUsers(int pageNumber, int pageSize) {
		return dslContext.selectFrom(Tables.USER)
				.where(Tables.USER.APPROVAL_EMAIL_SENT.eq(false))
				.limit(pageSize).offset((pageNumber - 1) * pageSize)
				.fetchInto(User.class);
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public User getUserById(final Long id) {
		return userDao.findById(id);
	}

	public User getUserByUsername(final String username) {
		return userDao.fetchOneByUsername(username);
	}

	@PreAuthorize(SecurityExpressionConstants.IS_ADMIN)
	public List<User> getUsersAlphabetically(final Integer pageNumber,
			final int pageSize) {
		return dslContext.select(Tables.USER.fields())
				.groupBy(Tables.USER.ID)
				.orderBy(Tables.USER.USERNAME).limit(pageSize)
				.offset(pageSize * (pageNumber - 1)).fetchInto(User.class);
	}

	public User saveUser(User user) {
		UserRecord userRecord = dslContext.newRecord(Tables.USER);
		userRecord.from(user);
		userRecord.store();
		userRecord.into(user);
		return user;
	}
}

@@ -6083,24 +6083,28 @@
 ntext.select
+From
 (Tables.USER
@@ -6099,33 +6099,24 @@
 (Tables.USER
-.fields()
 )%0A%09%09%09%09.group

Fix user list