/*
 *
 *  Copyright (C) 2023, 2024 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.2.1   Use DBKeyHandler for all options
 *          Support selection by Location
 *
 */

package uk.co.gardennotebook.mysql;

import org.apache.logging.log4j.message.EntryMessage;
import uk.co.gardennotebook.spi.GNDBException;
import uk.co.gardennotebook.spi.ILifecycleAnalysis;
import uk.co.gardennotebook.spi.ILifecycleAnalysisLister;

import java.util.*;

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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;

/**
 *
 *{@inheritDoc}
 *
 *	@author	Andy Gegg
 *	@version	3.2.1
 *	@since	3.2.0
 */
final class LifecycleAnalysisLister implements ILifecycleAnalysisLister
{
    private static final Logger LOGGER = LogManager.getLogger();

    private final DBKeyHandler<IPlantSpecies> usePlantSpecies = new DBKeyHandler<>("plantSpeciesId");
    private final DBKeyHandler<IPlantVariety> usePlantVariety = new DBKeyHandler<>("plantVarietyId");
    private final DBKeyHandler<ILocation> useLocation = new DBKeyHandler<>("locationId");

    private boolean useFromDate = false;
    private LocalDate fromDate;
    private boolean useToDate = false;
    private LocalDate toDate;

    private boolean useWhere = false;


    @Override
    public List<ILifecycleAnalysis> fetch() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetch(): usePlantSpecies: {}, usePlantVariety: {}, useLocation: {}", usePlantSpecies.isUsed(), usePlantVariety.isUsed(), useLocation.isUsed());

        if (!useLocation.isUsed() && !usePlantSpecies.isUsed() && !usePlantVariety.isUsed())
        {
            return Collections.emptyList();
        }

        List<ILifecycleAnalysis> vals = new ArrayList<>();

        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement(); )
        {
            conn.setAutoCommit(false);
            switch (DBConnection.DB_IN_USE)
            {
                case MariaDB, MySQL -> vals = runQuery_MySQL(stmt);
                case hsqldb -> vals = runQuery_hsqldb(stmt);
                case MSSQLServer -> vals = runQuery_MSSQLServer(stmt);
                default -> {
                    LOGGER.error("fetch(): no known rdbms");
                    throw new GNDBException(new IllegalStateException("HusbandryLister: fetch(): no known RDBMS"));
                }
            }
            conn.commit();
        }
        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();

        populateAfflictionEvent(vals);

        populateHusbandry(vals);

        populateGroundwork(vals);

        LOGGER.traceExit(log4jEntryMsg);
        return vals;
    }

    private List<ILifecycleAnalysis> runQuery_hsqldb(Statement stmt) throws SQLException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("runQuery_hsqldb()");

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

        if (useLocation.isUsed())
        {
            String query_loc_1 ="declare local temporary table locns(locationId INTEGER) on commit preserve rows;";
            LOGGER.debug("query_loc_1: {}", query_loc_1);
            stmt.execute(query_loc_1);
            String subQuery = "values(";
            for (int lcn : useLocation.getIds())
            {
                subQuery += " row(" + lcn + "), ";
            }
            subQuery = subQuery.substring(0, subQuery.length() - 2);
            subQuery += " )";
            String query_loc ="insert into locns(locationId) with recursive locrec (locationId) as (" + subQuery +
                    " union select locationId from location, locrec where locrec.locationId = location.parentLocationId) select locationId from locrec;";
            LOGGER.debug("query_loc: {}", query_loc);
            stmt.execute(query_loc);
            LOGGER.debug("after query_loc");
        }

        //  create a temporary table and populate it with HU, GW and AE for the requested plant species/varieties.
        String query_1 = "declare local temporary table husb (id INTEGER, type varchar(3), plantSpeciesId INTEGER, plantVarietyId INTEGER, locationId INTEGER, targetLocation BOOLEAN DEFAULT FALSE, date DATE) on commit preserve rows;";
        LOGGER.debug("query_1: {}", query_1);
        stmt.executeQuery(query_1);

        boolean first = true;
        StringBuilder query_2 = new StringBuilder("insert into husb ");
        query_2.append(" (select husbandryId, 'HU', plantSpeciesId, plantVarietyId, locationId, FALSE, date from husbandry as d ");

        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d.locationId in (select locationId from locns) ");
            first = false;
        }

        first = plantSubquery(query_2, first, "d");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date <= '").append(this.toDate).append("'");
            first = false;
        }

        first = true;
        query_2.append(" union select groundworkId, 'GW', plantSpeciesId, plantVarietyId, locationId, FALSE, date from groundwork as d1 ");
        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d1.locationId in (select locationId from locns) ");
            first = false;
        }
        first = plantSubquery(query_2, first, "d1");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d1.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d1.date <= '").append(this.toDate).append("'");
            first = false;
        }

        first = true;
        query_2.append(" union select afflictionEventId, 'AE', plantSpeciesId, plantVarietyId, locationId, FALSE, date from afflictionevent as d2 ");
        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d2.locationId in (select locationId from locns) ");
            first = false;
        }
        first = plantSubquery(query_2, first, "d2");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d2.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d2.date <= '").append(this.toDate).append("'");
            first = false;
        }

        query_2.append(");");
        this.useToDate = false;
        this.useFromDate = false;

        LOGGER.debug("query_2: {}", query_2.toString());
        stmt.executeQuery(query_2.toString());

        //  now get all the story line entries with any of the entries in the previous table.
        //  Exclude previous Purchases.  Using the descendantId/Type excludes any Sales.
        String query_3= "declare local temporary table story (ancestorId integer, ancestorType varchar(3), descendantId integer, descendantType varchar(3), depth integer) on commit preserve rows;";
        LOGGER.debug("query_3: {}", query_3);
        stmt.executeQuery(query_3);

        //  Because PS/PV are necessarily in every entry in a storyline, analysis by PS/PV only will now have the complete tree for each storyline and
        //      query_3a below is all we need.
        //  If the analysis is for a location this is not true - seeds are sown in one place, potted up in another then planted out in yet another
        //      meaning we've only picked up odd fragments of each tree.  For a history with A->B->C->X, say, query_3a below will get the AX, BX, CX
        //      entries but not AB,BC, etc.  However, the A, B, C are NOT necessarily in husb so don't get into the final select.
        String query_3a = "";
        if (!useLocation.isUsed())
        {// PS/PV only
            query_3a = "insert into story ";
            query_3a += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where" +
                    " (descendantId, descendantType) in (select id, type from husb) and ancestorType != 'PI' );";
            LOGGER.debug("query_3a: {}", query_3a);
            stmt.executeQuery(query_3a);
        }
        else
        {// locations used
            query_3a = "insert into story ";
            query_3a += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where " +
                    " ( (descendantId, descendantType) in (select id, type from husb) " +
                    " OR (ancestorId,ancestorType) in (select id, type from husb) ) " +
                    " AND ancestorType != 'PI' );";
            LOGGER.debug("query_3a: {}", query_3a);
            stmt.executeQuery(query_3a);

            LOGGER.debug("about to delete (husb)");
            stmt.execute("delete from husb;");

            String query_3b = "insert into husb " +
                    " (select distinct husbandryId, 'HU', plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from husbandry as d " +
                    " where (d.husbandryId in (select descendantId from story where descendantType = 'HU')) OR" +
                    " (d.husbandryId in (select ancestorId from story where ancestorType = 'HU')) )";
            LOGGER.debug("query_3b: {}", query_3b);
            stmt.executeQuery(query_3b);

            String query_3b2 = "insert into husb " +
                    " (select distinct groundworkId, 'GW', plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from groundwork as d " +
                    " where (d.groundworkId in (select descendantId from story where descendantType = 'GW')) OR" +
                    " (d.groundworkId in (select ancestorId from story where ancestorType = 'GW')) )";
            LOGGER.debug("query_3b2: {}", query_3b2);
            stmt.executeQuery(query_3b2);

            String query_3b3= "insert into husb " +
                    " (select distinct afflictionEventId, 'AE', plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from afflictionevent as d " +
                    " where (d.afflictionEventId in (select descendantId from story where descendantType = 'AE')) OR" +
                    " (d.afflictionEventId in (select ancestorId from story where ancestorType = 'AE')) )";
            LOGGER.debug("query_3b3: {}", query_3b3);
            stmt.executeQuery(query_3b3);

            LOGGER.debug("about to delete (story)");
            stmt.execute("delete from story;");

            String query_3c = "insert into story ";
            query_3c += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where" +
                    " (descendantId, descendantType) in (select id, type from husb) and ancestorType != 'PI' );";
            LOGGER.debug("query_3c: {}", query_3c);
            stmt.executeQuery(query_3c);
        }

        // We only want a single thread for each story line, starting from the earliest entry and finding each descendant recursively.
        //  There can be side branches (e.g. old entries from the flat file!) such as GW and AE entries which won't necessarily be parents of later HU.
        //  Start by getting everything which is a child
        String query_4 = "declare local temporary table child (descendantId integer, descendantType varchar(3));";
        LOGGER.debug("query_4: {}", query_4);
        stmt.executeQuery(query_4);
        String query_4a = "insert into child (select distinct descendantId, descendantType from  story where depth>0);";
        LOGGER.debug("query_4a: {}", query_4a);
        stmt.executeQuery(query_4a);

        //  now remove all those child entries
        String query_5 = "delete from story where (descendantId, descendantType) in (select descendantId, descendantType from child);";
        LOGGER.debug("query_5: {}", query_5);
        stmt.executeQuery(query_5);

        //  Now get the storylines for all the parent entries
        String query_6 = "declare local temporary table story2 (ancestorId integer, ancestorType varchar(3), descendantId integer, descendantType varchar(3), depth integer);";
        LOGGER.debug("query_6: {}", query_6);
        stmt.executeQuery(query_6);
        String query_6a = "insert into story2 (select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where" +
                " (ancestorId, ancestorType) in (select ancestorId, ancestorType from story));";
        LOGGER.debug("query_6a: {}", query_6a);
        stmt.executeQuery(query_6a);

        // the LEFT JOIN is to make sure stand-alone entries with NO entry in StoryLine are still passed through.
        String query_7 = "select husb.*, story2.* from husb left join story2 on (husb.type=story2.descendantType AND husb.id=story2.descendantId) order by story2.ancestorId, story2.ancestorType, story2.depth, husb.date;";
        LOGGER.debug("query_7: {}", query_7);

        ResultSet rs = stmt.executeQuery(query_7);

        tempList = processResults_hsqldb(rs);

        stmt.executeQuery("commit;");

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

    private boolean plantSubquery(StringBuilder query, boolean first, String alias)
    {
        if (usePlantSpecies.isUsed() || usePlantVariety.isUsed())
        {
            if (first)
                query.append(" where ");
            else
                query.append(" and ");

            if (usePlantSpecies.isUsed() && usePlantVariety.isUsed())
                query.append(" ( ");

            if (usePlantSpecies.isUsed())
            {
                query.append(usePlantSpecies.getSQLClause(alias));
                first = false;
            }

            if (usePlantSpecies.isUsed() && usePlantVariety.isUsed())
                query.append(" or ");
            if (usePlantVariety.isUsed())
            {
                query.append(usePlantVariety.getSQLClause(alias));
                first = false;
            }
            if (usePlantSpecies.isUsed() && usePlantVariety.isUsed())
                query.append(" ) ");
        }
        return first;
    }

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

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

        while (rs.next())
        {
            LOGGER.debug("in loop");
            int ancestorId = rs.getInt("story2.ancestorId");
            NotebookEntryType childType = NotebookEntryType.fromString(rs.getString("husb.type"));
            NotebookEntryType ancestorType = NotebookEntryType.fromString(rs.getString("story2.ancestorType"));

            int plantSpeciesId = rs.getInt("husb.plantSpeciesId");
            int tmp_plantVarietyId = rs.getInt("husb.plantVarietyId");
            int plantVarietyId = rs.wasNull() ? 0 : tmp_plantVarietyId;
            int husbandryId = 0;
            int afflictionEventId = 0;
            int groundworkId = 0;
            switch (childType)
            {
                case HUSBANDRY ->  husbandryId = rs.getInt("husb.id");
                case AFFLICTIONEVENT -> afflictionEventId = rs.getInt("husb.id");
                case GROUNDWORK -> groundworkId = rs.getInt("husb.id");
                default -> {
                    LOGGER.debug("unknown descendant type: {}", childType);
                    return Collections.emptyList();
                }
            }
            int locationId = rs.getInt("husb.locationId");
            boolean targetLocation = rs.getBoolean("husb.targetLocation");
            LocalDate date = rs.getDate("husb.date").toLocalDate();

            LOGGER.debug("childType: {}, ancestorId: {}, ancestorType: {}, husbandryId: {}, plantSpeciesId: {}, plantVarietyId: {}, afflictionEventId: {}, groundworkId: {}, locationId: {}, targetLocation: {}, date: {}",
                    childType, ancestorId, ancestorType, husbandryId, plantSpeciesId, plantVarietyId, afflictionEventId, groundworkId, locationId, targetLocation, date);
            tempList.add(new LifecycleAnalysis(date, ancestorId, ancestorType, plantSpeciesId, plantVarietyId, childType, husbandryId, afflictionEventId, groundworkId, locationId, targetLocation));
        }

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

    private List<ILifecycleAnalysis> runQuery_MySQL(Statement stmt) throws SQLException
    {
        // the 'create table' commands specify the field details to avoid indexes and constraints being copied - particularly 'non null' on plantSpeciesId

        EntryMessage log4jEntryMsg = LOGGER.traceEntry("runQuery_MySQL()");

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

        if (useLocation.isUsed())
        {
            String subQuery = "values ";
            for (int lcn : useLocation.getIds())
            {
                subQuery += " row(" + lcn + "), ";
            }
            subQuery = subQuery.substring(0, subQuery.length() - 2);
            String query_loc = STR."create temporary table locns(locationId INTEGER) as (with recursive locrec (locationId) as ( \{subQuery} union all select location.locationId from location, locrec where locrec.locationId = location.parentlocationid) select locationId from locrec);";
            LOGGER.debug("query_loc: {}", query_loc);
            stmt.execute(query_loc);
        }

        boolean first = true;
        StringBuilder query_2 = new StringBuilder("create temporary table husb(id INTEGER, type VARCHAR(3), plantSpeciesId INTEGER, plantVarietyId INTEGER, " +
                "locationId INTEGER, targetLocation BOOLEAN, date DATE) as ");
        query_2.append(" (select husbandryId as id, 'HU' as type, plantSpeciesId, plantVarietyId, locationId, FALSE as targetLocation, date from husbandry as d ");

        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d.locationId in (select locationId from locns) ");
            first = false;
        }

        first = plantSubquery(query_2, first, "d");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date <= '").append(this.toDate).append("'");
            first = false;
        }

        query_2.append(");");

        LOGGER.debug("query_2: {}", query_2.toString());
        stmt.execute(query_2.toString());

        first = true;
        query_2 = new StringBuilder("insert into husb (select groundworkId as id, 'GW' as type, plantSpeciesId, plantVarietyId, locationId, FALSE, date from groundwork as d ");

        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d.locationId in (select locationId from locns) ");
            first = false;
        }

        first = plantSubquery(query_2, first, "d");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date <= '").append(this.toDate).append("'");
            first = false;
        }

        query_2.append(");");

        LOGGER.debug("query_2 (bis): {}", query_2.toString());
        stmt.execute(query_2.toString());

        first = true;
        query_2 = new StringBuilder("insert into husb (select afflictionEventId as id, 'AE' as type, plantSpeciesId, plantVarietyId, locationId, FALSE, date from afflictionevent as d ");

        if (useLocation.isUsed())
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");
            query_2.append("d.locationId in (select locationId from locns) ");
            first = false;
        }

        first = plantSubquery(query_2, first, "d");

        if (this.useFromDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date >= '").append(this.fromDate).append("'");
            first = false;
        }
        if (this.useToDate)
        {
            if (first) query_2.append(" where ");
            else query_2.append(" and ");

            query_2.append(" d.date <= '").append(this.toDate).append("'");
            first = false;
        }

        query_2.append(");");
        this.useToDate = false;
        this.useFromDate = false;

        LOGGER.debug("query_2 (tri): {}", query_2.toString());
        stmt.execute(query_2.toString());

        //  Because PS/PV are necessarily in every entry in a storyline analysis by PS/PV will now have the complete tree for each storyline and
        //      query_3a below is all we need.
        //  If the analysis is for a location this is not true - seeds are sown in one place, potted up in another then planted out in yet another
        //      meaning we've only picked up odd fragments of each tree.  For a history with A->B->C->X, say, query_3a below will get the AX, BX, CX
        //      entries but not AB,BC, etc.  However, the A, B, C are NOT necessarily in husb so don't get into the final select.
        String query_3a = "";
        if (!useLocation.isUsed())
        {
            query_3a = "create temporary table story as ";
            query_3a += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex" +
                    " where (descendantId, descendantType) in (select id, type from husb) and ancestorType != \"PI\" );";
            LOGGER.debug("query_3a: {}", query_3a);
            stmt.execute(query_3a);
        }
        else
        {// using Location
            query_3a = "create temporary table story as ";
            query_3a += "(select distinct sli.ancestorId, sli.ancestorType, sli.descendantId, sli.descendantType, depth from storylineindex as sli join husb ON " +
                    " ((sli.descendantId = husb.id AND sli.descendantType = husb.type) OR " +
                    " (sli.ancestorId = husb.id AND sli.ancestorType = husb.type)) AND sli.ancestorType != \"PI\" );";
            LOGGER.debug("query_3a: {}", query_3a);
            stmt.execute(query_3a);

            LOGGER.debug("about to delete (husb)");
            stmt.execute("delete from husb;");

            String query_3b = "insert into husb " +
                    " (select distinct husbandryId, \"HU\", plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from husbandry as d join story as sli ON (" +
                    " (d.husbandryId = sli.descendantId AND sli.descendantType = \"HU\") OR" +
                    " (d.husbandryId = sli.ancestorId AND sli.ancestorType = \"HU\")) )";
            LOGGER.debug("query_3b: {}", query_3b);
            stmt.execute(query_3b);

            String query_3b2 = "insert into husb " +
                    " (select distinct groundworkId, \"GW\", plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from groundwork as d join story as sli ON (" +
                    " (d.groundworkId = sli. descendantId AND sli.descendantType = \"GW\") OR" +
                    " (d.groundworkId = sli.ancestorId AND sli.ancestorType = \"GW\")) )";
            LOGGER.debug("query_3b2: {}", query_3b2);
            stmt.execute(query_3b2);

            String query_3b3= "insert into husb " +
                    " (select distinct afflictionEventId, \"AE\", plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from locns)), date from afflictionevent as d join story as sli ON (" +
                    " (d.afflictionEventId = sli.descendantId AND sli.descendantType = \"AE\") OR" +
                    " (d.afflictionEventId = sli.ancestorId AND sli.ancestorType = \"AE\")) )";
            LOGGER.debug("query_3b3: {}", query_3b3);
            stmt.execute(query_3b3);

            LOGGER.debug("about to delete (story)");
            stmt.execute("delete from story;");

            String query_3c = "insert into story ";
            query_3c += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where" +
                    " (descendantId, descendantType) in (select id, type from husb) and ancestorType != \"PI\" );";
            LOGGER.debug("query_3c: {}", query_3c);
            stmt.execute(query_3c);
        }

        String query_4a = "create temporary table child as (select distinct descendantId, descendantType from  story where depth>0);";
        LOGGER.debug("query_4a: {}", query_4a);
        stmt.execute(query_4a);

        String query_5 = "delete from story where (descendantId, descendantType) in (select descendantId, descendantType from child);";
        LOGGER.debug("query_5: {}", query_5);
        stmt.execute(query_5);

        String query_6a = "create temporary table story2 as ";
        query_6a += "(select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex where (ancestorId, ancestorType) in (select ancestorId, ancestorType from story));";
        LOGGER.debug("query_6a: {}", query_6a);
        stmt.execute(query_6a);

        // the LEFT JOIN is to make sure stand-alone entries with NO entry in StoryLine are still passed through.
        String query_7 = "select husb.*, story2.* from husb left join story2 on (husb.type=story2.descendantType AND husb.id=story2.descendantId) order by story2.ancestorId, story2.ancestorType, story2.depth, husb.date;";
        LOGGER.debug("query_7: {}", query_7);

        ResultSet rs = stmt.executeQuery(query_7);

        tempList = processResults_hsqldb(rs);

        LOGGER.debug("before commit");
        stmt.execute("commit;");

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

    private List<ILifecycleAnalysis> runQuery_MSSQLServer(Statement stmt) throws SQLException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("runQuery_MSSQLServer()");

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

        try
        {
            if (useLocation.isUsed())
            {
                //  in SQLServer the UNION ALL indicates a recursive common table expression (WITH clause)
                String subQuery = " (values ";
                for (int lcn : useLocation.getIds())
                {
                    subQuery += " (" + lcn + "), ";
                }
                subQuery = subQuery.substring(0, subQuery.length() - 2);
                subQuery += " )";
                String query_loc ="with locrec (locationId) as (select * from " + subQuery + " as d(id)" +
                        " union all select location.locationId from location, locrec where locrec.locationId = location.parentlocationid) select locationId into #locns from locrec;";
                LOGGER.debug("query_loc: {}", query_loc);
                stmt.execute(query_loc);
                LOGGER.debug("after query_loc");
            }

            String query_1 = "create table #husb(id INTEGER, type VARCHAR(3), plantSpeciesId INTEGER, plantVarietyId INTEGER, locationId INTEGER, targetLocation BIT, date DATE);";
            LOGGER.debug("query_1: {}", query_1);
            stmt.execute(query_1);

            boolean first = true;
            StringBuilder query_2 = new StringBuilder(" insert into #husb select husbandryId as id, 'HU' as type, plantSpeciesId, plantVarietyId, locationId, 0, date from husbandry as d ");

            if (useLocation.isUsed())
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");
                query_2.append("d.locationId in (select locationId from #locns) ");
                first = false;
            }
            first = plantSubquery(query_2, first, "d");

            if (this.useFromDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d.date >= '").append(this.fromDate).append("'");
                first = false;
            }
            if (this.useToDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d.date <= '").append(this.toDate).append("'");
                first = false;
            }

            first = true;
            query_2.append(" union select groundworkId as id, 'GW' as type, plantSpeciesId, plantVarietyId, locationId, 0, date from groundwork as d1 ");
            if (useLocation.isUsed())
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");
                query_2.append("d1.locationId in (select locationId from #locns) ");
                first = false;
            }
            first = plantSubquery(query_2, first, "d1");

            if (this.useFromDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d1.date >= '").append(this.fromDate).append("'");
                first = false;
            }
            if (this.useToDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d1.date <= '").append(this.toDate).append("'");
                first = false;
            }

            first = true;
            query_2.append(" union select afflictionEventId as id, 'AE' as type, plantSpeciesId, plantVarietyId, locationId, 0, date from afflictionevent as d2 ");
            if (useLocation.isUsed())
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");
                query_2.append("d2.locationId in (select locationId from #locns) ");
                first = false;
            }
            first = plantSubquery(query_2, first, "d2");

            if (this.useFromDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d2.date >= '").append(this.fromDate).append("'");
                first = false;
            }
            if (this.useToDate)
            {
                if (first) query_2.append(" where ");
                else query_2.append(" and ");

                query_2.append(" d2.date <= '").append(this.toDate).append("'");
                first = false;
            }

            query_2.append(";");
            this.useToDate = false;
            this.useFromDate = false;

            LOGGER.debug("query_2: {}", query_2.toString());
            stmt.execute(query_2.toString());

            String query_3 = "create table #story (ancestorId integer, ancestorType varchar(3), descendantId integer, descendantType varchar(3), depth integer);";
            LOGGER.debug("query_3: {}", query_3);
            stmt.execute(query_3);

            String query_3a = "";
            if (!useLocation.isUsed())
            {// PS/PV only
                query_3a = "insert into #story ";
                query_3a += "select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex as sli join #husb as h on" +
                        " sli.descendantId = h.id and sli.descendantType = h.type and ancestorType != 'PI';";
                LOGGER.debug("query_3a: {}", query_3a);
                stmt.execute(query_3a);
            }
            else
            {// locations used
                query_3a = "insert into #story ";
                query_3a += "select distinct sli.ancestorId, sli.ancestorType, sli.descendantId, sli.descendantType, depth from storylineindex as sli join #husb ON " +
                        " ((sli.descendantId = #husb.id AND sli.descendantType = #husb.type) OR " +
                        " (sli.ancestorId = #husb.id AND sli.ancestorType = #husb.type)) AND sli.ancestorType != 'PI' ;";
                LOGGER.debug("query_3a: {}", query_3a);
                stmt.execute(query_3a);

                LOGGER.debug("about to delete (#husb)");
                stmt.execute("delete from #husb;");

                String query_3b = "insert into #husb " +
                        " select distinct husbandryId, 'HU', plantSpeciesId, plantVarietyId, locationId, " +
//                        "(locationId in (select * from #locns)), " +
                        " 0, " +
                        "date from husbandry as d join #story as sli ON (" +
                        " (d.husbandryId = sli.descendantId AND sli.descendantType = 'HU') OR" +
                        " (d.husbandryId = sli.ancestorId AND sli.ancestorType = 'HU')) ";
                LOGGER.debug("query_3b: {}", query_3b);
                stmt.execute(query_3b);

                String query_3b2 = "insert into #husb " +
//                        " select distinct groundworkId, 'GW', plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from #locns)), date from groundwork as d join #story as sli ON (" +
                        " select distinct groundworkId, 'GW', plantSpeciesId, plantVarietyId, locationId, 0, date from groundwork as d join #story as sli ON (" +
                        " (d.groundworkId = sli. descendantId AND sli.descendantType = 'GW') OR" +
                        " (d.groundworkId = sli.ancestorId AND sli.ancestorType = 'GW')) ";
                LOGGER.debug("query_3b2: {}", query_3b2);
                stmt.execute(query_3b2);

                String query_3b3= "insert into #husb " +
//                        " select distinct afflictionEventId, 'AE', plantSpeciesId, plantVarietyId, locationId, (locationId in (select * from #locns)), date from afflictionevent as d join #story as sli ON (" +
                        " select distinct afflictionEventId, 'AE', plantSpeciesId, plantVarietyId, locationId, 0, date from afflictionevent as d join #story as sli ON (" +
                        " (d.afflictionEventId = sli.descendantId AND sli.descendantType = 'AE') OR" +
                        " (d.afflictionEventId = sli.ancestorId AND sli.ancestorType = 'AE')) ";
                LOGGER.debug("query_3b3: {}", query_3b3);
                stmt.execute(query_3b3);

                String query_3b4 = "update #husb set targetLocation = 1 where locationId in (select * from #locns)";
                LOGGER.debug("query_3b4: {}", query_3b4);
                stmt.execute(query_3b4);

                LOGGER.debug("about to delete (#story)");
                stmt.execute("delete from #story;");

                String query_3c = "insert into #story ";
                query_3c += "select ancestorId, ancestorType, descendantId, descendantType, depth from storylineindex join #husb on " +
                        " (descendantId = #husb.id and descendantType = #husb.type) and ancestorType != 'PI' ;";
                LOGGER.debug("query_3c: {}", query_3c);
                stmt.execute(query_3c);
            }

            String query_4 = "create table #child (descendantId integer, descendantType varchar(3));";
            LOGGER.debug("query_4: {}", query_4);
            stmt.execute(query_4);

            String query_4a = "insert into #child select distinct descendantId, descendantType from  #story where depth>0";
            LOGGER.debug("query_4a: {}", query_4a);
            stmt.execute(query_4a);

            String query_5 = "delete s from #story as s inner join #child as c on s.descendantId = c.descendantId and s.descendantType = c.descendantType;";
            LOGGER.debug("query_5: {}", query_5);
            stmt.execute(query_5);

            String query_6 = "create table #story2 (ancestorId integer, ancestorType varchar(3), descendantId integer, descendantType varchar(3), depth integer);";
            LOGGER.debug("query_6: {}", query_6);
            stmt.execute(query_6);

            String query_6a = "insert into #story2 select sli.ancestorId, sli.ancestorType, sli.descendantId, sli.descendantType, sli.depth from storylineindex as sli join #story as sty on sli.ancestorId = sty.ancestorId and sli.ancestorType = sty.ancestorType;";
            LOGGER.debug("query_6a: {}", query_6a);
            stmt.execute(query_6a);

            // the LEFT JOIN is to make sure stand-alone entries with NO entry in StoryLine are still passed through.
            String query_7 = "select #husb.id as d_id, #husb.type as d_type, #husb.plantSpeciesId as d_plantSpeciesId, #husb.plantVarietyId as d_plantVarietyId," +
                            " #husb.locationId as d_locationId, #husb.targetLocation as d_targetLocation, #husb.date as d_date, " +
                            " #story2.ancestorId as d_ancestorId, #story2.ancestorType as d_ancestorType " +
                            " from #husb left join #story2 on (#husb.type=#story2.descendantType AND #husb.id=#story2.descendantId) order by #story2.ancestorId, #story2.ancestorType, #story2.depth, #husb.date;";
            LOGGER.debug("query_7: {}", query_7);

            ResultSet rs = stmt.executeQuery(query_7);

            tempList = processResults_MSSQLServer(rs);
        } catch (SQLException sq)
        {
            LOGGER.debug("-----------------------------------\nSQLException thrown\n______________________");
            throw sq;
        }

        LOGGER.debug("before commit");
        stmt.execute("commit;");

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

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

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

        while (rs.next())
        {
            LOGGER.debug("in loop");
            int ancestorId = rs.getInt("d_ancestorId");
            NotebookEntryType childType = NotebookEntryType.fromString(rs.getString("d_type"));
            NotebookEntryType ancestorType = NotebookEntryType.fromString(rs.getString("d_ancestorType"));

            int plantSpeciesId = rs.getInt("d_plantSpeciesId");
            int tmp_plantVarietyId = rs.getInt("d_plantVarietyId");
            int plantVarietyId = rs.wasNull() ? 0 : tmp_plantVarietyId;
            int husbandryId = 0;
            int afflictionEventId = 0;
            int groundworkId = 0;
            switch (childType)
            {
                case HUSBANDRY ->  husbandryId = rs.getInt("d_id");
                case AFFLICTIONEVENT -> afflictionEventId = rs.getInt("d_id");
                case GROUNDWORK -> groundworkId = rs.getInt("d_id");
                default -> {
                    LOGGER.debug("unknown descendant type: {}", childType);
                    return Collections.emptyList();
                }
            }
            int locationId = rs.getInt("d_locationId");
            boolean targetLocation = rs.getBoolean("d_targetLocation");
            LocalDate date = rs.getDate("d_date").toLocalDate();

            LOGGER.debug("childType: {}, ancestorId: {}, ancestorType: {}, husbandryId: {}, plantSpeciesId: {}, plantVarietyId: {}, afflictionEventId: {}, groundworkId: {}, " +
                            "locationId: {}, targetLocation: {}, date: {}",
                    childType, ancestorId, ancestorType, husbandryId, plantSpeciesId, plantVarietyId, afflictionEventId, groundworkId, locationId, targetLocation, date);
            tempList.add(new LifecycleAnalysis(date, ancestorId, ancestorType, plantSpeciesId, plantVarietyId, childType, husbandryId, afflictionEventId, groundworkId, locationId, targetLocation));
        }

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

    private void populateHusbandry(List<ILifecycleAnalysis> vals) throws GNDBException
    {
        int [] ids = vals.stream().filter(lca -> lca.childType() == NotebookEntryType.HUSBANDRY).mapToInt(ILifecycleAnalysis::husbandryId).toArray();
        new HusbandryLister().id(ids).fetch();
    }

    private void populateAfflictionEvent(List<ILifecycleAnalysis> vals) throws GNDBException
    {
        int [] ids = vals.stream().filter(lca -> lca.childType() == NotebookEntryType.AFFLICTIONEVENT).mapToInt(ILifecycleAnalysis::afflictionEventId).toArray();
        new AfflictionEventLister().id(ids).fetch();
    }

    private void populateGroundwork(List<ILifecycleAnalysis> vals) throws GNDBException
    {
        int [] ids = vals.stream().filter(lca -> lca.childType() == NotebookEntryType.GROUNDWORK).mapToInt(ILifecycleAnalysis::groundworkId).toArray();
        new GroundworkLister().id(ids).fetch();
    }

    @Override
    public ILifecycleAnalysisLister plantSpecies(IPlantSpecies... items)
    {
        usePlantSpecies.item(items);
        useWhere = useWhere || usePlantSpecies.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister plantSpecies(List<IPlantSpecies> items)
    {
        usePlantSpecies.item(items);
        useWhere = useWhere || usePlantSpecies.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister plantVariety(IPlantVariety... items)
    {
        usePlantVariety.item(items);
        useWhere = useWhere || usePlantVariety.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister plantVariety(List<IPlantVariety> items)
    {
        usePlantVariety.item(items);
        useWhere = useWhere || usePlantVariety.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister location(ILocation... items)
    {
        useLocation.item(items);
        useWhere = useWhere || useLocation.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister location(List<ILocation> items)
    {
        useLocation.item(items);
        useWhere = useWhere || useLocation.isUsed();
        return this;
    }

    @Override
    public ILifecycleAnalysisLister fromDate(LocalDate date)
    {
        if (date == null) return this;
        this.fromDate = date;
        this.useFromDate = true;
        this.useWhere = true;
        return this;
    }

    @Override
    public ILifecycleAnalysisLister toDate(LocalDate date)
    {
        if (date == null) return this;
        this.toDate = date;
        this.useToDate = true;
        this.useWhere = true;
        return this;
    }

}
