/*

   Android Bookmark Database

   Copyright 2013 Thincast Technologies GmbH, Author: Martin Fleisz

   This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.

   If a copy of the MPL was not distributed with this file, You can obtain one at

   http://mozilla.org/MPL/2.0/.

*/

package com.freerdp.freerdpcore.services;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.provider.BaseColumns;

import android.util.Log;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

public class BookmarkDB extends SQLiteOpenHelper

{

	public static final String ID = BaseColumns._ID;

	private static final int DB_VERSION = 9;

	private static final String DB_BACKUP_PREFIX = "temp_";

	private static final String DB_NAME = "bookmarks.db";

	static final String DB_TABLE_BOOKMARK = "tbl_manual_bookmarks";

	static final String DB_TABLE_SCREEN = "tbl_screen_settings";

	static final String DB_TABLE_PERFORMANCE = "tbl_performance_flags";

	private static final String[] DB_TABLES = { DB_TABLE_BOOKMARK, DB_TABLE_SCREEN,

		                                        DB_TABLE_PERFORMANCE };

	static final String DB_KEY_SCREEN_COLORS = "colors";

	static final String DB_KEY_SCREEN_RESOLUTION = "resolution";

	static final String DB_KEY_SCREEN_WIDTH = "width";

	static final String DB_KEY_SCREEN_HEIGHT = "height";

	static final String DB_KEY_SCREEN_SETTINGS = "screen_settings";

	static final String DB_KEY_SCREEN_SETTINGS_3G = "screen_3g";

	static final String DB_KEY_PERFORMANCE_FLAGS = "performance_flags";

	static final String DB_KEY_PERFORMANCE_FLAGS_3G = "performance_3g";

	static final String DB_KEY_PERFORMANCE_RFX = "perf_remotefx";

	static final String DB_KEY_PERFORMANCE_GFX = "perf_gfx";

	static final String DB_KEY_PERFORMANCE_H264 = "perf_gfx_h264";

	static final String DB_KEY_PERFORMANCE_WALLPAPER = "perf_wallpaper";

	static final String DB_KEY_PERFORMANCE_THEME = "perf_theming";

	static final String DB_KEY_PERFORMANCE_DRAG = "perf_full_window_drag";

	static final String DB_KEY_PERFORMANCE_MENU_ANIMATIONS = "perf_menu_animations";

	static final String DB_KEY_PERFORMANCE_FONTS = "perf_font_smoothing";

	static final String DB_KEY_PERFORMANCE_COMPOSITION = "perf_desktop_composition";

	static final String DB_KEY_BOOKMARK_LABEL = "label";

	static final String DB_KEY_BOOKMARK_HOSTNAME = "hostname";

	static final String DB_KEY_BOOKMARK_USERNAME = "username";

	static final String DB_KEY_BOOKMARK_PASSWORD = "password";

	static final String DB_KEY_BOOKMARK_DOMAIN = "domain";

	static final String DB_KEY_BOOKMARK_PORT = "port";

	static final String DB_KEY_BOOKMARK_REDIRECT_SDCARD = "redirect_sdcard";

	static final String DB_KEY_BOOKMARK_REDIRECT_SOUND = "redirect_sound";

	static final String DB_KEY_BOOKMARK_REDIRECT_MICROPHONE = "redirect_microphone";

	static final String DB_KEY_BOOKMARK_SECURITY = "security";

	static final String DB_KEY_BOOKMARK_REMOTE_PROGRAM = "remote_program";

	static final String DB_KEY_BOOKMARK_WORK_DIR = "work_dir";

	static final String DB_KEY_BOOKMARK_ASYNC_CHANNEL = "async_channel";

	static final String DB_KEY_BOOKMARK_ASYNC_INPUT = "async_input";

	static final String DB_KEY_BOOKMARK_ASYNC_UPDATE = "async_update";

	static final String DB_KEY_BOOKMARK_CONSOLE_MODE = "console_mode";

	static final String DB_KEY_BOOKMARK_DEBUG_LEVEL = "debug_level";

	static final String DB_KEY_BOOKMARK_GW_ENABLE = "enable_gateway_settings";

	static final String DB_KEY_BOOKMARK_GW_HOSTNAME = "gateway_hostname";

	static final String DB_KEY_BOOKMARK_GW_PORT = "gateway_port";

	static final String DB_KEY_BOOKMARK_GW_USERNAME = "gateway_username";

	static final String DB_KEY_BOOKMARK_GW_PASSWORD = "gateway_password";

	static final String DB_KEY_BOOKMARK_GW_DOMAIN = "gateway_domain";

	static final String DB_KEY_BOOKMARK_3G_ENABLE = "enable_3g_settings";

	public BookmarkDB(Context context)

	{

		super(context, DB_NAME, null, DB_VERSION);

	}

	private static List<String> GetColumns(SQLiteDatabase db, String tableName)

	{

		List<String> ar = null;

		Cursor c = null;

		try

		{

			c = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 1", null);

			if (c != null)

			{

				ar = new ArrayList<>(Arrays.asList(c.getColumnNames()));

			}

		}

		catch (Exception e)

		{

			Log.v(tableName, e.getMessage(), e);

			e.printStackTrace();

		}

		finally

		{

			if (c != null)

				c.close();

		}

		return ar;

	}

	private static String joinStrings(List<String> list, String delim)

	{

		StringBuilder buf = new StringBuilder();

		int num = list.size();

		for (int i = 0; i < num; i++)

		{

			if (i != 0)

				buf.append(delim);

			buf.append((String)list.get(i));

		}

		return buf.toString();

	}

	private void backupTables(SQLiteDatabase db)

	{

		for (String table : DB_TABLES)

		{

			final String tmpTable = DB_BACKUP_PREFIX + table;

			final String query = "ALTER TABLE '" + table + "' RENAME TO '" + tmpTable + "'";

			try

			{

				db.execSQL(query);

			}

			catch (Exception e)

			{

				/* Ignore errors if table does not exist. */

			}

		}

	}

	private void dropOldTables(SQLiteDatabase db)

	{

		for (String table : DB_TABLES)

		{

			final String tmpTable = DB_BACKUP_PREFIX + table;

			final String query = "DROP TABLE IF EXISTS '" + tmpTable + "'";

			db.execSQL(query);

		}

	}

	private void createDB(SQLiteDatabase db)

	{

		final String sqlScreenSettings =

		    "CREATE TABLE IF NOT EXISTS " + DB_TABLE_SCREEN + " (" + ID + " INTEGER PRIMARY KEY, " +

		    DB_KEY_SCREEN_COLORS + " INTEGER DEFAULT 16, " + DB_KEY_SCREEN_RESOLUTION +

		    " INTEGER DEFAULT 0, " + DB_KEY_SCREEN_WIDTH + ", " + DB_KEY_SCREEN_HEIGHT + ");";

		db.execSQL(sqlScreenSettings);

		final String sqlPerformanceFlags =

		    "CREATE TABLE IF NOT EXISTS " + DB_TABLE_PERFORMANCE + " (" + ID +

		    " INTEGER PRIMARY KEY, " + DB_KEY_PERFORMANCE_RFX + " INTEGER, " +

		    DB_KEY_PERFORMANCE_GFX + " INTEGER, " + DB_KEY_PERFORMANCE_H264 + " INTEGER, " +

		    DB_KEY_PERFORMANCE_WALLPAPER + " INTEGER, " + DB_KEY_PERFORMANCE_THEME + " INTEGER, " +

		    DB_KEY_PERFORMANCE_DRAG + " INTEGER, " + DB_KEY_PERFORMANCE_MENU_ANIMATIONS +

		    " INTEGER, " + DB_KEY_PERFORMANCE_FONTS + " INTEGER, " +

		    DB_KEY_PERFORMANCE_COMPOSITION + " INTEGER);";

		db.execSQL(sqlPerformanceFlags);

		final String sqlManualBookmarks = getManualBookmarksCreationString();

		db.execSQL(sqlManualBookmarks);

	}

	private void upgradeTables(SQLiteDatabase db)

	{

		for (String table : DB_TABLES)

		{

			final String tmpTable = DB_BACKUP_PREFIX + table;

			final List<String> newColumns = GetColumns(db, table);

			List<String> columns = GetColumns(db, tmpTable);

			if (columns != null)

			{

				columns.retainAll(newColumns);

				// restore data

				final String cols = joinStrings(columns, ",");

				final String query = String.format("INSERT INTO %s (%s) SELECT %s from '%s'", table,

				                                   cols, cols, tmpTable);

				db.execSQL(query);

			}

		}

	}

	private void downgradeTables(SQLiteDatabase db)

	{

		for (String table : DB_TABLES)

		{

			final String tmpTable = DB_BACKUP_PREFIX + table;

			List<String> oldColumns = GetColumns(db, table);

			final List<String> columns = GetColumns(db, tmpTable);

			if (oldColumns != null)

			{

				oldColumns.retainAll(columns);

				// restore data

				final String cols = joinStrings(oldColumns, ",");

				final String query = String.format("INSERT INTO %s (%s) SELECT %s from '%s'", table,

				                                   cols, cols, tmpTable);

				db.execSQL(query);

			}

		}

	}

	private List<String> getTableNames(SQLiteDatabase db)

	{

		final String query = "SELECT name FROM sqlite_master WHERE type='table'";

		Cursor cursor = db.rawQuery(query, null);

		List<String> list = new ArrayList<>();

		try

		{

			if (cursor.moveToFirst() && (cursor.getCount() > 0))

			{

				while (!cursor.isAfterLast())

				{

					final String name = cursor.getString(cursor.getColumnIndex("name"));

					list.add(name);

					cursor.moveToNext();

				}

			}

		}

		finally

		{

			cursor.close();

		}

		return list;

	}

	private void insertDefault(SQLiteDatabase db)

	{

		ContentValues screenValues = new ContentValues();

		screenValues.put(DB_KEY_SCREEN_COLORS, 32);

		screenValues.put(DB_KEY_SCREEN_RESOLUTION, 1);

		screenValues.put(DB_KEY_SCREEN_WIDTH, 1024);

		screenValues.put(DB_KEY_SCREEN_HEIGHT, 768);

		final long idScreen = db.insert(DB_TABLE_SCREEN, null, screenValues);

		final long idScreen3g = db.insert(DB_TABLE_SCREEN, null, screenValues);

		ContentValues performanceValues = new ContentValues();

		performanceValues.put(DB_KEY_PERFORMANCE_RFX, 1);

		performanceValues.put(DB_KEY_PERFORMANCE_GFX, 1);

		performanceValues.put(DB_KEY_PERFORMANCE_H264, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_WALLPAPER, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_THEME, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_DRAG, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_MENU_ANIMATIONS, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_FONTS, 0);

		performanceValues.put(DB_KEY_PERFORMANCE_COMPOSITION, 0);

		final long idPerformance = db.insert(DB_TABLE_PERFORMANCE, null, performanceValues);

		final long idPerformance3g = db.insert(DB_TABLE_PERFORMANCE, null, performanceValues);

		ContentValues bookmarkValues = new ContentValues();

		bookmarkValues.put(DB_KEY_BOOKMARK_LABEL, "Test Server");

		bookmarkValues.put(DB_KEY_BOOKMARK_HOSTNAME, "testservice.afreerdp.com");

		bookmarkValues.put(DB_KEY_BOOKMARK_USERNAME, "");

		bookmarkValues.put(DB_KEY_BOOKMARK_PASSWORD, "");

		bookmarkValues.put(DB_KEY_BOOKMARK_DOMAIN, "");

		bookmarkValues.put(DB_KEY_BOOKMARK_PORT, "3389");

		bookmarkValues.put(DB_KEY_SCREEN_SETTINGS, idScreen);

		bookmarkValues.put(DB_KEY_SCREEN_SETTINGS_3G, idScreen3g);

		bookmarkValues.put(DB_KEY_PERFORMANCE_FLAGS, idPerformance);

		bookmarkValues.put(DB_KEY_PERFORMANCE_FLAGS_3G, idPerformance3g);

		bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_SDCARD, 0);

		bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_SOUND, 0);

		bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_MICROPHONE, 0);

		bookmarkValues.put(DB_KEY_BOOKMARK_SECURITY, 0);

		bookmarkValues.put(DB_KEY_BOOKMARK_REMOTE_PROGRAM, "");

		bookmarkValues.put(DB_KEY_BOOKMARK_WORK_DIR, "");

		bookmarkValues.put(DB_KEY_BOOKMARK_ASYNC_CHANNEL, 1);

		bookmarkValues.put(DB_KEY_BOOKMARK_ASYNC_INPUT, 1);

		bookmarkValues.put(DB_KEY_BOOKMARK_ASYNC_UPDATE, 1);

		bookmarkValues.put(DB_KEY_BOOKMARK_CONSOLE_MODE, 0);

		bookmarkValues.put(DB_KEY_BOOKMARK_DEBUG_LEVEL, "INFO");

		db.insert(DB_TABLE_BOOKMARK, null, bookmarkValues);

	}

	@Override public void onCreate(SQLiteDatabase db)

	{

		createDB(db);

		insertDefault(db);

	}

	private String getManualBookmarksCreationString()

	{

		return ("CREATE TABLE IF NOT EXISTS " + DB_TABLE_BOOKMARK + " (" + ID +

		        " INTEGER PRIMARY KEY, " + DB_KEY_BOOKMARK_LABEL + " TEXT NOT NULL, " +

		        DB_KEY_BOOKMARK_HOSTNAME + " TEXT NOT NULL, " + DB_KEY_BOOKMARK_USERNAME +

		        " TEXT NOT NULL, " + DB_KEY_BOOKMARK_PASSWORD + " TEXT, " + DB_KEY_BOOKMARK_DOMAIN +

		        " TEXT, " + DB_KEY_BOOKMARK_PORT + " TEXT, " + DB_KEY_SCREEN_SETTINGS +

		        " INTEGER NOT NULL, " + DB_KEY_PERFORMANCE_FLAGS + " INTEGER NOT NULL, "

		        + DB_KEY_BOOKMARK_GW_ENABLE + " INTEGER DEFAULT 0, " + DB_KEY_BOOKMARK_GW_HOSTNAME +

		        " TEXT, " + DB_KEY_BOOKMARK_GW_PORT + " INTEGER DEFAULT 443, " +

		        DB_KEY_BOOKMARK_GW_USERNAME + " TEXT, " + DB_KEY_BOOKMARK_GW_PASSWORD + " TEXT, " +

		        DB_KEY_BOOKMARK_GW_DOMAIN + " TEXT, "

		        + DB_KEY_BOOKMARK_3G_ENABLE + " INTEGER DEFAULT 0, " + DB_KEY_SCREEN_SETTINGS_3G +

		        " INTEGER NOT NULL, " + DB_KEY_PERFORMANCE_FLAGS_3G + " INTEGER NOT NULL, " +

		        DB_KEY_BOOKMARK_REDIRECT_SDCARD + " INTEGER DEFAULT 0, " +

		        DB_KEY_BOOKMARK_REDIRECT_SOUND + " INTEGER DEFAULT 0, " +

		        DB_KEY_BOOKMARK_REDIRECT_MICROPHONE + " INTEGER DEFAULT 0, " +

		        DB_KEY_BOOKMARK_SECURITY + " INTEGER, " + DB_KEY_BOOKMARK_REMOTE_PROGRAM +

		        " TEXT, " + DB_KEY_BOOKMARK_WORK_DIR + " TEXT, " + DB_KEY_BOOKMARK_ASYNC_CHANNEL +

		        " INTEGER DEFAULT 0, " + DB_KEY_BOOKMARK_ASYNC_INPUT + " INTEGER DEFAULT 0, " +

		        DB_KEY_BOOKMARK_ASYNC_UPDATE + " INTEGER DEFAULT 0, " +

		        DB_KEY_BOOKMARK_CONSOLE_MODE + " INTEGER, " + DB_KEY_BOOKMARK_DEBUG_LEVEL +

		        " TEXT DEFAULT 'INFO', "

		        + "FOREIGN KEY(" + DB_KEY_SCREEN_SETTINGS + ") REFERENCES " + DB_TABLE_SCREEN +

		        "(" + ID + "), "

		        + "FOREIGN KEY(" + DB_KEY_PERFORMANCE_FLAGS + ") REFERENCES " +

		        DB_TABLE_PERFORMANCE + "(" + ID + "), "

		        + "FOREIGN KEY(" + DB_KEY_SCREEN_SETTINGS_3G + ") REFERENCES " + DB_TABLE_SCREEN +

		        "(" + ID + "), "

		        + "FOREIGN KEY(" + DB_KEY_PERFORMANCE_FLAGS_3G + ") REFERENCES " +

		        DB_TABLE_PERFORMANCE + "(" + ID + ") "

		        + ");");

	}

	private void recreateDB(SQLiteDatabase db)

	{

		for (String table : DB_TABLES)

		{

			final String query = "DROP TABLE IF EXISTS '" + table + "'";

			db.execSQL(query);

		}

		onCreate(db);

	}

	private void upgradeDB(SQLiteDatabase db)

	{

		db.beginTransaction();

		try

		{

			/* Back up old tables. */

			dropOldTables(db);

			backupTables(db);

			createDB(db);

			upgradeTables(db);

			db.setTransactionSuccessful();

		}

		finally

		{

			db.endTransaction();

			dropOldTables(db);

		}

	}

	private void downgradeDB(SQLiteDatabase db)

	{

		db.beginTransaction();

		try

		{

			/* Back up old tables. */

			dropOldTables(db);

			backupTables(db);

			createDB(db);

			downgradeTables(db);

			db.setTransactionSuccessful();

		}

		finally

		{

			db.endTransaction();

			dropOldTables(db);

		}

	}

	// from

	// http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another

	@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

	{

		switch (oldVersion)

		{

			case 0:

			case 1:

			case 2:

			case 3:

			case 4:

			case 5:

			case 6:

			case 7:

			case 8:

			case 9:

				upgradeDB(db);

				break;

			default:

				recreateDB(db);

				break;

		}

	}

	@Override public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)

	{

		downgradeDB(db);

	}

}