/*
 * 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.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.evernote.edam.type.SharedNotebook;

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

public class SharedNotebookTable {
	
	private final ApplicationLogger 		logger;
	DatabaseConnection							db;
	
	// Constructor
	public SharedNotebookTable(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 SharedNotebook...");
        if (!query.exec("Create table SharedNotebook (id long primary key, " +
        		"userid Integer, " +
        		"notebookGuid VarChar, "+
        		"email VarChar, "+
        		"notebookModifiable boolean, " +
        		"requireLogin boolean, "+
        		"serviceCreated timestamp, "+
        		"shareKey VarChar,  "+
        		"username VarChar, "+
        		"icon blob, " +
        		"isDirty boolean)"))	        		
        	logger.log(logger.HIGH, "Table SharedNotebook creation FAILED!!!");   
	}
	// Drop the table
	public void dropTable() {
		NSqlQuery query = new NSqlQuery(db.getConnection());
		query.exec("Drop table SharedNotebook");
	}
	// Save an individual notebook
	public void addNotebook(SharedNotebook tempNotebook, boolean isDirty) {
		boolean check;
		
		SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        NSqlQuery query = new NSqlQuery(db.getConnection());
		check = query.prepare("Insert Into SharedNotebook (id, userid, notebookGuid, email,  "
				+"notebookModifiable, requireLogin, serviceCreated, shareKey, username, isDirty) "   
				+ " Values("
				+":id, :userid, :notebookGuid, :email, "
				+":notebookModifiable, :requireLogin, :serviceCreated, "
				+":shareKey, :username, :isDirty)");
		query.bindValue(":id", tempNotebook.getId());
		query.bindValue(":userid", tempNotebook.getUserId());
		query.bindValue(":notebookGuid", tempNotebook.getNotebookGuid());
		query.bindValue(":email", tempNotebook.getEmail());
		query.bindValue(":notebookModifiable", tempNotebook.isNotebookModifiable());
		query.bindValue(":requireLogin", tempNotebook.isRequireLogin());

		StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));			
		query.bindValue(":serviceCreated", serviceCreated.toString());
		
		query.bindValue(":shareKey", tempNotebook.getShareKey());
		query.bindValue(":username", tempNotebook.getUsername());
		
		if (isDirty)
			query.bindValue(":isDirty", true);
		else
			query.bindValue(":isDirty", false);

		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "SharedNotebook Table insert failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
		}
	}
	// Check if a notebook exists
	public boolean exists(long id) {
        NSqlQuery query = new NSqlQuery(db.getConnection());
        logger.log(logger.EXTREME, "Checking if shared notebook " +id +" exists");
       	boolean check = query.prepare("Select id from sharednotebook where id=:id");
       	query.bindValue(":id", id);
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "SharedNotebook Table exists check failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
		}
		if (query.next())
			return true;
        logger.log(logger.EXTREME, "Shared notebook " +id +" does not exist");		
		return false;
	}
	// Delete the notebook based on a id
	public void expungeNotebook(long id, boolean needsSync) {
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());

       	check = query.prepare("delete from SharedNotebook "
   				+"where id=:id");
		if (!check) {
			logger.log(logger.EXTREME, "SharedNotebook SQL delete prepare has failed.");
			logger.log(logger.EXTREME, query.lastError().toString());
		}
		query.bindValue(":id", id);
		check = query.exec();
		if (!check) 
			logger.log(logger.MEDIUM, "SharedNotebook delete failed.");
		
		// Signal the parent that work needs to be done
		if  (needsSync) {
			DeletedTable deletedTable = new DeletedTable(logger, db);
			deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");
		}
	}
	// Delete the notebook based on a id
	public void expungeNotebookByGuid(String id, boolean needsSync) {
		boolean check;
        NSqlQuery query = new NSqlQuery(db.getConnection());

       	check = query.prepare("delete from SharedNotebook "
   				+"where guid=:id");
		if (!check) {
			logger.log(logger.EXTREME, "SharedNotebook SQL delete by notebook guid prepare has failed.");
			logger.log(logger.EXTREME, query.lastError().toString());
		}
		query.bindValue(":id", id);
		check = query.exec();
		if (!check) 
			logger.log(logger.MEDIUM, "SharedNotebook delete by notebook guid failed.");
		
		// Signal the parent that work needs to be done
		if  (needsSync) {
			DeletedTable deletedTable = new DeletedTable(logger, db);
			deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");
		}
	}

	
	// Update a notebook
	public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {

		logger.log(logger.EXTREME, "*Updating Shared Notebook*");
		logger.log(logger.EXTREME, "ID: " + tempNotebook.getId());
		logger.log(logger.EXTREME, "Userid: " + tempNotebook.getUserId());
		logger.log(logger.EXTREME, "Email: " + tempNotebook.getEmail());
		logger.log(logger.EXTREME, "Notebook Guid: " + tempNotebook.getNotebookGuid());
		logger.log(logger.EXTREME, "Share Key: " + tempNotebook.getShareKey());
		logger.log(logger.EXTREME, "Username: " + tempNotebook.getUsername());

		
		boolean check;
		if (!exists(tempNotebook.getId())) {
			addNotebook(tempNotebook, isDirty);
			return;
		}
		
		List<SharedNotebook> list = getAll();
        logger.log(logger.EXTREME, "Dumping shared notebooks");
		for (int i=0; i<list.size(); i++) {
			logger.log(logger.EXTREME, "**************");
			logger.log(logger.EXTREME, "ID: " + list.get(i).getId());
			logger.log(logger.EXTREME, "Userid: " + list.get(i).getUserId());
			logger.log(logger.EXTREME, "Email: " + list.get(i).getEmail());
			logger.log(logger.EXTREME, "Notebook Guid: " + list.get(i).getNotebookGuid());
			logger.log(logger.EXTREME, "Share Key: " + list.get(i).getShareKey());
			logger.log(logger.EXTREME, "Username: " + list.get(i).getUsername());
		}
		
		SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
		
		StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));						
        NSqlQuery query = new NSqlQuery(db.getConnection());
       	check = query.prepare("Update SharedNotebook set userid=:userid, notebookGuid=:notebook, "
       			+ "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:serviceCreated, "
       			+ "shareKey=:shareKey, username=:username, isDirty=:isdirty where id=:id");
		query.bindValue(":id", tempNotebook.getId());
		query.bindValue(":userid", tempNotebook.getUserId());
		query.bindValue(":notebook", tempNotebook.getNotebookGuid());
		query.bindValue(":email", tempNotebook.getEmail());
		query.bindValue(":mod", tempNotebook.isNotebookModifiable());
		query.bindValue(":rlogin", tempNotebook.isRequireLogin());
		query.bindValue(":serviceCreated", serviceCreated.toString());
		query.bindValue(":shareKey", tempNotebook.getShareKey());
		query.bindValue(":username", tempNotebook.getUsername());
		
		query.bindValue(":isDirty", isDirty);
		
		check = query.exec();
		if (!check) {
			logger.log(logger.MEDIUM, "SharedNotebook Table update failed.");
			logger.log(logger.MEDIUM, query.lastError().toString());
		}
	}
	// Load notebooks from the database
	public List<SharedNotebook> getAll() {
		SharedNotebook tempNotebook;
		List<SharedNotebook> index = new ArrayList<SharedNotebook>();
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +
				"serviceCreated, "+
				"shareKey, username from SharedNotebook");
		if (!check)
			logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
		while (query.next()) {
			tempNotebook = new SharedNotebook();
			tempNotebook.setId(query.valueLong(0));
			tempNotebook.setUserId(query.valueInteger(1));
			tempNotebook.setNotebookGuid(query.valueString(2));
			tempNotebook.setEmail(query.valueString(3));
			tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));
			tempNotebook.setRequireLogin(query.valueBoolean(5,true));
			DateFormat indfm = null;
			try {
				indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
			} catch (Exception e) {	}
			try {
				tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());
			} catch (ParseException e) {
				e.printStackTrace();
			}
			tempNotebook.setShareKey(query.valueString(7));
			tempNotebook.setUsername(query.valueString(8));

			index.add(tempNotebook); 
		}	
		return index;
	}			

	// Load notebooks from the database
	public List<SharedNotebook> getForNotebook(String guid) {
		SharedNotebook tempNotebook;
		List<SharedNotebook> index = new ArrayList<SharedNotebook>();
		boolean check;
					
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.prepare("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +
				"serviceCreated, "+
				"shareKey, username from SharedNotebook where notebookGuid=:notebookGuid ");
		if (!check)
			logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL prepare has failed.");
		query.bindValue(":notebookGuid", guid);
		check = query.exec();
		if (!check)
			logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL exec has failed.");
		
		while (query.next()) {
			tempNotebook = new SharedNotebook();
			tempNotebook.setId(query.valueLong(0));
			tempNotebook.setUserId(query.valueInteger(1));
			tempNotebook.setNotebookGuid(query.valueString(2));
			tempNotebook.setEmail(query.valueString(3));
			tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));
			tempNotebook.setRequireLogin(query.valueBoolean(5,true));
			DateFormat indfm = null;
			try {
				indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
			} catch (Exception e) {	}
			try {
				tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());
			} catch (ParseException e) {
				e.printStackTrace();
			}
			tempNotebook.setShareKey(query.valueString(7));
			tempNotebook.setUsername(query.valueString(8));

			index.add(tempNotebook); 
		}	
		return index;
	}			

	
	// Get a list of shared notebooks that need to be updated
	public List <Long> getDirtyIds() {
		List<Long> index = new ArrayList<Long>();
		boolean check;	
        NSqlQuery query = new NSqlQuery(db.getConnection());
        				
		check = query.exec("Select id from SharedNotebook where isDirty = true");
		if (!check) 
			logger.log(logger.EXTREME, "SharedNotebook SQL retrieve has failed in getdirtyIds.");
		while (query.next()) {
			index.add(query.valueLong(0));
		}	
		return index;	
	}

}

