package com.megatim.fdxconsultation.dao.impl.stats;

import com.megatim.fdxcommons.model.referentiel.TypeFichier;
import com.megatim.fdxconsultation.dao.ifaces.stats.CriteriaEntityPersistedDAO;
import com.megatim.fdxconsultation.dao.ifaces.stats.TableConfigurationDAO;
import com.megatim.fdxconsultation.dao.ifaces.stats.TableauBordDAO;
import com.megatim.fdxconsultation.model.dto.ParticipantToFichierDto;
import com.megatim.fdxconsultation.model.stats.CriteriaEntityPersisted;
import com.megatim.fdxconsultation.model.stats.TableauBord;
import com.megatim.fdxconsultation.model.enums.TypeConfigurationTableauBord;
import com.megatim.fdxconsultation.model.searchentities.TypeFichierSearch;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.Tuple;

/**
 *
 * @author ASUS
 */
@Stateless
public class TableauBordDAOImpl implements TableauBordDAO {

    @PersistenceContext(unitName = "fdxConsultationPersistenceUnit")
    protected EntityManager em;

    @EJB
    TableConfigurationDAO tableConfigurationDao;

    @EJB
    CriteriaEntityPersistedDAO criteriaDao;

    @Override
    public Class<TableauBord> getManagedEntityClass() {
        return TableauBord.class;
    }

    @Override
    public EntityManager getEntityManager() {
        return em;
    }

    @Override
    public TableauBord getById(Long id) {
        Query query = em.createQuery("SELECT t FROM TableauBord t LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns WHERE t.id = :id");
        query.setParameter("id", id);

        TableauBord tableauBord = (TableauBord) query.getResultList().stream().findFirst().orElse(null);
        if (tableauBord != null) {
            loadCriteria(tableauBord);
        }

        return tableauBord;
    }

    @Override
    public TableauBord findByUserAndTypeFichierAndReferentiel(Long userId, String codeTypeFichier, String referentielVersion) {
        Query query = em.createQuery("SELECT t from TableauBord t LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns "
                + "WHERE t.user.id = :userId and t.typeFichier.code = :codeTypeFichier AND t.referentiel.version = :referentielVersion")
                .setParameter("codeTypeFichier", codeTypeFichier)
                .setParameter("userId", userId)
                .setParameter("referentielVersion", referentielVersion);

        TableauBord tableauBord = (TableauBord) query.getResultList().stream().findFirst().orElse(null);
        if (tableauBord != null) {
            loadCriteria(tableauBord);
        }

        return tableauBord;
    }

    @Override
    public List<TableauBord> findByUser(Long userId, String refVersion) {
        Query query = em.createQuery("SELECT t FROM TableauBord t"
                + " LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns"
                + " WHERE t.user.id = :userId AND t.referentiel.version = :refVersion")
                .setParameter("userId", userId)
                .setParameter("refVersion", refVersion);

        List<TableauBord> liste = query.getResultList();
        liste.forEach(t -> loadCriteria(t));

        return liste;
    }

    @Override
    public List<ParticipantToFichierDto> tableauBordToTypeFichiers(String userName, String referentielVersion) {
        List<Tuple> results = em.createQuery("SELECT t.participant.code, t.code AS code FROM TableauBord tb, TypeFichier t"
                + " WHERE tb.user.userName = :userName AND tb.referentiel.version = :referentielVersion"
                + " AND tb.typeFichier.code = t.code", Tuple.class)
                .setParameter("userName", userName)
                .setParameter("referentielVersion", referentielVersion)
                .getResultList();

        return results.stream().map(t -> {
            ParticipantToFichierDto dto = new ParticipantToFichierDto(t.get(0, String.class), t.get(1, String.class));
            return dto;
        }).collect(Collectors.toList());
    }

    @Override
    public List<ParticipantToFichierDto> tableauBordToTypeFichiers(TypeConfigurationTableauBord tableauBordConfiguration, String referentielVersion) {
        List<Tuple> results = em.createQuery("SELECT t.participant.code, t.code AS code from TableauBord tb, TypeFichier t"
                + " WHERE tb.typeConfiguration = :tableauBordConfiguration"
                + " AND tb.referentiel.version = :referentielVersion AND tb.typeFichier.code = t.code ", Tuple.class)
                .setParameter("tableauBordConfiguration", tableauBordConfiguration)
                .setParameter("referentielVersion", referentielVersion)
                .getResultList();

        return results.stream().map(t -> {
            ParticipantToFichierDto dto = new ParticipantToFichierDto(t.get(0, String.class), t.get(1, String.class));
            return dto;
        }).collect(Collectors.toList());
    }

    @Override
    public List<TableauBord> findByReferentiel(String referentielVersion) {
        return em.createQuery("SELECT t from TableauBord t"
                + " WHERE t.referentiel.version = :referentielVersion", TableauBord.class)
                .setParameter("referentielVersion", referentielVersion)
                .getResultList();

    }

    @Override
    public TableauBord findByTypeConfigurationAndReferentiel(String codeTypeFichier, String referentielVersion, TypeConfigurationTableauBord typeConfiguration) {
        Query query = em.createQuery("SELECT t from TableauBord t LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns "
                + "WHERE t.typeConfiguration = :typeConfiguration and t.typeFichier.code = :codeTypeFichier AND t.referentiel.version = :referentielVersion")
                .setParameter("codeTypeFichier", codeTypeFichier)
                .setParameter("typeConfiguration", typeConfiguration)
                .setParameter("referentielVersion", referentielVersion);

        Optional<TableauBord> optionalTableauBord = query.getResultList().stream().findFirst();
        TableauBord tableauBord = optionalTableauBord.isPresent() ? optionalTableauBord.get() : null;

        if (tableauBord == null) {
            return null;
        }
        loadCriteria(tableauBord);

        return tableauBord;
    }

    private void loadCriteria(TableauBord tableauBord) {
        if (tableauBord.getTableConfigurations() != null && !tableauBord.getTableConfigurations().isEmpty()) {
            tableauBord.getTableConfigurations().stream().forEach(t -> {
                if (t.getCriterion() != null) {
                    CriteriaEntityPersisted criterion = criteriaDao.loadSubCriteria(t.getCriterion());
                    t.setCriterion(criterion);
                }
            });
        }
    }

    @Override
    public List<TypeFichier> tableauBordTypeFichiers(String userName, String referentielVersion, TypeFichierSearch typeFichierSearch, int pageNumber, int pageSize) {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT t.typeFichier FROM TableauBord t")
                .append(" ")
                .append("WHERE t.typeConfiguration = :typeConfiguration AND t.user.userName = :userName AND t.referentiel.version = :referentielVersion");

        addQuerySearchPart(builder, typeFichierSearch);

        Query query = em.createQuery(builder.toString());
        query.setParameter("userName", userName);

        addQuerySearchParam(query, referentielVersion, TypeConfigurationTableauBord.USER_SPECIFIC, typeFichierSearch);
        query.setFirstResult((pageNumber - 1) * pageSize).setMaxResults(pageSize);

        return query.getResultList();
    }

    @Override
    public Long countTableauBordTypeFichiers(String userName, String referentielVersion, TypeFichierSearch typeFichierSearch) {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT COALESCE(COUNT(t.typeFichier), 0) FROM TableauBord t")
                .append(" ")
                .append("WHERE t.typeConfiguration = :typeConfiguration AND t.user.userName = :userName AND t.referentiel.version = :referentielVersion");

        addQuerySearchPart(builder, typeFichierSearch);

        Query query = em.createQuery(builder.toString());
        query.setParameter("userName", userName);

        addQuerySearchParam(query, referentielVersion, TypeConfigurationTableauBord.USER_SPECIFIC, typeFichierSearch);

        return (Long) query.getSingleResult();
    }

    @Override
    public List<TypeFichier> tableauBordGlobalTypeFichiers(String referentielVersion, TypeFichierSearch typeFichierSearch, int pageNumber, int pageSize) {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT t.typeFichier FROM TableauBord t")
                .append(" ")
                .append("WHERE t.typeConfiguration = :typeConfiguration AND t.referentiel.version = :referentielVersion");

        addQuerySearchPart(builder, typeFichierSearch);

        Query query = em.createQuery(builder.toString());
        addQuerySearchParam(query, referentielVersion, TypeConfigurationTableauBord.GLOBAL, typeFichierSearch);
        query.setFirstResult((pageNumber - 1) * pageSize).setMaxResults(pageSize);

        return query.getResultList();
    }

    @Override
    public Long countTableauBordGlobalTypeFichiers(String referentielVersion, TypeFichierSearch typeFichierSearch) {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT COALESCE(COUNT(t.typeFichier), 0) FROM TableauBord t")
                .append(" ")
                .append("WHERE t.typeConfiguration = :typeConfiguration AND t.referentiel.version = :referentielVersion");

        addQuerySearchPart(builder, typeFichierSearch);

        Query query = em.createQuery(builder.toString());
        addQuerySearchParam(query, referentielVersion, TypeConfigurationTableauBord.GLOBAL, typeFichierSearch);

        return (Long) query.getSingleResult();
    }

    private void addQuerySearchParam(Query query, String referentielVersion, TypeConfigurationTableauBord typeConfigurationTableauBord, TypeFichierSearch typeFichierSearch) {

        query.setParameter("referentielVersion", referentielVersion)
                .setParameter("typeConfiguration", typeConfigurationTableauBord);

        if (typeFichierSearch.getCode() != null) {
            query.setParameter("code", "%" + typeFichierSearch.getCode() + "%");
        }

        if (typeFichierSearch.getLibelle() != null) {
            query.setParameter("libelle", "%" + typeFichierSearch.getLibelle() + "%");
        }

        if (typeFichierSearch.getDescription() != null) {
            query.setParameter("description", "%" + typeFichierSearch.getDescription() + "%");
        }
    }

    private void addQuerySearchPart(StringBuilder builder, TypeFichierSearch typeFichierSearch) {

        if (typeFichierSearch.getCode() != null) {
            builder.append(" AND ");
            builder.append("t.typeFichier.code LIKE :code");
        }

        if (typeFichierSearch.getLibelle() != null) {
            builder.append(" AND ");
            builder.append("t.typeFichier.libelle LIKE :libelle");
        }

        if (typeFichierSearch.getDescription() != null) {
            builder.append(" AND ");
            builder.append("t.typeFichier.description LIKE :description");
        }
    }

    @Override
    public TableauBord findSpecificTableauBord(Long userId, String codeTypeFichier, String referentielVersion) {

        Query query = em.createQuery("SELECT t from TableauBord t LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns "
                + "WHERE t.user.id = :userId and t.typeFichier.code = :codeTypeFichier AND t.referentiel.version = :referentielVersion "
                + "AND t.typeConfiguration = :typeConfiguration")
                .setParameter("codeTypeFichier", codeTypeFichier)
                .setParameter("userId", userId)
                .setParameter("referentielVersion", referentielVersion)
                .setParameter("typeConfiguration", TypeConfigurationTableauBord.USER_SPECIFIC);

        TableauBord tableauBord = (TableauBord) query.getResultList().stream().findFirst().orElse(null);
        if (tableauBord != null) {
            loadCriteria(tableauBord);
        }

        return tableauBord;
    }

    @Override
    public TableauBord findGlobalTableauBord(String codeTypeFichier, String referentielVersion) {

        Query query = em.createQuery("SELECT t FROM TableauBord t LEFT JOIN FETCH t.tableConfigurations tc LEFT JOIN FETCH tc.groupingColumns "
                + "WHERE t.typeFichier.code = :codeTypeFichier AND t.referentiel.version = :referentielVersion "
                + "AND t.typeConfiguration = :typeConfiguration")
                .setParameter("codeTypeFichier", codeTypeFichier)
                .setParameter("referentielVersion", referentielVersion)
                .setParameter("typeConfiguration", TypeConfigurationTableauBord.GLOBAL);

        TableauBord tableauBord = (TableauBord) query.getResultList().stream().findFirst().orElse(null);
        if (tableauBord != null) {
            loadCriteria(tableauBord);
        }

        return tableauBord;
    }
}