package com.tyndalehouse.step.dataloader.loaders;

import java.io.File;
import java.io.FileFilter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.crosswire.jsword.passage.KeyFactory;
import org.crosswire.jsword.passage.NoSuchKeyException;
import org.crosswire.jsword.passage.NoSuchVerseException;
import org.crosswire.jsword.passage.PassageKeyFactory;
import org.crosswire.jsword.passage.RestrictionType;
import org.crosswire.jsword.passage.RocketPassage;
import org.crosswire.jsword.passage.Verse;
import org.crosswire.jsword.passage.VerseRange;

import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
import au.com.bytecode.opencsv.bean.CsvToBean;

import com.tyndalehouse.step.dataloader.beans.EventTypeBean;
import com.tyndalehouse.step.dataloader.beans.PartialDate;
import com.tyndalehouse.step.dataloader.beans.TargetTypeEnum;
import com.tyndalehouse.step.dataloader.beans.TimelineBean;
import com.tyndalehouse.step.dataloader.beans.TimelineEventBean;
import com.tyndalehouse.step.dataloader.common.DateParsingException;

public class TimelineLoader extends AbstractLoader {
	private HashMap<String, TimelineBean> subTimebands;
	private HashMap<String, TimelineBean> timebands;
	private HashMap<String, EventTypeBean> eventTypes;

	public TimelineLoader() throws SQLException {
		super();
	}

	/**
	 * Reads the timeline directory into beans...
	 * 
	 * @param directoryPath
	 *            directory path
	 * @throws FileNotFoundException
	 *             the directory specified was not found
	 * @throws IOException
	 *             an error occured while reading the data
	 */
	private List<TimelineEventBean> readDataFromTimelineDirectory(String directoryPath) throws FileNotFoundException, IOException {
		File directory = new File(directoryPath);

		if (!directory.isDirectory()) {
			error("The directory specified is not a valid directory");
		}

		// then get list of files in directory
		File[] listOfFiles = directory.listFiles(new FileFilter() {

			public boolean accept(File file) {
				if (!file.getName().endsWith(".csv")) {
					return false;
				}
				return true;
			}
		});

		List<TimelineEventBean> events = new ArrayList<TimelineEventBean>();
		int count = 0;


		for (File f : listOfFiles) {
			// TODO: use log4j
			CSVReader reader = new CSVReader(new FileReader(f));	
			reader.readNext();
			ColumnPositionMappingStrategy<TimelineEventBean> mappings = new ColumnPositionMappingStrategy<TimelineEventBean>();
			mappings.setType(TimelineEventBean.class);
			mappings.captureHeader(reader);
			mappings.setColumnMapping(new String[] { "ID","Name","From","To","From2","To2","Importance","Certainty","Flags","Timeline","Type","Description","Refs","Notes" });
			
			CsvToBean<TimelineEventBean> csv = new CsvToBean<TimelineEventBean>();
			
			List<TimelineEventBean> beans = csv.parse(mappings, reader);
			
			for(TimelineEventBean tb : beans) {
				tb.setTimebandId(f.getName().substring(0, f.getName().length()-4));
			}
			count += beans.size();
			events.addAll(beans);
		}
		System.out.println(String.format("Read %d events", count));

		return events;
	}

	/**
	 * Adds the timeline to the hashmap passed in, assuming it is not already in
	 * there This is just a helper function really
	 * 
	 * @param subTimebandCode
	 */
	public void addSubTimebandIfNotExists(final String subTimebandCode) {
		if(subTimebandCode == null) {
			//do nothing, it is not attached to a subtimeband or an importance
			return;
		}
		
		if (!subTimebands.containsKey(subTimebandCode)) {
			// debatable as to whether or not we want to set the id here...
			TimelineBean tb = new TimelineBean(subTimebands.size() + 1, subTimebandCode, subTimebandCode);
			subTimebands.put(subTimebandCode, tb);
		}
	}

	/**
	 * Adds the timeline to the hashmap passed in, assuming it is not already in
	 * there This is just a helper function really
	 * 
	 * @param timelineCode
	 */
	public void addTimebandIfNotExists(final String timelineCode) {
		if (!timebands.containsKey(timelineCode)) {
			// debatable as to whether or not we want to set the id here...
			TimelineBean tb = new TimelineBean(timebands.size() + 1, timelineCode, timelineCode);
			timebands.put(timelineCode, tb);
		}
	}
	
	private void addEventTypeIfNotExists(final String eventType) {
		if (!eventTypes.containsKey(eventType)) {
			// debatable as to whether or not we want to set the id here...
			EventTypeBean tb = new EventTypeBean(eventTypes.size() + 1, eventType, eventType);
			eventTypes.put(eventType, tb);
		}
	}

	/**
	 * main entry function to read in the timeline data into the database
	 * 
	 * @param directoryPath
	 *            path to the timeline directory
	 * @throws SQLException
	 *             sql exception occuring when trying to load the data
	 * @throws IOException
	 *             unable to read the timeline from the disk
	 * @throws NoSuchKeyException
	 * @throws ParseException
	 *             unable to parse dates contained in timeline source files
	 */
	public void loadData() throws SQLException, IOException, DateParsingException, NoSuchKeyException {
		String directoryPath = getClass().getResource("/data/timeline").getPath();
		List<TimelineEventBean> events = readDataFromTimelineDirectory(directoryPath);

		// first parse - get all the different timeline and populate timeline
		// table  also add what's in the importance field in case it's not there already		
		cleanupInputFile(events);
		
		populateTimebandTable(events);
		populateSubTimebandTable(events);
		populateEventTypeTable(events);
		populateEventsTable(events);
		executeTimebandUnitUpdates();
	}

	/** 
	 * If the importance field or subtimeband field are empty, then set to null
	 * @param events
	 */
	private void cleanupInputFile(List<TimelineEventBean> events) {
		for(int ii = 0; ii < events.size(); ii++) {
			TimelineEventBean teb = events.get(ii);
			if(teb.getTimeline() != null && 
					(teb.getTimeline().equals("-") ||
					teb.getTimeline().equals(" "))) {
				events.remove(ii);
			
				//readjust ii, so that we're looking at the next event
				ii--;
			} else if(teb.getTimeline() != null && teb.getTimeline().length() == 0) {
				teb.setTimeline(null);
			} else if(teb.getImportance() != null && teb.getImportance().length() == 0) {
				teb.setImportance(null);
			}
		}
	}

	private void executeTimebandUnitUpdates() throws IOException, SQLException {
		executeFile("/sql/com/tyndalehouse/step/dataloader/sql/data/1.timeband_units.sql");
		executeFile("/sql/com/tyndalehouse/step/dataloader/sql/data/2.timeband_setup.sql");
		executeFile("/sql/com/tyndalehouse/step/dataloader/sql/data/3.sub_timeband_units.sql");
	}

	private void setDate(PreparedStatement ps, int index, PartialDate d) throws SQLException {
		if (d == null || d.getDate() == null) {
			ps.setNull(index, Types.BIGINT);
		} else {
			ps.setLong(index, d.getDate().getTimeInMillis());
		}
	}

	private void setString(PreparedStatement ps, int index, String value) throws SQLException {
		if (value == null || value.length() == 0) {
			ps.setNull(index, Types.VARCHAR);
		} else {
			ps.setString(index, value);
		}
	}

	/**
	 * sets a nullable integer
	 * @param ps prepared statement
	 * @param index, the index of the parameter
	 * @param value the value
	 * @throws SQLException
	 */
	private void setInt(PreparedStatement ps, int index, Integer value) throws SQLException {
		if (value == null) {
			ps.setNull(index, Types.INTEGER);
		} else {
			ps.setInt(index, value);
		}
	}

	// TODO: all foreign key constraint, indexes and checks
	// TODO: Rewrite to get rid of all the parameter indexes. It should be
	// feasible to give a bunch
	// of things to a function, and it add it to the right function.
	private void populateEventsTable(List<TimelineEventBean> events) throws SQLException, DateParsingException, NoSuchKeyException {
		PreparedStatement timelineInsert = c.prepareStatement(TimelineEventBean.getInsertStatement());
		PreparedStatement alternativeDates = c.prepareStatement(TimelineEventBean.getAlternativeDateStatement());
		PreparedStatement scriptureReferences = c.prepareStatement(TimelineEventBean.getScriptureReferenceStatement());
		RocketPassage rp = null;
		int currentId = 1;

		// set up the key factory once
		KeyFactory keyFactory = PassageKeyFactory.instance();

		try {
			for (TimelineEventBean event : events) {
				// System.out.println(event.getName() + " " + event.getID() +
				// " " + event.getTimeline());
				int timelineParameterIndex = 1;
				int alternativeDateParameterIndex = 1;

				PartialDate from = PartialDate.parseDate(event.getFrom(), '-');
				PartialDate to = PartialDate.parseDate(event.getTo(), '-');

				timelineInsert.setInt(timelineParameterIndex++, currentId);
				setString(timelineInsert, timelineParameterIndex++, event.getID()); // event_text_id
				setString(timelineInsert, timelineParameterIndex++, event.getName()); // name
				setDate(timelineInsert, timelineParameterIndex++, from); // from_date
				setDate(timelineInsert, timelineParameterIndex++, to); // to_date
				setString(timelineInsert, timelineParameterIndex++, "" + from.getPrecision().getShortCode()); // from_precision
				setString(timelineInsert, timelineParameterIndex++, "" + to.getPrecision().getShortCode()); // to_precision
				timelineInsert.setInt(timelineParameterIndex++, timebands.get(event.getTimebandId()).getTimelineId()); // timeband_id
				timelineInsert.setInt(timelineParameterIndex++, subTimebands.get(event.getTimeline()).getTimelineId()); // timeband_id
				setInt(timelineInsert, timelineParameterIndex++, event.getImportance() == null ? null : subTimebands.get(event.getImportance()).getTimelineId()); // importance_id
				setString(timelineInsert, timelineParameterIndex++, event.getCertainty()); // certainty
				setString(timelineInsert, timelineParameterIndex++, event.getFlags()); // flags
				timelineInsert.setInt(timelineParameterIndex++, eventTypes.get(event.getType()).getEventTypeId()); // event_type_id
				setString(timelineInsert, timelineParameterIndex++, event.getDescription()); // description
				setString(timelineInsert, timelineParameterIndex++, event.getSource()); // source
				setString(timelineInsert, timelineParameterIndex++, event.getNotes()); // notes

				// Now do the alternative dates bit
				PartialDate from2 = PartialDate.parseDate(event.getFrom2(), '-');
				PartialDate to2 = PartialDate.parseDate(event.getTo2(), '-');
				timelineInsert.addBatch();

				//TODO: bug - alternative dates aren't being pulled through at the moment, due to the condition clause
				//which is inverted.
				if (from2.getDate() == null && to2.getDate() == null) {
					// set event_id from previous timeline insert
					alternativeDates.setInt(alternativeDateParameterIndex++, currentId);
					setDate(alternativeDates, alternativeDateParameterIndex++, from2); // from_date
					setDate(alternativeDates, alternativeDateParameterIndex++, to2); // to_date
					setString(alternativeDates, alternativeDateParameterIndex++, "" + from2.getPrecision().getShortCode()); // from_precision
					setString(alternativeDates, alternativeDateParameterIndex++, "" + to2.getPrecision().getShortCode()); // to_precision
					alternativeDates.addBatch();
				}

				// now do the insert into scripture bit!
				if (event.getRefs() != null && event.getRefs().length() != 0) {
					try {
						rp = (RocketPassage) keyFactory.getKey(event.getRefs());
						for (int ii = 0; ii < rp.countRanges(RestrictionType.NONE); ii++) {
							VerseRange vr = rp.getRangeAt(ii, RestrictionType.NONE);
							Verse start = vr.getStart();
							Verse end = vr.getEnd();

							int startVerseId = start.getOrdinal();
							int endVerseId = end.getOrdinal();

							scriptureReferences.setInt(1, currentId);
							scriptureReferences.setInt(2, TargetTypeEnum.TIMELINE_EVENT.getId());
							scriptureReferences.setInt(3, startVerseId);
							scriptureReferences.setInt(4, endVerseId);
							scriptureReferences.addBatch();
						}
					} catch(NoSuchVerseException nsve) {
						//Nehemiah 6.20 might not exist for example...
						//TODO: proper logging
						System.err.println(event.getRefs() + " => " + nsve.getMessage());
						}
				}
				currentId++;
			}

			timelineInsert.executeBatch();
			alternativeDates.executeBatch();
			scriptureReferences.executeBatch();
		} catch (SQLException ex) {
			// TODO: get some proper logging framework in there...
			// output warnings here...
			System.err.println(ex.getMessage());
			ex.printStackTrace();

		}
	}

	/**
	 * populates the timeline table, ie. the different timelines
	 * 
	 * @param events
	 *            the list of all the events to be uploaded.
	 * @throws SQLException
	 *             error running the sql statement
	 */
	private void populateTimebandTable(final List<TimelineEventBean> events) throws SQLException {
		timebands = new HashMap<String, TimelineBean>();

		for (TimelineEventBean event : events) {
			addTimebandIfNotExists(event.getTimebandId());
		}

		for (TimelineBean timeline : timebands.values()) {
			executeSQLText(timeline.getInsertStatement());
		}
	}

	/**
	 * populates the sub timeband table in a similar way to above
	 * @param events
	 * @throws SQLException
	 */
	private void populateSubTimebandTable(final List<TimelineEventBean> events) throws SQLException {
		subTimebands = new HashMap<String, TimelineBean>();

		for (TimelineEventBean event : events) {
			addSubTimebandIfNotExists(event.getTimeline());
			addSubTimebandIfNotExists(event.getImportance());
		}

		for (TimelineBean timeline : subTimebands.values()) {
			executeSQLText(timeline.getSubTimebandInsertStatement());
			//TODO: in any case this is poor as not using prepared statement
		}
	}
	
	private void populateEventTypeTable(List<TimelineEventBean> events) throws SQLException {
		eventTypes = new HashMap<String, EventTypeBean>();

		for (TimelineEventBean event : events) {
			addEventTypeIfNotExists(event.getType());
		}

		for (EventTypeBean eventType : eventTypes.values()) {
			executeSQLText(eventType.getInsertStatement());
			//TODO: in any case this is poor as not using prepared statement
		}
	}
}