/*
 * This file is part of vospace-rest
 * Copyright (C) 2021 Istituto Nazionale di Astrofisica
 * SPDX-License-Identifier: GPL-3.0-or-later
 */
package it.inaf.oats.vospace.persistence;

import it.inaf.ia2.aa.data.User;
import it.inaf.oats.vospace.DeleteNodeController;
import it.inaf.oats.vospace.datamodel.NodeProperties;
import it.inaf.oats.vospace.datamodel.NodeUtils;
import it.inaf.oats.vospace.exception.InternalFaultException;
import java.sql.Array;
import net.ivoa.xml.vospace.v2.Node;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import net.ivoa.xml.vospace.v2.ContainerNode;
import net.ivoa.xml.vospace.v2.DataNode;
import net.ivoa.xml.vospace.v2.Property;
import net.ivoa.xml.vospace.v2.View;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class NodeDAO {

    private static final Logger LOG = LoggerFactory.getLogger(DeleteNodeController.class);

    @Value("${vospace-authority}")
    private String authority;

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public NodeDAO(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void createNode(Node myNode) {

        String nodeURI = myNode.getUri();
        String path = nodeURI.replaceAll("vos://[^/]+", "");
        String parentPath = NodeUtils.getParentPath(path);

        List<NodePaths> paths = getNodePathsFromDB(nodeURI);

        if (paths.isEmpty()) {
            throw new IllegalStateException("Unable to find parent node during node creation");
        }
        if (paths.size() > 1) {
            throw new IllegalStateException("Multiple ltree parent paths found for " + parentPath);
        }

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO node");
        sb.append(" (name, busy_state, creator_id, group_read, group_write,");
        sb.append(" is_public, parent_path, parent_relative_path, type, accept_views, provide_views)");
        sb.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            int i = 0;
            ps.setString(++i, NodeUtils.getNodeName(myNode));
            ps.setBoolean(++i, NodeUtils.getIsBusy(myNode));
            ps.setString(++i, NodeProperties.getStandardNodePropertyByName(myNode, "creator"));
            ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getStandardNodePropertyByName(myNode, "groupread")));
            ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getStandardNodePropertyByName(myNode, "groupwrite")));
            ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getStandardNodePropertyByName(myNode, "publicread")));
            ps.setObject(++i, paths.get(0).path, Types.OTHER);
            ps.setObject(++i, paths.get(0).relativePath, Types.OTHER);
            ps.setObject(++i, NodeUtils.getDbNodeType(myNode), Types.OTHER);
            ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getAccepts()), Types.OTHER);
            ps.setObject(++i, fromViewsToArray(ps, myNode, d -> d.getProvides()), Types.OTHER);
            return ps;
        });
    }

    public Optional<Node> listNode(String path) {

        String sql = "SELECT os.vos_path, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
                + "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
                + "FROM node n\n"
                + "JOIN node_vos_path os ON n.node_id = os.node_id\n"
                + "WHERE n.path ~ (" + getFirstLevelChildrenSelector(path) + ")::lquery\n"
                + "OR os.vos_path = ? ORDER BY vos_path";

        List<Node> parentAndChildren = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, path);
            ps.setString(2, path);
            return ps;
        }, (row, index) -> {
            return getNodeFromResultSet(row);
        });

        if (parentAndChildren.isEmpty()) {
            return Optional.empty();
        }

        // Query returns parent as first node
        Node node = parentAndChildren.get(0);

        // Fill children
        if (node instanceof ContainerNode && parentAndChildren.size() > 1) {
            ContainerNode parent = (ContainerNode) node;
            for (int i = 1; i < parentAndChildren.size(); i++) {
                parent.getNodes().add(parentAndChildren.get(i));
            }
        }

        return Optional.of(node);
    }

    public Node setNode(Node newNode) {
        return setNode(newNode, false);
    }

    /**
     * If recursive flag is true the update is applied to children too.
     */
    public Node setNode(Node newNode, boolean recursive) {

        String vosPath = NodeUtils.getVosPath(newNode);

        StringBuilder sb = new StringBuilder();

        sb.append("UPDATE node");
        sb.append(" SET group_read = ?, group_write = ?, is_public = ? ");
        sb.append(" FROM node_vos_path p WHERE p.vos_path = ? AND p.node_id = node.node_id ");

        if (recursive) {
            updatePermissionsRecursively(newNode, vosPath);
        } else {
            jdbcTemplate.update(conn -> {
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                int i = 0;
                ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_READ_URI)));
                ps.setArray(++i, fromPropertyToArray(ps, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.GROUP_WRITE_URI)));
                ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
                ps.setString(++i, vosPath);
                return ps;
            });
        }

        return newNode;
    }

    private String getFirstLevelChildrenSelector(String path) {
        String select = "(SELECT path FROM node WHERE node_id = (SELECT node_id FROM node_vos_path WHERE vos_path = ?))::varchar || '";

        if (!"/".equals(path)) {
            select += ".";
        }
        select += "*{1}'";
        return select;
    }

    private String getAllLevelsChildrenSelector(String path) {
        String select = "(SELECT path FROM node WHERE node_id = (SELECT node_id FROM node_vos_path WHERE vos_path = ?))::varchar || '";

        if (!"/".equals(path)) {
            select += ".";
        }
        select += "*{1,}'";
        return select;
    }

    private Node getNodeFromResultSet(ResultSet rs) throws SQLException {

        Node node = NodeUtils.getTypedNode(rs.getString("type"));

        if (node instanceof DataNode) {

            DataNode dataNode = (DataNode) node;

            dataNode.setBusy(rs.getBoolean("busy_state"));
            dataNode.setAccepts(getViews(rs.getArray("accept_views")));
            dataNode.setProvides(getViews(rs.getArray("provide_views")));
        }

        node.setUri(getUri(rs.getString("vos_path")));

        List<Property> properties = new ArrayList<>();

        // Content length is required for CADC client compatibility
        String contentLength = rs.getString("content_length");
        addProperty(NodeProperties.LENGTH_URI, contentLength == null ? "0" : contentLength, properties);

        String creationTime = rs.getString("created_on").replace(" ", "T");

        addProperty(NodeProperties.INITIAL_CREATION_TIME_URI, creationTime, properties);
        addProperty(NodeProperties.DATE_URI, creationTime, properties); // required by CADC

        addProperty(NodeProperties.CREATOR_URI, rs.getString("creator_id"),
                properties);

        addProperty(NodeProperties.MODIFICATION_TIME_URI, rs.getString("last_modified"),
                properties);

        addProperty(NodeProperties.GROUP_READ_URI, getGroupsString(rs, "group_read"),
                properties);

        addProperty(NodeProperties.GROUP_WRITE_URI, getGroupsString(rs, "group_write"),
                properties);

        addProperty(NodeProperties.PUBLIC_READ_URI, String.valueOf(rs.getBoolean("is_public")),
                properties);

        addProperty("urn:async_trans", String.valueOf(rs.getBoolean("async_trans")),
                properties);

        addProperty("urn:sticky", String.valueOf(rs.getBoolean("sticky")), properties);

        node.setProperties(properties);
        return node;
    }

    public Optional<Long> getNodeId(String nodeVosPath) {
        String sql = "SELECT node_id FROM node_vos_path WHERE vos_path = ?";

        List<Long> nodeIdList = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, nodeVosPath);
            return ps;
        }, (row, index) -> {
            return row.getLong("node_id");
        });

        switch (nodeIdList.size()) {
            case 0:
                return Optional.empty();

            case 1:
                return Optional.of(nodeIdList.get(0));

            default:
                throw new InternalFaultException("More than 1 node id at path: " + nodeVosPath);
        }
    }

    /*
    public Optional<String> getLtreePath(String nodeVosPath) {
        String sql = "SELECT path FROM node n "
                + "JOIN node_vos_path p ON n.node_id = p.node_id "
                + "WHERE vos_path = ?";

        List<String> nodeIdList = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, nodeVosPath);
            return ps;
        }, (row, index) -> {
            return row.getString("path");
        });

        switch (nodeIdList.size()) {
            case 0:
                return Optional.empty();

            case 1:
                return Optional.of(nodeIdList.get(0));

            default:
                throw new InternalFaultException("More than 1 node at path: " + nodeVosPath);
        }
    }*/

    public Optional<ShortNodeDescriptor> getShortNodeDescriptor(String nodeVosPath,
            String userId, List<String> userGroups) {

        String sql = "SELECT path,\n"
                + "NOT (n.async_trans OR n.sticky OR loc.location_type = 'async' \n"
                + "OR ( (SELECT COUNT(*) FROM (SELECT UNNEST(?) INTERSECT SELECT UNNEST(n.group_write)) AS allowed_groups ) = 0 AND\n"
                + "n.creator_id <> ?)) AS is_writable,\n"
                + "n.type = 'container' AS is_container,\n"
                + "n.busy_state\n"
                + "FROM node n \n"
                + "JOIN node_vos_path p ON n.node_id = p.node_id \n"
                + "LEFT JOIN location loc ON loc.location_id = n.location_id\n"
                + "WHERE vos_path = ?\n";

        Optional<ShortNodeDescriptor> sndOpt = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setArray(1, ps.getConnection().createArrayOf("varchar", userGroups.toArray(String[]::new)));
            ps.setString(2, userId);
            ps.setString(3, nodeVosPath);
            return ps;
        }, rs -> {
            if (!rs.next()) {
                return Optional.empty();
            }

            String nodePath = rs.getString("path");
            Boolean isContainer = rs.getBoolean(("is_container"));
            Boolean isWritable = rs.getBoolean("is_writable");
            Boolean isBusy = rs.getBoolean("busy_state");

            ShortNodeDescriptor result = new ShortNodeDescriptor(nodePath, isContainer, isWritable, isBusy);

            return Optional.of(result);
        });

        return sndOpt;

    }

    /*
    public Optional<Node> getNodeById(Long nodeId, boolean enforceTapeStoredCheck) {
        String sql = "SELECT os.vos_path, loc.location_type, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
                + "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
                + "FROM node n\n"
                + "JOIN node_vos_path os ON n.node_id = os.node_id\n"
                + "JOIN location loc ON n.location_id = loc.location_id\n"
                + "WHERE n.node_id = ?\n"
                + "FOR UPDATE";

        List<Node> result = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, nodeId);
            return ps;
        }, (row, index) -> {
            if (enforceTapeStoredCheck && row.getString("location_type").equals("async")) {
                throw new InternalFaultException(
                        "Node id: " + nodeId + " has async location type. "
                        + "Failure due to enforced check.");
            }
            return getNodeFromResultSet(row);
        });

        switch (result.size()) {
            case 0:
                return Optional.empty();

            case 1:
                return Optional.of(result.get(0));

            default:
                throw new InternalFaultException("Multiple nodes with id: " + nodeId);
        }
    }
    */

    /*
    // First node is the root node 
    public List<Node> listNodesInBranch(Long rootNodeId, boolean enforceTapeStoredCheck) {
        String sql = "SELECT os.vos_path, loc.location_type, n.node_id, type, async_trans, sticky, busy_state, creator_id, group_read, group_write,\n"
                + "is_public, content_length, created_on, last_modified, accept_views, provide_views\n"
                + "FROM node n\n"
                + "JOIN node_vos_path os ON n.node_id = os.node_id\n"
                + "JOIN location loc ON n.location_id = loc.location_id\n"
                + "WHERE n.path ~ ('*.' || ? || '.*')::lquery\n"
                + "ORDER BY n.path FOR UPDATE";

        List<Node> result = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, Long.toString(rootNodeId));
            return ps;
        }, (row, index) -> {
            if (enforceTapeStoredCheck && row.getString("location_type").equals("async")) {
                throw new InternalFaultException(
                        "At least one node in branch with root id: " + rootNodeId
                        + " has async location type. "
                        + "Failure due to enforced check.");
            }
            return getNodeFromResultSet(row);
        });

        return result;

    }*/

    /*
    public void setBranchBusy(Long rootNodeId, boolean busyState) {
        String sql = "UPDATE node SET busy_state = ?\n"
                + "WHERE path ~ ('*.' || ? || '.*')::lquery";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setBoolean(1, busyState);
            ps.setLong(2, rootNodeId);
            return ps;
        });
    }*/

    public void renameNode(Long nodeId, String name) {
        String sql = "UPDATE node SET name = ?\n"
                + "WHERE path ~ ('*.' || ?)::lquery";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setLong(2, nodeId);
            return ps;
        });

    }

    public void moveNodeBranch(Long sourceRootId, String destParentLtreePath) {

        String sql = "UPDATE node c SET "
                + "parent_path = (? || SUBPATH(c.path, (SELECT nlevel(parent_path) FROM node WHERE node_id = ?), -1))::ltree, "
                + "parent_relative_path = COALESCE(c.parent_relative_path, c.parent_path) " // not sure about this
                + "FROM node n "
                + "WHERE n.path @> c.path AND n.node_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, destParentLtreePath);
            ps.setLong(2, sourceRootId);
            ps.setLong(3, sourceRootId);
            return ps;
        });
    }

    public boolean isBranchBusy(long parentNodeId) {

        String sql = "SELECT COUNT(c.node_id) > 0 "
                + "FROM node n "
                + "JOIN node c ON c.path <@ n.path "
                + "WHERE n.node_id = ? AND c.busy_state";

        return jdbcTemplate.queryForObject(sql, new Object[]{parentNodeId}, new int[]{Types.BIGINT}, Boolean.class);
    }

    public boolean isBranchWritable(long parentNodeId, String userId, List<String> userGroups) {

        String sql = "SELECT COUNT(c.node_id) = 0 "
                + "FROM node n "
                + "JOIN node c ON c.path <@ n.path "
                + "LEFT JOIN location loc ON c.location_id = loc.location_id "
                + "WHERE n.node_id = ? "
                + "AND (c.async_trans OR c.sticky OR location_type = 'async' OR "
                + "((SELECT COUNT(*) FROM (SELECT UNNEST(?) INTERSECT SELECT UNNEST(c.group_write)) AS allowed_groups) = 0 "
                + "AND c.creator_id <> ?))";

        return jdbcTemplate.query(sql, ps -> {
            ps.setLong(1, parentNodeId);
            ps.setArray(2, ps.getConnection().createArrayOf("varchar", userGroups.toArray(String[]::new)));
            ps.setString(3, userId);
        }, row -> {
            if (!row.next()) {
                throw new IllegalStateException("Expected one result");
            }
            return row.getBoolean(1);
        });
    }

    public void deleteNode(String path) {
        int nodesWithPath = countNodesWithPath(path);
        if (nodesWithPath == 0) {
            throw new IllegalStateException("Node at path "
                    + path + " not found");
        }

        if (nodesWithPath > 1) {
            throw new IllegalStateException("Multiple nodes at path " + path);
        }

        String insertSql = "INSERT INTO deleted_node "
                + "(node_id, parent_path, parent_relative_path, "
                + "name, os_name, tstamp_wrapper_dir, type, location_id, format, "
                + "async_trans, busy_state, creator_id, group_read, "
                + "group_write, is_public, delta, content_type, content_encoding, "
                + "content_length, content_md5, created_on, last_modified, "
                + "accept_views, provide_views, protocols, sticky)\n";

        String deleteSql = "DELETE \n"
                + "FROM node n\n"
                + "USING node_vos_path os\n"
                + "WHERE n.node_id = os.node_id AND\n"
                + "(n.path ~ (" + getAllLevelsChildrenSelector(path) + ")::lquery\n"
                + "OR os.vos_path = ?) RETURNING\n"
                + "n.node_id, parent_path, parent_relative_path, "
                + "name, os_name, tstamp_wrapper_dir, type, location_id, format, "
                + "async_trans, busy_state, creator_id, group_read, "
                + "group_write, is_public, delta, content_type, content_encoding, "
                + "content_length, content_md5, created_on, last_modified, "
                + "accept_views, provide_views, protocols, sticky\n";

        String withSql = "WITH del AS (" + deleteSql + ")";

        String sql = withSql + insertSql + "SELECT * FROM del\n";

        jdbcTemplate.update(sql, path, path);
    }

    // utility method for deleteNode
    public int countNodesWithPath(String path) {
        String sql = "SELECT COUNT(*) from "
                + "node_vos_path p "
                + "where p.vos_path = ?";

        Object[] args = {path};
        int[] types = {Types.VARCHAR};

        return jdbcTemplate.queryForObject(sql, args, types, Integer.class);
    }

    public String getNodeOsName(String vosPath) {
        String sql = "SELECT \n"
                + "(CASE WHEN os_name IS NOT NULL THEN os_name ELSE name END) AS os_name\n"
                + "FROM node n\n"
                + "JOIN node_vos_path p ON n.node_id = p.node_id\n"
                + "WHERE p.vos_path = ?";

        Object[] args = {vosPath};
        int[] types = {Types.VARCHAR};

        return jdbcTemplate.queryForObject(sql, args, types, String.class);
    }

    public void setNodeLocation(String vosPath, int locationId, String nodeOsName) {

        String sql = "UPDATE node SET location_id = ?, os_name = ? WHERE node_id = "
                + "(SELECT node_id FROM node_vos_path WHERE vos_path = ?)";

        int updated = jdbcTemplate.update(sql, ps -> {
            ps.setInt(1, locationId);
            ps.setString(2, nodeOsName);
            ps.setString(3, vosPath);
        });

        if (updated != 1) {
            throw new InternalFaultException("Unable to set node location for path " + vosPath);
        }
    }

    private String getGroupsString(ResultSet rs, String column) throws SQLException {
        Array array = rs.getArray(column);
        if (array == null) {
            return null;
        }
        return String.join(" ", (String[]) array.getArray());
    }

    // If value is null does nothing
    private void addProperty(String uri, String value, List<Property> list) {
        if (value != null) {
            Property prop = new Property();
            prop.setUri(uri);
            prop.setValue(value);
            list.add(prop);
        }
    }

    private String getUri(String path) {
        return "vos://" + authority + path;
    }

    private NodePaths getPathsFromResultSet(ResultSet rs) throws SQLException {
        NodePaths paths = new NodePaths(rs.getString("path"), rs.getString("relative_path"));
        return paths;
    }

    private Array fromPropertyToArray(PreparedStatement ps, String myProperty) throws SQLException {
        if (myProperty == null || myProperty.isBlank()) {
            return null;
        } else {
            return ps.getConnection().createArrayOf("varchar", myProperty.split(" "));
        }
    }

    private Array fromViewsToArray(PreparedStatement ps, Node node, Function<DataNode, List<View>> viewsExtractor) throws SQLException {
        if (node instanceof DataNode) {
            DataNode dataNode = (DataNode) node;
            List<View> views = viewsExtractor.apply(dataNode);
            if (views != null && !views.isEmpty()) {
                Object[] array = views.stream().map(v -> v.getUri()).toArray();
                return ps.getConnection().createArrayOf("varchar", array);
            }
        }
        return null;
    }

    private List<View> getViews(Array array) {
        if (array == null) {
            return null;
        }
        try {
            return Arrays.stream((String[]) array.getArray())
                    .map(uri -> {
                        View view = new View();
                        view.setUri(uri);
                        return view;
                    })
                    .collect(Collectors.toList());
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }

    }

    /*
    Map contains:
    Key             column
    column name     value
    column name     value
    and value is a String containing comma separated groups having permissions
     */
    private Map<String, List<String>> getPermissionsFromDB(String vosPath) {

        String sql = "SELECT group_read, group_write "
                + "FROM node n JOIN node_vos_path p ON n.node_id = p.node_id "
                + "WHERE p.vos_path = ?";

        return jdbcTemplate.query(sql, new Object[]{vosPath}, rs -> {
            if (!rs.next()) {
                throw new InternalFaultException("No records found for " + vosPath);
            }
            return Map.of(
                    "group_read", getArrayValue(rs, "group_read"),
                    "group_write", getArrayValue(rs, "group_write")
            );
        });
    }

    private List<String> getArrayValue(ResultSet rs, String key) throws SQLException {
        Array array = rs.getArray(key);
        if (array == null) {
            return new ArrayList<>();
        }
        return Arrays.asList((String[]) array.getArray());
    }

    private void updatePermissionsRecursively(Node newNode, String vosPath) {

        Map<String, List<String>> permissions = getPermissionsFromDB(vosPath);

        List<String> existingGroupReadList = permissions.get("group_read");
        List<String> existingGroupWriteList = permissions.get("group_write");

        List<String> newGroupReadList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_READ_URI);
        List<String> newGroupWriteList = NodeProperties.getNodePropertyAsListByURI(newNode, NodeProperties.GROUP_WRITE_URI);

        Set<String> existingGroupRead = new HashSet<>(existingGroupReadList);
        Set<String> existingGroupWrite = new HashSet<>(existingGroupWriteList);

        Set<String> newGroupRead = new HashSet<>(newGroupReadList);
        Set<String> newGroupWrite = new HashSet<>(newGroupWriteList);

        Set<String> groupReadToAdd = differenceBetweenSets(newGroupRead, existingGroupRead);
        Set<String> groupReadToRemove = differenceBetweenSets(existingGroupRead, newGroupRead);

        Set<String> groupWriteToAdd = differenceBetweenSets(newGroupWrite, existingGroupWrite);
        Set<String> groupWriteToRemove = differenceBetweenSets(existingGroupWrite, newGroupWrite);

        String sql = "UPDATE node SET "
                + "group_read = update_array(group_read, ?, ?), "
                + "group_write = update_array(group_write, ?, ?), "
                + "is_public = ? "
                + "WHERE path <@ (SELECT path FROM node n "
                + "JOIN node_vos_path p ON n.node_id = p.node_id "
                + "AND p.vos_path = ?)";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToAdd.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupReadToRemove.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToAdd.toArray()));
            ps.setArray(++i, ps.getConnection().createArrayOf("varchar", groupWriteToRemove.toArray()));
            ps.setBoolean(++i, Boolean.valueOf(NodeProperties.getNodePropertyByURI(newNode, NodeProperties.PUBLIC_READ_URI)));
            ps.setString(++i, vosPath);
            return ps;
        });
    }

    // Returns the difference a minus b
    private Set<String> differenceBetweenSets(Set<String> a, Set<String> b) {

        Set<String> diff = new HashSet<>(a);
        diff.removeAll(b);

        return diff;
    }

    private List<NodePaths> getNodePathsFromDB(String nodeURI) {

        String path = nodeURI.replaceAll("vos://[^/]+", "");
        String parentPath = NodeUtils.getParentPath(path);

        String sql = "SELECT path, relative_path from "
                + "node n join node_vos_path p on n.node_id = p.node_id "
                + "where p.vos_path = ?";

        List<NodePaths> paths = jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, parentPath);
            return ps;
        }, (row, index) -> {
            return getPathsFromResultSet(row);
        });

        return paths;
    }

    public class ShortNodeDescriptor {

        private final String nodeLtreePath;

        private final boolean container;
        private final boolean writable;
        private final boolean busy;

        public ShortNodeDescriptor(String nodeLtreePath, boolean container, boolean writable, boolean busy) {
            this.nodeLtreePath = nodeLtreePath;
            this.container = container;
            this.writable = writable;
            this.busy = busy;
        }

        public String getDestinationNodeLtreePath() {
            return nodeLtreePath;
        }

        public boolean isContainer() {
            return container;
        }

        public boolean isWritable() {
            return writable;
        }

        public boolean isBusy() {
            return busy;
        }

    }

    private class NodePaths {

        private final String path;
        private final String relativePath;

        public NodePaths(String myPath, String myRelativePath) {

            this.path = myPath;
            this.relativePath = myRelativePath;
        }

        @Override
        public String toString() {
            return relativePath + " " + path;
        }

        public String getPath() {
            return this.path;
        }

        public String getRelativePath() {
            return this.relativePath;
        }
    }
}