package id.co.sofcograha.gajiidapi.base.mst.user.repositories;

import javax.persistence.EntityManager;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import id.co.sofcograha.gajiidapi.base.extendables.BaseRepository;
import id.co.sofcograha.gajiidapi.base.mst.user.entities.DaftarUserOtorisasi;
import id.co.sofcograha.gajiidapi.base.search.NativeSearchBuilder;
import id.co.sofcograha.gajiidapi.base.search.SearchParameter;
import id.co.sofcograha.gajiidapi.base.search.SearchResult;
import id.co.sofcograha.gajiidapi.base.utils.QueryUtil;
import id.co.sofcograha.gajiidapi.base.utils.TimeUtil;

@Repository
public class DaftarUserOtorisasiRepository extends BaseRepository<DaftarUserOtorisasi> {

    public DaftarUserOtorisasiRepository(EntityManager em) {
        super(DaftarUserOtorisasi.class, em);
    }

    @Transactional
    public SearchResult<DaftarUserOtorisasi> searchDaftarUserOtorisasi(SearchParameter searchParameter) {
        String userName = (String) searchParameter.getValueFromMappedParam("userName");
        String name = (String) searchParameter.getValueFromMappedParam("name");
        String perusahaanId = (String) searchParameter.getValueFromMappedParam("perusahaan.id");
        String kodePerusahaan = (String) searchParameter.getValueFromMappedParam("kodePerusahaan");
        String namaPerusahaan = (String) searchParameter.getValueFromMappedParam("namaPerusahaan");
        Boolean flagAdmin = (Boolean) searchParameter.getValueFromMappedParam("flagAdmin");
        Boolean flagSuperUser = (Boolean) searchParameter.getValueFromMappedParam("flagSuperUser");
        Boolean flagActive = (Boolean) searchParameter.getValueFromMappedParam("flagActive");

        StringBuilder queryStringBuilder = new StringBuilder();
        queryStringBuilder.append("SELECT          public.sys_guid() as id, a.id as hm_msy004_user_id, a.username, a.name, c.id as hm_msy002_company_id, \n");
        queryStringBuilder.append("                c.code as hm_msy002_company_code, c.name as hm_msy002_company_name, \n");
        queryStringBuilder.append("                CASE WHEN c.id = a.hm_msy002_id THEN TRUE ELSE FALSE END as default, \n");
        queryStringBuilder.append("                a.email, a.admin, COALESCE(b.super_user, FALSE) as super_user, a.locked, \n");
        queryStringBuilder.append("                CASE WHEN a.admin = TRUE THEN a.active ELSE CASE WHEN b.end_date = :maxDate THEN TRUE ELSE FALSE END END as active, \n");
        queryStringBuilder.append("                a.version, a.user_create, a.creation_time, \n");
        queryStringBuilder.append("                a.user_modify, a.modification_time \n");
        queryStringBuilder.append("FROM            public.hm_msy004 a \n");
        queryStringBuilder.append("LEFT OUTER JOIN public.hm_msy005 b ON b.hm_msy004_id = a.id \n");
        queryStringBuilder.append("INNER JOIN      public.hm_msy002 c ON c.id = b.hm_msy002_id \n");
        queryStringBuilder.append("WHERE           1 = 1 \n");

        NativeSearchBuilder<DaftarUserOtorisasi> search = new NativeSearchBuilder<>(DaftarUserOtorisasi.class, em);
        search.addParameter("maxDate", TimeUtil.getMaxDate());

        if (!QueryUtil.isAll(userName)) {
          queryStringBuilder.append("AND LOWER(a.username) LIKE LOWER(:username) \n");
          search.addParameter("username", QueryUtil.formatStringForLikeFilter(userName));
        }
        if (!QueryUtil.isAll(name)) {
          queryStringBuilder.append("AND LOWER(a.name) LIKE LOWER(:name) \n");
          search.addParameter("name", QueryUtil.formatStringForLikeFilter(name));
        }
        if (perusahaanId != null) {
          queryStringBuilder.append("AND b.id = :perusahaanId \n");
          search.addParameter("perusahaanId", perusahaanId);
        }
        if (!QueryUtil.isAll(kodePerusahaan)) {
          queryStringBuilder.append("AND LOWER(b.code) LIKE LOWER(:kodePerusahaan) \n");
          search.addParameter("kodePerusahaan", QueryUtil.formatStringForLikeFilter(kodePerusahaan));
        }
        if (!QueryUtil.isAll(namaPerusahaan)) {
          queryStringBuilder.append("AND LOWER(b.name) LIKE LOWER(:namaPerusahaan) \n");
          search.addParameter("namaPerusahaan", QueryUtil.formatStringForLikeFilter(namaPerusahaan));
        }
        if (flagAdmin != null) {
          queryStringBuilder.append("AND a.id = :flagAdmin \n");
          search.addParameter("flagAdmin", flagAdmin);
        }
        if (flagSuperUser != null) {
          queryStringBuilder.append("AND a.id = :flagAdmin \n");
          search.addParameter("flagAdmin", flagAdmin);
        }

        if (flagActive != null) {
          queryStringBuilder.append("AND CASE WHEN a.admin = TRUE THEN \n");
          queryStringBuilder.append("        a.active \n");
          queryStringBuilder.append("    ELSE \n");
          queryStringBuilder.append("        COALESCE(CASE WHEN :flagActive THEN \n");
          queryStringBuilder.append("                   now() BETWEEN c.start_date AND c.end_date \n");
          queryStringBuilder.append("                ELSE \n");
          queryStringBuilder.append("                   c.end_date < :systemDate \n");
          queryStringBuilder.append("                END, FALSE) \n");
          queryStringBuilder.append("    END = :flagActive \n");
          search.addParameter("systemDate", TimeUtil.getSystemDate());
          search.addParameter("flagActive", flagActive);
        }

        search.setQueryString(queryStringBuilder);
        search.setPagination(searchParameter.getPagination());
        search.setSort(searchParameter.getSort());

        return search.getSearchResult();
    }
}