/*
 *
 *  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.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import uk.co.gardennotebook.spi.*;

import jakarta.json.*;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

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

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

    private boolean useId = false;
    private int[] idList = new int[10];
    private int idNext = 0;	// next free slot in list

    private boolean useParentLocationId = false;
    private int[] parentLocationList = new int[10];
    private int parentLocationNext = 0;
    private boolean forceTop = false;

    private boolean useWhere = false;

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

        if (MySQLCache.invalidLocation)
        {
            load();
        }

        final boolean onlyTop = forceTop;
        if(!useId && !useName)
        {
            resetCriteria();
            return MySQLCache.cacheLocation.values().stream().
                    filter(t -> !(onlyTop && t.hasParent())).
                    sorted((a, b) -> a.getName().compareToIgnoreCase(b.getName())).collect(Collectors.toList());
        }

        List<ILocation> outList = new ArrayList<>();

        mergeLists();

        for (int i : idList)
        {
            outList.add(MySQLCache.cacheLocation.get(i));
        }
        resetCriteria();

        LOGGER.traceExit(log4jEntryMsg);
        return outList.stream().
                filter(t -> !(onlyTop && t.hasParent())).
                sorted((a,b) -> a.getName().compareToIgnoreCase(b.getName())).collect(Collectors.toList());
    }

    private void resetCriteria()
    {
        useId = false;
        idNext = 0;
        useName = false;
        nameNext = 0;
        forceTop = false;
    }

    /*
    *   merge the id values of the useName and useId lists
     */
    private void mergeLists()
    {
        if (!useId && !useName) return ;

        idList = Arrays.copyOf(idList, idNext);

        if (useName)
        {
            nameList = Arrays.copyOf(nameList, nameNext);
            int [] nameKeys = MySQLCache.cacheLocation.values().stream().
                    filter(c -> Arrays.stream(nameList).anyMatch(p -> p.equals(c.getName())))
                    .mapToInt(INotebookEntry::getKey).
                            toArray();
            idList = IntStream.concat(Arrays.stream(nameKeys), Arrays.stream(idList)).toArray();
        }

        idList = Arrays.stream(idList).distinct().toArray();
    }

    @Override
    public List<? extends ILocation> fetchSubLocations() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetchSubLocations()");

        if (MySQLCache.invalidLocation)
        {
            load();
        }

        if (!useId && !useName)
        {
            resetCriteria();
            LOGGER.debug("no selection of Location values");
            // the effect of no selection is to return everything except the top level Locations
            return MySQLCache.cacheLocation.values().stream().
                    filter(ILocation::hasParent).
                    collect(Collectors.toList());
        }

        mergeLists();

        List<Integer> requestList = new ArrayList<>(idList.length);
        for (final int ix : idList)
        {
            requestList.add(ix);
        }

        resetCriteria();

        return MySQLCache.cacheLocation.values().stream().
                filter(ILocation::hasParent).
                filter(t -> requestList.contains(t.getParentLocation().get().getKey())).
                collect(Collectors.toList());
    }

    @Override
    public List<? extends ILocation> fetchDescendantLocations() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetchDescendantLocations()");

        //  first fetch the immediate children
        List<Integer> requestList = fetchSubLocations().stream().map(INotebookEntry::getKey).collect(Collectors.toList());
        List<Integer> targetList = new ArrayList<>(requestList);
        int numEntries = 0;

//        int ix = 0;
        while(targetList.size() > 0)
        {
            LOGGER.debug("in loop: requestList: {}, targetList: {}", requestList, targetList);
//            if (ix++ > 3)break;
            numEntries = requestList.size();
            final List<Integer> tempList = new ArrayList<>(targetList);
            targetList = MySQLCache.cacheLocation.values().stream().
                                    filter(ILocation::hasParent).
                                    filter(t -> tempList.contains(t.getParentLocation().get().getKey())).
                                    map(INotebookEntry::getKey).
                                    collect(Collectors.toList());
            requestList.addAll(targetList);
        }
        List<ILocation> result = new ArrayList<>(requestList.size());
        for (Integer ix : requestList)
        {
            result.add(MySQLCache.cacheLocation.get(ix));
        }
        return result;
    }   //  fetchDescendantLocations()

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

        if (MySQLCache.invalidLocation)
        {
            load();
        }

        if (!useId && !useName)
        {
            resetCriteria();
            LOGGER.debug("no selection of Location values");
            // the effect of no selection is to return everything except leaf Locations
            return MySQLCache.cacheLocation.values().stream().
                    filter(ILocation::hasParent).
                    map(t -> t.getParentLocation().orElse(null)).
                    filter(Objects::nonNull).
                    distinct().
                    collect(Collectors.toList());
        }

        mergeLists();

        List<Integer> requestList = new ArrayList<>(idList.length);
        for (final int ix : idList)
        {
            requestList.add(ix);
        }

        resetCriteria();

        List<ILocation> result = new ArrayList<>(requestList.size());
        for (Integer ix : requestList)
        {
            if (MySQLCache.cacheLocation.get(ix).hasParent())
                result.add(MySQLCache.cacheLocation.get(ix).getParentLocation().get());
        }
        return result;
    }

    void load() throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("load()");

        if (MySQLCache.invalidLocation)
        {
            List<Location> tempList = new ArrayList<>();

            String query = "";
            query = buildSQL();
            LOGGER.debug("load(): query: {}", query);
            try (Connection conn = DBConnection.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(query);)
            {
                tempList = processResults(rs);
            }
            catch (SQLException ex) {
                LOGGER.error("load(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
                throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
            }
            for (Location ps : tempList)
            {
                MySQLCache.cacheLocation.putIfAbsent(ps.getId(), ps);
            }
            MySQLCache.invalidLocation = false;
            MySQLCache.completeLocation = true;
        }

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

    private String buildSQL()
    {
        // Note the use of l.name in the 'order by' clause.  SQLServer will not accept l_name here, although hsqldb and MySQL are quite happy.
        final String query = """
                select l.locationId as l_locationId, l.parentLocationId as l_parentLocationId, l.name as l_name, l.description as l_description,
                    l.undercover as l_undercover, \
                 """
        + switch (DBConnection.DB_IN_USE)
                {
                    case MariaDB, MySQL -> "ST_AsText(l.geometry)";
                    case hsqldb -> "l.geometry";
                    case MSSQLServer -> "l.geometry.STAsText()";
                }
        + """
                 as l_geometry, l.lastUpdated as l_lastUpdated, l.created as l_created, c.* from location as l
                left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'LN') as c  
                on l.locationId = c_ownerId
                order by LOWER(l.name), c_date\
               """;
        return query;
    }

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

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

        Location item = null;

        while (rs.next())
        {
            int locationId = rs.getInt("l_locationId");
            int tmp_locationParentId = rs.getInt("l_parentlocationId");
            Integer parentlocationId = rs.wasNull() ? null : tmp_locationParentId;
            String name = rs.getString("l_name");
            String description = rs.getString("l_description");
            boolean underCover = rs.getBoolean("l_underCover");
            String geometry = rs.getString("l_geometry");
            LocalDateTime lastUpdated = rs.getTimestamp("l_lastUpdated").toLocalDateTime();
            LocalDateTime created = rs.getTimestamp("l_created").toLocalDateTime();
            LOGGER.debug("locationId: {}, parentlocationId: {}, name: {}, description: {}, underCover: {}, geometry: {}, lastUpdated: {}, created: {}",
                    locationId, parentlocationId, name, description, underCover, geometry, lastUpdated, created);
            if (item != null && locationId == item.getId())
            {// additional comment on the item
                LOGGER.debug("processResults(): got additional comment for: {}", item);
                Comment comm = new Comment(rs.getInt("c_commentId"),
                        rs.getInt("c_ownerId"),
                        NotebookEntryType.LOCATION.type(),
                        rs.getDate("c_date").toLocalDate(),
                        rs.getString("c_comment"),
                        rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
                        rs.getTimestamp("c_created").toLocalDateTime());
                LOGGER.debug("processResults(): extra comment is: {}", comm);
                item = new Location(item, comm);
            }
            else
            {
                if (item != null) tempList.add(item);
                int cid = rs.getInt("c_commentId");
                if (rs.wasNull())
                {// no comment
                    item = new Location(locationId, parentlocationId, name, description, underCover, geometry, lastUpdated, created);
                }
                else
                {// new item with comment
                    Comment comm = new Comment(cid,
                            locationId,
                            NotebookEntryType.LOCATION.type(),
                            rs.getDate("c_date").toLocalDate(),
                            rs.getString("c_comment"),
                            rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
                            rs.getTimestamp("c_created").toLocalDateTime());
                    LOGGER.debug("processResults_hsqldb(): first comment is: {}", comm);
                    item = new Location(locationId, parentlocationId, name, description, underCover, geometry, lastUpdated, created, comm);
                }
            }
        }
        if (item != null) tempList.add(item);

        LOGGER.traceExit(log4jEntryMsg);
        return tempList;
    }

    void clear()
    {
        MySQLCache.cacheLocation.clear();
        MySQLCache.invalidLocation = true;
        MySQLCache.completeLocation = false;
    }

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

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

    @Override
    public ILocationLister location(ILocation... vals)
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("key(varargs)");

        if (vals == null) return this;
        if (vals.length == 0) return this;
        int[] keys = new int[vals.length];
        int keyCount = 0;
        for (ILocation val : vals) {
            if (val == null) continue;
            Integer ky = val.getKey();
            if (ky == null) continue;
            keys[keyCount++] = ky;
        }
        if (keyCount == 0) return this;
        keys = Arrays.copyOf(keys, keyCount);	// trim array to actual size - should be a null-op
        return this.id(keys);
    }

    @Override
    public ILocationLister location(List<ILocation> items)
    {
        if (items == null) return this;
        if (items.isEmpty()) return this;
        return this.location(items.toArray(new ILocation[0]));
    }

    @Override
    public ILocationLister forceTopLevel()
    {
        forceTop = true;
        return this;
    }

    void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
    {
        if (MySQLCache.invalidLocation)
        {
            load();
        }

        JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
        //  we need a tree-order list of Locations so that mothers are always before daughters
        List<ILocation> tree = (List<ILocation>) new LocationLister().forceTopLevel().fetch();
        LOGGER.debug("top level: {}", tree);
        tree.addAll((List<ILocation>) new LocationLister().location(tree).fetchDescendantLocations());
        LOGGER.debug("full tree: {}", tree);

        for (ILocation ihc : tree)
        {
            Location hc = (Location)ihc;
            jsonHc.add(hc.toJson(builderFactory));
        }

        JsonObjectBuilder job = builderFactory.createObjectBuilder();
        job.add("JsonMode", "DUMP");
        job.add("JsonNBClass", "Location");
        job.add("values", jsonHc);

        try (JsonWriter writer = writerFactory.createWriter(new FileWriter(new File(dumpDirectory, "Location.json"), false)))
        {
            writer.writeObject(job.build());
        } catch (IOException ex) {
            LOGGER.error("toJSON(): IOException: ", ex);
        }
    }	// toJson

}
