package it.exprivia.eni.pdl.estrazione;

import it.exprivia.eni.pdl.PDLAPP_nomi.Siti;
import it.exprivia.eni.pdl.db.DatabaseManager;
import it.exprivia.eni.pdl.db.DatabaseManagerFactory;
import it.exprivia.eni.pdl.db.QueryRow;
import it.exprivia.eni.pdl.entity.PdlUser;
import it.exprivia.eni.pdl.ldap.PdlLDAPHelper;
import it.exprivia.eni.pdl.utility.PDLConstants;

import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class EstrazioneUtenze {

    public static void main(String[] args) throws Exception {
        SimpleDateFormat format = new SimpleDateFormat(
                "ddMMyyyyHHmmss");
        String config = "config_PROD.properties";

        Properties props = new Properties();
        props.load(EstrazioneUtenze.class.getResourceAsStream(config));

        PdlLDAPHelper.setup(props.getProperty("ldapUser"), props.getProperty("ldapPassword"),
                props.getProperty("ldapDomain"), props.getProperty("ldapURL"));
        PdlLDAPHelper ldap = new PdlLDAPHelper();

        PrintWriter output = new PrintWriter(
                "Utenti eWP " + format.format(new Date()) + "_siti.csv");
        //by Ros 21/05/2019 ->
        //String header = "Matricola;Profilo;Nome;Cognome;BusinessUnit;Sito;Mail";
        String header = "Matricola;Profilo;BusinessUnit";
        //<-

        String queryUtentiInterni = "SELECT U.MATRICOLA AS USERID, U.NOME AS NOME, U.COGNOME AS COGNOME, "
                + "UPPER(LISTAGG(R.DESCRIZIONE, ',') WITHIN GROUP(ORDER BY R.DESCRIZIONE)) AS PROFILO, "
                + "UPPER(LISTAGG(S.DESCRIZIONE, ',') WITHIN GROUP(ORDER BY S.DESCRIZIONE)) AS SITI,"
                + "UPPER(LISTAGG(TB.CODICE, ',') WITHIN GROUP(ORDER BY TB.CODICE)) AS BUSINESSUNIT "
                + "FROM TB_UTENTI U, TB_RUOLI_UTENTE TRU, TB_RUOLI R, TB_SITI S, TB_SITI_UTENTE TSU, TB_BU_UTENTE TBU, TB_BU TB "
                + "WHERE U.ID = TRU.UTENTEID "
                + "AND TRU.RUOLOID = R.ID "
                + "AND U.ID = TSU.UTENTEID "
                + "AND TSU.SITOID = S.ID "
                + "AND U.ID = TBU.UTENTEID "
                + "AND TB.ID = TBU.BUID "
                + "GROUP BY U.MATRICOLA, U.NOME, U.COGNOME ";

        String queryUtentiEsterni = "SELECT USER_NAME AS USERID, NOME, COGNOME, "
                + "LISTAGG(BU, ',') WITHIN GROUP(ORDER BY BU) AS BUSINESSUNIT, "
                + "LISTAGG(SITO, ',') WITHIN GROUP(ORDER BY SITO) AS SITI, "
                + "LISTAGG(RUOLI, ',') WITHIN GROUP(ORDER BY RUOLI) AS PROFILO "
                + "FROM TB_UTENTI_AIE_CONTRATTI "
                + "WHERE CANCELLATO = 0 "
                + "GROUP BY USER_NAME, NOME, COGNOME ";

        List<QueryRow> utenti = new LinkedList<QueryRow>();

        DatabaseManager database = DatabaseManagerFactory.get(props);
        database.connect();
        try {
            output.println(header);
            try {
                utenti.addAll(database.query(queryUtentiInterni, new Object[] {}));
                utenti.addAll(database.query(queryUtentiEsterni, new Object[] {}));

                Collections.sort(utenti, new Comparator<QueryRow>() {

                    @Override
                    public int compare(QueryRow o1, QueryRow o2) {
                        String matricola1 = (String) o1.get("USERID");
                        String matricola2 = (String) o2.get("USERID");
                        return compareStrings(matricola1, matricola2, true);
                    }
                });

                //by Ros 21/05/2019->
                Map<String,String[]> userMap = new HashMap<String,String[]>();
                //<-
                for (QueryRow utente : utenti) {
                    String matricola = (String) utente.get("USERID");

                    String profilo = utente.get("PROFILO") == null ? "" : utente.get("PROFILO").toString();

                    String nome = utente.get("NOME") == null ? "" : utente.get("NOME").toString();
                    String cognome = utente.get("COGNOME") == null ? "" : utente.get("COGNOME").toString();

                    String businessUnit = utente.get("BUSINESSUNIT") == null ? "" : utente.get("BUSINESSUNIT").toString();
                    String siti = utente.get("SITI") == null ? "" : utente.get("SITI").toString();

                    String mail = "";
                    //aggiunta query ldap per mail solo per utenti interni
                    if(!matricola.contains("@")){
                        PdlUser user = ldap.getPdlUser(matricola);
                        if(user!=null){
                            mail=user.getMail()==null?"":user.getMail();
                        }else{
                            System.err.println("user "+ matricola + " not found on ldap");
                        }

                    }else{
                        mail=matricola;
                    }


                    if (matricola != null) {
                        //by Ros 21/05/2019 ->
                        /*
                                       String input = matricola + ";";
                                       input += asString(Arrays.asList(profilo.split(",")), ", ") + ";";
                                       input += nome + ";";
                                       input += cognome + ";";
                                       input += asString(replaceBusinessUnits(Arrays.asList(businessUnit.split(","))), ", ") + ";";
                                       input += cleanAndList(siti) + ";";
                                       input += mail + ";";
                                       output.println(input);
                         */
                        if (!userMap.containsKey(mail))
                            userMap.put(mail, new String[] {matricola, profilo, businessUnit});
                        else{
                            if (!mail.equals(matricola))
                                userMap.get(mail)[0] = new String(matricola);
                            userMap.get(mail)[1] += "," + profilo;
                            userMap.get(mail)[2] += "," + businessUnit;
                        }
                        // <-
                    }
                }
                //by Ros 21/05/2019 ->
                for (Map.Entry<String, String[]> pair : userMap.entrySet()){
                    String input = pair.getValue()[0] + ";";
                    input += asString(Arrays.asList(pair.getValue()[1].split(",")), ", ") + ";";
                    input += (pair.getValue()[0].contains("@"))
                            ? asString(replaceBusinessUnits(Arrays.asList(pair.getValue()[2].split(","))), ", ") + ";"
                                    : "" ;
                    output.println(input);
                }
                //<-
            } finally {
                output.flush();
                output.close();
            }
        } finally {
            database.disconnect();
        }
    }

    private static List<String> replaceBusinessUnits(List<String> asList) {
        List<String> replaced = new LinkedList<String>();
        if (asList != null) {
            for (String bu : asList) {

                if (bu == null || bu.trim().isEmpty()) {
                    replaced.add("");
                } else {
                    if (bu.trim().equalsIgnoreCase("R&M")) {
                        replaced.add("R&M and Chemicals");
                    } else if (bu.trim().equalsIgnoreCase("PW")) {
                        replaced.add("EniPower S.p.A.");
                    } else if (bu.trim().equalsIgnoreCase("VS")) {
                        replaced.add("Versalis");
                    } else {
                        replaced.add("Upstream");
                    }
                }

            }
        }
        return replaced;
    }

    private static String cleanAndList(String siti) {
        List<String> result = new LinkedList<String>();
        String[] asArray = siti.split(",");
        for (String a : asArray) {
            Siti s = Siti.fromString(a);
            if (s != null) {
                result.add(s.getFullName());
            } else {
                System.err.println(a + " was not recognized");
                result.add(a);
            }
        }

        return asString(result, ", ");
    }

    /**
     * Converte in stringa una Collection,
     * utilizzando la stringa passata come secondo parametro
     * per separare i vari elementi.
     * @param removeDuplicates se bisogna rimuovere eventuali duplicati.
     */
    public static <T> String asString(Collection<T> collection, String separator, boolean removeDuplicates) {
        String result = "";
        if (collection != null) {
            Collection<T> coll;
            if (removeDuplicates) {
                coll = new HashSet<T>(
                        collection);
            } else {
                coll = new LinkedList<T>(
                        collection);
            }

            Iterator<T> iterator = coll.iterator();
            while (iterator.hasNext()) {
                T next = iterator.next();
                result += separator + (next == null ? "" : next);
            }
        }

        return result.replaceFirst(separator, "");
    }

    /**
     * Converte in stringa (eliminando i duplicati) una Collection,
     * utilizzando la stringa passata come secondo parametro
     * per separare i vari elementi.
     */
    public static <T> String asString(Collection<T> collection, String separator) {
        return asString(collection, separator, true);
    }

    public static <T> String asString(T[] collection, String separator) {
        return asString(Arrays.asList(collection), separator);
    }

    public static int compareStrings(final String str1, final String str2, final boolean nullIsLess) {
        if (str1 == str2) {
            return 0;
        }
        if (str1 == null) {
            return nullIsLess ? -1 : 1;
        }
        if (str2 == null) {
            return nullIsLess ? 1 : -1;
        }
        return str1.compareTo(str2);
    }
}