package com.rotoplastyc.util.reports;

import com.google.gson.Gson;
import static com.rotoplastyc.Maths.StandardDeviation;
import static com.rotoplastyc.Maths.mean;
import com.rotoplastyc.STAT;
import com.rotoplastyc.modulos.paginas.Erro;
import com.rotoplastyc.util.JSONResponse;
import com.rotoplastyc.util.LocalDate;
import com.rotoplastyc.util.Logable;
import com.rotoplastyc.util.SQL.Column;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.sql.Date;
import org.json.JSONObject;

public abstract class AbstractReport extends Logable implements ParametrizableReport {

    private static int COUNT = 0;

    private boolean state;
    protected final ReportSpec spec;
    private final String name;
    private final String whereTo;
    private int values[][];
    private Date dtini;
    private Date dtfin;

    public AbstractReport(ReportSpec spec, String name, String whereTo) {
        this.spec = spec;
        this.name = name;
        this.whereTo = whereTo;
        state = false;
    }

    private void setValues(int ids[][]) {
        if (ids.length != spec.params.length) throw new IllegalArgumentException("number of values differ from number of fields");
        values = ids;
        state = true;
    }

    private void setValues(JSONObject obj) throws ParseException {

        dtini = new Date(LocalDate.fromSQLSimple(obj.getString("dtini")).getTime());
        dtfin = new Date(LocalDate.fromSQLSimple(obj.getString("dtfin")).getTime());

        int arr[][] = new Gson().fromJson(obj.getString("values"), int[][].class);

        setValues(arr);
    }

    @Override
    public String parameterScreen(Connection connection) {
        try {
            StringBuilder out = new StringBuilder();

            out
                    .append("<div class='repscreen' id='rep").append(COUNT).append("'>")
                    .append("<h1>Relatório de ").append(name).append("</h1>")
                    .append("<div>")
                    .append("<label for='dtini'>Data inicial:</label><input type='date' id='rep_dtini'>")
                    .append("<label for='dtini'>Data final:</label><input type='date' id='rep_dtfin'>")
                    .append("</div>");

            for (int i = 0; i < spec.params.length; i++) {
                Column column = spec.params[i];
                ResultSet result = column.getGenericSelect(connection);

                String clName = column.getUncontextualizedLabel();
                out
                        .append("<div>")
                        .append("<label for='rep_s").append(i).append("'>").append(clName).append("</label><br>")
                        .append("<select id='rep_s").append(i).append("' multiple><option value='-1' selected>TODOS</option>");

                while (result.next()) {
                    out
                            .append("<option value='").append(result.getString(1)).append("'>")
                            .append(result.getString(2))
                            .append("</option>");
                }

                out
                        .append("</select>")
                        .append("</div>");
            }

            out
                    .append("<button class='rep_screen_btn'>GERAR</button>")
                    .append("<button style='visibility:hidden'>CSV</button>")
                    .append("<script>$('#rep").append(COUNT++).append("').report('").append(whereTo).append("')</script>")
                    .append("</div>");

            JSONResponse response = new JSONResponse(JSONResponse.RELOAD_ONLY);
            response.html("popup", STAT.popup(out));
            return response.toString();
        } catch (SQLException ex) {
            return new Erro(ex).getPageHTML();
        }
    }

    @Override
    public String processRequest(JSONObject obj, Connection connection) {
        switch (obj.getInt("operation")) {
            case 1:
                String out;

                try {
                    setValues(obj);
                    out = getReport(connection);
                } catch (ParseException | SQLException ex) {
                    out = new Erro(ex).getPageHTML();
                }

                JSONResponse response = new JSONResponse(JSONResponse.RELOAD_ONLY);
                response.html("popup", STAT.popup(out));
                return response.toString();
            default: return new JSONResponse(JSONResponse.ERROR, "Operação inválida!").toString();
        }
    }

    public ResultSet getResultSet(Connection connection) throws SQLException {
        StringBuilder SQL = new StringBuilder(spec.getQuery());

        boolean todos[] = new boolean[values.length+1];
        todos[values.length] = false; // se tem where ou não
        for (int i = 0; i < values.length; i++) {
            todos[i] = values[i].length < 0;
            for (int j = 0; j < values[i].length; j++)
                if (values[i][j] < 0) {
                    todos[i] = true;
                    break;
                }

        } for (int i = 0; i < values.length; i++)
            if (!todos[i]) {
                todos[values.length] = true; // tem where
                break;
            }

        SQL
                .append(" WHERE ")
                .append(spec.dates.getX().getFullName())
                .append(">=? AND ")
                .append(spec.dates.getY().getFullName())
                .append("<=?");

        if (spec.restrictions.length > 0) {
            for (Restriction<Object> restriction : spec.restrictions) {
                SQL
                        .append(" AND ")
                        .append(restriction.getX().getFullName())
                        .append(" IN (");

                for (Object value : restriction.getY())
                    SQL.append("?,");
            }
            STAT.removeLastChar(SQL).append(")");
        }

        if (todos[values.length]) { // se tem where
            SQL.append(" AND ");
            for (int i = 0; i < values.length; i++) {
                if (todos[i]) continue;
                SQL
                        .append(spec.params[i].getTable().getIdColumnName())
                        .append(" IN (");

                for (int j = 0; j < values[i].length; j++)
                    SQL.append("?, ");

                SQL.setLength(SQL.length()-2);
                SQL.append(") AND ");
            }
            SQL.setLength(SQL.length()-5);
        }

        SQL
                .append(" ORDER BY ")
                .append(spec.orderBy.getFullName());

        PreparedStatement statement = connection.prepareStatement(SQL.toString());

        int index = 1;

        statement.setDate(index++, dtini);
        statement.setDate(index++, dtfin);

        if (spec.restrictions.length > 0)
            for (Restriction<Object> restriction : spec.restrictions)
                for (Object value : restriction.getY())
                    statement.setObject(index++, value);

        if (todos[values.length])
            for (int i = 0; i < values.length; i++)
                if (!todos[i])
                    for (int j = 0; j < values[i].length; j++)
                        statement.setInt(index++, values[i][j]);

        debug(statement);
        return statement.executeQuery();
    }

    @Override
    public String getReport(Connection connection) throws SQLException {
        ResultSet result = getResultSet(connection);
        debug(result);
        result.last();
        int size = result.getRow();
        result.beforeFirst();

        String table[][] = new String[size + (!spec.subtotals.isEmpty() ? 1 : 0)][spec.columns.length];
        boolean haveSubtotal[] = new boolean[spec.columns.length];
        float subTotal[] = new float[spec.columns.length];

        for (int i = 0; i < haveSubtotal.length; i++) {
            haveSubtotal[i] = (spec.subtotals.contains(spec.columns[i]));
            subTotal[i] = 0;
        }

        for (int i = 0; i < size; i++) {
            result.next();
            for (int j = 0, k = 1; j < table[i].length; j++, k++) {
                table[i][j] = result.getString(k);

                if (haveSubtotal[j])
                    subTotal[j] += result.getFloat(k);
            }
        }

        if (!spec.subtotals.isEmpty())
            for (int i = 0; i < haveSubtotal.length; i++)
                if (haveSubtotal[i])
                    table[table.length-1][i] = Float.toString(subTotal[i]);
                else
                    table[table.length-1][i] = "";
        
        for (int i = 0; i<table.length; i++)
            for (int j = 0; j < table[i].length; j++)
                if (spec.formatters.containsKey(spec.columns[j]))
                    try {
                        table[i][j] = spec.formatters.get(spec.columns[j]).format(table[i][j]);
                    } catch (ParseException ex) {
                        if (i == size)
                            table[i][j] = "";
                        else {
                            table[i][j] = "ERRO DE PARSING";
                            log(ex);
                        }
                    }

        return getReport(table);
    }

    public String getReport(String table[][]) {
        if (!state) throw new IllegalStateException("parameters not defined yet!");
        StringBuilder out = new StringBuilder();

        out
                .append("<div class='rep_page'>")
                .append("<h1>Relatório de ").append(name).append("</h1>")
                .append("<table class='print'><thead><tr>");

        for (Column column: spec.columns)
            out.append("<th>").append(column.getContextualizedLabel()).append("</th>");

        out.append("</thead><tbody>");

        for (String[] tr : table) {
            out.append("<tr>");
            for (String td : tr)
                out.append("<td>").append(td).append("</td>");
            out.append("</tr>");
        }

        double
                lengths[][] = new double[spec.columns.length][table.length], //coluna/linha
                weights[] = new double[spec.columns.length],//de cada coluna
                total = 0;

        for (int i = 0; i < lengths.length; i++)
            for (int j = 0; j < lengths[i].length; j++)
                lengths[i][j] = table[j][i].length();// a tabela vem linha/coluna

        for (int i = 0; i < weights.length; i++) {
            weights[i] = mean(lengths[i]) + StandardDeviation(lengths[i]);
            total += weights[i];
        }

        out.append("</tbody></table><style scoped>");

        for (int i = 0, j = 1; i < weights.length; i++, j++)
            out
                    .append("tr>:nth-child(")
                    .append(j)
                    .append("){width:")
                    .append((weights[i]/total)*100)
                    .append("%;")
                    .append(spec.css.getOrDefault(spec.columns[i], ""))
                    .append("}");

        if (!spec.subtotals.isEmpty())
            out.append("tr:last-child{font-weight:bold;border}");

        out.append("</style></div>");

        return out.toString();
    }

}