package com.atlassian.plugin.refimpl.db;
   
   import com.atlassian.refapp.api.ConnectionProvider;
   import org.h2.tools.Server;
   import org.slf4j.Logger;
   import org.slf4j.LoggerFactory;
   import org.vibur.dbcp.ViburDBCPDataSource;
   
   import javax.sql.DataSource;
  import java.net.InetAddress;
  import java.sql.Connection;
  import java.sql.SQLException;
  import java.util.Optional;
  
  public class ConnectionProviderImpl implements ConnectionProvider {
      private static final Logger log = LoggerFactory.getLogger(ConnectionProviderImpl.class);
  
      private static final String DB_DIR = System.getProperty("refapp.home", System.getProperty("java.io.tmpdir"));
      private static final String LOCAL_HOST = InetAddress.getLoopbackAddress().getHostAddress();
  
      private static final String SYS_PROP_EXTERNAL = "refapp.jdbc.external";
      private static final String SYS_PROP_DRIVER_CLASS = "refapp.jdbc.driver.class.name";
      private static final String SYS_PROP_URL = "refapp.jdbc.app.url";
      private static final String SYS_PROP_SCHEMA = "refapp.jdbc.app.schema";
      private static final String SYS_PROP_USER = "refapp.jdbc.app.user";
      private static final String SYS_PROP_PASSWORD = "refapp.jdbc.app.pass";
      private static final String SYS_PROP_VAL_QUERY = "refapp.jdbc.validation.query";
  
      private static final String EMBEDDED_DRIVER_CLASS = "org.h2.Driver";
      private static final String EMBEDDED_URL_PREFIX = "jdbc:h2:tcp://" + LOCAL_HOST + ":";
      private static final String EMBEDDED_URL_SUFFIX = "/" + DB_DIR + "/h2db";
      private static final Optional<String> EMBEDDED_SCHEMA = Optional.of("PUBLIC");
      private static final String EMBEDDED_USER = "sa";
      private static final String EMBEDDED_PASSWORD = "";
      private static final Optional<String> EMBEDDED_VAL_QUERY = Optional.empty();
  
      private final String driverClassName;
      private final Optional<String> schema;
  
      private final ViburDBCPDataSource dataSource;
  
      /**
       * Construct based on system properties, defaulting if no properties given.
       *
       * @throws SQLException on failure to start the (default) H2 server instance
       */
      public ConnectionProviderImpl() throws SQLException {
  
          // default to false
          final boolean externalDb = Boolean.getBoolean(SYS_PROP_EXTERNAL);
  
          final String url;
          final String user;
          final String password;
          final Optional<String> validationQuery;
          if (externalDb) {
  
              // use the external database specified by the user, via (some mandatory) system properties
              this.driverClassName = mandatoryPropertyValue(SYS_PROP_DRIVER_CLASS);
              url = mandatoryPropertyValue(SYS_PROP_URL);
              this.schema = Optional.ofNullable(System.getProperty(SYS_PROP_SCHEMA));
              user = mandatoryPropertyValue(SYS_PROP_USER);
              password = mandatoryPropertyValue(SYS_PROP_PASSWORD);
              validationQuery = Optional.ofNullable(System.getProperty(SYS_PROP_VAL_QUERY));
          } else {
  
              // use an embedded database
              this.driverClassName = EMBEDDED_DRIVER_CLASS;
              url = embeddedUrl();
              this.schema = EMBEDDED_SCHEMA;
              user = EMBEDDED_USER;
              password = EMBEDDED_PASSWORD;
              validationQuery = EMBEDDED_VAL_QUERY;
          }
  
          log.info("starting database driverClassName='{}' url='{}' schema='{}' user='{}' password='{}' validationQuery='{}'",
                  driverClassName, url, schema, user, password, validationQuery);
  
          // create a vibur pooled datasource
          final ViburDBCPDataSource viburDS = new ViburDBCPDataSource();
          viburDS.setDriverClassName(driverClassName);
          viburDS.setJdbcUrl(url);
          viburDS.setUsername(user);
          viburDS.setPassword(password);
          validationQuery.ifPresent(q -> viburDS.setTestConnectionQuery(q));
          viburDS.setCloseConnectionHook(rawConnection -> {
              boolean autocommit = rawConnection.getAutoCommit();
              if (!autocommit) {
                  rawConnection.commit();
              }
          });
          viburDS.start();
  
          this.dataSource = viburDS;
      }
  
      public void terminate() {
          dataSource.terminate();
      }
 
     @Override
     public Connection connection() throws SQLException {
         return dataSource.getConnection();
     }
 
     @Override
     public DataSource dataSource() {
         return dataSource;
     }
 
     @Override
     public String driverClassName() {
         return driverClassName;
     }
 
     @Override
     public Optional<String> schema() {
         return schema;
     }
 
     /**
      * Create a jdbc url for an H2 embedded server, using a free port, starting the server.
      *
      * @return valid url
      * @throws SQLException on failure to start an H2 server
      */
     private String embeddedUrl() throws SQLException {
 
         // start an H2 server on an available port
         final Server server = Server.createTcpServer();
         System.setProperty("h2.bindAddress", LOCAL_HOST);
         server.start();
 
         // construct a JDBC URL for the above server
         return EMBEDDED_URL_PREFIX + server.getPort() + EMBEDDED_URL_SUFFIX;
     }
 
     /**
      * Return the value of the system property specified. Throws an exception if not specified.
      *
      * @param key the name of the system property
      * @return the string value of the system property
      * @throws RuntimeException when that value is not specified, with an appropriate message
      */
     private String mandatoryPropertyValue(final String key) {
         final String value = System.getProperty(key, null);
         if (value == null) {
             throw new RuntimeException("Mandatory java system property '" + key + "' not specified. See com.atlassian.refapp.api.ConnectionProvider for more information.");
         }
         return value;
     }
 }