package it.inaf.oats.vospace.persistence;

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.List;
import java.util.Optional;
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, owner_id, 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.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) {       
        
        String vosPath = NodeUtils.getVosPath(newNode);

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

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            int i = 0;
            ps.setString(++i, NodeProperties.getNodePropertyByURI(newNode, NodeProperties.CREATOR_URI));
            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<>();

        addProperty(NodeProperties.LENGTH_URI, rs.getString("content_length"),
                properties);

        addProperty(NodeProperties.INITIAL_CREATION_TIME_URI, rs.getString("created_on"),
                properties);

        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 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, owner_id, 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, owner_id, 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);
        }
        
    }
          
    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;
    }

    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;
        } 
        
    }
}