package ui;

import java.awt.EventQueue;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import javax.swing.JToolBar;
import javax.swing.SwingUtilities;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTable;

import org.rosuda.REngine.Rserve.RConnection;
import org.rosuda.REngine.Rserve.RserveException;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

@SuppressWarnings("serial")
public class MainUI extends JFrame {

	// Resource file to Rserve()
	private static final String RSOURCEFILE = "source('E:\\\\MyFrontEnd.R')";

	// Table header
	private static final Object[] headers = { "BATCH_ID", "FILE_NAME", "START_DATE", "END_DATE", "STATUS", "REMARKS" };

	// date & time vars
	private long start;
	private long end;
	private final SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	private Date startDate;
	private Date endDate;

	// UI elements
	private JPanel contentPane;
	private JFileChooser openFile;
	private final JScrollPane scrollPaneForConsole;
	private JTextArea console;
	private JScrollPane scrollPaneForTable;
	private JTable table;
	private DefaultTableModel tableModel;

	// R connection
	private RConnection rConnection;

	// Database parameters
	private static Connection connection = null;
	private static final String connectionUrl = "jdbc:oracle:thin:@192.168.99.1:1521:ORCL";
	private static final String connectionUser = "test";
	private static final String connectionPassword = "test";


	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					MainUI frame = new MainUI();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public MainUI() {
		setResizable(false);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 1002, 415);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);

		JToolBar toolBar = new JToolBar();
		toolBar.setBounds(5, 5, 991, 30);
		toolBar.setFloatable(false);
		contentPane.add(toolBar);

		// Open file dialog with action
		JButton btnOpen = new JButton("Open");
		toolBar.add(btnOpen);
		btnOpen.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				openFile = new JFileChooser();
				openFile.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
				openFile.setMultiSelectionEnabled(true);
				openFile.showOpenDialog(MainUI.this);

				File[] files = openFile.getSelectedFiles();

				for(int i = 0; i < files.length; i++){

					final File uploadedFile = files[i];
					consoleAppender("Chose: " + uploadedFile.getAbsolutePath());

					start = System.currentTimeMillis();
					startDate = new Date(start);
					final String uuidTmp = UUID.randomUUID().toString();
					Object[] dataForTable = {uuidTmp, uploadedFile.getName(), dateFormat.format(startDate), null, "In Progress", ""};
					tableModel.addRow(dataForTable);
					storeDbData(dataForTable);

					try {

						SwingUtilities.invokeLater(new Runnable() {
					           public void run() {
					        	   rEvent(uuidTmp, uploadedFile);
					           }
						});

					} catch (NullPointerException e) {
						consoleAppender("File was not chosen");
					}
				}

			}
		});

		// Scroll with Text Area for console
		scrollPaneForConsole = new JScrollPane();
		scrollPaneForConsole.setBounds(0, 320, 996, 66);
		contentPane.add(scrollPaneForConsole);

		console = new JTextArea();
		scrollPaneForConsole.setViewportView(console);

		// Main UI part with table
		scrollPaneForTable = new JScrollPane();
		scrollPaneForTable.setBounds(0, 36, 996, 273);
		contentPane.add(scrollPaneForTable);

		tableModel = new DefaultTableModel(null, headers);
		table = new JTable(tableModel);
		scrollPaneForTable.setViewportView(table);

		// Table sorter
		TableRowSorter<TableModel> sorterHeader = new TableRowSorter<TableModel>(table.getModel());
		table.setRowSorter(sorterHeader);

		// Load DB driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			consoleAppender(e.getMessage());
		}

		// Get data from db and put it into table
		List<Object[]> dataFromDb = getDbData();
		for(int i = 0; i < dataFromDb.size(); i++){
			tableModel.addRow(dataFromDb.get(i));
		}
	}

	/**
	 *  Append text to console
	 * @param text
	 */
	private void consoleAppender(String text) {
		console.append(text + "\n");
	}

	/**
	 *  R script processing and storing results to DB
	 */
	public void rEvent(String uuid, File file) {
		try {
			consoleAppender("Start uploading to Rserve()...");

			rConnection = new RConnection();
			rConnection.eval(RSOURCEFILE);

			rConnection.close();

			end = System.currentTimeMillis();
			endDate= new Date(end);

			NumberFormat formatter = new DecimalFormat("#0.00000");
			consoleAppender("Execution time is " + formatter.format((end - start) / 1000d) + " seconds");

			consoleAppender("File Written Succesfully.....");

			// add data to table and store it into database
			for(int i = 0; i < table.getRowCount(); i++){
				if(table.getValueAt(i, 0).equals(uuid)){
					tableModel.removeRow(i);
				}
			}

			Object[] dataForTable = {uuid, file.getName(), dateFormat.format(startDate), dateFormat.format(endDate), "Success", "+"};
			tableModel.addRow(dataForTable);
			updateDbData(dataForTable);

		} catch (RserveException e) {
			for(int i = 0; i < table.getRowCount(); i++){
				if(table.getValueAt(i, 0).equals(uuid)){
					tableModel.removeRow(i);
				}
			}
			Object[] dataForTable = {uuid, file.getName(), dateFormat.format(startDate), dateFormat.format(endDate), "Error", "-"};
			tableModel.addRow(dataForTable);
			updateDbData(dataForTable);

			consoleAppender("ERROR: " + e.getMessage());
		}
	}

	/**
	 * Store data to database into table: HM_PR_PERFORMANCE_VAR_LOG
	 */
	private void storeDbData(Object[] data){
		try {
			connection = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
			String insertString = "insert into HM_PR_PERFORMANCE_VAR_LOG(batch_id, file_name, start_date, end_date, status, remarks)"
					+ " values (?, ?, ?, ?, ?, ?)";
			PreparedStatement statement = connection.prepareStatement(insertString);

			statement.setString(1, String.valueOf(data[0])); // id
			statement.setString(2, String.valueOf(data[1])); // filename
			statement.setString(3, String.valueOf(data[2])); // startDate
			statement.setString(4, String.valueOf(data[3])); // endDate
			statement.setString(5, String.valueOf(data[4])); // status
			statement.setString(6, String.valueOf(data[5])); // remarks

			statement.execute();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			consoleAppender(e.getMessage());
		}
	}

	/**
	 * Update data in the data base
	 * @param data
	 */
	private void updateDbData(Object[] data){
		try {
			connection = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
			String insertString = "update HM_PR_PERFORMANCE_VAR_LOG set end_date = ?, status = ?, remarks = ? where batch_id = ?";
			PreparedStatement statement = connection.prepareStatement(insertString);

			statement.setString(1, String.valueOf(data[3])); // endDate
			statement.setString(2, String.valueOf(data[4])); // status
			statement.setString(3, String.valueOf(data[5])); // remarks
			statement.setString(4, String.valueOf(data[0])); // id

			statement.execute();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			consoleAppender(e.getMessage());
		}
	}

	/**
	 * Get data from database. From table: HM_PR_PERFORMANCE_VAR_LOG
	 * @return List of Object[]
	 */
	private List<Object[]> getDbData(){

		List<Object[]> result = new ArrayList<Object[]>();

		try {
			connection = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
			Statement statement = connection.createStatement();

			statement.executeQuery("select * from HM_PR_PERFORMANCE_VAR_LOG");
			ResultSet rs = statement.getResultSet();

			while(rs.next()){
				Object[] tmpResult = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
				result.add(tmpResult);
			}

			rs.close();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			consoleAppender(e.getMessage());
		}

		return result;
	}
}