package ca.corefacility.bioinformatics.irida.repositories;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.security.access.prepost.PreAuthorize;

import ca.corefacility.bioinformatics.irida.model.project.Project;
import ca.corefacility.bioinformatics.irida.model.user.User;

/**
 * Specialized repository for {@link Project}.
 * 
 */
public interface ProjectRepository extends IridaJpaRepository<Project, Long> {

	/**
	 * Load up a page of {@link Project}s, excluding the specified
	 * {@link Project}.
	 * 
	 * @param name
	 *            the name of the project to search for
	 * @param exclude
	 *            the project to exclude from results
	 * @param page
	 *            the page request
	 * @return a page of {@link Project}.
	 */
	@PreAuthorize("hasRole('ROLE_ADMIN')")
	@Query("from Project p where " + PROJECT_NAME_LIKE + " and " + EXCLUDE_PROJECT)
	public Page<Project> findAllProjectsByNameExcludingProject(final @Param("projectName") String name,
			final @Param("exclude") Project exclude, final Pageable page);

	static final String PROJECT_ID_LIKE = "(p.id like CONCAT('%', :projectName, '%'))";
	static final String PROJECT_NAME_LIKE = "(p.name like CONCAT('%', :projectName,'%'))";
	static final String PROJECT_ORGANISM_LIKE = "(p.organism like CONCAT('%', :organismName, '%'))";
	static final String EXCLUDE_PROJECT = "p != :exclude";
	static final String USER_ON_PROJECT = "(p in (select puj.project from ProjectUserJoin puj where puj.user = :forUser))";
	static final String USER_IN_GROUP = "(p in (select ugpj.project from UserGroupJoin ugj, UserGroupProjectJoin ugpj where ugj.group = ugpj.userGroup and ugj.user = :forUser))";
	static final String PROJECT_PERMISSIONS = "(" + USER_ON_PROJECT + " or " + USER_IN_GROUP + ")";

	/**
	 * Load a page of {@link Project}s for a specific {@link User}, excluding a
	 * {@link Project}.
	 * 
	 * @param name
	 *            the name of the project to search for
	 * @param exclude
	 *            the project to exclude from results
	 * @param user
	 *            the user account to load projects for
	 * @param page
	 *            the page request
	 * @return a page of {@link Project}.
	 */
	@Query("from Project p where " + PROJECT_NAME_LIKE + " and " + EXCLUDE_PROJECT + " and " + PROJECT_PERMISSIONS)
	public Page<Project> findProjectsByNameExcludingProjectForUser(final @Param("projectName") String name,
			final @Param("exclude") Project exclude, final @Param("forUser") User user, final Pageable page);

	/**
	 * Load a page of {@link Project}s for a specific {@link User}.
	 * 
	 * @param searchName
	 *            the name of the project to search for
	 * @param searchOrganism
	 *            the name of the organism to search for
	 * @param user
	 *            the user to load projects for
	 * @param page
	 *            the page request
	 * @return a page of {@link Project}.
	 */
	@Query("from Project p where ((" + PROJECT_NAME_LIKE + " or " + PROJECT_ID_LIKE + ") and " + PROJECT_ORGANISM_LIKE
			+ ") and " + PROJECT_PERMISSIONS)
	public Page<Project> findProjectsForUser(final @Param("projectName") String searchName,
			final @Param("organismName") String searchOrganism, final @Param("forUser") User user, final Pageable page);

	/**
	 * Load all projects in the system that match a name and organism.
	 * 
	 * @param searchName
	 *            the name to search for
	 * @param organismName
	 *            the organism to search for
	 * @param page
	 *            the page request
	 * @return a page of {@link Project}.
	 */
	@PreAuthorize("hasRole('ROLE_ADMIN')")
	@Query("from Project p where (" + PROJECT_NAME_LIKE + " and " + PROJECT_ORGANISM_LIKE + ")")
	public Page<Project> findAllProjectsByNameOrOrganism(final @Param("projectName") String searchName,
			final @Param("organismName") String organismName, final Pageable page);
}

@@ -604,613 +604,95 @@
 %09 * 
-Load up a page of %7B@link Project%7Ds, excluding the specified%0A%09 * %7B@link Project%7D.%0A%09 * %0A%09 * @param name%0A%09 *            the name of the project to search for%0A%09 * @param exclude%0A%09 *            the project to exclude from results%0A%09 * @param page%0A%09 *            the page request%0A%09 * @return a page of %7B@link Project%7D.%0A%09 */%0A%09@PreAuthorize(%22hasRole('ROLE_ADMIN')%22)%0A%09@Query(%22from Project p where %22 + PROJECT_NAME_LIKE + %22 and %22 + EXCLUDE_PROJECT)%0A%09public Page%3CProject%3E findAllProjectsByNameExcludingProject(final @Param(%22projectName%22) String name,%0A%09%09%09final @Param(%22exclude%22) Project exclude, final Pageable page);%0A
+Sub-expressions for filtering and paging projects on different property%0A%09 * names.%0A%09 */
 %0A%09st
@@ -1015,16 +1015,141 @@
 clude%22;%0A
+%09/**%0A%09 * Sub-expressions for filtering and paging projects on permissions (via%0A%09 * user groups and project membership).%0A%09 */%0A
 %09static 
@@ -1535,16 +1535,631 @@
 + %22)%22;%0A%0A
+%09/**%0A%09 * Load up a page of %7B@link Project%7Ds, excluding the specified%0A%09 * %7B@link Project%7D.%0A%09 * %0A%09 * @param name%0A%09 *            the name of the project to search for%0A%09 * @param exclude%0A%09 *            the project to exclude from results%0A%09 * @param page%0A%09 *            the page request%0A%09 * @return a page of %7B@link Project%7D.%0A%09 */%0A%09@PreAuthorize(%22hasRole('ROLE_ADMIN')%22)%0A%09@Query(%22from Project p where %22 + PROJECT_NAME_LIKE + %22 and %22 + EXCLUDE_PROJECT)%0A%09public Page%3CProject%3E findAllProjectsByNameExcludingProject(final @Param(%22projectName%22) String name,%0A%09%09%09final @Param(%22exclude%22) Project exclude, final Pageable page);%0A%0A
 %09/**%0A%09 *

Move the sub-expressions up to the top of the file and provide some basic comments about them.