package industries.doublehelix.furrynotemanager.siteImporters;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.TreeMap;
import java.util.TreeSet;

import javax.swing.JOptionPane;
import javax.swing.SwingWorker;

import industries.doublehelix.furrynotemanager.DBTools;
import industries.doublehelix.furrynotemanager.Folder;
import industries.doublehelix.furrynotemanager.Note;
import industries.doublehelix.furrynotemanager.Site;
import industries.doublehelix.furrynotemanager.User;
import industries.doublehelix.furrynotemanager.gui.ImprovedProgressMonitor;

public class SiteImporter extends SwingWorker<Void, String> {
	public static Importable getImportableForSite(Site site) {
		/*Class<? extends SiteImporter> clazz = getImporterForSite(user.getSite());
		clazz.getDeclaredConstructor(User.class).newInstance(user);*/
		
		switch (site.getSiteId()) {
			case DBTools.SITE_ID_FURAFFINITY: return new FurAffinity();
		}
		return null;
	}
	
	public enum ActionType{
		/**
		 * synchronizes the local DB with the website, updating folder, getting all messages that are missing, deleting
		 * no longer used folders, NOT deleting local notes that aren't on the server
		 */
		SYNCHRONIZE,
		
		/**
		 * synchronizes the local DB with the website, updating folder, getting all messages that are missing, deleting
		 * no longer used folders, NOT deleting local notes that aren't on the server
		 */
		SYNCHRONIZEWITHDELETE,
		
		/**
		 * updates the local DB with the website, getting all messages that are newer than the newest local one.
		 * Does not change folder or download old missing notes. considerably faster than synchronize in many contexts
		 */
		UPDATE 
	}
	
	public static final String INDETERMINATE = "INDETERMINATE";
	
	private TreeSet<User> users;
	private ActionType actionType;
	private ImprovedProgressMonitor progress;
	private Importable currentlyRunningImportable = null;
	private boolean isCanceled = false;
	Runnable callback;
	
	/**
	 * Worker to cause specified action on all users given
	 * @param actionType the type - see ActionType Javadoc for details
	 * @param users all users this should be applied to
	 * @param progress the progress monitor
	 * @param callback the callback method/runnable
	 */
	public SiteImporter(ActionType actionType, TreeSet<User> users, ImprovedProgressMonitor progress, Runnable callback) {
		if (users == null || users.isEmpty()) throw new IllegalArgumentException("No user given");
		this.actionType = actionType;
		this.users = users;
		this.progress = progress;
		if (progress != null) {
			this.progress.addCancelListener(new ActionListener() {
				@Override
				public void actionPerformed(ActionEvent e) {
					isCanceled = true;
				}
			});
		}
		this.callback = callback;
	}
	
	/**
	 * Gets all notes (not committed to DB), containing all info that can be gotten
	 * from the note overview (may be different for each site implementing, probably contains all but text)
	 * @return a list of the note ids
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private TreeSet<Note> getAllBareboneNotes(User user, Importable importable) throws SQLException, IOException {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date(Long.MIN_VALUE));
		return importable.getAllBareboneNotesSince(calendar, user, this);
	}
	
	/**
	 * Returns all Ids of all notes for this user which are new (not yet in DB) since given calendar date
	 * @param calendar date to use as starting point
	 * @return the imported notes
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private TreeSet<Note> getAllNewBareboneNotesSince(Calendar calendar, User user, Importable importable) throws SQLException, IOException {
		TreeSet<Note> bareboneNotesOnSite = importable.getAllBareboneNotesSince(calendar, user, this);
		TreeSet<Note> newBareboneNotes = new TreeSet<>();
		if (bareboneNotesOnSite.isEmpty()) return newBareboneNotes;
		
		TreeMap<Integer, Note> bbNoteMap = new TreeMap<>();
		for (Note note : bareboneNotesOnSite) {
			bbNoteMap.put(note.getOriginalNoteId(), note);
		}
		
		TreeSet<Integer> newIds = getNoteIdsNotInDB(new ArrayList<Integer>(bbNoteMap.keySet()), user);
		for (Integer id : newIds) {
			newBareboneNotes.add(bbNoteMap.get(id));
		}
		return newBareboneNotes;
	};
	
	/**
	 * import all given notes from the site to the database
	 * @param bareboneNotes the barebone (unfinished) notes to import
	 * @return the imported notes
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private TreeSet<Note> importNotes(TreeSet<Note> bareboneNotes, User user, Importable importable) throws SQLException, IOException {
		TreeSet<Note> notes = importable.getNotes(bareboneNotes, user, this);
		for (Note note : notes) {
			if (isCanceled) return null;
			note.saveToDb();
		}
		return notes;
	}
	
	/**
	 * imports all notes from the site to the database. shorthand for importNotes(getAllNoteIds())
	 * takes considerably longer than just getAllNoteIds()
	 * @return the imported notes
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private TreeSet<Note> importAllNotes(User user, Importable importable) throws SQLException, IOException {
		return importNotes(getAllBareboneNotes(user, importable), user, importable);
	};
	
	/**
	 * Imports all (not yet in DB) notes to DB since given calendar date
	 * existing notes will be updated if necessary
	 * @param calendar date to use as starting point or null to get all
	 * @return the imported notes
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private TreeSet<Note> importAllNotesSince(Calendar calendar, User user, Importable importable) throws SQLException, IOException {
		return importNotes(getAllNewBareboneNotesSince(calendar, user, importable), user, importable);
	};
	
	/**
	 * Check every local note if it's still on the server (and in the same folder) and check if the server has new ones
	 * @param delete true to delete notes that are not on the server anymore
	 * @throws SQLException 
	 * @throws IOException 
	 */
	private void synchronizeSingleUser(boolean delete, User user, Importable importable) throws SQLException, IOException {
		ensureUserIsLoggedIn(user, importable);
		
		//setProgress(ProgressTypes.NAME, "Gathering available notes for " + user.getName() + " from " + user.getSite().getName());
		//setProgress(ProgressTypes.CUR_VALUE, "indeterminateProgressVale");
		
		TreeMap<Integer, Note> bbNoteMap = new TreeMap<>();
		for (Note note : getAllBareboneNotes(user, importable)) {
			bbNoteMap.put(note.getOriginalNoteId(), note);
		}
		
		//setProgress(ProgressTypes.NAME, "Note cleanup for " + user.getName() + " from " + user.getSite().getName());
		
		// first delete all notes locally which are no longer on the server
		if (delete) {
			if (bbNoteMap.isEmpty()) {
				try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
					s.executeUpdate("DELETE FROM NOTES WHERE USERID=" + user.getUserId());
				}
			} else {
				// a select with a huge "NOT IN" doesn't sit well with java, so a 
				// DELETE FROM NOTES WHERE USERID=100 AND ORIGINALNOTEID NOT IN (2861,237,28973...)
				// won't work
				// instead, we male a new temporary table, dump all IDs in it and use that instead
				Connection conn = DBTools.getInstance().getConnection();
				try (Statement s = conn.createStatement()) {
					s.execute("DECLARE GLOBAL TEMPORARY TABLE SESSION.NEWNOTES(NID int) ON COMMIT PRESERVE ROWS NOT LOGGED");
				}
				try (PreparedStatement ps = conn.prepareStatement("INSERT INTO SESSION.NEWNOTES VALUES(?)")) {
					for (int id : bbNoteMap.keySet()) {
						ps.setInt(1, id);
						ps.executeUpdate();
					}
				}
				try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
					s.executeUpdate("DELETE FROM NOTES WHERE USERID=" + user.getUserId() +
							"  AND ORIGINALNOTEID NOT IN (SELECT NID FROM SESSION.NEWNOTES)");
					s.execute("DROP TABLE SESSION.NEWNOTES");
				}
			}
		}
		
		if (isCanceled) return;
		
		if (bbNoteMap.isEmpty()) return;
		
		// get and move notes which moved to another folder and were not in a custom folder locally
		TreeSet<Note> sysFolderNotes = Note.getNotesByExtendedStatement("USERID=" + user.getUserId() + " AND FOLDERID IN (SELECT FOLDERID FROM FOLDERS WHERE SYSTEMFOLDER = TRUE)");
		for (Note note : sysFolderNotes) {
			if (isCanceled) return;
			if (!bbNoteMap.containsKey(note.getOriginalNoteId())) continue; // new note
			int newFolderId = bbNoteMap.get(note.getOriginalNoteId()).getFolder().getFolderId();
			if (note.getFolder().getFolderId() != newFolderId) {
				note.setFolder(Folder.getFolder(newFolderId));
				note.saveToDb();
			}
		}
		
		// get (and delete) folders that are now empty (and not system folders)
		TreeSet<Folder> folders = Folder.getFoldersByExtendedStatement("FOLDERID NOT IN (SELECT DISTINCT FOLDERID FROM NOTES) AND SYSTEMFOLDER != TRUE");
		for (Folder folder : folders) {
			if (isCanceled) return;
			folder.removeFromDB();
		}
		
		// finally, filter out the new notes and then import them
		TreeSet<Integer> newIds = getNoteIdsNotInDB(new ArrayList<Integer>(bbNoteMap.keySet()), user);
		TreeSet<Note> notesToImport = new TreeSet<>();
		for (Integer id : newIds) {
			notesToImport.add(bbNoteMap.get(id));
		}
		
		//setProgress(ProgressTypes.NAME, "Downloading new notes for " + user.getName() + " from " + user.getSite().getName());
		importNotes(notesToImport, user, importable);
		return;
	}
	
	private TreeSet<Integer> getNoteIdsNotInDB(List<Integer> noteIds, User user) throws SQLException {
		TreeSet<Integer> resultNotes = new TreeSet<>();
		
		// the db can't handle too long lists, so let's do it in packets of 50
		int notesSize = noteIds.size();
		if (notesSize > 50) {
			int startAt = 0;
			while(startAt < notesSize) {
				int endAt = startAt + 50;
				if (endAt > notesSize) endAt = notesSize; // end is exclusive
				List<Integer> notesSmaller = noteIds.subList(startAt, endAt);
				startAt = endAt;
				resultNotes.addAll(getNoteIdsNotInDB(notesSmaller, user));
			}
			return resultNotes;
		}
		
		// select a from (values (1),(2),(3)) as x(a) where a not in (select originalnoteid from notes where userid=100);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a FROM (VALUES ");
		for (int originalId : noteIds) {
			sql.append("(").append(originalId).append("),");
		}
		sql.deleteCharAt(sql.length() - 1); // save because min. 1 value must have been added to list
		sql.append(") AS x(a) WHERE a NOT IN (SELECT ORIGINALNOTEID FROM NOTES WHERE USERID=").append(user.getUserId()).append(")");
		
		//System.out.println(sql.toString());
		try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
			try (ResultSet rs = s.executeQuery(sql.toString())) {
				while (rs.next()) {
					resultNotes.add(rs.getInt(1));
			    }
			}
		}
		return resultNotes;
	}
	
	private void updateSingleUser(User user, Importable importable) throws SQLException, IOException {
		if (!ensureUserIsLoggedIn(user, importable)) {
			return; // captcha canceled or no login
		};
		if (isCanceled) return;
		
		// get newest note in db
		TreeSet<Note> singleSet = Note.getNotesByExtendedStatement("1=1 ORDER BY DATE DESC FETCH FIRST ROW ONLY");
		if (singleSet.isEmpty()) {
			importAllNotes(user, importable);
		} else {
			Calendar cal = (Calendar) singleSet.first().getDate().clone();
			cal.add(Calendar.DAY_OF_YEAR, -1); // remove a day just to account for time zone differences
			importAllNotesSince(cal, user, importable);
		}
	}
	
	private boolean ensureUserIsLoggedIn(User user, Importable importable) throws MalformedURLException, IOException {
		// only log out if the current user is not the one logged in
		String userName = importable.getLoggedInUser();
		if (isCanceled) return false;
		if (!user.getName().equalsIgnoreCase(userName)) {
			if (userName != null) {
				importable.logout();
			}
			if (!importable.login(user)) return false;
		}
		return true;
	}
	
	/**
	 * Sets the progress text to the progress monitor
	 * Set SiteImporter.INDETERMINATE to instead switch
	 * the progress monitor into indeterminate state (or call setProgressValue with a negative value)
	 * @param text
	 */
	public void setProgressText(String text) {
		//System.out.println("progressText: " + text);
		this.publish(text);
	}
	
	public void setProgressValue(int progress) {
		if (progress < 0) {
			this.publish(INDETERMINATE);
		} else {
			// yes, this is fucking ugly, but SwingWorker exits when you set a value outside 1-99
			int prog = (progress == 0) ? 1 : ((progress > 99) ? 99 : progress);
			//System.out.println("progress set to " + prog);
			setProgress(prog);
		}
	}

	@Override
	protected Void doInBackground() throws Exception {
		Connection conn = DBTools.getInstance().getConnection();
		conn.setAutoCommit(false);
		try {
			switch(actionType) {
				case SYNCHRONIZE:
					for (User u : users) {
						if (isCanceled) break;
						currentlyRunningImportable = getImportableForSite(u.getSite());
						this.synchronizeSingleUser(false, u, currentlyRunningImportable);
					}
					if (!isCanceled) conn.commit();
					break;
				case SYNCHRONIZEWITHDELETE:
					for (User u : users) {
						if (isCanceled) break;
						currentlyRunningImportable = getImportableForSite(u.getSite());
						this.synchronizeSingleUser(true, u, currentlyRunningImportable);
					}
					if (!isCanceled) conn.commit();
					break;
				case UPDATE:
					for (User u : users) {
						if (isCanceled) break;
						currentlyRunningImportable = getImportableForSite(u.getSite());
						this.updateSingleUser(u, currentlyRunningImportable);
					}
					if (!isCanceled) conn.commit();
					break;
				default:
					break;
			}
		} catch (Exception e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "An error occured, notes were not imported: " + e.toString(), "Error", JOptionPane.ERROR_MESSAGE);
		} finally {
			conn.rollback(); // rollback after a commit if we were successfull does not hurt
			conn.setAutoCommit(true);
		}
		return null;
	}
	
	@Override
	protected void process(List<String> chunks) {
		if (this.progress == null) return;
		String message = null;
		boolean indeterminate = false;
		
		// check if we got any indeterminate mode to set
		// also, newest message is the last one that is not INDETERMINATE
		// yes, this is fucking ugly, but SwingWorker exits when you set a value outside 0-100
		for (String chunk : chunks) {
			if (chunk.equals(INDETERMINATE)) {
				indeterminate = true;
			} else {
				message = chunk;
			}
		}
		
		if (indeterminate) this.progress.setIndeterminate(true);
		if (message != null) this.progress.setNote("<html><body>" + message + "</body></html>");
	}
	
	@Override
	protected void done() {
		if (this.progress != null) this.progress.close();
		System.out.println("done");
		if (this.callback != null) callback.run();
	}

	public boolean isCanceled() {
		return isCanceled;
	}
}
