/*
 * Click nbfs://nbhost/SystemFileSystem/Templates/Licenses/license-default.txt to change this license
 * Click nbfs://nbhost/SystemFileSystem/Templates/Classes/Class.java to edit this template
 */
package com.megatim.apifdxweb.dao.impl.tmp;

import com.megatim.apifdxweb.dao.ifaces.tmp.TypeFichierDataTmpDAO;
import com.megatim.apifdxweb.model.tmp.TypeFichierDataTmp;
import com.megatim.fdxcommons.model.integration.ColumnDefinition;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Optional;
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 TypeFichierDataTmpDAOImpl implements TypeFichierDataTmpDAO {

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

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

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

    @Override
    public TypeFichierDataTmp getById(Long id) {
        Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t  WHERE t.id = :id");
        query.setParameter("id", id);

        Optional<TypeFichierDataTmp> optionalTypeFichierDataTmp = query.getResultList().stream().findFirst();
        return optionalTypeFichierDataTmp.isPresent() ? optionalTypeFichierDataTmp.get() : null;
    }

    @Override
    public void deleteByToken(String token) {
        Query query = em.createQuery("DELETE TypeFichierDataTmp t WHERE t.token = :token");
        query.setParameter("token", token);

        query.executeUpdate();
    }

    @Override
    public List<TypeFichierDataTmp> getByToken(String token) {
//        WITH NumberedValues AS (
//    SELECT 
//        colonne,
//        datum,
//        ROW_NUMBER() OVER (PARTITION BY colonne ORDER BY numeroligne) AS row_num
//    FROM 
//        typefichierdatatmp
//	where token='dd6ed96a-0bfc-38ce-bdf4-5d9716325f4a'
//)
//SELECT 
//    MAX(CASE WHEN colonne = 'niu' THEN datum END) AS NIU,
//    MAX(CASE WHEN colonne = 'codecentre' THEN datum END) AS codecentre,
//    MAX(CASE WHEN colonne = 'codecm' THEN datum END) AS codecm,
//	MAX(CASE WHEN colonne = 'regime' THEN datum END) AS regime,
//	MAX(CASE WHEN colonne = 'registre_commerce' THEN datum END) AS registre_commerce,
//	MAX(CASE WHEN colonne = 'raisoc' THEN datum END) AS raisoc,
//	MAX(CASE WHEN colonne = 'nom_etablisement' THEN datum END) AS nom_etablisement
//	
//FROM 
//    NumberedValues
//GROUP BY 
//    row_num
//ORDER BY 
//    row_num;

        Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token");
        query.setParameter("token", token);

        return query.getResultList();
    }

    @Override
    public List<TypeFichierDataTmp> getByTokenAndByNumeroLigne(String token, int minNumeroLigne, int maxNumeroLigne) {
        Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token AND t.numeroLigne >= : minNumeroLigne AND t.numeroLigne < :maxNumeroLigne");
        query.setParameter("token", token);
        query.setParameter("minNumeroLigne", minNumeroLigne);
        query.setParameter("maxNumeroLigne", maxNumeroLigne);

        return query.getResultList();
    }

    @Override
    public boolean hasBatchAlreadyBeenReceived(String token, int batchNumber) {

        Query query = em.createQuery("SELECT t FROM TypeFichierDataTmp t WHERE t.token = :token and t.batchNumber = :batchNumber");
        query.setParameter("token", token);
        query.setParameter("batchNumber", batchNumber);

        return query.getResultList().stream().findFirst().isPresent();
    }

    @Override
    public List<LinkedHashMap<String, Object>> getByStructuredLines(String token, List<ColumnDefinition> columnsDef) {
        Collections.sort(columnsDef, (ColumnDefinition c1, ColumnDefinition c2) -> Integer.valueOf(c1.getPosition()).compareTo(c2.getPosition()));
        List<LinkedHashMap<String, Object>> results = new ArrayList<>();

        String query = constructQuery(token, columnsDef);
        List<Tuple> tuples = em.createNativeQuery(query, Tuple.class).getResultList();

        tuples.stream().forEach(t -> {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            for (int i = 0; i < columnsDef.size(); i++) {
                map.put(columnsDef.get(i).getName(), t.get(i, String.class));
            }
            results.add(map);
        });
        return results;
    }

    private String constructQuery(String token, List<ColumnDefinition> columnsDef) {
        StringBuilder builder = new StringBuilder("WITH Lines AS ("
                + " SELECT colonne, datum, ROW_NUMBER() OVER (PARTITION BY colonne ORDER BY numeroligne) AS row_num"
                + " FROM typefichierdatatmp"
                + " WHERE token='" + token + "'"
                + ") SELECT ");
        for (ColumnDefinition c : columnsDef) {
            builder.append(" MAX(CASE WHEN colonne = '").append(c.getName()).append("'  THEN datum END) AS ").append(c.getName()).append(",");
        }
        builder.replace(builder.length() - 1, builder.length(), "");
        builder.append(" FROM Lines GROUP BY row_num ORDER BY row_num");

        return builder.toString();
    }
}