/*
 * This file is part of NixNote/NeighborNote 
 * Copyright 2009 Randy Baumgarte
 * Copyright 2013 Yuki Takahashi
 * 
 * This file may be licensed under the terms of of the
 * GNU General Public License Version 2 (the ``GPL'').
 *
 * Software distributed under the License is distributed
 * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the GPL for the specific language
 * governing rights and limitations.
 *
 * You should have received a copy of the GPL along with this
 * program. If not, go to http://www.gnu.org/licenses/gpl.html
 * or write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
 *
*/


package cx.fbn.nevernote.sql;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.StringEscapeUtils;

import com.evernote.edam.type.Note;
import com.evernote.edam.type.NoteAttributes;
import com.evernote.edam.type.Resource;
import com.evernote.edam.type.Tag;
import com.trolltech.qt.core.QByteArray;
import com.trolltech.qt.core.QDateTime;
import com.trolltech.qt.core.QTextCodec;
import com.trolltech.qt.gui.QPixmap;

import cx.fbn.nevernote.Global;
import cx.fbn.nevernote.evernote.EnmlConverter;
import cx.fbn.nevernote.evernote.NoteMetadata;
import cx.fbn.nevernote.sql.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
import cx.fbn.nevernote.utilities.Pair;

public class NoteTable {
	private final ApplicationLogger 		logger;
	public final NoteTagsTable				noteTagsTable;
	public NoteResourceTable				noteResourceTable;
	private final DatabaseConnection		db;
	int id;

	// Prepared Queries to improve speed
	private NSqlQuery						getQueryWithContent;
	private NSqlQuery						getQueryWithoutContent;
	private NSqlQuery						getAllQueryWithoutContent;
	
	// Constructor
	public NoteTable(ApplicationLogger l, DatabaseConnection d) {
		logger = l;
		db = d;
		id = 0;
		noteResourceTable = new NoteResourceTable(logger, db);
		noteTagsTable = new NoteTagsTable(logger, db);
		getQueryWithContent = null;
		getQueryWithoutContent = null;
	}
	// Create the table
	public void createTable() {
		//getQueryWithContent = new NSqlQuery(db.getConnection());
		//getQueryWithoutContent = new NSqlQuery(db.getConnection());
		NSqlQuery query = new NSqlQuery(db.getConnection());
        logger.log(logger.HIGH, "Creating table Note...");
        if (!query.exec("Create table Note (guid varchar primary key, " +
        		"updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+
        		"contentLength integer, created timestamp, updated timestamp, deleted timestamp, " 
        		+"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+
        		"attributeLatitude double, attributeLongitude double, attributeAltitude double,"+
        		"attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+
        		"attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +
        		"isDirty boolean)"))       		
        	logger.log(logger.HIGH, "Table Note creation FAILED!!!");    
        if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))
        	logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");
        if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))
        	logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!");  
        noteTagsTable.createTable();
//        noteResourceTable.createTable();     
	}
	// Drop the table
	public void dropTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("Drop table Note");
		noteTagsTable.dropTable();
		noteResourceTable.dropTable();
	}
	// Save Note List from Evernote 
	public void addNote(Note n, boolean isDirty) {
		logger.log(logger.EXTREME, "Inside addNote");
		if (n == null)
			return;
		
		SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		NSqlQuery query = new NSqlQuery(db.getConnection());			
		query.prepare("Insert Into Note ("
				+"guid, updateSequenceNumber, title, content, "
				+"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "
				+"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
				+"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
				+"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded, contentText" 
				+") Values("
				+":guid, :updateSequenceNumber, :title, :content, "
				+":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "
				+":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "
				+":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "
				+":indexNeeded, :isExpunged, :isDirty, -1, true, :contentText) ");

		StringBuilder created = new StringBuilder(simple.format(n.getCreated()));			
		StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));			
		StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));

		
		
		query.bindValue(":guid", n.getGuid());
		query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());
		query.bindValue(":title", n.getTitle());
		if (isDirty) {
			EnmlConverter enml = new EnmlConverter(logger);
			String contentText = Global.extractPlainText(enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));

			query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));
			query.bindValue(":contentText", contentText);
		}
		else {
			String contentText = Global.extractPlainText(n.getContent());
			
			query.bindValue(":content", n.getContent());
			query.bindValue(":contentText", contentText);
			
		}
		query.bindValue(":contentHash", n.getContentHash());
		query.bindValue(":contentLength", n.getContentLength());
		query.bindValue(":created", created.toString());
		query.bindValue(":updated", updated.toString());
		query.bindValue(":deleted", deleted.toString());
		query.bindValue(":active", n.isActive());
		query.bindValue(":notebookGuid", n.getNotebookGuid());
		
		if (n.getAttributes() != null) {
			created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));
			query.bindValue(":attributeSubjectDate", created.toString());
			query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());
			query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());
			query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());
			query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());
			query.bindValue(":attributeSource", n.getAttributes().getSource());
			query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());
			query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());
		} else {
			created = new StringBuilder(simple.format(n.getCreated()));	
			query.bindValue(":attributeSubjectDate", created.toString());
			query.bindValue(":attributeLatitude", 0.0);
			query.bindValue(":attributeLongitude", 0.0);
			query.bindValue(":attributeAltitude", 0.0);
			query.bindValue(":attributeAuthor", "");
			query.bindValue(":attributeSource", "");
			query.bindValue(":attributeSourceUrl", "");
			query.bindValue(":attributeSourceApplication", "");
		}
		query.bindValue(":indexNeeded", true);
		query.bindValue(":isExpunged", false);
		query.bindValue(":isDirty", isDirty);

		
		if (!query.exec())
			logger.log(logger.MEDIUM, query.lastError());
		
		// Save the note tags
		if (n.getTagGuids() != null) {
			for (int i=0; i<n.getTagGuids().size(); i++) 
				noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i), isDirty);
		}
		logger.log(logger.EXTREME, "Leaving addNote");
	} 
	// Setup queries for get to save time later
	private void prepareQueries() {
		if (getQueryWithContent == null) {
			getQueryWithContent = new NSqlQuery(db.getConnection());
			if (!getQueryWithContent.prepare("Select "
					+"guid, updateSequenceNumber, title, "
					+"created, updated, deleted, active, notebookGuid, "
					+"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
					+"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
					+"attributeContentClass, "
					+"content, contentHash, contentLength"
					+" from Note where guid=:guid and isExpunged=false")) {
						logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");
						logger.log(logger.MEDIUM, getQueryWithContent.lastError());
			}
		}
		
		if (getQueryWithoutContent == null) {
			getQueryWithoutContent = new NSqlQuery(db.getConnection());
			if (!getQueryWithoutContent.prepare("Select "
					+"guid, updateSequenceNumber, title, "
					+"created, updated, deleted, active, notebookGuid, "
					+"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
					+"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
					+"attributeContentClass"
					+" from Note where guid=:guid and isExpunged=false")) {
						logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");
						logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());
			}
		}
			
		if (getAllQueryWithoutContent == null) {
			getAllQueryWithoutContent = new NSqlQuery(db.getConnection());
		
			if (!getAllQueryWithoutContent.prepare("Select "
				+"guid, updateSequenceNumber, title, "
				+"created, updated, deleted, active, notebookGuid, "
				+"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
				+"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
				+"attributeContentClass "
				+" from Note where isExpunged = false")) {
				logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");
					logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());
			}
		}
	}


	// Get a note's content in blob format for index.
	public String getNoteContentNoUTFConversion(String guid) {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select content from note where guid=:guid");
		query.bindValue(":guid", guid);
		query.exec();		
		query.next();
		return query.valueString(0);
	}
	// Get a note by Guid
	public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {

		// 自分のキーに変更
//		extractMetadata("otherKey:{values};kimaira792:{titleColor=fff;pinned=true;};finalKey:{values1);");
		if (noteGuid == null)
			return null;
		if (noteGuid.trim().equals(""))
			return null;

		prepareQueries();
		NSqlQuery query;
		if (loadContent) {
			query = getQueryWithContent;
		} else {
			query = getQueryWithoutContent;
		}
		
		query.bindValue(":guid", noteGuid);
		if (!query.exec()) {
			logger.log(logger.EXTREME, "Note SQL select exec has failed.");
			logger.log(logger.MEDIUM, query.lastError());
			return null;
		}
		if (!query.next()) {
			logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");
			logger.log(logger.EXTREME, " -> " +query.lastError().toString());
			logger.log(logger.EXTREME, " -> " +query.lastError());
			return null;
		}
		Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);
		n.setContent(fixCarriageReturn(n.getContent()));
		n.getAttributes().setContentClassIsSet(false);
		return n;
	}
	// Get a note by Guid
	public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {
		DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
//		indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");

		Note n = new Note();
		NoteAttributes na = new NoteAttributes();
		n.setAttributes(na);
		
		n.setGuid(query.valueString(0));
		n.setUpdateSequenceNum(new Integer(query.valueString(1)));
		n.setTitle(query.valueString(2));

		try {
			n.setCreated(indfm.parse(query.valueString(3)).getTime());
			n.setUpdated(indfm.parse(query.valueString(4)).getTime());
			n.setDeleted(indfm.parse(query.valueString(5)).getTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}

		n.setActive(query.valueBoolean(6,true));
		n.setNotebookGuid(query.valueString(7));
		
		try {
			String attributeSubjectDate = query.valueString(8);
			if (!attributeSubjectDate.equals(""))
				na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		na.setLatitude(new Float(query.valueString(9)));
		na.setLongitude(new Float(query.valueString(10)));
		na.setAltitude(new Float(query.valueString(11)));
		na.setAuthor(query.valueString(12));
		na.setSource(query.valueString(13));
		na.setSourceURL(query.valueString(14));
		na.setSourceApplication(query.valueString(15));
		na.setContentClass(query.valueString(16));
		
		if (loadTags) {
			List<String> tagGuids = noteTagsTable.getNoteTags(n.getGuid());
			List<String> tagNames = new ArrayList<String>();
			TagTable tagTable = db.getTagTable();
			for (int i=0; i<tagGuids.size(); i++) {
				String currentGuid = tagGuids.get(i);
				Tag tag = tagTable.getTag(currentGuid);
				if (tag.getName() != null)
					tagNames.add(tag.getName());
				else
					tagNames.add("");
			}

			n.setTagNames(tagNames);
			n.setTagGuids(tagGuids);		
		}
		
		if (loadContent) {
			QTextCodec codec = QTextCodec.codecForLocale();
			codec = QTextCodec.codecForName("UTF-8");
	        String unicode =  codec.fromUnicode(query.valueString(17)).toString();

	        // This is a hack.  Basically I need to convert HTML Entities to "normal" text, but if I
	        // convert the &lt; character to < it will mess up the XML parsing.  So, to get around this
	        // I am "bit stuffing" the &lt; to &&lt; so StringEscapeUtils doesn't unescape it.  After
	        // I'm done I convert it back.
	        StringBuffer buffer = new StringBuffer(unicode);
	        if (Global.enableHTMLEntitiesFix && unicode.indexOf("&#") > 0) {
	        	unicode = query.valueString(17);
	        	//System.out.println(unicode);
	        	//unicode = unicode.replace("&lt;", "&_lt;");
	        	//unicode = codec.fromUnicode(StringEscapeUtils.unescapeHtml(unicode)).toString();
	        	//unicode = unicode.replace("&_lt;", "&lt;");
	        	//System.out.println("************************");
	        	int j=1;
	        	for (int i=buffer.indexOf("&#"); i != -1 && buffer.indexOf("&#", i)>0; i=buffer.indexOf("&#",i+1)) {
	        		j = buffer.indexOf(";",i)+1;
	        		if (i<j) {
	        			String entity = buffer.substring(i,j).toString();
	        			int len = entity.length()-1;
	        			String tempEntity = entity.substring(2, len);
	        			try {
	        				Integer.parseInt(tempEntity);
	        				entity = codec.fromUnicode(StringEscapeUtils.unescapeHtml4(entity)).toString();
		        			buffer.delete(i, j);
		        			buffer.insert(i, entity);
	        			} catch (Exception e){ }
	        			
	        		}
	        	} 
	        } 
	        	
	        n.setContent(unicode);
//			n.setContent(query.valueString(16).toString());
			
			String contentHash = query.valueString(18);
			if (contentHash != null)
				n.setContentHash(contentHash.getBytes());
			n.setContentLength(new Integer(query.valueString(19)));
		}
		if (loadResources)
			n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));
		if (loadRecognition) {
			if (n.getResources() == null) {
				List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());
				n.setResources(resources);
			} else {
				// We need to merge the recognition resources with the note resources retrieved earlier
				for (int i=0; i<n.getResources().size(); i++) {
					Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());
					n.getResources().get(i).setRecognition(r.getRecognition());
				}
			}
		}
		n.setContent(fixCarriageReturn(n.getContent()));
		return n;
	}
	// Update a note's title
	public void updateNoteTitle(String guid, String title) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteTitle");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note title sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		query.bindValue(":title", title);
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note title has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteTitle");
	}
	// Update a note's creation date
	public void updateNoteCreatedDate(String guid, QDateTime date) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteCreatedDate");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		
		query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));
		query.bindValue(":guid", guid);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note creation date has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteCreatedDate");
	}
	// Update a note's creation date
	public void updateNoteAlteredDate(String guid, QDateTime date) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteAlteredDate");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		
		query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
		query.bindValue(":guid", guid);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note altered date has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteAlteredDate");
	}
	// Update a note's creation date
	public void updateNoteSubjectDate(String guid, QDateTime date) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteSubjectDate");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
	
		query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
		query.bindValue(":guid", guid);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note subject date date has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSubjectDate");
	}
	// Update a note's creation date
	public void updateNoteAuthor(String guid, String author) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteSubject");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}

		query.bindValue(":author", author);
		query.bindValue(":guid", guid);

		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note author has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSubject");
	}
	// Update a note's geo tags
	public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteGeoTags");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+
				"attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}

		query.bindValue(":longitude", lon);
		query.bindValue(":latitude", lat);
		query.bindValue(":altitude", alt);
		query.bindValue(":guid", guid);

		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note geo tag has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteGeoTags");
		
	}
	// Update a note's creation date
	public void updateNoteSourceUrl(String guid, String url) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteSourceUrl");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note url sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		
		query.bindValue(":url", url);
		query.bindValue(":guid", guid);

		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note url has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSourceUrl");
	}
	// Update the notebook that a note is assigned to
	public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteNotebook");
		String currentNotebookGuid = new String("");
		
		
		// If we are going from a synchronized notebook to a local notebook, we
		// need to tell Evernote to purge the note online.  However, if this is  
		// conflicting change we move it to the local notebook without deleting it 
		// or it would then delete the copy on the remote server.
		NotebookTable notebookTable = new NotebookTable(logger, db);
		DeletedTable deletedTable = new DeletedTable(logger, db);
		if (expungeFromRemote) {
			if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {
				deletedTable.addDeletedItem(guid, "NOTE");
			}
		}
		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		query.bindValue(":notebook", notebookGuid);
		query.bindValue(":guid", guid);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note notebook has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteNotebook");
	}
	// Update a note's title
	public void updateNoteContent(String guid, String content) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteContent");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set content=:content, contentText=:contentText, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true, " +
				" thumbnailneeded=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note content sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		
//		QTextCodec codec = QTextCodec.codecForLocale();
//		codec = QTextCodec.codecForName("UTF-8");
//		query.bindValue(":content", codec.fromUnicode(content).toString());
		query.bindValue(":content", content);
		query.bindValue(":contentText", Global.extractPlainText(content));
		query.bindValue(":guid", guid);

		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note content has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteContent");
	}

	// Delete a note
	public void deleteNote(String guid) {
		logger.log(logger.HIGH, "Entering NoteTable.deleteNote");
        NSqlQuery query = new NSqlQuery(db.getConnection());
        query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");
		query.bindValue(":guid", guid);
		if (!query.exec()) {
			logger.log(logger.MEDIUM, "Note delete failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.deleteNote");
	}
	public void restoreNote(String guid) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Update Note set deleted=:reset, active=true, isDirty=true where guid=:guid");
//		query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");
		query.bindValue(":guid", guid);
		query.bindValue(":reset", "1969-12-31 19:00:00");
		if (!query.exec()) {
			logger.log(logger.MEDIUM, "Note restore failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
	}
	// Purge a note (actually delete it instead of just marking it deleted)
	public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {
		logger.log(logger.HIGH, "Entering NoteTable.expungeNote");
		
		if (!permanentExpunge) {
			hideExpungedNote(guid, needsSync);
			return;
		}
		
		
        NSqlQuery note = new NSqlQuery(db.getConnection());
        NSqlQuery resources = new NSqlQuery(db.getResourceConnection());
        NSqlQuery tags = new NSqlQuery(db.getConnection());
        NSqlQuery words = new NSqlQuery(db.getIndexConnection());
        
       	note.prepare("Delete from Note where guid=:guid");
		resources.prepare("Delete from NoteResources where noteGuid=:guid");
		tags.prepare("Delete from NoteTags where noteGuid=:guid");
		words.prepare("Delete from words where guid=:guid");

		note.bindValue(":guid", guid);
		resources.bindValue(":guid", guid);
		tags.bindValue(":guid", guid);
		words.bindValue(":guid", guid);
	
		// Start purging notes.
		if (!note.exec()) {
			logger.log(logger.MEDIUM, "Purge from note failed.");
			logger.log(logger.MEDIUM, note.lastError());
		}
		if (!resources.exec()) {
				logger.log(logger.MEDIUM, "Purge from resources failed.");
			logger.log(logger.MEDIUM, resources.lastError());
		}
		if (!tags.exec()) {
			logger.log(logger.MEDIUM, "Note tags delete failed.");
			logger.log(logger.MEDIUM, tags.lastError());
		}

		if (!words.exec()) {
			logger.log(logger.MEDIUM, "Word delete failed.");
			logger.log(logger.MEDIUM, words.lastError());
		}
		if (needsSync) {
			DeletedTable deletedTable = new DeletedTable(logger, db);
			deletedTable.addDeletedItem(guid, "Note");
		}
		logger.log(logger.HIGH, "Leaving NoteTable.expungeNote");
	}
	// Purge a bunch of notes based upon the notebook
	public void expungeNotesByNotebook(String notebookGuid, boolean permanentExpunge, boolean needsSync) {
		List<String> notes = getNotesByNotebook(notebookGuid);
		for (int i=0; i<notes.size(); i++) {
			expungeNote(notes.get(i), permanentExpunge, needsSync);
		}
	}

	// Purge a note (actually delete it instead of just marking it deleted)
	public void hideExpungedNote(String guid, boolean needsSync) {
        NSqlQuery note = new NSqlQuery(db.getConnection());
        NSqlQuery resources = new NSqlQuery(db.getResourceConnection());
        NSqlQuery tags = new NSqlQuery(db.getConnection());
        NSqlQuery words = new NSqlQuery(db.getIndexConnection());
        
       	note.prepare("Update Note set isExpunged=true where guid=:guid");
		resources.prepare("Delete from NoteResources where noteGuid=:guid");
		tags.prepare("Delete from NoteTags where noteGuid=:guid");
//		words.prepare("Delete from words where guid=:guid");

		note.bindValue(":guid", guid);
		resources.bindValue(":guid", guid);
		tags.bindValue(":guid", guid);
		words.bindValue(":guid", guid);

		// Start purging notes.
		if (!note.exec()) {
			logger.log(logger.MEDIUM, "Purge from note failed.");
			logger.log(logger.MEDIUM, note.lastError());
		}
		if (!resources.exec()) {
				logger.log(logger.MEDIUM, "Purge from resources failed.");
			logger.log(logger.MEDIUM, resources.lastError());
		}
		if (!tags.exec()) {
			logger.log(logger.MEDIUM, "Note tags delete failed.");
			logger.log(logger.MEDIUM, tags.lastError());
		}
//		System.out.println("Hiding Note: Deleting words");
//		if (!words.exec()) {
//			logger.log(logger.MEDIUM, "Word delete failed.");
//			logger.log(logger.MEDIUM, words.lastError());
//		}
		if (needsSync) {
			DeletedTable deletedTable = new DeletedTable(logger, db);
			deletedTable.addDeletedItem(guid, "Note");
		}
	}

		
	// Purge all deleted notes;
	public void expungeAllDeletedNotes() {
		logger.log(logger.HIGH, "Entering NoteTable.expungeAllDeletedNotes");
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("select guid, updateSequenceNumber from note where active = false");
		List<String> guids = new ArrayList<String>();
		List<Integer> usns = new ArrayList<Integer>();
		while (query.next()) {
			guids.add(query.valueString(0));
			Integer usn = new Integer(query.valueString(1));
			usns.add(usn);
		}
		
		for (int i=0; i<guids.size(); i++) {
			Integer usn = usns.get(i);
			String guid = guids.get(i);
			if (usn == 0)
				expungeNote(guid, true, false);
			else
				expungeNote(guid, false, true);
		}
		logger.log(logger.HIGH, "Leaving NoteTable.expungeAllDeletedNotes");
	}
	// Update the note sequence number
	public void updateNoteSequence(String guid, int sequence) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteSequence");
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
		check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");

		query.bindValue(":sequence", sequence);
		query.bindValue(":guid", guid);
		
		query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "Note sequence update failed.");
			logger.log(logger.MEDIUM, query.lastError());
		} 
		logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSequence");
	}
	// Update the note Guid
	public void updateNoteGuid(String oldGuid, String newGuid) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNoteGuid");
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        NSqlQuery resQuery = new NSqlQuery(db.getResourceConnection());
        NSqlQuery wordQuery = new NSqlQuery(db.getIndexConnection());
		
		query.prepare("Update Note set guid=:newGuid, original_guid=:original_guid where guid=:oldGuid");

		query.bindValue(":original_guid", oldGuid);
		query.bindValue(":newGuid", newGuid);
		query.bindValue(":oldGuid", oldGuid);

		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "Note Guid update failed.");
			logger.log(logger.MEDIUM, query.lastError());
		} 
		
		query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");
		query.bindValue(":newGuid", newGuid);
		query.bindValue(":oldGuid", oldGuid);
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		
		wordQuery.prepare("Update words set guid=:newGuid where guid=:oldGuid");
		wordQuery.bindValue(":newGuid", newGuid);
		wordQuery.bindValue(":oldGuid", oldGuid);
		// IFIXED check = が抜けていたので修正
		check = wordQuery.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "Note guid update failed for Words.");
			logger.log(logger.MEDIUM, wordQuery.lastError());
		}
		resQuery.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");
		resQuery.bindValue(":newGuid", newGuid);
		resQuery.bindValue(":oldGuid", oldGuid);
		// IFIXED check = が抜けていたので修正
		check = resQuery.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");
			logger.log(logger.MEDIUM, resQuery.lastError());
		}
		
		// 操作履歴テーブルのguidを更新
		db.getHistoryTable().updateHistoryGuid(newGuid, oldGuid);
		
		// 除外ノートテーブルのguidを更新
		db.getExcludedTable().updateExcludedNoteGuid(newGuid, oldGuid);
		
		// スター付きノートテーブルのguidを更新
		db.getStaredTable().updateStaredNoteGuid(newGuid, oldGuid);
	}
	// Update a note
	public void updateNote(Note n) {
		logger.log(logger.HIGH, "Entering NoteTable.updateNote");
		NoteMetadata meta = getNoteMetaInformation(n.getGuid());
		String originalGuid = findAlternateGuid(n.getGuid());
		expungeNote(n.getGuid(), true, false);
		addNote(n, false);
		if (n!=null) {
			updateNoteMetadata(meta);
		}
		if (originalGuid != null) {
			updateNoteGuid(n.getGuid(), originalGuid);
			updateNoteGuid(originalGuid, n.getGuid());
		}
		logger.log(logger.HIGH, "Leaving NoteTable.updateNote");
	}
	// Does a note exist?
	public boolean exists(String guid) {
 		if (guid == null)
 			return false;
		if (guid.trim().equals(""))
			return false;
 		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select guid from note where guid=:guid");
		query.bindValue(":guid", guid);
		if (!query.exec())
			logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");
		boolean retVal = query.next();
		return retVal;
	}
	// Does a note exist?
	public boolean isNoteExpunged(String guid) {
 		if (guid == null)
 			return false;
		if (guid.trim().equals(""))
			return false;
 		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");
		query.bindValue(":guid", guid);
		if (!query.exec())
			logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");
		boolean retVal = query.next();
		return retVal;
	}
	// This is a convenience method to check if a note exists & update/create based upon it
	public void syncNote(Note note) {
		logger.log(logger.HIGH, "Entering NoteTable.syncNote");
		// If we got the note from Evernote we use its 
		// metadata instead of the local copy.
		NoteMetadata meta = null;
		if (note.getAttributes() != null && note.getAttributes().getSourceApplication() != null) {
			meta = extractMetadata(note.getAttributes().getSourceApplication());
		} else 
			meta = getNoteMetaInformation(note.getGuid());
		
		// Now, if the note exists we simply update it.  Otherwise we
		// add a new note.
		if (exists(note.getGuid())) {
			updateNote(note);
		}
		else
			addNote(note, false);
		
		// If we have metadata, we write it out.
		if (meta != null) {
			meta.setGuid(note.getGuid());
			updateNoteMetadata(meta);
		}
		logger.log(logger.HIGH, "Leaving NoteTable.syncNote");
	}
	// Get a list of notes that need to be updated
	public List <Note> getDirty() {
		String guid;
		Note tempNote;
		List<Note> notes = new ArrayList<Note>();
		List<String> index = new ArrayList<String>();
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
              				
		check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true or linked = true)");
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
		
		// Get a list of the notes
		while (query.next()) {
			guid = new String();
			guid = query.valueString(0);
			index.add(guid); 
		}	
		
		// Start getting notes
		for (int i=0; i<index.size(); i++) {
			tempNote = getNote(index.get(i), true,true,false,true,true);
			notes.add(tempNote);
		}
		logger.log(logger.LOW, "Dirty local notes: " +new Integer(notes.size()).toString());
		return notes;	
	}
	// Get a list of notes that need to be updated
	public List <Note> getDirtyLinkedNotes() {
		String guid;
		Note tempNote;
		List<Note> notes = new ArrayList<Note>();
		List<String> index = new ArrayList<String>();
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid in (select guid from notebook where linked = true)");
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
		
		// Get a list of the notes
		while (query.next()) {
			guid = new String();
			guid = query.valueString(0);
			index.add(guid); 
		}	
		
		// Start getting notes
		for (int i=0; i<index.size(); i++) {
			tempNote = getNote(index.get(i), true,true,false,true,true);
			notes.add(tempNote);
		}
		logger.log(logger.LOW, "Dirty linked local notes: " +new Integer(notes.size()).toString());
		return notes;	
	}
	// Get a list of notes that need to be updated
	public List <Note> getDirtyLinked(String notebookGuid) {
		String guid;
		Note tempNote;
		List<Note> notes = new ArrayList<Note>();
		List<String> index = new ArrayList<String>();
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		query.prepare("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid=:notebookGuid");
		query.bindValue(":notebookGuid", notebookGuid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL retrieve has failed getting dirty linked notes: " +query.lastError().toString());
		
		// Get a list of the notes
		while (query.next()) {
			guid = new String();
			guid = query.valueString(0);
			index.add(guid); 
		}	
		
		// Start getting notes
		for (int i=0; i<index.size(); i++) {
			tempNote = getNote(index.get(i), true,true,false,true,true);
			notes.add(tempNote);
		}
		logger.log(logger.LOW, "Dirty local notes for notebook " +notebookGuid +": " +new Integer(notes.size()).toString());
		return notes;	
	}
	// Get a list of notes that need to be updated
	// IFIXED バグで空のリストを返すだけのメソッドになっていたのを修正
	public List <String> getNotesByNotebook(String notebookGuid) {
		List<String> notes = new ArrayList<String>();
		// IFIXED List<String> index = new ArrayList<String>();
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select guid from Note where notebookguid=:notebookguid");
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
		query.bindValue(":notebookguid", notebookGuid);
		query. exec();
		
		// Get a list of the notes
		while (query.next()) {
			// IFIXED index.add(query.valueString(0));
			notes.add(query.valueString(0));
		}	
		
		return notes;	
	}
	// Get a list of notes that need to be updated
	public boolean isNoteDirty(String guid) {
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
		
		boolean returnValue;
		// Get a list of the notes
		if (query.next()) 
			returnValue = true; 
		else
			returnValue = false;

		return returnValue;	
	}

	// Reset the dirty bit
	public void  resetDirtyFlag(String guid) {
		logger.log(logger.LOW, "Resetting dirty flag for " +guid);
		NSqlQuery query = new NSqlQuery(db.getConnection());
		
		query.prepare("Update note set isdirty=false where guid=:guid");
		query.bindValue(":guid", guid);
		if (!query.exec())
			logger.log(logger.EXTREME, "Error resetting note dirty field.");
	}
	// Get all notes
	public List<String> getAllGuids() {
		List<String> notes = new ArrayList<String>();
		
		boolean check;					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select guid from Note");
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());

		// Get a list of the notes
		while (query.next()) {
			notes.add(new String(query.valueString(0))); 
		}
		return notes;
	}
	// Get all notes
	public List<Note> getAllNotes() {
		List<Note> notes = new ArrayList<Note>();
		prepareQueries();
		boolean check;	
		if (getAllQueryWithoutContent == null) 
			prepareQueries();
        NSqlQuery query = getAllQueryWithoutContent;
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());
		// Get a list of the notes
		while (query.next()) {
			notes.add(mapNoteFromQuery(query, false, false, false, false, true));
		}
		return notes;
	}
	// Count unindexed notes
	public int getUnindexedCount() {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("select count(*) from note where indexneeded=true and isExpunged = false");
		query.next(); 
		int returnValue = new Integer(query.valueString(0));
		return returnValue;
	}
	// Count unsynchronized notes
	public int getDirtyCount() {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("select count(guid) from note where isDirty=true and isExpunged = false");
		query.next(); 
		int returnValue = new Integer(query.valueString(0));
		logger.log(logger.LOW, "dirty count: " +returnValue);
		//query.exec("select count(guid) from note where isDirty=true and Active = 0 and isExpunged = false");
		//query.next(); 
		//logger.log(logger.LOW, "dirty count (active only): " +query.valueString(0));
		//query.exec("Select count(guid) from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true or linked = true)");
		//query.next(); 
		//logger.log(logger.LOW, "dirty count (no l&l notebooks): " +query.valueString(0));
		//logger.log(logger.LOW, "Beginning stack trace");
		//logger.log(logger.LOW, Thread.currentThread().getStackTrace());

		//logger.log(logger.LOW, "*************************");
		//logger.log(logger.LOW, "*** DIRTY RECORD DUMP ***");
		//logger.log(logger.LOW, "*************************");
		//List<Note> recs = getDirty();
		//for (int i=0; i<recs.size(); i++) {
			//Note n = getNote(recs.get(i).getGuid(), true, true, true, false, true);
			//logger.log(logger.LOW, "-- Begin Record ---");
			//logger.log(logger.LOW, "Guid: " +n.getGuid());
			//logger.log(logger.LOW, "Title: " +n.getTitle());
			//logger.log(logger.LOW, "Active: " +n.isActive());
			//logger.log(logger.LOW, "USN: " +n.getUpdateSequenceNum());
			//logger.log(logger.LOW, "Date Created: " +n.getCreated());
			//logger.log(logger.LOW, "Date Updated: " +n.getUpdated());
			//logger.log(logger.LOW, "Date Deleted: " +n.getDeleted());
			//logger.log(logger.LOW, "Resource Count: " +n.getResourcesSize());
			//for (int j=0; j<n.getResourcesSize(); j++) {
				//Resource r = n.getResources().get(j);
				//logger.log(logger.LOW, "Resource " +j +": " +r.getGuid());
				//logger.log(logger.LOW, "Active: " +r.isActive());
				//logger.log(logger.LOW, "USN: " +r.getUpdateSequenceNum());
			//}
			//logger.log(logger.LOW, "-- End Record ---");
		//}
		//logger.log(logger.LOW, "*****************************");
		//logger.log(logger.LOW, "*** End DIRTY RECORD DUMP ***");
		//logger.log(logger.LOW, "*****************************");
		return returnValue;
	}
	// Count notes
	public int getNoteCount() {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("select count(*) from note where isExpunged = false");
		query.next(); 
		int returnValue = new Integer(query.valueString(0));
		return returnValue;
	}
	// Count deleted notes
	public int getDeletedCount() {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("select count(*) from note where isExpunged = false and active = false");
		if (!query.next()) 
			return 0;
		int returnValue = new Integer(query.valueString(0));
		return returnValue;
	}
	// Reset a note sequence number to zero.  This is useful for moving conflicting notes
	public void resetNoteSequence(String guid) {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Update note sequence number has failed.");
			logger.log(logger.MEDIUM, query.lastError());
		}
	}
	
	
	// Update a note resource by the hash
	public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {
		NSqlQuery query = new NSqlQuery(db.getResourceConnection());
/*		query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");
		query.bindValue(":noteGuid", noteGuid);
		query.bindValue(":hex", hash);
		query.exec();
		if (!query.next()) {
			logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash.  GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);
			return;
		}
		String guid = query.valueString(0);
*/		
		query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");
		query.bindValue(":guid", resGuid);
		query.bindValue(":noteGuid", noteGuid);
		query.bindValue(":hex", hash);
		if (!query.exec()) {
			logger.log(logger.EXTREME, "Note Resource Update by Hash failed");
			logger.log(logger.EXTREME, query.lastError().toString());
		}
	}

	// Fix CRLF problem that is on some notes
	private String fixCarriageReturn(String note) {
		if (note == null || !Global.enableCarriageReturnFix)
			return note;
		QByteArray a0Hex = new QByteArray("a0");
		String a0 = QByteArray.fromHex(a0Hex).toString();
		note = note.replace("<div>"+a0+"</div>", "<div>&nbsp;</div>");
		return note.replace("<div/>", "<div>&nbsp;</div>");
	}
	
	// Expunge notes that we don't want to synchronize
	public List<String> expungeIgnoreSynchronizedNotes(List<String> notebooks, List<String>tags, List<String> linked) {
		
		List<String> noteGuids = new ArrayList<String>();
		for (int i=0; i<notebooks.size(); i++) {
			List<String> notes = findNotesByNotebook(notebooks.get(i));
			for (int j=0; j<notes.size(); j++) {
				if (!isNoteDirty(notes.get(j))) {
					expungeNote(notes.get(j), true, false);
					noteGuids.add(notes.get(j));
				}
			}
		}
		
		for (int i=0; i<tags.size(); i++) {
			List<String> notes = findNotesByTag(tags.get(i));
			for (int j=0; j<notes.size(); j++) {
				if (!isNoteDirty(notes.get(j))) {
					expungeNote(notes.get(j), true, false);
					noteGuids.add(notes.get(j));
				}
			}
		}
		
		for (int i=0; i<linked.size(); i++) {
			String notebookGuid = db.getLinkedNotebookTable().getNotebookGuid(linked.get(i));
			if (notebookGuid != null && !notebookGuid.trim().equals("")) {
				List<Tag> linkedTags = db.getTagTable().getTagsForNotebook(notebookGuid);
				for (int j=0; j<linkedTags.size(); j++)
					db.getTagTable().expungeTag(linkedTags.get(j).getGuid(), false);
				
				List<String> notes = findNotesByNotebook(notebookGuid);
				for (int j=0; j<notes.size(); j++) {
					if (!isNoteDirty(notes.get(j))) {
						expungeNote(notes.get(j), true, false);
						noteGuids.add(notes.get(j));
					}
				}
			}
		}
		return noteGuids;
	}
	
	// Find a note by its notebook
	// Expunge notes that we don't want to synchronize
	public List<String> findNotesByNotebook(String notebook) {
		List<String> values = new ArrayList<String>();
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select guid from note where notebookguid=:notebook");

		query.bindValue(":notebook", notebook);
		query.exec();
		while (query.next()) {
			values.add(query.valueString(0));
		}
		return values;
	}
	
	public List<String> findNotesByTag(String tag) {
		List<String> values = new ArrayList<String>();
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select distinct noteguid from notetags where tagguid=:tag");

		query.bindValue(":tag", tag);
		query.exec();
		while (query.next()) {
			values.add(query.valueString(0));
		}
		return values;
	}
	
	// Find a note based upon its title.
	public List<Pair<String,String>> findNotesByTitle(String text) {
		List<Pair<String,String>> results = new ArrayList<Pair<String,String>>();
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select guid,title from Note where lower(title) like :title");
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL prepare for search by title has failed: " +query.lastError().toString());
		
		query.bindValue(":title", "%"+text.toLowerCase()+"%");
		query.exec();
		// Get a list of the notes
		while (query.next()) {
			Pair<String,String> p = new Pair<String,String>();
			p.setFirst(query.valueString(0));
			p.setSecond(query.valueString(1));			
			results.add(p); 
		}	
		return results;
	}

	
	
	//********************************************************************************
	//********************************************************************************
	//* Indexing Functions
	//********************************************************************************
	//********************************************************************************
	// set/unset a note to be reindexed
	public void setIndexNeeded(String guid, Boolean flag) {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Update Note set indexNeeded=:flag where guid=:guid");

		if (flag)
			query.bindValue(":flag", 1);
		else
			query.bindValue(":flag", 0);
		query.bindValue(":guid", guid);
		if (!query.exec()) {
			logger.log(logger.MEDIUM, "Note indexNeeded update failed.");
			logger.log(logger.MEDIUM, query.lastError());
		} 
		List<Resource> r = noteResourceTable.getNoteResources(guid, false);
		for (int i=0; r!= null && i<r.size(); i++) {
			noteResourceTable.setIndexNeeded(r.get(i).getGuid(), true);
		}
	}
	// Set all notes to be reindexed
	public void reindexAllNotes() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		if (!query.exec("Update Note set indexNeeded=true")) {
			logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");
			logger.log(logger.MEDIUM, query.lastError());
		} 
	}

	// Get all unindexed notes
	public List <String> getUnindexed() {
		String guid;
		List<String> index = new ArrayList<String>();
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))
			logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");

		// Get a list of the notes
		while (query.next()) {
			guid = new String();
			guid = query.valueString(0);
			index.add(guid); 
		}	
		return index;	
	}
	public List<String> getNextUnindexed(int limit) {
		List<String> guids = new ArrayList<String>();
			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))
			logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
		
		// Get a list of the notes
		String guid;
		while (query.next()) {
			guid = new String();
			guid = query.valueString(0);
			guids.add(guid);
		}	
		return guids;	
	}

	
	// Get note meta information
	public void updateNoteMetadata(NoteMetadata meta) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		if (!query.prepare("Update Note set titleColor=:color, pinned=:pinned, attributeSourceApplication=:metaString, isDirty=true where guid=:guid"))
			logger.log(logger.EXTREME, "Note SQL prepare has failed on updateNoteMetadata.");
		query.bindValue(":color", meta.getColor());
		query.bindValue(":pinned", meta.isPinned());
		query.bindValue(":guid", meta.getGuid());
		query.bindValue(":metaString", buildMetadataString(meta));
		if (!query.exec()) 
			logger.log(logger.EXTREME, "Note SQL exec has failed on updateNoteMetadata.");
		return;
	}
	
	// Get all note meta information
	public HashMap<String, NoteMetadata> getNotesMetaInformation() {
		HashMap<String, NoteMetadata> returnValue = new HashMap<String, NoteMetadata>();
        NSqlQuery query = new NSqlQuery(db.getConnection());
		
		if (!query.exec("Select guid,titleColor, isDirty, pinned from Note"))
			logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation.");

		// Get a list of the notes
		while (query.next()) {
			NoteMetadata note = new NoteMetadata();
			note.setGuid(query.valueString(0));
			note.setColor(query.valueInteger(1));
			note.setDirty(query.valueBoolean(2, false));
			int pinned = query.valueInteger(3);
			if (pinned > 0) 
				note.setPinned(true);
			returnValue.put(note.getGuid(), note); 
		}	

		return returnValue;
	}
	// Get note meta information
	public NoteMetadata getNoteMetaInformation(String guid) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		
		if (!query.prepare("Select guid,titleColor, isDirty, pinned from Note where guid=:guid")) {
			logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation.");
			return null;
		}
		query.bindValue(":guid", guid);
		query.exec();

		// Get a list of the notes
		while (query.next()) {
			NoteMetadata note = new NoteMetadata();
			note.setGuid(query.valueString(0));
			note.setColor(query.valueInteger(1));
			note.setDirty(query.valueBoolean(2, false));
			int pinned = query.valueInteger(3);
			if (pinned > 0) 
				note.setPinned(true);
			return note;
		}	

		return null;
	}
	
	
	//**********************************************************************************
	//* Thumbnail functions
	//**********************************************************************************
	// Set if a new thumbnail is needed
	public void setThumbnailNeeded(String guid, boolean needed) {
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");
		query.bindValue(":guid", guid);
		query.bindValue(":needed", needed);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());

	}
	// Is a thumbail needed for this guid?
	public boolean isThumbnailNeeded(String guid) {
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("select thumbnailneeded from note where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());
		
		boolean returnValue;
		// Get a list of the notes
		if (query.next()) 
			returnValue = query.valueBoolean(0, false); 
		else
			returnValue = false;

		return returnValue;	
	}
	// Set if a new thumbnail is needed
	public void setThumbnail(String guid, QByteArray thumbnail) {
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");
		query.bindValue(":guid", guid);
		query.bindValue(":thumbnail", thumbnail.toByteArray());
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());

	}
	// Set if a new thumbnail is needed
	public QByteArray getThumbnail(String guid) {
		
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select thumbnail from note where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());
		// Get a list of the notes
		if (query.next())  {
			try {
				if (query.getBlob(0) != null) {
					return new QByteArray(query.getBlob(0)); 
				}
			} catch (java.lang.IllegalArgumentException e) {
				return null;
			}
		}
		return null;
	}
	// Get all thumbnails
	public HashMap<String, QPixmap> getThumbnails() {
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        HashMap<String, QPixmap> map = new HashMap<String,QPixmap>();
        				
		check = query.prepare("Select guid,thumbnail from note where thumbnailneeded=false and isExpunged=false");
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());
		// Get a list of the notes
		while (query.next())  {
			try {
				if (query.getBlob(1) != null) {
					QByteArray data = new QByteArray(query.getBlob(1));
					QPixmap img = new QPixmap();
					if (img.loadFromData(data)) {
						img = img.scaled(Global.largeThumbnailSize);
						map.put(query.valueString(0), img);
					}
				}	
			} catch (java.lang.IllegalArgumentException e) {
				logger.log(logger.HIGH, "Error retrieving thumbnail " +e.getMessage());
			}
		}
		return map;
	}
	// Get a list of notes that need thumbnails
	public List<String> findThumbnailsNeeded() {
		
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("select guid from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 5");
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL findThumbnailsNeeded query failed: " +query.lastError().toString());
		

		// Get a list of the notes
		List<String> values = new ArrayList<String>();
		while (query.next()) {
			values.add(query.valueString(0)); 
		}

		return values;	
	}
	// Get a count of thumbnails needed
	public int getThumbnailNeededCount() {
		
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("select count(guid) from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 2");
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL findThumbnailNeededCount query failed: " +query.lastError().toString());
		
		if (query.next()) {
			return query.valueInteger(0); 
		}

		return 0;	
	}

	//***********************************************************************************
	public String findAlternateGuid(String guid) {
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("select guid from note where original_guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL findAlternateguid query failed: " +query.lastError().toString());
		
		if (query.next()) {
			return query.valueString(0); 
		}

		return null;	
	}
	
	//* Check if a note guid exists
	public boolean guidExists(String guid) {
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("select guid from note where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) 
			logger.log(logger.EXTREME, "Note SQL guidExists query failed: " +query.lastError().toString());
		
		if (query.next()) {
			return true; 
		}

		return false;			
	}
	
	// Update a note content's hash.  This happens if a resource is edited outside of NN
	public void updateResourceContentHash(String guid, String oldHash, String newHash) {
		Note n = getNote(guid, true, false, false, false,false);
		int position = n.getContent().indexOf("<en-media");
		int endPos;
		for (;position>-1;) {
			endPos = n.getContent().indexOf(">", position+1);
			String oldSegment = n.getContent().substring(position,endPos);
			int hashPos = oldSegment.indexOf("hash=\"");
			int hashEnd = oldSegment.indexOf("\"", hashPos+7);
			String hash = oldSegment.substring(hashPos+6, hashEnd);
			if (hash.equalsIgnoreCase(oldHash)) {
				String newSegment = oldSegment.replace(oldHash, newHash);
				String content = n.getContent().substring(0,position) +
				                 newSegment +
				                 n.getContent().substring(endPos);
				NSqlQuery query = new NSqlQuery(db.getConnection());
				query.prepare("update note set isdirty=true, thumbnailneeded=true, content=:content, contentText=:contentText where guid=:guid");
				query.bindValue(":content", content);
				query.bindValue(":contentText", Global.extractPlainText(content));
				query.bindValue(":guid", n.getGuid());
				query.exec();
			}
			
			position = n.getContent().indexOf("<en-media", position+1);
		}
	}

	// Extract metadata from a note's Note.attributes.sourceApplication
	private NoteMetadata extractMetadata(String sourceApplication) {
		// 自分のキーに変更
		String consumerKey = "kimaira792:{";
		
		int startPos = sourceApplication.indexOf(consumerKey);
		if (startPos < 0 )
				return null;
		
		NoteMetadata meta = new NoteMetadata();
		startPos = startPos+consumerKey.length();
		
//		String startString = sourceApplication.substring(0,startPos);
		String metaString = sourceApplication.substring(startPos);
//		String endString = metaString.substring(metaString.indexOf("};"));
		int endPos = metaString.indexOf("};");
		if (endPos > 0)
			metaString = metaString.substring(0,endPos);
		
		String value = parseMetaString(metaString, "titleColor");
		if (value != null)
			meta.setColor(Integer.parseInt(value));
		
		value = parseMetaString(metaString, "pinned");
		if (value != null && value.equals(true))
			meta.setPinned(true);
				
		return meta;
	}
	
	// Given a metadata string from attribute.sourceApplication, we
	// extract the information for a given key.
	private String parseMetaString(String metaString, String key) {
		int startPos = metaString.indexOf(key);
		if (startPos < 0)
			return null;
		
		String value = metaString.substring(startPos+key.length()+1);
		int endPos = value.indexOf(";");
		if (endPos > 0)
			value = value.substring(0,endPos);
		
		return value;
	}
	
	// Given a set of metadata, we build a string that can be inserted
	// into the attribute.sourceApplication string.
	private String buildMetadataString(NoteMetadata meta) {
		StringBuffer value = new StringBuffer(removeExistingMetaString(meta.getGuid()));
		StringBuffer metaString = new StringBuffer();
		
		if (meta.isPinned()) {
			metaString.append("pinned=true;");
		}
		if (meta.getColor() != -1) {
			metaString.append("titleColor=" +new Integer(meta.getColor()).toString()+";");
		}
		if (metaString.length()>0) {
			
			// Adda any missing ";" or " " at the end of the existing 
			// string.
			if (value.length()>1 && (!value.toString().trim().endsWith(";") || !value.toString().trim().endsWith(";")))   
				value.append("; ");
			
			// 自分のキーに変更
			value.append("kimaira792:{");
			value.append(metaString);
			value.append("};");
			return value.toString();
		}
		return null;
	}

	// This will remove the existing metadata string from the attribute.sourceApplication string.
	private String removeExistingMetaString(String guid) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
		
		if (!query.prepare("Select attributeSourceApplication from Note where guid=:guid")) {
			logger.log(logger.EXTREME, "Note SQL retrieve has failed in removeExistingMetaString.");
			return null;
		}
		query.bindValue(":guid", guid);
		query.exec();

		// Get the application source string
		String sourceApplication = null;
		while (query.next()) {
			sourceApplication = query.valueString(0);
		}
		if (sourceApplication == null) 
			return "";
		
		// 自分のキーに変更
		String consumerKey = "kimaira792:{";
		int startPos = sourceApplication.indexOf(consumerKey);
		if (startPos < 0 )
				return sourceApplication;
		String startString = sourceApplication.substring(0,startPos);
		String metaString = sourceApplication.substring(startPos);
		String endString = metaString.substring(metaString.indexOf("};")+2);

		return startString+endString;
	}

	public void dumpDirtyNotes() {
		logger.log(logger.LOW, "Dirty Notes: ");
		List<Note>  noteList = this.getDirty();
		for (int i=0; i<noteList.size();i++) {
			logger.log(logger.LOW, i +" : " +noteList.get(i).getGuid() + " : " +noteList.get(i).getTitle() );
		}
	}
	
	// guidからノートのタイトルをゲット
	public String getNoteTitle(String noteGuid) {

		if (noteGuid == null)
			return null;
		if (noteGuid.trim().equals(""))
			return null;

		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select title from Note where guid=:guid and isExpunged=false");
		query.bindValue(":guid", noteGuid);
		if (!query.exec()) {
			logger.log(logger.MEDIUM, "Noteテーブルからタイトルの取得失敗");
			logger.log(logger.MEDIUM, query.lastError());
			return null;
		}
		if (!query.next()) {
			logger.log(logger.EXTREME, "SQL Retrieve failed for note guid "
					+ noteGuid + " in getNoteTitle()");
			logger.log(logger.EXTREME, " -> " + query.lastError().toString());
			logger.log(logger.EXTREME, " -> " + query.lastError());
			return null;
		}

		String noteTitle = query.valueString(0);

		return noteTitle;
	}
	
	// ノートコンテンツのプレーンテキストを取得
	public String getNoteContentText(String noteGuid) {

		if (noteGuid == null)
			return null;
		if (noteGuid.trim().equals(""))
			return null;

		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select contentText from Note where guid=:guid");
		query.bindValue(":guid", noteGuid);
		if (!query.exec()) {
			logger.log(logger.MEDIUM, "Noteテーブルからプレーンコンテンツの取得失敗");
			logger.log(logger.MEDIUM, query.lastError());
			return null;
		}
		if (!query.next()) {
			logger.log(logger.EXTREME, "SQL Retrieve failed for note guid "
					+ noteGuid + " in getNotePlainContent()");
			logger.log(logger.EXTREME, " -> " + query.lastError().toString());
			logger.log(logger.EXTREME, " -> " + query.lastError());
			return null;
		}

		String noteContentText = query.valueString(0);

		return noteContentText;
	}
	
	// Evernoteサムネイルを取得
	public QByteArray getENThumbnail(String guid) {
		boolean check;			
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select enThumbnail from note where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Note SQL get enThumbail failed: " +query.lastError().toString());
		}
		
		if (query.next())  {
			try {
				if (query.getBlob(0) != null) {
					return new QByteArray(query.getBlob(0)); 
				}
			} catch (java.lang.IllegalArgumentException e) {
				return null;
			}
		}
		return null;
	}
	
	// Evernoteサムネイルを登録
	public void setENThumbnail(String guid, QByteArray thumbnail) {
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Update note set enThumbnail = :enThumbnail where guid=:guid");
		query.bindValue(":guid", guid);
		query.bindValue(":enThumbnail", thumbnail.toByteArray());
		check = query.exec();
		if (!check) {
			logger.log(logger.EXTREME, "Note SQL set enThumbail failed: " +query.lastError().toString());
		}
	}
}	





