package de.tudarmstadt.informatik.hostage.persistence;

import java.util.ArrayList;
import java.util.HashMap;

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 de.tudarmstadt.informatik.hostage.commons.HelperUtils;
import de.tudarmstadt.informatik.hostage.logging.AttackRecord;
import de.tudarmstadt.informatik.hostage.logging.MessageRecord;
import de.tudarmstadt.informatik.hostage.logging.NetworkRecord;
import de.tudarmstadt.informatik.hostage.logging.SyncInfoRecord;
import de.tudarmstadt.informatik.hostage.persistence.HostageDBContract.AttackEntry;
import de.tudarmstadt.informatik.hostage.persistence.HostageDBContract.NetworkEntry;
import de.tudarmstadt.informatik.hostage.persistence.HostageDBContract.PacketEntry;
import de.tudarmstadt.informatik.hostage.persistence.HostageDBContract.SyncDeviceEntry;
import de.tudarmstadt.informatik.hostage.persistence.HostageDBContract.SyncInfoEntry;

public class HostageDBOpenHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "hostage.db";
	private static final int DATABASE_VERSION = 1;
	private Context context;

	static {
		StringBuilder networkSQLBuilder = new StringBuilder("CREATE TABLE ").append(NetworkEntry.TABLE_NAME).append("(");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_BSSID).append(" TEXT PRIMARY KEY,");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_SSID).append(" TEXT,");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_LATITUDE).append(" INTEGER,");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_LONGITUDE).append(" INTEGER,");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_ACCURACY).append(" INTEGER,");
		networkSQLBuilder.append(NetworkEntry.COLUMN_NAME_GEO_TIMESTAMP).append(" INTEGER");
		networkSQLBuilder.append(")");
		SQL_CREATE_NETWORK_ENTRIES = networkSQLBuilder.toString();

		StringBuilder attackSQLBuilder = new StringBuilder("CREATE TABLE ").append(AttackEntry.TABLE_NAME).append("(");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_ATTACK_ID).append(" INTEGER PRIMARY KEY,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_PROTOCOL).append(" TEXT,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_EXTERNAL_IP).append(" TEXT,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_LOCAL_IP).append(" BLOB,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_LOCAL_PORT).append(" INTEGER,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_REMOTE_IP).append(" BLOB,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_REMOTE_PORT).append(" INTEGER,");
		attackSQLBuilder.append(AttackEntry.COLUMN_NAME_BSSID).append(" TEXT,");
		attackSQLBuilder.append(String.format("FOREIGN KEY(%s) REFERENCES %s(%s)", AttackEntry.COLUMN_NAME_BSSID, NetworkEntry.TABLE_NAME,
				NetworkEntry.COLUMN_NAME_BSSID));
		attackSQLBuilder.append(")");
		SQL_CREATE_ATTACK_ENTRIES = attackSQLBuilder.toString();

		StringBuilder packetSQLBuilder = new StringBuilder("CREATE TABLE ").append(PacketEntry.TABLE_NAME).append("(");
		packetSQLBuilder.append(PacketEntry.COLUMN_NAME_ID).append(" INTEGER NOT NULL,");
		packetSQLBuilder.append(PacketEntry.COLUMN_NAME_ATTACK_ID).append(" INTEGER NOT NULL,");
		packetSQLBuilder.append(PacketEntry.COLUMN_NAME_TYPE).append(" TEXT,");
		packetSQLBuilder.append(PacketEntry.COLUMN_NAME_PACKET_TIMESTAMP).append(" INTEGER,");
		packetSQLBuilder.append(PacketEntry.COLUMN_NAME_PACKET).append(" TEXT,");
		packetSQLBuilder.append(String.format("PRIMARY KEY(%s,%s)", PacketEntry.COLUMN_NAME_ID, PacketEntry.COLUMN_NAME_ATTACK_ID));
		packetSQLBuilder.append(String.format("FOREIGN KEY(%s) REFERENCES %s(%s)", PacketEntry.COLUMN_NAME_ATTACK_ID, AttackEntry.TABLE_NAME,
				AttackEntry.COLUMN_NAME_ATTACK_ID));
		packetSQLBuilder.append(")");
		SQL_CREATE_PACKET_ENTRIES = packetSQLBuilder.toString();
		
		StringBuilder syncDevicesSQLBuilder = new StringBuilder("CREATE TABLE ").append(SyncDeviceEntry.TABLE_NAME).append("(");
		syncDevicesSQLBuilder.append(SyncDeviceEntry.COLUMN_NAME_DEVICE_ID).append(" TEXT PRIMARY KEY,");
		syncDevicesSQLBuilder.append(SyncDeviceEntry.COLUMN_NAME_DEVICE_TIMESTAMP).append(" INTEGER");
		syncDevicesSQLBuilder.append(")");
		SQL_CREATE_SYNC_DEVICES_ENTRIES = syncDevicesSQLBuilder.toString();
		
		StringBuilder syncInfoSQLBuilder = new StringBuilder("CREATE TABLE ").append(SyncInfoEntry.TABLE_NAME).append("(");
		syncInfoSQLBuilder.append(SyncInfoEntry.COLUMN_NAME_DEVICE_ID).append(" TEXT,");
		syncInfoSQLBuilder.append(SyncInfoEntry.COLUMN_NAME_BSSID).append(" TEXT,");
		syncInfoSQLBuilder.append(SyncInfoEntry.COLUMN_NAME_NUMBER_ATTACKS).append(" INTEGER,");
		syncInfoSQLBuilder.append(SyncInfoEntry.COLUMN_NAME_NUMBER_PORTSCANS).append(" INTEGER,");
		syncInfoSQLBuilder.append(String.format("PRIMARY KEY(%s,%s)", SyncInfoEntry.COLUMN_NAME_DEVICE_ID, SyncInfoEntry.COLUMN_NAME_BSSID));
		syncInfoSQLBuilder.append(String.format("FOREIGN KEY(%s) REFERENCES %s(%s)", SyncInfoEntry.COLUMN_NAME_BSSID, NetworkEntry.TABLE_NAME,
				NetworkEntry.COLUMN_NAME_BSSID));
		syncInfoSQLBuilder.append(")");
		SQL_CREATE_SYNC_INFO_ENTRIES = syncInfoSQLBuilder.toString();
		

	}

	private static final String SQL_CREATE_NETWORK_ENTRIES;
	private static final String SQL_CREATE_ATTACK_ENTRIES;
	private static final String SQL_CREATE_PACKET_ENTRIES;
	private static final String SQL_CREATE_SYNC_DEVICES_ENTRIES;
	private static final String SQL_CREATE_SYNC_INFO_ENTRIES;

	private static final String SQL_DELETE_PACKET_ENTRIES = "DROP TABLE IF EXISTS " + PacketEntry.TABLE_NAME;
	private static final String SQL_DELETE_ATTACK_ENTRIES = "DROP TABLE IF EXISTS " + AttackEntry.TABLE_NAME;
	private static final String SQL_DELETE_NETWORK_ENTRIES = "DROP TABLE IF EXISTS " + NetworkEntry.TABLE_NAME;
	private static final String SQL_DELETE_SYNC_DEVICES_ENTRIES = "DROP TABLE IF EXISTS " + SyncDeviceEntry.TABLE_NAME;
	private static final String SQL_DELETE_SYNC_INFO_ENTRIES = "DROP TABLE IF EXISTS " + SyncInfoEntry.TABLE_NAME;

	public HostageDBOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_NETWORK_ENTRIES);
		db.execSQL(SQL_CREATE_ATTACK_ENTRIES);
		db.execSQL(SQL_CREATE_PACKET_ENTRIES);
		db.execSQL(SQL_CREATE_SYNC_DEVICES_ENTRIES);
		db.execSQL(SQL_CREATE_SYNC_INFO_ENTRIES);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(SQL_DELETE_SYNC_INFO_ENTRIES);
		db.execSQL(SQL_DELETE_PACKET_ENTRIES);
		db.execSQL(SQL_DELETE_ATTACK_ENTRIES);
		db.execSQL(SQL_DELETE_NETWORK_ENTRIES);
		db.execSQL(SQL_DELETE_SYNC_DEVICES_ENTRIES);
		onCreate(db);
	}

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

		ContentValues recordValues = new ContentValues();
		recordValues.put(PacketEntry.COLUMN_NAME_ID, record.getId()); // Log Message Number
		recordValues.put(PacketEntry.COLUMN_NAME_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		recordValues.put(PacketEntry.COLUMN_NAME_TYPE, record.getType().name()); // Log Type
		recordValues.put(PacketEntry.COLUMN_NAME_PACKET_TIMESTAMP, record.getTimestamp()); // Log Timestamp
		recordValues.put(PacketEntry.COLUMN_NAME_PACKET, record.getPacket()); // Log Packet

		// Inserting Rows
		db.insert(PacketEntry.TABLE_NAME, null, recordValues);
		db.close(); // Closing database connection
	}
	
	/**
	 * Adds a given {@link AttackRecord} to the database.
	 * 
	 * @param record
	 *            The added {@link AttackRecord} .
	 */
	public void addAttackRecord(AttackRecord record) {
		Log.i("DBHelper", "Add Attack Record with id: " + record.getAttack_id());
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues attackValues = new ContentValues();
		attackValues.put(AttackEntry.COLUMN_NAME_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		attackValues.put(AttackEntry.COLUMN_NAME_PROTOCOL, record.getProtocol().toString());
		attackValues.put(AttackEntry.COLUMN_NAME_EXTERNAL_IP, record.getExternalIP());
		attackValues.put(AttackEntry.COLUMN_NAME_LOCAL_IP, record.getLocalIP()); // Log Local IP
		attackValues.put(AttackEntry.COLUMN_NAME_LOCAL_PORT, record.getLocalPort());
		attackValues.put(AttackEntry.COLUMN_NAME_REMOTE_IP, record.getRemoteIP()); // Log Remote IP
		attackValues.put(AttackEntry.COLUMN_NAME_REMOTE_PORT, record.getRemotePort()); // Log Remote Port
		attackValues.put(AttackEntry.COLUMN_NAME_BSSID, record.getBssid());
		

		// Inserting Rows
		db.insertWithOnConflict(AttackEntry.TABLE_NAME, null, attackValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.close(); // Closing database connection
	}
	
	public void updateSyncAttackCounter(AttackRecord record){
		
		SQLiteDatabase db = this.getWritableDatabase();

		String mac = HelperUtils.getMacAdress(context);
		ContentValues syncDeviceValues = new ContentValues();
		syncDeviceValues.put(SyncDeviceEntry.COLUMN_NAME_DEVICE_ID, mac);
		syncDeviceValues.put(SyncDeviceEntry.COLUMN_NAME_DEVICE_TIMESTAMP, System.currentTimeMillis());
		
		String query = "SELECT  * FROM " + SyncInfoEntry.TABLE_NAME + 
					  " WHERE " + SyncInfoEntry.COLUMN_NAME_DEVICE_ID + " = ? " +
					   "AND " + SyncInfoEntry.COLUMN_NAME_BSSID + " = ?";
		Cursor cursor = db.rawQuery(query, new String[] {mac, record.getBssid()});
		long attackCount = 0;
		long portscanCount = 0;
		if (cursor.moveToFirst()){
			attackCount = cursor.getLong(2);
			portscanCount = cursor.getLong(3);
		}			
		
		if("PORTSCAN".equals(record.getProtocol())){
			portscanCount++;
		}else { attackCount++; }
			
		Log.i("DBHelper", "Update number of attack: " + attackCount);
		
		ContentValues synInfoValues = new ContentValues();
		synInfoValues.put(SyncInfoEntry.COLUMN_NAME_BSSID, record.getBssid());
		synInfoValues.put(SyncInfoEntry.COLUMN_NAME_DEVICE_ID, mac);
		synInfoValues.put(SyncInfoEntry.COLUMN_NAME_NUMBER_ATTACKS, attackCount);
		synInfoValues.put(SyncInfoEntry.COLUMN_NAME_NUMBER_PORTSCANS, portscanCount);

		// Inserting Rows
		db.insertWithOnConflict(SyncInfoEntry.TABLE_NAME, null, synInfoValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.insertWithOnConflict(SyncDeviceEntry.TABLE_NAME, null, syncDeviceValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.close(); // Closing database connection
	}
	

	/**
	 * 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 " + NetworkEntry.TABLE_NAME + " WHERE " + NetworkEntry.COLUMN_NAME_BSSID + " = ?";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, new String[] {BSSID});
		int result = cursor.getCount();
		cursor.close();
		db.close();
		return result > 0;
	}

	/**
	 * 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) {
		if(BSSID == null || protocol == null){
			return false;
		}
		String countQuery = "SELECT  * FROM " + AttackEntry.TABLE_NAME + " NATURAL JOIN " + NetworkEntry.TABLE_NAME + " WHERE "
				+ AttackEntry.COLUMN_NAME_PROTOCOL + " = ? AND " + NetworkEntry.COLUMN_NAME_BSSID + " = ?";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, new String[]{protocol, BSSID});
		int result = cursor.getCount();
		cursor.close();
		db.close();
		return result > 0;
	}


	/**
	 * Returns a String array with all BSSIDs stored in the database.
	 * 
	 * @return String[] of all recorded BSSIDs.
	 */
	public String[] getAllBSSIDS() {
		String selectQuery = "SELECT  * FROM " + NetworkEntry.TABLE_NAME;
		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;
	}

	/**
	 * Determines the number of different attacks in the database.
	 * 
	 * @return The number of different attacks in the database.
	 */
	public int getAttackCount() {
		SQLiteDatabase db = this.getReadableDatabase();
		String countQuery = "SELECT  * FROM " + AttackEntry.TABLE_NAME + 
				           " WHERE " + AttackEntry.COLUMN_NAME_PROTOCOL + " <> ?";		
		Cursor cursor = db.rawQuery(countQuery, new String[]{"PORTSCAN"});
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}
	
	/**
	 * Determines the number of different recorded attacks in a specific access point since the given attack_id.
	 * The given attack_id is not included.
	 * @param attack_id  The attack id to match the query against.
	 * @param bssid  The BSSID of the access point. 
	 * @return The number of different attacks in the database since the given attack_id.
	 */
	public int getAttackCount(int attack_id, String bssid) {
		SQLiteDatabase db = this.getReadableDatabase();
		String countQuery = "SELECT * FROM " + AttackEntry.TABLE_NAME + 
				           " WHERE "+ AttackEntry.COLUMN_NAME_PROTOCOL + " <> ? " +
				           	"AND " + AttackEntry.COLUMN_NAME_ATTACK_ID + " > ? " +
				           	"AND " + AttackEntry.COLUMN_NAME_BSSID + " = ?";			
		String[] selectArgs = new String[]{"PORTSCAN", attack_id + "", bssid};
		Cursor cursor = db.rawQuery(countQuery, selectArgs);
		int result = cursor.getCount();
		cursor.close();

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

	/**
	 * Determines the number of different attacks 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 attacks in the database.
	 */
	public int getAttackPerProtocolCount(String protocol) {
		SQLiteDatabase db = this.getReadableDatabase();
		String countQuery = "SELECT * FROM " + AttackEntry.TABLE_NAME + 
		                   " WHERE "+ AttackEntry.COLUMN_NAME_PROTOCOL + " = ? ";	
		Cursor cursor = db.rawQuery(countQuery, new String[]{protocol});
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}
	
	/**
	 * Determines the number of  attacks for a specific protocol in
	 * the database since the given attack_id.
	 * 
	 * @param protocol
	 *            The String representation of the
	 *            {@link de.tudarmstadt.informatik.hostage.protocol.Protocol
	 *            Protocol}
	 * @param attack_id  The attack id to match the query against.
	 * @return The number of different attacks in the database since the given attack_id.
	 */
	public int getAttackPerProtocolCount(String protocol, int attack_id) {
		SQLiteDatabase db = this.getReadableDatabase();
		String countQuery = "SELECT * FROM " + AttackEntry.TABLE_NAME + 
                		   " WHERE "+ AttackEntry.COLUMN_NAME_PROTOCOL + " = ? " +
                		    "AND " + AttackEntry.COLUMN_NAME_ATTACK_ID + " > ? ";	
		Cursor cursor = db.rawQuery(countQuery, new String[]{protocol, attack_id + ""});
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}
	
	/**
	 * Determines the number of recorded attacks for a specific protocol and accesss point since the given attack_id.
	 * 
	 * @param protocol
	 *            The String representation of the
	 *            {@link de.tudarmstadt.informatik.hostage.protocol.Protocol
	 *            Protocol}
	 * @param attack_id  The attack id to match the query against.
	 * @param bssid The BSSID of the access point. 
	 * @return The number of different attacks in the database since the given attack_id.
	 */
	public int getAttackPerProtocolCount(String protocol, int attack_id, String bssid) {
		SQLiteDatabase db = this.getReadableDatabase();
		String countQuery = "SELECT * FROM " + AttackEntry.TABLE_NAME + 
     		   			   " WHERE "+ AttackEntry.COLUMN_NAME_PROTOCOL + " = ? " +
     		   			    "AND " + AttackEntry.COLUMN_NAME_ATTACK_ID + " > ? " +	
						 	"AND " + AttackEntry.COLUMN_NAME_BSSID + " = ?";
		Cursor cursor = db.rawQuery(countQuery, new String[]{protocol, attack_id + "", bssid});
		int result = cursor.getCount();
		cursor.close();

		// return count
		db.close();
		return result;
	}
	
	/**
	 * Determines the number of portscans stored in the database.
	 * 
	 * @return The number of portscans stored in the database.
	 */
	public int getPortscanCount() {
		return getAttackPerProtocolCount("PORTSCAN");
	}	
	
	/**
	 * Determines the number of recorded portscans since the given attack_id.
	 * @param attack_id  The attack id to match the query against.
	 * @return The number of portscans stored in the database since the given attack_id.
	 */
	public int getPortscanCount(int attack_id) {
		return getAttackPerProtocolCount("PORTSCAN", attack_id);
	}	
	
	/**
	 * Determines the number of recorded portscans in a specific access point since the given attack_id.
	 * @param attack_id  The attack id to match the query against.
	 * @param bssid The BSSID of the access point. 
	 * @return The number of portscans stored in the database since the given attack_id.
	 */
	public int getPortscanCount(int attack_id, String bssid) {
		return getAttackPerProtocolCount("PORTSCAN", attack_id, bssid);
	}	


	/**
	 * 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 " + PacketEntry.TABLE_NAME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int result = cursor.getCount();
		cursor.close();

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

	/**
	 * Returns the {@link AttackRecord} with the given attack id from the database.
	 * 
	 * @param attack_id
	 *            The attack id of the {@link Record};
	 * @return The {@link Record}.
	 */
	public AttackRecord getRecordOfAttackId(long attack_id) {
		String selectQuery = "SELECT  * FROM " + AttackEntry.TABLE_NAME + " WHERE " + AttackEntry.COLUMN_NAME_ATTACK_ID + " = " + attack_id;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		AttackRecord record = null;

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

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

	/**
	 * Gets a {@link AttackRecord} for every attack identified by its attack id.
	 * 
	 * @return A ArrayList with a {@link AttackRecord} for each attack id in the Database.
	 */
	public ArrayList<AttackRecord> getRecordOfEachAttack() {
		ArrayList<AttackRecord> recordList = new ArrayList<AttackRecord>();
		String selectQuery = "SELECT  * FROM " + AttackEntry.TABLE_NAME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

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

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

	/**
	 * Gets a AttackRecord 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 AttackRecord} for each attack id
	 *         higher than the given.
	 */
	public ArrayList<AttackRecord> getRecordOfEachAttack(long attack_id) {
		ArrayList<AttackRecord> recordList = new ArrayList<AttackRecord>();
		String selectQuery = "SELECT  * FROM " + AttackEntry.TABLE_NAME + " WHERE " + AttackEntry.COLUMN_NAME_ATTACK_ID + " > " + attack_id;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

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

		// return count
		db.close();
		return recordList;
	}
	
	/**
	 * 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(" + AttackEntry.COLUMN_NAME_ATTACK_ID + ") FROM " + AttackEntry.TABLE_NAME;
		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;
	}

	/**
	 * 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(" + AttackEntry.COLUMN_NAME_ATTACK_ID + ") FROM " + AttackEntry.TABLE_NAME;
		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 " + NetworkEntry.COLUMN_NAME_SSID + " FROM " + NetworkEntry.TABLE_NAME + " WHERE " + NetworkEntry.COLUMN_NAME_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;
	}
	
	public ArrayList<NetworkRecord> getNetworkInformation() {
		String selectQuery = "SELECT  * FROM " + NetworkEntry.TABLE_NAME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		ArrayList<NetworkRecord> networkInformation = new ArrayList<NetworkRecord>();

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				NetworkRecord record = new NetworkRecord();
				record.setBssid(cursor.getString(0));
				record.setSsid(cursor.getString(1));
				record.setLatitude(Double.parseDouble(cursor.getString(2)));
				record.setLongitude(Double.parseDouble(cursor.getString(3)));
				record.setAccuracy(Float.parseFloat(cursor.getString(4)));
				record.setTimestampLocation(cursor.getLong(5));
				networkInformation.add(record);
			} while (cursor.moveToNext());
		}

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

	public void updateNetworkInformation(ArrayList<NetworkRecord> networkInformation) {
		Log.i("DatabaseHandler", "Starte updating");
		for (NetworkRecord record : networkInformation) {
			updateNetworkInformation(record);
		}
	}

	public void updateNetworkInformation(NetworkRecord record) {
		SQLiteDatabase db = this.getReadableDatabase();
		String bssid = record.getBssid();
		String bssidQuery = "SELECT  * FROM " +  NetworkEntry.TABLE_NAME + " WHERE " +  NetworkEntry.COLUMN_NAME_BSSID + " = ?";
		Cursor cursor = db.rawQuery(bssidQuery, new String[] {bssid});
		if (!cursor.moveToFirst() || cursor.getLong(5) < record.getTimestampLocation()){
			ContentValues bssidValues = new ContentValues();
			bssidValues.put(NetworkEntry.COLUMN_NAME_BSSID, bssid);
			bssidValues.put(NetworkEntry.COLUMN_NAME_SSID, record.getSsid());
			bssidValues.put(NetworkEntry.COLUMN_NAME_LATITUDE, record.getLatitude());
			bssidValues.put(NetworkEntry.COLUMN_NAME_LONGITUDE, record.getLongitude());
			bssidValues.put(NetworkEntry.COLUMN_NAME_ACCURACY, record.getAccuracy());
			bssidValues.put(NetworkEntry.COLUMN_NAME_GEO_TIMESTAMP, record.getTimestampLocation());
			db.insertWithOnConflict(NetworkEntry.TABLE_NAME, null, bssidValues, SQLiteDatabase.CONFLICT_REPLACE);
		}
		cursor.close();
		db.close();
	}
	
	
	public void updateSyncDevices(HashMap<String, Long> devices){
		SQLiteDatabase db = this.getReadableDatabase();		
		for(String key : devices.keySet()){
			ContentValues deviceValues = new ContentValues();
			deviceValues.put(SyncDeviceEntry.COLUMN_NAME_DEVICE_ID, key);
			deviceValues.put(SyncDeviceEntry.COLUMN_NAME_DEVICE_TIMESTAMP, devices.get(key));
			db.insertWithOnConflict(SyncDeviceEntry.TABLE_NAME, null, deviceValues, SQLiteDatabase.CONFLICT_REPLACE);
		}
		db.close();
	}
	
	/**
	 * Returns a HashMap of all devices that were previously synchronized with.
	 * @return HashMap containing device id's and the last synchronization timestamp.
	 */
	public HashMap<String, Long> getSyncDevices(){
		SQLiteDatabase db = this.getReadableDatabase();
		HashMap<String, Long> devices = new HashMap<String, Long>();
		
		String query = "SELECT  * FROM " + SyncDeviceEntry.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		
		if (cursor.moveToFirst()) {
			do {
				devices.put(cursor.getString(0), cursor.getLong(1));
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return devices;
	}
	
	/**
	 * Returns a ArrayList containing all information stored in the SyncInfo table.
	 * @return ArrayList<SyncInfo>
	 */
	public ArrayList<SyncInfoRecord> getSyncInfo(){
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<SyncInfoRecord> syncInfo = new ArrayList<SyncInfoRecord>();
		
		String query = "SELECT  * FROM " + SyncInfoEntry.TABLE_NAME;
		Cursor cursor = db.rawQuery(query, null);
		
		if (cursor.moveToFirst()) {
			do {
				SyncInfoRecord info = new SyncInfoRecord();
				info.setDeviceID(cursor.getString(0));
				info.setBSSID(cursor.getString(1));
				info.setNumber_of_attacks(cursor.getLong(2));
				info.setNumber_of_portscans(cursor.getLong(3));
				syncInfo.add(info);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return syncInfo;
	}	
	
	
	public void updateSyncInfo(ArrayList<SyncInfoRecord> syncInfo){
		for(SyncInfoRecord info : syncInfo){
			updateSyncInfo(info);
		}
	}
	
	public void updateSyncInfo(SyncInfoRecord syncInfo){
		SQLiteDatabase db = this.getReadableDatabase();
		ContentValues syncValues = new ContentValues();
		syncValues.put(SyncInfoEntry.COLUMN_NAME_BSSID, syncInfo.getBSSID());
		syncValues.put(SyncInfoEntry.COLUMN_NAME_DEVICE_ID, syncInfo.getDeviceID());
		syncValues.put(SyncInfoEntry.COLUMN_NAME_NUMBER_ATTACKS, syncInfo.getNumber_of_attacks());
		syncValues.put(SyncInfoEntry.COLUMN_NAME_NUMBER_PORTSCANS, syncInfo.getNumber_of_portscans());
		db.insertWithOnConflict(SyncInfoEntry.TABLE_NAME, null, syncValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.close();
	}
	
	/**
	 * Deletes a device with given id from the device {@link SyncDeviceEntry.TABLE_NAME} and also all data captured by this device in  {@link SyncInfoEntry.TABLE_NAME}
	 * @param device_id The id of the device that is to be deleted.
	 */
	public void clearSyncInfos(){
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(SyncDeviceEntry.TABLE_NAME, null, null);
		db.delete(SyncInfoEntry.TABLE_NAME, null, null);
		db.close();
	}
	
	
	/**
	 * Deletes all records from {@link #PacketEntry.TABLE_NAME}.
	 */
	public void clearData() {
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(PacketEntry.TABLE_NAME, null, null);
		db.delete(AttackEntry.TABLE_NAME, null, null);
		db.close();
	}

	/**
	 * Deletes all records from {@link #PacketEntry.TABLE_NAME} with a specific BSSID.
	 * 
	 * @param bssid
	 *            The BSSID to match against.
	 */
	public void deleteByBSSID(String bssid) {
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(NetworkEntry.TABLE_NAME, NetworkEntry.COLUMN_NAME_BSSID + " = ?", new String[] { bssid });
		db.delete(AttackEntry.TABLE_NAME, AttackEntry.COLUMN_NAME_BSSID + " = ?", new String[] { bssid });
		db.close();
	}
	
	/**
	 * Deletes all records from {@link #PacketEntry.TABLE_NAME} 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 " + PacketEntry.TABLE_NAME + " WHERE " + PacketEntry.COLUMN_NAME_PACKET_TIMESTAMP + " < " + date;
		db.execSQL(deleteQuery);
		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 MessageRecord createMessageRecord(Cursor cursor) {
		MessageRecord record = new MessageRecord();
		record.setId(Integer.parseInt(cursor.getString(0)));
		record.setAttack_id(cursor.getLong(1));
		record.setType(MessageRecord.TYPE.valueOf(cursor.getString(2)));
		record.setTimestamp(cursor.getLong(3));
		record.setPacket(cursor.getString(4));

		return record;
	}
	
	/**
	 * Creates a {@link AttackRecord} 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 AttackRecord createAttackRecord(Cursor cursor) {
		AttackRecord record = new AttackRecord();
		record.setAttack_id(cursor.getLong(0));
		record.setProtocol(cursor.getString(1));
		record.setExternalIP(cursor.getString(2));
		record.setLocalIP(cursor.getString(3));
		record.setLocalPort(Integer.parseInt(cursor.getString(4)));
		record.setRemoteIP(cursor.getString(5));
		record.setRemotePort(Integer.parseInt(cursor.getString(6)));
		record.setBssid(cursor.getString(11));

		return record;
	}

}