Datatable.java

/*
 * Copyright 2019 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.gringlobal.soap;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.lang.ref.WeakReference;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.TreeSet;
import java.util.concurrent.atomic.AtomicInteger;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.text.StringEscapeUtils;
import org.jdom2.Attribute;
import org.jdom2.Element;
import org.jdom2.Namespace;
import org.jdom2.Verifier;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonUnwrapped;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;

import lombok.Getter;
import lombok.NonNull;

@Slf4j
public class Datatable {

	private static final String EXCEPTION_TABLE_NAME = "ExceptionTable";

	/**
	 * Diffgram diffgr:hasChanges property
	 */
	public static enum HasChanges {
		inserted,
		deleted,
		modified,
		original
	}

	private static ThreadLocal<SimpleDateFormat> soapDateFormat = new ThreadLocal<SimpleDateFormat>() {
		@Override
		protected SimpleDateFormat initialValue() {
			// 2009-01-09T00:00:00
			final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
//			sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
			return sdf;
		}
	};

	private static Optional<Attribute> findAttr(final Set<Attribute> attrs, final String qualifiedName) {
		return attrs.stream().filter(a -> a.getQualifiedName().equals(qualifiedName)).findFirst();
	}

	private static Set<Attribute> ensureAttr(final Set<Attribute> attrs, final Namespace ns, final String name, final String value) {
		final Optional<Attribute> attr = findAttr(attrs, ns.getPrefix() + ":" + name);
		if (!attr.isPresent()) {
			attrs.add(new Attribute(name, value == null ? "" : value, ns));
		} else {
			attr.get().setValue(value == null ? "" : value);
		}
		return attrs;
	}

	public static class Column {
		private final Type type;
		private final String name;
		private final Set<Attribute> attrs = new HashSet<>();

		public Column(final String name, final Type type) {
			this.name = name;
			this.type = type;
		}

		public String getName() {
			return name;
		}

		public Type getType() {
			return type;
		}

		public void setReadonly(final boolean b) {
			ensureAttr(this.attrs, GGXml.NsMsDATA, "ReadOnly", Boolean.toString(b));
		}

		/**
		 * Set msprop:attr
		 *
		 * @param name
		 * @param value
		 */
		public void setProp(final String name, final String value) {
			ensureAttr(this.attrs, GGXml.NsMsPROP, name, value);
		}

		/**
		 * Get "msprop:prop=" attribute
		 *
		 * @param name
		 * @param value
		 */
		public String getProp(final String prop) {
			var attr = findAttr(attrs, GGXml.NsMsPROP.getPrefix() + ":" + prop);
			return attr.isPresent() ? attr.get().getValue() : null;
		}

		/**
		 * Set prop if not defined, update only if new value is 'better'
		 *
		 * @param string
		 * @param value
		 */
		public void improveProp(final String prop, final String value) {
			final Optional<Attribute> attr = findAttr(attrs, GGXml.NsMsPROP.getPrefix() + ":" + prop);
			if (attr.isPresent()) {
				if (StringUtils.isBlank(attr.get().getValue())) {
					log.trace("Updating prop {} from {} to {}", prop, attr.get().getValue(), value);
					attr.get().setValue(value);
				}
			} else {
				setProp(prop, value);
			}
		}

		/**
		 * Set the specified "msdata:prop=" to value
		 *
		 * @param name
		 * @param value
		 */
		public void setData(final String name, final String value) {
			ensureAttr(this.attrs, GGXml.NsMsDATA, name, value);
		}

		/**
		 * Get the specified "msdata:prop=" attribute
		 * @param prop
		 * @return
		 */
		public String getData(final String prop) {
			var attr = findAttr(attrs, GGXml.NsMsDATA.getPrefix() + ":" + prop);
			return attr.isPresent() ? attr.get().getValue() : null;
		}
	}

	/** 
	 * Custom JSON serializer for Datatable.Row that writes each row as a JSON object.
	 */
	public static class RowSerializer extends JsonSerializer<Row> {
		@Override
		public void serialize(Row value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
			serializers.defaultSerializeValue(value.getRowData(), gen);
		}
	}

	@JsonSerialize(using = RowSerializer.class)
	public static class Row {
		WeakReference<Datatable> dt;
		int rowOrder = -1;
		HasChanges changeType = HasChanges.original;
		Object[] d;

		public Row(@NonNull Datatable dt, int rowOrder) {
			this.dt = new WeakReference<>(dt);
			this.rowOrder = rowOrder;
			this.d = new Object[dt.columns.size()];
		}

		public Row(@NonNull Datatable dt, int rowOrder, @NonNull HasChanges changeType) {
			this.dt = new WeakReference<>(dt);
			this.rowOrder = rowOrder;
			this.changeType = changeType;
			this.d = new Object[dt.columns.size()];
		}

		private Row(@NonNull Datatable dt, int rowOrder, @NonNull HasChanges changeType, @NonNull Object[] d) {
			this.dt = new WeakReference<>(dt);
			this.rowOrder = rowOrder;
			this.changeType = changeType;
			this.d = d;
		}

		/**
		 * Returns a copy of the internal row data array.
		 * @deprecated use {@link #getValue(int)}
		 */
		@Deprecated
		public Object[] getData() {
			return Arrays.copyOf(d, d.length);
		}

		/**
		 * Get row value at specified column
		 * @param columnIndex Column index
		 * @return Column value
		 */
		public Object getValue(int columnIndex) {
			return d[columnIndex];
		}

		public Map<String, Object> getRowData() {
			var rowData = new HashMap<String, Object>(d.length);
			List<Column> columns = dt.get().columns;
			for (var col : columns) {
				rowData.put(col.name, d[columns.indexOf(col)]);
			}
			return rowData;
		}

		public Map<String, Object> updateRowData(Map<String, Object> updates) throws DatatableException {
			if (changeType == HasChanges.deleted) {
				throw new DatatableException("Cannot modify deleted row");
			}
			Datatable datatable = dt.get();
			List<Column> columns = datatable.columns;
			if (! datatable.columnNames.containsAll(updates.keySet())) {
				throw new DatatableException.NoSuchColumnException("No such column");
			}
			var newD = Arrays.copyOf(d, d.length);
			for (var col : columns) {
				var update = updates.get(col.name);
				if (update != null) {
					newD[columns.indexOf(col)] = update;
				}
			}
			datatable.assertTypes(newD);
			if (Arrays.deepEquals(d, newD)) {
				// no change
				return getRowData();
			}
			if (changeType != HasChanges.inserted) {
				changeType = HasChanges.modified; // Flag row as modified
				datatable.before.add(new Row(datatable, this.rowOrder, HasChanges.original, d)); // Add to before if not there yet
			}
			d = newD; // Change row data
			return getRowData();
		}

		public void deleteRow() {
			Datatable datatable = dt.get();
			if (changeType == HasChanges.deleted) {
				// Noop
			} else if (changeType == HasChanges.inserted) {
				// Since the row didn't exist previously it does not exist in datatable.before so we can just remove it from datatable.rows
				datatable.rows.remove(this);
				this.dt = null;
			} else if (changeType == HasChanges.modified) {
				// Since the row has been modified it must already exist in datatable.before (but we add it just in case) and it is removed from datatable.rows
				datatable.before.add(new Row(datatable, this.rowOrder, HasChanges.original, d)); // Add to before
				datatable.rows.remove(this);
				this.dt = null;
			} else if (changeType == null || changeType == HasChanges.original) {
				// This row must be added to datatable.before and can be removed from datatable.rows
				datatable.before.add(new Row(datatable, this.rowOrder, HasChanges.original, d)); // Add to before
				datatable.rows.remove(this);
				this.dt = null;
			}
		}
	}

	public static class DatatableException extends java.lang.Exception {
		private static final long serialVersionUID = -9054878115374555658L;
		public DatatableException(final String message) {
			super(message);
		}

		public static class NoSuchColumnException extends DatatableException {
			private static final long serialVersionUID = 3778287898823934710L;
			public NoSuchColumnException(String message) {
				super(message);
			}
		}
	}

	@Getter
	public static class Exception {
		int index;
		String message;
//		Object data;
		String stackTrace;
		String source = "API";
		public String exceptionType;

		/**
		 * Create a new exception.
		 * 
		 * @param index Exception index, must be tracked separately
		 * @param throwable The cause of the error
		 * @param generateStacktrace Should the stacktrace be generated?
		 */
		public Exception(int index, Throwable throwable, boolean generateStacktrace) {
			this.index = index;

			var cause = throwable;
			message = cause.getMessage();
			// getCause() should not be recursive
			while (cause.getCause() != null && !cause.equals(cause.getCause())) {
				if (cause.getMessage() != null) message = cause.getMessage();
				cause = cause.getCause();
			}
			exceptionType = cause.getClass().getName();
			message = cause.getMessage();

			if (generateStacktrace) {
				try (var sw = new StringWriter(1000)) {
					cause.printStackTrace(new PrintWriter(sw));
					this.stackTrace = sw.toString();
				} catch (IOException e1) {
					log.warn("Could not print stacktrace: {}", e1.getMessage());
				}
			}
		}

		/**
		 * Create a new exception but don't include the stacktrace.
		 * 
		 * @param index Exception index, must be tracked separately
		 * @param throwable The cause of the error
		 */
		public Exception(int index, Throwable throwable) {
			this.index = index;

			var cause = throwable;
			// getCause() should not be recursive
			while (cause.getCause() != null && !cause.equals(cause.getCause())) {
				cause = cause.getCause();
			}
			exceptionType = cause.getClass().getName();
			message = cause.getMessage();
		}
	}

	private static Comparator<Row> rowOrderComparator = Comparator.comparing((Row row) -> row.rowOrder);

	private String name = "dt";
	private AtomicInteger nextRowOrder = new AtomicInteger(0);
	private final List<Datatable.Column> columns = new ArrayList<>();
	private final Set<String> columnNames = new HashSet<>();
	private final List<Datatable.Row> rows = new LinkedList<>();
	private final TreeSet<Datatable.Row> before = new TreeSet<>(rowOrderComparator);
	private final Set<Attribute> attrs = new HashSet<>();
	private final List<Datatable.Exception> exceptions = new ArrayList<>();

	private int limit;

	private int offset;

	private static NumberFormat DECIMAL_FORMATTER;

	static {
		DECIMAL_FORMATTER = NumberFormat.getInstance(Locale.US);
		DECIMAL_FORMATTER.setMinimumIntegerDigits(1);
		DECIMAL_FORMATTER.setMinimumFractionDigits(2);
		DECIMAL_FORMATTER.setMaximumFractionDigits(20);
		DECIMAL_FORMATTER.setGroupingUsed(false);
	}

	public Datatable() {
	}

	public Datatable(final String name) {
		this.name = name;
	}

	public Datatable(final String name, final List<Column> tableColumns) {
		this.name = name;
		for (var column : tableColumns) {
			if (columnNames.contains(column.name)) {
				throw new RuntimeException("Duplicate column name " + column.name);
			}
			columns.add(column);
			columnNames.add(column.name);
		}
	}

	public String getName() {
		return name;
	}

	public void setReadonly(final String b) {
		ensureAttr(this.attrs, GGXml.NsMsPROP, "is_readonly", b);
	}

	/**
	 * Set msprop:attr
	 *
	 * @param name
	 * @param value
	 */
	public void setProp(final String name, final String value) {
		ensureAttr(this.attrs, GGXml.NsMsPROP, name, value);
	}

	/**
	 * Get msprop:attr
	 *
	 * @param name
	 * @return
	 */
	public String getProp(final String name) {
		final Optional<Attribute> attr = findAttr(attrs, GGXml.NsMsPROP.getPrefix() + ":" + name);
		if (!attr.isPresent()) {
			return null;
		} else {
			return attr.get().getValue();
		}
	}

	public Column addColumn(final String name, final Type type) throws DatatableException {
		if (rows.size() > 0 || before.size() > 0) {
			throw new DatatableException("Datatable contains rows, cannot change columns");
		}
		synchronized (columnNames) {
			if (columnNames.contains(name)) {
				throw new RuntimeException("Column " + name + " already registered");
			}
			var column = new Column(name, type);
			columns.add(column);
			columnNames.add(name);
			// TODO Resize all rows and remove exception above
			return column;
		}
	}

	/**
	 * Add row of data
	 * @param values row values
	 * @return Row filled with data
	 */
	public Row addRow(HasChanges changeType, final Object... values) {
		if (changeType == HasChanges.deleted || changeType == HasChanges.modified) {
			throw new RuntimeException("Invalid row changeType " + changeType);
		}
		assertTypes(values);
		final Row r = new Row(this, nextRowOrder.getAndIncrement(), changeType, Arrays.copyOf(values, columns.size()));
		rows.add(r);
		return r;
	}

	/**
	 * Convert Map<columnName, value> to Row
	 * @param rowData Map of columnNames with values
	 * @return Row filled with data
	 * @throws DatatableException 
	 */
	public Row addRow(final Map<String, Object> rowData) throws DatatableException {
		final Row r = new Row(this, nextRowOrder.getAndIncrement(), HasChanges.inserted);
		r.updateRowData(rowData);
		rows.add(r);
		return r;
	}

	private void assertTypes(final Object[] values) {
		int index = -1;
		for (final Column col : columns) {
			index++;
			if (values[index] == null) {
				continue;
			}
//			System.err.println("col " + col.name + " "  + col.type + " <-- " + values[index].getClass());
			try {
				assert((Class<?>) col.type).isAssignableFrom(values[index].getClass());
			} catch (AssertionError e) {
				log.error("Invalid value type for column {} {}: {}", col.type, col.name, values[index]);
				throw e;
			}
		}
	}

	/**
	 * Commit row status: remove deleted rows, accept inserted and modified rows.
	 * 
	 * In practice this means clearing {@code before} list and clearing {@link Row#changeType}. 
	 */
	public void acceptChanges() {
		before.clear();
		nextRowOrder.set(0);
		rows.forEach(row -> {
			row.changeType = HasChanges.original; // Set row status to "original"
			row.rowOrder = nextRowOrder.getAndIncrement(); // Renumber rowOrder
		});
	}

	public Element toDiffgram(final String rootName, final String namespaceUri) {
		return toDiffgram(rootName, namespaceUri, "SecureDataDataSet");
	}

	public Element toDiffgram(final String rootName, final String namespaceUri, final String datasetName) {
		final Element datatable = new Element(rootName, namespaceUri);

		datatable.addContent(generateSchema());

		final Element diffgram = new Element("diffgram", GGXml.NsDIFFGR);
		diffgram.addNamespaceDeclaration(GGXml.NsMsDATA);
		datatable.addContent(diffgram);
		// <SecureDataDataSet xmlns="">
		final Element secureDataDataSet = new Element(datasetName);
		diffgram.addContent(secureDataDataSet);
		// Add exceptions to SecureDataDataSet
		for (final var exception : exceptions) {
			secureDataDataSet.addContent(exceptionToDiffgram(exception));
		}
		// Add rows to SecureDataDataSet
		for (final Row row : rows) {
			secureDataDataSet.addContent(rowToDiffgram(row));
		}
		if (! CollectionUtils.isEmpty(before)) {
			var diffgrBefore = new Element("before", GGXml.NsDIFFGR);
			diffgram.addContent(diffgrBefore);
			for (final Row row : before) {
				diffgrBefore.addContent(rowToDiffgram(row));
			}
		}

		return datatable;
	}

	public static Element exceptionToDiffgram(final Datatable.Exception exception) {
		final Element r = new Element(EXCEPTION_TABLE_NAME);
		r.setAttribute("id", EXCEPTION_TABLE_NAME + exception.index, GGXml.NsDIFFGR);
		r.setAttribute("rowOrder", Integer.toString(exception.index), GGXml.NsMsDATA);

		// <xs:element name="ExceptionIndex" type="xs:int" minOccurs="0" />
		var element = new Element("ExceptionIndex");
		element.setText(Integer.toString(exception.index));
		r.addContent(element);

		// <xs:element name="ExceptionType" type="xs:string" minOccurs="0" />
		element = new Element("ExceptionType");
		element.setText(serializeValue(exception.exceptionType));
		r.addContent(element);

		// <xs:element name="Data" type="xs:string" minOccurs="0" />
		element = new Element("Data");
//		if (exception.data != null) {
//			element.setText(serializeValue(exception.data));
//		}
		r.addContent(element);

		// <xs:element name="Message" type="xs:string" minOccurs="0" />
		element = new Element("Message");
		element.setText(exception.message);
		r.addContent(element);

		// <xs:element name="Source" type="xs:string" minOccurs="0" />
		element = new Element("Source");
		element.setText(exception.source);
		r.addContent(element);

		// <xs:element name="StackTrace" type="xs:string" minOccurs="0" />
		element = new Element("StackTrace");
		element.setText(exception.stackTrace);
		r.addContent(element);

		// <xs:element name="InnerException" type="xs:string" minOccurs="0" />
//		element = new Element("InnerException");
//		element.setText(serializeValue(rowIndex + 1));
//		r.addContent(element);

		return r;
	}

	public Element rowToDiffgram(final Row row) {
		final Element r = new Element(this.name);
		r.setAttribute("id", this.name + (row.rowOrder), GGXml.NsDIFFGR);
		r.setAttribute("rowOrder", Integer.toString(row.rowOrder), GGXml.NsMsDATA);
		if (row.changeType != null && row.changeType != HasChanges.original) {
			r.setAttribute("hasChanges", row.changeType.name(), GGXml.NsDIFFGR);
		}
		int index = -1;
		for (final Column col : columns) {
			index++;
			final Object d = row.d[index];
			if (d == null) {
				continue;
			}
			final Element c = new Element(col.getName());
			if (d != null) {
				c.setText(serializeValue(d));
			}
			r.addContent(c);
		}
		return r;
	}

	private static String serializeValue(final Object d) {
		assert d != null;

		if (d instanceof Date) {
			final Date date = (Date) d;
			return soapDateFormat.get().format(date);
		}
		if (d instanceof Calendar) {
			final Calendar calendar = (Calendar) d;
			return soapDateFormat.get().format(calendar.getTime());
		}
		if (d instanceof Double || d instanceof Float || d instanceof BigDecimal) {
			return DECIMAL_FORMATTER.format(d); // Needs custom formatting
		}
		final String txt = d.toString();
		if (requiresEscaping(txt)) {
			return StringEscapeUtils.escapeXml10(txt);
		} else {
			return txt;
		}
	}

	/**
	 * Check if text requires XML escaping.
	 */
	private static boolean requiresEscaping(final String txt) {
		if (txt.indexOf('<') > -1) {
			return true;
		}
		if (Verifier.checkCharacterData(txt) != null) {
			return true;
		}
		return false;
	}

	private Element generateSchema() {
		final Element schema = new Element("schema", GGXml.NsSCHEMA);
		schema.setAttribute("id", "SecureDataDataSet");
		schema.addNamespaceDeclaration(GGXml.NsMsDATA);

		// <xs:element name="SecureDataDataSet" msdata:IsDataSet="true"
		// msdata:UseCurrentLocale="true">
		final Element element = new Element("element", GGXml.NsSCHEMA);
		element.setAttribute("IsDataSet", "true", GGXml.NsMsDATA);
		element.setAttribute("UseCurrentLocale", "true", GGXml.NsMsDATA);
		element.setAttribute("name", "SecureDataDataSet");
		schema.addContent(element);

		// <xs:complexType>
		Element ct = new Element("complexType", GGXml.NsSCHEMA);
		element.addContent(ct);

		// <xs:choice minOccurs="0" maxOccurs="unbounded">
		// <xs:element name="ExceptionTable">
		final Element choice = datasetEmptySchemaWithExceptions();
		ct.addContent(choice);


		// Declare Datatable type and columns, ONLY if columns are declared
		if (columns.size() > 0) {
			// <xs:element name="validate_login">
			final Element typeElement = new Element("element", GGXml.NsSCHEMA);
			typeElement.setAttribute("name", this.name);
			attrs.forEach(attr -> {
				typeElement.setAttribute(attr.clone());
			});
			choice.addContent(typeElement);
	
			// <xs:complexType>
			ct = new Element("complexType", GGXml.NsSCHEMA);
			typeElement.addContent(ct);
	
			// <xs:sequence>
			final Element seq = new Element("sequence", GGXml.NsSCHEMA);
			ct.addContent(seq);
	
			for (final Column col : columns) {
				// <xs:element name="sys_user_id" type="xs:int" minOccurs="0" />
				final Element field = fieldElement(col.getName(), toXmlType(col.getType()), 0);
				col.attrs.forEach(attr -> {
					field.setAttribute(attr.clone());
				});
				seq.addContent(field);
			}
		}

		return schema;
	}

	private Element datasetEmptySchemaWithExceptions() {
		// <xs:choice minOccurs="0" maxOccurs="unbounded">
		final Element choice = new Element("choice", GGXml.NsSCHEMA);
		choice.setAttribute("minOccurs", "0");
		choice.setAttribute("maxOccurs", "unbounded");

		choice.addContent(makeExceptionTableSchema());
		return choice;
	}

	public static Element makeExceptionTableSchema() {
		// <xs:element name="ExceptionTable">
		final Element typeElement = new Element("element", GGXml.NsSCHEMA);
		typeElement.setAttribute("name", EXCEPTION_TABLE_NAME);

		// <xs:complexType>
		final Element ct = new Element("complexType", GGXml.NsSCHEMA);
		typeElement.addContent(ct);

		// <xs:sequence>
		final Element seq = new Element("sequence", GGXml.NsSCHEMA);
		ct.addContent(seq);

		// <xs:element name="ExceptionIndex" type="xs:int" minOccurs="0" />
		seq.addContent(fieldElement("ExceptionIndex", "xs:int", 0));
		// <xs:element name="ExceptionType" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("ExceptionType", "xs:string", 0));
		// <xs:element name="Data" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("Data", "xs:string", 0));
		// <xs:element name="Message" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("Message", "xs:string", 0));
		// <xs:element name="Source" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("Source", "xs:string", 0));
		// <xs:element name="StackTrace" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("StackTrace", "xs:string", 0));
		// <xs:element name="InnerException" type="xs:string" minOccurs="0" />
		seq.addContent(fieldElement("InnerException", "xs:string", 0));

		return typeElement;
	}

	public static Element fieldElement(final String name, final String type, final int minOccurs) {
		final Element element = new Element("element", GGXml.NsSCHEMA);
		element.setAttribute("name", name);
		element.setAttribute("type", type);
		element.setAttribute("minOccurs", Integer.toString(minOccurs));
		return element;
	}

	private static String toXmlType(final Type type) {
		final Class<?> typeCls = (Class<?>) type;
		if (String.class.isAssignableFrom(typeCls)) {
			return "xs:string";
		} else if (typeCls.equals(String.class)) {
			return "xs:string";
		} else if (Integer.class.isAssignableFrom(typeCls) || Long.class.isAssignableFrom(typeCls) || Short.class.isAssignableFrom(typeCls) || BigInteger.class.isAssignableFrom(typeCls)) {
			return "xs:int"; // CT is fixed on integers
		} else if (Objects.equals(typeCls, int.class) || Objects.equals(typeCls, long.class) || Objects.equals(typeCls, short.class)) {
			return "xs:int";
		} else if (Double.class.isAssignableFrom(typeCls) || Float.class.isAssignableFrom(typeCls) || BigDecimal.class.isAssignableFrom(typeCls)) {
			return "xs:decimal";
		} else if (Number.class.isAssignableFrom(typeCls)) {
			return "xs:decimal";
		} else if (Date.class.isAssignableFrom(typeCls) || Calendar.class.isAssignableFrom(typeCls) || Instant.class.isAssignableFrom(typeCls)) {
			return "xs:dateTime";
		} else {
			log.warn("Type not matched: " + typeCls + " " + typeCls.getClass());
			return "xs:string";
		}
	}

	public static Datatable from(final String dataviewName, final ResultSet resultSet, final ResultSetMetaData metaData, final int offset, final int limit) throws SQLException {

		// Make Datatable columns
		final int columnCount = metaData.getColumnCount();
		var columns = new ArrayList<Column>(columnCount);
		for (int i = 1; i <= columnCount; i++) {
			try {
				columns.add(new Column(metaData.getColumnLabel(i).toLowerCase(), Class.forName(metaData.getColumnClassName(i))));
			} catch (final ClassNotFoundException e) {
				throw new RuntimeException("Could not detect data type", e);
			}
		}

		final Datatable dt = new Datatable(dataviewName, columns);
		dt.setOffset(offset);
		dt.setLimit(limit);

		while ((limit == 0 || dt.rows.size() < limit) && resultSet.next()) {
			final Object[] row = new Object[columnCount];
			for (int i = 0; i < columnCount; i++) {
				row[i] = resultSet.getObject(i + 1);
			}
			if (dt.rows.size() < 10) {
				if (log.isTraceEnabled()) {
					log.trace("Row: {}", ArrayUtils.toString(row, "N/A"));
				}
			}
			dt.addRow(HasChanges.original, row);
		}

		if (limit > 0 && dt.rows.size() > limit) {
			log.warn("Datatable for {} has {} rows but the limit is {}", dataviewName, dt.rows.size(), limit);
		}
		if (resultSet.next()) {
			log.warn("Resultset for {} has some more rows, but already we reached the limit {}.", dataviewName, limit);
		}
//		dt.acceptChanges(); // All rows are HasChanges.original
		return dt;
	}

	private void setLimit(final int limit) {
		this.limit = limit;
	}

	public int getLimit() {
		return limit;
	}

	private void setOffset(final int offset) {
		this.offset = offset;
	}

	public int getOffset() {
		return offset;
	}

	public List<Column> getColumns() {
		return columns;
	}

	public Column getColumn(String columnName) {
		return columns.stream().filter(c -> Objects.equals(columnName, c.name)).findFirst().orElse(null);
	}

	/**
	 * Find column index by name
	 * 
	 * @param columnName the column name
	 * @return column index or {@code -1} if not found
	 */
	public int getColumnIndex(String columnName) {
		for (var i = 0; i < columns.size(); i++) {
			var c = columns.get(i);
			if (Objects.equals(c.getName(), columnName)) return i;
		}
		return -1;
	}

	public List<Datatable.Row> getRows() {
		return List.copyOf(rows);
	}

	public Datatable.Row getRow(int rowIndex) {
		return rows.get(rowIndex);
	}

	public Datatable.Exception addException(Throwable e) {
		var exception = new Datatable.Exception(this.exceptions.size() + 1, e);
		this.exceptions.add(exception);
		return exception;
	}

	/**
	 * Get rowData as a Map<columnName, value>
	 * @param i row index in {@link #getRows()}
	 * @return Map of columnNames to values
	 */
	public Map<String, Object> getRowData(int i) {
		return rows.get(i).getRowData();
	}

	public Map<String, Object> updateRowData(int rowIndex, final Map<String, Object> rowData) throws DatatableException {
		return rows.get(rowIndex).updateRowData(rowData);
	}

	/**
	 * Add Curator Tool row metadata columns to schema. 
	 * 
	 * These are columns for update/error fields of each row in SecureDataDataSet:
	 * ExceptionIndex, ExceptionMessage, NewPrimaryKeyID, OriginalPrimaryKeyID,
	 * SavedAction, SavedStatus, TableName, AliasName
	 * 
	 * @param dataviewSchemaRoot
	 */
	public static void addRowMetadataSchemaElements(Element dataviewSchemaRoot) {
		dataviewSchemaRoot.addContent(Datatable.fieldElement("ExceptionIndex", "xs:int", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("ExceptionMessage", "xs:string", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("NewPrimaryKeyID", "xs:int", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("OriginalPrimaryKeyID", "xs:int", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("SavedAction", "xs:string", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("SavedStatus", "xs:string", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("TableName", "xs:string", 0));
		dataviewSchemaRoot.addContent(Datatable.fieldElement("AliasName", "xs:string", 0));
	}

}