package workerthreads;

import gui.MainFrameController;

import java.awt.Color;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.SwingWorker;

import logging.Log;
import logging.LogLevel;
import data.Script;
import data.Settings;
import database.DatabaseAccess;

/**
 * batch execute of given SQL scripts
 * 
 * @author Martin Pabst, 2009
 * 
 */

public class ExecuteScriptsWorker extends SwingWorker<Void, String> {

	/**
	 * store controller object
	 */
	private MainFrameController controller;

	/**
	 * filesystem path to database
	 */
	private String path;

	/**
	 * Remember created views so they can be dropped automatically if script
	 * fails
	 */
	private ArrayList<String> views = new ArrayList<String>();

	/**
	 * constructor, only stores controller
	 * 
	 * @param controller
	 */
	public ExecuteScriptsWorker(MainFrameController controller) {
		super();
		this.controller = controller;

	}

	/**
	 * batch execute scripts
	 */
	@Override
	protected Void doInBackground() throws Exception {
		controller.setButtonsEnabled(false);
		controller.setWaitCursor(true);
		controller.enableProgressbar(true);
		controller.setProgressBarMaximum(-1);

		try {
			DatabaseAccess.getActiveDatabase(false).openConnection();

			for (Script script : Settings.getScripts()) {
				executeScript(script);
			}

		} catch (Exception e) {
			Log.outl("ExecuteScriptsWorker: " + e.toString(), LogLevel.error);
		} finally {
			DatabaseAccess.getActiveDatabase(false).closeConnection();
		}

		controller.setWaitCursor(false);
		controller.setButtonsEnabled(true);
		controller.enableProgressbar(false);

		return null;
	}

	/**
	 * execute one SQL-Script
	 * 
	 * @param script
	 * @throws SQLException
	 */
	private void executeScript(Script script) throws SQLException {

		Log.out("Führe Script " + script.getIdentifier() + " aus: ",
				LogLevel.useful);

		ArrayList<String> scripts = script.getSingleStatements();

		Log.setProgressBarMaximum(scripts.size());

		int i = 1;

		int linesChanged = 0;

		long time = System.currentTimeMillis();

		views.clear();

		for (String query : scripts) {

			Log.setProgressBarValue(i,
					"Statement " + i + " von " + scripts.size());

			query = deleteEndSpace(query);
			query = deleteBeginSpace(query);

			if (query.endsWith(";")) {
				query = query.substring(0, query.length() - 1);
			}

			Connection connection = DatabaseAccess.getActiveDatabase(false)
					.getConnection();

			Statement statement = connection.createStatement();

			try {
				int returncode = statement.executeUpdate(query);

				linesChanged += returncode;

				statement.close();

				rememberview(query);
			} catch (SQLException ex) {
				Log.outl("SQLException: " + ex.toString(), LogLevel.error);
			}
			i++;
		}

		if (views.size() > 0) {
			Log.outl("Ungeschlossene views: ", LogLevel.error);
			for (String identifier : views) {
				Log.outl("Schließe " + identifier, LogLevel.useful);

				Connection connection = DatabaseAccess.getActiveDatabase(false)
						.getConnection();

				Statement statement = connection.createStatement();

				try {

					statement.executeUpdate("drop view " + identifier);

					statement.close();

				} catch (SQLException ex) {
					Log.outl("SQLException: " + ex.toString(), LogLevel.error);
				}

			}
		}

		Log.outlColor("" + linesChanged + " Zeilen geändert. " + "Dauer: "
				+ (System.currentTimeMillis() - time) + " ms.",
				LogLevel.useful, Color.blue);

	}

	private void rememberview(String query) {
		String query1 = query.toLowerCase();

		if (query1.startsWith("create")) {
			query1 = query1.substring("create".length());
			query1 = deleteBeginSpace(query1);
			if (query1.toLowerCase().startsWith("view")) {
				query1 = query1.substring("view".length());
				query1 = deleteBeginSpace(query1);

				int pos = 0;
				while (pos < query1.length() && !isSpace(query1.charAt(pos))
						&& !(query1.charAt(pos) == ';')) {
					pos++;
				}
				String viewIdentifier = query1.substring(0, pos);

				views.add(viewIdentifier);
				Log.outl("View " + viewIdentifier + " definiert.",
						LogLevel.trace);

			}

		} else {
			if (query1.startsWith("drop")) {
				query1 = query1.substring("drop".length());
				query1 = deleteBeginSpace(query1);
				if (query1.toLowerCase().startsWith("view")) {
					query1 = query1.substring("view".length());
					query1 = deleteBeginSpace(query1);

					int pos = 0;
					while (pos < query1.length()
							&& !isSpace(query1.charAt(pos))
							&& !(query1.charAt(pos) == ';')) {
						pos++;
					}
					String viewIdentifier = query1.substring(0, pos);

					for (int i = 0; i < views.size(); i++) {
						if (views.get(i).compareTo(viewIdentifier) == 0) {
							views.remove(i);
							Log.outl("View " + viewIdentifier + " gelöscht.",
									LogLevel.trace);
							break;
						}
					}
				}
			}
		}

	}

	/**
	 * delete space at end of script
	 * 
	 * @param s
	 * @return
	 */
	private String deleteEndSpace(String s) {
		while (s != null && !s.isEmpty() && isSpace(s.charAt(s.length() - 1))) {
			s = s.substring(0, s.length() - 1);
		}
		return s;
	}

	/**
	 * delete space at begin of script
	 * 
	 * @param s
	 * @return
	 */
	private String deleteBeginSpace(String s) {
		while (s != null & !s.isEmpty() && isSpace(s.charAt(0))) {
			s = s.substring(1);
		}
		return s;
	}

	/**
	 * returns true if given character is a space or cr or lf
	 * 
	 * @param c
	 * @return
	 */
	private boolean isSpace(char c) {
		return c == ' ' || c == '\n' || c == '\t' || c == '\r';
	}

}
