/*
 * 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 com.evernote.edam.type.LinkedNotebook;

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

public class LinkedNotebookTable {
	
	private final ApplicationLogger 		logger;
	DatabaseConnection							db;
	
	// Constructor
	public LinkedNotebookTable(ApplicationLogger l, DatabaseConnection d) {
		logger = l;
		db = d;
	}
	// Create the table
	public void createTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
        logger.log(logger.HIGH, "Creating table LinkedNotebook...");
        if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +
        		"shareName VarChar, " +
        		"username VarChar, "+
        		"shardID VarChar, " +
        		"shareKey VarChar, " +
        		"uri VarChar, " +
        		"updateSequenceNumber Long," +
        		"lastSequenceNumber Integer," +
        		"lastSequenceDate Long," +
        		"notebookGuid VarChar," +
        		"isDirty boolean)"))	        		
        	logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   
	}
	// Drop the table
	public void dropTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("Drop table LinkedNotebook");
	}
	// Save an individual notebook
	public void addNotebook(LinkedNotebook tempNotebook,  boolean isDirty) {
		boolean check;
		
        NSqlQuery query = new NSqlQuery(db.getConnection());
		check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username,  "
				+"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "
				+ "lastSequenceDate, notebookGuid) "   
				+ " Values("
				+":guid, :shareName, :username, "
				+":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");
		query.bindValue(":guid", tempNotebook.getGuid());
		query.bindValue(":shareName", tempNotebook.getShareName());
		query.bindValue(":username", tempNotebook.getUsername());
		query.bindValue(":shardId", tempNotebook.getShardId());
		query.bindValue(":shareKey", tempNotebook.getShareKey());
		query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());
		query.bindValue(":uri", tempNotebook.getUri());
		query.bindValue(":notebookGuid", "");
		
		if (isDirty)
			query.bindValue(":isDirty", true);
		else
			query.bindValue(":isDirty", false);

		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
			return;
		}
	}
	// Delete the notebook based on a guid
	public void expungeNotebook(String id, boolean needsSync) {
		boolean check;
		
		// First, delete any tags associated with this notebook
		String notebookGuid = getNotebookGuid(id);
		db.getNotebookTable().deleteLinkedTags(notebookGuid);
		
		// Now, delete any notes associated with this notebook
		List<String> notes = db.getNoteTable().getNotesByNotebook(notebookGuid);
		for (int i=0; i<notes.size(); i++) {
			db.getNoteTable().expungeNote(notes.get(i), true, needsSync);
		}
		
		// Delete the notebook record
		db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);
		
		// Finally, delete the linked notebook object itself
        NSqlQuery query = new NSqlQuery(db.getConnection());
       	check = query.prepare("delete from LinkedNotebook "
   				+"where guid=:guid");
		if (!check) {
			logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");
			logger.log(logger.EXTREME, query.lastError().toString());
		}
		query.bindValue(":guid", id);
		check = query.exec();
		if (!check) 
			logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");
		
		if  (needsSync) {
			DeletedTable deletedTable = new DeletedTable(logger, db);
			deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");
		}
	}
	// Check if a notebook exists
	public boolean exists(String id) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
       	boolean check = query.prepare("Select guid from linkednotebook where guid=:guid");
       	query.bindValue(":guid", id);
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
		}
		if (query.next())
			return true;
		return false;
	}
	// Update a notebook
	public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {
		boolean check;
		if (!exists(tempNotebook.getGuid())) {
			addNotebook(tempNotebook, isDirty);
			return;
		}
		
        NSqlQuery query = new NSqlQuery(db.getConnection());
       	check = query.prepare("Update LinkedNotebook set guid=:guid, shareName=:shareName, " +
       			"username=:username, shardID=:shardID, uri=:uri, updateSequenceNumber=:usn, isDirty=:isDirty "+
       			"where guid=:keyGuid");
		query.bindValue(":guid", tempNotebook.getGuid());
		query.bindValue(":keyGuid", tempNotebook.getGuid());
		query.bindValue(":shareName", tempNotebook.getShareName());
		query.bindValue(":username", tempNotebook.getUsername());
		query.bindValue(":shardID", tempNotebook.getShardId());
		query.bindValue(":shareKey", tempNotebook.getShareKey());
		query.bindValue(":uri", tempNotebook.getUri());
		query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());

		query.bindValue(":isDirty", isDirty);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "LinkedNotebook Table update failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
		}
	}
	// Load notebooks from the database
	public List<LinkedNotebook> getAll() {
		LinkedNotebook tempNotebook;
		List<LinkedNotebook> index = new ArrayList<LinkedNotebook>();
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +
				" from LinkedNotebook");
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
		while (query.next()) {
			tempNotebook = new LinkedNotebook();
			tempNotebook.setGuid(query.valueString(0));
			tempNotebook.setShareName(query.valueString(1));
			tempNotebook.setUsername(query.valueString(2));
			tempNotebook.setShardId(query.valueString(3));
			tempNotebook.setShareKey(query.valueString(4));
			tempNotebook.setUri(query.valueString(5));

			index.add(tempNotebook); 
		}	
		return index;
	}			
	// Load notebooks from the database
	public LinkedNotebook getNotebook(String guid) {
		LinkedNotebook tempNotebook;
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +
				" from LinkedNotebook where guid=:guid");
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");
		query.bindValue(":guid", guid);
		query.exec();
		while (query.next()) {
			tempNotebook = new LinkedNotebook();
			tempNotebook.setGuid(query.valueString(0));
			tempNotebook.setShareName(query.valueString(1));
			tempNotebook.setUsername(query.valueString(2));
			tempNotebook.setShardId(query.valueString(3));
			tempNotebook.setShareKey(query.valueString(4));
			tempNotebook.setUri(query.valueString(5));
			return tempNotebook;
		}	
		return null;
	}	
	// Load notebooks from the database
	public LinkedNotebook getByNotebookGuid(String guid) {
		LinkedNotebook tempNotebook;
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +
				" from LinkedNotebook where notebookguid=:guid");
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");
		query.bindValue(":guid", guid);
		query.exec();
		while (query.next()) {
			tempNotebook = new LinkedNotebook();
			tempNotebook.setGuid(query.valueString(0));
			tempNotebook.setShareName(query.valueString(1));
			tempNotebook.setUsername(query.valueString(2));
			tempNotebook.setShardId(query.valueString(3));
			tempNotebook.setShareKey(query.valueString(4));
			tempNotebook.setUri(query.valueString(5));
			return tempNotebook;
		}	
		return null;
	}
	// Get last sequence date for the notebook
	public long getLastSequenceDate(String guid) {
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select LastSequenceDate " 
				+"from LinkedNotebook where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed.");
		if (query.next()) {
			return query.valueLong(0);
		}	
		return 0;
	}			
	// Get a guid by uri
	public String getNotebookGuid(String guid) {
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select notebookGuid " 
				+"from LinkedNotebook where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed.");
		if (query.next()) {
			return query.valueString(0);
		}	
		return null;
	}	
	// get last sequence numeber
	public int getLastSequenceNumber(String guid) {
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select LastSequenceNumber " 
				+"from LinkedNotebook where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
		while (query.next()) {
			return query.valueInteger(0);
		}	
		return 0;
	}		
	
	// get the "true" notebook guid and not the shared notebook guid
	public String getLocalNotebookGuid(String guid) {
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select notebookGuid " 
				+"from LinkedNotebook where guid=:guid");
		query.bindValue(":guid", guid);
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
		while (query.next()) {
			return query.valueString(0);
		}	
		return null;
	}

	// does a record exist?
	public String findNotebookByShareName(String name) {
 		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		
		query.prepare("Select guid from Linkednotebook where shareName=:name");
		query.bindValue(":name", name);
		if (!query.exec())
			logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
		String val = null;
		if (query.next())
			val = query.valueString(0);
		return val;
	}

	// does a record exist?
	public String setNotebookGuid(String shareKey, String notebookGuid) {
 		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		
		query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey");
		query.bindValue(":notebookGuid", notebookGuid);
		query.bindValue(":shareKey", shareKey);
		if (!query.exec())
			logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
		String val = null;
		if (query.next())
			val = query.valueString(0);
		return val;
	}
	// set sync date
	public String setLastSequenceDate(String guid, long date) {
 		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		
		query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid");
		query.bindValue(":date", date);
		query.bindValue(":guid", guid);
		if (!query.exec())
			logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
		String val = null;
		if (query.next())
			val = query.valueString(0);
		return val;
	}
	// set sync number
	public String setLastSequenceNumber(String guid, int number) {
 		
		NSqlQuery query = new NSqlQuery(db.getConnection());
		
		query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid");
		query.bindValue(":number", number);
		query.bindValue(":guid", guid);
		if (!query.exec())
			logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
		String val = null;
		if (query.next())
			val = query.valueString(0);
		return val;
	}
	
	// Get a list of linked notebooks that need to be updated
	public List<String> getDirtyGuids() {
		List<String> index = new ArrayList<String>();
		boolean check;	
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select guid from LinkedNotebook where isDirty = true");
		if (!check) 
			logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds.");
		while (query.next()) {
			index.add(query.valueString(0));
		}	
		return index;	
	}

}

