package com.jasamedika.medifirst2000.dao;

import com.jasamedika.medifirst2000.entities.SkoringTindakanPerawat;
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("skoringTindakanPerawatDao")
public interface SkoringTindakanPerawatDao extends PagingAndSortingRepository<SkoringTindakanPerawat, String> {

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " order by prp.namaProduk asc, " + "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabled();

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and lower(prp.namaProduk) like %:namaProduk%" + " order by prp.namaProduk asc, "
			+ "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByProduk(@Param("namaProduk") String namaProduk);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and stp.statusVerifikasi = :isVerif" + " order by prp.namaProduk asc, "
			+ "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByVerif(@Param("isVerif") Boolean isVerif);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and prp.klasifikasi = :kdKlasif" + " order by prp.namaProduk asc, "
			+ "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByKlasif(@Param("kdKlasif") Short kdKlasif);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and lower(prp.namaProduk) like %:namaProduk%" + " and stp.statusVerifikasi = :isVerif"
			+ " order by prp.namaProduk asc, " + "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByProdukVerif(@Param("namaProduk") String namaProduk,
			@Param("isVerif") Boolean isVerif);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and lower(prp.namaProduk) like %:namaProduk%" + " and prp.klasifikasi = :kdKlasif"
			+ " order by prp.namaProduk asc, " + "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByProdukKlasif(@Param("namaProduk") String namaProduk,
			@Param("kdKlasif") Short kdKlasif);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and stp.statusVerifikasi = :isVerif" + " and prp.klasifikasi = :kdKlasif"
			+ " order by prp.namaProduk asc, " + "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByVerifKlasif(@Param("isVerif") Boolean isVerif,
			@Param("kdKlasif") Short kdKlasif);

	@Query("select new Map(stp.noRec as noRec," + "stp.statusEnabled as statusEnabled," + "stp.skor as skor,"
			+ "stp.statusVerifikasi as kdVerif," + "(case when stp.statusVerifikasi is false then 'Belum Verifikasi' "
			+ "when stp.statusVerifikasi is true then 'Terverifikasi' end) as stVerif,"
			+ "stp.tanggalMulaiBerlaku as tglMulaiBerlaku," + "stp.tanggalPembaharuanData as tglPembaharuanData,"
			+ "prp.id as produkId,prp.namaProduk as namaProduk," + "prp.klasifikasi as kdKlasif,"
			+ "(case when prp.klasifikasi = 0 then 'Tidak Ada' " + "when prp.klasifikasi = 1 then 'Asuhan' "
			+ "when prp.klasifikasi = 2 then 'Tindakan' " + "when prp.klasifikasi = 3 then 'Laporan' end) as stKlasif) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') <= to_char(now(),'yyyy-MM-dd')"
			+ " and lower(prp.namaProduk) like %:namaProduk%" + " and stp.statusVerifikasi = :isVerif"
			+ " and prp.klasifikasi = :kdKlasif" + " order by prp.namaProduk asc, "
			+ "stp.tanggalMulaiBerlaku desc, stp.tanggalPembaharuanData desc")
	List<Map<String, Object>> findAllEnabledByProdukVerifKlasif(@Param("namaProduk") String namaProduk,
			@Param("isVerif") Boolean isVerif, @Param("kdKlasif") Short kdKlasif);

	@Query("select stp.noRec " + "from SkoringTindakanPerawat stp " + "inner join stp.produk prp "
			+ "where stp.statusEnabled is true "
			+ "and lower(regexp_replace(prp.namaProduk,:patternText,'','g')) = :namaProduk" + " and stp.skor = :skor"
			+ " order by stp.tanggalMulaiBerlaku desc")
	List<String> findLastDupSkoring(@Param("namaProduk") String namaProduk, @Param("skor") Double skor,
			@Param("patternText") String patternText);

	@Query("select stp.noRec " + "from SkoringTindakanPerawat stp " + "inner join stp.produk prp "
			+ "where stp.statusEnabled is true "
			+ "and lower(regexp_replace(prp.namaProduk,:patternText,'','g')) = :namaProduk"
			+ " and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') = :tglMulaiBerlaku")
	List<String> findDupSkoring(@Param("namaProduk") String namaProduk,
			@Param("tglMulaiBerlaku") String tglMulaiBerlaku, @Param("patternText") String patternText);

	@Query("select stp.noRec " + "from SkoringTindakanPerawat stp " + "inner join stp.produk prp "
			+ "where stp.statusEnabled is true "
			+ "and lower(regexp_replace(prp.namaProduk,:patternText,'','g')) = :namaProduk" + " and stp.skor = :skor"
			+ " and stp.noRec <> :noRec" + " order by stp.tanggalMulaiBerlaku desc")
	List<String> findLastDupSkoring(@Param("namaProduk") String namaProduk, @Param("skor") Double skor,
			@Param("patternText") String patternText, @Param("noRec") String noRec);

	@Query("select stp.noRec " + "from SkoringTindakanPerawat stp " + "inner join stp.produk prp "
			+ "where stp.statusEnabled is true "
			+ "and lower(regexp_replace(prp.namaProduk,:patternText,'','g')) = :namaProduk"
			+ " and to_char(stp.tanggalMulaiBerlaku,'yyyy-MM-dd') = :tglMulaiBerlaku" + " and stp.noRec <> :noRec")
	List<String> findDupSkoring(@Param("namaProduk") String namaProduk,
			@Param("tglMulaiBerlaku") String tglMulaiBerlaku, @Param("patternText") String patternText,
			@Param("noRec") String noRec);

	@Query("select new Map(stp.noRec as noRec,stp.skor as skor," + "prp.namaProduk as namaProduk) "
			+ "from SkoringTindakanPerawat stp " + "inner join stp.produk prp " + "where stp.statusEnabled is true "
			+ "and stp.noRec in (:listNorec)")
	List<Map<String, Object>> findAllDup(@Param("listNorec") List<String> listNorec);

	@Query(value = "select * from sdm_skoringtindakanperawat_t "
			+ "where statusenabled is true and statusverifikasi is true "
			+ "and objectprodukperawatfk = :produkId and to_char(tanggalmulaiberlaku,'yyyy-MM-dd') <= :tglMulaiBerlaku "
			+ "order by tanggalmulaiberlaku desc, tanggalpembaharuandata desc limit 1", nativeQuery = true)
	SkoringTindakanPerawat findLast(@Param("produkId") Integer idProduk,
			@Param("tglMulaiBerlaku") String tglMulaiBerlaku);

}
