package com.eksad.masterdata.repository;

import com.eksad.ddms.common.util.status.DataStatus;
import com.eksad.masterdata.common.dto.PromoCategoryInfo;
import com.eksad.masterdata.domain.Promo;
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 PromoRepository extends GenericRepository<Promo> {
    List<Promo> findAllByOrderByIdAsc();

    List<Promo> findAllByPromoStatusOrderByIdAsc(DataStatus status);

    List<Promo> findAllByPromoStatusAndPromoNameContainingIgnoreCase(DataStatus dataStatus, String promoName);

    Page<Promo> findAllByPromoStatusAndPromoNameContainingIgnoreCase(DataStatus dataStatus, String promoName, Pageable pageable);

    Page<Promo> findAllByPromoStatus(DataStatus status, Pageable pageable);

    Promo findOneByPromoIDAndPromoStatus(String promoID, DataStatus status);

    List<Promo> findAllByPromoStatusAndCategoryIDOrderByIdAsc(DataStatus status, String categoryID);

    //    List<Promo> findAllByCustomerGenderAndCustomerOccupationAndCustomerTypeAndPromoStatus(PromoGenderCustomerType customerGender,
//                                                                                          PromoCustomerOccupationType customerOccupation,
//                                                                                          CustomerType customerType, DataStatus promoStatus);
    @Query(value = "SELECT p FROM Promo p JOIN p.listPromoAhass pa where pa.ahassID in ('ALL', :ahassID) and p.promoStatus = :status ")
    Page<Promo> findAllByPromoStatusAndAhassID(@Param("status") DataStatus status, @Param("ahassID") String ahassID, Pageable pageable);

    @Query(value = "SELECT p FROM Promo p JOIN p.listPromoAhass pa where pa.ahassID in ('ALL', :ahassID) and p.promoStatus = :status and UPPER(p.promoName) like %:promoName% ")
    Page<Promo> findAllByPromoStatusAndPromoNameContainingIgnoreCaseAndAhassID(@Param("status") DataStatus dataStatus, @Param("promoName") String promoName, @Param("ahassID") String ahassID, Pageable pageable);

    @Query(value = "SELECT p FROM Promo p JOIN p.listPromoAhass pa where pa.ahassID in ('ALL', :ahassID) and p.promoStatus = :status and pa.promoAhassStatus = 'ACTIVE' ")
    Page<Promo> findAllByPromoStatusAndPromoAhassStatusAndAhassID(@Param("status") DataStatus status, @Param("ahassID") String ahassID, Pageable pageable);

    @Query(value = "SELECT p FROM Promo p JOIN p.listPromoAhass pa where pa.ahassID in ('ALL', :ahassID) and p.promoStatus = :status and pa.promoAhassStatus = 'ACTIVE' and UPPER(p.promoName) like %:promoName% ")
    Page<Promo> findAllByPromoStatusAndPromoAhassStatusAndPromoNameContainingIgnoreCaseAndAhassID(@Param("status") DataStatus dataStatus, @Param("promoName") String promoName, @Param("ahassID") String ahassID, Pageable pageable);

    @Query("Select p From Promo p Where p.promoID = :promoid")
    Promo findPromoByPromoID(@Param("promoid") String promoID);

    @Query(nativeQuery = true, value = "Select mp.* From mst_promo mp join mst_category mc on mp.categoryid = mc.categoryid where mp.categoryid = :categoryID and mp.promostatus='ACTIVE'")
    List<Promo> findAllByPromoFilteredByCategoryID(@Param("categoryID") String categoryID);


    @Query(nativeQuery = true, value = "Select p.* From mst_promo p join mst_promoahass pa on p.promoid = pa.promoid Where ((p.ahasstype = 'ALL_AHASS') or \n" +
            "(p.ahasstype = 'SPESIFIC_AHASS' and pa.ahassid =:ahassid )) and p.promostatus = 'ACTIVE'  AND (:dateNow between p.promostartdate and p.promoenddate) AND pa.promoahassstatus = 'ACTIVE'")
    List<Promo> findPromoByPromoDate(@Param("dateNow") Date dateNow, @Param("ahassid") String ahassid);

    @Query(nativeQuery = true, value = "select mp.*, mc.categoryname from mst_promo mp join mst_category mc on mp.categoryid = mc.categoryid where mp.promostatus ='ACTIVE' and mp.promoid =:promoID")
    PromoCategoryInfo findPromoByPromoIDAndPromoStatusByFunction(@Param("promoID") String promoID);

    @Query(nativeQuery = true, value = "select * from mst_promo mp inner join mst_promoahass mp2 on mp.promoid = mp2.promoid")
    List<Promo> promoById(@Param("promoid") String promoID);

    @Query(nativeQuery = true, value = "select * from get_on_sale (?1, ?2, ?3, ?4, ?5 , ?6)")
    List<Promo> getOnSale(String ahassid, String unitCategoryID, String unitSeriesID, String unitTypeID, String areaID, String workshopServiceID);

    @Query(nativeQuery = true, value = "select * from get_on_sale_v2 (?1, ?2, ?3, ?4, ?5 , ?6)")
    List<Promo> getOnSale2(String ahassid, String unitCategoryID, String unitSeriesID, String unitTypeID, String areaType, String workshopServiceID);

    @Query(nativeQuery = true, value = "Select p.* From mst_promo p \n" +
            "join mst_promoahass pa on p.promoid = pa.promoid and pa.promoahassstatus = 'ACTIVE'\n" +
            "left join mst_promopos pp on pa.promoid = pp.promoid and pp.promoposstatus = 'ACTIVE'\n" +
            "Where \n" +
            "( \n" +
            "	(p.ahasstype = 'ALL_AHASS') \n" +
            "	or \n" +
            "	( \n" +
            "		(p.ahasstype = 'SPESIFIC_AHASS' and pa.ahassid =:ahassID)\n" +
            "		and \n" +
            "		(p.lokasitype = 'ALL_LOKASI' \n" +
            "		or \n" +
            "			(p.lokasitype = 'SPESIFIC_LOKASI' and pp.posserviceid =:posserviceid)\n" +
            "		) \n" +
            "	) \n" +
            ") \n" +
            "and p.promostatus = 'ACTIVE'  AND (:dateNow between p.promostartdate and p.promoenddate) AND pa.promoahassstatus = 'ACTIVE';")
    List<Promo> findPromoByPromoDatePos(@Param("dateNow") Date dateNow, @Param("ahassID") String ahassID, @Param("posserviceid") String posServiceID);

    @Query(nativeQuery = true, value = "Select p.* From mst_promo p \n" +
            "join mst_promoahass pa on p.promoid = pa.promoid and pa.promoahassstatus = 'ACTIVE'\n" +
            "left join mst_promopos pp on pa.promoid = pp.promoid and pp.promoposstatus = 'ACTIVE'\n" +
            "Where \n" +
            "( \n" +
            "	(p.ahasstype = 'ALL_AHASS') \n" +
            "	or \n" +
            "	( \n" +
            "		(p.ahasstype = 'SPESIFIC_AHASS' and pa.ahassid =:ahassID)\n" +
            "		and \n" +
            "		(p.lokasitype = 'ALL_LOKASI' or p.lokasitype isnull\n" +
            "		or \n" +
            "			(p.lokasitype = 'SPESIFIC_LOKASI' and pp.posserviceid isnull)\n" +
            "		) \n" +
            "	) \n" +
            ") \n" +
            "and p.promostatus = 'ACTIVE'  AND (:dateNow between p.promostartdate and p.promoenddate) AND pa.promoahassstatus = 'ACTIVE';")
    List<Promo> findPromoByPromoDatePosNull(@Param("dateNow") Date dateNow, @Param("ahassID") String ahassID);
}