package com.eksad.masterdata.repository;

import com.eksad.ddms.common.util.status.DataStatus;
import com.eksad.masterdata.domain.UnitCustomer;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface UnitCustomerRepository extends GenericRepository<UnitCustomer> {
    List<UnitCustomer> findAllByUnitCustomerStatusOrderByIdAsc(DataStatus unitCustomerStatus);

    List<UnitCustomer> findAllByUnitCustomerStatusAndPoliceNumberContainingIgnoreCase(DataStatus dataStatus, String policeNumber);

    List<UnitCustomer> findAllByUnitCustomerStatusAndPoliceNumberIgnoreCase(DataStatus dataStatus, String policeNumber);

    List<UnitCustomer> findAllByUnitCustomerStatusAndPoliceNumberAndMachineNumberIgnoreCase(DataStatus dataStatus, String policeNumber, String machineNumber);

    List<UnitCustomer> findAllByUnitCustomerStatusAndUnitCustomerIDIgnoreCase(DataStatus dataStatus, String unitCustomerID);

    UnitCustomer findOneByUnitCustomerID(String unitCustomerID);

    UnitCustomer findOneByMachineNumberAndUnitCustomerStatus(String machineNumber, DataStatus unitCustomerStatus);

    UnitCustomer findOneByChasisNumberAndUnitCustomerStatus(String chasisNumber, DataStatus unitCustomerStatus);

    UnitCustomer findOneByUnitCustomerStatusAndPoliceNumber(DataStatus unitCustomerStatus, String policeNumber);

    Page<UnitCustomer> findAllByUnitCustomerStatus(DataStatus dataStatus, Pageable pageable);

    Page<UnitCustomer> findAllByDealerIDAndUnitCustomerStatus(String dealerid, DataStatus dataStatus, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndCustomerNameContainingIgnoreCase(DataStatus dataStatus, String customerName, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndSexIDContainingIgnoreCase(DataStatus dataStatus, String sexID, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndNoKtpContainingIgnoreCase(DataStatus dataStatus, String noKtp, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndNoStnkContainingIgnoreCase(DataStatus dataStatus, String noStnk, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndPoliceNumberContainingIgnoreCase(DataStatus dataStatus, String policeNumber, Pageable pageable);

    Page<UnitCustomer> findAllByUnitCustomerStatusAndMachineNumberContainingIgnoreCase(DataStatus dataStatus, String machineNumber, Pageable pageable);

    List<UnitCustomer> findAllByUnitCustomerStatusAndMachineNumberContainingIgnoreCase(DataStatus dataStatus, String machineNumber);

    List<UnitCustomer> findAllByUnitCustomerStatusAndMachineNumberIgnoreCase(DataStatus dataStatus, String machineNumber);

    List<UnitCustomer> findAllByUnitCustomerStatusAndChasisNumberIgnoreCase(DataStatus dataStatus, String chasisNumber);

    UnitCustomer findAllByUnitTypeID(String unitTypeID);

    @Query(value = "select uc from UnitCustomer uc ,WorkOrder wo where uc.unitCustomerID=wo.unitCustomerID AND "
            + "wo.woID= :woID", nativeQuery = false)
    public UnitCustomer findUnitCustomerByWOID(@Param("woID") String woID);

    @Query(value = "select uc from UnitCustomer uc , WorkOrder wo, SalesOrderWorkOrder sowo where uc.unitCustomerID=wo.unitCustomerID AND "
            + "wo.woID = sowo.woID AND sowo.soID= :soID", nativeQuery = false)
    public UnitCustomer findUnitCustomerBySOID(@Param("soID") String soID);

    @Query(nativeQuery = true, value = "SELECT u.* FROM mst_unitcustomer u WHERE u.createdat between :from AND :to ")
    List<UnitCustomer> findUnitCustomerByRangeCreatedDate(@Param("from") Date from, @Param("to") Date to);

    Integer countAllByUnitCustomerStatus(DataStatus dataStatus);

    @Query(value = "Select count(distinct tw.nopolisi) from mst_unitcustomer mu join (select * from trx_wo where trx_wo.wolastknownstatus = 'COMPLETE') tw on mu.unitcustomerid = tw.unitcustomerid where tw.tahunperakitan >= :tahun", nativeQuery = true)
    Integer countAllByUnitFilterWoId(@Param("tahun") Integer tahun);

    @Query(value = "select * from mst_unitcustomer mu join (select * from trx_wo where trx_wo.wolastknownstatus = 'COMPLETE') tw on mu.unitcustomerid = tw.unitcustomerid where tw.tahunperakitan >= :tahun \n-- #pageable\n",
            countQuery = "Select count(distinct tw.nopolisi) from mst_unitcustomer mu join (select * from trx_wo where trx_wo.wolastknownstatus = 'COMPLETE') tw on mu.unitcustomerid = tw.unitcustomerid where tw.tahunperakitan >= :tahun",
            nativeQuery = true)
    Page<UnitCustomer> findAllByUnitCustomerFilterWoid(@Param("tahun") Integer tahun, Pageable pageable);

    @Query(value = "select * from mst_unitcustomer mu join get_unit_h1_by_chasisnumber(:chasisNumber) gu on gu.machinenumber = mu.machinenumber", nativeQuery = true)
    List<UnitCustomer> findUnitCustomerByChasisNumber(@Param("chasisNumber") String chasisNumber);

    @Query(value = "select * from get_unit_h1_by_machinenumber_v2(:machineNumber)", nativeQuery = true)
    List<UnitCustomer> findUnitCustomerByMachineNumber(@Param("machineNumber") String machineNumber);

    @Query(value = "select * from get_unit_h1_by_chasisnumber_v2(:chasisNumber)", nativeQuery = true)
    List<UnitCustomer> findUnitCustomerByChasisNumberv2(@Param("chasisNumber") String machineNumber);
}