package it.unibz.krdb.obda.parser;

import it.unibz.krdb.sql.DBMetadata;
import it.unibz.krdb.sql.ViewDefinition;
import it.unibz.krdb.sql.api.Attribute;
import it.unibz.krdb.sql.api.QueryTree;
import it.unibz.krdb.sql.api.Relation;
import it.unibz.krdb.sql.api.TablePrimary;

import org.antlr.runtime.ANTLRStringStream;
import org.antlr.runtime.CommonTokenStream;
import org.antlr.runtime.RecognitionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SQLQueryTranslator {

	private DBMetadata dbMetaData;
	
	private static int id_counter;
	
	private static Logger log = LoggerFactory.getLogger(SQLQueryTranslator.class);
	
	public SQLQueryTranslator(DBMetadata dbMetaData) {
		this.dbMetaData = dbMetaData;
		id_counter = 0;		
	}

	public QueryTree contructQueryTree(String query) {
		ANTLRStringStream inputStream = new ANTLRStringStream(query);
		SQL99Lexer lexer = new SQL99Lexer(inputStream);
		CommonTokenStream tokenStream = new CommonTokenStream(lexer);
		SQL99Parser parser = new SQL99Parser(tokenStream);

		QueryTree queryTree = null;
		try {
			queryTree = parser.parse();
		} catch (RecognitionException e) {
			// Does nothing
		}
		
		if (parser.getNumberOfSyntaxErrors() != 0) {
			log.warn("The following query couldn't be parsed. This means Quest will need to use nested subqueries (views) to use this mappings. This is not good for SQL performance, specially in MySQL. Try to simplify your query to allow Quest to parse it. If you think this query is already simple and should be parsed by Quest, please contact the authors. \nQuery: '{}'", query);
			queryTree = createView(query);
		}		
		return queryTree;
	}
	
	private QueryTree createView(String query) {
		String viewName = String.format("view_%s", id_counter++);
		
		ViewDefinition vd = createViewDefintion(viewName, query);
		dbMetaData.add(vd);
		
		QueryTree vt = createViewTree(viewName, query);
		return vt;
	}
		
	private ViewDefinition createViewDefintion(String viewName, String query) {
		int start = 6; // the keyword 'select'
		int end = query.toLowerCase().indexOf("from");		
		
		if (end == -1) {
			throw new RuntimeException("Error parsing SQL query: Couldn't find FROM clause");
		}
		String projection = query.substring(start, end).trim();
		String[] columns = projection.split(",");
		
		ViewDefinition viewDefinition = new ViewDefinition();
		viewDefinition.setName(viewName);
		viewDefinition.copy(query);		
		for (int i = 0; i < columns.length; i++) {
			String columnName = columns[i].trim();
			if (columnName.contains(" as ")) {
				columnName = columnName.split(" as ")[1].trim();
			}			
			viewDefinition.setAttribute(i+1, new Attribute(columnName)); // the attribute index always start at 1
		}
		return viewDefinition;
	}
	
	private QueryTree createViewTree(String viewName, String query) {		
		TablePrimary view = new TablePrimary(viewName);
		QueryTree queryTree = new QueryTree(new Relation(view));

		return queryTree;
	}
}

@@ -2589,16 +2589,865 @@
 rim();%0D%0A
+%09%09%09%0D%0A%09%09%09/*%0D%0A%09%09%09 * Remove any identifier quotes%0D%0A%09%09%09 * Example:%0D%0A%09%09%09 * %09%09INPUT: %22table%22.%22column%22%0D%0A%09%09%09 * %09%09OUTPUT: table.column%0D%0A%09%09%09 */%0D%0A%09%09%09if (columnName.contains(%22%5C%22%22)) %7B%0D%0A%09%09%09%09columnName = columnName.replaceAll(%22%5C%22%22, %22%22);%0D%0A%09%09%09%7D else if (columnName.contains(%22%60%22)) %7B%0D%0A%09%09%09%09columnName = columnName.replaceAll(%22%60%22, %22%22);%0D%0A%09%09%09%7D else if (columnName.contains(%22%5B%22) && columnName.contains(%22%5D%22)) %7B%0D%0A%09%09%09%09columnName = columnName.replaceAll(%22%5B%22, %22%22).replaceAll(%22%5D%22, %22%22);%0D%0A%09%09%09%7D%0D%0A%0D%0A%09%09%09/*%0D%0A%09%09%09 * Get only the short name if the column name uses qualified name.%0D%0A%09%09%09 * Example:%0D%0A%09%09%09 * %09%09INPUT: table.column%0D%0A%09%09%09 * %09%09OUTPUT: column%0D%0A%09%09%09 */%0D%0A%09%09%09if (columnName.contains(%22.%22)) %7B%0D%0A%09%09%09%09columnName = columnName.substring(columnName.lastIndexOf(%22.%22)+1, columnName.length()); // get only the name%0D%0A%09%09%09%7D%0D%0A%09%09%09%0D%0A%09%09%09/*%0D%0A%09%09%09 * Take the alias name if the column name has it.%0D%0A%09%09%09 */%0D%0A
 %09%09%09if (c
@@ -3471,24 +3471,40 @@
 s(%22 as %22)) %7B
+ // has an alias
 %0D%0A%09%09%09%09column

Remove identifier quotes in the input string so that the parser can parse properly.