package brn.distsim.ormapper.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectStreamClass;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

public class DbBinaryLoader {
  
  public class ObjectInputStream2 extends ObjectInputStream {

    /* (non-Javadoc)
     * @see java.io.ObjectInputStream#resolveClass(java.io.ObjectStreamClass)
     */
    @Override
    protected Class<?> resolveClass(ObjectStreamClass desc) throws IOException,
        ClassNotFoundException {
      String name = desc.getName();
      try {
        if (null != appClassLoader)
          return Class.forName(name, false, appClassLoader);
      } 
      catch (ClassNotFoundException e) {
        return super.resolveClass(desc);
      }
      return super.resolveClass(desc);
    }

    protected ObjectInputStream2() throws IOException, SecurityException {
      super();
      // TODO Auto-generated constructor stub
    }

    public ObjectInputStream2(InputStream in) throws IOException {
      super(in);
      // TODO Auto-generated constructor stub
    }

    
  }

  public static class DbEntry {
    public String path;
    public int type;
    public int id;
  }

  protected Connection connection;

  private PreparedStatement loadQuery;
  private PreparedStatement loadQuery2;

  protected int counter;
  
  private ClassLoader appClassLoader;

  public DbBinaryLoader(String dbUrl, String dbUser, String dbPasswd,
      ClassLoader appClassLoader) throws ClassNotFoundException, SQLException {
    this.appClassLoader = appClassLoader;
    setProperties(dbUrl, dbUser, dbPasswd);
    initialize();
  }

  public DbBinaryLoader(String dbUrl, String dbUser, String dbPasswd) throws ClassNotFoundException, SQLException {
    this(dbUrl, dbUser, dbPasswd, null);
  }

  private void setProperties(String dbUrl, String dbUser, String dbPasswd)
  throws SQLException, ClassNotFoundException {
    Properties hbProps = new Properties();

    hbProps.put("driver_class", "com.mysql.jdbc.Driver");
    hbProps.put("user", dbUser);
    hbProps.put("password", dbPasswd);
    hbProps.put("batch_size", "50");
    hbProps.put("use_streams_for_binary", "true");
    hbProps.put("isolation", "1");
    hbProps.put("autocommit", "true");
    hbProps.put("batch_versioned_data", "true");

    hbProps.put("cachePrepStmts", "true");
    hbProps.put("cacheCallableStatements", "true");
    hbProps.put("cacheServerConfiguration", "true");
    hbProps.put("useLocalSessionState", "true");
    hbProps.put("elideSetAutoCommits", "true");
    hbProps.put("alwaysSendSetIsolation", "false");
    hbProps.put("enableQueryTimeouts", "true");

    hbProps.put("allowMultiQueries", "true");
    hbProps.put("cacheResultSetMetadata", "true");
    hbProps.put("callableStatementCacheSize", "200");
    hbProps.put("detectServerPreparedStmts", "true");
    hbProps.put("metadataCacheSize", "100");
    hbProps.put("preparedStatementCacheSize", "100");
    hbProps.put("preparedStatementCacheSqlLimit", "256");

    hbProps.put("reportMetricsIntervalMillis", "30000");
    hbProps.put("gatherPerformanceMetrics", "true");

    hbProps.put("emulateUnsupportedPstmts", "false");
    hbProps.put("rewriteBatchedStatements", "true");

    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(dbUrl, hbProps);
  }

  private void initialize() throws SQLException {
    connection.setAutoCommit(true);
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    loadQuery = connection.prepareStatement("SELECT content FROM binarystore "+
        " WHERE simulationId=? and contributionId=?;");
    loadQuery2 = connection.prepareStatement("SELECT content FROM binarystore "+
        " WHERE simulationId=? and path=?;");
    counter = 0;
  }

  public synchronized Object load(int simulationId, int id)
      throws SQLException, IOException, ClassNotFoundException {
    loadQuery.setInt(1, simulationId);
    loadQuery.setInt(2, id);
    loadQuery.execute();

    ResultSet resultSet = loadQuery.getResultSet();
    if (!resultSet.next())
      return null;

    ObjectInputStream2 objIn = new ObjectInputStream2(resultSet.getBinaryStream(1));
    Object obj = objIn.readObject();
    objIn.close();

    return obj;
  }

  public synchronized Object load(int simulationId, String path)
      throws SQLException, IOException, ClassNotFoundException {

    loadQuery2.setInt(1, simulationId);
    loadQuery2.setString(2, path);
    loadQuery2.execute();

    ResultSet resultSet = loadQuery2.getResultSet();
    if (null == resultSet || !resultSet.next())
      return null;

    ObjectInputStream2 objIn = new ObjectInputStream2(resultSet.getBinaryStream(1));
    Object obj = objIn.readObject();
    objIn.close();

    return obj;
  }

  public synchronized List /* DbEntry */ loadEntries(int simulationId)
      throws SQLException, IOException, ClassNotFoundException {
    List ret = new LinkedList();

    Statement stmt = connection.createStatement();
    stmt.execute("SELECT contributionId, type, path FROM binarystore "
        + " WHERE simulationId=" + simulationId);

    ResultSet resultSet = stmt.getResultSet();
    while (resultSet.next()) {
      DbEntry entry = new DbEntry();

      entry.id = resultSet.getInt(1);
      entry.type = resultSet.getInt(2);
      entry.path = resultSet.getString(3);

      ret.add(entry);
    }
    stmt.close();

    return ret;
  }

  /**
   * Loads all available simulatin ids.
   * @return all available simulatin ids.
   * @throws SQLException
   */
  public synchronized List /* Integer */ loadSimulationIds() throws SQLException {
    List ret = new LinkedList();

    Statement stmt = connection.createStatement();
    stmt.execute("SELECT simulationId FROM progress"
        + " WHERE state='FINISHED'");

    ResultSet resultSet = stmt.getResultSet();
    while (resultSet.next())
      ret.add(Integer.valueOf(resultSet.getInt(1)));

    stmt.close();

    return ret;
  }

  /**
   * Loads all available simulatin ids for the specified study.
   * @return all available simulatin ids.
   * @throws SQLException
   */
  public synchronized List /* Integer */ loadSimulationIds(
      String studyName, String studyVersion) throws SQLException {
    List ret = new LinkedList();

    Statement stmt = connection.createStatement();
    stmt.execute("SELECT progress.simulationId FROM progress " +
      "WHERE progress.state='FINISHED' and progress.simulationId in " +
      "(SELECT simulations.id FROM simulations WHERE simulations.groupId in " +
      "(SELECT groups.id FROM groups WHERE groups.studyId in " +
      "(SELECT studies.id FROM studies " +
      "WHERE studies.name = '"+studyName+"' and studies.version = '"+studyVersion+"')))");
    ResultSet resultSet = stmt.getResultSet();
    while (resultSet.next())
      ret.add(Integer.valueOf(resultSet.getInt(1)));

    stmt.close();

    return ret;
  }

  public void close() {
    if (null != connection) {
      try {
        loadQuery.close();
        loadQuery2.close();
        connection.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      connection = null;
    }
  }

  public void finalize() throws Throwable {
    close();
  }
}
