/*
 * Copyright 2007 - 2016 the original author or authors.
 *
 * 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 net.sf.jailer.util;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;

import net.sf.jailer.database.DBMS;
import net.sf.jailer.database.SQLDialect;
import net.sf.jailer.datamodel.DataModel;
import net.sf.jailer.datamodel.Table;
import net.sf.jailer.entitygraph.EntityGraph;

/**
 * Some utility methods.
 * 
 * @author Ralf Wisser
 */
public class SqlUtil {
    
    /**
     * Change alias A to B and B to A in a SQL-condition.
     * 
     * @param condition the condition
     * @return condition with revered aliases
     */
    public static String reversRestrictionCondition(String condition) {
        final String chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_";
        StringBuffer reversed = new StringBuffer("");
        for (int i = 0; i < condition.length(); ++i) {
            char c = condition.charAt(i);
            if (c == 'A' || c == 'B' || c == 'a' || c == 'b') {
                if (i + 1 < condition.length() && condition.charAt(i + 1) == '.') {
                    if (i == 0 || chars.indexOf(condition.charAt(i - 1)) < 0) {
                        reversed.append(c == 'A' || c == 'a'? 'B' : 'A');
                        continue;
                    }
                }
            }
            reversed.append(c);
        }
        return reversed.toString();
    }
    
    /**
     * Replaces the aliases A and B with given aliases in a SQL-condition.
     * 
     * @param condition the condition
     * @param aliasA alias for A
     * @param aliasB alias for B
     * @return condition with replaced aliases
     */
    public static String replaceAliases(String condition, String aliasA, String aliasB) {
        final String chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_";
        StringBuffer result = new StringBuffer("");
        for (int i = 0; i < condition.length(); ++i) {
            char c = condition.charAt(i);
            if (c == 'A' || c == 'B' || c == 'a' || c == 'b') {
                if (i + 1 < condition.length() && condition.charAt(i + 1) == '.') {
                    if (i == 0 || chars.indexOf(condition.charAt(i - 1)) < 0) {
                        String alias = c == 'A' || c == 'a'? aliasA : aliasB;
                        if (alias == null) {
                        	++i; // skip '.'
                        } else {
                        	result.append(alias);
                        }
                        continue;
                    }
                }
            }
            result.append(c);
        }
        return result.toString();
    }
    
    /**
     * Replaces the alias T with given alias in a SQL-condition.
     * 
     * @param condition the condition
     * @param alias alias for T
     * @return condition with replaced aliases
     */
    public static String replaceAlias(String condition, String alias) {
        final String chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_";
        StringBuffer result = new StringBuffer("");
        for (int i = 0; i < condition.length(); ++i) {
            char c = condition.charAt(i);
            if (c == 'T' || c == 't') {
                if (i + 1 < condition.length() && condition.charAt(i + 1) == '.') {
                    if (i == 0 || chars.indexOf(condition.charAt(i - 1)) < 0) {
                        result.append(alias);
                        continue;
                    }
                }
            }
            result.append(c);
        }
        return result.toString();
    }
    
    /**
     * Resolves the pseudo-columns in a restriction condition.
     * 
     * @param condition the condition
     * @param entityAAlias alias for entity table joined with A
     * @param entityBAlias alias for entity table joined with B
     * @param birthdayOfSubject birthday of subject
     * @param today today
     * @param inDeleteMode 
     */
    public static String resolvePseudoColumns(String condition, String entityAAlias, String entityBAlias, int today, int birthdayOfSubject, boolean inDeleteMode) {
    	return resolvePseudoColumns(condition, entityAAlias, entityBAlias, today, birthdayOfSubject, "birthday", inDeleteMode);
    }

    /**
     * Resolves the pseudo-columns in a restriction condition.
     * 
     * @param condition the condition
     * @param entityAAlias alias for entity table joined with A
     * @param entityBAlias alias for entity table joined with B
     * @param birthdayOfSubject birthday of subject
     * @param today today
     * @param birthdayColumnName name of the column which holds the birthday of an entity ('birthday' or 'orig_birthday')
     * @param inDeleteMode 
     */
    public static String resolvePseudoColumns(String condition, String entityAAlias, String entityBAlias, int today, int birthdayOfSubject, String birthdayColumnName, boolean inDeleteMode) {
    	String aBirthday = entityAAlias == null? "" + (today - birthdayOfSubject) : ("(" + entityAAlias + "." + birthdayColumnName + " - " + birthdayOfSubject + ")");
    	String bBirthday = entityBAlias == null? "" + (today - birthdayOfSubject) : ("(" + entityBAlias + "." + birthdayColumnName + " - " + birthdayOfSubject + ")");
    	String aIsSubject = entityAAlias == null? "(" + (today - birthdayOfSubject) + " = 0)" : ("(" + entityAAlias + "." + birthdayColumnName + " - " + birthdayOfSubject + " = 0)");
    	String bIsSubject = entityBAlias == null? "(" + (today - birthdayOfSubject) + " = 0)" : ("(" + entityBAlias + "." + birthdayColumnName + " - " + birthdayOfSubject + " = 0)");

    	condition = condition.replaceAll("(?i:a\\s*\\.\\s*\\$distance)", Matcher.quoteReplacement(aBirthday));
		condition = condition.replaceAll("(?i:b\\s*\\.\\s*\\$distance)", Matcher.quoteReplacement(bBirthday));
    	condition = condition.replaceAll("(?i:a\\s*\\.\\s*\\$is_subject)", Matcher.quoteReplacement(aIsSubject));
    	condition = condition.replaceAll("(?i:b\\s*\\.\\s*\\$is_subject)", Matcher.quoteReplacement(bIsSubject));
    	
    	condition = condition.replaceAll("(?i:\\$in_delete_mode)", Matcher.quoteReplacement(inDeleteMode? "(1=1)" : "(1=0)"));
    	
    	return condition;
    }

    /**
     * Resolves the pseudo-columns in a restriction condition.
     * 
     * @param condition the condition
     * @param birthdayOfSubject birthday of subject
     * @param today today
     * @param reversed 
     * @param inDeleteMode 
     */
    public static String resolvePseudoColumns(String condition, int today, int birthdayOfSubject, boolean reversed, boolean inDeleteMode) {
    	int da = reversed? 0 : 1;
    	int db = reversed? 1 : 0;
    	String aBirthday = "" + (today - birthdayOfSubject - da);
    	String bBirthday = "" + (today - birthdayOfSubject - db);
    	String aIsSubject = "(" + (today - birthdayOfSubject - da) + " = 0)";
    	String bIsSubject = "(" + (today - birthdayOfSubject - db) + " = 0)";

    	condition = condition.replaceAll("(?i:a\\s*\\.\\s*\\$distance)", Matcher.quoteReplacement(aBirthday));
		condition = condition.replaceAll("(?i:b\\s*\\.\\s*\\$distance)", Matcher.quoteReplacement(bBirthday));
    	condition = condition.replaceAll("(?i:a\\s*\\.\\s*\\$is_subject)", Matcher.quoteReplacement(aIsSubject));
    	condition = condition.replaceAll("(?i:b\\s*\\.\\s*\\$is_subject)", Matcher.quoteReplacement(bIsSubject));

    	condition = condition.replaceAll("(?i:$in_delete_mode)", Matcher.quoteReplacement(inDeleteMode? "(1=1)" : "(1=0)"));
    	
    	return condition;
    }
   
    /**
     * Reads a table-list from CSV-file.
     * 
     * @param dataModel to get tables from
     * @param tableFile the file containing the list
     * @return set of tables, empty list if file contains no tables
     */
    public static Set<Table> readTableList(CsvFile tableFile, DataModel dataModel, Map<String, String> sourceSchemaMapping) {
        Set<Table> tabuTables = new HashSet<Table>();
        
        if (tableFile != null) {
            for (CsvFile.Line line: tableFile.getLines()) {
                String name = mappedSchema(sourceSchemaMapping, line.cells.get(0));
				Table table = dataModel.getTable(name);
                if (table == null) {
                    throw new RuntimeException(line.location + ": unknown table: '" + name + "'");
                }
                tabuTables.add(table);
            }
        }
        return tabuTables;
    }

    /**
	 * Replaces schema of qualified table name according to a schema-map. 
	 * 
	 * @param schemaMapping the mapping
	 * @param tableName the table name
	 * @return table name with replaced schema
	 */
	public static String mappedSchema(Map<String, String> schemaMapping, String tableName) {
		if (schemaMapping == null) {
			return tableName;
		}
		Table t = new Table(tableName, null, false);
		String schema = t.getOriginalSchema("");
    	String mappedSchema = schemaMapping.get(schema);
    	if (mappedSchema != null) {
    		schema = mappedSchema;
    	}
    	if (schema.length() == 0) {
    		return t.getUnqualifiedName();
    	}
		return schema + "." + t.getUnqualifiedName();
	}
   
    /**
     * List of all jailer tables (upper case).
     */
    public static final List<String> JAILER_TABLES;
    static {
    	JAILER_TABLES = new ArrayList<String>();
    	JAILER_TABLES.add(EntityGraph.ENTITY_GRAPH);
    	JAILER_TABLES.add(EntityGraph.ENTITY_SET_ELEMENT);
    	JAILER_TABLES.add(EntityGraph.ENTITY);
    	JAILER_TABLES.add(EntityGraph.DEPENDENCY);
    	JAILER_TABLES.add(SQLDialect.CONFIG_TABLE_);
    	JAILER_TABLES.add(SQLDialect.DUAL_TABLE);
    	JAILER_TABLES.add(SQLDialect.TMP_TABLE_);
    }
    
    /**
     * List of all jailer tables (upper case).
     */
    public static final List<String> JAILER_MH_TABLES;
    static {
    	JAILER_MH_TABLES = new ArrayList<String>();
    	JAILER_MH_TABLES.add(EntityGraph.ENTITY_GRAPH);
    	JAILER_MH_TABLES.add(EntityGraph.ENTITY_SET_ELEMENT);
    	JAILER_MH_TABLES.add(EntityGraph.ENTITY);
    	JAILER_MH_TABLES.add(EntityGraph.DEPENDENCY);
    	JAILER_MH_TABLES.add(SQLDialect.CONFIG_TABLE_);
    }
	
	public static DBMS dbms;
	
    /**
     * Gets type of column from result-set.
     * 
     * @param resultSet result-set
     * @param i column index
     * @param typeCache for caching types
     * @return type according to {@link Types}
     */
	public static int getColumnType(ResultSet resultSet, ResultSetMetaData resultSetMetaData, int i, Map<Integer, Integer> typeCache) throws SQLException {
		Integer type = typeCache.get(i);
		if (type == null) {
			try {
				type = resultSetMetaData.getColumnType(i);
			} catch (Exception e) {
				type = Types.OTHER;
			}
			typeCache.put(i, type);
		}
		return type;
	};
	
    /**
     * Gets type of column from result-set.
     * 
     * @param resultSet result-set
     * @param columnName column name
     * @param typeCache for caching types
     * @return object
     */
	public static int getColumnType(ResultSet resultSet, ResultSetMetaData resultSetMetaData, String columnName, Map<String, Integer> typeCache) throws SQLException {
		Integer type = typeCache.get(columnName);
		if (type == null) {
			try {
				type = Types.OTHER;
				for (int i = resultSetMetaData.getColumnCount(); i > 0; --i) {
					if (columnName.equalsIgnoreCase(resultSetMetaData.getColumnLabel(i))) {
						type = resultSetMetaData.getColumnType(i);
						break;
					}
				}
			} catch (Exception e) {
			}
			typeCache.put(columnName, type);
		}
		return type;
	}
	
	/**
	 * Splits a DML statement into several lines with limited length.
	 * 
	 * @param sql the DML statement
	 * @param maxLength maximum line length
	 * @return DML statement with line breaks
	 */
	public static String splitDMLStatement(String sql, int maxLength) {
		if (sql.length() <= maxLength) {
			return sql;
		}
		StringBuilder sb = new StringBuilder();
		int lastBreak = -1;
		int currentLength = 0;
		boolean inLiteral = false;
		for (int i = 0; i < sql.length(); ++i) {
			char c = sql.charAt(i);
			
			if (currentLength >= maxLength) {
				if (inLiteral && lastBreak <= 0) {
					if (i + 1 < sql.length() && sql.charAt(i + 1) != '\'') {
						sb.append("'||\n'");
						currentLength = 3;
						lastBreak = -1;
					}
				} else if (lastBreak > 0) {
					sb.insert(lastBreak + 1, "\n");
					currentLength = sb.length() - lastBreak - 2;
					lastBreak = -1;
				}
			}
			
			if ((!inLiteral) && (c == ' ' || c == ',')) {
				lastBreak = sb.length();
			} else if (c == '\n') {
				currentLength = 0;
				lastBreak = -1;
			}
			
			++currentLength;
			sb.append(c);
			if (c == '\'') {
				inLiteral = !inLiteral;
			}
		}
		return sb.toString();
	}

	public static final String LETTERS_AND_DIGITS = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789";

	/**
	 * Maps SQL types from {@link java.sql.Types} to clear text types.
	 */
    public final static Map<Integer, String> SQL_TYPE;
    static {
        SQL_TYPE = new HashMap<Integer, String>();
        SQL_TYPE.put(Types.BIGINT, "BIGINT");
        SQL_TYPE.put(Types.BINARY, "BINARY");
        SQL_TYPE.put(Types.BIT, "BIT");
        SQL_TYPE.put(Types.CHAR, "CHAR");
        SQL_TYPE.put(Types.DATE, "DATE");
        SQL_TYPE.put(Types.DECIMAL, "DECIMAL");
        SQL_TYPE.put(Types.DOUBLE, "DOUBLE");
        SQL_TYPE.put(Types.FLOAT, "FLOAT");
        SQL_TYPE.put(Types.INTEGER, "INTEGER");
        SQL_TYPE.put(Types.NUMERIC, "NUMERIC");
        SQL_TYPE.put(Types.TIME, "TIME");
        SQL_TYPE.put(Types.TIMESTAMP, "TIMESTAMP");
        SQL_TYPE.put(Types.TINYINT, "TINYINT");
        SQL_TYPE.put(Types.VARCHAR, "VARCHAR");
        SQL_TYPE.put(Types.SMALLINT, "SMALLINT");
        SQL_TYPE.put(Types.CLOB, "CLOB");
        SQL_TYPE.put(Types.BLOB, "BLOB");
    }
    
}
