package org.jleaf.preorderappsbo.bo.customer;

import java.util.List;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.preorderappsbo.dao.SoDao;
import org.jleaf.preorderappscore.exception.DataNotFoundException;
import org.jleaf.util.DtoUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
@InfoIn(value={
		@Info(name="merchantCode",description="merchant code",type=String.class),
		@Info(name="orderDocNo",description="order document number",type=String.class)
})
@InfoOut(value={
		@Info(name="docNo",description="document number",type=String.class),
		@Info(name="docDate",description="document date",type=String.class),
		@Info(name="statusDoc",description="status document",type=String.class),
		@Info(name="soAmount",description="sales order amount",type=Double.class),
		@Info(name="merchantCode",description="merchant code",type=String.class),
		@Info(name="itemList",description="List of ItemList(productId, productImagesId, productCode, productName, qtySo, unit)",type=List.class)
})
@SuppressWarnings({"unchecked","rawtypes"})
public class FindCustomerDetailTransactionByIndex extends AbstractBusinessFunction implements BusinessFunction{
	Logger log = LoggerFactory.getLogger(getClass());
	
	@Autowired
	SoDao soDao;
	
	@Override
	public String getDescription() {
		return "Get Customer Detail Transaction";
	}
	
	@Override
	public Dto execute(Dto dto) throws Exception {

		Dto output = getDetailTrx(dto);
		output.put("itemList", getDetailTrxItem(dto));
		
		return output;
	}
	
	private Dto getDetailTrx(Dto dto){	
		QueryBuilder builder = new QueryBuilder();
		builder.add(" SELECT A.doc_no, A.doc_date, A.status_doc, ");
		builder.add(" SUM(B.nett_item_amount) + COALESCE(G.add_amount,0) as add_amount, C.tenant_code, C.tenant_name, ");
		builder.add(" E.address1, F.cp_name, F.phone1, ");
		builder.add(" A.remark ");
		builder.add(" FROM sl_so A ");
		builder.add(" JOIN sl_quotation A1 ON A.ext_doc_no = A1.doc_no AND A.ext_doc_date = A1.doc_date ");
		builder.add(" JOIN sl_so_item B ON A.so_id = B.so_id ");
		builder.add(" JOIN t_tenant C ON A.tenant_id = C.tenant_id ");
		builder.add(" JOIN m_partner D ON A1.partner_id = D.partner_id ");
		builder.add(" JOIN m_partner_address E ON A1.partner_address_id = E.partner_address_id ");
		builder.add(" JOIN m_partner_cp F ON A1.partner_cp_id = F.partner_cp_id ");
		builder.add(" LEFT JOIN sl_so_cost G ON A.so_id = G.so_id ");
		builder.add(" WHERE A.doc_no = :orderDocNo ");
		builder.add(" AND C.tenant_code = :merchantCode ");
		builder.add(" GROUP BY A.doc_no, A.doc_date, A.status_doc, C.tenant_code, C.tenant_name, E.address1, F.cp_name, F.phone1, A.remark,G.add_amount");
		
		Query query = soDao.createNativeQuery(builder.toString());
		query.setParameter("merchantCode", dto.getString("merchantCode"));
		query.setParameter("orderDocNo", dto.getString("orderDocNo"));
		
		List<Object[]> resultList = query.getResultList();
		if(resultList.size() > 0){
			List<Dto> finalResultList = DtoUtil.createDtoListFromArray(resultList, "docNo", "docDate", "statusDoc",
																					"soAmount", "merchantCode", "merchantName",
																					"merchantAddress", "cpName", "merchantPhoneNumber",
																					"remark");
			
			return new Dto(finalResultList.get(0));
		}
		throw new DataNotFoundException("Customer Detail Transaction not found");
	}

	private List getDetailTrxItem(Dto dto){
		QueryBuilder builder = new QueryBuilder();
		builder.add(" WITH data_item AS ( ");
		builder.add(" 	SELECT C.product_id, COALESCE(D.product_images_id,-99) AS product_images_id, E.product_code, ");
		builder.add(" 	E.product_name, C.qty_so, H.uom_name,  ");
		builder.add(" 	ROW_NUMBER() OVER( ");
		builder.add(" 		PARTITION BY E.product_id ");
		builder.add(" 		ORDER BY D.image_sequence ASC ");
		builder.add(" 	) ");
		builder.add(" 	FROM sl_so A ");
		builder.add(" 	JOIN t_tenant B ON A.tenant_id = B.tenant_id ");
		builder.add(" 	JOIN sl_so_item C ON A.so_id = C.so_id ");
		builder.add(" 	LEFT JOIN m_product_images D ON C.product_id = D.product_id ");
		builder.add(" 	JOIN m_product E ON C.product_id = E.product_id ");
		builder.add(" 	JOIN m_uom H ON C.so_uom_id = H.uom_id ");
		builder.add(" 	LEFT JOIN sl_so_cost I ON A.so_id = I.so_id ");
		builder.add(" 	WHERE A.doc_no = :orderDocNo ");
		builder.add(" 	AND B.tenant_code = :merchantCode ");
		builder.add(" ) ");
		builder.add(" SELECT product_id, product_images_id, product_code, ");
		builder.add(" product_name, qty_so, uom_name ");
		builder.add(" FROM data_item  ");
		builder.add(" WHERE row_number = 1 ");
		
		Query query = soDao.createNativeQuery(builder.toString());
		query.setParameter("merchantCode", dto.getString("merchantCode"));
		query.setParameter("orderDocNo", dto.getString("orderDocNo"));
		
		List<Object[]> resultList = query.getResultList();
		List<Dto> finalResultList = DtoUtil.createDtoListFromArray(resultList, "productId", "productImagesId", "productCode", 
																				"productName", "qtySo", "unit");
		
		return finalResultList;
	}
	
}