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

import com.bekosoftware.genericdaolayer.dao.impl.AbstractGenericDAO;
import com.megatim.fdxconsultation.dao.ifaces.supervision.TransfertDAO;
import com.megatim.fdxconsultation.model.supervision.SensTransfert;
import com.megatim.fdxconsultation.model.supervision.Transfert;
import com.megatim.fdxconsultation.model.supervision.dto.SupervisionFilterModalFormRequest;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author ASUS
 */
@Stateless
public class TransfertDAOImpl extends AbstractGenericDAO<Transfert, Long> implements TransfertDAO {

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

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

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

    @Override
    public List<Transfert> filter(SupervisionFilterModalFormRequest supervisionFilterModalFormRequest) {

        Query query = em.createQuery(filterQueryString(supervisionFilterModalFormRequest));
        setParameters(query, supervisionFilterModalFormRequest);

        return query
                .setFirstResult(0)
                .setMaxResults(200)
                .getResultList();
    }

    @Override
    public Long getTotalTransfert(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(COUNT(t), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery + " AND " : " WHERE ")
                + " etattransfert = 'TERMINE'";

        Query query = em.createQuery(queryString);

        return (Long) result(query, sens, dateDebut, dateFin);
    }

    @Override
    public Long getTransfertsReussie(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(COUNT(t), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery + " AND " : " WHERE ")
                + " t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.TERMINE";

        Query query = em.createQuery(queryString);

        return (Long) result(query, sens, dateDebut, dateFin);
    }

    @Override
    public Long getTransfertsAttente(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(COUNT(t), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery + " AND " : " WHERE ")
                + " t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.ATTENTE_ROUTAGE";

        Query query = em.createQuery(queryString);

        return (Long) result(query, sens, dateDebut, dateFin);
    }

    @Override
    public Long getTransfertsEchec(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(COUNT(t), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery + " AND " : " WHERE ")
                + " t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.ECHOUE "
                + "AND t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.ECHEC_EMPREINTE "
                + "AND t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.ECHEC_NOMENCLATURE";

        Query query = em.createQuery(queryString);

        return (Long) result(query, sens, dateDebut, dateFin);
    }

    @Override
    public Long getTransfertsManuel(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {
        return 0L;
    }

    @Override
    public Long getTransfertsAutomatique(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {
        return 0L;
    }

    @Override
    public Double getTailleTotale(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();
        String queryString = "SELECT COALESCE(SUM(t.taille), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery : "");

        Query query = em.createQuery(queryString);
        return ((double) result(query, sens, dateDebut, dateFin)) / 1000;
    }

    @Override
    public Long getNombreTypeFichier(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(COUNT(DISTINCT t.typeFichier), 0) FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery : "");

        Query query = em.createQuery(queryString);

        return (Long) result(query, sens, dateDebut, dateFin);
    }

    @Override
    public Double getVitesseMoyenne(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        double tempsMisTermine = getTempsMisTermine(sens, dateDebut, dateFin);
        double tailleTotaleTermine = getTailleTotaleTermine(sens, dateDebut, dateFin) / 1000;

        return (tempsMisTermine > 0) ? (tailleTotaleTermine / tempsMisTermine) : 0;
    }

    private Double getTailleTotaleTermine(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).query();

        String queryString = "SELECT COALESCE(SUM(t.taille), 0) "
                + "FROM Transfert t " + (!whereQuery.isEmpty() ? "WHERE " + whereQuery + " AND " : " WHERE ")
                + " t.etatTransfert = com.megatim.fdxconsultation.model.supervision.EtatTransfert.TERMINE";

        Query query = em.createQuery(queryString);

        return (double) result(query, sens, dateDebut, dateFin);
    }

    private Double getTempsMisTermine(SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {

        String whereNativeQuery = new TransfertDaoDynamicWhereString(sens, dateDebut, dateFin).nativeQuery();

        String queryString = "SELECT COALESCE(SUM(DATE_PART('second', t.datefin - t.datedebut)), 0) AS date_diff "
                + "FROM transfert t " + (!whereNativeQuery.isEmpty() ? "WHERE " + whereNativeQuery + " AND " : " WHERE ")
                + " t.etattransfert = 'TERMINE'";

        Query query = em.createNativeQuery(queryString);

        return (double) nativeResult(query, sens, dateDebut, dateFin);
    }

    private Object result(Query query, SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {
        if (sens != null) {
            setParam(query, "sens", sens);
        }

        if (dateDebut != null) {
            setParam(query, "dateDebut", dateDebut);
        }

        if (dateFin != null) {
            setParam(query, "dateFin", dateFin.toLocalDate().atTime(LocalTime.MAX));
        }

        return query.getSingleResult();
    }

    private Object nativeResult(Query query, SensTransfert sens, LocalDateTime dateDebut, LocalDateTime dateFin) {
        if (sens != null) {
            setParam(query, "sens", sens.toString());
        }

        if (dateDebut != null) {
            setParam(query, "dateDebut", Timestamp.valueOf(dateDebut));
        }

        if (dateFin != null) {
            setParam(query, "dateFin", Timestamp.valueOf(dateFin.toLocalDate().atTime(LocalTime.MAX)));
        }

        return query.getSingleResult();
    }

    private void setParam(Query query, String paramName, Object paramvalue) {
        if (paramvalue != null) {
            query.setParameter(paramName, paramvalue);
        }
    }

    private String filterQueryString(SupervisionFilterModalFormRequest supervisionFilterModalFormRequest) {
        StringBuilder queryString = new StringBuilder("SELECT t FROM Transfert t");

        boolean hasCondition = false;

        if (supervisionFilterModalFormRequest.getNomFichier() != null) {
            queryString.append(" WHERE t.nomFichier LIKE :nomFichier");
            hasCondition = true;
        }

        if (supervisionFilterModalFormRequest.getTypeFichier() != null) {
            if (!hasCondition) {
                queryString.append(" WHERE ");
            } else {
                queryString.append(" AND ");
            }
            hasCondition = true;

            queryString.append("t.typeFichier = :typeFichier");
        }

        if (supervisionFilterModalFormRequest.getSensTransfert() != null) {
            if (!hasCondition) {
                queryString.append(" WHERE ");
            } else {
                queryString.append(" AND ");
            }
            hasCondition = true;

            queryString.append("t.sensTransfert = :sensTransfert");
        }

        if (supervisionFilterModalFormRequest.getEtatTransfert() != null) {
            if (!hasCondition) {
                queryString.append(" WHERE ");
            } else {
                queryString.append(" AND ");
            }
            hasCondition = true;

            queryString.append("t.etatTransfert = :etatTransfert");
        }

        if (supervisionFilterModalFormRequest.getDateDebut() != null) {
            if (!hasCondition) {
                queryString.append(" WHERE ");
            } else {
                queryString.append(" AND ");
            }
            hasCondition = true;

            queryString.append("t.dateCreation >= :dateDebut");
        }

        if (supervisionFilterModalFormRequest.getDateFin() != null) {
            if (!hasCondition) {
                queryString.append(" WHERE ");
            } else {
                queryString.append(" AND ");
            }

            queryString.append("t.dateDebut <= :dateFin");
        }

        return queryString.toString();
    }

    private void setParameters(Query query, SupervisionFilterModalFormRequest supervisionFilterModalFormRequest) {

        if (supervisionFilterModalFormRequest.getNomFichier() != null) {
            setParam(query, "nomFichier", "%" + supervisionFilterModalFormRequest.getNomFichier() + "%");
        }

        if (supervisionFilterModalFormRequest.getTypeFichier() != null) {
            setParam(query, "typeFichier", supervisionFilterModalFormRequest.getTypeFichier().getCode());
        }

        if (supervisionFilterModalFormRequest.getSensTransfert() != null) {
            setParam(query, "sensTransfert", supervisionFilterModalFormRequest.getSensTransfert());
        }

        if (supervisionFilterModalFormRequest.getEtatTransfert() != null) {
            setParam(query, "etatTransfert", supervisionFilterModalFormRequest.getEtatTransfert());
        }

        if (supervisionFilterModalFormRequest.getDateDebut() != null) {
            setParam(query, "dateDebut", supervisionFilterModalFormRequest.getDateDebut().toLocalDate().atStartOfDay());
        }

        if (supervisionFilterModalFormRequest.getDateFin() != null) {
            setParam(query, "dateFin", supervisionFilterModalFormRequest.getDateFin().toLocalDate().atTime(LocalTime.MAX));
        }

    }

}