package dlw.dataimport;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import au.com.bytecode.opencsv.*;

public class CSVImporter {

	int[] columns;
	Map<Integer, IFilter> filters;
	String tableName;

	public CSVImporter(int[] columns, String tableName) {
		this.filters = new HashMap<Integer, IFilter>();
		this.columns = columns;
		this.tableName = tableName;
	}

	public void attachFilter(int colNumber, IFilter filter) {
		filters.put(colNumber, filter);
	}

	protected String buildStatement() {
		String rv = "INSERT INTO " + tableName + " VALUES (";
		rv += "?";
		for (int i = 1; i < columns.length; i++)
			rv += ", ?";
		rv += ")";
		return rv;
	}

	protected String processFilter(int colNumber, String item) {
		if (filters.containsKey(colNumber))
			return filters.get(colNumber).filter(item);
		else
			return item;
	}

	public void importData(String inputFile, char separator, String encoding,
			Connection connection) throws SQLException, IOException {

		List<String[]> data = importFile(inputFile, separator, encoding);

		connection.createStatement().execute("delete from " + tableName);

		PreparedStatement ps = connection.prepareStatement(buildStatement());

		int COMMIT_EVERY = 100;

		// skip the first row, it's the header
		for (int i = 1; i < data.size(); i++) {
			String[] row = data.get(i);

			for (int fi = 0; fi < columns.length; fi++) {
				int actualCol = columns[fi];
				ps.setString(fi + 1, processFilter(actualCol, row[actualCol]));
			}

			ps.addBatch();

			if (i % COMMIT_EVERY == 0) {
				ps.executeBatch();
				ps.clearBatch();
			}
		}

		ps.executeBatch();
		ps.close();

	}

	@SuppressWarnings("unchecked")
	public static List<String[]> importFile(String filename, char separator,
			String encoding) throws IOException {

		Reader csvfile = new InputStreamReader(new FileInputStream(filename),
				encoding);
		List<String[]> rv = (List<String[]>) new CSVReader(csvfile, separator)
				.readAll();

		for (String[] a : rv) {
			for (int i = 0; i < a.length; i++)
				System.out.print("<" + a[i] + ">");
			System.out.println();
		}
		return rv;
	}

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");

		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://localhost:8889/dlworkshop", "root", "root");

		CSVImporter ccpayment = new CSVImporter(
				new int[] { 2, 3, 4, 5, 6, 10 }, "card_payment");
		ccpayment.importData(
				"/Users/Domenico/Desktop/dlworkshop/pagamenti.csv", '\t',
				"UTF8", conn);
	}
}