package com.ff.weijinxiao.repository;


import com.ff.weijinxiao.domain.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CustomerRepository extends JpaRepository<Customer, Integer>, JpaSpecificationExecutor<Customer> {
    Customer findByOnlyID(String onlyID);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(sales_price)) THEN 0 ELSE (SUM(sales_price)  ) END  FROM tb_order WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getCustomerTotalAmount(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(sales_price)) THEN 0 ELSE (SUM(sales_price)  ) END  FROM tb_order WHERE uid = :uid AND isDelete=0 and custom_id=:customerOnlyId", nativeQuery = true)
    Double getCustomerTotalAmountByCustomerOnlyId(String uid, String customerOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(realReceive)) THEN 0 ELSE (SUM(realReceive)  ) END  FROM tb_order WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getCustomerAleadyPayed1(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(realReceive)) THEN 0 ELSE (SUM(realReceive)  ) END  FROM tb_order WHERE uid = :uid AND isDelete=0 and custom_id=:customerOnlyId", nativeQuery = true)
    Double getCustomerAleadyPayed1ByCostomerOnlyId(String uid, String customerOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(payAmount)) THEN 0 ELSE (SUM(payAmount)  ) END  FROM tb_paybackcustom WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getCustomerAleadyPayed2(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(payAmount)) THEN 0 ELSE (SUM(payAmount)  ) END  FROM tb_paybackcustom WHERE uid = :uid AND isDelete=0 and custom_id=:customerOnlyId ", nativeQuery = true)
    Double getCustomerAleadyPayed2ByCostomerOnlyId(String uid, String customerOnlyId);

    @Query(value = "SELECT count(DISTINCT(t.goods_id)) FROM tb_order_good t LEFT JOIN tb_order tt ON t.order_id=tt.onlyid WHERE tt.custom_id=:customerOnlyId AND tt.uid =:uid ",nativeQuery = true)
    Integer countCustomerBuyedGoods(String uid, String customerOnlyId );
}
