package com.ishop.merchant.service;

import com.ishop.merchant.Constants;
import com.ishop.merchant.ProductConstants;
import com.ishop.merchant.pojo.ProductParam;
import com.ishop.model.po.EbMerchantInfo;
import com.ishop.model.po.EbProduct;
import com.ishop.model.po.EbProductAttr;
import com.ishop.model.po.EbProductAttrValue;
import com.ishop.model.po.EbProductCoupon;
import com.ishop.model.po.EbProductDescription;
import com.ishop.model.vo.ProductTabsHeaderVo;
import com.walker.db.page.GenericPager;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.infrastructure.utils.UrlUtils;
import com.walker.jdbc.service.BaseServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class ProductServiceImpl extends BaseServiceImpl {

    private ProductDescriptionServiceImpl productDescriptionService;
    private ProductRelationServiceImpl productRelationService;
    private ProductAttrServiceImpl productAttrService;
    private ProductAttrValueServiceImpl productAttrValueService;

    @Autowired
    public ProductServiceImpl(ProductDescriptionServiceImpl productDescriptionService
            , ProductRelationServiceImpl productRelationService
            , ProductAttrServiceImpl productAttrService, ProductAttrValueServiceImpl productAttrValueService){
        this.productDescriptionService = productDescriptionService;
        this.productRelationService = productRelationService;
        this.productAttrService = productAttrService;
        this.productAttrValueService = productAttrValueService;
    }

    /**
     * 判断给定'平台分类'下面是否存在关联商品
     * @param platformCategoryId
     * @return 存在返回true，不存在返回false
     * @date 2023-07-31
     */
    public boolean queryExistCategoryProductRef(int platformCategoryId){
        int total = this.sqlMathQuery("select count(id) total from eb_product where category_id=?", new Object[]{platformCategoryId}, Integer.class);
        return total > 0;
    }

    /**
     * 分页查询商户商品列表，移动端使用
     * @param merId 商户ID
     * @param keyword 关键词
     * @param cid 分类
     * @param maxPrice 最大价格
     * @param minPrice 最小价格
     * @param salesOrder 销量排序
     * @param priceOrder 价格排序
     * @return
     * @date 2023-07-05
     */
    public GenericPager<EbProduct> queryPageMerchantProductH5List(int merId
            , String keyword, Integer cid, Double maxPrice, Double minPrice, String salesOrder, String priceOrder){
        Map<String, Object> param = new HashMap<>(4);
        StringBuilder sql = new StringBuilder(SQL_PRODUCT_H5_LIST);
        sql.append(" where mer_id=:merId and is_del=0 and is_recycle=0 and is_show=1 and (audit_status=2 or audit_status=0)");
        param.put("merId", merId);
        if(StringUtils.isNotEmpty(keyword)){
            sql.append(" and (name like :keyword or keyword like :keyword)");
            param.put("keyword", StringUtils.CHAR_PERCENT + keyword + StringUtils.CHAR_PERCENT);
        }
        if(cid != null && cid.intValue() > 0){

        }
        if(maxPrice != null){
            sql.append(" and price<=:maxPrice");
            param.put("maxPrice", maxPrice);
        }
        if(minPrice != null){
            sql.append(" and price>=:minPrice");
            param.put("minPrice", minPrice);
        }
        if(StringUtils.isNotEmpty(salesOrder)){
            if(salesOrder.equals(Constants.SORT_ASC)){
                sql.append(" order by sales asc, sort desc");
            } else {
                sql.append(" order by sales desc, sort desc");
            }
        } else if(StringUtils.isNotEmpty(priceOrder)){
            if(priceOrder.equals(Constants.SORT_ASC)){
                sql.append(" order by price asc, sort desc");
            } else {
                sql.append(" order by price desc, sort desc");
            }
        }
        return this.selectSplit(sql.toString(), param, new EbProduct());
    }
    private static final String SQL_PRODUCT_H5_LIST = "select id, mer_id,image,name,price,ot_price,stock,sales,intro,ficti,browse,unit_name,brand_id,category_id from eb_product";

    /**
     * 商品下架
     * @param productId
     * @date 2023-08-02
     */
    public void execProductDown(long productId){
        // 1:购物车中商品状态更新：不可用
        this.execute("update eb_cart set status=0 where product_id=? and status=1", new Object[]{productId});
        // 2:用户收藏表，删除商品
        this.productRelationService.execDeleteProduct(productId);
        // 3:更新商品状态
        this.execute("update eb_product set is_show=0 where id=?", new Object[]{productId});
    }

    /**
     * 商品上架
     * @param productId 商品id
     * @param skuIdList 购物车中更新（sku）状态为可用
     * @date 2023-08-02
     */
    public void execProductUp(long productId, List<Integer> skuIdList){
        if(skuIdList != null){
            Map<String, Object> parameter = new HashMap<>(2);
            parameter.put("status", 1);
            parameter.put("skuIds", skuIdList);
            this.execute(SQL_CART_STATUS, parameter);
        }
        this.execute("update eb_product set is_show=1 where id=?", new Object[]{productId});
    }
    private static final String SQL_CART_STATUS = "update eb_cart set status=:status where product_attr_unique in (:skuIds)";

    /**
     * 操作库存
     * @param id 商品ID
     * @param num 数量
     * @param type 类型：add—添加，sub—扣减
     * @return
     */
    public int execOperateStock(long id, Integer num, String type){
        Map<String, Object> param = new HashMap<>(4);
        StringBuilder sql = new StringBuilder("update eb_product");
        if(type.equals(Constants.OPERATION_TYPE_QUICK_ADD)){
            sql.append(" set stock=stock+:num");
            param.put("num", num);
        } else if(type.equals(Constants.OPERATION_TYPE_ADD)){
            sql.append(" set stock=stock+:num");
            sql.append(",sales=sales-:num");
            param.put("num", num);
        } else if(type.equals(Constants.OPERATION_TYPE_SUBTRACT)){
            sql.append(" set stock=stock-:num");
            sql.append(",sales=sales+:num");
            param.put("num", num);
        } else {
            throw new UnsupportedOperationException("不支持的库存操作类型：" + type);
        }
        sql.append(" where id=:id");
        param.put("id", id);
        return this.execute(sql.toString(), param);
    }

    /**
     * 更新商品信息。
     * @param product 商品
     * @param addAttrList 添加的规格
     * @param updateAttrList 更新的规格
     * @param addAttrValueList 添加的自定义属性值
     * @param updateAttrValueList 更新的自定义属性值
     * @param spd 描述信息
     * @param couponList 优惠券集合
     * @date 2023-08-02
     */
    public void execUpdateProduct(EbProduct product
            , List<EbProductAttr> addAttrList, List<EbProductAttr> updateAttrList
            , List<EbProductAttrValue> addAttrValueList, List<EbProductAttrValue> updateAttrValueList
            , EbProductDescription spd, List<EbProductCoupon> couponList){

        // attr
        this.productAttrService.execDeleteByProductIdAndType(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
        if(!StringUtils.isEmptyList(addAttrList)){
            int attrId = this.queryAttrNextId();
            for(EbProductAttr attr: addAttrList){
                attr.setId(attrId++);
            }
            this.insertBatch(addAttrList);
        }
        if(!StringUtils.isEmptyList(updateAttrList)){
            this.updateBatch(updateAttrList);
        }

        // attr value
        this.productAttrValueService.execDeleteByProductIdAndType(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
        if(!StringUtils.isEmptyList(addAttrValueList)){
            int attrValueId = this.queryAttrValueNextId();
            for(EbProductAttrValue attrValue: addAttrValueList){
                attrValue.setId(attrValueId++);
            }
            this.insertBatch(addAttrValueList);
        }
        if(!StringUtils.isEmptyList(updateAttrValueList)){
            this.updateBatch(updateAttrValueList);
        }

        if(spd != null){
            this.deleteProductDescription(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
            this.insert(spd);
        }

        // 先删除商品关联优惠券，如果存在添加的在加入。
        this.execDeleteProductCouponList(product.getId());
        if(!StringUtils.isEmptyList(couponList)){
            this.insertBatch(couponList);
        }
        this.save(product);
    }

    /**
     * 根据商品ID，删除关联的商品优惠券集合
     * @param productId
     * @date 2023-08-02
     */
    public void execDeleteProductCouponList(long productId){
        this.execute("delete from eb_product_coupon where product_id=?", new Object[]{productId});
    }

    /**
     * 商户新增一个商品信息。
     * @param product
     * @param productAttrList
     * @param productAttrValueList
     * @param spd
     * @param couponList
     */
    public void execInsertProduct(EbProduct product, List<EbProductAttr> productAttrList
            , List<EbProductAttrValue> productAttrValueList, EbProductDescription spd, List<EbProductCoupon> couponList){
        if(!StringUtils.isEmptyList(productAttrList)){
            int attrId = this.queryAttrNextId();
            for(EbProductAttr attr: productAttrList){
                attr.setId(attrId++);
            }
            this.insertBatch(productAttrList);
        }
        if(!StringUtils.isEmptyList(productAttrValueList)){
            int attrValueId = this.queryAttrValueNextId();
            for(EbProductAttrValue attrValue: productAttrValueList){
                attrValue.setId(attrValueId++);
            }
            this.insertBatch(productAttrValueList);
        }
        if(spd != null){
            this.deleteProductDescription(product.getId(), ProductConstants.PRODUCT_TYPE_NORMAL);
            this.insert(spd);
        }
        if(!StringUtils.isEmptyList(couponList)){
            this.insertBatch(couponList);
        }
        this.insert(product);
    }

    private void deleteProductDescription(long productId, int type){
        this.execute("delete from eb_product_description where product_id=? and type=?", new Object[]{productId, type});
    }

    /**
     * 查询商家推荐的商品。
     * @param merId
     * @param limit 限制条数
     * @return
     * @date 2023-07-04
     */
    public List queryRecommendedProductsByMerId(int merId, int limit){
        return this.select(SQL_PRODUCT_RECOMMEND, new Object[]{merId,limit}, new EbProduct());
    }
    private static final String SQL_PRODUCT_RECOMMEND = "select id,mer_id,image,name,price,sales,ficti from eb_product where mer_id=? and is_del=0 and is_recycle=0 and is_show=1 and (audit_status=2 or audit_status=0) limit ?";

    /**
     * 返回H5移动端商品详情。
     * @param id 商品ID
     * @return
     * @date 2023-07-04
     */
    public EbProduct queryH5Detail(long id){
        EbProduct product = this.get(SQL_PRODUCT_DETAIL_H5, new Object[]{id}, new EbProduct());
        EbProductDescription description = this.productDescriptionService.queryProductDescription(ProductConstants.PRODUCT_TYPE_NORMAL, id);
        if(description != null){
            product.setParameterString("content", StringUtils.isNotEmpty(description.getDescription())? description.getDescription():StringUtils.EMPTY_STRING);
        }
        return product;
    }

    private static final String SQL_PRODUCT_DETAIL_H5 = "select id, mer_id,image,name,slider_image,ot_price,stock,sales,price,intro,ficti,browse,unit_name,guarantee_ids,brand_id,category_id from eb_product where id=?";

    /**
     * 商户端，商品管理列表。
     * @param productParam
     * @return
     * @date 2023-06-15
     */
    public GenericPager<EbProduct> queryPageMerchantProductList(ProductParam productParam){
        Map<String, Object> parameters = new HashMap<>(4);
        StringBuilder sql = new StringBuilder("select * from eb_product where mer_id=:merId");
        parameters.put("merId", productParam.getMerId());
        this.searchConditionByType(sql, productParam.getType(), productParam.getMerId());
        // 2023-07-31 平台商品分类
        if(productParam.getCategoryId() != null && productParam.getCategoryId() > 0){
            sql.append(" and category_id=:categoryId");
            parameters.put("categoryId", productParam.getCategoryId());
        }
        if(StringUtils.isNotEmpty(productParam.getKeywords())){
            sql.append(" and (keyword like :keywords or name like :keywords)");
            parameters.put("keywords", StringUtils.CHAR_PERCENT + UrlUtils.decode(productParam.getKeywords()) + StringUtils.CHAR_PERCENT);
        }
        if(StringUtils.isNotEmpty(productParam.getCateId())){
            sql.append(" and cate_id like :cateId");
            parameters.put("cateId", StringUtils.CHAR_PERCENT + productParam.getCateId() + StringUtils.CHAR_PERCENT);
        }

        sql.append(" order by create_time desc");
        return this.selectSplit(sql.toString(), parameters, new EbProduct());
    }

    private void searchConditionByType(StringBuilder sql, Integer type, Integer merId){
        if(type.intValue() == ProductConstants.PRODUCT_STATUS_UP){
            sql.append(" and is_show=1 and is_recycle=0 and is_del=0");
            sql.append(" and audit_status in (2,0)");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_DOWN){
            //仓库中（未上架）
            sql.append(" and is_show=0 and is_recycle=0 and is_del=0 and is_audit=0");
            sql.append(" and audit_status in (1,2,0)");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_OVER){
            //已售罄
            sql.append(" and stock<=0 and is_recycle=0 and is_del=0");
            sql.append(" and audit_status in (2,0)");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_WARN){
            //警戒库存
            EbMerchantInfo merchantInfo = new EbMerchantInfo();
            merchantInfo.setMerId(merId);
            merchantInfo = this.get(merchantInfo);
            Integer alertStock = 0;
            if(merchantInfo != null){
                alertStock = merchantInfo.getAlertStock();
            }
            sql.append(" and stock=").append(alertStock==null? 0 : alertStock);
            sql.append(" and is_recycle=0 and is_del=0");
            sql.append(" and audit_status in (2,0)");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_CYCLE){
            //回收站
            sql.append(" and is_recycle=1 and is_del=0");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_WAIT_AUDIT){
            //待审核
            sql.append(" and audit_status=1 and is_audit=1 and is_recycle=0 and is_del=0");
        } else if(type.intValue() == ProductConstants.PRODUCT_STATUS_AUDIT_FAILED){
            //审核失败
            sql.append(" and audit_status=3 and is_audit=0 and is_recycle=0 and is_del=0");
        }
    }

    /**
     * 平台商品分类统计，暂时先不实现。
     * @return
     * @date 2023-06-11
     */
    public List<ProductTabsHeaderVo> queryTabsHeader() {
        List<ProductTabsHeaderVo> headers = new ArrayList<>();
        ProductTabsHeaderVo header1 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_UP);
        ProductTabsHeaderVo header2 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_DOWN);
        ProductTabsHeaderVo header3 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_OVER);
        ProductTabsHeaderVo header4 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_WARN);
        ProductTabsHeaderVo header5 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_CYCLE);
        ProductTabsHeaderVo header6 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_WAIT_AUDIT);
        ProductTabsHeaderVo header7 = new ProductTabsHeaderVo(1, ProductConstants.PRODUCT_STATUS_AUDIT_FAILED);
        headers.add(header1);
        headers.add(header2);
        headers.add(header3);
        headers.add(header4);
        headers.add(header5);
        headers.add(header6);
        headers.add(header7);
        return headers;
    }

    /**
     * 平台分页查询商品信息列表
     * @param productParam
     * @return
     * @date 2023-06-11
     */
    public GenericPager<EbProduct> queryPageProductList(ProductParam productParam){
        StringBuilder sql = new StringBuilder(SQL_PAGE_LIST);
        if(productParam == null){
            sql.append(SQL_PAGE_ORDER);
            return this.selectSplit(sql.toString(), new Object[]{}, new EbProduct());
        }

        Map<String, Object> parameters = new HashMap<>(4);

        int type = productParam.getType();
        if(type == ProductConstants.PRODUCT_STATUS_UP){
            sql.append(" and p.is_show = 1");
            sql.append(" and (p.audit_status=0 or p.audit_status=2)");
        } else if(type == ProductConstants.PRODUCT_STATUS_DOWN){
            sql.append(" and p.is_show = 0");
            sql.append(" and p.is_audit = 0");
            sql.append(" and p.audit_status in (0,1,2)");
        } else if(type == ProductConstants.PRODUCT_STATUS_WAIT_AUDIT){
            sql.append(" and p.audit_status = 1");
            sql.append(" and p.is_audit = 1");
        } else if (type == ProductConstants.PRODUCT_STATUS_AUDIT_FAILED) {
            sql.append(" and p.audit_status = 3");
            sql.append(" and p.is_audit = 0");
        }

        if(productParam.getMerId() != null && productParam.getMerId().intValue() > 0){
            sql.append(" and p.mer_id=:merId");
            parameters.put("merId", productParam.getMerId());
        }
        if(productParam.getIsSelf() != null){
            sql.append(" and m.is_self=:isSelf");
            parameters.put("isSelf", productParam.getIsSelf());
        }
        if(productParam.getCategoryId() != null){
            sql.append(" and p.category_id=:categoryId");
            parameters.put("categoryId", productParam.getCategoryId());
        }
        if(StringUtils.isNotEmpty(productParam.getKeywords())){
            sql.append(" and (p.name like :keywords or p.keyword like :keywords)");
            parameters.put("keywords", StringUtils.CHAR_PERCENT + productParam.getKeywords() + StringUtils.CHAR_PERCENT);
        }
        sql.append(SQL_PAGE_ORDER);
        return this.selectSplit(sql.toString(), parameters, new EbProduct());
    }

    public int queryAttrNextId(){
        int maxId = this.queryForInt(ATTR_NEXT_ID, new Object[]{});
        return maxId+1;
    }
    public int queryAttrValueNextId(){
        int maxId = this.queryForInt(ATTR_VALUE_NEXT_ID, new Object[]{});
        return maxId+1;
    }

    private static final String ATTR_NEXT_ID = "select max(id) from eb_product_attr";
    private static final String ATTR_VALUE_NEXT_ID = "select max(id) from eb_product_attr_value";
    private static final String SQL_PAGE_ORDER = " ORDER BY p.rank desc, p.id desc";
    private static final String SQL_PAGE_LIST = "SELECT p.id,p.mer_id,p.image,p.name,p.keyword,p.category_id,p.price,p.sales" +
            ",p.stock,p.ficti,p.audit_status,p.reason,p.rank FROM eb_product p right join eb_merchant m on p.mer_id = m.id " +
            "where p.is_del = 0 and p.is_recycle = 0";
}