/*
 * This file is part of NixNote/NeighborNote 
 * Copyright 2009 Randy Baumgarte
 * 
 * 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.util.ArrayList;
import java.util.List;

import cx.fbn.nevernote.sql.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
import cx.fbn.nevernote.utilities.Pair;

public class NoteTagsTable {
	private final ApplicationLogger 		logger;
	DatabaseConnection						db;
	NSqlQuery								getNoteTagsQuery;

	
	// Constructor
	public NoteTagsTable(ApplicationLogger l,DatabaseConnection d) {
		logger = l;
		db = d;
	}
	// Create the table
	public void createTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
        // Create the NoteTag table
        logger.log(logger.HIGH, "Creating table NoteTags...");
        if (!query.exec("Create table NoteTags (noteGuid varchar, " +
        		"tagGuid varchar, primary key(noteGuid, tagGuid))"))
        	logger.log(logger.HIGH, "Table NoteTags creation FAILED!!!"); 
	}
	// Drop the table
	public void dropTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("drop table NoteTags");
	}
	// Get a note tags by the note's Guid
	public List<String> getNoteTags(String noteGuid) {
		if (noteGuid == null)
			return null;
		List<String> tags = new ArrayList<String>();
		
		if (getNoteTagsQuery == null)
			prepareGetNoteTagsQuery();
		
		getNoteTagsQuery.bindValue(":guid", noteGuid);
		if (!getNoteTagsQuery.exec()) {
			logger.log(logger.EXTREME, "NoteTags SQL select has failed.");
			logger.log(logger.MEDIUM, getNoteTagsQuery.lastError());
			return null;
		}
		while (getNoteTagsQuery.next()) {
			tags.add(getNoteTagsQuery.valueString(0));
		}	
		return tags;
	}
	// Get a list of notes by the tag guid
	public List<String> getTagNotes(String tagGuid) {
		if (tagGuid == null)
			return null;
		List<String> notes = new ArrayList<String>();
		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.prepare("Select NoteGuid from NoteTags where tagGuid = :guid");
		
		query.bindValue(":guid", tagGuid);
		if (!query.exec()) {
			logger.log(logger.EXTREME, "getTagNotes SQL select has failed.");
			logger.log(logger.MEDIUM, query.lastError());
			return notes;
		}
		while (query.next()) {
			notes.add(query.valueString(0));
		}	
		return notes;
	}
	void prepareGetNoteTagsQuery() {
		getNoteTagsQuery = new NSqlQuery(db.getConnection());
		getNoteTagsQuery.prepare("Select TagGuid from NoteTags where noteGuid = :guid");
	}
	// Get a note tags by the note's Guid
	public List<NoteTagsRecord> getAllNoteTags() {
		List<NoteTagsRecord> tags = new ArrayList<NoteTagsRecord>();
		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		if (!query.exec("Select TagGuid, NoteGuid from NoteTags")) {
			logger.log(logger.EXTREME, "NoteTags SQL select has failed.");
			logger.log(logger.MEDIUM, query.lastError());
			return null;
		}
		while (query.next()) {
			NoteTagsRecord record = new NoteTagsRecord();
			record.tagGuid = query.valueString(0);
			record.noteGuid = query.valueString(1);
			tags.add(record);
		}	
		return tags;
	}
	// Check if a note has a specific tag already
	public boolean checkNoteNoteTags(String noteGuid, String tagGuid) {
		if (noteGuid == null || tagGuid == null)
			return false;
		boolean check;
		NSqlQuery query = new NSqlQuery(db.getConnection());
		check = query.prepare("Select "
				+"NoteGuid, TagGuid from NoteTags where noteGuid = :noteGuid and tagGuid = :tagGuid");
		if (!check)
			logger.log(logger.EXTREME, "checkNoteTags SQL prepare has failed.");
		
		query.bindValue(":noteGuid", noteGuid);
		query.bindValue(":tagGuid", tagGuid);
		query.exec();
		
		if (!check) {
			logger.log(logger.EXTREME, "checkNoteTags SQL select has failed.");
			logger.log(logger.MEDIUM, query.lastError());
			return false;
		}
		
		if (query.next()) {
			return true;
		}	
		return false;
	}
	// Save Note Tags
	public void saveNoteTag(String noteGuid, String tagGuid, boolean isDirty) {
		boolean check;
		NSqlQuery query = new NSqlQuery(db.getConnection());

		check = query.prepare("Insert Into NoteTags (noteGuid, tagGuid) "
				+"Values("
				+":noteGuid, :tagGuid)");
		if (!check)
			logger.log(logger.EXTREME, "Note SQL insert prepare has failed.");
	
		query.bindValue(":noteGuid", noteGuid);
		query.bindValue(":tagGuid", tagGuid);
						
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "NoteTags Table insert failed.");		
			logger.log(logger.MEDIUM, query.lastError());
		}
		check = query.prepare("Update Note set isDirty=:isDirty where guid=:guid");
		if (!check)
			logger.log(logger.EXTREME, "RNoteTagsTable.saveNoteTag prepare has failed.");
		query.bindValue(":isDirty", isDirty);
		query.bindValue(":guid", noteGuid);
		query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "RNoteTagsTable.saveNoteTag has failed to set note as dirty.");		
			logger.log(logger.MEDIUM, query.lastError());
		}
	}
	// Delete a note's tags
	public void deleteNoteTag(String noteGuid) {
		boolean check;
		NSqlQuery query = new NSqlQuery(db.getConnection());
		check = query.prepare("Delete from NoteTags where noteGuid = :noteGuid");
		if (!check)
			logger.log(logger.EXTREME, "Note SQL delete prepare has failed.");
	
		query.bindValue(":noteGuid", noteGuid);
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "NoteTags Table delete failed.");		
			logger.log(logger.MEDIUM, query.lastError());
		}

	}
	// Get a note tag counts
	public List<Pair<String,Integer>> getTagCounts() {
		List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		if (!query.exec("select tagguid, count(noteguid) from notetags group by tagguid;")) {
			logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");
			logger.log(logger.MEDIUM, query.lastError());
			return null;
		}
		while (query.next()) {
			Pair<String,Integer> newCount = new Pair<String,Integer>();
			newCount.setFirst(query.valueString(0));
			newCount.setSecond(query.valueInteger(1));
			counts.add(newCount);
		}	
		return counts;
	}
}
