/*
* Copyright (C) 2018, 2019 Andrew Gegg
 *
 *	This file is part of the Garden Notebook application
 *
  * The Garden Notebook application is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl.html>.
 */

/*
	Change log
	2.1.0   support multiple server types (MySQL, MariaDB, etc.)
            Retrieve DB and Connector Version strings
    2.1.1   Guard property 'disableMariaDbDriver' moved to end of URL or MySQL connexion fails
    2.2.0   Support for in-process DB hsqldb
            Guard property 'disableMariaDbDriver' removed as it causes MySQL connexion to fail.
            Include 'connectionTimeout' as a configurable value - MySQL was timing out.
    2.4.0   Support MS SQLServer
    3.0.2   SQLServer Connector/J version 10 sets 'always encrypted' on.  The connection string needs changing to set it off
                and a work-around to stop it trying to find a key store.
 */

package uk.co.gardennotebook.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.prefs.Preferences;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
 
/**
 * A singleton class to manage Connections to the MySQL database.
 * This version does NOT use pooled connections, for simplicity;
 * the performance hit is negligible if the database server is co-resident with the application
 * (i.e. the url is localhost);
 *
 *	@author Andy Gegg
 *	@version	3.0.2
 *	@since	1.0
 */
final class DBConnection
{
	private static final Logger LOGGER = LogManager.getLogger();

    private static String connString;
    
    static enum RDBMS_ENUM {MySQL, MariaDB, hsqldb, MSSQLServer}
    static RDBMS_ENUM DB_IN_USE;
	
	/**
	 * Private constructor to enforce non-instantiability
	 */
	private DBConnection()
	{
	}

	/**
	 * Set up the connection parameters for the database connection.
	 * 
     * @param prefs A set of Preferences as specified by the user (in the application Configuration dialogue).
     *              The following attributes are used: 
     * <UL>
     *  <LI>SQL
     *      <UL>
     *      <LI>selectedServer
     *          <UL>
     *              <LI>host - the host computer for the database</LI>
     *              <LI>port - the port  number used to connect to the database</LI>
     *              <LI>directory - hsqldb - the name of the directory holding the database files</LI>
     *              <LI>database - the name of the database</LI>
     *              <LI>schema - the schema within the database - not MySQL, MariaDB</LI>
     *              <LI>user - the user name to use</LI>
     *              <LI>password - the user's password</LI>
     *              <LI>timeout - the connectionTimeout property.  NB value in seconds</LI>
     *          </UL>
     *      </LI>
     *      </UL>
     *  </LI>
     * </UL>
     * For MySQL and MariaDB, the terms 'schema' and 'database' are used interchangeably; database is the relevant property here.
     * For hsqldb the database property should be a directory to hold the relevant files.
     * 
	 * @return	true if a Connection can be created for this set of parameters
     * 
	 * @throws SQLException if cannot connect
    */
    static boolean setConnection(Preferences prefs) throws SQLException
    {
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("setConnection(prefs): prefs {}", prefs);
        
        // try the possible servers in turn
        Preferences lp;
        String setHost;
        String setPort;
        String setDatabase;
        String setUser;
        String setPass;
        int setTimeout;
        if ("MySQL".equalsIgnoreCase(prefs.get("selectedServer", "")))
        {
            lp = prefs.node("MySQL");
            setHost = lp.get("host", "localhost");
            setPort = lp.get("port", "3306");
            setDatabase = lp.get("database", "gardennotebook");
            setUser = lp.get("user", "");
            setPass = lp.get("password", "");
            setTimeout = lp.getInt("timeout", 1);
            // note guard property to prevent a MariaDB connection picking this up
            connString = "jdbc:mysql://" + setHost +":"+ setPort +"/" + setDatabase + "?user=" + setUser +
                            "&password=" + setPass +
                            "&useSSL=false&allowPublicKeyRetrieval=true&sslMode=DISABLED&connectTimeout="+(setTimeout *1000);

//  NB useServerPrepStmts=true means prefix names are lost in PreparedStatement result sets, e.g. the comment fields from a listing (see Product, in particular)
            connString += "&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&maintainTimeStats=false&disableMariaDbDriver=true&serverTimezone=UTC";
            DB_IN_USE = RDBMS_ENUM.MySQL;
        }
        else if ("MariaDB".equalsIgnoreCase(prefs.get("selectedServer", "")))
        {
            lp = prefs.node("MariaDB");
            setHost = lp.get("host", "localhost");
            setPort = lp.get("port", "3307");
            setDatabase = lp.get("database", "gardennotebook");
            setUser = lp.get("user", "");
            setPass = lp.get("password", "");
            setTimeout = lp.getInt("timeout", 1);
            connString = "jdbc:mariadb://" + setHost +":"+ setPort +"/" + setDatabase + "?user=" + setUser +
                            "&password=" + setPass + "&useSSL=false&allowPublicKeyRetrieval=true&sslMode=DISABLED&pool&minPoolSize=2&connectTimeout="+(setTimeout *1000);
            DB_IN_USE = RDBMS_ENUM.MariaDB;
        }
        else if ("hsqldb".equalsIgnoreCase(prefs.get("selectedServer", "")))
        {
            lp = prefs.node("hsqldb");
            String defaultHsqldbLocation;
            if (System.getProperty("os.name").startsWith("Window"))
            {
                LOGGER.debug("using Windows");
                defaultHsqldbLocation = System.getenv("APPDATA") + System.getProperty("file.separator") + "GardenNotebook" + System.getProperty("file.separator") + "hsqldb";
            }
            else
            {
                LOGGER.debug("not Windows");
                defaultHsqldbLocation = System.getProperty("user.home") + System.getProperty("file.separator") + "GardenNotebook" + System.getProperty("file.separator") + "hsqldb";
            }
            //  hsqldb
            String setDirectory = lp.get("directory", defaultHsqldbLocation);
            setDatabase = lp.get("database", "gardennotebook");
            setUser = lp.get("user", "SA");
            setPass = lp.get("password", "");
            connString = "jdbc:hsqldb:file:" + setDirectory + "/" + setDatabase + "?user=" + setUser +
                            "&password=" + setPass + ";ifexists=true;close_result=true";   // + ";get_column_name=false";
            DB_IN_USE = RDBMS_ENUM.hsqldb;
        }
        else if ("MSSQLServer".equalsIgnoreCase(prefs.get("selectedServer", "")))
        {
            lp = prefs.node("MSSQLServer");
            setHost = lp.get("host", "localhost");
            setPort = lp.get("port", "1433");
            setDatabase = lp.get("database", "gardennotebook");
            setUser = lp.get("user", "");
            setPass = lp.get("password", "");
            setTimeout = lp.getInt("timeout", 1);
            connString = "jdbc:sqlserver://" + setHost +":"+ setPort +";databaseName=" + setDatabase + ";user=" + setUser +
                            ";password=" + setPass + ";applicationName=GardenNotebook;loginTimeout="+ setTimeout +    // + "&useSSL=false&allowPublicKeyRetrieval=true&sslMode=DISABLED&pool&minPoolSize=2&connectTimeout="+(setTimeout*1000);
                            ";columnEncryptionSetting=Disabled;TrustServerCertificate =true;";  //  3.0.2 work round for 'always encrypted' problem
            DB_IN_USE = RDBMS_ENUM.MSSQLServer;
        }
        else
        {
            LOGGER.error("No suitable connection could be made");
            return false;
        }
		LOGGER.debug("connection string: {}", connString);   //>>>>>>>>>>>>>>>>>>>>>> This leaks passwords!
        
		Connection connexion = getConnection();
		boolean isValid = (connexion != null);
		if (isValid)
        {
            ResultSet rs = null;
            // intentional drop through
            switch (DB_IN_USE)
            {
                case MariaDB, MySQL -> rs = connexion.createStatement().executeQuery("select version()");
                case MSSQLServer -> rs = connexion.createStatement().executeQuery("select @@version");
                case hsqldb -> rs = connexion.prepareCall("call database_version()").executeQuery();
                default -> {
                    LOGGER.debug("setConnection: no DB_IN_USE: {}", DB_IN_USE);
                    return false;
                }
            }
            while (rs.next())
            {
                String dbVers = rs.getString(1);
                LOGGER.info("database {}, version: {}", DB_IN_USE, dbVers);
                lp.put("dbversion", dbVers);
            }
			connexion.close();
        }
		return isValid;
    }
	
	/**
	 * Factory method to obtain a database Connection
	 * 
	 * @return a usable Connection for the database
	 * @throws SQLException	if the attempt to get a connexion fails
	 */
    static Connection getConnection() throws SQLException
    {
//		EntryMessage log4jEntryMsg = LOGGER.traceEntry("getConnection(): connString {}", connString);
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("getConnection()");
		Connection connexion = null;
		try {
			connexion = DriverManager.getConnection(connString);
		} catch (SQLException ex) {
			LOGGER.error("getConnection(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
//            LOGGER.error(ex.getStackTrace());
			throw ex;
		} catch (Throwable exx)
        {
            LOGGER.error("getConnection(): unexpected error: {}", exx.getMessage());
//            exx.printStackTrace();
            throw new SQLException("Unexpected error in getConnection: "+exx.getMessage());
        }

		return connexion;
    }
    
    static void close()
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("close()");
        switch (DBConnection.DB_IN_USE)
        {
            case MariaDB:
            case MySQL:
            case MSSQLServer:
                break;
            case hsqldb:
                try
                {
                    Connection conn;
                    conn = getConnection();
                    conn.createStatement().execute("shutdown");
                } catch (SQLException ex) {
                    LOGGER.debug("close(): exception: {}", ex);
                    java.util.logging.Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
                }
                break;
            default:
                LOGGER.debug("close(): no known rdbms");
        }
    }

    /**
     * For unit testing only
     */
    static void forceConnectionString(RDBMS_ENUM DBType, String forcedString)
    {
        DB_IN_USE = DBType;
        connString = forcedString;
    }

}
