/*
 *
 *  Copyright (C) 2021-2023 Andrew Gegg
 *
 * 	This file is part of the Gardeners Notebook application
 *
 *  The Gardeners 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
	3.1.0	Use jakarta implementation of JSON
*/

package uk.co.gardennotebook.mysql;

import org.apache.logging.log4j.Level;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import uk.co.gardennotebook.spi.*;

import jakarta.json.JsonObject;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Year;
import java.util.List;

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

    private ICroppingPlan oldInstance = null;

    private final boolean newInstance;

    private int id;

    private int cropRotationGroupId;
    private boolean changedCropRotationGroupId = false;

    /*
     *	Where the activity took place, e.g. where the plants were planted out.
     */
    private int locationId;
    private boolean changedLocationId = false;

    private Year yearOfPlan;
    private boolean changedYearOfPlan = false;

    private LocalDateTime lastUpdated;
    private LocalDateTime created;

    private boolean somethingChanged = false;

    private boolean changedComments = false;

    private DBCommentHandler commentHandler;	//	compiler will not allow declaration as final

    /**
     *	constructor to use for a new entry
     */
    CroppingPlanBuilder()
    {
        this(null);
    }

    /**
     *	constructor to use to edit or delete an existing entry
     *
     *	@param	oldVal	the existing item to modify or delete; if null a new entry will be created
     */
    CroppingPlanBuilder(final ICroppingPlan oldVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("constructor(): oldVal={}", oldVal);
        if (oldVal == null || oldVal.getKey() == null || !(oldVal.getKey() instanceof Integer))
        {
            newInstance = true;
            oldInstance = null;
            this.id = -1;
            commentHandler = new DBCommentHandler(NotebookEntryType.CROPPINGPLAN);
            return;
        }

        newInstance = false;
        oldInstance = oldVal;

        if (oldVal instanceof CroppingPlan baseObj)
        {
            this.id = baseObj.getId();
            this.cropRotationGroupId = baseObj.getCropRotationGroupId();
            this.yearOfPlan = baseObj.getYearOfPlan();
//            System.out.println("constructor: yearOfPlan: "+this.yearOfPlan);
            this.locationId = baseObj.getLocationId();
            this.lastUpdated = baseObj.getLastUpdated();
            this.created = baseObj.getCreated();
        }
        else
        {
            Object ky = oldVal.getKey();
            if (ky == null) return;
            this.id = (Integer)ky;
            ky = oldVal.getCropRotationGroup();
            if (ky == null)
            {
                this.cropRotationGroupId = 0;
            }
            else
            {
                this.cropRotationGroupId = ((ICropRotationGroup)ky).getKey();
            }
            ky = oldVal.getLocation();
            if (ky == null)
            {
                this.locationId = 0;
            }
            else
            {
                this.locationId = ((ILocation)ky).getKey();
            }
            this.yearOfPlan = oldVal.getYearOfPlan();
            this.lastUpdated = oldVal.getLastUpdated();
            this.created = oldVal.getCreated();
        }
        commentHandler = new DBCommentHandler(NotebookEntryType.CROPPINGPLAN, this.id);

        LOGGER.traceExit();
    }	//	constructor()

    /**
     *	give the (new) value of cropRotationGroup
     *
     *	@param	newVal	the new value
     *	@return	this Builder
     */
    ICroppingPlanBuilder cropRotationGroupId(final int newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("cropRotationGroupId(): oldVal={}, newVal={}", this.cropRotationGroupId, newVal);
        if (this.cropRotationGroupId == newVal) return this;
        this.cropRotationGroupId = newVal;
        changedCropRotationGroupId = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }
    @Override
    public ICroppingPlanBuilder cropRotationGroup(ICropRotationGroup newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("cropRotationGroup(): oldVal={}, newVal={}", this.cropRotationGroupId, newVal);
        if (newVal == null) return this;
        if (this.cropRotationGroupId == newVal.getKey()) return this;
        this.cropRotationGroupId = newVal.getKey();
        changedCropRotationGroupId = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    /**
     *	give the (new) value of locationId
     *
     *	@param	newVal	the new value
     *	@return	this Builder
     */
    ICroppingPlanBuilder locationId(final int newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("location(): oldVal={}, newVal={}", this.locationId, newVal);
        if (this.locationId == newVal) return this;
        this.locationId = newVal;
        changedLocationId = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ICroppingPlanBuilder location(final ILocation newVal)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("location(): oldVal={}, newVal={}", this.locationId, newVal);
        if ((newVal == null) && (this.locationId == 0)) return this;
        if ((newVal != null) && (this.locationId != 0) && (this.locationId == newVal.getKey())) return this;
        if (newVal == null)
        {
            this.locationId = 0;
        }
        else
        {	//	non-null value
            this.locationId = newVal.getKey();
        }
        changedLocationId = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ICroppingPlanBuilder yearOfPlan(Year yearOfPlan)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("yearOfPlan(): oldVal={}, newVal={}", this.yearOfPlan, yearOfPlan);

        if (yearOfPlan == null) return this;
        if (yearOfPlan.equals(this.yearOfPlan)) return this;
        this.yearOfPlan = yearOfPlan;
        changedYearOfPlan = true;
        somethingChanged = true;
        LOGGER.traceExit();
        return this;
    }

    @Override
    public boolean isPlanDuplicate() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("isPlanDuplicate()");

        //  some things not set yet
        if (locationId <= 0 || cropRotationGroupId <= 0 || yearOfPlan == null)
        {
            return false;
        }
        //  nothing changed yet
        if (!newInstance && !changedLocationId && !changedCropRotationGroupId && !changedYearOfPlan)
        {
            return false;
        }
        //  things changed but changed back to original value
        LOGGER.debug("about to check values changed and changed back: oldInstance: {}", oldInstance);
        LOGGER.debug("new values: newInstance: {}, locationId: {}, cropRotationGroupId: {}, yearOfPlan:, {}", newInstance, locationId, cropRotationGroupId, yearOfPlan);
        if (!newInstance &&
                locationId == oldInstance.getLocation().getKey() &&
                cropRotationGroupId == oldInstance.getCropRotationGroup().getKey() &&
                yearOfPlan.equals(oldInstance.getYearOfPlan()) )
        {
            return false;
        }

        //  check the database
        boolean duplicateFound = false;
        String query;
        switch (DBConnection.DB_IN_USE)
        {
            case MariaDB, MySQL -> query = "select exists (select 1 from croppingplan where locationId = " + locationId +
                                                            " and cropRotationGroupId = " + cropRotationGroupId +
                                                            " and yearOfPlan = " + yearOfPlan + ") as numvals";
            case hsqldb -> query = "select exists (select 1 from croppingplan where locationId = " + locationId +
                                                            " and cropRotationGroupId = " + cropRotationGroupId +
                                                            " and YEAR(yearOfPlan) = " + yearOfPlan + ") as numvals from (values(99))";
            case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from croppingplan where locationId = " + locationId +
                                                            " and cropRotationGroupId = " + cropRotationGroupId +
                                                            " and YEAR(yearOfPlan) = " + yearOfPlan + ") THEN 1 ELSE 0 END as numvals";
            default -> {
                LOGGER.error("isPlanDuplicate(): no known rdbms");
                throw new GNDBException(new IllegalStateException("PlantSpeciesBuilder: isCommonNameDuplicate(): no known RDBMS"));
            }
        }
        LOGGER.debug("isPlanDuplicate(): query: {}", query);

        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query); )
        {
            rs.next();
            duplicateFound = rs.getBoolean("numvals");
            LOGGER.debug("isPlanDuplicate(): result: {}", duplicateFound);
        }
        catch (SQLException ex)
        {
            LOGGER.error("isPlanDuplicate(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }

        return LOGGER.traceExit(log4jEntryMsg, duplicateFound);
    }

    @Override
    public ICroppingPlanBuilder addComment(String... newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

        commentHandler.addComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit();
        return this;
    }

    @Override
    public ICroppingPlanBuilder addComment(List<String> newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

        commentHandler.addComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit();
        return this;
    }

    /**
     *	remove a comment from this item
     *
     *	@param	newVals	the comment to remove.  If the comment does not exist, this is a null-op
     *	@return	 this Builder
     */
    ICroppingPlanBuilder deleteComment(int... newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

        commentHandler.deleteComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ICroppingPlanBuilder deleteComment(IComment... newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

        commentHandler.deleteComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ICroppingPlanBuilder deleteComment(List<IComment> newVals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

        commentHandler.deleteComment(newVals);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ICroppingPlanBuilder changeComment(IComment base, String comment)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, comment);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ICroppingPlanBuilder changeComment(IComment base, LocalDate date)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, date);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public ICroppingPlanBuilder changeComment(IComment base, LocalDate date, String comment)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

        commentHandler.changeComment(base, date, comment);
        changedComments = commentHandler.isChangedComments();
        LOGGER.traceExit(log4jEntryMsg);
        return this;
    }

    @Override
    public boolean needSave()
    {
        LOGGER.debug("needSave: somethingChanged : {}, changedComments: {}", somethingChanged, changedComments);
        return somethingChanged || changedComments;
    }

    @Override
    public boolean canSave()
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("canSave(): newInstance={}", newInstance);

        if (!needSave())
        {//	save() will be a null-op but that's OK
//            return true;
//            System.out.println("not need save: newInstance: "+newInstance);
            return !newInstance;
        }

        if (this.cropRotationGroupId <= 0)
        {
            LOGGER.debug("Crop Rotation Group not set");
//            System.out.println("Crop Rotation Group not set");
            return false;
        }
        if (this.locationId <= 0)
        {
            LOGGER.debug("Location not set");
//            System.out.println("Location not set");
            return false;
        }

        if (this.yearOfPlan == null ||
                this.yearOfPlan.equals(Year.MAX_VALUE) ||
                this.yearOfPlan.equals(Year.MIN_VALUE) )
        {
            LOGGER.debug("Year of Plan not set");
//            System.out.println("Year of Plan not set");
            return false;
        }

        return true;
    }

    @Override
    public boolean canDelete() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("canDelete(): newInstance={}", newInstance);

        if (newInstance) return LOGGER.traceExit(log4jEntryMsg, false);

        String query;
        ResultSet rs;
        boolean  readValue = false;
        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();	)
        {
            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> query = "select exists (select 1 from croppingactual where croppingPlanId = " + this.id + ") as fred";
                case hsqldb -> query = "select exists (select 1 from croppingactual where croppingPlanId = " + this.id + ") as fred from (values(99))";
                case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from croppingactual where croppingPlanId = " + this.id + ") THEN 1 ELSE 0 END as fred";
                default -> {
                    LOGGER.error("canDelete(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("CroppingPlanBuilder: canDelete(): no known RDBMS"));
                }
            }
            LOGGER.debug("canDelete(): query: {}", query);
            rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            LOGGER.debug("canDelete(): readValue: {}", readValue);
            if (readValue)
            {
                return LOGGER.traceExit(log4jEntryMsg, false);
            }

        }catch (SQLException ex) {
            LOGGER.error("canDelete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        return LOGGER.traceExit(log4jEntryMsg, !readValue);
    }

    @Override
    public void delete() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("delete(): newInstance={}", newInstance);

        if (newInstance) return;
        if (!canDelete()) return;

        int res = 0;
        String query = "delete from croppingplan where croppingPlanId = " + this.id;
        LOGGER.debug("delete(): query: {}", query);
        try (	Connection conn = DBConnection.getConnection();
                 Statement stmt = conn.createStatement();	)
        {
            res = stmt.executeUpdate(query);
            LOGGER.debug("delete(): result: {}", res);
            // tidy up dependencies with nullable keys
            if (res > 0) {
                query = "delete from comment where ownerId = " + this.id + " and ownerType = '"+NotebookEntryType.CROPPINGPLAN.type()+"'";
                int res2 = stmt.executeUpdate(query);
                LOGGER.debug("delete() comments: result: {}", res2);
            }
            stmt.close();
        }catch (SQLException ex) {
            LOGGER.error("delete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        if (res > 0)
        {
            oldInstance.flagDeleted();
            MySQLCache.cacheCroppingPlan.remove(this.id);
            //	tell any parent beans the child list has mutated
            //	only additions and deletions matter, other changes will be reflected through the child bean
            MySQLCache.cacheCropRotationGroup.get(oldInstance.getCropRotationGroup().getKey()).flagChildDeleted(oldInstance);
            MySQLCache.cacheLocation.get(oldInstance.getLocation().getKey()).flagChildDeleted(oldInstance);
        }
        oldInstance = null;
        LOGGER.traceExit(log4jEntryMsg);
    }

    @Override
    public ICroppingPlan save() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("save(): somethingChanged: {}, newInstance: {}, changedComments: {}",
                somethingChanged, newInstance, changedComments);

        if (!somethingChanged && !newInstance && !changedComments)
        {
            LOGGER.traceExit("nothing changed");
            return MySQLCache.cacheCroppingPlan.get(this.id);
        }

        if (newInstance)
        {
            doInsert();
        }
        else if (somethingChanged)
        {
            doUpdate();
        }

        if (changedComments)
        {
            commentHandler.setParentId(this.id);
            commentHandler.save();
        }

// mark cache as dirty
        if (!newInstance &&(somethingChanged || changedComments))
        {
            MySQLCache.cacheCroppingPlan.remove(this.id);
        }
// populate the cache
        new CroppingPlanLister().id(this.id).fetch();
        ICroppingPlan newValue = MySQLCache.cacheCroppingPlan.get(this.id);
//System.out.println("newValue: "+newValue);
        if (oldInstance != null)
        {
            oldInstance.flagReplaced(newValue);
        }

        //	tell any parent beans the child list has mutated
        //	only additions and deletions matter, other changes will be reflected through the child bean
        if (newInstance)
        {
//            System.out.println("cropRotationGroupId: "+cropRotationGroupId);
            MySQLCache.cacheCropRotationGroup.get(cropRotationGroupId).flagChildAdded(newValue);
            MySQLCache.cacheLocation.get(locationId).flagChildAdded(newValue);
        }
        else
        {	//	updated
            if (changedCropRotationGroupId)
            {
                if (oldInstance != null)
                {
                    MySQLCache.cacheCropRotationGroup.get(oldInstance.getCropRotationGroup().getKey()).flagChildDeleted(oldInstance);
                }
                MySQLCache.cacheCropRotationGroup.get(newValue.getCropRotationGroup().getKey()).flagChildAdded(newValue);
            }
            if (changedLocationId)
            {
//                LOGGER.debug("changedLocation: oldInstance: {}, newValue: {}", oldInstance, newValue);
                if (oldInstance != null)
                {
                   MySQLCache.cacheLocation.get(oldInstance.getLocation().getKey()).flagChildDeleted(oldInstance);
                }
                MySQLCache.cacheLocation.get(newValue.getLocation().getKey()).flagChildAdded(newValue);
            }
        }

        //	stop multiple saves!
        oldInstance = null;

        somethingChanged = false;
        changedComments = false;
        changedCropRotationGroupId = false;
        changedLocationId = false;
        changedYearOfPlan = false;

        LOGGER.traceExit(log4jEntryMsg);
        return newValue;
    }   //  save()

    private void doUpdate() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("doUpdate(): newInstance={}, somethingChanged={}", newInstance, somethingChanged);

        if (newInstance) return;
        if (!somethingChanged) return;
        String query = "update croppingplan set ";
        if (changedCropRotationGroupId)
        {
            query += "cropRotationGroupId = ?, ";
        }

        if (changedLocationId)
        {
            query += "locationId = ?, ";
        }

        if (changedYearOfPlan)
        {
            query += "yearOfPlan = ?, ";
        }

        query = query.substring(0, query.length() - 2);
        query += " where croppingPlanId = " + this.id;
        LOGGER.debug("doUpdate(): query={} ", query);

        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query);)
        {
            int paramIx = 1;
            if (changedCropRotationGroupId)
            {
                LOGGER.debug("doUpdate(): param {}={}", paramIx, this.changedCropRotationGroupId);
                stmt.setInt(paramIx++, this.cropRotationGroupId);
            }

            if (changedLocationId)
            {
                LOGGER.debug("doUpdate(): param {}={}", paramIx, this.locationId);
                stmt.setInt(paramIx++, this.locationId);
            }

            if (changedYearOfPlan)
            {
                LOGGER.debug("doUpdate(): param {}={}", paramIx, Date.valueOf(this.yearOfPlan.atDay(1)));
                if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MySQL ||
                        DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MariaDB)
                {
                    // Passing a simple Date value throws SQLException 'data truncated' (i.e. truncating 2021-01-01T00:00.00 to 2021)
                    // see https://stackoverflow.com/questions/66411364/how-to-insert-year-data-type-in-mysql-server-using-java
                    // answer from Basil Borque
                    stmt.setString(paramIx++, yearOfPlan.toString());
                }
                else
                {
                    stmt.setDate(paramIx++, Date.valueOf(this.yearOfPlan.atDay(1)), java.util.Calendar.getInstance());
                }
            }

            stmt.executeUpdate();

        }catch (SQLException ex) {
            LOGGER.error("doUpdate(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }
        LOGGER.traceExit(log4jEntryMsg);
    }   //  doUpdate()

    private void doInsert() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("doInsert(): newInstance={}", newInstance);

        if (!newInstance) return;
        if (!canSave())
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("doInsert(): save criteria not met"));
        }

        if (!this.changedCropRotationGroupId)
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("CroppingPlanBuilder: doInsert(): Crop Rotation Group unspecified"));
        }
        if (!this.changedLocationId)
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("CroppingPlanBuilder: doInsert(): Location unspecified"));
        }
        if (!this.changedYearOfPlan ||
                this.yearOfPlan == null ||
                this.yearOfPlan.equals(Year.MAX_VALUE) ||
                this.yearOfPlan.equals(Year.MIN_VALUE) )
        {
            throw LOGGER.throwing(Level.ERROR, new IllegalStateException("CroppingPlanBuilder: doInsert(): Year of Plan unspecified"));
        }

        String query = "insert into croppingplan ";
        query += "(cropRotationGroupId, locationId, yearOfPlan) values(" + cropRotationGroupId + ", " + locationId + ", ";
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MySQL ||
            DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MariaDB)
        {
            // Passing a simple Date value throws SQLException 'data truncated' (i.e. truncating 2021-01-01T00:00.00 to 2021)
            // see https://stackoverflow.com/questions/66411364/how-to-insert-year-data-type-in-mysql-server-using-java
            // answer from Basil Borque
            query += this.yearOfPlan.getValue();
        }
        else
        {
            query += "'" + Date.valueOf(this.yearOfPlan.atDay(1)) + "'";
        }
        query += ");";
//        System.out.println("query: "+ query);
        LOGGER.debug("doInsert(): query={}", query.toString());

        try (	Connection conn = DBConnection.getConnection();
                 Statement stmt = conn.createStatement(); )
        {
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

            ResultSet rs = stmt.getGeneratedKeys();
            rs.next();
            int newId = rs.getInt(1);
            LOGGER.debug("doInsert(): newId: {}", newId);
            this.id = newId;
        }
        catch (SQLException ex) {
            LOGGER.error("doInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
            throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
        }

        LOGGER.traceExit(log4jEntryMsg);
    }   //  doInsert()

    /**
     * Process the whole JSON array from a DUMP
     *
     *  @param newVal    a list of JSON objects representing Husbandry entries 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 3.0.0
     */
    void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("restoreJsonDump(list JSON)");

        if (newVal.isEmpty())
            return;

        String query = "insert into croppingplan (croppingPlanId, cropRotationGroupId, locationId, yearOfPlan, lastUpdated, created) ";
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query += " overriding system value ";
        }
        query += " values (?, ?, ?, ?, ?, ?)";
        LOGGER.debug("restoreJsonDump(): query={}", query.toString());

        try (	Connection conn = DBConnection.getConnection();
                 PreparedStatement stmt = conn.prepareStatement(query.toString());	)
        {
            conn.setAutoCommit(false);
            int txCount = 0;
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT croppingplan ON");
            }

            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("CroppingPlan DUMP object is not DUMP");
                    throw new IllegalArgumentException("CroppingPlan DUMP object is not DUMP");
                }
                if (!"CroppingPlan".equals(jo.getString("JsonNBClass", "CroppingPlan")))
                {
                    LOGGER.error("CroppingPlan DUMP object is not CroppingPlan");
                    throw new IllegalArgumentException("CroppingPlan DUMP object is not CroppingPlan");
                }
                CroppingPlan ps = new CroppingPlan(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    LOGGER.error("CroppingPlan DUMP object does not have an id");
                    throw new IllegalArgumentException("CroppingPlan DUMP object does not have an id");
                }
                int paramIx = 1;
                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getId());
                stmt.setInt(paramIx++, ps.getId());

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCropRotationGroupId());
                stmt.setInt(paramIx++, ps.getCropRotationGroupId());

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLocationId());
                stmt.setInt(paramIx++, ps.getLocationId());

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getYearOfPlan());
//                stmt.setDate(paramIx++, Date.valueOf(ps.getYearOfPlan().atDay(1)), java.util.Calendar.getInstance()); //  2.3.0
                if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MySQL ||
                        DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MariaDB)
                {
                    // Passing a simple Date value throws SQLException 'data truncated' (i.e. truncating 2021-01-01T00:00.00 to 2021)
                    // see https://stackoverflow.com/questions/66411364/how-to-insert-year-data-type-in-mysql-server-using-java
                    // answer from Basil Borque
                    stmt.setInt(paramIx++, ps.getYearOfPlan().getValue());
                }
                else
                {
                    stmt.setDate(paramIx++, Date.valueOf(ps.getYearOfPlan().atDay(1)), java.util.Calendar.getInstance());
                }

                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLastUpdated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getLastUpdated()));
                LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCreated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getCreated()));

                stmt.executeUpdate();

                if (!ps.getComments().isEmpty())
                {
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT croppingplan OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.CROPPINGPLAN, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT croppingplan ON");
                    }
                }

                if (++txCount > 50)
                {
                    conn.commit();
                    txCount = 0;
                }
            }
            conn.commit();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT croppingplan 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());
        }
    }	// restoreJsonDump(JsonObjects)

}
