/*******************************************************************************
 * Copyright 2Fi Business Solutions Ltd.
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.tbms.order.service;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.BooleanUtils;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.NumberUtils;
import com.ffii.core.utils.Params;
import com.ffii.core.utils.SecurityUtils;
import com.ffii.core.web.AbstractService;
import com.ffii.tbms.log.LogUtils;
import com.ffii.tbms.log.service.AuditLogService;
import com.ffii.tbms.order.Order;
import com.ffii.tbms.order.dao.OrderDao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author Patrick
 */
@Service
public class OrderService extends AbstractService {

	@Autowired
	private JdbcDao jdbcDao;

	@Autowired
	private OrderDao orderDao;

	@Autowired
	private AuditLogService auditLogService;

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdate(final Order instance) {
		return orderDao.saveOrUpdate(instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Order find(final Integer id) {
		return orderDao.find(id);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchForCombo(Map<String, Object> args) {

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		
		StringBuilder sql = new StringBuilder(
			" SELECT o.type, "
			+" o.code, "
			+" o.status, "
			+" o.id, "
			+" o.inDate, "
			+" o.inRemark, "
			+" o.outRemark, "
			+" o.outDate, "
			+" o.custId, "
			+" CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
				+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
				+" IF(c.nameCh IS NULL, '', c.nameCh)) AS customerName "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" WHERE o.deleted = 0 ");

		if(args != null){
			if(args.containsKey("query")){
				sql.append(" AND (concat(o.type, o.code) LIKE :query "
				+" OR "
				+" ( ( concat_ws(' ', c.firstName ,c.surname, c.nameCh) LIKE :query "
				+" OR concat_ws(' ', c.surname, c.firstName, c.nameCh) LIKE :query )) "
				+" )");
			}
			if(args.containsKey("sysGroupId")) sql.append(" AND o.sysGroupId = :sysGroupId ");
			if(args.containsKey("id"))sql.append(" AND o.id = :id ");
			if(args.containsKey("custId"))sql.append(" AND o.custId = :custId ");
			if(args.containsKey("status"))sql.append(" AND o.status IN (:status) ");
		}

		sql.append(" ORDER BY concat(o.type, o.code) ASC ");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	private String getSearchCriteriaString(final Map<String, Object> args) {
		final StringBuilder sql = new StringBuilder("");

		if (args != null) {
			if (args.containsKey(Params.ID))
				sql.append(" AND o.id = :id");
			if (args.containsKey("orderType"))
				sql.append(" AND o.type = :orderType ");
			if (args.containsKey("orderCode"))
				sql.append(" AND o.code LIKE :orderCode ");
			if (args.containsKey("orderNoStr")){
				sql.append(args.get("orderNoStr"));
			}

		/*
			if(args.containsKey("orderNo")){
				//sql.append(" AND ( concat(o.type, o.code) LIKE :orderNo OR o.refNo LIKE :orderNo ) ");
				String str = args.get("orderNo").toString().trim();
				String[] array = str.split(",| ");
				str = "";
				for(String orderNo: array){
					if(!str.equals("")){
						str = str + " OR ";
					}
					str = str+" concat(o.type, o.code) LIKE '%"+orderNo.trim()+"%' ";
				}
				sql.append(" AND ( "+str+" ) ");
			}
		*/
			
			if (args.containsKey("targetCompleteDateFrom"))
				sql.append(" AND o.targetCompleteDate >= :targetCompleteDateFrom ");
			
			if (args.containsKey("targetCompleteDateTo"))
					sql.append(" AND o.targetCompleteDate < :targetCompleteDateTo ");
		
			if (args.containsKey("workshopTargetDateFrom"))
				sql.append(" AND DATE_ADD(o.targetCompleteDate, INTERVAL -2 day) >= :workshopTargetDateFrom ");
			
			if (args.containsKey("workshopTargetDateTo"))
				sql.append(" AND DATE_ADD(o.targetCompleteDate, INTERVAL -2 day) < :workshopTargetDateTo ");
			
				
			if (args.containsKey("fromOutDate"))
				sql.append(" AND o.outDate >= :fromOutDate ");
			if (args.containsKey("toOutDate"))
				sql.append(" AND o.outDate < :toOutDate ");
			if (args.containsKey("fromDate"))
				sql.append(" AND o.date >= :fromDate ");
			if (args.containsKey("toDate"))
				sql.append(" AND o.date < :toDate ");
			/*
			if (args.containsKey("showIgnored")){
				if(BooleanUtils.isTrue(args.get("showIgnored"))== true)
					sql.append(" ");
				else
					//sql.append(" AND o.ignoreC <> 1 ");
			}
			*/
			if (args.containsKey("custId"))
				sql.append(" AND o.custId = :custId ");

			if (args.containsKey("firstName"))
				sql.append(" AND c.firstName LIKE :firstName ");
			if (args.containsKey("nameCh"))
				sql.append(" AND c.nameCh LIKE :nameCh ");
			if (args.containsKey("surname"))
				sql.append(" AND c.surname LIKE :surname ");
			if (args.containsKey("emailPhone"))
				sql.append(" AND (c.email LIKE :emailPhone OR c.phone1 LIKE :emailPhone OR c.phone2 LIKE :emailPhone ) ");
			
			if (args.containsKey("status"))
				sql.append(" AND o.status = :status ");
			if (args.containsKey("workshopStatus"))
				sql.append(" AND o.workshopStatus = :workshopStatus ");
			if (args.containsKey("itemName"))
				sql.append(" AND _oi.itemName LIKE :itemName ");
			if (args.containsKey("balance") && BooleanUtils.isTrue(args.get("balance")))
				sql.append(" AND IF(_p.paymentAmount IS NULL, 0, _p.paymentAmount) < _oi.orderAmount ");
		}

		return sql.toString();
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> find(final Map<String, Object> args) {

		final StringBuilder sql = new StringBuilder(
			" SELECT "
			+" o.code, "
			+" o.id, "
			+" o.type, "
			+" CONCAT(o.type,o.code) AS orderNo, "
			+" CONCAT(IF(c.firstName IS NULL, '', CONCAT(c.firstName, ' ')), "
				+" IF(c.surname IS NULL, '', CONCAT(c.surname, ' ')), "
				+" IF(c.nameCh IS NULL, '', c.nameCh)) AS customerName "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" WHERE o.id = :orderId ");

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> search(final Map<String, Object> args) {

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());

		final StringBuilder sql = new StringBuilder(
			" SELECT "
			+" o.id, "
			+" o.code, "
			+" o.type, "
			+" o.ref, "
			+" o.refNo, "
			+" o.status, "
			+" o.date, "
			+" o.custId, "
			+" o.fileId, "
			+" o.remarks, "
			+" o.outDate, "
			+" o.inDate, "
			+" o.outRemark, "
			+" o.inRemark, "
			+" o.workshopStatus, "
			//+" o.ignoreC, "
			+" o.targetCompleteDate, "
			+" o.targetCompleteDate, "
			+" o.inStatus, "
			+" o.outStatus, "
			+" c.email, "
			+" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
			+" concat_ws(' ', c.firstName ,c.surname, c.nameCh) AS customerName, "
			+" _p.paymentAmount, "
			+" IF(_p.paymentAmount IS NULL, false, true) AS havePayment, "
			+" _oi.orderAmount "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount, "
				+" GROUP_CONCAT(oi.name SEPARATOR ' ') AS itemName  "
				+" FROM order_item oi WHERE oi.deleted = 0 "
				+" GROUP BY oi.orderId) _oi ON _oi.orderId = o.id "
			+" LEFT JOIN ( "
				+" SELECT "
				+" SUM(p.amount) AS paymentAmount, "
				+" p.orderId "
				+" FROM payment p "
				+" WHERE p.deleted = 0 "
				+" GROUP BY p.orderId) _p ON _p.orderId = o.id "
			+" WHERE o.deleted = 0 ");

		sql.append(getSearchCriteriaString(args));

		if(args.containsKey("sysGroupId")){
			sql.append(" AND o.sysGroupId = :sysGroupId ");
		}

		if(args.containsKey("orderNoStr")){
			sql.append(" ORDER BY o.type ASC, o.code ASC,  o.id ASC ");
		}else{
			sql.append(" ORDER BY o.date DESC, o.type ASC, o.id DESC ");
		}
		
		if (args != null) {
			if (args.containsKey("start") && args.containsKey("limit"))
				sql.append(" LIMIT :start, :limit ");
		}

		logger.info("orderSerach : "+sql.toString());

		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> countSearch(final Map<String, Object> args) {

		

		StringBuilder sql = new StringBuilder(
			" SELECT "
			+" COUNT(o.id) AS count "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount, "
				+" GROUP_CONCAT(oi.name SEPARATOR ' ') AS itemName  "
				+" FROM order_item oi WHERE oi.deleted = 0 "
				+" GROUP BY oi.orderId) _oi ON _oi.orderId = o.id "
			+" LEFT JOIN ( "
				+" SELECT "
				+" SUM(p.amount) AS paymentAmount, "
				+" p.orderId "
				+" FROM payment p "
				+" WHERE p.deleted = 0 "
				+" GROUP BY p.orderId) _p ON _p.orderId = o.id "
			+" WHERE o.deleted = 0 ");

		sql.append(getSearchCriteriaString(args));

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		if(args.containsKey("sysGroupId")){
			sql.append(" AND o.sysGroupId = :sysGroupId ");
		}

		logger.info("sql: "+sql.toString());

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> sumTotalAmount(final Map<String, Object> args) {

		StringBuilder sql = new StringBuilder(
			" SELECT "
			+" SUM(_oi.orderAmount) AS totalAmount "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" LEFT JOIN (SELECT oi.orderId, SUM(oi.price) AS orderAmount, "
				+" GROUP_CONCAT(oi.name SEPARATOR ' ') AS itemName  "
				+" FROM order_item oi WHERE oi.deleted = 0 "
				+" GROUP BY oi.orderId) _oi ON _oi.orderId = o.id "
			+" LEFT JOIN ( "
				+" SELECT "
				+" SUM(p.amount) AS paymentAmount, "
				+" p.orderId "
				+" FROM payment p "
				+" WHERE p.deleted = 0 "
				+" GROUP BY p.orderId) _p ON _p.orderId = o.id "
			+" WHERE o.deleted = 0 ");

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		if(args.containsKey("sysGroupId")){
			sql.append(" AND o.sysGroupId = :sysGroupId ");
		}

		sql.append(getSearchCriteriaString(args));
		

		logger.info("sql: "+sql.toString());

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchForWorkShop(final Map<String, Object> args) {

		final StringBuilder sql = new StringBuilder(
			" SELECT "
			+" o.id, "
			+" o.code, "
			+" o.type, "
			+" o.ref, "
			+" o.refNo, "
			+" o.status, "
			+" o.date, "
			+" o.custId, "
			+" o.fileId, "
			+" o.remarks, "
			+" o.outDate, "
			+" o.inDate, "
			+" o.outRemark, "
			+" o.inRemark, "
			//+" o.ignoreC, "
			+" o.workshopStatus, "
			+" o.inStatus, "
			+" o.outStatus, "
			+" DATE_ADD(o.targetCompleteDate, INTERVAL -2 day) AS targetCompleteDate, "
			+" c.email, "
			+" concat_ws(' / ', c.phone1, c.phone2) AS phone, "
			+" concat_ws(' ', c.firstName ,c.surname, c.nameCh) AS customerName "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" WHERE o.deleted = 0 AND o.workshopStatus IS NOT NULL ");

		sql.append(getSearchCriteriaString(args));

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		if(args.containsKey("sysGroupId")){
			sql.append(" AND o.sysGroupId = :sysGroupId ");
		}

		if(args.containsKey("stockInStatus"))
			sql.append(" AND o.outStatus = :stockInStatus ");

		sql.append(" ORDER BY o.outDate DESC, o.id DESC ");

		if (args != null) {
			if (args.containsKey("start") && args.containsKey("limit"))
				sql.append(" LIMIT :start, :limit ");
		}

		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> countSearchForWorkshop(final Map<String, Object> args) {

		StringBuilder sql = new StringBuilder(
			" SELECT "
			+" COUNT(o.id) AS count "
			+" FROM orders o "
			+" LEFT JOIN customer c ON c.id = o.custId "
			+" WHERE o.deleted = 0 AND o.workshopStatus IS NOT NULL ");

		sql.append(getSearchCriteriaString(args));

		args.put("sysGroupId", SecurityUtils.getUser().getSysGroupId());
		if(args.containsKey("sysGroupId")){
			sql.append(" AND o.sysGroupId = :sysGroupId ");
		}

		if(args.containsKey("stockInStatus"))
			sql.append(" AND o.outStatus = :stockInStatus ");

		logger.info("sql: "+sql.toString());

		return jdbcDao.queryForMap(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> queryForMap(final Integer id) {
		final String sql = "SELECT o.*, concat(o.type, o.code) As orderNo FROM orders o WHERE o.id = :id";

		return jdbcDao.queryForMap(sql, MapUtils.toHashMap(Params.ID, id));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void insertLog (String tableName, int recordId, Integer modifiedBy, Date modified, String oldData, String newData) {
		
		final String sql = "INSERT INTO audit_log (`tableName`, `recordId`, `modifiedBy`, `modified`, `oldData`, `newData`) "
		+ "VALUES (:tableName, :recordId, :modifiedBy, :modified, :oldData, :newData)";

		jdbcDao.queryForMap(sql, MapUtils.toHashMap("tableName", tableName,
													"recordId", recordId,
													"modifiedBy", modifiedBy,
													"modified", modified,
													"oldData", oldData,
													"newData", newData));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void updateChildOrder(final Order parentOrder) throws JsonProcessingException {
		int id = parentOrder.getId();
		String orderNo = parentOrder.getType()+parentOrder.getCode();
		int status = parentOrder.getStatus();

		List<Map<String,Object>> childList = findChild(id);
		for(Map<String, Object> child: childList){
			Map<String, Object> diff = new HashMap();
			if(!child.get("status").equals(status)) diff.put("status", status);
			if(!child.get("refNo").equals(orderNo)) diff.put("refNo", orderNo);
			if(!child.get("ref").equals(id)) diff.put("ref", id);
			if(diff.size()>0){
				log(LogUtils.ACTION_UPDATE, NumberUtils.toInt(child.get("id"),-1), diff);
			}
		}

		final String sql = "UPDATE orders o SET "
		+" o.refNo = :orderNo, "
		+" o.targetCompleteDate = IF( '"+Order.STATUS_COMPLETED+"' = :status , NULL, o.targetCompleteDate), "
		+" o.status = :status "
		+" WHERE o.ref = :orderId ";
		jdbcDao.executeUpdate(sql,
			MapUtils.toHashMap(
				"orderId", id,
				"orderNo", orderNo,
				"status", status));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String,Object>> findChild(int parentOrderId) {
		final String sql = " SELECT o.* FROM orders o WHERE o.ref = :orderId ";
		return jdbcDao.queryForList(sql,MapUtils.toHashMap("orderId", parentOrderId));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public List<Map<String, Object>> apiSearch(final Map<String, Object> args){
		//@formatter:off 
		final StringBuilder sql = new StringBuilder();
		//@formatter:on
		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void deletedOrder(Integer id) throws JsonProcessingException{
		Order instance = find(id);
		instance.setDeleted(true);
		saveOrUpdate(instance);
		log(LogUtils.ACTION_DELETE, id, instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void log(String action, int id, Order newRecord, Order oldRecord)throws JsonProcessingException {
		Map<String, Object> data = new HashMap<>();
		Map<String, Object> compare = LogUtils.compare(newRecord, oldRecord);
		data = (Map<String, Object>)compare.get(LogUtils.DIFF);
		log(action, id, data);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void log(String action, int id, Map<String, Object> data)throws JsonProcessingException {
		if(data.size()<=0) return;
		ObjectMapper mapper = new ObjectMapper();
		auditLogService.save("orders",
							id,
							SecurityUtils.getUser().getId(),
							new Date(),
							action,
							mapper.writeValueAsString(data));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void log(int userId, String action, int id, Map<String, Object> data)throws JsonProcessingException {
		if(data.size()<=0) return;
		ObjectMapper mapper = new ObjectMapper();
		auditLogService.save("orders",
							id,
							userId,
							new Date(),
							action,
							mapper.writeValueAsString(data));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public void log(String action, int id, Order newRecord)throws JsonProcessingException {
		Map<String, Object> data = LogUtils.filter(LogUtils.objectToMap(newRecord));
		log(action, id, data);
	}

	
	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchOrderByTypeNCode(Map<String, Object> args) {

		final StringBuilder sql = new StringBuilder(
			" SELECT * FROM orders o WHERE o.deleted = 0 AND o.status NOT IN("+Order.STATUS_IGNORE +", "+Order.STATUS_CANCELLED+") AND o.type = :type AND o.code = :code AND o.id != :id");

		return jdbcDao.queryForList(sql.toString(), args);
	}

}
