package echoquery.utils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.lang3.StringEscapeUtils;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.amazon.speech.speechlet.Session;
import com.google.common.base.Joiner;

import echoquery.SingletonConnections;
import echoquery.querier.ResultTable;

public class VisualizationUtil {
  private static final Logger log =
      LoggerFactory.getLogger(VisualizationUtil.class);
  private static Connection conn = SingletonConnections.getStateInstance();

  public static void updateDisplayText(String message, Session session) {
    try {
      makeSureSessionExistsInDB(session.getUser().getUserId());
      Statement statement = conn.createStatement();
      statement.executeUpdate("update sessions set display='" +
          StringEscapeUtils.escapeJava(message) + "' where id='" +
          cleanId(session.getUser().getUserId()) + "';");
    } catch (SQLException e) {
      log.error(e.getMessage());
    }
  }

  public static void updateResultData(JSONObject data, Session session) {
    try {
      makeSureSessionExistsInDB(session.getUser().getUserId());
      Statement statement = conn.createStatement();
      statement.executeUpdate("update sessions set result='" +
          StringEscapeUtils.escapeJava(data.toString()) + "'where id='" +
          cleanId(session.getUser().getUserId()) + "';");
    } catch (SQLException e) {
      log.error(e.getMessage());
    }
  }

  public static void updatePlotColumns(List<String> plotCols, Session session) {
    try {
      makeSureSessionExistsInDB(session.getUser().getUserId());
      Statement statement = conn.createStatement();
      statement.executeUpdate("update sessions set vis='"
          + StringEscapeUtils.escapeJava(Joiner.on(',').join(plotCols))
          + "'where id='" + cleanId(session.getUser().getUserId()) + "';");
    } catch (SQLException e) {
      log.error(e.getMessage());
    }
  }

  public static JSONObject getUserData(String userId) {
    try {
      makeSureSessionExistsInDB(userId);
      Statement statement = conn.createStatement();
      return new ResultTable(
          statement.executeQuery(
              "select display,result,vis from sessions where id='"
                  + cleanId(userId) + "';")).json();
    } catch (SQLException e) {
      log.error(e.getMessage());
      JSONObject obj = new JSONObject();
      obj.put("display",
          "There was an error retrieving the display text from the database");
      return obj;
    }
  }

  // Since id is a unique column in the sessions table, we don't need to worry
  // about duplicate rows being inserted
  public static void makeSureSessionExistsInDB(String userId) {
      try {
        Statement statement = conn.createStatement();
        statement.executeUpdate("insert into sessions values ('" +
            cleanId(userId) + "', 'EchoQuery. What do you want?');");
      } catch (SQLException e) {
        // silently ignore - we already have this value in the db
      }
  }

  // removes the text at the beginning of the id
  public static String cleanId(String userId) {
    String[] splitted = userId.split("\\.");
    if (splitted.length == 3) {
      return splitted[2];
    } else {
      return splitted[0];
    }
  }
}