/*
 * 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.2.0   Support hsqldb dialect
    2.2.5   Improve JSON load - make it faster!
    2.4.0   Support MS SQLServer
    2.5.0   Fix bug introduced at 2.4.0
    2.6.0   Add functionality to disconnect a leaf item from its Storyline
            Remove commented out code
            Add checks to prevent illegal links (PI and SI)
    2.8.1   Removed deprecated method doJsonInsert
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.sql.*;
import java.util.List;
import jakarta.json.JsonObject;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;

/**
This is the 'closure table' for the storyline (thread) hierarchy.<BR>
Given a tree structure<BR> 
{@code A->B->C, A->D}<BR>
there is an entry for each link, chased all the way back up:<BR>
(A,A,0) (A,B,1) (B,B,0) (A,C,2) (C,C,0) (B,C,1),<BR>
(A,D,1) (D,D,0)<BR>
It's easy to get the id of every ancestor or descendant of a given node.<BR>
The self-referencing nodes (A,A) make it easy to insert new nodes (single SQL rather than several bits).<BR>
Multiple parents are supported (cf a BoM structure).<BR>

For appropriate SQL see, e.g.,<BR>
https://www.slideshare.net/billkarwin/models-for-hierarchical-data
(slide 41 et seq)<BR>
and also<BR>
https://gist.github.com/ekillaby/2377806
and also<BR>
http://karwin.blogspot.co.uk/2010/03/rendering-trees-with-closure-tables.html
and also for reparenting a sub-tree<BR>
https://gist.github.com/kentoj/872cbefc68f68a2a97b6189da9cd6e23
*
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/

final class StoryLineIndexBuilder
{
	private static final Logger LOGGER = LogManager.getLogger();

/**
*
*<p>Make the StoryLineIndex entries required to add a descendant to a parent
*	It is assumed that hygiene checks have been performed:
*		-	the daughter has no children
*		-	the daughter has no parent
*
*	@param	motherType	the type of the ancestor node to be the new parent
*	@param	motherId	the database id of the ancestor node to be the new parent
*	@param	daughterType	the type of the descendant node to be the new child
*	@param	daughterId	the database id of the descendant node to be the new child
*
*	@return	true if the update was successful
*
*	@throws	GNDBException	If the underlying MySQL database throws SQLException it is translated to this.
*				            The causal SQLException can be retrieved by <code>getCause()</code>
*/
	boolean addDescendant(NotebookEntryType motherType, int motherId, NotebookEntryType daughterType, int daughterId) throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addDescendant(): mother: {}, {}, daughter: {}, {}", motherType, motherId, daughterType, daughterId);
		//	check mother and daughter are both allowed to be in a StoryLine
		if (!checkThreadable(motherType)) return false;
		if (!checkThreadable(daughterType)) return false;
        
        //  2.6.0
        // PurchaseItems cannot be descendants
        if (daughterType == NotebookEntryType.PURCHASEITEM) return false;
        // SaleItems cannot be ancestors
        if (motherType == NotebookEntryType.SALEITEM) return false;
        // SaleItems cannot be direct descendants of PurchaseItems
        if (    motherType == NotebookEntryType.PURCHASEITEM &&
                daughterType == NotebookEntryType.SALEITEM) return false;

		final String motherTag = motherType.type();
		final String daughterTag = daughterType.type();
		// first check if the mother is already known in the StoryLineIndex
		boolean gotOne = false;
        String query_str;
        switch (DBConnection.DB_IN_USE)
        {
            case MariaDB, MySQL -> query_str = "select exists (select 1 from storylineindex where ancestorId = " + motherId +
                    " and ancestorType = '" + motherTag + "') as fred";   //  2.5.0
            case hsqldb -> query_str = "select exists (select 1 from storylineindex where ancestorId = " + motherId +
                    " and ancestorType = '" + motherTag + "') as fred from (values(99))";   //  2.5.0
            case MSSQLServer -> query_str = "select CASE WHEN EXISTS (select 1 from storylineindex where ancestorId = " + motherId +
                    " and ancestorType = '" + motherTag + "') THEN 1 ELSE 0 END as fred";   //  2.5.0
            default -> {
                LOGGER.error("addDescendant(): no known rdbms");
                throw new GNDBException(new IllegalStateException("StoryLineIndexBuilder: addDescendant(): no known RDBMS"));
            }
        }
LOGGER.debug("addDescendant(): check mother: query: {}", query_str);

		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(query_str); )
		{
//			ResultSet rs = stmt.executeQuery(query_str);
            rs.next();
            gotOne = rs.getBoolean("fred");
            rs.close();
LOGGER.debug("addDescendant(): readValue(fred): {}", gotOne);
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("addDescendant(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

		int rowCount = -1;
		// if the mother is NOT known in the StoryLineIndex, add the self reference
        StringBuilder query;
        if (!gotOne)
		{
			query = new StringBuilder("insert into storylineindex ").
					append("(ancestorId, ancestorType, descendantId, descendantType, depth) ").
					append("values (").append(motherId).append(", '").append(motherTag).append("', ").
					append(motherId).append(", '").append(motherTag).append("', 0)");
LOGGER.debug("addDescendant(): add mother: query: {}", query);
			try (	Connection conn = DBConnection.getConnection();
					Statement stmt = conn.createStatement();	)
			{
				rowCount = stmt.executeUpdate(query.toString());
LOGGER.debug("addDescendant(): after insert mother: rowCount: {}", rowCount);
				stmt.close();
			}catch (SQLException ex) {
				LOGGER.error("addDescendant(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
				throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
			}
		}

		query = new StringBuilder("insert into storylineindex ").
				append("(ancestorId, ancestorType, descendantId, descendantType, depth) ").
				append("select ancestorId, ancestorType, ").append(daughterId).append(", '").append(daughterTag).append("', depth+1 ").
				append("from storylineindex ").
				append("where descendantId = ").append(motherId).append(" and descendantType = '").append(motherTag).append("' ").
				append("union all select ").append(daughterId).append(", '").append(daughterTag).append("', ").
				append(daughterId).append(", '").append(daughterTag).append("', 0");
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query.append(" from (values(99))");
        }
LOGGER.debug("addDescendant(): query: {}", query);
		rowCount = -1;
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
			rowCount = stmt.executeUpdate(query.toString());
LOGGER.debug("addDescendant(): after insert: rowCount: {}", rowCount);
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("addDescendant(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
		return LOGGER.traceExit(log4jEntryMsg, (rowCount > 0));
	}	//	addDescendant()
    
    /**
     * Disconnect a leaf item from its storyline.
     * It is assumed that hygiene checks have been made:
     *  -   the item has no descendants.
     * NB This code MUST NOT be used to disconnect a sub-tree.
     * There is no actual requirement for the item to have any ancestors or even to be in the index!
     * 
     * @param daughterType  the type of the leaf item
     * @param daughterId    the id of the leaf item
     * @throws GNDBException    If the underlying MySQL database throws SQLException it is translated to this.
     *				            The causal SQLException can be retrieved by <code>getCause()</code>
     * 
     * @since   2.6.0
     */
    void dropLeaf(NotebookEntryType daughterType, int daughterId) throws GNDBException
    {
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("dropLeaf(): daughter: {}, {}", daughterType, daughterId);
		//	check daughter is allowed to be in a StoryLine
		if (!checkThreadable(daughterType)) return;
        
        if (StoryLineIndexUtils.hasDescendant(daughterType, daughterId))
        {
            LOGGER.error("StoryLineIndexBuilder: dropLeaf(): IllegalArgument: daughter: {}, {} is not a leaf", daughterType, daughterId);
            throw new IllegalArgumentException("StoryLineIndexBuilder: Cannot drop a non-leaf item");
        }

		final String daughterTag = daughterType.type();
        
        //  NB the following will delete the level 0 'self reference' entry so this CANNOT be used to disconnect a sub-tree
        final String query = "delete from storylineindex where descendantType = '" + daughterTag + "' and descendantId = " + daughterId;
        LOGGER.trace("StoryLineIndexBuilder: dropLeaf(): query: {}", query);
        
        try (	Connection conn = DBConnection.getConnection();
                Statement stmt = conn.createStatement();	)
        {
            int rowCount = stmt.executeUpdate(query);
            LOGGER.debug("StoryLineIndexBuilder: dropLeaf(): after delete daughter: rowCount: {}", rowCount);
            stmt.close();
        }catch (SQLException ex) {
            LOGGER.error("StoryLineIndexBuilder: dropLeaf(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
    }   //  dropLeaf()

	private boolean checkThreadable(NotebookEntryType type)
	{
		//	check mother and daughter are both allowed to be in a StoryLine
        //	intentional drop through!
        return switch (type)
                {
                    case PURCHASEITEM, GROUNDWORK, AFFLICTIONEVENT, HUSBANDRY, SALEITEM -> true;
                    default -> false;
                };
	}	//	checkThreadable()

    /**
     * Process the whole JSON array from a DUMP
     * 
     *  @param newVal    a list of JSON objects representing StoryLineIndexes as output by a JSON DUMP
     * 
     *	@throws	GNDBException	If the underlying MySQL database throws SQLException it is translated to this.
     *				The causal SQLException can be retrieved by <code>getCause()</code>
     * 
     * @since 2.2.5
     */
    void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("doJsonInsert(list JSON)");
        
        if (newVal.isEmpty())
            return;
      
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
            conn.setAutoCommit(false);
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT storylineindex ON");
            }
            
            int txCount = 0;
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("StoryLineIndex DUMP object is not DUMP");
                    throw new IllegalArgumentException("StoryLineIndex DUMP object is not DUMP");
                }
                if (!"StoryLineIndex".equals(jo.getString("JsonNBClass", "StoryLineIndex")))
                {
                    LOGGER.error("StoryLineIndex DUMP object is not StoryLineIndex");
                    throw new IllegalArgumentException("StoryLineIndex DUMP object is not StoryLineIndex");
                }
                StoryLineIndex ps = new StoryLineIndex(jo);
                if (ps.id() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("StoryLineIndex DUMP object does not have an id");
                    throw new IllegalArgumentException("StoryLineIndex DUMP object does not have an id");
                }
                
                String query = "insert into storylineindex (storylineindexid, ancestorId, ancestorType, descendantId, descendantType, depth)";
                if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
                {
                    query += " overriding system value ";
                }
                query += " values (" + ps.id() + ", " + 
                            ps.ancestorId() + ", '" + ps.getAncestorType().type() + "', " + 
                            ps.descendantId() + ", '" + ps.getDescendantType().type() + "', " +
                            ps.depth() + ")";
LOGGER.debug("restoreJsonDump(): query: {}", query);

                int rowCount = stmt.executeUpdate(query);
LOGGER.debug("restoreJsonDump(): after insert: rowCount: {}", rowCount);

                if (++txCount > 100)	//	increased from 50 to improve MySQL performance
                {
                    conn.commit();
                    txCount = 0;
                }
            }
            conn.commit();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT storylineindex OFF");
            }
		}catch (SQLException ex) {
			LOGGER.error("restoreJsonDump(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

        LOGGER.traceExit(log4jEntryMsg);
	}	//	restoreJsonDump(JsonObject)

}
