package eu.dnetlib.data.claims.handler;

import com.google.gson.*;
import eu.dnetlib.data.claims.entity.Metrics;
import eu.dnetlib.data.claims.sql.SQLStoreException;
import eu.dnetlib.data.claims.sql.SqlDAO;
import eu.dnetlib.data.claims.utils.QueryGenerator;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.postgresql.util.PGobject;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@EnableScheduling
@Service
public class MetricsHandler {
    SqlDAO sqlDAO = null;
    QueryGenerator queryGenerator = null;

    private static final Logger logger = LogManager.getLogger(MetricsHandler.class);

    public int countEUProjects() throws SQLStoreException, SQLException {
        logger.info("Counting EU projects...");
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateSelectNumOfEUProjectsQuery();
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        int res = fetchCountByResultSet(rs);

        return res;
    }

    public int countCountriesFromClaimedBy() throws SQLStoreException, SQLException {
        logger.info("Counting countries for people who claimed...");
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateSelectNumOfCountriesQuery();
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        int res = fetchCountByResultSet(rs);

        return res;
    }

    public int countUniqueResearchResults() throws SQLStoreException, SQLException {
        logger.info("Counting unique research results claimed...");
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateSelectNumOfUniqueResearchResultsQuery();
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        int res = fetchCountByResultSet(rs);

        return res;
    }

    // every day at midnight
    @Scheduled(cron = "0 0 0 * * ?")
    // every 5 mins for testing
//    @Scheduled(cron = "0 0/5 * * * *")
    public void calculateMetrics() throws SQLStoreException, Exception {
        logger.info("Calculating metrics...");
        String id = null;
        String query = queryGenerator.generateSelectAndBuildMetricsQuery();
        ResultSet rs1 = sqlDAO.executePreparedQuery(query);
        Metrics calculatedMetrics = fetchMetricsByResultSet(rs1, false);

        ArrayList<Object> params = new ArrayList<>();
        query = queryGenerator.generateUpdateMetricsQuery(calculatedMetrics, params);
        sqlDAO.executeUpdateQuery(query, params);
    }

    public Metrics getMetrics() throws SQLStoreException, SQLException {
        logger.info("Fetching metrics...");
        String query = queryGenerator.generateSelectMetricsQuery();
        ResultSet rs = sqlDAO.executePreparedQuery(query);
        Metrics res = fetchMetricsByResultSet(rs, true);
        return res;
    }

    private Metrics fetchMetricsByResultSet(ResultSet rs, boolean fromMetricsTable) throws SQLException {
        logger.debug("fetchMetricsByResultSet (from table="+fromMetricsTable+")");
        Metrics metrics = null;
        boolean hasNext = false;
        List<JsonObject> metrics_per_dashboard = new ArrayList<>();

        if(fromMetricsTable) {
            if(rs.next()) {
                metrics = new Metrics();
                PGobject pg = (PGobject) rs.getObject("metrics_per_dashboard");
                String jsonText = pg.getValue();

                Gson gson = new Gson();
                JsonArray myList2 = gson.fromJson(jsonText, JsonArray.class);

                List<JsonObject> listObj = new ArrayList<>();

                for(JsonElement jsonElement : myList2) {
                    listObj.add(jsonElement.getAsJsonObject());
                }

                metrics.setMetrics_per_dashboard(listObj);
                metrics.setDate(rs.getTimestamp("date"));
            }
        } else {
            while (rs.next()) {
                String[] dashboard_elements = ((String) rs.getString("claimed_in_dashboard")).split("_", 2);

                JsonObject dashboardMetrics = new JsonObject();
                dashboardMetrics.add("environment", new JsonPrimitive(dashboard_elements[0]));
                dashboardMetrics.add("dashboard", new JsonPrimitive(dashboard_elements[1]));
                dashboardMetrics.add("claims", new JsonPrimitive(rs.getInt("total_claims")));
                dashboardMetrics.add("users", new JsonPrimitive(rs.getInt("total_users")));
                dashboardMetrics.add("projects", new JsonPrimitive(rs.getInt("projects")));
                dashboardMetrics.add("eu_projects", new JsonPrimitive(rs.getInt("eu_projects")));
                dashboardMetrics.add("countries", new JsonPrimitive(rs.getInt("countries")));
                dashboardMetrics.add("research_results", new JsonPrimitive(rs.getInt("research_results")));

                metrics_per_dashboard.add(dashboardMetrics);

                hasNext = true;
            }

            if (hasNext) {
                metrics = new Metrics();
                metrics.setMetrics_per_dashboard(metrics_per_dashboard);
                metrics.setDate(new Date());
                metrics.setId("current");
            }
        }

        return metrics;
    }

    private int fetchCountByResultSet(ResultSet rs) throws SQLException {
        int count = 0;
        if(rs.next()) {
            count = rs.getInt("count");
        }
        return count;
    }

    public SqlDAO getSqlDAO() {
        return sqlDAO;
    }

    public void setSqlDAO(SqlDAO sqlDAO) {
        this.sqlDAO = sqlDAO;
    }

    public QueryGenerator getQueryGenerator() {
        return queryGenerator;
    }

    public void setQueryGenerator(QueryGenerator queryGenerator) {
        this.queryGenerator = queryGenerator;
    }
}