/*
 * 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.2.0   Support hsqldb dialect
    2.2.5   Improved processing for JSON load
    2.3.0   Date handling change - seems to be a MySQL 8 thing.
    2.4.0   Support MS SQLServer
    3.0.0	FIx bug - crashed if changed date AND text
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.time.*;

import java.util.Collection;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.List;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;

/**
*   A comment on a particular entry.  Often the main content of a husbandry item.
*
*	A 'dirty' builder which should ONLY be called from a public item builder.
*	No validation or checking is performed on the parameters of any function.
*
*	@author	Andy Gegg
*	@version	3.0.0
*	@since	1.0
*/
final class CommentBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

	// must have an owner key
	private final NotebookEntryType ownerType;
	private final int ownerId;

	/**
	*	A convenience constructor
	*
	*	@param	ownerType	the comment owner's NotebookEntry type

	*	@param	ownerId	the id of the comment owner

	*/
	CommentBuilder(NotebookEntryType ownerType, int ownerId)
	{
		this.ownerType = ownerType;
		this.ownerId = ownerId;
	}

	void removeComment(int... keys) throws GNDBException
	{
		StringBuilder query = new StringBuilder("delete from comment where commentId in(");
		for (int ix : keys)
		{
			query.append(ix).append(", ");
		}
		query.replace(query.length()-2, query.length(), ")");
LOGGER.debug("removeComment(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
			int res = stmt.executeUpdate(query.toString());
LOGGER.debug("removeComment(): result: {}", res);
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("removeComment(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
	}

	void changeComment(Collection<CommentChangeList> changes) throws GNDBException
	{
		LOGGER.debug("changeComment(): changes: {}", changes);

		for (CommentChangeList ccl : changes)
		{
			changeComment(ccl.key, ccl.date, ccl.comment);
		}
	}

	void changeComment(int key, LocalDate date, String comment) throws GNDBException
	{
		LOGGER.debug("changeComment(): key: {}, date: {}, comment: {}", key, date, comment);

		StringBuilder query = new StringBuilder("update comment set ");
		if (date != null)
		{
			query.append("date = ?, ");
		}
		if (comment != null)
		{
			query.append("comment = ?, ");
		}
		query.delete(query.length()-2, query.length());

		query.append(" where commentId = ").append(key);
LOGGER.debug("changeComment(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{

			int paramIx = 1;
			if (date != null)
			{
LOGGER.debug("changeComment(): paramIx: {} = {}", paramIx, Date.valueOf(date));
				stmt.setDate(paramIx++, Date.valueOf(date), java.util.Calendar.getInstance()); //  2.3.0
			}
			if (comment != null)
			{
LOGGER.debug("changeComment(): paramIx: {} = {}", paramIx, comment);
				stmt.setString(paramIx++, comment);
			}

			stmt.executeUpdate();
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("changeComment(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

	}

	void addComment(LocalDate date, String... comments) throws GNDBException
	{
		LocalDate useDate = LocalDate.now();
		if (comments.length == 0) return;
		if (date != null) useDate = date;
		StringBuilder query = new StringBuilder("insert into comment (ownerId, ownerType, date, comment) values ");
		for (String str : comments) {
			query.append("(").append(this.ownerId).append(", '").append(this.ownerType.type()).append("', ?, ?), ");
		}
		query.delete(query.length()-2, query.length());

LOGGER.debug("addComment(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
			int paramIx = 1;
			for (String str : comments)
			{
LOGGER.debug("addComment(): paramIx: {} = {}", paramIx, Date.valueOf(useDate));
				stmt.setDate(paramIx++, Date.valueOf(useDate), java.util.Calendar.getInstance());   //  2.3.0
LOGGER.debug("addComment(): paramIx: {} = {}", paramIx, str);
				stmt.setString(paramIx++, str);
			}
			stmt.executeUpdate();
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("addComment(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
	}	//	addComment

	/**
	*	Used to restore a table from a JSON dump.
	*	The calling Builder must have been constructed with its Json object constructor.
	*	All fields (including timestamps) are written to the database, no checks are made.
	*
	*	@param	newVal	A Comment object created from a JSON object written as a DUMP.
	*/
	void doJsonInsert(Comment newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doJsonInsert(): newVal={}", newVal);

		StringBuilder query = new StringBuilder("insert into comment (");
		if (newVal.getId() > 0)
		{
			query.append("commentId, ");
		}
		query.append("ownerId, ownerType, date, comment, created, lastUpdated)");
        if (newVal.getId() > 0 && DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query.append(" overriding system value ");
        }
        query.append(" values (");
		if (newVal.getId() > 0)
		{
			query.append(newVal.getId()).append(", ");
		}
		query.append(this.ownerId).append(", '").append(this.ownerType.type()).append("', ?, ?, ?, ?)");
LOGGER.debug("doJsonInsert(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
			int paramIx = 1;
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, newVal.getDate());
				stmt.setDate(paramIx++, Date.valueOf(newVal.getDate()), java.util.Calendar.getInstance()); //  2.3.0
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, newVal.getComment());
				stmt.setString(paramIx++, newVal.getComment());
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, newVal.getCreated());
			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getCreated()));
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, newVal.getLastUpdated());
			stmt.setTimestamp(paramIx++, Timestamp.valueOf(newVal.getLastUpdated()));
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT comment ON");
            }
			stmt.executeUpdate();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT comment OFF");
            }
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("doJsonInsert(): SQLException", ex);
		}
	}	//	doJsonInsert
    
	/**
	*	Used to restore a table from a JSON dump.
	*	The calling Builder must have been constructed with its Json object constructor.
	*	All fields (including timestamps) are written to the database, no checks are made.
	*
	*	@param	newVal	A List of Comment objects created from a JSON object written as a DUMP.
    *   @param conn     A database connection - this is the responsibility of the caller
    * 
    *   @since 2.2.5
	*/
	void doJsonInsert(List<IComment> newVal, Connection conn)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doJsonInsert(): newVal={}", newVal);

        if (newVal.isEmpty())
            return;
        
		StringBuilder query = new StringBuilder("insert into comment (");
		query.append("commentId, ownerId, ownerType, date, comment, created, lastUpdated)");
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query.append(" overriding system value ");
        }
		query.append(" values (?, ?, ?, ?, ?, ?, ?)");
LOGGER.debug("doJsonInsert(): query: {}", query);
		try (	//Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT comment ON");
            }
            for (IComment cmtx : newVal)
            {
                Comment cmt = (Comment)cmtx;
    			int paramIx = 1;
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, cmt.getId());
                stmt.setInt(paramIx++, cmt.getId());
                
LOGGER.debug("doJsonInsert(): param {}={}", paramIx, cmt.getOwnerId());
                stmt.setInt(paramIx++, cmt.getOwnerId());
                
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, cmt.getOwnerString());
    				stmt.setString(paramIx++, cmt.getOwnerString());
                    
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, cmt.getDate());
    				stmt.setDate(paramIx++, Date.valueOf(cmt.getDate()), java.util.Calendar.getInstance()); //  2.3.0
                    
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, cmt.getComment());
    				stmt.setString(paramIx++, cmt.getComment());
                    
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, cmt.getCreated());
    			stmt.setTimestamp(paramIx++, Timestamp.valueOf(cmt.getCreated()));
LOGGER.debug("doJsonInsert(): paramIx: {} = {}", paramIx, cmt.getLastUpdated());
    			stmt.setTimestamp(paramIx++, Timestamp.valueOf(cmt.getLastUpdated()));
    			stmt.executeUpdate();
            }
			stmt.close();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT comment OFF");
            }
		}catch (SQLException ex) {
			LOGGER.error("doJsonInsert(): SQLException", ex);
		}
	}	//	doJsonInsert
    
}
