package de.ugoe.cs.swe.memos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Vector;

import de.ugoe.cs.swe.memos.database.Connector;
import de.ugoe.cs.swe.memos.datamodel.Category;
import de.ugoe.cs.swe.memos.datamodel.Memo;
import de.ugoe.cs.swe.memos.xml.XMLInterface;

public class ExportManager {

	final SettingsManager sm = SettingsManager.getInstance();
	private int authorFilter;
	private Category categoryFilter;
	private Timestamp fromFilter, tillFilter;
	private String exportPath;
	private Boolean useDateFilter, useCategoryFilter;
	private static ExportManager instance;

	public static ExportManager getInstance() {
		if (instance == null)
			instance = new ExportManager();
		return instance;
	}

	public void setUseCategoryFilter(Boolean useCategoryFilter) {
		this.useCategoryFilter = useCategoryFilter;
	}

	private ExportManager() {
		// The Constructor
	}

	public void setAuthorFilter(int filter) {
		authorFilter = filter;
	}

	public void setCategoryFilter(Category filter) {
		categoryFilter = filter;
	}

	public void setTimeFilter(Timestamp from, Timestamp till) {
		fromFilter = from;
		tillFilter = till;
	}

	public void setExportPath(String path) {
		exportPath = path;
	}

	public void setUseDateFilter(Boolean value) {
		useDateFilter = value;
	}

	private void addMemosFromChildCategories(Category parent,
			PreparedStatement statement, ResultSet results, Connection con,
			Vector<Memo> exportMemos) {
		// walk through all ChildCats and add the included Memos and calls
		// itself on the Categories
		// TODO: Do not load on NULL!
		if (parent != null) {
			parent.loadChildrenFromDB();
			parent.loadMemosFromDB();
		} else {
			Utils
					.showError("Versuche aus der null-Kategorie zu lesen, quietly dying...");
		}

		try {
			// all my Data
			if (authorFilter == 1) {

				if (useDateFilter) {
					if (useCategoryFilter) {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=? AND m.author=? AND m.timestamp >= ? AND m.timestamp <= ?;");

						statement.setLong(1, parent.getiD());
						statement.setString(2, sm.getUsername());
						statement.setTimestamp(3, fromFilter);
						statement.setTimestamp(4, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {
							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}

						statement.close();

					} else {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.author=? AND m.timestamp >= ? AND m.timestamp <= ?;");
						statement.setString(1, sm.getUsername());
						statement.setTimestamp(2, fromFilter);
						statement.setTimestamp(3, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {
							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(results.getLong(5));
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							// newMemo.setCategory(categoryFilter);
							exportMemos.add(newMemo);
						}
						statement.close();
					}

				} else {
					if (useCategoryFilter) {

						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=? AND m.author=?;");
						statement.setLong(1, parent.getiD());
						statement.setString(2, sm.getUsername());

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}
						statement.close();
					} else {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.author=?;");
						statement.setString(1, sm.getUsername());

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(results.getLong(5));
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							// newMemo.setCategory(categoryFilter);
							exportMemos.add(newMemo);
						}
						statement.close();
					}

				}
			}

			// all Data
			if (authorFilter == 0) {
				if (useDateFilter) {
					if (useCategoryFilter) {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=? AND m.timestamp >= ? AND m.timestamp <= ?;");
						statement.setLong(1, parent.getiD());
						statement.setTimestamp(2, fromFilter);
						statement.setTimestamp(3, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp,
							// boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}
						statement.close();
					} else {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.timestamp >= ? AND m.timestamp <= ?;");
						statement.setTimestamp(1, fromFilter);
						statement.setTimestamp(2, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp,
							// boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(results.getLong(5));
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							// newMemo.setCategory(categoryFilter);
							exportMemos.add(newMemo);
						}
						statement.close();
					}

				} else {
					if (useCategoryFilter) {

						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=?;");
						statement.setLong(1, parent.getiD());

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp,
							// boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}
						statement.close();
					}
				}
			}

			// all foreign Data
			if (authorFilter == 2) {
				if (useDateFilter) {
					if (useCategoryFilter) {
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=? AND m.author<>? AND m.timestamp >= ? AND m.timestamp <= ?;");
						statement.setLong(1, parent.getiD());
						statement.setString(2, sm.getUsername());
						statement.setTimestamp(3, fromFilter);
						statement.setTimestamp(4, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}
						statement.close();
					} else { // Don't use the Categoryfilter but the datefilter
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.author<>? AND m.timestamp >= ? AND m.timestamp <= ?;");
						// statement.setLong(1, categoryFilter.getiD());
						statement.setString(1, sm.getUsername());
						statement.setTimestamp(2, fromFilter);
						statement.setTimestamp(3, tillFilter);

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(results.getLong(5));
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							// newMemo.setCategory(categoryFilter);
							exportMemos.add(newMemo);
						}
						statement.close();
					}
				}

				else {
					if (useCategoryFilter) {
						// use the categoryfilter but not the date filter
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.category=? AND m.author<>?;");
						statement.setLong(1, parent.getiD());
						statement.setString(2, sm.getUsername());

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(parent.getiD());
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							newMemo.setCategory(parent);
							exportMemos.add(newMemo);
						}
						statement.close();
					} else {
						// use neither the category filter nor the date filter
						statement = con
								.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
										+ " m.author, m.draft from Memos m "
										+ " where m.author<>?;");
						// statement.setLong(1, categoryFilter.getiD());
						statement.setString(1, sm.getUsername());

						results = statement.executeQuery();

						while (results.next()) {

							// public Memo(String title, Date timestamp, boolean
							// draft)
							Memo newMemo = new Memo(results.getString(2),
									results.getTimestamp(4),
									results.getInt(7) == 1);
							newMemo.setAuthor(results.getString(6));
							newMemo.setCategoryID(results.getLong(5));
							newMemo.setContent(results.getString(3));
							newMemo.setiD(results.getLong(1));
							// newMemo.setCategory(categoryFilter);
							exportMemos.add(newMemo);
						}
						statement.close();
					}

				}
			}
			// System.out.println("exportMemo: "
			// + exportMemos.lastElement().getTitle());
		} catch (SQLException e) {
			Utils
					.showError("Ein DB - Fehler ist beim gefilterten Export aus der DB aufgetreten");
			e.printStackTrace();
		}
		if (parent != null) {
			for (Category c : parent.getChildCategories()) {
				addMemosFromChildCategories(c, statement, results, con,
						exportMemos);
			}
		}
	}

	public void ExportFilteredMemosToXML() {
		Connection con = Connector.getConnection();
		XMLInterface xmlinterface;
		Vector<Memo> exportMemos = new Vector<Memo>();
		PreparedStatement statement = null;
		ResultSet results = null;
		if (useCategoryFilter == false || categoryFilter == null) {
			try {
				statement = con
						.prepareStatement("select m.id, m.title, m.content, m.timestamp, m.category,"
								+ " m.author, m.draft from Memos m;");

				results = statement.executeQuery();

				while (results.next()) {

					// public Memo(String title, Date timestamp,
					// boolean
					// draft)
					Memo newMemo = new Memo(results.getString(2), results
							.getTimestamp(4), results.getInt(7) == 1);
					newMemo.setAuthor(results.getString(6));
					newMemo.setCategoryID(results.getLong(5));
					newMemo.setContent(results.getString(3));
					newMemo.setiD(results.getLong(1));

					exportMemos.add(newMemo);
				}
				statement.close();
//				for (Category c : cats) {
//					if (c.getParentCategory() == null) {
//						addMemosFromChildCategories(c, statement, results, con,
//								exportMemos);
//					}
//				}
			} catch (Exception e) {
				Utils
						.showError("Fehler beim auslesen zum Export (ohne Filter)");
				e.printStackTrace();
			}
		}

		else {
			addMemosFromChildCategories(categoryFilter, statement, results,
					con, exportMemos);
		}
		xmlinterface = new XMLInterface(exportMemos, exportPath);
		xmlinterface.saveToXML();

	}
}