package de.ugoe.cs.swe.memos.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import de.ugoe.cs.swe.memos.RelevanceRanker;
import de.ugoe.cs.swe.memos.SettingsManager;
import de.ugoe.cs.swe.memos.Utils;
import de.ugoe.cs.swe.memos.datamodel.Category;
import de.ugoe.cs.swe.memos.datamodel.Memo;

public class DBSearcher {

	private String searchString;

	public DBSearcher(String searchString) {
		this.searchString = searchString;
	}

	private Vector<String> searchItems = new Vector<String>();

	private String parseSearch() {

		String formattedInput = this.formatInput(searchString);

		if (!isValidSearch(formattedInput)) {
			return null;
		}

		String[] inputParts = formattedInput.split("\\s+");

		ArrayList<String> searchParts = new ArrayList<String>();

		boolean lastWasWordOrBracketsClose = false;
		for (int i = 0; i < inputParts.length; i++) {
			boolean currentIsWord = (!inputParts[i].equals("/")
					&& !inputParts[i].equals("(") && !inputParts[i].equals(")"));

			if (currentIsWord) {
				this.searchItems.add(inputParts[i]);
			}

			if (lastWasWordOrBracketsClose
					&& (currentIsWord || inputParts[i].equals("(")))
				searchParts.add("AND");
			searchParts.add(inputParts[i].replace("/", "OR"));
			lastWasWordOrBracketsClose = currentIsWord
					|| inputParts[i].equals(")");
		}

		for (int i = 0; i < searchParts.size(); i++) {
			searchParts.set(i, createSQLWherePart(searchParts.get(i), i == 0));
		}

		String query = "SELECT DISTINCT  m.id, m.title, m.content, m.timestamp, m.category,"
				+ " m.lockedby, m.author, m.draft, m.lockedat, c.name, c.Parent FROM"
				+ " Memos m left outer join Categories c on c.id=m.category"
				+ " WHERE (";
		for (int i = 0; i < searchParts.size(); i++) {
			query += searchParts.get(i);
		}

		query += ");";
		System.out.println(query);
		return query;
	}

	private String[] dbFields = new String[] { "m.title", "m.content",
			"DATE_FORMAT(m.timestamp,'%d.%m.%Y %Y-%m-%d %a %b %H:%i %M %W')",
			"m.author" };

	private String createSQLWherePart(String inputWord, boolean first) {
		boolean inputIsWord = (!inputWord.equals("AND")
				&& !inputWord.equals("OR") && !inputWord.equals("(") && !inputWord
				.equals(")"));
		boolean HSQL = SettingsManager.getInstance().getDBType() == SettingsManager.DatabaseType.HSQL;
		if (!inputIsWord)
			return " " + inputWord + " ";
		String sqlWherePart = "(";

		boolean firstDBField = true;
		for (String dbField : dbFields) {
			if (HSQL && dbField.startsWith("DATE_FORMAT"))
				continue;
			if (!firstDBField)
				sqlWherePart += "OR ";
			firstDBField = false;
			sqlWherePart += dbField + " LIKE '%" + inputWord + "%' ";
		}

		sqlWherePart += "OR ('" + inputWord
				+ "' IN (SELECT t.Tag FROM Tagging t WHERE t.memo=m.id))";
		sqlWherePart += "OR ('" + inputWord
				+ "' IN (SELECT f.Name FROM Files f WHERE f.memo=m.id))";
		sqlWherePart += ") ";
		return sqlWherePart;
	}

	enum SearchPartType {
		none, start, word, bracketsOpen, bracketsClose, operator
	}

	private boolean isValidSearch(String input) {
		// TODO: verify input
		// String verifyExpression = "";
		int openBracketCount = 0;
		SearchPartType lastPart = SearchPartType.start;
		SearchPartType currentPart = SearchPartType.none;
		String[] inputParts = input.split("\\s+");
		for (int i = 0; i < inputParts.length; i++) {
			boolean valid = false;
			String part = inputParts[i];
			if (part.equals("/"))
				currentPart = SearchPartType.operator;
			else if (part.equals("(")) {
				currentPart = SearchPartType.bracketsOpen;
				openBracketCount++;
			}
			else if (part.equals(")")) {
				currentPart = SearchPartType.bracketsClose;
				openBracketCount--;
			}
			else
				currentPart = SearchPartType.word;
			

			switch (lastPart) {
			case start:
				valid = (currentPart == SearchPartType.word || currentPart == SearchPartType.bracketsOpen);
				break;
			case word:
				valid = true;
				break;
			case bracketsOpen:
				valid = (currentPart == SearchPartType.word);
				break;
			case bracketsClose:
				valid = true;
				break;
			case operator:
				valid = (currentPart != SearchPartType.operator);
				break;
			}

			if (!valid)
				return false;
			
			lastPart = currentPart;
		}
		return (openBracketCount == 0 && (currentPart == SearchPartType.bracketsClose || currentPart == SearchPartType.word));
	}

	private String formatInput(String input) {
		return input.replace("/", " / ").replace("(", " ( ")
				.replace(")", " ) ").replace("*", "%").replace("?", "_");
	}

	public Vector<Memo> execute() {
		parseSearch();
		Vector<Memo> foundMemos = new Vector<Memo>();
		Connection connection = Connector.getConnection();
		Statement statement;

		String sqlQ = this.parseSearch();
		if (sqlQ == null)
			return null;

		try {
			statement = connection.createStatement();

			ResultSet results = statement.executeQuery(sqlQ);

			while (results.next()) {
				Memo newMemo = new Memo(results.getString(2), results
						.getTimestamp(4), results.getInt(8) == 1);
				newMemo.setAuthor(results.getString(7));
				newMemo.setCategoryID(results.getLong(5));
				newMemo.setContent(results.getString(3));
				newMemo.setLock(results.getString(6));
				newMemo.setiD(results.getLong(1));
				foundMemos.add(newMemo);

				String catName = results.getString(10);
				if (catName != null) {
					Category c = new Category(catName);
					c.setParentID(results.getLong(11));
					c.setiD(newMemo.getCategoryID());
					newMemo.setCategory(c);
				}
			}
			statement.close();

		} catch (SQLException e) {
			Utils.showError("Fehler beim Suchen.");
			return new Vector<Memo>();
		}

		for (Memo m : foundMemos) {
			m.loadTags();
		}

		// If the search string was empty, there is no need to try to sort them
		// by relevance
		if (searchString.matches("\\s*"))
			return foundMemos;
		else
			return RelevanceRanker.rankMemos(foundMemos, this.searchItems);
	}
}
