package workerthreads.changedates;

import gui.MainFrameController;

import java.awt.Color;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

import javax.swing.SwingWorker;

import logging.Log;
import logging.LogLevel;
import data.DateChangeJob;
import data.Schuljahr;
import data.Settings;
import database.DatabaseAccess;

/**
 * change dates in ASV-database
 * 
 * @author Martin Pabst, 2009
 * 
 */
public class ChangeDatesWorker extends SwingWorker<Void, String> {

	/**
	 * When shifting teachers in time we have to calculate new control-digits
	 * for their PKZ. We use this helper-table to accomplish this.
	 * 
	 */
	private static int[][] pkzHelper = { { 0, 1, 2, 6, 3, 7, 4, 8, 5, 9 },
			{ 0, 1, 9, 7, 5, 3, 8, 6, 4, 2 }, { 0, 1, 2, 4, 6, 8, 3, 5, 7, 9 },
			{ 0, 1, 9, 8, 7, 6, 5, 4, 3, 2 }, { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 },
			{ 0, 1, 9, 5, 8, 4, 7, 3, 6, 2 }, { 0, 1, 2, 6, 3, 7, 4, 8, 5, 9 },
			{ 0, 1, 9, 7, 5, 3, 8, 6, 4, 2 } };

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

	/**
	 * List of Schuljahre in database
	 */
	private ArrayList<Schuljahr> schuljahre = new ArrayList<Schuljahr>();

	/**
	 * List of SchuljahrChangeInfo-objects for batch-run
	 */
	private ArrayList<SchuljahrChangeInfo> schuljahrChangeInfoList = new ArrayList<SchuljahrChangeInfo>();

	/**
	 * List of DateInfo objects. A DateInfo stores a date together with its id
	 * in current entity.
	 * 
	 * Changing dates is a two-step process:
	 * 
	 * 1. Read all dates in entity and store them (together with their database
	 * id) in dateInfos 2. Change dates
	 * 
	 * Doing the Job in two steps enables us to display progress information in
	 * progressbar as we know number of dates just after step 1
	 * 
	 */
	private ArrayList<DateInfo> dateInfos = new ArrayList<DateInfo>();

	/**
	 * if globalDelta != 0 then globalDelta is used instead of
	 * DateChangeJob.delta
	 */
	private int globalDelta = 0;

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

	}

	/**
	 * perform job
	 */
	@Override
	protected Void doInBackground() throws Exception {

		controller.setButtonsEnabled(false);
		controller.setWaitCursor(true);
		controller.enableProgressbar(true);
		controller.setProgressBarMaximum(-1);

		doInForeground();

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

		return null;
	}

	public void doInForeground() {

		long timeStart = System.currentTimeMillis();
		try {
			DatabaseAccess.getActiveDatabase(false).openConnection();

			getSchuljahre();

			int i = 1;

			for (DateChangeJob dcj : Settings.getDateChangeJobs()) {

				if (!dcj.isOnlyCorrectCalendar()) {

					try {
						Log.out("Job " + i + " von "
								+ Settings.getDateChangeJobs().size() + ": ",
								LogLevel.useful);
						changeDates(dcj);
					} catch (Exception e) {
						Log.outl(
								"ChangeDatesWorker.changeDates(): "
										+ e.toString(), LogLevel.error);
						e.printStackTrace();
						StringWriter w = new StringWriter();
						// e.printStackTrace();
						Log.printStackTrace(e);
					}
				}
				i++;
			}

			if (Settings.getDateChangeJobs().size() > 0) {
				correctCalendar(Settings.getDateChangeJobs().get(0));
			}

		} catch (Exception ex) {
			Log.outlColor("Fehler: " + ex.toString(), LogLevel.error, Color.red);
			Log.printStackTrace(ex);
		} finally {
			DatabaseAccess.getActiveDatabase(false).closeConnection();
		}

		Log.outl("", LogLevel.useful);
		Log.outlColor("Dauer: "
				+ (System.currentTimeMillis() - timeStart + " ms."),
				LogLevel.useful, Color.blue);
	}

	/**
	 * 
	 * perform one dateChangeJob
	 * 
	 * @param dateChangeJob
	 * 
	 * @throws SQLException
	 */
	private void changeDates(DateChangeJob dateChangeJob) throws SQLException {

		long timeStart = System.currentTimeMillis();

		int d = getDelta(dateChangeJob);

		Log.outColor("aendere Spalte " + dateChangeJob.getAttribute()
				+ " in Entitaet " + dateChangeJob.getEntity() + ": Addiere "
				+ d + " Jahr" + (d * d > 1 ? "e" : "") + ". ", LogLevel.useful,
				Color.blue);

		if (columnExists(dateChangeJob)) {

			if (dateChangeJob.getAttribute().toLowerCase()
					.compareTo("schuljahr_id") == 0) {
				changeSchuljahrNeu(dateChangeJob);
			} else {
				readDateInfo(dateChangeJob);
				changeDateInfo(dateChangeJob);
			}

		} else {
			Log.outColor(" Spalte existiert nicht.", LogLevel.useful,
					new Color(150, 0, 0));
		}

		Log.outlColor(" in " + (System.currentTimeMillis() - timeStart)
				+ " ms.", LogLevel.useful, new Color(0, 125, 0));
		Log.outl("", LogLevel.useful);

	}

	private boolean columnExists(DateChangeJob dateChangeJob) {

		boolean exists = true;

		String query = "SELECT " + dateChangeJob.getAttribute() + " FROM "
				+ dateChangeJob.getEntity();

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

		Statement statement;
		try {
			statement = connection.createStatement();

			try {

				ResultSet resultSet = statement.executeQuery(query);

				while (resultSet.next()) {

				}

				resultSet.close();

			} catch (SQLException ex) {
				exists = false;
			}

			statement.close();
		} catch (SQLException e) {
			exists = false;
		}

		return exists;
	}

	/**
	 * This class is used if attribute which has to be changed holds reference
	 * to svp_wl_schuljahr. It collects info for all necessary database
	 * modifications (that is: id of line to alter, new reference to
	 * svp_wl_schuljahr) in list schuljahrChangeInfoList.
	 * 
	 * @param dateChangeJob
	 * @throws SQLException
	 */
	private void getSchuljahrChangeInfo(DateChangeJob dateChangeJob)
			throws SQLException {

		schuljahrChangeInfoList.clear();

		int delta = getDelta(dateChangeJob);

		if (delta > 0) {

			for (int i = schuljahre.size() - 1; i - delta >= 0; i--) {

				int oldSchoolyear = i - delta;
				int newSchoolyear = i;

				getDateChangeInfoFromDataBase(dateChangeJob, oldSchoolyear,
						newSchoolyear);

			}

		} else {
			for (int i = 0; i - delta < schuljahre.size(); i++) {

				int oldSchoolyear = i - delta;
				int newSchoolyear = i;

				getDateChangeInfoFromDataBase(dateChangeJob, oldSchoolyear,
						newSchoolyear);

			}
		}

	}

	/**
	 * store information of all necessary database-updates to transfer lines
	 * with given oldSchoolYear in given entity (via dateChangeJob) to given
	 * newSchoolYear. stores informatoin in schuljahrChangeInfoList
	 * 
	 * @param dateChangeJob
	 * @param oldSchoolYear
	 * @param newSchoolyear
	 * @throws SQLException
	 */
	private void getDateChangeInfoFromDataBase(DateChangeJob dateChangeJob,
			int oldSchoolYear, int newSchoolyear) throws SQLException {

		String query = "SELECT ID FROM " + dateChangeJob.getEntity()
				+ " WHERE SCHULJAHR_ID = '"
				+ schuljahre.get(oldSchoolYear).getId() + "'";

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

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);

		while (resultSet.next()) {

			String id = resultSet.getString(1);

			schuljahrChangeInfoList.add(new SchuljahrChangeInfo(id, schuljahre
					.get(newSchoolyear).getId()));

		}

		resultSet.close();
		statement.close();
	}

	/**
	 * MaPa, 01.09.2010:
	 * 
	 * Da die Einträge von svp_schule_schuljahr auf das neue Schuljahr umgebogen
	 * werden, passen die Datumseinträge in svp_kalendertag nicht mehr zu den
	 * Referenzen svp_kalendertag.schule_schuljahr_id. Daher muss hier
	 * nachkorrigiert werden.
	 * 
	 * @param dateChangeJob
	 * @throws SQLException
	 */
	private void correctCalendar(DateChangeJob dateChangeJob)
			throws SQLException {

		int delta = getDelta(dateChangeJob);

		Log.outColor(
				"Korrigiere Referenzen svp_kalendertag.schule_schuljahr_id... ",
				LogLevel.useful, new Color(0, 100, 0));

		String query = "select ssj.ID, ssj.SCHULE_STAMM_ID, ssj.SCHULJAHR_ID,"
				+ "(select schulnummer from svp_schule_stamm sst where sst.id = ssj.schule_stamm_id)"
				+ " from SVP_SCHULE_SCHULJAHR ssj";

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

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);

		SchuleSchuljahrList ssjlist = new SchuleSchuljahrList();

		while (resultSet.next()) {

			String id = resultSet.getString(1);
			String schule_stamm_id = resultSet.getString(2);
			String schuljahr_id = resultSet.getString(3);
			String schulnummer = resultSet.getString(4);

			SchuleSchuljahrInfo ssji = new SchuleSchuljahrInfo(id,
					schuljahr_id, schule_stamm_id, schulnummer);
			ssjlist.add(ssji);

		}

		resultSet.close();
		statement.close();

		/**
		 * determine correct values
		 */

		ssjlist.determineCorrectValues(delta);
		Collections.sort(ssjlist);

		/**
		 * correct references in svp_kalendertag
		 */

		int i = 0;
		int increment = 1;
		int endIndex = ssjlist.size();

		if (delta < 0) {
			i = endIndex - 1;
			increment = -1;
			endIndex = -1;
		}

		Log.setProgressBarMaximum(ssjlist.size());
		int n = 0;
		try {
			do {

				SchuleSchuljahrInfo ssji = ssjlist.get(i);

				Log.setProgressBarValue(
						n,
						"Schule " + ssji.getSchulnummer() + ", Schuljahr "
								+ ssji.getSchuljahr() + "/"
								+ (ssji.getSchuljahr() + 1) % 100);

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

				statement = connection.createStatement();

				if (ssji.getCorrectValue() != null) {
					query = "UPDATE SVP_KALENDERTAG SET "
							+ "SCHULE_SCHULJAHR_ID = '"
							+ ssji.getCorrectValue().getId() + "' "
							+ "WHERE SCHULE_SCHULJAHR_ID = '" + ssji.getId()
							+ "'";

					statement.executeUpdate(query);
				} else {
					String query1 = "DELETE FROM SVP_TERMIN WHERE "
							+ "KALENDERTAG_ID IN ("
							+ "SELECT ID FROM SVP_KALENDERTAG WHERE SCHULE_SCHULJAHR_ID = '"
							+ ssji.getId() + "')";

					String query2 = "DELETE FROM SVP_KALENDERTAG WHERE SCHULE_SCHULJAHR_ID = '"
							+ ssji.getId() + "'";

					statement.executeUpdate(query1);
					statement.executeUpdate(query2);

				}

				statement.close();

				i += increment;
				n++;

			} while (i != endIndex);

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

		Log.outlColor("fertig.", LogLevel.useful, new Color(0, 100, 0));

	}

	/**
	 * change Schuljahr for given dateChangeJob. call this method only if
	 * attribute in given dateChangeJob holds a reference to svp_wl_schuljahr
	 * 
	 * @param dateChangeJob
	 * @throws SQLException
	 */
	private void changeSchuljahrNeu(DateChangeJob dateChangeJob)
			throws SQLException {

		/**
		 * delete instances which reference oldest/newest scholyear from old
		 * svp_wl_schuljahr
		 */

		int delta = getDelta(dateChangeJob);

		String deleteQuery = "";

		Statement statement = null;
		Connection connection = null;

		for (int i = 0; i < Math.abs(delta); i++) {

			if (delta > 0) {

				deleteQuery = "DELETE FROM " + dateChangeJob.getEntity()
						+ " WHERE SCHULJAHR_ID = " + "'"
						+ schuljahre.get(schuljahre.size() - 1 - i).getId()
						+ "'";

			} else {

				deleteQuery = "DELETE FROM " + dateChangeJob.getEntity()
						+ " WHERE SCHULJAHR_ID = " + "'"
						+ schuljahre.get(0 + i).getId() + "'";

			}

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

			statement = connection.createStatement();
			statement.executeUpdate(deleteQuery);
			statement.close();
		}

		/**
		 * Change link to svp_wl_schuljahr
		 */

		getSchuljahrChangeInfo(dateChangeJob);
		Log.setProgressBarMaximum(schuljahrChangeInfoList.size());

		for (int i = 0; i < schuljahrChangeInfoList.size(); i++) {

			SchuljahrChangeInfo info = schuljahrChangeInfoList.get(i);

			String query = "UPDATE " + dateChangeJob.getEntity()
					+ " SET SCHULJAHR_ID = '" + info.getNewSchoolYear()
					+ "' WHERE ID = '" + info.getId() + "'";

			statement = connection.createStatement();
			statement.executeUpdate(query);

			statement.close();
			if (i % 10 == 0) {
				Log.setProgressBarValue(i, "aendere Datensatz " + i + " von "
						+ schuljahrChangeInfoList.size() + ".");
			}

		}
		Log.setProgressBarMaximum(0);

		Log.outColor("" + schuljahrChangeInfoList.size()
				+ " Datensaetze geaendert", LogLevel.useful, new Color(0, 125,
				0));

	}

	/**
	 * get count of datasets to update for given dateChangeJob. only call this
	 * method if attribute to change holds date-values
	 * 
	 * @param dateChangeJob
	 * @return
	 * @throws SQLException
	 */
	private int getCount(DateChangeJob dateChangeJob) throws SQLException {

		String query = "SELECT COUNT(*)" + " FROM " + dateChangeJob.getEntity();

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

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);

		dateInfos.clear();

		resultSet.next();
		String s = resultSet.getString(1);

		int ret = Integer.parseInt(s);

		resultSet.close();
		statement.close();

		return ret;

	}

	/**
	 * collect information about all update-statements needed to do given
	 * dateChangeJob. only cass this if attribute to change holds date-values
	 * 
	 * @param dateChangeJob
	 * @throws SQLException
	 */
	private void readDateInfo(DateChangeJob dateChangeJob) throws SQLException {

		int n = getCount(dateChangeJob);

		// Log.setProgressBarMaximum(n);

		String query = "SELECT ID," + dateChangeJob.getAttribute() + " FROM "
				+ dateChangeJob.getEntity();

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

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);

		dateInfos.clear();

		int i = 0;

		while (resultSet.next()) {

			String id = resultSet.getString(1);
			String date = resultSet.getString(2);

			dateInfos.add(new DateInfo(date, id));

			i++;

			// if (dateInfos.size() % 10 == 0) {
			// Log.setProgressBarValue(i, "" + dateInfos.size()
			// + " Datensaetze gelesen.");
			// }

		}

		// Log.setProgressBarMaximum(0);

		Log.out("" + dateInfos.size() + " Datensaetze gelesen... ",
				LogLevel.useful);

		resultSet.close();
		statement.close();

	}

	/**
	 * get all Schuljahre stored in svp_wl_schuljahr and store them in list
	 * schuljahre
	 * 
	 * @throws SQLException
	 */
	private void getSchuljahre() throws SQLException {

		Log.outColor("Hole Schuljahre: ", LogLevel.useful, Color.blue);

		String query = "SELECT ID, KURZFORM FROM SVP_WL_SCHULJAHR";

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

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);

		schuljahre.clear();

		while (resultSet.next()) {

			String id = resultSet.getString(1);
			String schuljahr = resultSet.getString(2);

			if (schuljahr.length() == 7) {
				schuljahr = schuljahr.substring(0, 4);
				schuljahre.add(new Schuljahr(id, schuljahr));
				Log.out("" + schuljahr + ", ", LogLevel.useful);
			}

		}
		Log.outl("\n", LogLevel.useful);

		Collections.sort(schuljahre);

		resultSet.close();
		statement.close();

	}

	/**
	 * Perform all Updates stored in list dateInfos.
	 * 
	 * @param dateChangeJob
	 * @throws SQLException
	 */
	private void changeDateInfo(DateChangeJob dateChangeJob)
			throws SQLException {

		int n = dateInfos.size();

		Log.setProgressBarMaximum(n);

		String sDelta = "" + getDelta(dateChangeJob);

		if (sDelta.startsWith("+")) {
			sDelta = sDelta.substring(1);
		}

		int delta = Integer.parseInt(sDelta);

		int i = 1;
		int changed = 0;

		for (DateInfo di : dateInfos) {

			String date = di.getDate();

			if ((date != null)) {

				String newDate = date;

				String quotation = "'";

				if (dateChangeJob.isIntegerAttribute()) {

					quotation = "";

					if (!date.isEmpty()) {
						try {
							int newDateInt = Integer.parseInt(date);
							newDate = "" + (newDateInt + delta);
						} catch (NumberFormatException ex) {

						}
					}

				} else {
					if (dateChangeJob.getAttribute().compareToIgnoreCase("pkz") == 0
							|| dateChangeJob.getAttribute()
									.compareToIgnoreCase("pkz_wunsch") == 0) {

						newDate = changePKZ(delta, date);

						Log.out("PKZ " + date + " -> " + newDate,
								LogLevel.trace);
					} else {
						newDate = changeDateString(delta, date);
					}

				}

				if (date.compareToIgnoreCase(newDate) != 0) {

					String query = "UPDATE " + dateChangeJob.getEntity()
							+ " SET " + dateChangeJob.getAttribute() + " = "
							+ quotation + newDate + quotation + " WHERE ID = '"
							+ di.getId() + "'";

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

					Statement statement = connection.createStatement();
					statement.executeUpdate(query);

					statement.close();
					changed++;
				}

			}

			Log.setProgressBarValue(i, "" + i + " Datensaetze geaendert.");

			i++;
		}

		Log.setProgressBarMaximum(0);

		Log.outColor("" + changed + " Datensaetze geaendert", LogLevel.useful,
				new Color(0, 125, 0));

	}

	/**
	 * increases year of PKZ (form "510908741") by given delta. control digits
	 * (51 and 1) are altered so that new PKZ is valid.
	 * 
	 * @param delta
	 * @param oldPKZ
	 * @return new PKZ as String in format "510908741"
	 */
	public static String changePKZ(int delta, String oldPKZ) {
		String newDate = oldPKZ;
		if (oldPKZ.length() == 9 && oldPKZ.charAt(0) != ' ') {

			String lfdNr = oldPKZ.substring(0, 2);
			String day = oldPKZ.substring(2, 4);
			String month = oldPKZ.substring(4, 6);
			String year = oldPKZ.substring(6, 8);

			/**
			 * Zum Test von getPruefziffer:
			 */
			int pz = getPruefziffer(oldPKZ.substring(0, 8));
			if (pz != Integer.parseInt(oldPKZ.substring(8, 9))) {
				Log.outl("Falsche Prüfziffer: " + oldPKZ + "; " + pz,
						LogLevel.error);
			}

			int iYear = Integer.parseInt(year);

			/**
			 * Beware of February 29th!
			 */
			if (month.compareTo("02") == 0 && day.compareTo("29") == 0) {
				day = "28";
			}

			if (month.compareTo("02") == 0 && day.compareTo("28") == 0
					&& (iYear + delta) % 4 == 0) {
				day = "29";
			}

			newDate = lfdNr + day + month + (iYear + delta);

			newDate += getPruefziffer(newDate);

		}
		return newDate;
	}

	/**
	 * add delta in years to given date as String
	 * 
	 * @param delta
	 * @param date
	 * @return
	 */
	public String changeDateString(int delta, String date) {

		String newDate = date;

		if (date.length() >= 4) {
			String year = date.substring(0, 4);
			int iyear = Integer.parseInt(year);
			String newYear = "" + (iyear + delta);

			newDate = newYear;

			if (date.length() > 4) {

				String month = date.substring(5, 7);
				String day = date.substring(8, 10);

				/**
				 * Beware of February 29th!
				 */
				if (month.compareTo("02") == 0 && day.compareTo("29") == 0) {
					day = "28";
				}

				if (month.compareTo("02") == 0 && day.compareTo("28") == 0
						&& (iyear + delta) % 4 == 0) {
					day = "29";
				}

				newDate += "-" + month + "-" + day;

			}

			String tail = date.substring(newDate.length());
			newDate += tail;
		}

		return newDate;
	}

	/**
	 * get correction digit for given pkz-start
	 * 
	 * @param pKZAnfang
	 *            start of pkz, form "51090874"
	 * @return correction digit
	 */
	public static int getPruefziffer(String pKZAnfang) {
		int summe = 0;
		int x2 = 0, x3 = 0;

		for (int x = 1; x <= 8; x++) {
			x2 = Integer.parseInt(pKZAnfang.substring(x - 1, x));
			summe += pkzHelper[x - 1][x2];
		}

		x3 = ((summe / 10) + 1) * 10 - summe;
		if (x3 == 10)
			x3 = 0;

		return x3;
	}

	private int getDelta(DateChangeJob dateChangeJob) {
		return globalDelta != 0 ? globalDelta : dateChangeJob.getDeltaInt();
	}

}
