package com.jasamedika.medifirst2000.dao;

import com.jasamedika.medifirst2000.dto.MonitoringJatahCutiDto;
import com.jasamedika.medifirst2000.entities.JatahCutiDanIzin;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

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

@Repository("JatahCutiDanIzinDao")
public interface JatahCutiDanIzinDao extends JpaRepository<JatahCutiDanIzin, String> {

	@Query("select jatahCuti " + "from JatahCutiDanIzin jatahCuti " + "left join jatahCuti.pegawai pegawai "
			+ "left join jatahCuti.komponenIndex komponenIndex "
			+ "where jatahCuti.tahun = :tahun and pegawai.id = :idPegawai " + "and komponenIndex.id = :komponenIndexId")
	JatahCutiDanIzin getJatahCuti(@Param("tahun") String tahun, @Param("idPegawai") Integer idPegawai,
			@Param("komponenIndexId") Integer komponenIndexId);

	@Query("select jatahCuti " + "from JatahCutiDanIzin jatahCuti " + "left join jatahCuti.pegawai pegawai "
			+ "left join jatahCuti.komponenIndex komponenIndex "
			+ "where jatahCuti.tahun = :tahun and pegawai.id = :idPegawai "
			+ "and komponenIndex.id in (:komponenIndexId)")
	JatahCutiDanIzin getJatahCuti(@Param("tahun") String tahun, @Param("idPegawai") Integer idPegawai,
			@Param("komponenIndexId") List<Integer> komponenIndexId);

	@Query("select coalesce(jatahCuti.value,0)  " + "from JatahCutiDanIzin jatahCuti "
			+ "left join jatahCuti.pegawai pegawai " + "left join jatahCuti.komponenIndex komponenIndex "
			+ "where jatahCuti.tahun = :tahun and pegawai.id = :idPegawai " + "and komponenIndex.id = :komponenIndexId")
	Integer getDataCuti(@Param("tahun") String tahun, @Param("idPegawai") Integer idPegawai,
			@Param("komponenIndexId") Integer komponenIndexId);

	@Query("select jatahCuti.isTangguhkan " + "from JatahCutiDanIzin jatahCuti "
			+ "left join jatahCuti.pegawai pegawai " + "left join jatahCuti.komponenIndex komponenIndex "
			+ "where jatahCuti.tahun = :tahun and pegawai.id = :idPegawai " + "and komponenIndex.id = :komponenIndexId")
	Boolean getPenangguhanSisaCuti(@Param("tahun") String tahun, @Param("idPegawai") Integer idPegawai,
			@Param("komponenIndexId") Integer komponenIndexId);

	@Query("select new map(" + "jatahCuti.value as value, " + "jatahCuti.sisaCuti as sisaCuti, "
			+ "jatahCuti.cutiTerpakai as cutiTerpakai, " + "jatahCuti.tahun as tahun, "
			+ "jatahCuti.isTangguhkan as isTangguhkan, " + "pegawai.id as idPegawai, "
			+ "pegawai.namaLengkap as namaPegawai, " + "pegawai.nipPns as nipPegawai, " + "unitKerja.name as unitKerja,"
			+ "subUnitKerja.name as subUnitKerja)  "
			+ "from JatahCutiDanIzin jatahCuti, MapPegawaiJabatanToUnitKerja mapPegawai "
			+ "left join jatahCuti.pegawai pegawai " + "left join jatahCuti.komponenIndex komponenIndex "
			+ "left join mapPegawai.subUnitKerjaPegawai subUnitKerja "
			+ "left join mapPegawai.unitKerjaPegawai unitKerja " + "where mapPegawai.pegawaiId = pegawai.id "
			+ "and mapPegawai.isPrimary is true " + "and mapPegawai.statusEnabled is true "
			+ "and komponenIndex.id = :komponenIndexId " + "order by pegawai.id, jatahCuti.tahun")
	List<Map<String, Object>> getAllJatahCuti(@Param("komponenIndexId") Integer komponenIndexId);

	@Query("select new map(" + "jatahCuti.noRec as noRec, " + "jatahCuti.komponenIndexId as komponenIndexId, "
			+ "komponenIndex.komponenIndex as komponenIndex, " + "jatahCuti.tahun as tahun, "
			+ "jatahCuti.value as value, " + "jatahCuti.sisaCuti as sisaCuti, "
			+ "jatahCuti.cutiTerpakai as cutiTerpakai, " + "jatahCuti.isTangguhkan as isTangguhkan, "
			+ "pegawai.id as idPegawai, " + "pegawai.namaLengkap as namaPegawai, " + "pegawai.nipPns as nipPegawai, "
			+ "unitKerja.name as unitKerja," + "subUnitKerja.name as subUnitKerja)  "
			+ "from JatahCutiDanIzin jatahCuti, MapPegawaiJabatanToUnitKerja mapPegawai "
			+ "left join jatahCuti.pegawai pegawai " + "left join jatahCuti.komponenIndex komponenIndex "
			+ "left join mapPegawai.subUnitKerjaPegawai subUnitKerja "
			+ "left join mapPegawai.unitKerjaPegawai unitKerja " + "where mapPegawai.pegawaiId = pegawai.id "
			+ "and mapPegawai.isPrimary is true " + "and mapPegawai.statusEnabled is true "
			+ "and cast(jatahCuti.tahun as int) >= cast(to_char(current_date,'yyyy') as int)-2 "
			+ "and pegawai.id = :pegawaiId " + "and komponenIndex.id in (:komponenIndexId) "
			+ "order by jatahCuti.tahun, komponenIndex.komponenIndex")
	List<Map<String, Object>> getAllJatahCuti(@Param("komponenIndexId") List<Integer> komponenIndexId,
			@Param("pegawaiId") Integer pegawaiId);

	@Query("select l.tgl " + "from PlanningPegawaiStatus p " + "left join p.listTanggal l "
			+ "where p.statusEnabled is true and l.statusEnabled is true "
			+ "and p.approvalStatus = 1 and l.approvalStatus is true " + "and p.statusPegawaiPlanId = :idStatusPlan "
			+ "and p.pegawaiId = :idPegawai " + "and to_char(l.tgl, 'yyyy-MM-dd') = :tglCuti " + "order by l.tgl")
	List<Date> getCutiTahunanDiTglCutiBersama(@Param("idStatusPlan") Integer idStatusPlan,
			@Param("idPegawai") Integer idPegawai, @Param("tglCuti") String tglCuti);

	@Query("select sum(jc.cutiTerpakai) from JatahCutiDanIzin jc "
			+ "where jc.pegawaiId = :pegawaiId and jc.tahun < :tahun")
	Integer getHistoriCutiTerpakai(@Param("pegawaiId") Integer idPegawai, @Param("tahun") String tahun);

	@Query("select new com.jasamedika.medifirst2000.dto.MonitoringJatahCutiDto(" + "jc.noRec as noRec,"
			+ "kx.komponenIndex as komponenCuti," + "cast(jc.tahun as int) as tahun," + "jc.value as jatahCuti,"
			+ "jc.cutiTerpakai as cutiTerpakai," + "jc.sisaCuti as sisaCuti,"
			+ "case when jc.isTangguhkan is true and kx.id = 5 then 'Ditangguhkan'"
			+ "when jc.isTangguhkan is false and kx.id = 5 then 'Belum/Tidak Ditangguhkan'"
			+ "else 'Tidak Dapat Ditangguhkan' end as statusPenangguhan) " + "from JatahCutiDanIzin jc "
			+ "inner join jc.komponenIndex kx " + "where jc.pegawaiId = :pegawaiId "
			+ "order by cast(jc.tahun as int), jc.komponenIndexId desc")
	List<MonitoringJatahCutiDto> findByPegawaiId(@Param("pegawaiId") Integer idPegawai);

}
