package it.integry.integrywmsnative.gest.prod_fabbisogno_linee_prod.rest;

import com.google.gson.reflect.TypeToken;

import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.inject.Singleton;

import it.integry.integrywmsnative.core.expansion.RunnableArgs;
import it.integry.integrywmsnative.core.rest.RESTBuilder;
import it.integry.integrywmsnative.core.rest.consumers.MagazzinoBufferRESTConsumerService;
import it.integry.integrywmsnative.core.rest.consumers.SystemRESTConsumer;
import it.integry.integrywmsnative.core.rest.consumers._BaseRESTConsumer;
import it.integry.integrywmsnative.core.rest.model.ServiceRESTResponse;
import it.integry.integrywmsnative.core.utility.UtilityDB;
import it.integry.integrywmsnative.core.utility.UtilityDate;
import it.integry.integrywmsnative.gest.prod_fabbisogno_linee_prod.dto.ProdFabbisognoLineeProdDTO;
import retrofit2.Call;
import retrofit2.Callback;
import retrofit2.Response;

@Singleton
public class ProdFabbisognoLineeProdRESTConsumer extends _BaseRESTConsumer {

    private final SystemRESTConsumer mSystemRESTConsumer;

    public ProdFabbisognoLineeProdRESTConsumer(SystemRESTConsumer systemRESTConsumer) {
        this.mSystemRESTConsumer = systemRESTConsumer;
    }


    public void loadFabbisogno(String numOrd, String codMdep, RunnableArgs<List<ProdFabbisognoLineeProdDTO>> onComplete, RunnableArgs<Exception> onFailed) {
        ProdFabbisognoLineeProdRESTConsumerService prodFabbisognoLineeProdRESTConsumerService = RESTBuilder.getService(ProdFabbisognoLineeProdRESTConsumerService.class);
        prodFabbisognoLineeProdRESTConsumerService.loadFabbisogno(numOrd, codMdep)
                .enqueue(new Callback<>() {
                    @Override
                    public void onResponse(Call<ServiceRESTResponse<List<ProdFabbisognoLineeProdDTO>>> call,
                                           Response<ServiceRESTResponse<List<ProdFabbisognoLineeProdDTO>>> response) {
                        analyzeAnswer(response, "retrieveFabbisogno", onComplete, onFailed);
                    }

                    @Override
                    public void onFailure(Call<ServiceRESTResponse<List<ProdFabbisognoLineeProdDTO>>> call, Throwable t) {
                        onFailed.run(new Exception(t));
                    }
                });
    }


    public void loadFabbisogno(String codMart, Date startDate, Date endDate, String codMdep, RunnableArgs<List<ProdFabbisognoLineeProdDTO>> onComplete, RunnableArgs<Exception> onFailed) {
        String sql = "WITH input_values AS ( " +
                "    SELECT " + UtilityDB.valueToString(startDate) + " AS data_inizio, " +
                "           " + UtilityDB.valueToString(endDate) + " AS data_fine, " +
//                "           " + UtilityDB.valueToString(lineaProd) + " AS linea_prod, " +
                "           " + UtilityDB.valueToString(codMdep) + " AS cod_mdep)," +
                " withdraw_mtb_colr AS ( " +
                "         SELECT cod_mart, " +
                "                SUM(ISNULL(qta_col, 0)) as qta_col, " +
                "                SUM(ISNULL(num_cnf, 0)) as num_cnf, " +
                "                mtb_colr.data_ord, " +
                "                mtb_colr.num_ord, " +
                "                mtb_colr.gestione " +
                "         FROM mtb_colr " +
                "                  INNER JOIN mtb_colt mc on mtb_colr.gestione = mc.gestione and mtb_colr.data_collo = mc.data_collo and " +
                "                                            mtb_colr.ser_collo = mc.ser_collo and mtb_colr.num_collo = mc.num_collo " +
                "         WHERE segno = -1 " +
                "           AND cod_dtip IS NULL " +
                "         GROUP BY cod_mart, " +
                "                  mtb_colr.data_ord, " +
                "                  mtb_colr.num_ord, " +
                "                  mtb_colr.gestione " +
                "     ), " +
                "     custom_mtb_colr AS (SELECT cod_mart, " +
                "                                SUM(qta_col) AS qta_col, " +
                "                                SUM(num_cnf) AS num_cnf, " +
                "                                posizione " +
                "                         FROM mvw_sitart_udc_det_inventario " +
                "                         GROUP BY cod_mart, posizione), " +
                "     grouped_ord AS ( " +
                "         SELECT dtb_ordr.cod_mart, " +
                "                dtb_ordt.cod_jfas, " +
                "                dtb_ordt.cod_mdep, " +
                "                dtb_ordt.gestione, " +
                "                CONVERT(NUMERIC(15, 5), SUM(dtb_ordr.qta_ord * dtb_ordr.rap_conv) - " +
                "                                        SUM(dtb_ordr.qta_evasa * dtb_ordr.rap_conv)) AS qta_ord, " +
                "                SUM(dtb_ordr.num_cnf - dtb_ordr.num_cnf_evasa)                       AS num_cnf_ord, " +
                "                SUM(ISNULL(withdraw_mtb_colr.qta_col, 0))                            AS qta_col_scaricata, " +
                "                SUM(ISNULL(withdraw_mtb_colr.num_cnf, 0))                            AS num_cnf_scaricata " +
                "         FROM dtb_ordr " +
                "                  INNER JOIN dtb_ordt on dtb_ordr.gestione = dtb_ordt.gestione AND " +
                "                                         dtb_ordr.data_ord = dtb_ordt.data_ord AND " +
                "                                         dtb_ordr.num_ord = dtb_ordt.num_ord " +
                "                  LEFT OUTER JOIN withdraw_mtb_colr " +
                "                                  ON withdraw_mtb_colr.gestione = dtb_ordt.gestione AND " +
                "                                     withdraw_mtb_colr.num_ord = dtb_ordt.num_ord AND " +
                "                                     withdraw_mtb_colr.data_ord = dtb_ordt.data_ord AND " +
                "                                     withdraw_mtb_colr.cod_mart = dtb_ordr.cod_mart " +
                "                  CROSS APPLY input_values " +
                "         WHERE dtb_ordt.gestione = 'L' " +
                "           AND flag_evaso = 'I' " +
                "           AND dtb_ordr.flag_evaso_forzato = 'N' " +
                "           AND flag_annulla = 'N' " +
                "           AND dtb_ordt.cod_mdep = input_values.cod_mdep " +
                "           AND dtb_ordt.data_ord BETWEEN input_values.data_inizio AND input_values.data_fine " +
                "         GROUP BY dtb_ordr.cod_mart, dtb_ordt.cod_jfas, dtb_ordt.cod_mdep, dtb_ordt.gestione " +
                "     ), " +
                "     tmp_ord AS (SELECT grouped_ord.cod_mart, " +
                "                        mtb_aart.unt_mis, " +
                "                        grouped_ord.cod_jfas, " +
                "                        SUM(qta_ord) - SUM(qta_col_scaricata)     AS qta_ord, " +
                "                        SUM(num_cnf_ord) - SUM(num_cnf_scaricata) AS num_cnf_ord " +
                "                 FROM grouped_ord " +
                "                          INNER JOIN mtb_aart ON grouped_ord.cod_mart = mtb_aart.cod_mart " +
                "                          CROSS APPLY input_values " +
                "                 GROUP BY grouped_ord.cod_mart, mtb_aart.unt_mis, grouped_ord.cod_jfas " +
                "     ), " +
                "     ord_without_positioned_mtb_colt AS ( " +
                "         SELECT tmp_ord.cod_mart, " +
                "                tmp_ord.cod_jfas, " +
                "                ISNULL(custom_mtb_colr_on_linea.posizione, tmp_ord.cod_jfas) AS posizione, " +
                "                unt_mis, " +
                "                qta_ord                                                      AS qta_ord_calc, " +
                "                num_cnf_ord                                                  AS num_cnf_ord_calc, " +
                "                ISNULL(custom_mtb_colr_on_linea.qta_col, 0)                  AS qta_gia_posizionata, " +
                "                ISNULL(custom_mtb_colr_on_linea.num_cnf, 0)                  AS num_cnf_gia_posizionata, " +
                "                qta_ord - ISNULL(custom_mtb_colr_on_linea.qta_col, 0)        AS qta_fabbisogno, " +
                "                num_cnf_ord - ISNULL(custom_mtb_colr_on_linea.num_cnf, 0)    AS num_cnf_fabbisogno " +
                "         FROM tmp_ord " +
                "                  INNER JOIN jrl_fase_posizioni ON tmp_ord.cod_jfas = jrl_fase_posizioni.cod_jfas " +
                "             AND tmp_ord.cod_jfas = jrl_fase_posizioni.posizione " +
                "                  LEFT OUTER JOIN custom_mtb_colr custom_mtb_colr_on_linea " +
                "                                  ON tmp_ord.cod_mart = custom_mtb_colr_on_linea.cod_mart AND " +
                "                                     tmp_ord.cod_jfas = custom_mtb_colr_on_linea.posizione " +
                " " +
                "         UNION ALL " +
                " " +
                "         SELECT DISTINCT tmp_ord.cod_mart, " +
                "                         null                                        AS cod_jfas, " +
                "                         jrl_fase_posizioni.posizione, " +
                "                         unt_mis, " +
                "                         0                                           AS qta_ord_calc, " +
                "                         0                                           AS num_cnf_ord_calc, " +
                "                         ISNULL(custom_mtb_colr_on_linea.qta_col, 0) AS qta_gia_posizionata, " +
                "                         ISNULL(custom_mtb_colr_on_linea.num_cnf, 0) AS num_cnf_gia_posizionata, " +
                "                         0                                           AS qta_fabbisogno, " +
                "                         0                                           AS num_cnf_fabbisogno " +
                "         FROM tmp_ord " +
                "                  INNER JOIN jrl_fase_posizioni ON tmp_ord.cod_jfas = jrl_fase_posizioni.cod_jfas " +
                "             AND tmp_ord.cod_jfas <> jrl_fase_posizioni.posizione " +
                " " +
                " " +
                "                  LEFT OUTER JOIN custom_mtb_colr custom_mtb_colr_on_linea " +
                "                                  ON tmp_ord.cod_mart = custom_mtb_colr_on_linea.cod_mart AND " +
                "                                     jrl_fase_posizioni.posizione = custom_mtb_colr_on_linea.posizione " +
                "     ) " +
                "SELECT DISTINCT cod_mart, " +
                "       unt_mis, " +
                "       cod_jfas, " +
                "       input_values.data_inizio, " +
                "       input_values.data_fine " +
                "FROM ord_without_positioned_mtb_colt " +
                "CROSS APPLY input_values " +
                "WHERE cod_mart = " + UtilityDB.valueToString(codMart) + " " +
                "GROUP BY cod_mart, unt_mis, cod_jfas, input_values.data_inizio, input_values.data_fine " +
                "HAVING SUM(qta_ord_calc - qta_gia_posizionata) > 0 AND SUM(num_cnf_ord_calc - num_cnf_gia_posizionata) > 0 " +
                "ORDER BY cod_mart";


        Type typeOfObjectsList = new TypeToken<ArrayList<ProdFabbisognoLineeProdDTO>>() {}.getType();
        this.mSystemRESTConsumer.processSql(sql, typeOfObjectsList, onComplete, onFailed);
    }

}
