package de.ugoe.cs.swe.memos;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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.File;
import de.ugoe.cs.swe.memos.datamodel.Memo;
import de.ugoe.cs.swe.memos.datamodel.Tag;
import de.ugoe.cs.swe.memos.xml.XMLInterface;

public class ImportManager {
	private String importPath;
	private static ImportManager instance;

	public void setImportPath(String path) {
		importPath = path;
	}

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

	private void insertChildCats(Category parent, PreparedStatement statement,
			Connection con) {
		try {
			if (parent.getParentCategory() == null
					|| parent.getParentCategory().getiD() == null) {
				statement = con
						.prepareStatement("INSERT INTO Categories (Name, ID) VALUES (?,?)");
				statement.setString(1, parent.getName());
				statement.setLong(2, parent.getiD());
			} else {
				statement = con
						.prepareStatement("INSERT INTO Categories (Name, Parent,ID) VALUES (?, ?, ?)");
				statement.setString(1, parent.getName());
				statement.setLong(2, parent.getParentID());
				statement.setLong(3, parent.getiD());
			}
			statement.execute();
			for (Category child : parent.getChildCategories()) {
				insertChildCats(child, statement, con);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void loadMemosFromXML() {

		Vector<Memo> importMemos = new Vector<Memo>();
		XMLInterface xmlinterface = new XMLInterface(importMemos, importPath);
		xmlinterface.loadFromXML();
		Memo activeMemo;
		Connection con = Connector.getConnection();
		PreparedStatement statement = null;
		try { // clear all DBTables
			statement = con
					.prepareStatement("Update Categories set parent=null;");
			statement.execute();
			statement = con.prepareStatement("Delete from Files;");
			statement.execute();
			statement = con.prepareStatement("Delete from Tagging;");
			statement.execute();
			statement = con.prepareStatement("Delete from Memos;");
			statement.execute();
			statement = con.prepareStatement("Delete from Categories;");
			statement.execute();
			// Delete from Tagging; Delete from Memos; Delete from Categories;
		} catch (SQLException e) {
			Utils.showError("Beim löschen der DB ist ein Fehler aufgetreten.");
			System.err.println(e.toString());
		}
		for (Category c : importMemos.get(0).getAllCategories()) {
			if (c.getParentCategory() == null) {
				// Wurzelkategorie gefunden!
				insertChildCats(c, statement, con);
			}
		}

		for (int i = 0; !importMemos.isEmpty(); i++) {
			activeMemo = importMemos.get(i);
			try {
				
				if (activeMemo.getCategoryID() == null) {
					statement = con
							.prepareStatement("INSERT INTO Memos (Title,content, Author, Draft) VALUES (?,?,?,?);");

					statement.setString(1, activeMemo.getTitle());
					statement.setString(2, activeMemo.getContent());
					statement.setString(3, activeMemo.getAuthor());
					statement.setBoolean(4, activeMemo.isDraft());

				} else {
					statement = con
							.prepareStatement("INSERT INTO Memos (Title,content,Author,Draft,Category,id) Values(?,?,?,?,?,?);");

					statement.setString(1, activeMemo.getTitle());
					statement.setString(2, activeMemo.getContent());
					statement.setString(3, activeMemo.getAuthor());
					statement.setBoolean(4, activeMemo.isDraft());
					statement.setLong(5, activeMemo.getCategoryID());
					statement.setLong(6, activeMemo.getiD());
				}
				statement.execute();
				statement.close();
				// inserts the files belonging to a memo into the DB
				statement = con
				.prepareStatement("INSERT INTO Files (name,memo,content) Values(?,?,?)");
				for (File f : activeMemo.getFiles()) {

					statement.setString(1, f.name);
					statement.setLong(2, activeMemo.getiD());
					statement.setBlob(3, new ByteArrayInputStream(f.content));
					//statement.setLong(4, f.id);
					statement.execute();
				}
				statement.close();
				//insert the tags of the active Memo into DB
				statement = con.prepareStatement(
						"INSERT INTO Tagging (tag,memo) Values(?,?)",Statement.RETURN_GENERATED_KEYS);
				for (Tag t : activeMemo.getTags()) {
					statement.setString(1, t.getName());
					statement.setLong(2, t.getMemo().getiD());
					statement.executeUpdate();
					ResultSet rs = statement.getGeneratedKeys();
					rs.next();
					t.setiD(rs.getLong(1));
				}


				statement.close();
			} catch (SQLException e) {
				Utils.showError("Fehler bei Import in die DB");
				
				e.printStackTrace();
			}
		}

	}
}
