package com.jasamedika.medifirst2000.dao;

import com.jasamedika.medifirst2000.entities.ProdukPerawat;
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;

/**
 * @author salmanoe
 * @since Oct 8, 2021
 */
@Repository("produkPerawat")
public interface ProdukPerawatDao extends PagingAndSortingRepository<ProdukPerawat, Integer> {

	@Query("select new Map(prp.id as id,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 1 then 'Asuhan' " + "when prp.klasifikasi = 2 then 'Tindakan' "
			+ "when prp.klasifikasi = 3 then 'Laporan' end) as klasifikasi) " + "from ProdukPerawat prp "
			+ "where prp.statusEnabled is true" + " order by prp.klasifikasi, prp.namaProduk asc")
	List<Map<String, Object>> findAllActive();

	@Query("select new Map(prp.id as id,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 1 then 'Asuhan' " + "when prp.klasifikasi = 2 then 'Tindakan' "
			+ "when prp.klasifikasi = 3 then 'Laporan' end) as klasifikasi) " + "from ProdukPerawat prp "
			+ "where prp.statusEnabled is true" + " and lower(prp.namaProduk) like %:namaProduk%"
			+ " order by prp.klasifikasi, prp.namaProduk asc")
	List<Map<String, Object>> findAllActive(@Param("namaProduk") String namaProduk);

	@Query("select new Map(prp.id as id,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 1 then 'Asuhan' " + "when prp.klasifikasi = 2 then 'Tindakan' "
			+ "when prp.klasifikasi = 3 then 'Laporan' end) as klasifikasi) " + "from ProdukPerawat prp "
			+ "where prp.statusEnabled is true" + " and prp.klasifikasi = :kdKlasif"
			+ " order by prp.klasifikasi, prp.namaProduk asc")
	List<Map<String, Object>> findAllActive(@Param("kdKlasif") Short kdKlasif);

	@Query("select new Map(prp.id as id,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 1 then 'Asuhan' " + "when prp.klasifikasi = 2 then 'Tindakan' "
			+ "when prp.klasifikasi = 3 then 'Laporan' end) as klasifikasi) " + "from ProdukPerawat prp "
			+ "where prp.statusEnabled is true" + " and lower(prp.namaProduk) like %:namaProduk%"
			+ " and prp.klasifikasi = :kdKlasif" + " order by prp.klasifikasi, prp.namaProduk asc")
	List<Map<String, Object>> findAllActive(@Param("namaProduk") String namaProduk, @Param("kdKlasif") Short kdKlasif);

	@Query("select new Map(prp.id as id,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 1 then 'Asuhan' " + "when prp.klasifikasi = 2 then 'Tindakan' "
			+ "when prp.klasifikasi = 3 then 'Laporan' end) as klasifikasi) " + "from ProdukPerawat prp "
			+ "where prp.statusEnabled is true" + " and prp.id in (:listProdukId)"
			+ " order by prp.klasifikasi, prp.namaProduk asc")
	List<Map<String, Object>> findProdukPerawat(@Param("listProdukId") List<Integer> listIdProduk);

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

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

}
