/*
 * Copyright (C) 2018, 2019, 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.2.0   Support hsqldb dialect
    2.4.0   Support MS SQLServer
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.time.LocalDateTime;
import java.util.*;

import java.sql.*;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import java.io.FileWriter;
import java.io.IOException;
import java.io.File;
import java.util.stream.Collectors;

import jakarta.json.JsonArrayBuilder;
import jakarta.json.JsonBuilderFactory;
import jakarta.json.JsonWriter;
import jakarta.json.JsonWriterFactory;
import jakarta.json.JsonObjectBuilder;

/**
*
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/
final class ProductBrandLister implements IProductBrandLister
{
	private static final Logger LOGGER = LogManager.getLogger();

	private boolean useName = false;
	private String[] nameList = new String[10];
	private int nameNext = 0;	// next free slot in list

//	private boolean useId = false;
//	private int[] idList = new int[10];
//	private int idNext = 0;	// next free slot in list
	private final DBKeyHandler<IProductBrand> useProductBrand = new DBKeyHandler<>("productBrandId");

//	private boolean useOwnBrandRetailerId = false;
//	private int[] ownBrandRetailerIdList = new int[10];
//	private int ownBrandRetailerIdNext = 0;
	private final DBKeyHandler<IRetailer> useOwnBrandRetailer = new DBKeyHandler<>("ownBrandRetailerId");

	private boolean useWhere = false;

	ProductBrandLister() {}

	@Override
	public List<IProductBrand> fetch() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetch()");

		List<IProductBrand> vals = new ArrayList<>();
        
        //  2.1.0
        // common case - complete list requested
        if (!useWhere && MySQLCache.completeProductBrand)
        {
            LOGGER.debug("fetch(): returning complete list, already read");
            vals = MySQLCache.cacheProductBrand.values().stream().
                    sorted((pb_1, pb_2) -> pb_1.getName().compareToIgnoreCase(pb_2.getName())).
                    collect(Collectors.toList());
            return vals;
        }
        
		boolean fetchAll = !useWhere;
        String query = "";
		switch (DBConnection.DB_IN_USE)
		{
			case MariaDB, MySQL -> query = buildSQL_MySQL();
			case hsqldb -> query = buildSQL_hsqldb();
			case MSSQLServer -> query = buildSQL_MSSQLServer();
			default -> {
				LOGGER.debug("fetch(): no known rdbms");
				throw new GNDBException(new IllegalStateException("no known RDBMS"));
			}
		}
LOGGER.debug("fetch(): query: {}", query);

		try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
		{
			ResultSet rs = null;
			if (this.useName)
			{
				PreparedStatement pstmt = conn.prepareStatement(query);
				int pstmtIx = 1;
				if (this.useName) {
					for(int ix =0; ix < nameNext; ix++) {
LOGGER.debug("fetch(): pstmtIx: {} = {}", pstmtIx, nameList[ix].toLowerCase());
						pstmt.setString(pstmtIx++, nameList[ix].toLowerCase());}
				}
				rs = pstmt.executeQuery();
			}
			else {
				rs = stmt.executeQuery(query);
			}
			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> vals = processResults_MySQL(rs);
				case hsqldb -> vals = processResults_hsqldb(rs);
				case MSSQLServer -> vals = processResults_MSSQLServer(rs);
				default -> {
					LOGGER.debug("fetch(): no known rdbms");
					throw new GNDBException(new IllegalStateException("no known RDBMS"));
				}
			}
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

		if (vals.isEmpty()) return Collections.emptyList();

		for (IProductBrand vx : vals)
		{
			MySQLCache.cacheProductBrand.putIfAbsent(vx.getKey(), vx);
		}
		if (fetchAll) { MySQLCache.completeProductBrand = true; }

		populateRetailer(vals, fetchAll);

LOGGER.traceExit(log4jEntryMsg);
		return vals;
	}	// fetch()

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from productbrand as d ");
        query.append("left join (select * from comment where ownerType = \"BR\") as c ");
        query.append("on d.productBrandId = c.ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by LOWER(d.name), d.productBrandId, c.date");
        return query.toString();
    }   //  buildSQL_MySQL()

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from productbrand as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'BR') as c ");
        query.append("on d.productBrandId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);//				first = false;
        }
        query.append(" order by LOWER(d.name), d.productBrandId, c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select d.productBrandId as d_productBrandId, " +
                                                        "d.ownBrandRetailerId as d_ownBrandRetailerId, " +
                                                        "d.name as d_name, " +
                                                        "d.description as d_description, " +
                                                        "d.lastUpdated as d_lastUpdated, " +
                                                        "d.created as d_created," +
                                                        " c.* from productbrand as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'BR') as c ");
        query.append("on d.productBrandId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);//				first = false;
        }
        query.append(" order by LOWER(d.name), d.productBrandId, c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

    private void buildCommonSQL(StringBuilder query)
    {
        boolean first = true;

//        if (this.useId)
//        {
//            if (first) query.append(" where ");
//            else query.append(" and");
//            if (idNext > 1) {
//                query.append(" d.productBrandId in (");
//                for(int ix = 0; ix < idNext; ix++) { query.append(idList[ix]).append(", "); }
//                query.replace(query.length()-2, query.length(), ")");
//            }
//            else
//                query.append(" d.productBrandId = ").append(idList[0]);
//            first = false;
//            this.useId = false;
//            this.idNext = 0;
//        }
		if (useProductBrand.isUsed())
		{
			if (first) query.append(" where ");
			else query.append(" and");
			query.append(useProductBrand.getSQLClause("d"));
			first = false;
			useProductBrand.clear();
		}

//		if (this.useOwnBrandRetailerId)
//        {
//            if (first) query.append(" where ");
//            else query.append(" and");
//            if (ownBrandRetailerIdNext > 1) {
//                query.append(" d.ownBrandRetailerId in (");
//                for(int ix =0; ix < ownBrandRetailerIdNext; ix++) {query.append(ownBrandRetailerIdList[ix]).append(", ");}
//                query.replace(query.length()-2, query.length(), ")");
//            }
//            else
//                query.append(" d.ownBrandRetailerId = ").append(ownBrandRetailerIdList[0]);
//            first = false;
//            this.useOwnBrandRetailerId = false;
//            this.ownBrandRetailerIdNext = 0;
//        }
		if (useOwnBrandRetailer.isUsed())
		{
			if (first) query.append(" where ");
			else query.append(" and");
			query.append(useOwnBrandRetailer.getSQLClause("d"));
			first = false;
			useOwnBrandRetailer.clear();
		}

		if (this.useName)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            if (nameNext > 1) {
                query.append(" LOWER(d.name) in (");
                for(int ix =0; ix < nameNext; ix++)
                { query.append("?, "); }
                query.replace(query.length()-2, query.length(), ")");
            }
            else
                query.append("LOWER(d.name) = ?");
            this.useName = false;
            this.nameNext = 0;
        }
    }   //  buildCommonSQL()

	private List<IProductBrand> processResults_MySQL(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MySQL()");

		List<IProductBrand> tempList = new ArrayList<>();

		ProductBrand item = null;

		while (rs.next())
		{
			int productBrandId = rs.getInt("d.productBrandId");
			int tmp_ownBrandRetailerId = rs.getInt("d.ownBrandRetailerId");
			Integer ownBrandRetailerId = rs.wasNull() ? null : tmp_ownBrandRetailerId;
			String name = rs.getString("d.name");
			String description = rs.getString("d.description");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("productBrandId: {}, ownBrandRetailerId: {}, name: {}, description: {}, lastUpdated: {}, created: {}",
                productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
			if (item != null && productBrandId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_MySQL(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("commentId"),
					rs.getInt("ownerId"),
					"BR",
					rs.getDate("c.date").toLocalDate(),
					rs.getString("c.comment"),
					rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults_MySQL(): extra comment is: {}", comm);
				item = new ProductBrand(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productBrandId,
						"BR",
						rs.getDate("c.date").toLocalDate(),
						rs.getString("c.comment"),
						rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults_MySQL(): first comment is: {}", comm);
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MySQL()

	private List<IProductBrand> processResults_hsqldb(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_hsqldb()");

		List<IProductBrand> tempList = new ArrayList<>();

		ProductBrand item = null;

		while (rs.next())
		{
			int productBrandId = rs.getInt("productbrand.productBrandId");
			int tmp_ownBrandRetailerId = rs.getInt("productbrand.ownBrandRetailerId");
			Integer ownBrandRetailerId = rs.wasNull() ? null : tmp_ownBrandRetailerId;
			String name = rs.getString("productbrand.name");
			String description = rs.getString("productbrand.description");
			LocalDateTime lastUpdated = rs.getTimestamp("productbrand.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("productbrand.created").toLocalDateTime();
LOGGER.debug("productBrandId: {}, ownBrandRetailerId: {}, name: {}, description: {}, lastUpdated: {}, created: {}",
                productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
			if (item != null && productBrandId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_hsqldb(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"BR",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): extra comment is: {}", comm);
				item = new ProductBrand(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productBrandId,
						"BR",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): first comment is: {}", comm);
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_hsqldb()

	private List<IProductBrand> processResults_MSSQLServer(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MSSQLServer()");

		List<IProductBrand> tempList = new ArrayList<>();

		ProductBrand item = null;

		while (rs.next())
		{
			int productBrandId = rs.getInt("d_productBrandId");
			int tmp_ownBrandRetailerId = rs.getInt("d_ownBrandRetailerId");
			Integer ownBrandRetailerId = rs.wasNull() ? null : tmp_ownBrandRetailerId;
			String name = rs.getString("d_name");
			String description = rs.getString("d_description");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("productBrandId: {}, ownBrandRetailerId: {}, name: {}, description: {}, lastUpdated: {}, created: {}",
                productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
			if (item != null && productBrandId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults_hsqldb(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"BR",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): extra comment is: {}", comm);
				item = new ProductBrand(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						productBrandId,
						"BR",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults_hsqldb(): first comment is: {}", comm);
					item = new ProductBrand(productBrandId, ownBrandRetailerId, name, description, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MSSQLServer()

	/*
	*	Populate the parents slot(s)
	*/
	private void populateRetailer(List<IProductBrand> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: TD
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populateRetailer()");

		if (fetchAll)
		{
			new RetailerLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			map(c -> ((ProductBrand)c).getOwnBrandRetailerId()).filter(Objects::nonNull).
			mapToInt(Integer::intValue).filter(c -> c>0).distinct().
			toArray();

		if (keys.length > 0)
		{
			new RetailerLister().id(keys).fetch();
		}
LOGGER.traceExit(log4jEntryMsg);
	}

	void clear()
	{
		MySQLCache.cacheProductBrand.clear();
		MySQLCache.completeProductBrand = false;
	}

	/**
	*
	*Select only the ProductBrand entries with these ids
	*May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	ProductBrandLister id(int... vals)
	{
//		useId = true;
//		if (idNext + vals.length >= idList.length)
//		{
//			idList = Arrays.copyOf(idList, idList.length+vals.length+10);
//		}
//		for (int val : vals)
//		{
//			idList[idNext++] = val;
//		}
//		this.useWhere = true;
//		return this;
		useProductBrand.id(vals);
		useWhere = useWhere || useProductBrand.isUsed();
		return this;
	}

	@Override
	public IProductBrandLister productBrand(IProductBrand... items)
	{
//		if (items == null) return this;
//		if (items.length == 0) return this;
//		int[] keys = new int[items.length];
//		int keyCount = 0;
//		for (IProductBrand item : items)
//		{
//			if (item == null) continue;
//			Object ky = item.getKey();
//			if (ky == null) continue;
//			if (ky instanceof Integer) keys[keyCount++] = (Integer)ky;
//		}
//		if (keyCount == 0) return this;
//		keys = Arrays.copyOf(keys, keyCount);	// trim array to actual size - should be a null-op
//		return this.id(keys);
		useProductBrand.item(items);
		useWhere = useWhere || useProductBrand.isUsed();
		return this;
	}

	@Override
	public IProductBrandLister productBrand(List<IProductBrand> items)
	{
//		if (items == null) return this;
//		if (items.isEmpty()) return this;
//		return this.productBrand(items.toArray(new IProductBrand[0]));
		useProductBrand.item(items);
		useWhere = useWhere || useProductBrand.isUsed();
		return this;
	}

	@Override
	public IProductBrandLister retailer(IRetailer... items)
	{
//		if (items == null) return this;
//		if (items.length == 0) return this;
//		useOwnBrandRetailerId = true;
//		if (ownBrandRetailerIdNext + items.length >= ownBrandRetailerIdList.length)
//		{
//			ownBrandRetailerIdList = Arrays.copyOf(ownBrandRetailerIdList, ownBrandRetailerIdList.length+items.length+10);
//		}
//		for (IRetailer item : items) {
//			if (item == null) continue;
//			Object ky = item.getKey();
//			if (ky == null) continue;
//			if (ky instanceof Integer) {ownBrandRetailerIdList[ownBrandRetailerIdNext++] = (Integer)ky;}
//		}
//		useWhere = true;
//		return this;
		useOwnBrandRetailer.item(items);
		useWhere = useWhere || useOwnBrandRetailer.isUsed();
		return this;
	}

	@Override
	public IProductBrandLister retailer(List<IRetailer> items)
	{
//		if (items == null) return this;
//		if (items.isEmpty()) return this;
//		return this.retailer(items.toArray(new IRetailer[0]));
		useOwnBrandRetailer.item(items);
		useWhere = useWhere || useOwnBrandRetailer.isUsed();
		return this;
	}

	@Override
	public IProductBrandLister name(String... items)
	{
		if (items == null) return this;
		if (items.length == 0) return this;
		useName = true;
		if (nameNext + items.length >= nameList.length)
		{
			nameList = Arrays.copyOf(nameList, nameList.length + items.length + 10);
		}
		for (String item : items)
		{
			if (item != null) nameList[nameNext++] = item;
		}
		this.useWhere = true;
		return this;
	}

	void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
	{
		useWhere = false;
		fetch();

		JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
		for (IProductBrand ihc : MySQLCache.cacheProductBrand.values())
		{
			ProductBrand hc = (ProductBrand)ihc;
			jsonHc.add(hc.toJson(builderFactory));
		}
        
        JsonObjectBuilder job = builderFactory.createObjectBuilder();
        job.add("JsonMode", "DUMP");
        job.add("JsonNBClass", "ProductBrand");
        job.add("values", jsonHc);
        
		try (JsonWriter writer = writerFactory.createWriter(new FileWriter(new File(dumpDirectory, "ProductBrand.json"), false)))
		{
			writer.writeObject(job.build());
		} catch (IOException ex) {
			LOGGER.error("toJson(): IOException", ex);
		}
	}	// toJson

}
