package moe.minori.openxiaomiscale.objects;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteCantOpenDatabaseException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

import java.util.ArrayList;

/**
 * Describes database structure
 * Created by minori on 16. 8. 21.
 */
public class Database
{
	//private String DB_PATH = "/data/data/moe.minori.openxiaomiscale/databases/";
	private String DB_PATH;

	//Table name
	final public String WEIGHT_TABLE_NAME = "WEIGHT";
	final public String USER_TABLE_NAME = "USERS";

	final public String DATABASE_NAME = "openxiaomiscale.db";

	//Create table
	final public String CREATE_WEIGHT_TABLE_QUERY =
			"CREATE TABLE " + WEIGHT_TABLE_NAME + " (" +
					"UNIXTIME INTEGER PRIMARY KEY," +
					"WEIGHTVALUE REAL NOT NULL," +
					"WEIGHTUNIT INTEGER NOT NULL," +
					"USERID INTEGER," +
					"FOREIGN KEY(USERID) REFERENCES " + USER_TABLE_NAME + "(ID)" +
					")";

	final public String CREATE_USER_TABLE_QUERY =
			"CREATE TABLE " + USER_TABLE_NAME + " (" +
					"ID INTEGER PRIMARY KEY AUTOINCREMENT," +
					"USERNAME TEXT" +
					")";

	Activity activity = null;

	public Database(Activity a)
	{
		Log.d("Database", "Database constructor called!");
		activity = a;
		// Check if database file already exists

		DB_PATH = activity.getFilesDir().getPath() + "/" + DATABASE_NAME;

		if (!existsDatabase())
		{
			createDatabaseAndInitialize();
		}
	}

	public void insertElement (WeightDBElement element)
	{
		Log.d("Database", "Insert element called!");

		SQLiteDatabase database = null;

		try
		{
			database = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);

			database.setForeignKeyConstraintsEnabled(true);

			database.execSQL(
					"INSERT INTO " + WEIGHT_TABLE_NAME + " VALUES (" +
							element.unixTime + "," +
							element.weight + "," +
							element.weightUnit + "," +
							element.userID + ")"
			);
		}
		catch (SQLiteException e)
		{
			// Unhandled exception!
			Log.d("Database", "Exception in inserting weight element in database");
			e.printStackTrace();
			System.exit(-1);
		}
		finally
		{
			if ( database != null )
				database.close();
		}
	}

	/**
	 * Retrieves WeightDBElements from database, and returns ArrayList
	 *
	 * @param unixTimeFrom If -1, no limit low
	 * @param unixTimeUntil If -1, no limit high
	 * @param userId If -1, all users
	 * @return
	 */
	public ArrayList<WeightDBElement> getElements (long unixTimeFrom, long unixTimeUntil, int userId)
	{
		ArrayList<WeightDBElement> toReturn = new ArrayList<>();
		SQLiteDatabase database = null;

		try
		{
			database = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY);

			StringBuilder stringBuilder = new StringBuilder();

			stringBuilder.append("SELECT * FROM ");
			stringBuilder.append(WEIGHT_TABLE_NAME);

			if ( unixTimeFrom != -1 && unixTimeUntil == -1 ) // only low limit
			{
				stringBuilder.append(" WHERE ");

				stringBuilder.append("UNIXTIME > ");
				stringBuilder.append(unixTimeFrom);

				if ( userId != -1 )
				{
					stringBuilder.append(" AND ");

					stringBuilder.append("USERID = ");
					stringBuilder.append(userId);
				}

			}
			else if ( unixTimeFrom == -1 && unixTimeUntil != -1 ) // only high limit
			{
				stringBuilder.append(" WHERE ");

				stringBuilder.append("UNIXTIME < ");
				stringBuilder.append(unixTimeUntil);

				if ( userId != -1 )
				{
					stringBuilder.append(" AND ");

					stringBuilder.append("USERID = ");
					stringBuilder.append(userId);
				}
			}
			else if ( unixTimeFrom == -1 && unixTimeUntil == -1 ) // no limit in time
			{
				if ( userId != -1 )
				{
					stringBuilder.append(" WHERE ");

					stringBuilder.append("USERID = ");
					stringBuilder.append(userId);
				}
			}
			else if ( unixTimeFrom != -1 && unixTimeUntil != -1 ) // limit in time
			{
				stringBuilder.append(" WHERE ");

				stringBuilder.append("UNIXTIME > ");
				stringBuilder.append(unixTimeFrom);

				stringBuilder.append(" AND ");

				stringBuilder.append("UNIXTIME < ");
				stringBuilder.append(unixTimeUntil);

				if ( userId != -1 )
				{
					stringBuilder.append(" AND ");

					stringBuilder.append("USERID = ");
					stringBuilder.append(userId);
				}
			}

			Log.d("Database", "Query built: " + stringBuilder.toString());

			Cursor cursor = database.rawQuery(stringBuilder.toString(), null);

			cursor.moveToNext();

			while ( !cursor.isLast() )
			{
				long tempTime;
				float tempWeight;
				int tempWeightUnit;
				int tempUserId;

				tempTime = cursor.getLong(0);
				tempWeight = cursor.getFloat(1);
				tempWeightUnit = cursor.getInt(2);
				tempUserId = cursor.getInt(3);

				WeightDBElement tempElement = new WeightDBElement(tempTime, tempWeight, tempWeightUnit, tempUserId);

				toReturn.add(tempElement);

				cursor.moveToNext();
			}
		}
		catch (SQLiteException e)
		{
			Log.d("Database", "Exception in getElements!");
			e.printStackTrace();
			System.exit(-1);
		}
		finally
		{
			if ( database != null )
				database.close();
		}

		return toReturn;
	}

	private boolean existsDatabase()
	{
		SQLiteDatabase database = null;
		boolean isExist = true;

		try
		{
			database = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY);
		}
		catch (SQLiteCantOpenDatabaseException e)
		{
			// No database
			Log.d("Database", "Database not found!");
			isExist = false;
		}
		finally
		{
			if ( database != null )
				database.close();
		}

		return isExist;
	}

	private void createDatabaseAndInitialize()
	{
		Log.d("Database", "Database initializing...");

		SQLiteDatabase database = null;

		try
		{
			database = activity.openOrCreateDatabase(DB_PATH, Context.MODE_PRIVATE, null);

			database.setForeignKeyConstraintsEnabled(true);

			// Drop previous tables
			database.execSQL("DROP TABLE IF EXISTS " + WEIGHT_TABLE_NAME);
			database.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);

			// Create new tables
			database.execSQL(CREATE_USER_TABLE_QUERY);
			database.execSQL(CREATE_WEIGHT_TABLE_QUERY);

			// Add default user to users table, it will have id 1
			database.execSQL("INSERT INTO " + USER_TABLE_NAME + " VALUES (1,'Default')");
		}
		catch (SQLiteException e)
		{
			// Unhandled exception!
			Log.d("Database", "Exception in createDatabaseAndInitialize!");
			e.printStackTrace();
			System.exit(-1);
		}
		finally
		{
			Log.d("Database", "Database initialize finished!");

			if ( database != null )
				database.close();
		}

	}

}