hostage/src/de/tudarmstadt/informatik/hostage/logging/UglyDbHelper.java

package de.tudarmstadt.informatik.hostage.logging;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import de.tudarmstadt.informatik.hostage.logging.Record.TYPE;
import de.tudarmstadt.informatik.hostage.model.Profile;
import de.tudarmstadt.informatik.hostage.ui.LogFilter;

/**
 * This class creates SQL tables and handles all access to the database.<br>
 * It contains several methods with predefined queries to extract different
 * kinds of information from the database.<br>
 * The database contains two tables: {@link #TABLE_RECORDS} and
 * {@link #TABLE_BSSIDS}:<br>
 * {@link #TABLE_RECORDS} contains all logging information of a single message
 * record except the SSID.<br>
 * {@link #TABLE_BSSIDS} contains the BSSID of all recorded Networks and the
 * corresponding SSID.<br>
 * 
 * @author Lars Pandikow
 */
public class UglyDbHelper extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 7;

	// Database Name
	private static final String DATABASE_NAME = "recordManager";

	// Contacts table names
	private static final String TABLE_ATTACK_INFO = "attack_info";
    private static final String TABLE_RECORDS = "records";
    private static final String TABLE_BSSIDS = "bssids";
	private static final String TABLE_PROFILES = "profiles";

	// Contacts Table Columns names
	public static final String KEY_ID = "_id";
	public static final String KEY_ATTACK_ID = "_attack_id";
	public static final String KEY_TYPE = "type";
	public static final String KEY_TIME = "timestamp";
	public static final String KEY_PACKET = "packet";
	public static final String KEY_PROTOCOL = "protocol";
	public static final String KEY_EXTERNAL_IP = "externalIP";
	public static final String KEY_LOCAL_IP = "localIP";
	public static final String KEY_LOCAL_HOSTNAME = "localHostName";
	public static final String KEY_LOCAL_PORT = "localPort";
	public static final String KEY_REMOTE_IP = "remoteIP";
	public static final String KEY_REMOTE_HOSTNAME = "remoteHostName";
	public static final String KEY_REMOTE_PORT = "remotePort";
	public static final String KEY_BSSID = "_bssid";
	public static final String KEY_SSID = "ssid";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_ACCURACY = "accuracy";

	public static final String KEY_PROFILE_ID = "_profile_id";
	public static final String KEY_PROFILE_NAME = "profile_name";
	public static final String KEY_PROFILE_DESCRIPTION = "profile_description";
	public static final String KEY_PROFILE_ICON = "profile_icon";
	public static final String KEY_PROFILE_EDITABLE = "profile_editable";
	public static final String KEY_PROFILE_ACTIVE = "profile_active";

	// Database sql create statements
	private static final String CREATE_PROFILE_TABLE = "CREATE TABLE "
			+ TABLE_PROFILES + "("
			+ KEY_PROFILE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ KEY_PROFILE_NAME + " TEXT,"
			+ KEY_PROFILE_DESCRIPTION + " TEXT,"
			+ KEY_PROFILE_ICON + " TEXT,"
			+ KEY_PROFILE_EDITABLE + " INTEGER,"
			+ KEY_PROFILE_ACTIVE + " INTEGER"
			+ ")";

	private static final String CREATE_RECORD_TABLE = "CREATE TABLE "
			+ TABLE_RECORDS + "("
            + KEY_ID + " INTEGER NOT NULL,"
			+ KEY_ATTACK_ID + " INTEGER NOT NULL,"
            + KEY_TYPE + " TEXT,"
			+ KEY_TIME + " INTEGER,"
            + KEY_PACKET + " TEXT,"
            + "FOREIGN KEY("
			+ KEY_ATTACK_ID + ") REFERENCES " + TABLE_ATTACK_INFO + "("
			+ KEY_ATTACK_ID + ")," + "PRIMARY KEY(" + KEY_ID + ", "
			+ KEY_ATTACK_ID + ")" + ")";

	private static final String CREATE_ATTACK_INFO_TABLE = "CREATE TABLE "
			+ TABLE_ATTACK_INFO + "("
            + KEY_ATTACK_ID + " INTEGER PRIMARY KEY,"
			+ KEY_PROTOCOL + " TEXT,"
            + KEY_EXTERNAL_IP + " TEXT,"
			+ KEY_LOCAL_IP + " BLOB,"
            + KEY_LOCAL_HOSTNAME + " TEXT,"
			+ KEY_LOCAL_PORT + " INTEGER,"
            + KEY_REMOTE_IP + " BLOB,"
			+ KEY_REMOTE_HOSTNAME + " TEXT,"
            + KEY_REMOTE_PORT + " INTEGER,"
			+ KEY_BSSID + " TEXT,"
            + "FOREIGN KEY(" + KEY_BSSID
			+ ") REFERENCES " + TABLE_BSSIDS + "(" + KEY_BSSID + ")" + ")";

	private static final String CREATE_BSSID_TABLE = "CREATE TABLE "
			+ TABLE_BSSIDS + "("
            + KEY_BSSID + " TEXT PRIMARY KEY,"
            + KEY_SSID + " TEXT,"
            + KEY_LATITUDE + " INTEGER,"
            + KEY_LONGITUDE + " INTEGER,"
            + KEY_ACCURACY + " INTEGER,"
            + KEY_TIME + " INTEGER"
			+ ")";

	public UglyDbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	  /*
    // Contacts Table Columns names
	private static final String KEY_ID = "_id";
	private static final String KEY_ATTACK_ID = "_attack_id";
	private static final String KEY_TYPE = "type";
	private static final String KEY_TIME = "timestamp";
	private static final String KEY_PACKET = "packet";
	private static final String KEY_PROTOCOL = "protocol";
	private static final String KEY_EXTERNAL_IP ="externalIP";
	private static final String KEY_LOCAL_IP = "localIP";
	private static final String KEY_LOCAL_HOSTNAME = "localHostName";
	private static final String KEY_LOCAL_PORT = "localPort";
	private static final String KEY_REMOTE_IP = "remoteIP";
	private static final String KEY_REMOTE_HOSTNAME = "remoteHostName";
	private static final String KEY_REMOTE_PORT = "remotePort";
	private static final String KEY_BSSID = "_bssid";
	private static final String KEY_SSID = "ssid";
	private static final String KEY_LATITUDE = "latitude";
	private static final String KEY_LONGITUDE = "longitude";
	private static final String KEY_ACCURACY = "accuracy";
	*/

	/**
	 * Gets all received {@link Record Records} for the specified information in the LogFilter ordered by date.
	 * @return A ArrayList with all received {@link Record Records} for the LogFilter.
	 */
	public ArrayList<Record> getRecordsForFilter(LogFilter filter) {
		ArrayList<Record> recordList = new ArrayList<Record>();
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO +  " JOIN " + TABLE_BSSIDS + " USING "+ "(" + KEY_BSSID + ")";

		// TIMESTAMPS
		selectQuery = selectQuery + " WHERE " + TABLE_RECORDS +"."+KEY_TIME;
		selectQuery = selectQuery + " < " + filter.getBelowTimestamp();
		selectQuery = selectQuery + " AND " + TABLE_RECORDS +"."+KEY_TIME;
		selectQuery = selectQuery + " > " + filter.getAboveTimestamp();

		if (filter.getBSSIDs() != null && filter.getBSSIDs().size() > 0) {
			selectQuery = selectQuery + " AND ";
			selectQuery = selectQuery + filter.getBSSIDQueryStatement(TABLE_BSSIDS, KEY_BSSID);
		}
		if (filter.getESSIDs() != null && filter.getESSIDs().size() > 0) {
			selectQuery = selectQuery + " AND ";
			selectQuery = selectQuery + filter.getESSIDQueryStatement(TABLE_BSSIDS, KEY_SSID);
		}
		if (filter.getProtocols() != null && filter.getProtocols().size() > 0) {
			selectQuery = selectQuery + " AND ";
			selectQuery = selectQuery + filter.getProtocolsQueryStatement(TABLE_ATTACK_INFO, KEY_PROTOCOL);
		}

        if (filter.getSorttype() == LogFilter.SortType.timestamp){
            //DESC
            selectQuery = selectQuery + " ORDER BY " + filter.getSorttype() + " DESC";
        } else {
            selectQuery = selectQuery + " ORDER BY " + filter.getSorttype();
        }
		System.out.println(selectQuery);
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();

		// return record list
		db.close();
		return recordList;
	}

	/**
	 * Gets all non duplicate Records For the key BSSID.
	 * @return A ArrayList with received Records.
	 */
	public ArrayList<String> getUniqueBSSIDRecords(){
		return this.getUniqueDataEntryForKeyType(KEY_BSSID, TABLE_BSSIDS);
	}
	/**
	 * Gets all non duplicate Records For the key ESSID.
	 * @return A ArrayList with received Records.
	 */
	public ArrayList<String> getUniqueESSIDRecords(){
		return this.getUniqueDataEntryForKeyType(KEY_SSID, TABLE_BSSIDS);
	}

    public ArrayList<String> getUniqueESSIDRecordsForProtocol(String protocol){
        return this.getUniqueIDForProtocol(KEY_SSID, protocol);
    }
    public ArrayList<String> getUniqueBSSIDRecordsForProtocol(String protocol){
        return this.getUniqueIDForProtocol(KEY_BSSID, protocol);
    }

    private ArrayList<String> getUniqueIDForProtocol(String id, String protocol){
        ArrayList<String> recordList = new ArrayList<String>();
        String selectQuery = "SELECT DISTINCT " + id + " FROM " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING "+ "(" + id + ") " + " WHERE " + TABLE_ATTACK_INFO + "."+ KEY_PROTOCOL + " = " + "'" + protocol + "'"+ " ORDER BY " + id; // " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS +


        // ORDERED BY TIME
        System.out.println(selectQuery);
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                String record = cursor.getString(0);
                recordList.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();

        // return record list
        db.close();
        return recordList;
    }

	/**
	 * Gets all non duplicate Data Entry For a specific KeyType ( e.g. BSSIDs).
	 * @return A ArrayList with received Records.
	 */
	public ArrayList<String> getUniqueDataEntryForKeyType(String keyType, String table) {
		ArrayList<String> recordList = new ArrayList<String>();
		//String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS;
		String selectQuery = "SELECT DISTINCT " + keyType + " FROM " + table + " ORDER BY " + keyType; // " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS +


		// ORDERED BY TIME
		System.out.println(selectQuery);
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				String record = cursor.getString(0);
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();

		// return record list
		db.close();
		return recordList;
	}

	/**
	 * Adds a given {@link Record} to the database.
	 * 
	 * @param record
	 *            The added {@link Record} .
	 */
	public void addRecord(Record record) {
		SQLiteDatabase db = this.getWritableDatabase();

		HashMap<String, Object> bssidValues = new HashMap<String, Object>();
		bssidValues.put(KEY_BSSID, record.getBssid());
		bssidValues.put(KEY_SSID, record.getSsid());
		bssidValues.put(KEY_LATITUDE, record.getLatitude());
		bssidValues.put(KEY_LONGITUDE, record.getLongitude());
		bssidValues.put(KEY_ACCURACY, record.getAccuracy());
		bssidValues.put(KEY_TIME, record.getTimestampLocation());

		ContentValues attackValues = new ContentValues();
		attackValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		attackValues.put(KEY_PROTOCOL, record.getProtocol().toString());
		attackValues.put(KEY_EXTERNAL_IP, record.getExternalIP());
		attackValues.put(KEY_LOCAL_IP, record.getLocalIP()); // Log Local IP
		attackValues.put(KEY_LOCAL_HOSTNAME, record.getLocalHost());
		attackValues.put(KEY_LOCAL_PORT, record.getLocalPort());
		attackValues.put(KEY_REMOTE_IP, record.getRemoteIP()); // Log Remote IP
		attackValues.put(KEY_REMOTE_HOSTNAME, record.getRemoteHost());
		attackValues.put(KEY_REMOTE_PORT, record.getRemotePort()); // Log Remote
																	// Port
		attackValues.put(KEY_BSSID, record.getBssid());

		ContentValues recordValues = new ContentValues();
		recordValues.put(KEY_ID, record.getId()); // Log Message Number
		recordValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		recordValues.put(KEY_TYPE, record.getType().name()); // Log Type
		recordValues.put(KEY_TIME, record.getTimestamp()); // Log Timestamp
		recordValues.put(KEY_PACKET, record.getPacket()); // Log Packet

		// Inserting Rows
		db.insertWithOnConflict(TABLE_ATTACK_INFO, null, attackValues,
				SQLiteDatabase.CONFLICT_REPLACE);
		db.insert(TABLE_RECORDS, null, recordValues);
		db.close(); // Closing database connection
		// Update Network Information
		updateNetworkInformation(bssidValues);
	}

	/**
	 * Determines if a network with given BSSID has already been recorded as
	 * malicious.
	 * 
	 * @param BSSID
	 *            The BSSID of the network.
	 * @return True if an attack has been recorded in a network with the given
	 *         BSSID, else false.
	 */
	public boolean bssidSeen(String BSSID) {
		String countQuery = "SELECT  * FROM " + TABLE_BSSIDS + " WHERE "
				+ KEY_BSSID + " = " + "'" + BSSID + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();
		db.close();
		return result > 0;
	}

	public int numBssidSeen(String protocol, String BSSID) {
		String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO
				+ " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_ATTACK_INFO + "." +  KEY_PROTOCOL
				+ " = " + "'" + protocol + "'" + " AND " + TABLE_BSSIDS+ "."+ KEY_BSSID + " = "
				+ "'" + BSSID + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();
		db.close();
		return result;
	}

	/**
	 * Determines if an attack has been recorded on a specific protocol in a
	 * network with a given BSSID.
	 * 
	 * @param protocol
	 *            The
	 *            {@link de.tudarmstadt.informatik.hostage.protocol.Protocol
	 *            Protocol} to inspect.
	 * @param BSSID
	 *            The BSSID of the network.
	 * @return True if an attack on the given protocol has been recorded in a
	 *         network with the given BSSID, else false.
	 */
	public boolean bssidSeen(String protocol, String BSSID) {
		String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO
				+ " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_ATTACK_INFO + "." +  KEY_PROTOCOL
				+ " = " + "'" + protocol + "'" + " AND " + TABLE_BSSIDS+ "."+ KEY_BSSID + " = "
				+ "'" + BSSID + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();
		db.close();
		return result > 0;
	}

	/**
	 * Deletes all records from {@link #TABLE_RECORDS}.
	 */
	public void clearData() {
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(TABLE_RECORDS, null, null);
		db.delete(TABLE_ATTACK_INFO, null, null);
		db.delete(TABLE_PROFILES, null, null);
		db.close();
	}

	/**
	 * Deletes all records from {@link #TABLE_RECORDS} with a specific BSSID.
	 * 
	 * @param bssid
	 *            The BSSID to match against.
	 */
	public void deleteByBSSID(String bssid) {
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(TABLE_RECORDS, KEY_BSSID + " = ?", new String[] { bssid });
		db.delete(TABLE_ATTACK_INFO, KEY_BSSID + " = ?", new String[] { bssid });
		db.close();
	}

	// TODO Delete statement �berarbeiten
	/**
	 * Deletes all records from {@link #TABLE_RECORDS} with a time stamp smaller
	 * then the given
	 * 
	 * @param date
	 *            A Date represented in milliseconds.
	 */
	public void deleteByDate(long date) {
		SQLiteDatabase db = this.getReadableDatabase();
		String deleteQuery = "DELETE  FROM " + TABLE_RECORDS + " WHERE "
				+ KEY_TIME + " < " + date;
		// TODO Delete statement �berarbeiten
		// String deleteQuery2 = "DELETE "
		db.execSQL(deleteQuery);
		db.close();
	}

	/**
	 * Returns a String array with all BSSIDs stored in the database.
	 * 
	 * @return String[] of all recorded BSSIDs.
	 */
	public String[] getAllBSSIDS() {
		String selectQuery = "SELECT  * FROM " + TABLE_BSSIDS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String[] bssidList = new String[cursor.getCount()];
		int counter = 0;
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				bssidList[counter] = cursor.getString(0);
				counter++;
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return bssidList;
	}

	/**
	 * Gets all received {@link Record Records} for every attack identified by
	 * its attack id and ordered by date.
	 * 
	 * @return A ArrayList with all received {@link Record Records} for each
	 *         attack id in the Database.
	 */
	public ArrayList<Record> getAllReceivedRecordsOfEachAttack() {
		ArrayList<Record> recordList = new ArrayList<Record>();
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + KEY_TYPE + "='RECEIVE'"
				+ " ORDER BY " + TABLE_RECORDS+ "."+ KEY_TIME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();

		// return record list
		db.close();
		return recordList;
	}

	/**
	 * Gets all {@link Record Records} saved in the database.
	 * 
	 * @return A ArrayList of all the {@link Record Records} in the Database.
	 */
	public ArrayList<Record> getAllRecords() {
		ArrayList<Record> recordList = new ArrayList<Record>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		Log.i("Database", "Start loop");
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Log.i("Database", "Add Record");
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return record list
		return recordList;
	}

	/**
	 * Determines the number of different attack_ids in the database.
	 * 
	 * @return The number of different attack_ids in the database.
	 */
	public int getAttackCount() {
		String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}

	/**
	 * Determines the number of different attack_ids for a specific protocol in
	 * the database.
	 * 
	 * @param protocol
	 *            The String representation of the
	 *            {@link de.tudarmstadt.informatik.hostage.protocol.Protocol
	 *            Protocol}
	 * @return The number of different attack_ids in the database.
	 */
	public int getAttackPerProtocolCount(String protocol) {
		String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO + " WHERE "
				+ KEY_PROTOCOL + " = " + "'" + protocol + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}

	/**
	 * Determines the highest attack id stored in the database.
	 * 
	 * @return The highest attack id stored in the database.
	 */
	public long getHighestAttackId() {
		String selectQuery = "SELECT MAX(" + KEY_ATTACK_ID + ") FROM "
				+ TABLE_ATTACK_INFO;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		int result;

		if (cursor.moveToFirst()) {
			result = cursor.getInt(0);
		} else {
			result = -1;
		}
		cursor.close();
		db.close();
		return result;
	}

	public ArrayList<HashMap<String, Object>> getNetworkInformation() {
		String selectQuery = "SELECT  * FROM " + TABLE_BSSIDS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		ArrayList<HashMap<String, Object>> networkInformation = new ArrayList<HashMap<String, Object>>();

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				HashMap<String, Object> values = new HashMap<String, Object>();
				values.put(KEY_BSSID, cursor.getString(0));
				values.put(KEY_SSID, cursor.getString(1));
				values.put(KEY_LATITUDE,
						Double.parseDouble(cursor.getString(2)));
				values.put(KEY_LONGITUDE,
						Double.parseDouble(cursor.getString(3)));
				values.put(KEY_ACCURACY, Float.parseFloat(cursor.getString(4)));
				values.put(KEY_TIME, cursor.getLong(5));
				networkInformation.add(values);
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();
		return networkInformation;
	}

	/**
	 * Gets a single {@link Record} with the given ID from the database.
	 * 
	 * @param id
	 *            The ID of the {@link Record};
	 * @return The {@link Record}.
	 */
	public Record getRecord(int id) {
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_RECORDS+"."+KEY_ID + " = " + id;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);
		Record record = null;
		if (cursor.moveToFirst()) {
			record = createRecord(cursor);
		}

		cursor.close();
		db.close();
		// return contact
		return record;
	}

	/**
	 * Determines the number of {@link Record Records} in the database.
	 * 
	 * @return The number of {@link Record Records} in the database.
	 */
	public int getRecordCount() {
		String countQuery = "SELECT  * FROM " + TABLE_RECORDS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}

	/**
	 * Gets a single {@link Record} with the given attack id from the database.
	 * 
	 * @param attack_id
	 *            The attack id of the {@link Record};
	 * @return The {@link Record}.
	 */
	public Record getRecordOfAttackId(long attack_id) {
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_RECORDS+"."+KEY_ATTACK_ID + " = " + attack_id
				+ " GROUP BY " + TABLE_RECORDS+"."+KEY_ATTACK_ID;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		Record record = null;

		if (cursor.moveToFirst()) {
			record = createRecord(cursor);
		}
		cursor.close();

		// return record list
		db.close();
		return record;
	}

	/**
	 * Gets a representative {@link Record} for every attack identified by its
	 * attack id.
	 * 
	 * @return A ArrayList with one {@link Record Records} for each attack id in
	 *         the Database.
	 */
	public ArrayList<Record> getRecordOfEachAttack() {
		ArrayList<Record> recordList = new ArrayList<Record>();
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS  + " USING " + "(" + KEY_BSSID + ")" + " GROUP BY " + TABLE_RECORDS+ "."+KEY_ATTACK_ID;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();

		// return record list
		db.close();
		return recordList;
	}

	/**
	 * Gets a representative {@link Record} for every attack with a higher
	 * attack id than the specified.
	 * 
	 * @param attack_id
	 *            The attack id to match the query against.
	 * @return A ArrayList with one {@link Record Records} for each attack id
	 *         higher than the given.
	 */
	public ArrayList<Record> getRecordOfEachAttack(long attack_id) {
		ArrayList<Record> recordList = new ArrayList<Record>();
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS
				+ " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN "
				+ TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_RECORDS+"."+KEY_ATTACK_ID + " > " + attack_id
				+ " GROUP BY " + TABLE_RECORDS+"."+KEY_ATTACK_ID;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
		cursor.close();

		// return count
		db.close();
		return recordList;
	}

	/**
	 * Determines the smallest attack id stored in the database.
	 * 
	 * @return The smallest attack id stored in the database.
	 */
	public long getSmallestAttackId() {
		String selectQuery = "SELECT MIN(" + KEY_ATTACK_ID + ") FROM "
				+ TABLE_ATTACK_INFO;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		int result;

		if (cursor.moveToFirst()) {
			result = cursor.getInt(0);
		} else {
			result = -1;
		}
		cursor.close();
		db.close();
		return result;
	}

	/**
	 * Gets the last recorded SSID to a given BSSID.
	 * 
	 * @param bssid
	 *            The BSSID to match against.
	 * @return A String of the last SSID or null if the BSSID is not in the
	 *         database.
	 */
	public String getSSID(String bssid) {
		String selectQuery = "SELECT " + KEY_SSID + " FROM " + TABLE_BSSIDS
				+ " WHERE " + KEY_BSSID + " = " + "'" + bssid + "'";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String ssid = null;
		if (cursor.moveToFirst()) {
			ssid = cursor.getString(0);
		}
		cursor.close();
		db.close();
		return ssid;
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_BSSID_TABLE);
		db.execSQL(CREATE_ATTACK_INFO_TABLE);
		db.execSQL(CREATE_RECORD_TABLE);
		db.execSQL(CREATE_PROFILE_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORDS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ATTACK_INFO);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BSSIDS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROFILES);

		// Create tables again
		onCreate(db);
	}

	/**
	 * Retrieves all the profiles from the database
	 *
	 * @return list of profiles
	 */
	public List<Profile> getAllProfiles(){
		List<Profile> profiles = new LinkedList<Profile>();

		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_PROFILES;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Profile profile = new Profile(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(4) == 1);

				if(cursor.getInt(5) == 1){
					profile.mActivated = true;
				}

				// Adding record to list
				profiles.add(profile);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		// return record list
		return profiles;
	}

	/**
	 * Persists the given profile into the database
	 *
	 * @param profile the profile which should be persisted
	 *
	 * @return
	 */
	public long persistProfile(Profile profile){
		SQLiteDatabase db = this.getReadableDatabase();

		ContentValues values = new ContentValues();

		if(profile.mId != -1){
			values.put(KEY_PROFILE_ID, profile.mId);
		}

		values.put(KEY_PROFILE_NAME, profile.mLabel);
		values.put(KEY_PROFILE_DESCRIPTION, profile.mText);
		values.put(KEY_PROFILE_ICON, profile.mIconPath);
		values.put(KEY_PROFILE_ACTIVE, profile.mActivated);
		values.put(KEY_PROFILE_EDITABLE, profile.mEditable);

		return db.replace(TABLE_PROFILES, null, values);
	}


	public Profile getProfile(int id) {
		String selectQuery = "SELECT  * FROM " + TABLE_PROFILES + " WHERE " + TABLE_PROFILES + "." + KEY_PROFILE_ID + " = " + id;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);
		Profile profile = null;

		if (cursor.moveToFirst()) {
			profile = new Profile(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(4) == 1);

			if(cursor.getInt(5) == 1){
				profile.mActivated = true;
			}
		}

		cursor.close();
		db.close();

		// return contact
		return profile;
	}

	public void deleteProfile(int id){
		SQLiteDatabase db = this.getReadableDatabase();

		db.delete(TABLE_PROFILES, KEY_PROFILE_ID + "=?", new String[]{String.valueOf(id)});
	}

	public void updateNetworkInformation(
			ArrayList<HashMap<String, Object>> networkInformation) {
		Log.i("DatabaseHandler", "Starte updating");
		for (HashMap<String, Object> values : networkInformation) {
			updateNetworkInformation(values);
		}
	}

	public void updateNetworkInformation(
			HashMap<String, Object> networkInformation) {
		SQLiteDatabase db = this.getReadableDatabase();
		String bssid = (String) networkInformation.get(KEY_BSSID);
		String bssidQuery = "SELECT  * FROM " + TABLE_BSSIDS + " WHERE "
				+ KEY_BSSID + " = " + "'" + bssid + "'";
		Cursor cursor = db.rawQuery(bssidQuery, null);
		int result = cursor.getCount();
		if (cursor != null
				&& cursor.moveToFirst()
				&& (result <= 0 || cursor.getLong(5) < (Long) networkInformation
						.get(KEY_TIME)))
			;
		{
			ContentValues bssidValues = new ContentValues();
			bssidValues.put(KEY_BSSID, bssid);
			bssidValues
					.put(KEY_SSID, (String) networkInformation.get(KEY_SSID));
			bssidValues.put(KEY_LATITUDE,
					(double) (Double) networkInformation.get(KEY_LATITUDE));
			bssidValues.put(KEY_LONGITUDE,
					(double) (Double) networkInformation.get(KEY_LONGITUDE));
			bssidValues.put(KEY_ACCURACY,
					(float) (Float) networkInformation.get(KEY_ACCURACY));
			bssidValues.put(KEY_TIME, (Long) networkInformation.get(KEY_TIME));
			db.insertWithOnConflict(TABLE_BSSIDS, null, bssidValues,
					SQLiteDatabase.CONFLICT_REPLACE);
		}
		cursor.close();
		db.close();
	}

	/**
	 * Creates a {@link Record} from a Cursor. If the cursor does not show to a
	 * valid data structure a runtime exception is thrown.
	 * 
	 * @param cursor
	 * @return Returns the created {@link Record} .
	 */
	private Record createRecord(Cursor cursor) {
		Record record = new Record();
		record.setId(Integer.parseInt(cursor.getString(0)));
		record.setAttack_id(cursor.getLong(1));
		record.setType(TYPE.valueOf(cursor.getString(2)));
		record.setTimestamp(cursor.getLong(3));
		record.setPacket(cursor.getString(4));
		record.setProtocol(cursor.getString(5));
		record.setExternalIP(cursor.getString(6));

		record.setLocalIP(cursor.getString(7));
		record.setLocalHost(cursor.getString(8));
		record.setLocalPort(Integer.parseInt(cursor.getString(9)));

		record.setRemoteIP(cursor.getString(10));
		record.setRemoteHost(cursor.getString(11));
		record.setRemotePort(Integer.parseInt(cursor.getString(12)));

		record.setBssid(cursor.getString(13));
		record.setSsid(cursor.getString(14));
		record.setLatitude(Double.parseDouble(cursor.getString(15)));
		record.setLongitude(Double.parseDouble(cursor.getString(16)));
		record.setAccuracy(Float.parseFloat(cursor.getString(17)));
		record.setTimestampLocation(cursor.getLong(18));

		return record;
	}
}