package nl.anchormen.sql4es.model;

import java.math.BigDecimal;
import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import nl.anchormen.sql4es.model.Column.Operation;

/**
 * Represents all the columns and their column index in the {@link ResultSet}. In addition it keeps track
 * of special scenarios such as 'select * from ..' in which all available columns should be returned or 
 * when client side calculations must be done like sum(column)/10. To support these calculations a Heading might
 * have invisible columns used to store data not accessible to the client.   
 * 
 * @author cversloot
 *
 */
public class Heading {

	public final static String ID = "_id";
	public final static String INDEX = "_index";
	public final static String TYPE = "_type";
	public static final String SCORE = "_score";
	public static final String SEARCH = "_search";
	
	private List<Column> columns = new ArrayList<Column>();
	private HashMap<String, Column> fieldIndex = new HashMap<String, Column>();
	private HashMap<String, Column> aliasIndex = new HashMap<String, Column>();
	private HashMap<String, Integer> labelToColNr = new HashMap<String, Integer>();
	private HashMap<Integer, Integer> columnToColIndex = new HashMap<Integer, Integer>();
	private Map<String, Integer> typeIndex = new HashMap<String, Integer>();
	private boolean allColumns = false;
	private boolean indexed = false;
	
	public Heading(){}
	
	/**
	 * Adds a column to this heading, checking if the column exists as well as adding its type.
	 * @param column
	 */
	public void add(Column column) {
		String colName = column.getColumn(); 
		// remove .* at the end to convert 'field.*' just to 'field' which will be expanded during result parsing
		if(colName.endsWith(".*")) column.setColumn(colName.substring(0, colName.length()-2));
		if(column.getColumn().equals("*") && column.getOp() == Operation.NONE) {
			this.allColumns  = true;
			String table = column.getTable();
			String tableAlias = column.getTableAlias();
			this.add(new Column(ID).setSqlType(Types.VARCHAR).setIndex(getColumnCount()).setTable(table, tableAlias));
			this.add(new Column(INDEX).setSqlType(Types.VARCHAR).setIndex(getColumnCount()).setTable(table, tableAlias));
			this.add(new Column(TYPE).setSqlType(Types.VARCHAR).setIndex(getColumnCount()).setTable(table, tableAlias));
			
			for(Map.Entry<String, Integer> entry : this.typeIndex.entrySet()){
				String field = entry.getKey();
				if(field.equals(ID) || field.equals(TYPE) || field.equals(INDEX)) continue;
				this.add(new Column(field).setSqlType(entry.getValue()).setTable(table, tableAlias));
			}
		} else {
			column.setIndex(this.getColumnCount());
			columns.add(column);
			fieldIndex.put(column.getColumn(), column);
			if(column.getAlias() != null) aliasIndex.put(column.getAlias(), column);
		}
		// set type
		if(column.getColumn().equals(SCORE)) column.setSqlType(Types.DOUBLE);
		if(column.getOp() == Operation.COUNT) column.setSqlType(Types.BIGINT);
		else if(column.getOp() == Operation.AVG || column.hasCalculation()) column.setSqlType(Types.FLOAT);
		else if(column.getSqlType() != Types.ARRAY && typeIndex.containsKey(column.getColumn())){
			column.setSqlType(typeIndex.get(column.getColumn()));
		}
	}
	
	public void remove(Column column){
		for(int i=0; i<columns.size(); i++){
			Column col = columns.get(i);
			if(col.getIndex() == column.getIndex()){
				columns.remove(i);
				this.buildIndex();
				break;
			}
		}
	}
	
	/**
	 * Returns if any of the columns equals '*' indicating all fields must be
	 * fetched and provided in the ResultSet
	 * @return
	 */
	public boolean hasAllCols(){
		return allColumns;
	}
	
	public int getColumnCount(){
		return columns.size();
	}
	
	public Iterable<Column> columns(){
		return columns;
	}
	
	public Column getColumn(int index){
		return columns.get(index);
	}
	
	public Column getColumnByAlias(String alias){
		return aliasIndex.get(alias);
	}
	
	public boolean hasAlias(String alias){
		return aliasIndex.containsKey(alias);
	}
	
	public Column getColumnByLabel(String label){
		return fieldIndex.get(label);
	}
	
	public Column getColumnByNameAndOp(String colName, Operation op){
		for(Column col : columns){ // first check columnname and operation
			if(col.getColumn().equals(colName) && op == col.getOp()) return col;
		}
		for(Column col : columns){ // else check if it matches an alias
			if(colName.equals(col.getAlias())) return col;
		}
		return null;
	}
	
	public boolean hasLabel(String label){
		return fieldIndex.containsKey(label);
	}
	
	public boolean hasLabelStartingWith(String prefix) {
		for(String label : fieldIndex.keySet()){
			if(label.startsWith(prefix) && getColumnByLabel(label).getOp() != Operation.HIGHLIGHT ) return true;
		}
		return false;
	}

	public Column getFirstColumnStartingWith(String prefix) {
		for(Map.Entry<String, Column> entry : fieldIndex.entrySet()){
			if(entry.getKey().startsWith(prefix)) return entry.getValue();
		}
		return null;
	}
	
	public int getJDBCColumnNr(String label) throws SQLException{
		if(!indexed){
			buildIndex();
			indexed = true;
		}
		if(labelToColNr.containsKey(label)){
			return labelToColNr.get(label);
		}
		throw new SQLException("Column with label '"+label+"' does not exist");
	}

	public void setTypes(Map<String, Integer> types){
		if(types == null) return;
		this.typeIndex = types;
		// update types of any columns present within this heading
		for(Column s : columns){
			if(typeIndex.containsKey(s.getColumn())){
				s.setSqlType(typeIndex.get(s.getColumn()));
			}
		}
	}
	
	public Heading setAllColls(boolean value) {
		this.allColumns = value;
		return this;
	}
	
	public boolean followNode(String key){
		return allColumns || this.hasLabel(key) || this.hasLabelStartingWith(key);
	}
	
	/**
	 * Builds the indexes used by the heading for fast access of values in rows. Indexes include
	 * from column name, alias, and jdbc column number (starting at 1) to right array-index 
	 */
	public void buildIndex(){
		fieldIndex.clear();
		labelToColNr.clear();
		aliasIndex.clear();
		columnToColIndex.clear();
		int visIndex = 1;
		for(Column col : columns) {
			fieldIndex.put(col.getLabel(), col);
			fieldIndex.put(col.getAggName(), col);
			labelToColNr.put(col.getLabel(), visIndex);
			labelToColNr.put(col.getFullName(), visIndex);
			if(col.getAlias() != null) aliasIndex.put(col.getAlias(), col);
			if(col.isVisible()){
				columnToColIndex.put(visIndex, col.getIndex());
				visIndex++;
			}
		}
	}
	
	/**
	 * This function fixes column setup after all columns have been provided. It does 3 things:
	 * <ol>
	 * <li>Finds and converts lower-cased column labels and aliases back into their original</li>
	 * <li>Orders the columns as they were specified within the sql (might be mixed up if calculations are present)</li>
	 * <li>Rebuilds the mapping from name/alias to column index</li>
	 * </ol>
	 * @param originalSql
	 * @param columns
	 */
	public void reorderAndFixColumns(String originalSql, String prefix, String suffix){
		//Heading.fixColumnReferences(originalSql, prefix, suffix, columns);
		Collections.sort(this.columns);
		for(int i=0; i<columns.size(); i++) columns.get(i).setIndex(i);
		buildIndex();
	}
	
	public static void fixColumnReferences(String originalSql, String prefix, String suffix, List<Column> columns){
		for(Column c : columns){
			String name = c.getColumn().replaceAll("\\*", "\\\\*");
			String original = findOriginal(originalSql, name, prefix, suffix);
			c.setColumn(original);
			if(c.getOp() != Operation.NONE && c.getAlias() == null){
				switch(c.getOp()){
					case AVG: name = "AVG\\(\\s*"+name+"\\s*\\)"; break;
					case COUNT: name = "COUNT\\(\\s*"+name+"\\s*\\)";break;
					case MAX: name = "MAX\\(\\s*"+name+"\\s*\\)"; break;
					case MIN: name = "MIN\\(\\s*"+name+"\\s*\\)"; break;
					case SUM: name = "SUM\\(\\s*"+name+"\\s*\\)"; break;
					case HIGHLIGHT: name = "HIGHLIGHT\\(\\s*"+name+"\\s*\\)"; break;
					default: name = c.getColumn();
				}
				String alias = findOriginal(originalSql, name, prefix, suffix);
				c.setAlias(alias);
			}
		}
	}
	
	public static String findOriginal(String originalSql, String target, String prefix, String suffix){
		String pattern = target.replaceAll("\\*","\\\\*").replaceAll("\\(", "\\\\s*\\\\(\\\\s*")
				.replaceAll("\\)", "\\\\s*\\\\)\\\\s*").replaceAll("\\$", "\\\\\\$");
		//Matcher.quoteReplacement(target); // does not work for all cases it seems
		Pattern p = Pattern.compile(prefix+"("+pattern+")"+suffix, Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(originalSql.replaceAll("\"", "")); // replace quotes to fix things like SELECT "table"."field" FROM ...
		if(m.find()){
			return m.group(1);
		}
		return target;
	}

	/**
	 * Returns if this heading specifies just a count() in which case there is no need to
	 * fetch all the data
	 * @return
	 */
	public boolean aggregateOnly() {
		if(this.hasAllCols()) return false;
		for(Column s : columns) if(s.getOp() == Operation.NONE && s.getCalculation() == null) return false;
		return true;
	}
	
	/**
	 * Gets the real index for the provided column number (starting with 1)
	 * @param nr
	 * @return
	 */
	public Integer getIndexForColumn(int nr) throws SQLException{
		if(!indexed || columnToColIndex.size() == 0){
			buildIndex();
			indexed = true;
		}
		Integer idx = columnToColIndex.get(nr);
		if(idx == null) throw new SQLException("Column "+nr+" does not exist");
		return idx;
	}
	
	public String toString(){
		StringBuilder sb = new StringBuilder();
		sb.append(" allvisible = "+allColumns+" [");
		for(Column col : columns) sb.append(col.getLabel() +" ("+col.isVisible()+"), ");
		sb.append("]");
		return sb.toString();
	}
	
	/**
	 * Returns the class associated with a java.sql.Types id
	 * @param type
	 * @return
	 */
	public static Class<?> getClassForTypeId(int type){
		switch(type){
			case Types.ARRAY : return Array.class;
			case Types.BIGINT : return Long.class;
			case Types.TINYINT : return Byte.class;
			case Types.BINARY : return Byte[].class;
			case Types.BIT : return Boolean.class;
			case Types.BOOLEAN : return Boolean.class;
			case Types.CHAR : return Character.class;
			case Types.DATE : return java.util.Date.class;
			case Types.DOUBLE : return Double.class;
			case Types.FLOAT : return Float.class;
			case Types.INTEGER : return Integer.class;
			case Types.NUMERIC : return BigDecimal.class;
			case Types.SMALLINT : return Short.class;
			case Types.LONGVARCHAR : return String.class;
			case Types.REAL : return Float.class;
			case Types.VARCHAR : return String.class;
			case Types.TIME : return Time.class;
			case Types.TIMESTAMP : return Timestamp.class;
			case Types.LONGVARBINARY : return Byte[].class;
			case Types.VARBINARY : return Byte[].class;
			default : return Object.class;
		}
	}

	public static int getTypeIdForObject(Object c) {
		if (c instanceof Long)
			return Types.BIGINT;
		if (c instanceof Boolean)
			return Types.BOOLEAN;
		if (c instanceof Character)
			return Types.CHAR;
		if (c instanceof Timestamp)
			return Types.TIMESTAMP;
		if (c instanceof java.sql.Date)
			return Types.DATE;
		if (c instanceof java.util.Date)
			return Types.DATE;
		if (c instanceof Double)
			return Types.DOUBLE;
		if (c instanceof Integer)
			return Types.INTEGER;
		if (c instanceof BigDecimal)
			return Types.NUMERIC;
		if (c instanceof Short)
			return Types.SMALLINT;
		if (c instanceof Float)
			return Types.FLOAT;
		if (c instanceof String)
			return Types.VARCHAR;
		if (c instanceof Time)
			return Types.TIME;
		if (c instanceof Byte)
			return Types.TINYINT;
		if (c instanceof Byte[])
			return Types.VARBINARY;
		if(c instanceof Object[])
			return Types.JAVA_OBJECT;
		if(c instanceof Object)
			return Types.JAVA_OBJECT;
		if (c instanceof Array)
			return Types.ARRAY;
		else
			return Types.OTHER;
	}

}
