package com.jasamedika.medifirst2000.dao;

import com.jasamedika.medifirst2000.entities.ProdukFarmakologi;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("produkFarmakologiDao")
public interface ProdukFarmakologiDao extends PagingAndSortingRepository<ProdukFarmakologi, Integer> {

	@Query("select new Map(prf.id as produkId,prf.namaProduk as namaProduk,"
			+ "prf.kdProduk as kdProduk,(case when prf.kdProduk = 1 then 'Pelayanan Pasien (Tarif)' "
			+ "when prf.kdProduk = 2 then 'Pelayanan Pasien (Nontarif)' "
			+ "when prf.kdProduk = 3 then 'Pelayanan Nonpasien' end) as kodeProduk) " + "from ProdukFarmakologi prf "
			+ "where prf.statusEnabled is true" + " order by prf.namaProduk, prf.kdProduk asc")
	List<Map<String, Object>> findProdukFarmakologi();

	@Query("select new Map(prf.id as produkId,prf.namaProduk as namaProduk,"
			+ "prf.kdProduk as kdProduk,(case when prf.kdProduk = 1 then 'Pelayanan Pasien (Tarif)' "
			+ "when prf.kdProduk = 2 then 'Pelayanan Pasien (Nontarif)' "
			+ "when prf.kdProduk = 3 then 'Pelayanan Nonpasien' end) as kodeProduk) " + "from ProdukFarmakologi prf "
			+ "where prf.statusEnabled is true" + " and lower(prf.namaProduk) like %:namaProduk%"
			+ " order by prf.namaProduk, prf.kdProduk asc")
	List<Map<String, Object>> findProdukFarmakologiByProduk(@Param("namaProduk") String namaProduk);

	@Query("select new Map(prf.id as produkId,prf.namaProduk as namaProduk,"
			+ "prf.kdProduk as kdProduk,(case when prf.kdProduk = 1 then 'Pelayanan Pasien (Tarif)' "
			+ "when prf.kdProduk = 2 then 'Pelayanan Pasien (Nontarif)' "
			+ "when prf.kdProduk = 3 then 'Pelayanan Nonpasien' end) as kodeProduk) " + "from ProdukFarmakologi prf "
			+ "where prf.statusEnabled is true" + " and prf.kdProduk = :kdProduk"
			+ " order by prf.namaProduk, prf.kdProduk asc")
	List<Map<String, Object>> findProdukFarmakologiByKdProduk(@Param("kdProduk") Short kdProduk);

	@Query("select new Map(prf.id as produkId,prf.namaProduk as namaProduk,"
			+ "prf.kdProduk as kdProduk,(case when prf.kdProduk = 1 then 'Pelayanan Pasien (Tarif)' "
			+ "when prf.kdProduk = 2 then 'Pelayanan Pasien (Nontarif)' "
			+ "when prf.kdProduk = 3 then 'Pelayanan Nonpasien' end) as kodeProduk) " + "from ProdukFarmakologi prf "
			+ "where prf.statusEnabled is true" + " and lower(prf.namaProduk) like %:namaProduk%"
			+ " and prf.kdProduk = :kdProduk" + " order by prf.namaProduk, prf.kdProduk asc")
	List<Map<String, Object>> findProdukFarmakologiByProduk(@Param("namaProduk") String namaProduk,
			@Param("kdProduk") Short kdProduk);

	@Query("select new Map(prf.id as produkId,prf.namaProduk as namaProduk,"
			+ "prf.kdProduk as kdProduk,(case when prf.kdProduk = 1 then 'Pelayanan Pasien (Tarif)' "
			+ "when prf.kdProduk = 2 then 'Pelayanan Pasien (Nontarif)' "
			+ "when prf.kdProduk = 3 then 'Pelayanan Nonpasien' end) as kodeProduk) " + "from ProdukFarmakologi prf "
			+ "where prf.statusEnabled is true" + " and prf.id in (:listProdukId)"
			+ " order by prf.namaProduk, prf.kdProduk asc")
	List<Map<String, Object>> findProdukFarmakologi(@Param("listProdukId") List<Integer> listIdProduk);

	@Query("select prf.id from ProdukFarmakologi prf " + "where prf.statusEnabled is true "
			+ "and lower(regexp_replace(prf.namaProduk,:patternText,'','g')) = :namaProduk")
	List<Integer> findDupProduk(@Param("patternText") String patternText, @Param("namaProduk") String namaProduk);

	@Query("select prf.id from ProdukFarmakologi prf " + "where prf.statusEnabled is true "
			+ "and lower(regexp_replace(prf.namaProduk,:patternText,'','g')) = :namaProduk"
			+ " and prf.id <> :produkId")
	List<Integer> findDupProduk(@Param("patternText") String patternText, @Param("namaProduk") String namaProduk,
			@Param("produkId") Integer idProduk);

}
