package com.sonymusic.dx.dxadvsearchservice.service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sonymusic.dx.dxadvsearchservice.dto.CompanyDTO;
import com.sonymusic.dx.dxadvsearchservice.dto.LabelDTO;
import com.sonymusic.dx.dxadvsearchservice.dto.ProjectDTO;
import com.sonymusic.dx.dxadvsearchservice.dto.ProjectTypeDTO;
import com.sonymusic.dx.dxadvsearchservice.model.ListModel;
import com.sonymusic.dx.dxadvsearchservice.model.RecordingProjectModel;
import com.sonymusic.dx.dxadvsearchservice.searchModel.RecordingProjectSearchModel;
import com.sonymusic.dx.dxadvsearchservice.util.ConvertString;
import com.sonymusic.dx.dxadvsearchservice.util.DxSearchUtil;
import com.sonymusic.dx.persistence.gras.Company;
import com.sonymusic.dx.persistence.gras.Country;
import com.sonymusic.dx.persistence.gras.Label;
import com.sonymusic.dx.persistence.gras.Participant;
import com.sonymusic.dx.persistence.gras.Product;
import com.sonymusic.dx.persistence.gras.ProductVersion;
import com.sonymusic.dx.persistence.gras.RecordingProject;
import com.sonymusic.dx.persistence.gras.RecordingProjectCountry;
import com.sonymusic.dx.persistence.gras.RecordingProjectParticipant;
import com.sonymusic.dx.persistence.gras.RecordingProjectType;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Tuple;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.JoinType;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;

@Service
public class RecordingProjectService {

	@PersistenceContext(name = "dx-gras-persistence")
	private EntityManager em;

	@Autowired
	private ConvertString converter;

	private final Logger log = LoggerFactory.getLogger(getClass());

	public ListModel<RecordingProjectModel> advSearchProjects(RecordingProjectSearchModel projects, int page, int size)
			throws ParseException {
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<ProjectDTO> cq1 = cb.createQuery(ProjectDTO.class);
		Root<RecordingProject> root = cq1.from(RecordingProject.class);
		Join<RecordingProject, RecordingProjectType> recordingProjectRecordingProjectTypeJoin = root
				.join("recordingProjectType", JoinType.LEFT);
		Join<RecordingProject, Label> recordingProjectLabelJoin = root.join("label", JoinType.LEFT);
		Join<RecordingProject, Company> recordingProjectCompanyJoin = root.join("initiatedByCompany", JoinType.LEFT);
		Join<RecordingProject, RecordingProjectParticipant> recordingProjectRecordingProjectParticipantJoin = root
				.join("recordingProjectParticipants", JoinType.LEFT);
		Join<RecordingProjectParticipant, Participant> recordingProjectParticipantParticipantJoin = recordingProjectRecordingProjectParticipantJoin
				.join("participant");
		Join<RecordingProject, RecordingProjectCountry> recordingProjectRecordingProjectCountryJoin = root
				.join("recordingProjectCountries", JoinType.LEFT);
		Predicate projectPredicates = buildPredicates(cb, root, projects);
		cq1.where(projectPredicates);
		cq1.select(cb.construct(ProjectDTO.class, root.get("recordingProjectId"),
				cb.construct(ProjectTypeDTO.class,
						recordingProjectRecordingProjectTypeJoin.get("recordingProjectTypeKey"),
						recordingProjectRecordingProjectTypeJoin.get("recordingProjectTypeName")),
				root.get("recordingProjectTitle"), root.get("recordingProjectNumber"), root.get("isConfidential"),
				cb.construct(LabelDTO.class, recordingProjectLabelJoin.get("labelKey"),
						recordingProjectLabelJoin.get("labelName")),
				root.get("repertoireOwner").get("companyName"),
				cb.construct(CompanyDTO.class, recordingProjectCompanyJoin.get("companyKey"),
						recordingProjectCompanyJoin.get("companyName")),
				root.get("origRecordingProjectNumber"), cb.construct(CompanyDTO.class,
						recordingProjectCompanyJoin.get("companyKey"), recordingProjectCompanyJoin.get("companyName")),
				root.get("raasInitDate")
		));
		cq1.distinct(true);
		List<ProjectDTO> projectDTOS = em.createQuery(cq1).setFirstResult((page - 1) * size).setMaxResults(size)
				.getResultList();
		List<Long> projectIds = projectDTOS.stream().map(ProjectDTO::getRecordingProjectId).distinct()
				.filter(Objects::nonNull).toList();
		Map<Long, Long> productCount = null;
		if (!projectIds.isEmpty()) {
			CriteriaQuery<Tuple> pq = cb.createTupleQuery();
			Root<RecordingProject> pr = pq.from(RecordingProject.class);
			Join<RecordingProject, RecordingProjectParticipant> rpp = pr.join("recordingProjectParticipants",
					JoinType.LEFT);
			Join<RecordingProjectParticipant, Participant> part = rpp.join("participant", JoinType.LEFT);
			pq.multiselect(pr.get("recordingProjectId").alias("projectKey"), part.get("fullName").alias("fullName"));
			pq.where(pr.get("recordingProjectId").in(projectIds), cb.isTrue(rpp.get("isProductMainArtist")));
			List<Tuple> rows = em.createQuery(pq).getResultList();
			Map<Long, List<String>> nameByProject = rows.stream()
					.map(t -> Map.entry((Long) t.get("projectKey"), (String) t.get("fullName")))
					.filter(e -> e.getValue() != null && !e.getValue().isBlank()).collect(Collectors.groupingBy(
							Map.Entry::getKey, Collectors.mapping(Map.Entry::getValue, Collectors.toList())));
			for (ProjectDTO dto : projectDTOS) {
				List<String> names = nameByProject.getOrDefault(dto.getRecordingProjectId(), Collections.emptyList());
				String joined = names.stream().distinct().collect(Collectors.joining(", "));
				dto.setMainArtist(joined);
			}
			CriteriaQuery<Tuple> lfNo = cb.createTupleQuery();
			Root<RecordingProject> rpNo = lfNo.from(RecordingProject.class);
			Join<RecordingProject, RecordingProjectCountry> rrp1 = rpNo.join("recordingProjectCountries",
					JoinType.LEFT);
			Join<RecordingProjectCountry, Country> rpc = rrp1.join("country", JoinType.LEFT);
			lfNo.multiselect(rpNo.get("recordingProjectId").alias("projectKey"),
					rrp1.get("sapProfitCenterKey").alias("sapProfitCenterKey"));
			lfNo.where(rpNo.get("recordingProjectId").in(projectIds));
			List<Tuple> rows1 = em.createQuery(lfNo).getResultList();
			Map<Long, List<String>> nameOfSap = rows1.stream()
					.filter(t -> t.get("projectKey") != null && t.get("sapProfitCenterKey") != null)
					.map(t -> Map.entry((Long) t.get("projectKey"), (String) t.get("sapProfitCenterKey")))
					.collect(Collectors.groupingBy(Map.Entry::getKey,
							Collectors.mapping(Map.Entry::getValue, Collectors.toList())));
			for (ProjectDTO dto : projectDTOS) {
				List<String> nameOfSapJoin = nameOfSap.getOrDefault(dto.getRecordingProjectId(),
						Collections.emptyList());
				String sapJoined = nameOfSapJoin.stream().distinct().sorted(String.CASE_INSENSITIVE_ORDER)
						.collect(Collectors.joining(", "));
				dto.setSapProfitCenter(sapJoined);
			}

			CriteriaQuery<Tuple> cqp = cb.createTupleQuery();
			Root<RecordingProject> recordingProject = cqp.from(RecordingProject.class);
			Join<RecordingProject, ProductVersion> recordingProjectProductVersionJoin = recordingProject
					.join("productVersions", JoinType.INNER);
			Join<ProductVersion, Product> productVersionProductJoin = recordingProjectProductVersionJoin
					.join("products", JoinType.INNER);
			Predicate projectFilter = recordingProject.get("recordingProjectId").in(projectIds);
			cqp.multiselect(recordingProject.get("recordingProjectId").alias("recordingProjectId"),
					cb.count(productVersionProductJoin.get("productNumber")).alias("productCount")).where(projectFilter)
					.groupBy(recordingProject.get("recordingProjectId"));
			List<Tuple> result = em.createQuery(cqp).getResultList();
			productCount = new HashMap<>();
			for (Tuple tuple : result) {
				Long projectId = (Long) tuple.get("recordingProjectId");
				Long count = (Long) tuple.get("productCount");
				productCount.put(projectId, count);
			}
		}
		System.out.println(productCount);
		CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
		Root<RecordingProject> countRoot = countQuery.from(RecordingProject.class);
		countQuery.select(cb.count(countRoot));
		countQuery.where(buildPredicates(cb, countRoot, projects));
		Long totalRecords = em.createQuery(countQuery).getSingleResult();
		List<RecordingProjectModel> projectListModel = new ArrayList<>();
		RecordingProjectModel projectResponseModel = null;
		for (ProjectDTO result : projectDTOS) {
			if (!result.isConfidential()) {
				projectResponseModel = new RecordingProjectModel();
				projectResponseModel.setProjectId(result.getRecordingProjectId());
				projectResponseModel.setProjectTitle(result.getProjectTitle());
				projectResponseModel.setProjectNumber(result.getProjectNumber());
				projectResponseModel.setProjectType(result.getProjectType().getRecordingProjectTypeName());
				projectResponseModel.setInitiator(result.getInitiator().getCompanyName());
				projectResponseModel.setMaintenanceOwner(result.getMaintenanceOwner().getCompanyName());
				projectResponseModel.setLabel(result.getLabel().getLabelName());
				projectResponseModel.setMainArtist(result.getMainArtist());
				projectResponseModel.setConfidential(result.isConfidential());
				projectResponseModel.setRepertoireOwner(result.getRepertoireOwner());
				projectResponseModel.setSapProfitCenter(result.getSapProfitCenter());
				projectResponseModel.setOriginalProjectNo(result.getOrigRecordingProjectNumber());
				if (result.getInitiatedDate() != null) {
					String inputDate = result.getInitiatedDate();
					SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
					Date parsedDate = format.parse(inputDate);
					SimpleDateFormat displayDate = new SimpleDateFormat(DxSearchUtil.DATE_FORMAT_dd_MMM_yyyy);
					String formattedDate = displayDate.format(parsedDate);
					projectResponseModel.setInitiatedDate(formattedDate);
				}
				Long count = productCount.get(result.getRecordingProjectId());
				if (count != null) {
					projectResponseModel.setNoOfProducts(count);
				}
				projectListModel.add(projectResponseModel);
			} else {
				projectResponseModel = new RecordingProjectModel();
				projectResponseModel.setProjectNumber(result.getProjectNumber());
				projectResponseModel.setConfidential(result.isConfidential());
				projectResponseModel.setProjectType("ConfidentialProject");
				projectListModel.add(projectResponseModel);
			}

		}
		ListModel<RecordingProjectModel> listModel = new ListModel<RecordingProjectModel>();
		listModel.setItems(projectListModel);
		listModel.setTotalCount(totalRecords);
		listModel.setLimit(size);
		listModel.setTotalPageCount((int) Math.ceil((double) totalRecords / size));
		listModel.setCurPage(page);
		return listModel;
	}

	private Predicate buildPredicates(CriteriaBuilder cb, Root<RecordingProject> root,
			RecordingProjectSearchModel projects) {

		List<Predicate> predicates = new ArrayList<>();
//		predicates.add(cb.isTrue(root.get("isConfidential")));
		if (projects.getProjectNumber() != null) {
			predicates.add(cb.equal(root.get("recordingProjectNumber"), projects.getProjectNumber()));
		}
		if (projects.getProjectType() != null) {
			predicates.add(cb.equal(root.get("recordingProjectType").get("recordingProjectTypeKey"),
					projects.getProjectType().getKey()));
		}
		if (projects.getProjectTitle() != null) {
			predicates.add(cb.like(root.get("recordingProjectMatch"),
					converter.convertString(projects.getProjectTitle()) + DxSearchUtil.WILD_CARD));
		}
		if (projects.getMainArtist() != null) {
			predicates.add(cb.like(root.get("recordingProjectParticipants").get("participant").get("fullMatchName"),
					converter.convertString(projects.getMainArtist()) + DxSearchUtil.WILD_CARD));
			predicates.add(cb.isTrue(root.get("recordingProjectParticipants").get("isProductMainArtist")));

		}
		if (projects.getInitiator() != null) {
			predicates
					.add(cb.equal(root.get("initiatedByCompany").get("companyKey"), projects.getInitiator().getKey()));
		}
		if (projects.getInitiatedBy() != null) {
			predicates.add(cb.like(root.get("raasInitUser"), projects.getInitiatedBy() + DxSearchUtil.WILD_CARD));
		}
		if (projects.getMaintenanceOwner() != null) {
			predicates.add(
					cb.equal(root.get("maintanceOwner").get("companyKey"), projects.getMaintenanceOwner().getKey()));
		}
		if (projects.getRepertoireOwner() != null) {
			predicates.add(
					cb.equal(root.get("repertoireOwner").get("companyKey"), projects.getRepertoireOwner().getKey()));
		}
		if (projects.getImprintLabel() != null) {
			predicates.add(cb.equal(root.get("label").get("labelKey"), projects.getImprintLabel().getKey()));
		}
		if (projects.getProductProfitCenter() != null) {
			predicates.add(cb.equal(root.get("recordingProjectCountries").get("sapProfitCenterKey"),
					projects.getProductProfitCenter()));
		}
		DateTimeFormatter inputFormat = DateTimeFormatter.ofPattern("d MMM yyyy");
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
		if (projects.getInitiatedFromDate() != null && projects.getInitiatedToDate() != null) {
			LocalDate parsedFromDate = LocalDate.parse(projects.getInitiatedFromDate(), inputFormat);
			LocalDate parsedToDate = LocalDate.parse(projects.getInitiatedToDate(), inputFormat);
			String formattedFromDate = parsedFromDate.format(formatter);
			String formattedToDate = parsedToDate.format(formatter);
			predicates.add(cb.between(root.get("raasInitDate"), formattedFromDate, formattedToDate));
		} else if (projects.getInitiatedFromDate() != null) {
			LocalDate fromDate = LocalDate.parse(projects.getInitiatedFromDate(), formatter);
			String formattedFromDate = fromDate.format(formatter);
			predicates.add(cb.greaterThanOrEqualTo(root.get("raasInitDate"), formattedFromDate));

		} else if (projects.getInitiatedToDate() != null) {
			LocalDate toDate = LocalDate.parse(projects.getInitiatedToDate(), formatter);
			String formattedToDate = toDate.format(formatter);
			predicates.add(cb.lessThanOrEqualTo(root.get("raasInitDate"), formattedToDate));

		}

		return cb.and(predicates.toArray(new Predicate[0]));
	}
}