/*
 * Copyright (C) 2018-2020, 2023 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   renamed as SQLTrug (formerly MySQLTrug)
            Support for multiple RDBMSs
    2.2.0   Support for in-process DB hsqldb
    2.4.0   Support MS SQLServer
    2.6.1   Code tidy
    2.8.1   Add method to detect empty database.
    3.0.0	Add support for Journal entries
    3.2.0	Add support for Lifecycle analysis.
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.io.IOException;
import java.sql.Driver;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
//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;

/**
 *	Implements ITrug for SQL access to RDBMSs such as MySQL, MariaDB, etc
 * 
 * @author Andrew Gegg
*	@version	3.2.0
*	@since	1.0
 */
public final class SQLTrug implements ITrug
{
	private static final Logger LOGGER = LogManager.getLogger();

	@Override
	public String getName()
	{
		return "SQL";
	}	// getName()

	@Override
	public String getServiceType()
	{
		return "SQL";
	}	// getName()
    
    @Override
	public Map<String, Properties> getServers()
    {
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("getServers()");
        
        Map<String, Properties> servers = new HashMap<>();

        LOGGER.debug("getServers(): jdbc.drivers: "+ System.getProperty("jdbc.drivers"));
//		try
//		{
//			DriverManager.setLogWriter(new PrintWriter("DriverManager.log"));
//		}
//		catch (FileNotFoundException e)
//		{
//			e.printStackTrace();
//			LOGGER.error("getServers(): FileNotFoundException: {}", e);
//		}
//		List<Driver> drivers = DriverManager.drivers().collect(Collectors.toList());
		List<Driver> drivers = DriverManager.drivers().toList();
        LOGGER.debug("getServers(): drivers size: {}", drivers.size());
        LOGGER.debug("getServers(): drivers list: {}", drivers);

		//check each type we know about
        for (Driver d : drivers)
        {
            try {
                LOGGER.trace("Walking driver list: driver: {}", d);
                if (d.acceptsURL("jdbc:mariadb:"))
                {
                   LOGGER.debug("MariaDB support found");
                   Properties p = new Properties();
                   servers.put("MariaDB", p);
                   p.setProperty("name", "MariaDB");
                   p.setProperty("supported", "Y");
                   p.setProperty("driverversion", d.getMajorVersion()+"."+d.getMinorVersion());
                   LOGGER.info("MariaDB: driverversion: {}", p.get("driverversion"));
                }
                else if (d.acceptsURL("jdbc:mysql:"))
                {// NB MariaDB will accept the MySQL URL, so check MariaDB first
                   LOGGER.debug("MySQL support found");
                   Properties p = new Properties();
                   servers.put("MySQL", p);
                   p.setProperty("name", "MySQL");
                   p.setProperty("supported", "Y"); 
//                   LOGGER.debug("getServers(): mysql: before version string");
//                   LOGGER.debug("getServers(): mysql: before version string: major: {}",d.getMajorVersion());
//                   LOGGER.debug("getServers(): mysql: before version string: minor: {}",d.getMinorVersion());
                   p.setProperty("driverversion", d.getMajorVersion()+"."+d.getMinorVersion());
                   LOGGER.info("MySQL: driverversion: {}", p.get("driverversion"));
                }
                else if (d.acceptsURL("jdbc:hsqldb:file:"))
                {// in-process hsqldb
                   LOGGER.debug("hsqldb support found");
                   Properties p = new Properties();
                   servers.put("hsqldb", p);
                   p.setProperty("name", "hsqldb");
                   p.setProperty("supported", "Y");                         
                   p.setProperty("driverversion", d.getMajorVersion()+"."+d.getMinorVersion());
                   LOGGER.info("hsqldb: driverversion: {}", p.get("driverversion"));
                }
                else if (d.acceptsURL("jdbc:sqlserver://")) // the trailing // here is required
                {// MS SQLServer
                   LOGGER.debug("SQLServer support found");
                   Properties p = new Properties();
                   servers.put("MSSQLServer", p);
                   p.setProperty("name", "MSSQLServer");
                   p.setProperty("supported", "Y");                         
                   p.setProperty("driverversion", d.getMajorVersion()+"."+d.getMinorVersion());
                   LOGGER.info("MSSQLServer: driverversion: {}", p.get("driverversion"));
                }
                else
				{
					LOGGER.debug("unsupported driver found: {}", d);
				}
            } catch (SQLException ex) {
                LOGGER.error("getServers(): SQL exception: {}", ex);
            }
            catch (Exception exx)
            {
                LOGGER.error("getServers(): generic exception: {}", exx);
            }
            catch (Error err)
            {
                LOGGER.error("getServers(): generic error: {}", err);
            }
            catch (Throwable exx)
            {
                LOGGER.error("getServers(): generic throwable: {}", exx);
            }
        }
        
        return LOGGER.traceExit(servers);
    }

	@Override
	/**
	*{@inheritDoc}
    *   Each supported Database Manager has its own node in the Preference hierarchy, e.g. a node named "MySQL" for MySQL.
	*	The parameters taken from the {@code prefs} sub-node are<UL>
	*<LI>host - the computer hosting the database server, e.g. localhost
	*<LI>port - the port used to connect to the server, usually 3306 for MySQL, 3307 for MariaDB, 5432 for PostgreSQL
	*<LI>schema - the name of the schema (database), usually 'gardennotebook'
	*<LI>user - the user name used to connect
	*<LI>password - the password for this user on this database
	*</UL>
	*/
	public boolean isAvailable(Preferences prefs) throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("isAvailable()");
        
		boolean checkConn = false;
		try {
			checkConn = DBConnection.setConnection(prefs);
		} catch (SQLException ex) {
			LOGGER.error("isAvailable(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
		return LOGGER.traceExit(log4jEntryMsg, checkConn);
	}	// isAvailable()

	@Override
	public void close()
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("close()");
        DBConnection.close();
	}	// close()
    
    @Override
	public boolean isDatabaseEmpty() throws GNDBException
    {
        return getAfflictionClassLister().fetch().isEmpty();
    }

	@Override
	public IProductCategoryLister getProductCategoryLister()
	{
		return new ProductCategoryLister();
	}

	@Override
	public IProductCategoryBuilder getProductCategoryBuilder()
	{
		return new ProductCategoryBuilder();
	}

	@Override
	public IProductCategoryBuilder getProductCategoryBuilder(IProductCategory item)
	{
		return new ProductCategoryBuilder(item);
	}

	@Override
	public IShoppingListLister getShoppingListLister()
	{
		return new ShoppingListLister();
	}

	@Override
	public IShoppingListBuilder getShoppingListBuilder()
	{
		return new ShoppingListBuilder();
	}

	@Override
	public IShoppingListBuilder getShoppingListBuilder(IShoppingList item)
	{
		return new ShoppingListBuilder(item);
	}

	@Override
	public IPurchaseItemLister getPurchaseItemLister()
	{
		return new PurchaseItemLister();
	}

	@Override
	public IPurchaseItemBuilder getPurchaseItemBuilder()
	{
		return new PurchaseItemBuilder();
	}

	@Override
	public IPurchaseItemBuilder getPurchaseItemBuilder(IPurchaseItem item)
	{
		return new PurchaseItemBuilder(item);
	}

	@Override
	public IWildlifeSpeciesLister getWildlifeSpeciesLister()
	{
		return new WildlifeSpeciesLister();
	}

	@Override
	public IWildlifeSpeciesBuilder getWildlifeSpeciesBuilder()
	{
		return new WildlifeSpeciesBuilder();
	}

	@Override
	public IWildlifeSpeciesBuilder getWildlifeSpeciesBuilder(IWildlifeSpecies item)
	{
		return new WildlifeSpeciesBuilder(item);
	}

	@Override
	public IGroundworkActivityLister getGroundworkActivityLister()
	{
		return new GroundworkActivityLister();
	}

	@Override
	public IGroundworkActivityBuilder getGroundworkActivityBuilder()
	{
		return new GroundworkActivityBuilder();
	}

	@Override
	public IGroundworkActivityBuilder getGroundworkActivityBuilder(IGroundworkActivity item)
	{
		return new GroundworkActivityBuilder(item);
	}

	@Override
	public IGroundworkLister getGroundworkLister()
	{
		return new GroundworkLister();
	}

	@Override
	public IGroundworkBuilder getGroundworkBuilder()
	{
		return new GroundworkBuilder();
	}

	@Override
	public IGroundworkBuilder getGroundworkBuilder(IGroundwork item)
	{
		return new GroundworkBuilder(item);
	}

	@Override
	public IAfflictionClassLister getAfflictionClassLister()
	{
		return new AfflictionClassLister();
	}

	@Override
	public IAfflictionClassBuilder getAfflictionClassBuilder()
	{
		return new AfflictionClassBuilder();
	}

	@Override
	public IAfflictionClassBuilder getAfflictionClassBuilder(IAfflictionClass item)
	{
		return new AfflictionClassBuilder(item);
	}

	@Override
	public IWeatherLister getWeatherLister()
	{
		return new WeatherLister();
	}

	@Override
	public IWeatherBuilder getWeatherBuilder()
	{
		return new WeatherBuilder();
	}

	@Override
	public IWeatherBuilder getWeatherBuilder(IWeather item)
	{
		return new WeatherBuilder(item);
	}

	@Override
	public IRetailerHasProductLister getRetailerHasProductLister()
	{
		return new RetailerHasProductLister();
	}

	@Override
	public IRetailerHasProductBuilder getRetailerHasProductBuilder()
	{
		return new RetailerHasProductBuilder();
	}

	@Override
	public IRetailerHasProductBuilder getRetailerHasProductBuilder(IRetailerHasProduct item)
	{
		return new RetailerHasProductBuilder(item);
	}

	@Override
	public IProductBrandLister getProductBrandLister()
	{
		return new ProductBrandLister();
	}

	@Override
	public IProductBrandBuilder getProductBrandBuilder()
	{
		return new ProductBrandBuilder();
	}

	@Override
	public IProductBrandBuilder getProductBrandBuilder(IProductBrand item)
	{
		return new ProductBrandBuilder(item);
	}

	@Override
	public IHusbandryClassLister getHusbandryClassLister()
	{
		return new HusbandryClassLister();
	}

	@Override
	public IHusbandryClassBuilder getHusbandryClassBuilder()
	{
		return new HusbandryClassBuilder();
	}

	@Override
	public IHusbandryClassBuilder getHusbandryClassBuilder(IHusbandryClass item)
	{
		return new HusbandryClassBuilder(item);
	}

	@Override
	public IAfflictionLister getAfflictionLister()
	{
		return new AfflictionLister();
	}

	@Override
	public IAfflictionBuilder getAfflictionBuilder()
	{
		return new AfflictionBuilder();
	}

	@Override
	public IAfflictionBuilder getAfflictionBuilder(IAffliction item)
	{
		return new AfflictionBuilder(item);
	}

	@Override
	public IWildlifeLister getWildlifeLister()
	{
		return new WildlifeLister();
	}

	@Override
	public IWildlifeBuilder getWildlifeBuilder()
	{
		return new WildlifeBuilder();
	}

	@Override
	public IWildlifeBuilder getWildlifeBuilder(IWildlife item)
	{
		return new WildlifeBuilder(item);
	}

	@Override
	public IProductLister getProductLister()
	{
		return new ProductLister();
	}

	@Override
	public IProductBuilder getProductBuilder()
	{
		return new ProductBuilder();
	}

	@Override
	public IProductBuilder getProductBuilder(IProduct item)
	{
		return new ProductBuilder(item);
	}

	@Override
	public IReminderLister getReminderLister()
	{
		return new ReminderLister();
	}

	@Override
	public IReminderBuilder getReminderBuilder()
	{
		return new ReminderBuilder();
	}

	@Override
	public IReminderBuilder getReminderBuilder(IReminder item)
	{
		return new ReminderBuilder(item);
	}

	@Override
	public IRetailerLister getRetailerLister()
	{
		return new RetailerLister();
	}

	@Override
	public IRetailerBuilder getRetailerBuilder()
	{
		return new RetailerBuilder();
	}

	@Override
	public IRetailerBuilder getRetailerBuilder(IRetailer item)
	{
		return new RetailerBuilder(item);
	}

	@Override
	public IPurchaseLister getPurchaseLister()
	{
		return new PurchaseLister();
	}

	@Override
	public IPurchaseBuilder getPurchaseBuilder()
	{
		return new PurchaseBuilder();
	}

	@Override
	public IPurchaseBuilder getPurchaseBuilder(IPurchase item)
	{
		return new PurchaseBuilder(item);
	}

	@Override
	public IPlantVarietyLister getPlantVarietyLister()
	{
		return new PlantVarietyLister();
	}

	@Override
	public IPlantVarietyBuilder getPlantVarietyBuilder()
	{
		return new PlantVarietyBuilder();
	}

	@Override
	public IPlantVarietyBuilder getPlantVarietyBuilder(IPlantVariety item)
	{
		return new PlantVarietyBuilder(item);
	}

	@Override
	public IPlantVarietyBuilder getPlantVarietyBuilder(IPlantSpecies species)
	{
		return new PlantVarietyBuilder(species);
	}

	@Override
	public IPlantVarietyBuilder getPlantVarietyBuilder(IPlantSpecies species, IPlantVariety item)
	{
		return new PlantVarietyBuilder(species, item);
	}

	@Override
	public IAfflictionEventLister getAfflictionEventLister()
	{
		return new AfflictionEventLister();
	}

	@Override
	public IAfflictionEventBuilder getAfflictionEventBuilder()
	{
		return new AfflictionEventBuilder();
	}

	@Override
	public IAfflictionEventBuilder getAfflictionEventBuilder(IAfflictionEvent item)
	{
		return new AfflictionEventBuilder(item);
	}

	@Override
	public IPlantNoteLister getPlantNoteLister()
	{
		return new PlantNoteLister();
	}

	@Override
	public IPlantNoteBuilder getPlantNoteBuilder()
	{
		return new PlantNoteBuilder();
	}

	@Override
	public IPlantNoteBuilder getPlantNoteBuilder(IPlantNote item)
	{
		return new PlantNoteBuilder(item);
	}

	@Override
	public IHusbandryLister getHusbandryLister()
	{
		return new HusbandryLister();
	}

	@Override
	public IHusbandryBuilder getHusbandryBuilder()
	{
		return new HusbandryBuilder();
	}

	@Override
	public IHusbandryBuilder getHusbandryBuilder(IHusbandry item)
	{
		return new HusbandryBuilder(item);
	}

	@Override
	public IToDoListLister getToDoListLister()
	{
		return new ToDoListLister();
	}

	@Override
	public IToDoListBuilder getToDoListBuilder()
	{
		return new ToDoListBuilder();
	}

	@Override
	public IToDoListBuilder getToDoListBuilder(IToDoList item)
	{
		return new ToDoListBuilder(item);
	}

	@Override
	public IWeatherConditionLister getWeatherConditionLister()
	{
		return new WeatherConditionLister();
	}

	@Override
	public IWeatherConditionBuilder getWeatherConditionBuilder()
	{
		return new WeatherConditionBuilder();
	}

	@Override
	public IWeatherConditionBuilder getWeatherConditionBuilder(IWeatherCondition item)
	{
		return new WeatherConditionBuilder(item);
	}

	@Override
	public IPlantSpeciesLister getPlantSpeciesLister()
	{
		return new PlantSpeciesLister();
	}

	@Override
	public IPlantSpeciesBuilder getPlantSpeciesBuilder()
	{
		return new PlantSpeciesBuilder();
	}

	@Override
	public IPlantSpeciesBuilder getPlantSpeciesBuilder(IPlantSpecies item)
	{
		return new PlantSpeciesBuilder(item);
	}

	@Override
	public ISaleLister getSaleLister()
	{
		return new SaleLister();
	}

	@Override
	public ISaleBuilder getSaleBuilder()
	{
		return new SaleBuilder();
	}

	@Override
	public ISaleBuilder getSaleBuilder(ISale item)
	{
		return new SaleBuilder(item);
	}

	@Override
	public ISaleItemLister getSaleItemLister()
	{
		return new SaleItemLister();
	}

	@Override
	public ISaleItemBuilder getSaleItemBuilder()
	{
		return new SaleItemBuilder();
	}

	@Override
	public ISaleItemBuilder getSaleItemBuilder(ISaleItem item)
	{
		return new SaleItemBuilder(item);
	}

	@Override
	public ILocationLister getLocationLister()
	{
		return new LocationLister();
	}
	@Override
	public ILocationBuilder getLocationBuilder()
	{
		return new LocationBuilder();
	}
	@Override
	public ILocationBuilder getLocationBuilder(ILocation item)
	{
		return new LocationBuilder(item);
	}

	@Override
	public IJournalLister getJournalLister()
	{
		return new JournalLister();
	}

	@Override
	public IJournalBuilder getJournalBuilder()
	{
		return new JournalBuilder();
	}

	@Override
	public IJournalBuilder getJournalBuilder(IJournal item)
	{
		return new JournalBuilder(item);
	}

	@Override
	public IReviewLister getReviewLister()
	{
		return new ReviewLister();
	}

	@Override
	public IReviewBuilder getReviewBuilder()
	{
		return new ReviewBuilder();
	}

	@Override
	public IReviewBuilder getReviewBuilder(IReview item)
	{
		return new ReviewBuilder(item);
	}

	@Override
	public ICropRotationGroupLister getCropRotationGroupLister()
	{
		return new CropRotationGroupLister();
	}
	@Override
	public ICropRotationGroupBuilder getCropRotationGroupBuilder()
	{
		return new CropRotationGroupBuilder();
	}
	@Override
	public ICropRotationGroupBuilder getCropRotationGroupBuilder(ICropRotationGroup item)
	{
		return new CropRotationGroupBuilder(item);
	}

	@Override
	public ICroppingPlanLister getCroppingPlanLister()
	{
		return new CroppingPlanLister();
	}
	@Override
	public ICroppingPlanBuilder getCroppingPlanBuilder()
	{
		return new CroppingPlanBuilder();
	}
	@Override
	public ICroppingPlanBuilder getCroppingPlanBuilder(ICroppingPlan item)
	{
		return new CroppingPlanBuilder(item);
	}

	@Override
	public ICroppingActualLister getCroppingActualLister()
	{
		return new CroppingActualLister();
	}
	@Override
	public ICroppingActualBuilder getCroppingActualBuilder()
	{
		return new CroppingActualBuilder();
	}
	@Override
	public ICroppingActualBuilder getCroppingActualBuilder(ICroppingActual item)
	{
		return new CroppingActualBuilder(item);
	}

	@Override
	public ILifecycleAnalysisLister getLifecycleAnalysisLister()
	{
		return new LifecycleAnalysisLister();
	}

	@Override
	public void toJson(Preferences prefs) throws IOException, GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("toJson()");
		JsonHandler.toJson(prefs);
		LOGGER.traceExit(log4jEntryMsg);
	}

	@Override
	public void fromJson(Preferences prefs) throws IOException, GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("fromJson()");
		JsonHandler.fromJson(prefs);
		LOGGER.traceExit(log4jEntryMsg);
	}

}

