/**
 * 
 */
package de.bstly.board.businesslogic;

import java.math.BigInteger;
import java.time.Instant;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import com.google.common.collect.Lists;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;

import de.bstly.board.controller.model.EntryFilter;
import de.bstly.board.model.Bookmarks;
import de.bstly.board.model.Entry;
import de.bstly.board.model.QEntry;
import de.bstly.board.model.QFlag;
import de.bstly.board.model.QVote;
import de.bstly.board.model.support.FlaggedStatus;
import de.bstly.board.model.support.Types;
import de.bstly.board.model.support.VoteType;
import de.bstly.board.repository.EntryRepository;
import de.bstly.board.repository.VoteRepository;

/**
 * The Class EntryManager.
 */
@Component
public class EntryManager {

	@Autowired
	private EntryRepository entryRepository;
	@Autowired
	private TagManager tagManager;
	@Autowired
	private CommentManager commentManager;
	@Autowired
	private VoteManager voteManager;
	@Autowired
	private VoteRepository voteRepository;
	@Autowired
	private BookmarksManager bookmarksManager;
	@Autowired
	private SettingsManager settingsManager;
	@Autowired
	private FlagManager flagManager;
	@Autowired
	private JPAQueryFactory jpaQueryFactory;
	@Autowired
	private EntityManager em;

	private QEntry qEntry = QEntry.entry;
	private QVote qVote = QVote.vote;
	private QFlag qFlag = QFlag.flag;

	static final String UPVOTES_QUERY = "SELECT DISTINCT upvote.target,COUNT(upvote.id) AS count FROM votes as upvote WHERE upvote.type = 0 AND upvote.target_type = 1 GROUP BY upvote.target";

	static final String DOWNVOTES_QUERY = "SELECT DISTINCT downvote.target,COUNT(downvote.id) AS count FROM votes as downvote WHERE downvote.type = 1 AND downvote.target_type = 1 GROUP BY downvote.target";

	static final String COMMENTS_QUERY = "SELECT DISTINCT comment.target,MAX(comment.created) as last,COUNT(comment.id) AS count FROM comments as comment WHERE comment.flagged_status = :flag GROUP BY comment.target";

	static final String RANK_CALCULATION_QUERY = "SELECT DISTINCT entry.*, (IFNULL(upvote.count,0) - IFNULL(downvote.count,0)) as points,  (IFNULL(upvote.count,0) - IFNULL(downvote.count,0)) / IF(:gravity > 0, POW(TIMESTAMPDIFF(HOUR, entry.created, :before)+2,:gravity), 1) AS ranking FROM entries AS entry LEFT JOIN ("
			+ UPVOTES_QUERY
			+ ") AS upvote ON upvote.target = entry.id LEFT JOIN ("
			+ DOWNVOTES_QUERY
			+ ") AS downvote ON downvote.target = entry.id %s";

	static final String DATE_QUERY = "SELECT DISTINCT entry.* FROM entries AS entry %s";

	static final String USER_QUERY = "SELECT DISTINCT entry.* FROM entries AS entry %s ORDER BY entry.created :order";

	static final String COMMENT_CALCULATION_QUERY = "SELECT DISTINCT entry.*, IFNULL(comment.count,0) as comments,  IFNULL(comment.count,0) /  IF(:gravity > 0, POW(TIMESTAMPDIFF(HOUR, comment.last, :before)+2,:gravity), 1) AS ranking FROM entries AS entry LEFT JOIN ("
			+ COMMENTS_QUERY
			+ ") AS comment ON comment.target = entry.id %s";

	static final String LAST_COMMENT_QUERY = "SELECT DISTINCT entry.* FROM entries AS entry LEFT JOIN ("
			+ COMMENTS_QUERY
			+ ") AS comment ON comment.target = entry.id %s ";

	static final String COUNT_QUERY = "SELECT count(entry.id) FROM entries as entry %s";

	/**
	 * Fetch by ranking.
	 *
	 * @param username the username
	 * @param filter the filter
	 * @param gravity the gravity
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchByRanking(String username, EntryFilter filter, float gravity, int page,
			int size, boolean asc) {
		Query query = createEntryQuery(RANK_CALCULATION_QUERY, username, filter,
				asc ? "ranking ASC, entry.created ASC" : "ranking DESC, entry.created DESC");
		query.setParameter("gravity", gravity);
		query.setFirstResult((page) * size);
		query.setMaxResults(size);
		@SuppressWarnings("unchecked")
		List<Entry> list = query.getResultList();
		Query queryTotal = createCountQuery(COUNT_QUERY, username, filter);
		long countResult = ((BigInteger) queryTotal.getSingleResult()).longValue();
		return new PageImpl<Entry>(list, PageRequest.of(page, size), countResult);
	}

	/**
	 * Fetch by date.
	 *
	 * @param username the username
	 * @param filter the filter
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchByDate(String username, EntryFilter filter, int page, int size,
			boolean asc) {
		Query query = createEntryQuery(DATE_QUERY, username, filter,
				asc ? "entry.created ASC" : "entry.created DESC");
		query.setFirstResult((page) * size);
		query.setMaxResults(size);
		@SuppressWarnings("unchecked")
		List<Entry> list = query.getResultList();
		Query queryTotal = createCountQuery(COUNT_QUERY, username, filter);
		long countResult = ((BigInteger) queryTotal.getSingleResult()).longValue();
		return new PageImpl<Entry>(list, PageRequest.of(page, size), countResult);
	}

	/**
	 * Fetch by comments.
	 *
	 * @param username the username
	 * @param filter the filter
	 * @param gravity the gravity
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchByComments(String username, EntryFilter filter, float gravity,
			int page, int size, boolean asc) {
		Query query = createEntryQuery(COMMENT_CALCULATION_QUERY, username, filter,
				asc ? "ranking ASC, entry.created ASC" : "ranking DESC, entry.created DESC");
		query.setParameter("gravity", gravity);
		query.setFirstResult((page) * size);
		query.setMaxResults(size);
		@SuppressWarnings("unchecked")
		List<Entry> list = query.getResultList();
		Query queryTotal = createCountQuery(COUNT_QUERY, username, filter);
		long countResult = ((BigInteger) queryTotal.getSingleResult()).longValue();
		return new PageImpl<Entry>(list, PageRequest.of(page, size), countResult);
	}

	/**
	 * Fetch by last comment.
	 *
	 * @param username the username
	 * @param filter the filter
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchByLastComment(String username, EntryFilter filter, int page, int size,
			boolean asc) {
		Query query = createEntryQuery(LAST_COMMENT_QUERY, username, filter,
				asc ? "comment.last ASC, entry.created ASC"
						: "comment.last DESC, entry.created DESC");
		query.setFirstResult((page) * size);
		query.setMaxResults(size);
		@SuppressWarnings("unchecked")
		List<Entry> list = query.getResultList();
		Query queryTotal = createCountQuery(COUNT_QUERY, username, filter);
		long countResult = ((BigInteger) queryTotal.getSingleResult()).longValue();
		return new PageImpl<Entry>(list, PageRequest.of(page, size), countResult);
	}

	/**
	 * Fetch by user.
	 *
	 * @param fromUser the from user
	 * @param username the username
	 * @param filter the filter
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchByUser(String fromUser, String username, EntryFilter filter, int page,
			int size, boolean asc) {
		filter.setAdditional("AND entry.author = :username");
		Query query = createEntryQuery(DATE_QUERY, username, filter,
				asc ? "entry.created ASC" : "entry.created DESC");
		query.setParameter("username", username);
		query.setFirstResult((page) * size);
		query.setMaxResults(size);
		@SuppressWarnings("unchecked")
		List<Entry> list = query.getResultList();
		Query queryTotal = createCountQuery(COUNT_QUERY, username, filter);
		queryTotal.setParameter("username", username);
		long countResult = ((BigInteger) queryTotal.getSingleResult()).longValue();
		return new PageImpl<Entry>(list, PageRequest.of(page, size), countResult);
	}

	/**
	 * Creates the entry query.
	 *
	 * @param rawQuery the raw query
	 * @param username the username
	 * @param filter the filter
	 * @param orderBy the order by
	 * @return the query
	 */
	protected Query createEntryQuery(String rawQuery, String username, EntryFilter filter,
			String orderBy) {
		String filterString = "";

		String tagsString = "";
		if (filter.getTags() != null && !filter.getTags().isEmpty()) {
			for (int index = 0; index < filter.getTags().size(); index++) {
				tagsString += "'"
						+ filter.getTags().get(index)
						+ "'";
				if (index < filter.getTags().size() - 1) {
					tagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(tagsString)) {
			filterString += " INNER JOIN tags as tag ON entry.id = tag.target AND tag.tag IN ("
					+ tagsString
					+ ")";
		}
		
		String fixedTagsString = "";
		if (filter.getFixedTags() != null && !filter.getFixedTags().isEmpty()) {
			for (int index = 0; index < filter.getFixedTags().size(); index++) {
				fixedTagsString += "'"
						+ filter.getFixedTags().get(index)
						+ "'";
				if (index < filter.getFixedTags().size() - 1) {
					fixedTagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(fixedTagsString)) {
			filterString += " INNER JOIN tags as fixedTag ON entry.id = fixedTag.target AND fixedTag.tag IN ("
					+ fixedTagsString
					+ ")";
		}

		boolean author = false;
		if (filter.getDate() != null) {
			filterString += " WHERE entry.created < :before";
		} else {
			filter.setDate(Instant.now());
			author = true;
			filterString += " WHERE (entry.created < :before OR entry.author = :author)";
		}

		if (filter.getFlaggedStatus() == null) {
			filter.setFlaggedStatus(FlaggedStatus.NORMAL);
		}

		filterString += " AND entry.flagged_status = :flag";

		if (filter.getEntryType() != null) {
			filterString += " AND entry.entry_type = :type";
		}

		if (StringUtils.hasText(filter.getAdditional())) {
			filterString += " "
					+ filter.getAdditional();
		}

		String excludedTagsString = "";
		if (filter.getExcludedTags() != null && !filter.getExcludedTags().isEmpty()) {
			for (int index = 0; index < filter.getExcludedTags().size(); index++) {
				excludedTagsString += "'"
						+ filter.getExcludedTags().get(index)
						+ "'";
				if (index < filter.getExcludedTags().size() - 1) {
					excludedTagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(excludedTagsString)) {
			filterString += " AND NOT EXISTS (SELECT * FROM tags as excludedTag WHERE entry.id = excludedTag.target AND excludedTag.tag IN ("
					+ excludedTagsString
					+ "))";
		}
		
		String fixedExcludedTagsString = "";
		if (filter.getFixedExcludedTags() != null && !filter.getFixedExcludedTags().isEmpty()) {
			for (int index = 0; index < filter.getFixedExcludedTags().size(); index++) {
				fixedExcludedTagsString += "'"
						+ filter.getFixedExcludedTags().get(index)
						+ "'";
				if (index < filter.getFixedExcludedTags().size() - 1) {
					fixedExcludedTagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(fixedExcludedTagsString)) {
			filterString += " AND NOT EXISTS (SELECT * FROM tags as fixedExcludedTag WHERE entry.id = fixedExcludedTag.target AND fixedExcludedTag.tag IN ("
					+ fixedExcludedTagsString
					+ "))";
		}

		if (StringUtils.hasText(orderBy)) {
			filterString += " ORDER BY "
					+ orderBy;
		}

		Query query = em.createNativeQuery(String.format(rawQuery, filterString), Entry.class);
		query.setParameter("before", filter.getDate());
		if (author) {
			query.setParameter("author", username);
		}

		if (filter.getEntryType() != null) {
			query.setParameter("type", filter.getEntryType().toString());
		}

		query.setParameter("flag", filter.getFlaggedStatus().toString());

		return query;
	}

	/**
	 * Creates the count query.
	 *
	 * @param rawQuery the raw query
	 * @param username the username
	 * @param filter the filter
	 * @return the query
	 */
	protected Query createCountQuery(String rawQuery, String username, EntryFilter filter) {
		String filterString = "";

		String tagsString = "";
		if (filter.getTags() != null && !filter.getTags().isEmpty()) {
			for (int index = 0; index < filter.getTags().size(); index++) {
				tagsString += "'"
						+ filter.getTags().get(index)
						+ "'";
				if (index < filter.getTags().size() - 1) {
					tagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(tagsString)) {
			filterString += " INNER JOIN tags as tag ON entry.id = tag.target AND tag.tag IN ("
					+ tagsString
					+ ")";
		}

		boolean author = false;
		if (filter.getDate() != null) {
			filterString += " WHERE entry.created < :before";
		} else {
			filter.setDate(Instant.now());
			author = true;
			filterString += " WHERE (entry.created < :before OR entry.author = :author)";
		}

		if (filter.getFlaggedStatus() == null) {
			filter.setFlaggedStatus(FlaggedStatus.NORMAL);
		}

		filterString += " AND entry.flagged_status = :flag";

		if (filter.getEntryType() != null) {
			filterString += " AND entry.entry_type = :type";
		}

		if (StringUtils.hasText(filter.getAdditional())) {
			filterString += " "
					+ filter.getAdditional();
		}

		String excludedTagsString = "";
		if (filter.getExcludedTags() != null && !filter.getExcludedTags().isEmpty()) {
			for (int index = 0; index < filter.getExcludedTags().size(); index++) {
				excludedTagsString += "'"
						+ filter.getExcludedTags().get(index)
						+ "'";
				if (index < filter.getExcludedTags().size() - 1) {
					excludedTagsString += ",";
				}
			}
		}

		if (StringUtils.hasText(excludedTagsString)) {
			filterString += " AND NOT EXISTS (SELECT * FROM tags as excludedTag WHERE entry.id = excludedTag.target AND excludedTag.tag IN ("
					+ excludedTagsString
					+ "))";
		}

		Query query = em.createNativeQuery(String.format(rawQuery, filterString));
		query.setParameter("before", filter.getDate());
		if (author) {
			query.setParameter("author", username);
		}

		if (filter.getEntryType() != null) {
			query.setParameter("type", filter.getEntryType().toString());
		}

		query.setParameter("flag", filter.getFlaggedStatus().toString());

		return query;
	}

	/**
	 * Fetch flagged.
	 *
	 * @param page the page
	 * @param size the size
	 * @param asc the asc
	 * @return the page
	 */
	public Page<Entry> fetchFlagged(int page, int size, boolean asc) {
		Sort sort = Sort.by(asc ? Order.asc("created") : Order.desc("created"));
		JPAQuery<Entry> query = jpaQueryFactory.selectFrom(qEntry).leftJoin(qFlag)
				.on(qEntry.id.eq(qFlag.target)).where(qFlag.targetType.eq(Types.entry))
				.groupBy(qFlag.target);
		return new PageImpl<Entry>(query
				.orderBy(new OrderSpecifier<>(asc ? com.querydsl.core.types.Order.ASC
						: com.querydsl.core.types.Order.DESC, qEntry.created))
				.limit(size).offset(page * size).fetch(), PageRequest.of(page, size, sort),
				query.fetchCount());
	}

	/**
	 * Fetch by bookmarks.
	 *
	 * @param username the username
	 * @param page the page
	 * @param size the size
	 * @return the page
	 */
	public Page<Entry> fetchByBookmarks(String username, int page, int size) {
		Bookmarks bookmarks = bookmarksManager.get(username);

		if (bookmarks.getEntries() == null) {
			bookmarks.setEntries(Lists.newArrayList());
		}

		return entryRepository.findAll(qEntry.id.in(bookmarks.getEntries()),
				PageRequest.of(page, size, Sort.by(Order.desc("created"))));
	}

	/**
	 * Apply metadata.
	 *
	 * @param username the username
	 * @param karma the karma
	 * @param entry the entry
	 * @param ignore the ignore
	 */
	public void applyMetadata(String username, long karma, Entry entry, List<String> ignore) {

		entry.setTags(tagManager.getForTarget(entry.getId()));

		ignore.addAll(entry.getMetadata().keySet());

		if (!ignore.contains("author")) {
			entry.getMetadata().put("author", entry.getAuthor().equals(username));
		}

		if (!ignore.contains("edit")) {
			entry.getMetadata().put("edit", entry.getAuthor().equals(username)
					&& entry.getCreated().isAfter(Instant.now()));
		}

		if (!ignore.contains("comments")) {
			entry.getMetadata().put("comments", commentManager.count(entry.getId()));
		}

		if (!ignore.contains("points")) {
			entry.getMetadata().put("points", voteManager.getPoints(entry.getId(), Types.entry));
		}

		if (!ignore.contains("bookmark")) {
			entry.getMetadata().put("bookmark",
					!bookmarksManager.hasEntry(username, entry.getId()));
		}

		if (!ignore.contains("removeBookmark")) {
			entry.getMetadata().put("removeBookmark",
					bookmarksManager.hasEntry(username, entry.getId()));
		}

		if (!ignore.contains("upvoted")) {
			entry.getMetadata().put("upvoted",
					voteRepository.exists(qVote.target.eq(entry.getId())
							.and(qVote.targetType.eq(Types.entry)).and(qVote.type.eq(VoteType.up))
							.and(qVote.author.equalsIgnoreCase(username))));
		}

		if (!ignore.contains("downvoted")) {
			entry.getMetadata().put("downvoted",
					voteRepository.exists(qVote.target.eq(entry.getId())
							.and(qVote.targetType.eq(Types.entry)).and(qVote.type.eq(VoteType.down))
							.and(qVote.author.equalsIgnoreCase(username))));
		}

		if (!username.equals(entry.getAuthor()) && !ignore.contains("flag")) {
			entry.getMetadata().put("flag",
					flagManager.get(username, entry.getId(), Types.entry) == null);
		}

		if (!username.equals(entry.getAuthor()) && !ignore.contains("unflag")) {
			entry.getMetadata().put("unflag",
					flagManager.get(username, entry.getId(), Types.entry) != null);
		}

		if (!ignore.contains("flagged")) {
			entry.getMetadata().put("flagged",
					flagManager.getFlags(entry.getId(), Types.entry) > 0);
		}

		if (voteRepository
				.exists(qVote.target.eq(entry.getId()).and(qVote.targetType.eq(Types.entry))
						.and(qVote.author.equalsIgnoreCase(username)))) {
			if (!ignore.contains("unvote")) {
				entry.getMetadata().put("unvote", true);
			}
		} else {
			if (!ignore.contains("vote")) {
				entry.getMetadata().put("vote", true);
			}

			if (!ignore.contains("downvote") && karma >= settingsManager.getUnvoteThresh()) {
				entry.getMetadata().put("downvote", true);
			}
		}
	}

	/**
	 * Apply metadata.
	 *
	 * @param username the username
	 * @param karma the karma
	 * @param entries the entries
	 * @param ignore the ignore
	 */
	public void applyMetadata(String username, long karma, List<Entry> entries,
			List<String> ignore) {
		for (Entry entry : entries) {
			applyMetadata(username, karma, entry, ignore);
		}
	}

	/**
	 * Exists.
	 *
	 * @param id the id
	 * @return true, if successful
	 */
	public boolean exists(Long id) {
		return entryRepository.existsById(id);
	}

	/**
	 * Gets the.
	 *
	 * @param id the id
	 * @return the entry
	 */
	public Entry get(Long id) {
		return entryRepository.findById(id).orElse(null);
	}

	/**
	 * Save.
	 *
	 * @param entry the entry
	 * @return the entry
	 */
	public Entry save(Entry entry) {
		List<String> tags = Lists.newArrayList();
		if (entry.getTags() != null) {
			tags = Lists.newArrayList(entry.getTags());

			if (tags.size() > settingsManager.getMaxTags()) {
				tags = tags.subList(0, settingsManager.getMaxTags() - 1);
			}
		}

		entry = entryRepository.save(entry);
		tagManager.setForTarget(entry.getId(), tags);
		return entry;
	}

	/**
	 * Delete.
	 *
	 * @param entry the entry
	 */
	public void delete(Entry entry) {
		tagManager.deleteByTarget(entry.getId());
		commentManager.deleteByTarget(entry.getId());
		voteManager.deleteByTarget(entry.getId(), Types.entry);
		bookmarksManager.removeEntry(entry.getId());
		flagManager.deleteByTarget(entry.getId(), Types.entry);
		entryRepository.delete(entry);
	}

	/**
	 * Gets the points.
	 *
	 * @param entryId the entry id
	 * @return the points
	 */
	public long getPoints(Long entryId) {
		long upvotes = voteRepository.count(qVote.targetType.eq(Types.entry)
				.and(qVote.type.eq(VoteType.up)).and(qVote.target.eq(entryId)));
		long downvotes = voteRepository.count(qVote.targetType.eq(Types.entry)
				.and(qVote.type.eq(VoteType.down)).and(qVote.target.eq(entryId)));
		return upvotes - downvotes;
	}

	/**
	 * Gets the user points.
	 *
	 * @param entryId the entry id
	 * @param username the username
	 * @return the user points
	 */
	public long getUserPoints(Long entryId, String username) {
		long upvotes = voteRepository.count(qVote.targetType.eq(Types.entry)
				.and(qVote.type.eq(VoteType.up).and(qVote.author.notEqualsIgnoreCase(username)))
				.and(qVote.target.eq(entryId)));
		long downvotes = voteRepository.count(qVote.targetType.eq(Types.entry)
				.and(qVote.type.eq(VoteType.down).and(qVote.author.notEqualsIgnoreCase(username)))
				.and(qVote.target.eq(entryId)));
		return upvotes - downvotes;
	}

}
