package com.sonymusic.dx.dxuiservice.service;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;

import com.sonymusic.dx.dxuiservice.dto.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatusCode;
import org.springframework.stereotype.Service;

import com.sonymusic.dx.dxuiservice.model.ProductModel;
import com.sonymusic.dx.dxuiservice.util.DxUIUtil;
import com.sonymusic.dx.persistence.gras.Company;
import com.sonymusic.dx.persistence.gras.Configuration;
import com.sonymusic.dx.persistence.gras.DataSource;
import com.sonymusic.dx.persistence.gras.Genre;
import com.sonymusic.dx.persistence.gras.Label;
import com.sonymusic.dx.persistence.gras.Language;
import com.sonymusic.dx.persistence.gras.LocalProductVersion;
import com.sonymusic.dx.persistence.gras.Participant;
import com.sonymusic.dx.persistence.gras.Product;
import com.sonymusic.dx.persistence.gras.ProductClass;
import com.sonymusic.dx.persistence.gras.ProductPurpose;
import com.sonymusic.dx.persistence.gras.ProductVersion;
import com.sonymusic.dx.persistence.gras.PublishStatus;
import com.sonymusic.dx.persistence.gras.RepertoireOwner;
import com.sonymusic.dx.persistence.gras.StrategicReportingGroup;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.JoinType;
import jakarta.persistence.criteria.Root;
import org.springframework.web.ErrorResponseException;

@Service
public class ProductService {

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

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

	public ProductModel getProductById(String productNo) throws ParseException {
//		ProductDTO productDTO = null;
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<ProductDTO> cq1 = cb.createQuery(ProductDTO.class);
		Root<Product> root = cq1.from(Product.class);
		Join<Product, ProductVersion> productVersionJoin = root.join("productVersion", JoinType.LEFT);
		Join<Product, Company> productCompanyJoin = root.join("maintenanceOwner", JoinType.LEFT);
		Join<Product, DataSource> productDataSourceJoin = root.join("dataSource", JoinType.LEFT);
		Join<Product, Configuration> productConfigurationJoin = root.join("configuration", JoinType.LEFT);
		Join<Product, PublishStatus> productPublishStatusJoin = root.join("publishKey", JoinType.LEFT);
		Join<ProductVersion, Genre> productVersionGenreJoin = productVersionJoin.join("genre", JoinType.LEFT);
		Join<ProductVersion, Label> productLabelJoin = productVersionJoin.join("label", JoinType.LEFT);
		Join<ProductVersion, RepertoireOwner> productVersionRepertoireOwnerJoin = productVersionJoin
				.join("repertoireOwner", JoinType.LEFT);
		Join<RepertoireOwner, Company> repertoireOwnerCompanyJoin = productVersionRepertoireOwnerJoin.join("company",
				JoinType.LEFT);
		Join<ProductVersion, Participant> productVersionParticipantJoin = productVersionJoin.join("mainArtist",
				JoinType.LEFT);
		Join<ProductVersion, ProductPurpose> productVersionProductPurposeJoin = productVersionJoin
				.join("productPurpose", JoinType.LEFT);
		Join<ProductVersion, ProductClass> productVersionProductClassJoin = productVersionJoin.join("productClass",
				JoinType.LEFT);
		Join<ProductVersion, Language> productVersionLanguageJoin = productVersionJoin.join("titleLanguage",
				JoinType.LEFT);
		Join<ProductVersion, LocalProductVersion> productVersionLocalProductVersionJoin = productVersionJoin
				.join("localProductVersions", JoinType.LEFT);
		Join<ProductVersion, StrategicReportingGroup> productVersionStrategicReportingGroupJoin = productVersionJoin
				.join("strategicReportingGroup", JoinType.LEFT);
		cq1.select(cb.construct(ProductDTO.class, root.get("productNumber"), root.get("barcode"),
				root.get("originalProductNumber"), root.get("digitalTitleSuppl"), root.get("digitalBarcode"),
				root.get("isComplete"), root.get("setCompleteDate"), root.get("setCompleteUser"), root.get("grid"),
				root.get("cLine"), root.get("firstRelaseDate"), root.get("catlgPfixId"), root.get("catlgCoreId"),
				root.get("affiliateId"),
				cb.construct(ProductVersionDTO.class, productVersionJoin.get("productVersionNumber"),
						productVersionJoin.get("productTitle"), productVersionJoin.get("productTitleSupplemental"),
						productVersionJoin.get("sonyRightsExpired")),
				cb.construct(ProductVersionGenreDTO.class, productVersionGenreJoin.get("genreKey"),
						productVersionGenreJoin.get("genreName")),
				cb.construct(DataSourceDTO.class, productDataSourceJoin.get("dataSourceId"),
						productDataSourceJoin.get("dataSourceName")),
				cb.construct(LabelDTO.class, productLabelJoin.get("labelKey"),productLabelJoin.get("labelName")),
				cb.construct(CompanyDTO.class, productCompanyJoin.get("companyKey"),productCompanyJoin.get("companyName")),
				cb.construct(ProductRepOwnerDTO.class, productVersionRepertoireOwnerJoin.get("repOwnerKey"),
						repertoireOwnerCompanyJoin.get("companyName")),
				cb.construct(ProductParticipantDTO.class, productVersionParticipantJoin.get("fullName")),
				cb.construct(ProductConfigurationDTO.class, productConfigurationJoin.get("configKey"),
						productConfigurationJoin.get("configName")),
				cb.construct(ProductPurposeDTO.class, productVersionProductPurposeJoin.get("productPurposeKey"),
						productVersionProductPurposeJoin.get("productPurposeName")),
				cb.construct(ProductClassDTO.class, productVersionProductClassJoin.get("productClassKey"),
						productVersionProductClassJoin.get("productClassName")),
				cb.construct(ProductPublishStatusDTO.class, productPublishStatusJoin.get("publishKey"),
						productPublishStatusJoin.get("publishName")),
				root.get("raasInitiatedDate"),
				cb.construct(LanguageDTO.class, productVersionLanguageJoin.get("languageKey"),
						productVersionLanguageJoin.get("languageName")),
				cb.construct(LocalProductStrategyDTO.class,
						productVersionStrategicReportingGroupJoin.get("strategicRepoprtingGroupName"))))
				.where(cb.equal(root.get("productNumber"), productNo));
		cq1.distinct(true);
		List<ProductDTO> productDTOs = em.createQuery(cq1).getResultList();
		if (productDTOs.isEmpty()) {
			throw new ErrorResponseException(HttpStatusCode.valueOf(400));
		}
		ProductDTO productDTO = productDTOs.get(0);
		ProductModel productResponseModel = null;
		productResponseModel = new ProductModel();
		productResponseModel.setProductNo(productDTO.getProductNumber());
		productResponseModel.setBarcode(productDTO.getBarcode());
		productResponseModel.setDigitalTitleSupplemental(productDTO.getDigitalTitleSuppl());
		productResponseModel.setDigBarcode(productDTO.getDigitalBarcode());
		productResponseModel.setComplete(productDTO.isComplete());
		productResponseModel.setCompleteUser(productDTO.getCompleteUser());
		productResponseModel.setGridNumber(productDTO.getGrid());
		productResponseModel.setcLine(productDTO.getcLine());
		productResponseModel.setProductTitle(productDTO.getProductVersion().getProductMatchTitle());
		productResponseModel
				.setProductTitleSupplemental(productDTO.getProductVersion().getProductMatchTitleSupplemental());
		productResponseModel.setSonyRightsExpired(productDTO.getProductVersion().getSonyRightsExpired());
		productResponseModel.setGenre(productDTO.getGenre().getGenreName());
		productResponseModel.setImprintLabel(productDTO.getLabel().getLabelName());
		productResponseModel.setDataSource(productDTO.getDataSource().getDataSourceName());
		productResponseModel.setMaintenanceOwner(productDTO.getCompanyName().getCompanyName());
		productResponseModel.setMainArtist(productDTO.getParticipantFullName().getFullName());
		productResponseModel.setRepertoireOwner(productDTO.getRepOwnerName().getCompanyName());
		if (productDTO.getCompleteDate() != null) {
			Timestamp dbValue = productDTO.getCompleteDate();

			LocalDateTime parsedDate = dbValue.toLocalDateTime();
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DxUIUtil.DATE_FORMAT_dd_MMM_yyyy);
			String formattedDate = parsedDate.format(formatter);
			productResponseModel.setCompleteDate(formattedDate);
		}
		productResponseModel.setProductConfiguration(productDTO.getConfiguration().getConfigName());
//            productResponseModel.setConfigurationCategory(productDTO.getConfiguration);
		productResponseModel.setCompleteUser(productDTO.getCompleteUser());
		productResponseModel.setProductPurpose(productDTO.getProductPurpose().getProductPurposeName());
		productResponseModel.setProductClass(productDTO.getProductClass().getProductClassName());
		productResponseModel.setInitiator(productDTO.getCompanyName().getCompanyName());
		productResponseModel.setProductTitleLanguage(productDTO.getProductVersion().getProductMatchTitle());
		productResponseModel
				.setStrategicRepGroup(productDTO.getLocalProductStrategy().getStrategicRepoprtingGroupName());
		productResponseModel.setPublishStatus(productDTO.getPublishStatus().getPublishName());
		productResponseModel.setCatalogPrefix(productDTO.getCatalogPrefix());
		productResponseModel.setCatalogCore(productDTO.getCatalogCore());
		productResponseModel.setAffiliate(productDTO.getAffiliate());
		if (productDTO.getFirstRelaseDate() != null) {
			String inputDate = productDTO.getFirstRelaseDate();
			SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
			Date parsedDate = format.parse(inputDate);
			SimpleDateFormat displayDate = new SimpleDateFormat(DxUIUtil.DATE_FORMAT_dd_MMM_yyyy);
			String formattedDate = displayDate.format(parsedDate);
			productResponseModel.setFirstReleasedDate(formattedDate);
		}
		if (productDTO.getRaasInitiatedDate() != null) {
			String inputDate = productDTO.getRaasInitiatedDate();
			SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
			Date parsedDate = format.parse(inputDate);
			SimpleDateFormat displayDate = new SimpleDateFormat(DxUIUtil.DATE_FORMAT_dd_MMM_yyyy);
			String formattedDate = displayDate.format(parsedDate);
			productResponseModel.setRaasInitiatedDate(formattedDate);
		}

		return productResponseModel;
	}
}