/*
 * 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.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

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


public class DatabaseConnection {
	// Table helpers
	private WordsTable					wordsTable;
	private TagTable					tagTable;
	private NotebookTable				notebookTable;
	private NoteTable					noteTable;
	private DeletedTable				deletedTable;
	private SavedSearchTable			searchTable;
	private WatchFolderTable			watchFolderTable;
	private InvalidXMLTable				invalidXMLTable;
	private LinkedNotebookTable			linkedNotebookTable;
	private SharedNotebookTable			sharedNotebookTable;
	private InkImagesTable				inkImagesTable;
	private SyncTable					syncTable;
	private SystemIconTable				systemIconTable;
	private HistoryTable historyTable;
	private ExcludedTable excludedTable;
	private StaredTable staredTable;
	
	private final ApplicationLogger		logger;
	private Connection					conn;
	private Connection					indexConn;
	private Connection					resourceConn;
	private Connection behaviorConn;
	
	int throttle;
	int id;

	public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String burl, String userid, String password, String cypherPassword, int throttle) {
		logger = l;
		this.throttle = throttle;
		dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
	}
	
	private void setupTables() {
		tagTable = new TagTable(logger, this);
		notebookTable = new NotebookTable(logger, this);
		noteTable = new NoteTable(logger, this);
		deletedTable = new DeletedTable(logger, this);
		searchTable = new SavedSearchTable(logger, this);	
		watchFolderTable = new WatchFolderTable(logger, this);
		invalidXMLTable = new InvalidXMLTable(logger, this);
		wordsTable = new WordsTable(logger, this);
		syncTable = new SyncTable(logger, this);
		linkedNotebookTable = new LinkedNotebookTable(logger, this);
		sharedNotebookTable = new SharedNotebookTable(logger, this);
		systemIconTable = new SystemIconTable(logger, this);
		inkImagesTable = new InkImagesTable(logger, this);
		historyTable = new HistoryTable(logger, this);
		excludedTable = new ExcludedTable(logger, this);
		staredTable = new StaredTable(logger, this);
		
	}
	
	
	// Compact the database
	public void compactDatabase() {
		
	}
	
	// Initialize the database connection
	public void dbSetup(String url,String indexUrl, String resourceUrl, String behaviorUrl, String userid, String userPassword, String cypherPassword) {
		logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);

		
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			System.exit(16);
		}
		
//		QJdbc.initialize();
		
		setupTables();
		
		File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
		boolean dbExists = f.exists(); 
		f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
		boolean indexDbExists = f.exists(); 
		f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
		boolean resourceDbExists = f.exists();
		f = Global.getFileManager().getDbDirFile(Global.behaviorDatabaseName + ".h2.db");
		boolean behaviorDbExists = f.exists();
		
		logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
		
		String passwordString = null;
		try {
			
			if (cypherPassword==null || cypherPassword.trim().equals(""))
				passwordString = userPassword;
			else
				passwordString = cypherPassword+" "+userPassword;
//			conn = DriverManager.getConnection(url,userid,passwordString);
//			conn = DriverManager.getConnection(url,userid,passwordString);
//			conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
			if (throttle == 0) {
				conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
			} else {
 				conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
			}
			indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
			resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
			behaviorConn = DriverManager.getConnection(behaviorUrl, userid, passwordString);
			
//			conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		
		// If it doesn't exist and we are the main thread, then we need to create stuff.
		if (!dbExists)  {
			createTables();
			Global.setAutomaticLogin(false);
		}		
		if (!resourceDbExists) {
			createResourceTables();
			if (dbTableExists("NoteResources")) {
				// Begin migration of database
				NSqlQuery query = new NSqlQuery(resourceConn);
				String linkcmd = "create linked table oldnoteresources "+
						"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
				query.exec(linkcmd);
				query.exec("insert into noteresources (select * from oldnoteresources)");
				query.exec("Drop table oldnoteresources;");
				query.exec("Update noteresources set indexneeded='true'");
				
			}
		}
		if (!indexDbExists)  {
			createIndexTables();
			executeSql("Update note set indexneeded='true'");
		}
		
		// 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
		if (!behaviorDbExists) {
			createHistoryTables();
			createExcludedTables();
			createStaredTables();
		}
		
		// If we encrypted/decrypted it the last time, we need to reconnect the tables.
//		if (Global.relinkTables) {
//			NSqlQuery query = new NSqlQuery(conn);
//			query.exec("Drop table NoteResources;");
//			String linkcmd = "create linked table NoteResources "
//				+"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
//			System.out.println(linkcmd);
//			query.exec(linkcmd);
//			System.err.println(query.lastError());
//			Global.relinkTables = false;
//		}
		
		
		logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
	}
	
	
	public void dbShutdown() {
		logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
	}
	
	public void upgradeDb(String version) {
		if (version.equals("0.85")) {
			executeSql("alter table note add column titleColor integer");
			executeSql("alter table note add column thumbnail blob");
			executeSql("alter table note add column thumbnailneeded boolean");
			executeSql("Update note set thumbnailneeded = true;");
			executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
			executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
			version = "0.86";
			Global.setDatabaseVersion(version);
		} 
		if (version.equals("0.86")) {
	
			executeSql("alter table notebook add column publishingUri VarChar");
			executeSql("alter table notebook add column publishingOrder Integer");
			executeSql("alter table notebook add column publishingAscending Boolean");
			executeSql("alter table notebook add column publishingPublicDescription varchar");
			executeSql("alter table notebook add column stack varchar");
			executeSql("alter table notebook add column icon blob");
			executeSql("alter table notebook add column readOnly boolean");
			executeSql("alter table notebook add column linked boolean");
			
			executeSql("alter table tag add column realname varchar");
			executeSql("alter table tag add column linked boolean");
			executeSql("alter table tag add column icon blob");
			executeSql("alter table tag add column notebookguid varchar");
			executeSql("alter table SavedSearch add column icon blob");

			executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
			executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
			executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
			executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
			
			executeSql("update note set thumbnailneeded=true, thumbnail=null;");
			executeSql("update notebook set publishingUri='', " +
					"publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
					"publishingPublicDescription='', linked=false");
			executeSql("update tag set linked=false, realname='', notebookguid=''");
			
			sharedNotebookTable.createTable();
			linkedNotebookTable.createTable();
			systemIconTable.createTable();
			inkImagesTable.createTable();
			
			version = "0.95";
			executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
			executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
			executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
			executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
			Global.setDatabaseVersion(version);
		} 
		if (version.equals("0.95")) {
			if (dbTableExists("words"))
				executeSql("Drop table words;");
			if (dbTableExists("NoteResources"))
				executeSql("Drop table NoteResources;");
			
			version = "0.97";
			Global.setDatabaseVersion(version);
		}
		if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
			executeSql("alter table note add column ORIGINAL_GUID VarChar");
			executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
		}
		if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
			executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
			executeSql("update notebook set NARROW_SORT_ORDER = -1");

			executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
			executeSql("update notebook set WIDE_SORT_ORDER = -1");
			
			executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
			executeSql("update notebook set WIDE_SORT_COLUMN = -1");
			
			executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
			executeSql("update notebook set NARROW_SORT_COLUMN = -1");
		}
		if (!dbTableColumnExists("NOTE", "PINNED")) {
			executeSql("alter table note add column pinned integer");
			executeSql("update note set pinned = 0");
		}
		if (!dbTableColumnExists("NOTE", "ATTRIBUTECONTENTCLASS")) {
			executeSql("alter table note add column attributeContentClass VarChar");
			executeSql("update note set attributeContentClass = ''");
		}
		
		// Evernoteサムネイルカラムを追加
		if (!dbTableColumnExists("NOTE", "ENTHUMBNAIL")) {
			executeSql("alter table note add column enThumbNail Blob");
		}
		
		// Apache Luceneを使った日本語検索のためのプレーンテキストノートコンテンツカラムを準備
		NSqlQuery query = new NSqlQuery(conn);
		if (!dbTableColumnExists("NOTE", "CONTENTTEXT")) {
			executeSql("alter table note add column contentText VarChar");
			executeSql("update note set contentText = ''");
			query.exec("Select guid, content from Note where contentText = ''");
			while (query.next()) {
				String guid = query.valueString(0);
				String content = query.valueString(1);
				String contentText = Global.extractPlainText(content);
				NSqlQuery query2 = new NSqlQuery(conn);
				query2.prepare("update note set contentText=:contentText where guid=:guid");
				query2.bindValue(":contentText", contentText);
				query2.bindValue(":guid", guid);
				query2.exec();
			}
			
			// Apache Luceneを使った全文検索のための準備
			query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
			query.exec("CALL FTL_INIT()");
		}
		
		// Apache Luceneを使った日本語検索のためのプレーンテキストノートリソースカラムを準備
		NSqlQuery rQuery = new NSqlQuery(resourceConn);
		rQuery.exec("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='NOTERESOURCES' and COLUMN_NAME='RESOURCETEXT'");
		if (!rQuery.next()) {
			rQuery.exec("alter table noteResources add column resourceText VarChar");
			rQuery.exec("update noteResources set resourceText = ''");
			
			// Apache Luceneを使った全文検索のための準備
			rQuery.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
			rQuery.exec("CALL FTL_INIT()");
		}
		
		// 注意：ここから先でnoteテーブルとnoteResourcesテーブルの構造を変更するな。全文検索ができなくなる。
		
		// v0.4.1以前では起動時に全文検索が正しく設定されない問題があったのでLuceneを再構築
		if (version.equals("0.97")) {
			query.exec("CALL FTL_DROP_ALL();");
			rQuery.exec("CALL FTL_DROP_ALL();");
			Global.rebuildFullTextNoteTarget(this);
			Global.rebuildFullTextResourceTarget(this);
			
			version = "0.98";
			Global.setDatabaseVersion(version);
		}
	}
	
	public void executeSql(String sql) {
		NSqlQuery query = new NSqlQuery(conn);
		query.exec(sql);	
	}
	
	public void checkDatabaseVersion() {
//		if (!Global.getDatabaseVersion().equals("0.86")) {
//			upgradeDb(Global.getDatabaseVersion());
//		}
//		if (!Global.getDatabaseVersion().equals("0.95")) {
//			upgradeDb(Global.getDatabaseVersion());
//		}
		if (!Global.getDatabaseVersion().equals("0.98")) {
			upgradeDb(Global.getDatabaseVersion());
		}
	}
	

	public void backupDatabase(int highSequence, long date) {
		
	}
	
	
	public void createTables() {
		Global.setDatabaseVersion("0.85");
		Global.setAutomaticLogin(false);
		Global.saveCurrentNoteGuid("");
		Global.saveUploadAmount(0);
		
		getTagTable().createTable();
		notebookTable.createTable(true);
		noteTable.createTable();
		deletedTable.createTable();		
		searchTable.createTable();
		watchFolderTable.createTable();
		invalidXMLTable.createTable();
		syncTable.createTable();
	}
	
	public void createIndexTables() {
		wordsTable.createTable();
	}
	
	public void createResourceTables() {
		noteTable.noteResourceTable.createTable();
	}
	
	public void createHistoryTables() {
		historyTable.createTable();
	}
	
	public void createExcludedTables() {
		excludedTable.createTable();
	}
	
	public void createStaredTables() {
		staredTable.createTable();
	}
	
	public Connection getConnection() {
		return conn;
	}
	public Connection getIndexConnection() {
		return  indexConn;
	}
	public Connection getResourceConnection() {
		return resourceConn;
	}
	
	public Connection getBehaviorConnection() {
		return behaviorConn;
	}
	
	//***************************************************************
	//* Table get methods
	//***************************************************************
	public DeletedTable getDeletedTable() {
		return deletedTable;
	}
	public TagTable getTagTable() {
		return tagTable;
	}
	public NoteTable getNoteTable() {
		return noteTable;
	}
	public NotebookTable getNotebookTable() {
		return notebookTable;
	}
	public SavedSearchTable getSavedSearchTable() {
		return searchTable;
	}
	public WatchFolderTable getWatchFolderTable() {
		return watchFolderTable;
	}
	public WordsTable getWordsTable() {
		return wordsTable;
	}
	public InvalidXMLTable getInvalidXMLTable() {
		return invalidXMLTable;
	}
	public SyncTable getSyncTable() {
		return syncTable;
	}
	public LinkedNotebookTable getLinkedNotebookTable() {
		return linkedNotebookTable;
	}
	public SharedNotebookTable getSharedNotebookTable() {
		return sharedNotebookTable;
	}
	public SystemIconTable getSystemIconTable() {
		return systemIconTable;
	}
	public InkImagesTable getInkImagesTable() {
		return inkImagesTable;
	}
	
	public HistoryTable getHistoryTable() {
		return historyTable;
	}
	
	public ExcludedTable getExcludedTable() {
		return excludedTable;
	}
	
	public StaredTable getStaredTable() {
		return staredTable;
	}

	//****************************************************************
	//* Begin/End transactions
	//****************************************************************
	public void beginTransaction() {
		commitTransaction();
        NSqlQuery query = new NSqlQuery(getConnection());	        				        
		if (!query.exec("Begin Transaction"))
			logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());

	}
	public void commitTransaction() {
        NSqlQuery query = new NSqlQuery(getConnection());
	        				        
		if (!query.exec("Commit"))
			logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
	}

	//****************************************************************
	//* Check if a table exists
	//****************************************************************
	public boolean dbTableExists(String name) {
        NSqlQuery query = new NSqlQuery(getConnection());
        query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
        query.bindValue(":name", name.toUpperCase());
        query.exec();
        if (query.next())
        	return true;
        else
        	return false;
	}
	
	//****************************************************************
	//* Check if a row in a table exists
	//****************************************************************
	public boolean dbTableColumnExists(String tableName, String columnName) {
        NSqlQuery query = new NSqlQuery(getConnection());
        query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
        query.bindValue(":name", tableName.toUpperCase());
        query.bindValue(":column", columnName);
        query.exec();
        if (query.next())
        	return true;
        else
        	return false;
	}
}
