package org.eclipse.hawk.sqlite;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.nio.file.FileVisitResult;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.SimpleFileVisitor;
import java.nio.file.attribute.BasicFileAttributes;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialException;

import org.apache.commons.codec.binary.Base64;
import org.eclipse.hawk.core.IConsole;
import org.eclipse.hawk.core.graph.IGraphDatabase;
import org.eclipse.hawk.core.graph.IGraphEdge;
import org.eclipse.hawk.core.graph.IGraphIterable;
import org.eclipse.hawk.core.graph.IGraphNode;
import org.eclipse.hawk.core.graph.IGraphNodeIndex;
import org.eclipse.hawk.core.graph.IGraphTransaction;
import org.eclipse.hawk.sqlite.iteration.StatementGraphNodeIterable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SQLiteDatabase implements IGraphDatabase {

	private static final String SQLITE_FILE = "hawk.sqlite";
	private static final Logger LOGGER = LoggerFactory.getLogger(SQLiteDatabase.class);

	private Mode currentMode = Mode.TX_MODE;
	private File storageFolder;
	private File tempFolder;

	protected class SQLiteConnection implements AutoCloseable {
		public static final String TYPE_BLOB_BASE64 = "blobBase64";

		private final Connection rawConn;
		private final Set<String> dropIndicesOnCommit = new HashSet<>();

		private PreparedStatement stmtInsertNode;
		private PreparedStatement stmtDeleteNode;
		private PreparedStatement stmtNodeIDsByLabel;
		private PreparedStatement stmtNodeCountByLabel;
		private PreparedStatement stmtFirstNodeIDByLabel;

		private PreparedStatement stmtGetNodePropKeys;
		private PreparedStatement stmtGetNodePropValue;
		private PreparedStatement stmtUpsertNodeProperty;
		private PreparedStatement stmtDeleteNodeProperty;
		private PreparedStatement stmtDeleteNodeProperties;

		private PreparedStatement stmtInsertEdge;
		private PreparedStatement stmtDeleteEdge;
		private PreparedStatement stmtDeleteEdgesForNode;
		private PreparedStatement stmtGetOutgoingEdgesWithType;
		private PreparedStatement stmtGetIncomingEdgesWithType;
		private PreparedStatement stmtGetIncomingEdges;
		private PreparedStatement stmtGetOutgoingEdges;
		private PreparedStatement stmtGetEdges;
		private PreparedStatement stmtGetEdgesWithType;
		
		private PreparedStatement stmtGetEdgePropKeys;
		private PreparedStatement stmtGetEdgePropValue;
		private PreparedStatement stmtUpsertEdgeProperty;
		private PreparedStatement stmtDeleteEdgeProperty;
		private PreparedStatement stmtDeleteEdgeProperties;
		private PreparedStatement stmtDeleteEdgePropsForNode;

		private PreparedStatement stmtUpsertNodeIndex;
		private PreparedStatement stmtDeleteNodeIndex;
		private PreparedStatement stmtGetAllNodeIndices;
		private Map<String, PreparedStatement> stmtAddNodeIndexEntry = new HashMap<>();
		private Map<String, PreparedStatement> stmtRemoveNodeIndexEntry = new HashMap<>();
		private Map<String, PreparedStatement> stmtRemoveNodeFromIndex = new HashMap<>();
		private Map<String, PreparedStatement> stmtRemoveNodeFieldFromIndex = new HashMap<>();
		private Map<String, PreparedStatement> stmtRemoveNodeValueFromIndex = new HashMap<>();

		private Map<String, PreparedStatement> stmtQueryIndexValuePattern = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexValuePatternCount = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexValuePatternSingle = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexValueExact = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexValueExactCount = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexValueExactSingle = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllValues = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllValuesCount = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllValuesSingle = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllPairs = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllPairsCount = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexAllPairsSingle = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexNumberRange = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexNumberRangeCount = new HashMap<>();
		private Map<String, PreparedStatement> stmtQueryIndexNumberRangeSingle = new HashMap<>();

		public SQLiteConnection(Connection rawConn) {
			this.rawConn = rawConn;
		}

		@Override
		public void close() throws SQLException {
			rawConn.close();
		}

		public Statement createStatement() throws SQLException {
			return rawConn.createStatement();
		}
		
		public void commit() throws SQLException {
			for (String idx : dropIndicesOnCommit) {
				try (Statement stmt = createStatement()) {
					stmt.execute(getDropNodeIndexTableSQL(idx));
				}
			}
			dropIndicesOnCommit.clear();
			rawConn.commit();
		}

		public void rollback() throws SQLException {
			dropIndicesOnCommit.clear();
			rawConn.rollback();
		}

		public PreparedStatement getInsertNodeStatement() throws SQLException {
			if (stmtInsertNode == null) {
				stmtInsertNode = rawConn.prepareStatement("INSERT INTO nodes (label) VALUES (?);");
			}
			return stmtInsertNode;
		}

		public PreparedStatement getNodeIDsByLabelStatement(String label) throws SQLException {
			if (stmtNodeIDsByLabel == null) {
				stmtNodeIDsByLabel = rawConn.prepareStatement("SELECT rowid FROM nodes WHERE label = ?;");
			}
			stmtNodeIDsByLabel.setString(1, label);
			return stmtNodeIDsByLabel;
		}

		public PreparedStatement getNodeCountByLabelStatement(String label) throws SQLException {
			if (stmtNodeCountByLabel == null) {
				stmtNodeCountByLabel = rawConn.prepareStatement("SELECT COUNT(1) FROM nodes WHERE label = ?;");
			}
			stmtNodeCountByLabel.setString(1, label);
			return stmtNodeCountByLabel;
		}

		public PreparedStatement getFirstNodeIDByLabelStatement(String label) throws SQLException {
			if (stmtFirstNodeIDByLabel == null) {
				stmtFirstNodeIDByLabel = rawConn.prepareStatement("SELECT rowid FROM nodes WHERE label = ? LIMIT 1;");
			}
			stmtFirstNodeIDByLabel.setString(1, label);
			return stmtFirstNodeIDByLabel;
		}

		public PreparedStatement getNodePropKeysStatement(int nodeId) throws SQLException {
			if (stmtGetNodePropKeys == null) {
				stmtGetNodePropKeys = rawConn.prepareStatement("SELECT key FROM nodeprops WHERE nodeid = ?;");
			}
			stmtGetNodePropKeys.setInt(1, nodeId);
			return stmtGetNodePropKeys;
		}
		
		public PreparedStatement getNodePropValueStatement() throws SQLException {
			if (stmtGetNodePropValue == null) {
				stmtGetNodePropValue = rawConn.prepareStatement("SELECT type, value FROM nodeprops WHERE nodeid = ? AND key = ? LIMIT 1;");
			}
			return stmtGetNodePropValue;
		}
		
		public PreparedStatement getUpsertNodePropStatement(int nodeId, String key, Object value) throws SQLException, IOException {
			if (stmtUpsertNodeProperty == null) {
				stmtUpsertNodeProperty = rawConn.prepareStatement(
					"INSERT INTO nodeprops (nodeid, key, type, value) VALUES (?, ?, ?, ?) "
					+ "ON CONFLICT (nodeid, key) DO UPDATE SET value = ?;"
				);
			}
			setUpsertPropParameters(stmtUpsertNodeProperty, nodeId, key, value);
			return stmtUpsertNodeProperty;
		}

		protected void setUpsertPropParameters(PreparedStatement stmt, int nodeOrEdgeId, String key, Object value)
				throws SQLException, IOException {
			stmt.setInt(1, nodeOrEdgeId);
			stmt.setString(2, key);
			if (value instanceof Blob) {
				stmt.setString(3, TYPE_BLOB_BASE64);

				// Xerial SQLite JDBC driver does not support BLOB
				ByteArrayOutputStream bos = new ByteArrayOutputStream();
				copy(((Blob)value).getBinaryStream(), bos);
				String base64String = Base64.encodeBase64String(bos.toByteArray());

				stmt.setString(4, base64String);
				stmt.setString(5, base64String);
			} else {
				stmt.setString(3, getPropertyType(value));
				stmt.setObject(4, value);
				stmt.setObject(5, value);
			}
		}

		/* From https://www.baeldung.com/java-inputstream-to-outputstream (Java 8 alternative to InputStream.transferTo) */
		private void copy(InputStream source, OutputStream target) throws IOException {
		    byte[] buf = new byte[8192];
		    int length;
		    while ((length = source.read(buf)) > 0) {
		        target.write(buf, 0, length);
		    }
		}		

		protected String getPropertyType(Object value) {
			return value == null ? "unknown" : value.getClass().getSimpleName();
		}

		public PreparedStatement getDeleteNodePropStatement(int nodeId, String key) throws SQLException {
			if (stmtDeleteNodeProperty == null) {
				stmtDeleteNodeProperty = rawConn.prepareStatement("DELETE FROM nodeprops WHERE nodeid = ? AND key = ?;");
			}

			stmtDeleteNodeProperty.setInt(1, nodeId);
			stmtDeleteNodeProperty.setString(2, key);
			return stmtDeleteNodeProperty;
		}

		public PreparedStatement getDeleteNodeStatement(int nodeId) throws SQLException {
			if (stmtDeleteNode == null) {
				stmtDeleteNode = rawConn.prepareStatement("DELETE FROM nodes WHERE rowid = ?;");
			}
			stmtDeleteNode.setInt(1, nodeId);
			return stmtDeleteNode;
		}

		public PreparedStatement getDeleteNodePropsStatement(int nodeId) throws SQLException {
			if (stmtDeleteNodeProperties == null) {
				stmtDeleteNodeProperties = rawConn.prepareStatement("DELETE FROM nodeprops WHERE nodeid = ?;");
			}
			stmtDeleteNodeProperties.setInt(1, nodeId);
			return stmtDeleteNodeProperties;
		}

		public PreparedStatement getInsertEdgeStatement(int startId, int endId, String label) throws SQLException {
			if (stmtInsertEdge == null) {
				stmtInsertEdge = rawConn.prepareStatement("INSERT INTO edges (startId, endId, label) VALUES (?, ?, ?);");
			}
			stmtInsertEdge.setInt(1, startId);
			stmtInsertEdge.setInt(2, endId);
			stmtInsertEdge.setString(3, label);
			return stmtInsertEdge;
		}

		public PreparedStatement getDeleteEdgeStatement(int edgeId) throws SQLException {
			if (stmtDeleteEdge == null) {
				stmtDeleteEdge = rawConn.prepareStatement("DELETE FROM edges WHERE rowid = ?;");
			}
			stmtDeleteEdge.setInt(1, edgeId);
			return stmtDeleteEdge;
		}

		public PreparedStatement getOutgoingEdgesWithTypeStatement(int fromId, String type) throws SQLException {
			if (stmtGetOutgoingEdgesWithType == null) {
				stmtGetOutgoingEdgesWithType = rawConn.prepareStatement("SELECT rowid, endId FROM edges WHERE label = ? AND startId = ?;");
			}
			stmtGetOutgoingEdgesWithType.setString(1, type);
			stmtGetOutgoingEdgesWithType.setInt(2, fromId);
			return stmtGetOutgoingEdgesWithType;
		}

		public PreparedStatement getOutgoingEdgesStatement(int fromId) throws SQLException {
			if (stmtGetOutgoingEdges == null) {
				stmtGetOutgoingEdges = rawConn.prepareStatement("SELECT rowid, endId, label FROM edges WHERE startId = ?;");
			}
			stmtGetOutgoingEdges.setInt(1, fromId);
			return stmtGetOutgoingEdges;
		}

		public PreparedStatement getIncomingEdgesWithTypeStatement(int endId, String type) throws SQLException {
			if (stmtGetIncomingEdgesWithType == null) {
				stmtGetIncomingEdgesWithType = rawConn.prepareStatement("SELECT rowid, startId FROM edges WHERE label = ? AND endId = ?;");
			}
			stmtGetIncomingEdgesWithType.setString(1, type);
			stmtGetIncomingEdgesWithType.setInt(2, endId);
			return stmtGetIncomingEdgesWithType;
		}

		public PreparedStatement getIncomingEdgesStatement(int endId) throws SQLException {
			if (stmtGetIncomingEdges == null) {
				stmtGetIncomingEdges = rawConn.prepareStatement("SELECT rowid, startId, label FROM edges WHERE endId = ?;");
			}
			stmtGetIncomingEdges.setInt(1, endId);
			return stmtGetIncomingEdges;
		}

		public PreparedStatement getEdgesStatement(int startOrEndId) throws SQLException {
			if (stmtGetEdges == null) {
				stmtGetEdges = rawConn.prepareStatement("SELECT rowid, startId, endId, label FROM edges WHERE startId = ? OR endId = ?;");
			}
			stmtGetEdges.setInt(1, startOrEndId);
			stmtGetEdges.setInt(2, startOrEndId);
			return stmtGetEdges;
		}

		public PreparedStatement getEdgesByTypeStatement(int startOrEndId, String type) throws SQLException {
			if (stmtGetEdgesWithType == null) {
				stmtGetEdgesWithType = rawConn.prepareStatement("SELECT rowid, startId, endid FROM edges WHERE startId = ? OR endId = ?;");
			}
			stmtGetEdgesWithType.setInt(1, startOrEndId);
			stmtGetEdgesWithType.setInt(2, startOrEndId);
			return stmtGetEdgesWithType;
		}

		public PreparedStatement getEdgePropKeysStatement(int edgeId) throws SQLException {
			if (stmtGetEdgePropKeys == null) {
				stmtGetEdgePropKeys = rawConn.prepareStatement("SELECT key FROM edgeprops WHERE edgeid = ?;");
			}
			stmtGetEdgePropKeys.setInt(1, edgeId);
			return stmtGetEdgePropKeys;
		}

		public PreparedStatement getEdgePropValueStatement(int edgeId, String key) throws SQLException {
			if (stmtGetEdgePropValue == null) {
				stmtGetEdgePropValue = rawConn.prepareStatement("SELECT type, value FROM edgeprops WHERE edgeid = ? AND key = ? LIMIT 1;");
			}
			stmtGetEdgePropValue.setInt(1, edgeId);
			stmtGetEdgePropValue.setString(2, key);
			return stmtGetEdgePropValue;
		}
		
		public PreparedStatement getUpsertEdgePropStatement(int edgeId, String key, Object value) throws SQLException, IOException {
			if (stmtUpsertEdgeProperty == null) {
				stmtUpsertEdgeProperty = rawConn.prepareStatement(
					"INSERT INTO edgeprops (edgeid, key, type, value) VALUES (?, ?, ?, ?) "
					+ "ON CONFLICT (edgeid, key) DO UPDATE SET value = ?;"
				);
			}
			setUpsertPropParameters(stmtUpsertEdgeProperty, edgeId, key, value);
			return stmtUpsertEdgeProperty;
		}

		public PreparedStatement getDeleteEdgePropStatement(int edgeId, String key) throws SQLException {
			if (stmtDeleteEdgeProperty == null) {
				stmtDeleteEdgeProperty = rawConn.prepareStatement("DELETE FROM edgeprops WHERE edgeId = ? AND key = ?;");
			}
			stmtDeleteEdgeProperty.setInt(1, edgeId);
			stmtDeleteEdgeProperty.setString(2, key);
			return stmtDeleteEdgeProperty;
		}
		
		public PreparedStatement getDeleteEdgePropertiesStatement(int edgeId) throws SQLException {
			if (stmtDeleteEdgeProperties == null) {
				stmtDeleteEdgeProperties = rawConn.prepareStatement("DELETE FROM edgeprops WHERE edgeId = ?;");
			}
			stmtDeleteEdgeProperties.setInt(1, edgeId);
			return stmtDeleteEdgeProperties;
		}
		
		public PreparedStatement getDeleteEdgePropsForNodeStatement(int nodeId) throws SQLException {
			if (stmtDeleteEdgePropsForNode == null) {
				stmtDeleteEdgePropsForNode = rawConn.prepareStatement(
					"DELETE FROM edgeprops "
					+ "WHERE rowid IN (SELECT rowid FROM edges WHERE startId = ? OR endId = ?);");
			}
			stmtDeleteEdgePropsForNode.setInt(1, nodeId);
			stmtDeleteEdgePropsForNode.setInt(2, nodeId);
			return stmtDeleteEdgePropsForNode;
		}

		public PreparedStatement getDeleteEdgesForNodeStatement(int nodeId) throws SQLException {
			if (stmtDeleteEdgesForNode == null) {
				stmtDeleteEdgesForNode = rawConn.prepareStatement("DELETE FROM edges WHERE startId = ? OR endId = ?;");
			}
			stmtDeleteEdgesForNode.setInt(1, nodeId);
			stmtDeleteEdgesForNode.setInt(2, nodeId);
			return stmtDeleteEdgesForNode;
		}

		public PreparedStatement getUpsertNodeIndexStatement(String name) throws SQLException {
			if (stmtUpsertNodeIndex == null) {
				stmtUpsertNodeIndex = rawConn.prepareStatement("INSERT INTO nodeindices (name) VALUES (?) ON CONFLICT (name) DO NOTHING;");
			}
			stmtUpsertNodeIndex.setString(1, name);
			return stmtUpsertNodeIndex;
		}

		public PreparedStatement getDeleteNodeIndexStatement(String name) throws SQLException {
			if (stmtDeleteNodeIndex == null) {
				stmtDeleteNodeIndex = rawConn.prepareStatement("DELETE FROM nodeindices WHERE name = ?;");
			}
			stmtDeleteNodeIndex.setString(1, name);
			return stmtDeleteNodeIndex;
		}

		public PreparedStatement getAllNodeIndices() throws SQLException {
			if (stmtGetAllNodeIndices == null) {
				stmtGetAllNodeIndices = rawConn.prepareStatement("SELECT name FROM nodeindices;");
			}
			return stmtGetAllNodeIndices;
		}

		public PreparedStatement getAddNodeIndexEntryStatement(String indexName, int nodeId, String key, Object value) throws SQLException {
			PreparedStatement stmt = stmtAddNodeIndexEntry.computeIfAbsent(indexName,
				(name) -> {
					try {
						return rawConn.prepareStatement(String.format("INSERT INTO `idx_%s` (key, nodeId, value) VALUES (?, ?, ?);", name));
					} catch (SQLException e) {
						LOGGER.error(e.getMessage(), e);
						return null;
					}
				});

			stmt.setString(1, key);
			stmt.setInt(2, nodeId);
			stmt.setObject(3, value);
			return stmt;
		}

		public PreparedStatement getRemoveIndexEntryStatement(String indexName, int nodeId, String key, Object value) throws SQLException {
			PreparedStatement stmt = stmtRemoveNodeIndexEntry.computeIfAbsent(indexName,
				(name) -> {
					try {
						return rawConn.prepareStatement(String.format("DELETE FROM `idx_%s` WHERE key = ? AND nodeId = ? AND value = ?;", name));
					} catch (SQLException e) {
						LOGGER.error(e.getMessage(), e);
						return null;
					}
				});
			stmt.setString(1, key);
			stmt.setInt(2, nodeId);
			stmt.setObject(3, value);
			return stmt;
		}

		public PreparedStatement getRemoveNodeFromIndexStatement(String indexName, int nodeId) throws SQLException {
			PreparedStatement stmt = stmtRemoveNodeFromIndex.computeIfAbsent(indexName,
				(name) -> {
					try {
						return rawConn.prepareStatement(String.format("DELETE FROM `idx_%s` WHERE nodeId = ?;", name));
					} catch (SQLException e) {
						LOGGER.error(e.getMessage(), e);
						return null;
					}
				});
			stmt.setInt(1, nodeId);
			return stmt;
		}

		public PreparedStatement getRemoveNodeFieldFromIndexStatement(String indexName, int nodeId, String key) throws SQLException {
			PreparedStatement stmt = stmtRemoveNodeFieldFromIndex.computeIfAbsent(indexName,
				(name) -> {
					try {
						return rawConn.prepareStatement(String.format("DELETE FROM `idx_%s` WHERE nodeId = ? AND key = ?;", name));
					} catch (SQLException e) {
						LOGGER.error(e.getMessage(), e);
						return null;
					}
				});
			stmt.setInt(1, nodeId);
			stmt.setString(2, key);
			return stmt;
		}

		public PreparedStatement getRemoveNodeValueFromIndexStatement(String indexName, int nodeId, Object value) throws SQLException {
			PreparedStatement stmt = stmtRemoveNodeValueFromIndex.computeIfAbsent(indexName,
				(name) -> {
					try {
						return rawConn.prepareStatement(String.format("DELETE FROM `idx_%s` WHERE nodeId = ? AND value = ?;", name));
					} catch (SQLException e) {
						LOGGER.error(e.getMessage(), e);
						return null;
					}
				});
			stmt.setInt(1, nodeId);
			stmt.setObject(2, value);
			return stmt;
		}

		public PreparedStatement getQueryIndexValuePatternStatement(String indexName, String key, String valueGlob) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValuePattern.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT DISTINCT nodeId FROM `idx_%s` WHERE key = ? AND value GLOB ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setString(2, valueGlob);
			return stmt;
		}

		public PreparedStatement getQueryIndexValuePatternCountStatement(String indexName, String key, String valueGlob) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValuePatternCount.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT DISTINCT COUNT(1) FROM `idx_%s` WHERE key = ? AND value GLOB ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setString(2, valueGlob);
			return stmt;
		}

		public PreparedStatement getQueryIndexValuePatternSingleStatement(String indexName, String key, String valueGlob) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValuePatternSingle.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT nodeId FROM `idx_%s` WHERE key = ? AND value GLOB ? LIMIT 1;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setString(2, valueGlob);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueExactStatement(String indexName, String key, Object value) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValueExact.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT DISTINCT nodeId FROM `idx_%s` WHERE key = ? AND value = ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setObject(2, value);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueExactCountStatement(String indexName, String key, Object value) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValueExactCount.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT COUNT(DISTINCT nodeId) FROM `idx_%s` WHERE key = ? AND value = ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setObject(2, value);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueExactSingleStatement(String indexName, String key, Object value) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexValueExactSingle.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT nodeId FROM `idx_%s` WHERE key = ? AND value = ? LIMIT 1;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			stmt.setObject(2, value);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllValuesStatement(String indexName, String key) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllValues.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT DISTINCT nodeId FROM `idx_%s` WHERE key = ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllValuesCountStatement(String indexName, String key) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllValuesCount.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT COUNT(DISTINCT nodeId) FROM `idx_%s` WHERE key = ?;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllValuesSingleStatement(String indexName, String key) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllValuesSingle.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT nodeId FROM `idx_%s` WHERE key = ? LIMIT 1;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			stmt.setString(1, key);
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllPairsStatement(String indexName) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllPairs.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT DISTINCT nodeId FROM `idx_%s`;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllPairsCountStatement(String indexName) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllPairsCount.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT COUNT(DISTINCT nodeId) FROM `idx_%s`;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			return stmt;
		}

		public PreparedStatement getQueryIndexValueAllPairsSingleStatement(String indexName) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexAllPairsSingle.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format("SELECT nodeId FROM `idx_%s` LIMIT 1;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			return stmt;
		}

		public PreparedStatement getQueryIndexNumberRangeStatement(String indexName, String key, Number from, Number to, boolean fromInclusive, boolean toInclusive) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexNumberRange.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format(
						"SELECT DISTINCT nodeId FROM `idx_%s` WHERE key = ?"
						+ " AND (? AND value >= ? OR value > ?) "
						+ " AND (? AND value <= ? OR value < ?) "
						+ ";", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});

			setNumberRangeParameters(key, from, to, fromInclusive, toInclusive, stmt);
			return stmt;
		}

		public PreparedStatement getQueryIndexNumberRangeCountStatement(String indexName, String key, Number from, Number to, boolean fromInclusive, boolean toInclusive) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexNumberRangeCount.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format(
						"SELECT COUNT(DISTINCT nodeId) FROM `idx_%s` WHERE key = ?"
						+ " AND (? AND value >= ? OR value > ?) "
						+ " AND (? AND value <= ? OR value < ?) "
						+ ";", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});
			setNumberRangeParameters(key, from, to, fromInclusive, toInclusive, stmt);
			return stmt;
		}

		public PreparedStatement getQueryIndexNumberRangeSingleStatement(String indexName, String key, Number from, Number to, boolean fromInclusive, boolean toInclusive) throws SQLException {
			PreparedStatement stmt = stmtQueryIndexNumberRangeSingle.computeIfAbsent(indexName, (name) -> {
				try {
					return rawConn.prepareStatement(String.format(
						"SELECT nodeId FROM `idx_%s` WHERE key = ?"
						+ " AND (? AND value >= ? OR value > ?) "
						+ " AND (? AND value <= ? OR value < ?) "
						+ "LIMIT 1;", name));
				} catch (SQLException e) {
					LOGGER.error(e.getMessage(), e);
					return null;
				}
			});

			setNumberRangeParameters(key, from, to, fromInclusive, toInclusive, stmt);
			return stmt;
		}

		private void setNumberRangeParameters(String key, Number from, Number to, boolean fromInclusive,
				boolean toInclusive, PreparedStatement stmt) throws SQLException {
			stmt.setString(1, key);
			stmt.setBoolean(2, fromInclusive);
			stmt.setObject(3, from);
			stmt.setObject(4, from);
			stmt.setBoolean(5, toInclusive);
			stmt.setObject(6, to);
			stmt.setObject(7, to);
		}

		protected Object preprocessPropertyValue(Object value) throws IOException, SerialException, SQLException {
			if (value.getClass().isArray()) {
				// SQLite cannot store arrays - use Java serialisation for them
				ByteArrayOutputStream bOS = new ByteArrayOutputStream();
				try (ObjectOutputStream oOS = new ObjectOutputStream(bOS)) {
					oOS.writeObject(value);
				}
				value = new SerialBlob(bOS.toByteArray());
			}
			return value;
		}

		protected Object getPropertyValue(PreparedStatement stmt) throws SQLException, IOException, ClassNotFoundException {
			try (ResultSet rs = stmt.getResultSet()) {
				if (rs.next()) {
					String type = rs.getString(1);

					switch (type) {
					case "Boolean":
						// SQLite has no boolean type - it stores true values as 1s
						return rs.getInt(2) == 1;
					case SQLiteConnection.TYPE_BLOB_BASE64:
						// SQLite has no array types - we serialise and store them as base64-encoded strings
						byte[] bytes = Base64.decodeBase64(rs.getString(2));
						try (ObjectInputStream oIS = new ObjectInputStream(new ByteArrayInputStream(bytes))) {
							return oIS.readObject();
						}
					default:
						return rs.getObject(2);
					}
				}
			}

			return null;
		}

		protected Set<String> getStrings(PreparedStatement stmt) throws SQLException {
			try (ResultSet rs = stmt.getResultSet()) {
				Set<String> results = new HashSet<>();
				while (rs.next()) {
					results.add(rs.getString(1));
				}
				return results;
			}
		}

		public String getClearNodeIndexTableSQL(String name) {
			return String.format("DELETE FROM `idx_%s`;", name);
		}

		public String getDropNodeIndexTableSQL(String name) {
			return String.format("DROP TABLE `idx_%s`;", name);
		}

		public void dropIndexTableOnCommit(String name) {
			dropIndicesOnCommit.add(name);
		}
	}

	private SQLiteConnection conn;

	@Override
	public String getHumanReadableName() {
		return "SQLite Database";
	}

	@Override
	public String getPath() {
		return storageFolder.getAbsolutePath();
	}

	@Override
	public void run(File parentfolder, IConsole c) {
		this.storageFolder = parentfolder;
		this.tempFolder = new File(storageFolder, "temp");
		if (!tempFolder.exists()) {
			tempFolder.mkdirs();
		}

		try {
			Connection db = DriverManager.getConnection("jdbc:sqlite:" + new File(storageFolder, SQLITE_FILE).getAbsolutePath());
			db.setAutoCommit(false);
			conn = new SQLiteConnection(db);

			createSchema();
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
		}
	}

	private void createSchema() throws SQLException {
		final SQLiteConnection connection = conn;
		try (Statement stmt = connection.createStatement()) {
			stmt.execute("CREATE TABLE IF NOT EXISTS nodes (label VARCHAR NOT NULL);");
			stmt.execute("CREATE INDEX IF NOT EXISTS nodes_by_label ON nodes (label);");

			// NOTE: SQLite uses dynamic typing (typing by value, not by column)
			stmt.execute("CREATE TABLE IF NOT EXISTS nodeprops (nodeid INTEGER NOT NULL, key VARCHAR NOT NULL, type VARCHAR NOT NULL, value);");
			stmt.execute("CREATE INDEX IF NOT EXISTS nodeprops_by_id ON nodeprops (nodeid);");
			stmt.execute("CREATE UNIQUE INDEX IF NOT EXISTS nodeprops_by_id_key ON nodeprops (nodeid, key);");

			stmt.execute("CREATE TABLE IF NOT EXISTS edges (label VARCHAR NOT NULL, startId INTEGER NOT NULL, endId INTEGER NOT NULL);");
			stmt.execute("CREATE UNIQUE INDEX IF NOT EXISTS edges_unique ON edges (label, startId, endId);");
			stmt.execute("CREATE INDEX IF NOT EXISTS edges_label_start ON edges (label, startId);");
			stmt.execute("CREATE INDEX IF NOT EXISTS edges_label_end ON edges (label, endId);");
			stmt.execute("CREATE INDEX IF NOT EXISTS edges_start ON edges (startId);");
			stmt.execute("CREATE INDEX IF NOT EXISTS edges_end ON edges (endId);");

			stmt.execute("CREATE TABLE IF NOT EXISTS edgeprops (edgeid INTEGER NOT NULL, key VARCHAR NOT NULL, type VARCHAR NOT NULL, value);");
			stmt.execute("CREATE INDEX IF NOT EXISTS edgeprops_by_id ON edgeprops (edgeid);");
			stmt.execute("CREATE UNIQUE INDEX IF NOT EXISTS edgeprops_by_id_key ON edgeprops (edgeid, key);");

			stmt.execute("CREATE TABLE IF NOT EXISTS nodeindices (name UNIQUE NOT NULL);");
			connection.commit();
		}
	}

	@Override
	public void shutdown() throws Exception {
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}

	@Override
	public void delete() throws Exception {
		shutdown();
		deleteRecursively(storageFolder);
	}

	@Override
	public IGraphNodeIndex getOrCreateNodeIndex(String name) {
		try {
			PreparedStatement stmt = conn.getUpsertNodeIndexStatement(name);
			int rowCount = stmt.executeUpdate();
			if (rowCount > 0) {
				try (Statement ddl = conn.createStatement()) {
					ddl.execute(String.format("CREATE TABLE IF NOT EXISTS `idx_%s` (key VARCHAR NOT NULL, nodeId INTEGER NOT NULL, value);", name));
					ddl.execute(String.format("CREATE INDEX IF NOT EXISTS `idx_%s_key` ON `idx_%s` (key);", name, name));
					ddl.execute(String.format("CREATE INDEX IF NOT EXISTS `idx_%s_node` ON `idx_%s` (nodeId);", name, name));
					ddl.execute(String.format("CREATE INDEX IF NOT EXISTS `idx_%s_val` ON `idx_%s` (value);", name, name));
				}
			}

			return new SQLiteNodeIndex(this, name);
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public IGraphNodeIndex getMetamodelIndex() {
		return getOrCreateNodeIndex("_hawk_metamodels");
	}

	@Override
	public IGraphNodeIndex getFileIndex() {
		return getOrCreateNodeIndex("_hawk_files");
	}

	@Override
	public IGraphTransaction beginTransaction() throws Exception {
		return new SQLiteTransaction(this);
	}

	@Override
	public boolean isTransactional() {
		return true;
	}

	@Override
	public void enterBatchMode() {
		currentMode = Mode.NO_TX_MODE;
	}

	@Override
	public void exitBatchMode() {
		currentMode = Mode.TX_MODE;
	}

	@Override
	public IGraphIterable<? extends IGraphNode> allNodes(String label) {
		return new StatementGraphNodeIterable(this,
			() -> conn.getNodeIDsByLabelStatement(label),
			() -> conn.getNodeCountByLabelStatement(label),
			() -> conn.getFirstNodeIDByLabelStatement(label));
	}

	@Override
	public IGraphNode createNode(Map<String, Object> props, String label) {
		try {
			PreparedStatement insertNodeStmt = conn.getInsertNodeStatement();
			insertNodeStmt.setString(1, label);
			final int rowCount = insertNodeStmt.executeUpdate();
			assert rowCount == 1 : "A row should have been inserted for the node";
			int nodeId = insertNodeStmt.getGeneratedKeys().getInt(1);

			SQLiteNode node = new SQLiteNode(this, nodeId);;
			if (props != null) {
				for (Entry<String, Object> e : props.entrySet()) {
					node.setProperty(e.getKey(), e.getValue());
				}
			}

			return node;
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage(), ex);
		}

		return null;
	}

	@Override
	public IGraphEdge createRelationship(IGraphNode start, IGraphNode end, String type, Map<String, Object> props) {
		final int startId = (int) start.getId();
		final int endId = (int) end.getId();

		try {
			PreparedStatement stmt = conn.getInsertEdgeStatement(startId, endId, type);
			int rowCount = stmt.executeUpdate();
			assert rowCount == 1 : "One row should have been inserted for the edge";
			int edgeId = stmt.getGeneratedKeys().getInt(1);

			final SQLiteEdge edge = new SQLiteEdge(this, edgeId, startId, endId, type);
			if (props != null) {
				for (Map.Entry<String, Object> e : props.entrySet()) {
					edge.setProperty(e.getKey(), e.getValue());
				}
			}

			return edge;
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage(), ex);
		}
		
		return null;
	}

	@Override
	public SQLiteConnection getGraph() {
		return conn;
	}

	@Override
	public IGraphNode getNodeById(Object id) {
		if (id instanceof String) {
			return new SQLiteNode(this, Integer.parseInt((String) id));
		} else {
			return new SQLiteNode(this, (int) id);
		}
	}

	@Override
	public String getTempDir() {
		return tempFolder.getAbsolutePath();
	}

	@Override
	public Mode currentMode() {
		return currentMode;
	}

	@Override
	public Set<String> getNodeIndexNames() {
		try {
			PreparedStatement stmt = conn.getAllNodeIndices();
			stmt.execute();
			return conn.getStrings(stmt);
		} catch (SQLException ex) {
			LOGGER.error(ex.getMessage(), ex);
			return Collections.emptySet();
		}
	}

	protected SQLiteConnection getConnection() {
		return conn;
	}

	private static void deleteRecursively(File f) throws IOException {
		if (!f.exists()) return;

		Files.walkFileTree(f.toPath(), new SimpleFileVisitor<Path>() {
			@Override
			public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
				Files.delete(file);
				return FileVisitResult.CONTINUE;
			}
	
			@Override
			public FileVisitResult postVisitDirectory(Path dir, IOException exc) throws IOException {
				Files.delete(dir);
				return FileVisitResult.CONTINUE;
			}
	
		});
	}
	
}
