package descriptorApp.model;

import java.io.File;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.Label;
import javafx.scene.control.TextArea;
import javafx.scene.layout.GridPane;
import javafx.scene.layout.Priority;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerException;

import descriptorApp.MainApp;

public class IOOperations {

	private DBConnection dbConnection;

	public static final String metaTableName = "MetaDescriptions";
	public static final String successfullyUpdatedMessage = "Successfully applied changes in DataBase!\n";
	public static final String connectionsFilePath = "connectionFile.xml";
	public static final String viewsFilePath = "viewFile.xml";
	public static final String connectionTestSuccessfullMessage = "Connection Tested Successfully!";
	public static final String connectionTestUnsuccessfullMessage = "Connection is not Valid!";

	// private File viewsFile;
	private File connectionsFile;
	private MainApp mainApp;

	public IOOperations(MainApp mainApp) {
		this.mainApp = mainApp;
		dbConnection = new DBConnection();
		connectionsFile = new File(connectionsFilePath);
	}

	public void getInitialDescriptionsFromDB(
			HashMap<String, ArrayList<String>> tablesAndColumns) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			rs = null;
			int id;
			String columnName, tableName, understandableName, description;

			String selectFromMetaDescriptions = "SELECT id, tableName, columnName, understandableName, description "
					+ "FROM " + metaTableName;

			String createDescriptionTableQuery = "CREATE TABLE "
					+ metaTableName + " " + "(id INTEGER not NULL, "
					+ " tableName VARCHAR(255), "
					+ " columnName VARCHAR(255), "
					+ " understandableName NVARCHAR(255), "
					+ " description NVARCHAR(255), " + " PRIMARY KEY ( id ))";

			// System.out.println(query);

			DatabaseMetaData dbm = conn.getMetaData();
			rs = dbm.getTables(null, null, "MetaDescriptions", null);
			if (rs.next()) {
				// System.out.println("Table MetaDescriptions exists.");

				rs.close();
				rs = stmt.executeQuery(selectFromMetaDescriptions);

				while (rs.next()) {
					id = rs.getInt(1);
					tableName = rs.getString(2);
					columnName = rs.getString(3);
					understandableName = (rs.getString(4) == null) ? "" : rs
							.getString(4);
					description = (rs.getString(5) == null) ? "" : rs
							.getString(5);

					Description tmpDescription = new Description(id, tableName,
							columnName, understandableName, description);

					tmpDescription.setDescriptionID(id);

					insertIntoMap(tmpDescription, tablesAndColumns);
					mainApp.getDescriptionData().add(tmpDescription);
					// System.out.println(tableName + " : " + columnName);
				}
				Description.hasAnyChanged.setValue(false);

			} else {
				stmt.executeUpdate(createDescriptionTableQuery);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void loadAllColumnsFromDB(
			HashMap<String, ArrayList<String>> tablesAndColumns,
			boolean addToAll) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
			String columnName, tableName;

			String findAllTablesAndColumnsQuery = "SELECT o.Name, c.Name "
					+ "FROM sys.columns c "
					+ "JOIN sys.objects o ON o.object_id = c.object_id "
					+ "WHERE o.type = 'U'"/* + "ORDER BY o.Name " */;

			rs = stmt.executeQuery(findAllTablesAndColumnsQuery);
			ArrayList<String> columnsOfTable = null;
			while (rs.next()) {

				tableName = rs.getString(1);
				columnName = rs.getString(2);

				if (!tablesAndColumns.containsKey(tableName)) {
					tablesAndColumns.put(tableName, new ArrayList<String>());
				}

				columnsOfTable = tablesAndColumns.get(tableName);
				if (!columnsOfTable.contains(columnName)) {
					columnsOfTable.add(columnName);

					if (addToAll) {
						Description tmpDescription = new Description(tableName,
								columnName);
						tmpDescription.setHasChanged(true);
						Description.hasAnyChanged.setValue(true);
						mainApp.getDescriptionData().add(tmpDescription);
					}
					// System.out.println(tableName + " : " + columnName);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	private void insertIntoMap(Description tmpDescription,
			HashMap<String, ArrayList<String>> tablesAndColumns) {
		if (!tablesAndColumns.containsKey(tmpDescription.getTableName())) {
			ArrayList<String> columns = new ArrayList<String>();
			columns.add(tmpDescription.getColumnName());
			tablesAndColumns.put(tmpDescription.getTableName(), columns);
		} else {
			ArrayList<String> columns = tablesAndColumns.get(tmpDescription
					.getTableName());
			if (!columns.contains(tmpDescription.getColumnName())) {
				columns.add(tmpDescription.getColumnName());
			}
		}
	}

	public String checkConnection() {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		boolean reachable;

		try {
			conn = ds.getConnection();

			reachable = conn.isValid(10);// 10 sec

			// String selectFromMetaDescriptions =
			// "SELECT id, tableName, columnName, understandableName, description "
			// + "FROM " + metaTableName;
			// stmt = conn.createStatement();
			// rs = stmt.executeQuery(selectFromMetaDescriptions);
			// rs.close();
		} catch (SQLServerException e) {
			return e.getMessage();
		} catch (SQLException e) {
			return e.getMessage();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
					return e.getMessage();
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (Exception e) {
					return e.getMessage();
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
					return e.getMessage();
				}
		}
		if (reachable) {
			return connectionTestSuccessfullMessage;
		}
		return connectionTestUnsuccessfullMessage;
	}

	public String updateDescriptionsInDB(List<Description> descriptions) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		String message = "";

		if (descriptions.size() == 0) {
			return successfullyUpdatedMessage;
		}

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = null;

			for (Description tmpDescription : descriptions) {
				String query = "UPDATE " + metaTableName + " SET tableName = '"
						+ tmpDescription.getTableName() + "' , columnName = '"
						+ tmpDescription.getColumnName()
						+ "' , understandableName = N'"
						+ tmpDescription.getUnderstandableName()
						+ "' , description = N'"
						+ tmpDescription.getDescription() + "' WHERE id = "
						+ tmpDescription.getDescriptionID();
				// System.out.println(query);
				if (stmt.executeUpdate(query) == 0) {
					query = "INSERT INTO " + metaTableName + " VALUES ("
							+ tmpDescription.getDescriptionID() + " , '"
							+ tmpDescription.getTableName() + "' , '"
							+ tmpDescription.getColumnName() + "' , N'"
							+ tmpDescription.getUnderstandableName() + "' , N'"
							+ tmpDescription.getDescription() + "')";
					System.out.println(stmt.executeUpdate(query));
				}
			}
			message += successfullyUpdatedMessage;

		} catch (SQLException e) {
			e.printStackTrace();
			message += e.getMessage();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
		}
		return message;
	}

	public String deleteDescriptionsInDB(List<Description> descriptions) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		String message = "";

		if (descriptions.size() == 0) {
			return successfullyUpdatedMessage;
		}

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = null;

			for (Description tmpDescription : descriptions) {
				String query = "DELETE FROM " + metaTableName + " WHERE ID = "
						+ tmpDescription.getDescriptionID();
				// System.out.println(query);
				stmt.executeUpdate(query);
			}
			message += successfullyUpdatedMessage;

		} catch (SQLException e) {
			message += e.getMessage();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					message += e.getMessage();
				}
			}
		}
		return message;
	}

	public void loadConnectionDataFromFile() {
		try {
			JAXBContext context = JAXBContext
					.newInstance(DBConnectionListWrapper.class);
			Unmarshaller um = context.createUnmarshaller();

			// Reading XML from the file and unmarshalling.
			DBConnectionListWrapper wrapper = (DBConnectionListWrapper) um
					.unmarshal(connectionsFile);

			mainApp.getConnectionData().clear();
			mainApp.getConnectionData().addAll(wrapper.getDbConnections());

			// Save the file path to the registry.
			// setPersonFilePath(file);

		} catch (Exception e) { // catches ANY exception
			showException(e, "Error", "Could not save data to file!",
					connectionsFile.getPath());
		}
	}

	public void saveConnectionDataToFile() {
		try {
			JAXBContext context = JAXBContext
					.newInstance(DBConnectionListWrapper.class);
			Marshaller m = context.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

			// Wrapping our person data.
			DBConnectionListWrapper wrapper = new DBConnectionListWrapper();
			wrapper.setDbConnections(mainApp.getConnectionData());

			// Marshalling and saving XML to the file.
			m.marshal(wrapper, connectionsFile);

			// Save the file path to the registry.
			// setPersonFilePath(file);
		} catch (Exception e) { // catches ANY exception
			showException(e, "Error", "Could not save data to file!",
					connectionsFile.getPath());
		}
	}

	public void showException(Exception e, String title, String body,
			String content) {
		Alert alert = new Alert(AlertType.ERROR);
		alert.setTitle(title);
		alert.setHeaderText(body);
		alert.setContentText(content);

		// Create expandable Exception.
		StringWriter sw = new StringWriter();
		PrintWriter pw = new PrintWriter(sw);
		e.printStackTrace(pw);
		String exceptionText = sw.toString();

		Label label = new Label("The exception stacktrace was:");

		TextArea textArea = new TextArea(exceptionText);
		textArea.setEditable(false);
		textArea.setWrapText(true);

		textArea.setMaxWidth(Double.MAX_VALUE);
		textArea.setMaxHeight(Double.MAX_VALUE);
		GridPane.setVgrow(textArea, Priority.ALWAYS);
		GridPane.setHgrow(textArea, Priority.ALWAYS);

		GridPane expContent = new GridPane();
		expContent.setMaxWidth(Double.MAX_VALUE);
		expContent.add(label, 0, 0);
		expContent.add(textArea, 0, 1);

		// Set expandable Exception into the dialog pane.
		alert.getDialogPane().setExpandableContent(expContent);

		alert.showAndWait();
	}

	public String[][] previewView(String queryString, int numberOfRows) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsMetaData = null;
		String[][] results = null;

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();

			rs = stmt.executeQuery(queryString);

			rsMetaData = rs.getMetaData();

			results = new String[numberOfRows][rsMetaData.getColumnCount()];

			for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
				results[0][i] = rsMetaData.getColumnLabel(i+1);
			}
			int j = 1;
			while(rs.next()){
				for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
					results[j][i] = rs.getString(i+1);
				}
				j++;
			}


		} catch (SQLException e) {
			showException(e, "Error", "Could not execute the query!",
					queryString);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return results;
	}

	public boolean createView(String queryString) {

		boolean exceptionOccurred = false;

		Connection conn = null;
		Statement stmt = null;

		SQLServerDataSource ds = new SQLServerDataSource();
		ds.setServerName(dbConnection.getServerIP());
		ds.setPortNumber(Integer.parseInt(dbConnection.getServerPort()));
		ds.setDatabaseName(dbConnection.getDbName());
		ds.setUser(dbConnection.getDbUsername());
		ds.setPassword(dbConnection.getDbPassword());

		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();

			stmt.executeUpdate(queryString);

		} catch (SQLException e) {
			showException(e, "Error", "Could not execute the query!",
					queryString);
			exceptionOccurred = true;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

		return exceptionOccurred;
	}

	/*
	 * public void loadViewDataFromFile() { try { JAXBContext context =
	 * JAXBContext .newInstance(DBViewListWrapper.class); Unmarshaller um =
	 * context.createUnmarshaller();
	 *
	 * // Reading XML from the file and unmarshalling. viewsFile = new
	 * File(mainApp.getActiveConnection() .getConnectionName() + "-" +
	 * viewsFilePath);
	 *
	 * if (viewsFile.exists()) { DBViewListWrapper wrapper = (DBViewListWrapper)
	 * um .unmarshal(viewsFile);
	 *
	 * mainApp.getViewData().clear();
	 *
	 * if (wrapper != null && wrapper.getDbViews() != null) {
	 * mainApp.getViewData().addAll(wrapper.getDbViews()); } } else {
	 * mainApp.getViewData().clear(); } // Save the file path to the registry.
	 * // setPersonFilePath(file);
	 *
	 * } catch (Exception e) { // catches ANY exception e.printStackTrace();
	 * Dialogs.create().title("Error")
	 * .masthead("Could not load data from view file!\n") .showException(e); } }
	 */
	/*
	 * public void saveViewDataToFile() { try { JAXBContext context =
	 * JAXBContext .newInstance(DBViewListWrapper.class); Marshaller m =
	 * context.createMarshaller();
	 * m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);
	 *
	 * // Wrapping our person data. DBViewListWrapper wrapper = new
	 * DBViewListWrapper(); wrapper.setDbViews(mainApp.getViewData());
	 *
	 * // Marshalling and saving XML to the file. viewsFile = new
	 * File(mainApp.getActiveConnection() .getConnectionName() + "-" +
	 * viewsFilePath); m.marshal(wrapper, viewsFile);
	 *
	 * // Save the file path to the registry. // setPersonFilePath(file); }
	 * catch (Exception e) { // catches ANY exception Dialogs.create()
	 * .title("Error") .masthead( "Could not save data to file:\n" +
	 * viewsFile.getPath()).showException(e); } }
	 */

	public String getServerIP() {
		return dbConnection.getServerIP();
	}

	public void setServerIP(String serverIP) {
		dbConnection.setServerIP(serverIP);
	}

	public String getServerPort() {
		return dbConnection.getServerPort();
	}

	public void setServerPort(String serverPort) {
		dbConnection.setServerPort(serverPort);
	}

	public String getDbName() {
		return dbConnection.getDbName();
	}

	public void setDbName(String dbName) {
		dbConnection.setDbName(dbName);
	}

	public String getDbUsername() {
		return dbConnection.getDbUsername();
	}

	public void setDbUsername(String dbUsername) {
		dbConnection.setDbUsername(dbUsername);
	}

	public String getDbPassword() {
		return dbConnection.getDbPassword();
	}

	public void setDbPassword(String dbPassword) {
		dbConnection.setDbPassword(dbPassword);
	}

	public MainApp getMainApp() {
		return mainApp;
	}

	public void setMainApp(MainApp mainApp) {
		this.mainApp = mainApp;
	}

	public String getMetaTableName() {
		return metaTableName;
	}

}