package com.epam.ta.reportportal.dao;

import com.epam.ta.reportportal.commons.querygen.Filter;
import com.epam.ta.reportportal.commons.querygen.QueryBuilder;
import com.epam.ta.reportportal.entity.project.Project;
import com.epam.ta.reportportal.entity.project.ProjectInfo;
import com.epam.ta.reportportal.jooq.enums.JLaunchModeEnum;
import com.epam.ta.reportportal.jooq.enums.JStatusEnum;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.support.PageableExecutionUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

import static com.epam.ta.reportportal.dao.util.RecordMappers.PROJECT_MAPPER;
import static com.epam.ta.reportportal.jooq.Tables.*;
import static org.jooq.impl.DSL.*;

@Repository
public class ProjectRepositoryCustomImpl implements ProjectRepositoryCustom {

	@Autowired
	private DSLContext dsl;

	@Override
	public List<Project> findByFilter(Filter filter) {

		return dsl.fetch(QueryBuilder.newBuilder(filter).build()).map(PROJECT_MAPPER);
	}

	@Override
	public Page<Project> findByFilter(Filter filter, Pageable pageable) {
		return PageableExecutionUtils.getPage(
				dsl.fetch(QueryBuilder.newBuilder(filter).with(pageable).build()).map(PROJECT_MAPPER),
				pageable,
				() -> dsl.fetchCount(QueryBuilder.newBuilder(filter).build())
		);
	}

	@Override
	public Page<ProjectInfo> findProjectInfoByFilter(Filter filter, Pageable pageable, String mode) {
		return PageableExecutionUtils.getPage(dsl.with("filtered_project")
				.as(QueryBuilder.newBuilder(filter).with(pageable).build())
				.select(
						DSL.countDistinct(PROJECT_USER.USER_ID).as("usersQuantity"),
						DSL.countDistinct(choose().when(LAUNCH.MODE.eq(JLaunchModeEnum.valueOf(mode))
								.and(LAUNCH.STATUS.ne(JStatusEnum.IN_PROGRESS)), LAUNCH.ID)).as("launchesQuantity"),
						DSL.max(LAUNCH.START_TIME).as("lastRun"),
						PROJECT.ID,
						PROJECT.CREATION_DATE,
						PROJECT.NAME,
						PROJECT.PROJECT_TYPE
				)
				.from(DSL.table(name("filtered_project"))
						.leftJoin(PROJECT_USER)
						.on(table("filtered_project").field(PROJECT.ID.getName(), Long.class).eq(PROJECT_USER.PROJECT_ID))
						.leftJoin(LAUNCH)
						.on(table("filtered_project").field(PROJECT.ID.getName(), Long.class).eq(LAUNCH.PROJECT_ID)))
				.groupBy(PROJECT.ID, PROJECT.CREATION_DATE, PROJECT.NAME, PROJECT.PROJECT_TYPE)
				.fetch()
				.into(ProjectInfo.class), pageable, () -> dsl.fetchCount(QueryBuilder.newBuilder(filter).build()));
	}

	@Override
	public Optional<String> findPersonalProjectName(String username) {
		Optional<Project> projectOptional = Optional.ofNullable(dsl.select()
				.from(USERS)
				.join(PROJECT)
				.on(USERS.DEFAULT_PROJECT_ID.eq(PROJECT.ID))
				.where(USERS.LOGIN.eq(username))
				.fetchOne(PROJECT_MAPPER));
		return projectOptional.map(Project::getName);
	}

	@Override
	public List<Project> findUserProjects(String username) {
		return dsl.select()
				.from(PROJECT)
				.join(PROJECT_USER)
				.on(PROJECT_USER.PROJECT_ID.eq(PROJECT.ID))
				.join(USERS)
				.on(PROJECT_USER.USER_ID.eq(USERS.ID))
				.where(USERS.LOGIN.eq(username))
				.fetch(PROJECT_MAPPER);
	}
}

@@ -2247,36 +2247,41 @@
 USER)%0A%09%09%09%09%09%09.on(
-tabl
+field(nam
 e(%22filtered_proj
@@ -2280,32 +2280,26 @@
 red_project%22
-).field(
+, 
 PROJECT.ID.g
@@ -2298,34 +2298,40 @@
 ECT.ID.getName()
-, 
+)).cast(
 Long.class).eq(P
@@ -2389,20 +2389,25 @@
 %09%09%09%09.on(
-tabl
+field(nam
 e(%22filte
@@ -2422,16 +2422,10 @@
 ect%22
-).field(
+, 
 PROJ
@@ -2440,18 +2440,24 @@
 etName()
-, 
+)).cast(
 Long.cla

fix aliased table