import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import javax.swing.BoxLayout;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellEditor;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

/**
 * Reads semicolon separated SCDF file for editing.
 *
 * @author easai
 *
 */
public class SCDFFileEdit extends JFrame {

	/**
	 *
	 */
	private static Logger log = LoggerFactory.getLogger(SCDFFileEdit.class);

	private static final long serialVersionUID = 1L;

	enum MARKING {
		DIFFER, ERROR
	};

	JMenuBar mb = new JMenuBar();

	private JScrollPane scroll = new JScrollPane();

	JTable table;

	ArrayList<String> scdfList = new ArrayList<>();
	String scdfFile = "";
	String _iniFile = "SCDFgen.ini";
	String _csvFile = "prq.csv";
	final String INFILE = "inFile";
	final String OUTDIR = "outDir";
	final String PDFDIR = "pdfDir";
	final String PDFOUTPUTDIR = "pdfOutputDir";
	String valueTable[][];
	Properties prop = new Properties();
	ArrayList<String[]> csvList = new ArrayList<>();
	PRQConstants.PRQ valueList[] = PRQ.values();

	Document readXMLFile(String sqlFile) throws Exception {
		DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory
				.newInstance();
		DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
		Document doc = docBuilder.newDocument();

		log.info("Reading XML file: " + sqlFile);
		File file = new File(sqlFile);
		if (!file.exists()) {
			log.error("Error reading XML file: " + sqlFile);
			throw new Exception("File not found");
		} else if (file.isDirectory()) {
			log.error("Error reading XML file, is a directory: " + sqlFile);
			throw new Exception("File not found, is a directory");
		} else {
			try {
				doc = docBuilder.parse(new FileInputStream(file));
			} catch (Exception e) {
				log.error("Error reading XML file, bad format: " + sqlFile);
				throw e;
			}
		}
		return doc;
	}

	public void readSCDF(String scdfFile) {
		BufferedReader br;
		try {
			log.info("Reading file: " + scdfFile);
			File file = new File(scdfFile);
			if (!file.exists()) {
				throw new Exception();
			}
			setTitle(file.getName());
			br = new BufferedReader(new FileReader(scdfFile));
			String line = "";

			scdfList.clear();
			while ((line = br.readLine()) != null) {
				scdfList.add(line);
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void readCSV(String csvFile) {
		BufferedReader br;
		try {
			log.info("Reading CSV file: " + csvFile);
			br = new BufferedReader(new FileReader(csvFile));
			String line = "";

			csvList.clear();
			while ((line = br.readLine()) != null) {
				String[] list = line.split(",");
				csvList.add(list);
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void exportCSV() {
		JFileChooser fileChooser = new JFileChooser();
		fileChooser.setCurrentDirectory(new File("."));
		fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
		int res = fileChooser.showOpenDialog(SCDFFileEdit.this);
		if (res == JFileChooser.APPROVE_OPTION) {
			File file = fileChooser.getSelectedFile();
			String fn = file.getPath();
			writeCSV(fn);
		}
	}

	public void writeCSV(String fn) {
		log.info("Exporting CSV: " + fn);
		PrintWriter pw = null;
		try {
			TableCellEditor editor = table.getCellEditor();
			if (editor != null) {
				editor.stopCellEditing();
			}
			pw = new PrintWriter(new File(fn));

			DefaultTableModel model = (DefaultTableModel) table.getModel();
			int nRow = model.getRowCount();
			int nCol = model.getColumnCount();

			String line = "";
			String val = "";
			for (int i = 0; i < nCol; i++) {
				line = "";
				val = "";
				for (int j = 0; j < nRow; j++) {
					val = (String) model.getValueAt(j, i);
					if (j != 0)
						line += ",";
					line += "\"" + val + "\"";
				}
				pw.println(line);
			}
			pw.close();
		} catch (Exception e) {
			log.error("File write error", e);
			if (pw != null) {
				pw.close();
			}
		}
	}

	public void writeSCDF(String scdfFile) {
		PrintWriter pw = null;
		try {
			TableCellEditor editor = table.getCellEditor();
			if (editor != null) {
				editor.stopCellEditing();
			}
			pw = new PrintWriter(new File(scdfFile));

			DefaultTableModel model = (DefaultTableModel) table.getModel();
			int nRow = model.getRowCount();
			int nCol = model.getColumnCount();

			String line = "";
			String val = "";
			for (int i = 2; i < nCol; i++) {
				line = "";
				val = "";
				for (int j = 0; j < nRow; j++) {
					val = (String) model.getValueAt(j, i);
					if (j != 0)
						line += ";";
					line += val;
				}
				pw.println(line);
			}
			pw.close();

			this.scdfFile = scdfFile;
			readSCDF(scdfFile);
			setTable();
		} catch (Exception e) {
			log.error("File write error", e);
			if (pw != null) {
				pw.close();
			}
		}
	}

	/**
	 * Constructor.
	 */
	SCDFFileEdit() {
		init();

		setTitle("SCDF File Edit");
		pack();
		// setSize(500,500);
		setVisible(true);
	}

	public void openSCDFFile() {
		JFileChooser fileChooser = new JFileChooser();
		fileChooser.setCurrentDirectory(new File("."));
		fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
		int res = fileChooser.showOpenDialog(SCDFFileEdit.this);
		if (res == JFileChooser.APPROVE_OPTION) {
			File file = fileChooser.getSelectedFile();
			scdfFile = file.getPath();
			readSCDF(scdfFile);
			setTable();
		}
	}

	public void saveSCDFFile() {
		JFileChooser fileChooser = new JFileChooser();
		File selectedFile = new File(scdfFile);
		fileChooser.setCurrentDirectory(selectedFile.getParentFile());
		fileChooser.setSelectedFile(selectedFile);
		fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
		int res = fileChooser.showOpenDialog(SCDFFileEdit.this);
		if (res == JFileChooser.APPROVE_OPTION) {
			File file = fileChooser.getSelectedFile();
			writeSCDF(file.getPath());
		}
	}

	private void setTable() {

		ArrayList<String> labelList = new ArrayList<>();
		labelList.add("");
		labelList.add("Field");
		for (int i = 0; i < scdfList.size(); i++) {
			labelList.add("" + (i + 1));
		}

		DefaultTableModel tableModel = new DefaultTableModel(
				labelList.toArray(), 0);

		int nVal = valueList.length;
		int nSCDF = scdfList.size();
		valueTable = new String[nVal][nSCDF];
		if (0 < nSCDF) {
			for (int i = 0; i < nSCDF; i++) {
				String line = scdfList.get(i);
				String scdf[] = line.split(";");
				for (int j = 0; j < nVal; j++) {
					if (j < scdf.length) {
						valueTable[j][i] = scdf[j];
					}
				}
			}
		}
		for (int i = 0; i < nVal; i++) {
			ArrayList<Object> fieldList = new ArrayList<>();
			fieldList.add(i + 1);
			fieldList.add(valueList[i]);
			for (int j = 0; j < nSCDF; j++) {
				fieldList.add(valueTable[i][j]);
			}
			tableModel.addRow(fieldList.toArray());
		}

		table.setModel(tableModel);

		RowRenderer rowRenderer = new RowRenderer();

		MARKING markingTable[][] = new MARKING[nVal][nSCDF];

		for (int row = 0; row < nVal; row++) {
			String item0 = valueTable[row][0];
			boolean differ = false;
			for (int col = 0; col < nSCDF; col++) {
				String item = valueTable[row][col];
				if ((item != null && !item.equals(item0))
						|| (item == null && item0 != null)) {
					differ = true;
				}
			}
			if (differ) {
				for (int col = 0; col < nSCDF; col++) {
					markingTable[row][col] = MARKING.DIFFER;
				}
			}
		}

		int pdfIndex = getIndex("PDF_FILE_NAME");
		log.debug("pdfIndex: " + pdfIndex);
		int pdfPathIndex = getIndex("PDF_PATH");
		log.debug("pdfPathIndex: " + pdfPathIndex);

		log.debug("setting pdf");
		for (int i = 0; i < nSCDF; i++) {
			if (pdfIndex != -1 && pdfPathIndex != -1) {
				log.debug(pdfIndex + "," + pdfPathIndex);
				String pdf = valueTable[pdfIndex][i];
				String path = valueTable[pdfPathIndex][i];

				File dir = new File(path + File.separator + pdf);

				// if (dir==null || !dir.exists()) {
				markingTable[pdfIndex][i] = MARKING.ERROR;
				markingTable[pdfPathIndex][i] = MARKING.ERROR;
				log.info("PDF file does not exist: " + dir.getPath());
				// }
			}
		}

		TableColumnModel tcm = table.getColumnModel();
		for (int i = 0; i < tcm.getColumnCount(); i++) {
			TableColumn tc = tcm.getColumn(i);
			tc.setCellRenderer(rowRenderer);
		}

		rowRenderer.setMarking(markingTable);

		DefaultTableColumnModel columnModel = (DefaultTableColumnModel) table
				.getColumnModel();
		TableColumn column = null;
		for (int i = 0; i < columnModel.getColumnCount(); i++) {
			column = columnModel.getColumn(i);
			if (i == 0) {
				column.setMinWidth(30);
				column.setMaxWidth(30);
				column.setResizable(false);
			} else if (i == 1) {
				column.setMinWidth(150);
				column.setMaxWidth(150);
				column.setResizable(false);
			} else {
				column.setPreferredWidth(500);
			}
		}

		repaint();
	}

	int getIndex(String label) {
		int index = 0;
		int nVal = valueList.length;
		while (!valueList[index].name().equals(label) && ++index < nVal)
			;
		if (nVal <= index)
			index = -1;
		return index;
	}

	public void init() {
		readCSV(this._csvFile);
		table = new JTable() {
			/**
			 *
			 */
			private static final long serialVersionUID = 1L;

			public String getToolTipText(MouseEvent e) {
				int row = convertRowIndexToModel(rowAtPoint(e.getPoint()));
				String text = "";
				if (row < csvList.size()) {
					String[] list = csvList.get(row);
					for (int i = 0; i < list.length; i++) {
						text += list[i] + " : ";
					}
				}
				return text;
			}
		};

		Container pane = getContentPane();
		pane.setLayout(new BoxLayout(pane, BoxLayout.Y_AXIS));

		this.setJMenuBar(mb);
		JMenu mFile = new JMenu("File");
		JMenuItem miOpen = new JMenuItem("Open");
		JMenuItem miSave = new JMenuItem("Save");
		JMenuItem miExport = new JMenuItem("Export");
		JMenuItem miQuit = new JMenuItem("Quit");

		mFile.add(miOpen);
		mFile.add(miSave);
		mFile.add(miExport);
		mFile.add(miQuit);
		mb.add(mFile);

		JMenu mSend = new JMenu("Send");
		JMenuItem miSend = new JMenuItem("Send");
		JMenuItem miGenerate = new JMenuItem("Generate SCDF file");
		mSend.add(miSend);
		mSend.add(miGenerate);
		mb.add(mSend);

		JMenu mPref = new JMenu("Pref");
		JMenuItem miIni = new JMenuItem("Edit .ini File");
		mPref.add(miIni);
		mb.add(mPref);

		JMenu mAnalysis = new JMenu("Analysis");
		JMenuItem miCompare = new JMenuItem("Compare");
		mAnalysis.add(miCompare);
		mb.add(mAnalysis);

		miOpen.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				openSCDFFile();
			}
		});
		miSave.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				saveSCDFFile();
			}
		});
		miExport.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				exportCSV();
			}
		});
		miQuit.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				dispose();
			}
		});
		miSend.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				sendSCDF();
			}
		});
		miGenerate.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				SCDFFileEdit f = new SCDFFileEdit();
				f.readSCDF(scdfFile);
				f.setTable();
			}
		});
		miIni.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				new IniFileEdit();
			}
		});
		miCompare.addActionListener(new Adapter() {
			public void actionPerformed(ActionEvent e) {
				compare();
			}
		});

		scroll.setViewportView(table);
		pane.add(scroll);

		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

	}

	public void readIniFile() {
		try {
			File file = new File(_iniFile);
			if (!file.exists()) {
				throw new Exception("INI File not found");
			} else if (file.isDirectory()) {
				throw new Exception("INI File not found");
			} else {// read the ini file
				BufferedReader reader = new BufferedReader(new FileReader(file));

				prop.load(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void sendSCDF() {
		log.info("Sending file: " + scdfFile);
		int nSCDF = valueTable[0].length;
		if (nSCDF == 0) {
			return;
		}
		// read ini file
		readIniFile();

		// get outDir
		String outDir = (String) prop.getProperty(OUTDIR);
		log.info("outDir: " + outDir);

		// write file

		int nVal = valueList.length;
		int index = getIndex("CLIENT_CODE");
		if (index != -1) {
			String clientCode = valueTable[index][0];
			log.info("Client Code: " + clientCode);

			File dir = new File(outDir + File.separator + clientCode);
			if (!dir.exists()) {
				dir.mkdir();
			}

			PrintWriter pw = null;
			try {
				String fn = PRQList.getPRQName(scdfFile);
				String prqFile = dir.getPath() + File.separator + new File(fn);
				pw = new PrintWriter(prqFile);
				log.info("Generating SCDF file: " + prqFile);
				for (int i = 0; i < nSCDF; i++) {
					String line = "";
					for (int j = 0; j < nVal; j++) {
						if (j != 0) {
							line += ";";
						}
						line += valueTable[j][i];
					}
					pw.println(line);
				}
				pw.close();
			} catch (Exception e) {
				log.error("Error generating SCDF file.", e);
			}

		}
	}

	public static void main(String[] args) {
		new SCDFFileEdit();
	}

	abstract class Adapter implements ActionListener {

	}

	private class RowRenderer extends DefaultTableCellRenderer {
		/**
		 *
		 */
		private static final long serialVersionUID = 1L;

		MARKING marking[][] = null;

		@SuppressWarnings("unused")
		public MARKING[][] getMarking() {
			return marking;
		}

		public void setMarking(MARKING[][] marking) {
			this.marking = marking;
		}

		public Component getTableCellRendererComponent(JTable t, Object value,
				boolean isSelected, boolean hasFocus, int row, int column) {
			if (row % 2 == 0) {
				setBackground(new Color(0xff, 0xff, 0xff));
			} else {
				setBackground(new Color(0xea, 0xeb, 0xff));
			}

			if (1 < column && marking[row][column - 2] == MARKING.ERROR) {
				setBackground(new Color(0xff, 0xcc, 0xcc));
			} else if (1 < column && marking[row][column - 2] == MARKING.DIFFER) {
				setBackground(new Color(0xe0, 0xff, 0xe0));
			}

			if (column == 1) {
				setBackground(new Color(0xd3, 0xd6, 0xff));
			}

			return super.getTableCellRendererComponent(t, value, isSelected,
					hasFocus, row, column);
		}
	}

	private class CompareRenderer extends DefaultTableCellRenderer {
		/**
		 *
		 */
		private static final long serialVersionUID = 1L;

		public Component getTableCellRendererComponent(JTable t, Object value,
				boolean isSelected, boolean hasFocus, int row, int column) {
			if (row % 2 == 0) {
				setBackground(new Color(0xff, 0xff, 0xff));
			} else {
				setBackground(new Color(0xea, 0xeb, 0xff));
			}

			if (column == 0) {
				setBackground(new Color(0xd3, 0xd6, 0xff));
			}

			return super.getTableCellRendererComponent(t, value, isSelected,
					hasFocus, row, column);
		}
	}

	public void compare() {
		// create header list
		String headerList[] = { "CLIENT_CODE", "PRODUCT_TYPE",
				"PERSONALIZED_INDICATOR", "PNP", "Job Type", "Source File",
				"Group Code", "Product" };

		// create index list
		int indexList[] = new int[headerList.length];
		for (int i = 0; i < headerList.length; i++) {
			indexList[i] = this.getIndex(headerList[i]);
		}

		// get file list
		File dir = new File("resources");
		String fileList[] = dir.list();
		String scdfArray[][] = new String[headerList.length][fileList.length];

		// read
		for (int i = 0; i < fileList.length; i++) {
			readSCDF(dir.getPath() + File.separator + fileList[i]);
			for (int j = 0; j < scdfList.size(); j++) {
				String list[] = scdfList.get(j).split(";");
				for (int k = 0; k < indexList.length; k++) {
					if (0 <= indexList[k]) {
						scdfArray[k][i] = list[indexList[k]];
					}
				}
			}
		}

		scdfArray = retrieve(scdfArray, 4, "jobtype", new int[] { 0, 1 });
		scdfArray = retrieve(scdfArray, 5, "filename", new int[] { 0, 1 });
		scdfArray = retrieve(scdfArray, 6, "groupcode", new int[] { 0, 1 });
		scdfArray = retrieve(scdfArray, 7, "product", new int[] { 0, 1 });

		// array -> table
		String tableHeader[] = new String[fileList.length + 1];
		tableHeader[0] = "";
		for (int i = 0; i < fileList.length; i++) {
			tableHeader[i + 1] = fileList[i];
		}

		DefaultTableModel tableModel = new DefaultTableModel(tableHeader, 0);

		for (int i = 0; i < scdfArray.length; i++) {
			ArrayList<Object> array = new ArrayList<>();
			array.add(headerList[i]);
			for (int j = 0; j < scdfArray[0].length; j++) {
				array.add(scdfArray[i][j]);
			}
			tableModel.addRow(array.toArray());
		}

		table.setModel(tableModel);

		DefaultTableColumnModel columnModel = (DefaultTableColumnModel) table
				.getColumnModel();
		TableColumn column = null;
		for (int i = 0; i < columnModel.getColumnCount(); i++) {
			column = columnModel.getColumn(i);
			if (i == 0) {
				column.setMinWidth(190);
				column.setMaxWidth(190);
				column.setResizable(false);
			} else {
				column.setPreferredWidth(50);
			}
		}

		CompareRenderer rowRenderer = new CompareRenderer();

		TableColumnModel tcm = table.getColumnModel();
		for (int i = 0; i < tcm.getColumnCount(); i++) {
			TableColumn tc = tcm.getColumn(i);
			tc.setCellRenderer(rowRenderer);
		}

		repaint();
	}

	public String replaceParam(String str, String[] list) {
		String res = "";
		int index = 0;
		int count = 0;
		int prev = 0;
		while ((index = str.indexOf('?', index)) != -1) {
			if (count < list.length) {
				if (prev < index) {
					res += str.substring(prev, index);
				}
				res += list[count];
				count++;
			}
			index++;
			prev = index;
		}
		if (prev < str.length()) {
			res += str.substring(prev);
		}
		return res;
	}

	public String[][] retrieve(String dataTable[][], int row, String sqlName,
			int[] param) {
		try {
			String sqlFile = "SCDFgen.xml";
			log.debug("Start reading XML file:" + sqlFile);
			Document doc = readXMLFile(sqlFile);
			log.debug("Finished reading XML file:" + sqlFile);
			Element root = doc.getDocumentElement();
			String sql = "";
			if (root != null) {
				NodeList nodeList = root.getElementsByTagName(sqlName);
				sql = nodeList.item(0).getTextContent();
			}

			for (int index = 0; index < dataTable[row].length; index++) {

				String paramList[] = new String[param.length];
				for (int i = 0; i < paramList.length; i++) {
					paramList[i] = dataTable[param[i]][index];
				}
				String sqlStr = replaceParam(sql, paramList);

				log.info("SQL: " + sqlStr);
				ArrayList<String> list = getRecord(sqlStr);

				if (0 < list.size()) {
					dataTable[row][index] = list.get(0);
				}
			}
		} catch (Exception e) {
			log.error("Error reading XML file:", e);
		}
		return dataTable;
	}

	public static ArrayList<String> getRecord(String sql) {
		ArrayList<String> list = new ArrayList<>();

		String jdbc_url = "jdbc:sqlserver://hostname:portnumber;databaseName=database";
		String user = "user";
		String password = "password";

		ResultSet resultSet;

		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			Connection con = DriverManager.getConnection(jdbc_url, user,
					password);
			con.setAutoCommit(true);
			Statement statement = con.createStatement();

			resultSet = statement.executeQuery(sql);
			ResultSetMetaData rsmd = resultSet.getMetaData();
			String field = "";
			if (0 < rsmd.getColumnCount()) {
				field = rsmd.getColumnName(1);

				while (resultSet.next()) {
					log.info(resultSet.getString(field));
					list.add(resultSet.getString(field));
				}
			}

			log.debug("Retrieved: " + list.size());
			for (int i = 0; i < list.size(); i++) {
				log.info(field + ": " + list.get(i));
			}

			statement.close();
			con.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}