/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.cloudera.sqoop.testutil;

import java.util.Arrays;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hsqldb.Server;

import com.cloudera.sqoop.SqoopOptions;
import com.cloudera.sqoop.manager.ConnManager;
import com.cloudera.sqoop.manager.HsqldbManager;

/**
 * Create a simple hsqldb server and schema to use for testing.
 */
public class HsqldbTestServer {
  public static final Log LOG =
    LogFactory.getLog(HsqldbTestServer.class.getName());

  // singleton server instance.
  private static Server server;

  // if -Dhsql.server.host hasn't been set to something like
  // hsql://localhost.localdomain/ a defaul in-mem DB will be used
  private static final String IN_MEM = "mem:";
  public static String getServerHost() {
    String host = System.getProperty("hsql.server.host", IN_MEM);
    if (!host.endsWith("/")) { host += "/"; }
    return host;
  }

  private static boolean inMemoryDB = IN_MEM.equals(getServerHost());

  // Database name can be altered too
  private static final String DATABASE_NAME =
      System.getProperty("hsql.database.name",  "db1");

  // hsqldb always capitalizes table and column names
  private static final String DUMMY_TABLE_NAME = "TWOINTTABLE";
  private static final String [] TWO_INT_TABLE_FIELDS = {
    "INTFIELD1",
    "INTFIELD2",
  };

  private static final String EMPLOYEE_TABLE_NAME = "EMPLOYEES";

  private static final String DB_URL = "jdbc:hsqldb:"
      + getServerHost() + DATABASE_NAME;
  private static final String DRIVER_CLASS = "org.hsqldb.jdbcDriver";

  // all user-created HSQLDB tables are in the "PUBLIC" schema when connected
  // to a database.
  private static final String HSQLDB_SCHEMA_NAME = "PUBLIC";

  public static String getSchemaName() {
    return HSQLDB_SCHEMA_NAME;
  }

  public static String [] getFieldNames() {
    return Arrays.copyOf(TWO_INT_TABLE_FIELDS, TWO_INT_TABLE_FIELDS.length);
  }

  public static String getUrl() {
    return DB_URL;
  }

  public static String getTableName() {
    return DUMMY_TABLE_NAME;
  }

  public static String getDatabaseName() {
    return DATABASE_NAME;
  }

  /**
   * start the server.
   */
  public void start() {
    if (null == server) {
      LOG.info("Starting new hsqldb server; database=" + DATABASE_NAME);
      String tmpDir = System.getProperty("test.build.data", "/tmp/");
      String dbLocation = tmpDir + "/sqoop/testdb.file";
      if (inMemoryDB) {dbLocation = IN_MEM; }
      server = new Server();

      server.setDatabaseName(0, DATABASE_NAME);
      server.putPropertiesFromString("database.0=" + dbLocation
          + ";no_system_exit=true");
      server.start();
    }
  }

  public void stop() {
    if (null == server) {
      return;
    }
    server.stop();
    server = null;
  }

  public Connection getConnection() throws SQLException {
    return getConnection(null, null);
  }

  public Connection getConnection(String user, String password) throws SQLException {
    try {
      Class.forName(DRIVER_CLASS);
    } catch (ClassNotFoundException cnfe) {
      LOG.error("Could not get connection; driver class not found: "
          + DRIVER_CLASS);
      return null;
    }

    Connection connection = DriverManager.getConnection(DB_URL, user, password);
    connection.setAutoCommit(false);
    return connection;
  }

  /**
   * Returns database URL for the server instance.
   * @return String representation of DB_URL
   */
  public static String getDbUrl() {
    return DB_URL;
  }

  /**
   * Create a table.
   */
  public void createSchema() throws SQLException {

    Connection connection = null;
    Statement st = null;

    try {
      connection = getConnection();

      st = connection.createStatement();
      st.executeUpdate("DROP TABLE \"" + DUMMY_TABLE_NAME + "\" IF EXISTS");
      st.executeUpdate("CREATE TABLE \"" + DUMMY_TABLE_NAME + "\"(intField1 INT, intField2 INT)");

      connection.commit();
    } finally {
      if (null != st) {
        st.close();
      }

      if (null != connection) {
        connection.close();
      }
    }
  }

  /**
   * @return the sum of the integers in the first column of TWOINTTABLE.
   */
  public static int getFirstColSum() {
    return 1 + 3 + 5 + 7;
  }

  /**
   * Fill the table with some data.
   */
  public void populateData() throws SQLException {

    Connection connection = null;
    Statement st = null;

    try {
      connection = getConnection();

      st = connection.createStatement();
      st.executeUpdate("INSERT INTO \"" + DUMMY_TABLE_NAME + "\" VALUES(1, 8)");
      st.executeUpdate("INSERT INTO \"" + DUMMY_TABLE_NAME + "\" VALUES(3, 6)");
      st.executeUpdate("INSERT INTO \"" + DUMMY_TABLE_NAME + "\" VALUES(5, 4)");
      st.executeUpdate("INSERT INTO \"" + DUMMY_TABLE_NAME + "\" VALUES(7, 2)");

      connection.commit();
    } finally {
      if (null != st) {
        st.close();
      }

      if (null != connection) {
        connection.close();
      }
    }
  }

  public void createEmployeeDemo() throws SQLException, ClassNotFoundException {
    Class.forName(DRIVER_CLASS);

    Connection connection = null;
    Statement st = null;

    try {
      connection = getConnection();

      st = connection.createStatement();
      st.executeUpdate("DROP TABLE \"" + EMPLOYEE_TABLE_NAME + "\" IF EXISTS");
      st.executeUpdate("CREATE TABLE \"" + EMPLOYEE_TABLE_NAME + "\"(emp_id INT NOT NULL PRIMARY KEY, name VARCHAR(64))");

      st.executeUpdate("INSERT INTO \"" + EMPLOYEE_TABLE_NAME + "\" VALUES(1, 'Aaron')");
      st.executeUpdate("INSERT INTO \"" + EMPLOYEE_TABLE_NAME + "\" VALUES(2, 'Joe')");
      st.executeUpdate("INSERT INTO \"" + EMPLOYEE_TABLE_NAME + "\" VALUES(3, 'Jim')");
      st.executeUpdate("INSERT INTO \"" + EMPLOYEE_TABLE_NAME + "\" VALUES(4, 'Lisa')");

      connection.commit();
    } finally {
      if (null != st) {
        st.close();
      }

      if (null != connection) {
        connection.close();
      }
    }
  }

  /**
   * Delete any existing tables.
   */
  public void dropExistingSchema() throws SQLException {
    ConnManager mgr = getManager();
    String [] tables = mgr.listTables();
    if (null != tables) {
      Connection conn = mgr.getConnection();
      for (String table : tables) {
        Statement s = conn.createStatement();
        try {
          s.executeUpdate("DROP TABLE \"" + table + "\"");
          conn.commit();
        } finally {
          s.close();
        }
      }
    }
  }

  /**
   * Creates an hsqldb server, fills it with tables and data.
   */
  public void resetServer() throws ClassNotFoundException, SQLException {
    start();
    dropExistingSchema();
    createSchema();
    populateData();
  }

  public SqoopOptions getSqoopOptions() {
    return new SqoopOptions(HsqldbTestServer.getUrl(),
        HsqldbTestServer.getTableName());
  }

  public ConnManager getManager() {
    return new HsqldbManager(getSqoopOptions());
  }

  public void createNewUser(String username, String password) throws SQLException {
    try (Connection connection = getConnection(); Statement statement = connection.createStatement()) {
      statement.executeUpdate(String.format("CREATE USER %s PASSWORD %s ADMIN", username, password));
    }
  }

  public void changePasswordForUser(String username, String newPassword) throws SQLException {
    try (Connection connection = getConnection(); Statement statement = connection.createStatement()) {
      statement.executeUpdate(String.format("ALTER USER %s SET PASSWORD %s", username, newPassword));
    }
  }

}