package com.loan.system.repository;

import com.loan.system.domain.dto.CustomerDTO;
import com.loan.system.domain.entity.Customer;
import com.loan.system.domain.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

/**
 * @author EdwinXu
 * @date 2020/9/2 - 15:35
 * @Description
 */
public interface CustomerRepository extends JpaRepository<Customer,Long> {
    boolean existsByMobileAndIsDelete(String mobile,Boolean isDelete);

    @Query("select c from Customer c where c.mobile = ?1 and c.isDelete = ?2")
    Customer findByMobile(String mobile,Boolean isDelete);

    Page<Customer> findByIsDelete(Boolean isDelete, Pageable pageable);

    Customer findByIdAndIsDelete(Long id, Boolean isDelete);
    
    @Query("select c from Customer c where (c.mobile like ?1 or c.name like ?1) and c.isDelete = ?2")
    Page<Customer> findByKey(String key, boolean isDelete, Pageable pageable);

    @Transactional
    @Modifying
    @Query("update Customer c set c.marriedStatus = ?2 ,c.isIllegal = ?3 where c.id = ?1")
    void updateMarriedStatusAndIsIllegalById(Long customerId, String marriedStatus,Boolean isIllegal);

    @Query("select co from Customer co where co.id = ?1 and co.isDelete = ?2")
    Customer findByCustomerId(Long customerId , Boolean isDelete);

    boolean existsByIdAndIsDelete(Long customerId, boolean isDelete);

    @Query("select co from Customer co where (co.mobile like ?1 or co.name like ?1) and co.isDelete = ?2")
    List<Customer> findAllByKey(String key, boolean isDelete);

    @Transactional
    @Modifying
    @Query("update Customer c set c.isDelete = 1 where c.mobile = ?1 and c.name = ?2")
    void deleteByMobileAndName(String mobile, String name);

    @Transactional
    @Modifying
    @Query(value = "UPDATE Customer c SET " +
            "c.name = COALESCE(:#{#customerDTO.name}, c.name), " +
            "c.sex = COALESCE(:#{#customerDTO.sex}, c.sex), " +
            "c.idNumber = COALESCE(:#{#customerDTO.idNumber}, c.idNumber), " +
            "c.bankAccount = COALESCE(:#{#customerDTO.bankAccount}, c.bankAccount), " +
            "c.bankName = COALESCE(:#{#customerDTO.bankName}, c.bankName) " +
            "WHERE c.openid = :#{#customerDTO.openid} AND c.isDelete = :isDelete")
    void updateInfoByOpenIdAndIsDelete(CustomerDTO customerDTO, boolean isDelete);

    @Transactional
    @Modifying
    @Query("update Customer c set c.isDelete = 1 where c.id = ?1")
    void deleteByLogic(Long id);

    @Query("select c from Customer c where c.openid = ?1")
    Customer findByOpenId(String openId);
}