/*
	Copyright (C) 2011 Lorenzo Bernardi (fastlorenzo@gmail.com)
	2010 Ben Van Daele (vandaeleben@gmail.com)

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package be.bernardi.mvforandroid.data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

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;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String	DATABASE_NAME	= "mvforandroid.db";
	private static final int	SCHEMA_VERSION	= 3;

	public final Usage			usage;
	public final Credit			credit;
	public final Topups			topups;
	public final Msisdns		msisdns;

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);

		this.usage = new Usage();
		this.credit = new Credit();
		this.topups = new Topups();
		this.msisdns = new Msisdns();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Usage.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "timestamp INTEGER NOT NULL, "
				+ "duration INTEGER, " + "type INTEGER, " + "incoming INTEGER, " + "contact TEXT, " + "cost REAL);");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Topups.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "amount REAL NOT NULL, "
				+ "method TEXT NOT NULL, " + "executed_on INTEGER NOT NULL, " + "received_on INTEGER, " + "status TEXT NOT NULL);");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Credit.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "valid_until INTEGER NULL, "
				+ "expired INTEGER NOT NULL, " + "sms INTEGER NOT NULL, " + "data INTEGER NOT NULL, " + "credits REAL NOT NULL, "
				+ "price_plan TEXT NOT NULL, " + "sms_son INTEGER NOT NULL);");
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Msisdns.TABLE_NAME + " (msisdn TEXT);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch(oldVersion) {
			case 1:
				if(newVersion < 2) {
					return;
				}
			case 2: {
				db.execSQL("DROP TABLE " + Topups.TABLE_NAME + ";");
				db.execSQL("CREATE TABLE IF NOT EXISTS " + Topups.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "amount REAL NOT NULL, "
						+ "method TEXT NOT NULL, " + "executed_on INTEGER NOT NULL, " + "received_on INTEGER, " + "status TEXT NOT NULL);");
				break;
			}
		}
	}

	private static SimpleDateFormat	apiFormat	= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	private static Date getDateFromAPI(String dateString) {
		try {
			return apiFormat.parse(dateString);
		}
		catch(ParseException e) {
			return null;
		}
	}

	public class Credit {

		private static final String	TABLE_NAME	= "credit";

		public void update(JSONObject json, boolean data_only) throws JSONException, NumberFormatException {
			Cursor query = getWritableDatabase().query(TABLE_NAME, new String[] { "_id" }, null, null, null, null, null, null);

			ContentValues values = new ContentValues();

			values.put("valid_until", getDateFromAPI(json.getString("valid_until")).getTime());
			values.put("expired", (Boolean.parseBoolean(json.getString("is_expired")) ? 1 : 0));
			if(Boolean.parseBoolean(json.getString("is_expired")) == true) {
				values.put("data", 0);
				values.put("credits", 0);
			}
			else {
				values.put("data", Long.parseLong(json.getString("data")));
			}
			values.put("price_plan", json.getString("price_plan"));
			if(!data_only) {
				values.put("credits", Double.parseDouble(json.getString("credits")));
				values.put("sms", Integer.parseInt(json.getString("sms")));
				values.put("sms_son", Integer.parseInt(json.getString("sms_super_on_net")));
			}
			else {
				values.put("credits", 0);
				values.put("sms", 0);
				values.put("sms_son", 0);
			}

			if(query.getCount() == 0) {
				// No credit info stored yet, insert a row
				getWritableDatabase().insert(TABLE_NAME, "valid_until", values);
			}
			else {
				// Credit info present already, so update it
				getWritableDatabase().update(TABLE_NAME, values, null, null);
			}

			query.close();
		}

		public long getValidUntil() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			long result;

			if(c.moveToFirst())
				result = c.getLong(1);
			else
				result = 0;

			c.close();
			return result;
		}

		public boolean isExpired() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			boolean result;

			if(c.moveToFirst())
				result = c.getLong(2) == 1;
			else
				result = true;

			c.close();
			return result;
		}

		public int getRemainingSms() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			int result;

			if(c.moveToFirst())
				result = c.getInt(3);
			else
				result = 0;

			c.close();
			return result;
		}

		public long getRemainingData() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			long result;

			if(c.moveToFirst())
				result = c.getLong(4);
			else
				result = 0;

			c.close();
			return result;
		}

		public double getRemainingCredit() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			double result;

			if(c.moveToFirst())
				result = c.getDouble(5);
			else
				result = 0;

			c.close();
			return result;
		}

		public int getPricePlan() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			int result;

			if(c.moveToFirst())
				result = c.getInt(6);
			else
				result = 0;

			c.close();
			return result;
		}

		public int getRemainingSmsSuperOnNet() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			int result;

			if(c.moveToFirst())
				result = c.getInt(7);
			else
				result = 0;

			c.close();
			return result;
		}
	}

	public class Usage {

		private static final String	TABLE_NAME		= "usage";

		public static final int		TYPE_DATA		= 0;
		public static final int		TYPE_SMS		= 1;
		public static final int		TYPE_VOICE		= 2;
		public static final int		TYPE_MMS		= 3;

		public static final int		ORDER_BY_DATE	= 1;

		public void update(JSONArray jsonArray) throws JSONException {
			getWritableDatabase().delete(TABLE_NAME, null, null);

			getWritableDatabase().beginTransaction();
			for(int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				insert(json);
			}
			getWritableDatabase().setTransactionSuccessful();
			getWritableDatabase().endTransaction();
		}

		public void insert(JSONObject json) throws JSONException {

			// "timestamp INTEGER NOT NULL, " +
			// "duration INTEGER NOT NULL, " +
			// "type INTEGER NOT NULL, " +
			// "incoming INTEGER NOT NULL, " +
			// "contact TEXT NOT NULL, " +
			// "cost REAL NOT NULL);");

			ContentValues values = new ContentValues();
			values.put("timestamp", getDateFromAPI(json.getString("start_timestamp")).getTime());
			values.put("duration", json.getLong("duration_connection"));

			if(Boolean.parseBoolean(json.getString("is_data")))
				values.put("type", TYPE_DATA);
			if(Boolean.parseBoolean(json.getString("is_sms")))
				values.put("type", TYPE_SMS);
			if(Boolean.parseBoolean(json.getString("is_voice")))
				values.put("type", TYPE_VOICE);
			if(Boolean.parseBoolean(json.getString("is_mms")))
				values.put("type", TYPE_MMS);

			values.put("incoming", (Boolean.parseBoolean(json.getString("is_incoming")) ? 1 : 0));
			values.put("contact", json.getString("to"));
			values.put("cost", Double.parseDouble(json.getString("price")));

			getWritableDatabase().insert(TABLE_NAME, "timestamp", values);
		}

		public Cursor get(long id) {
			return getReadableDatabase().query(TABLE_NAME, null, "_id=" + id, null, null, null, null);
		}

		/**
		 * Returns a cursor over the Usage table.
		 * 
		 * @param isSearch
		 *            Whether to include usage records obtained by a search, or
		 *            (xor) those obtained through auto-updating.
		 * @param order
		 *            The constant representing the field to order the cursor
		 *            by.
		 * @param ascending
		 *            Whether the order should be ascending or descending.
		 */
		public Cursor get(int order, boolean ascending) {
			String orderBy = null;
			switch(order) {
				case ORDER_BY_DATE:
					orderBy = "timestamp " + (ascending ? "asc" : "desc");
			}
			return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, orderBy);
		}

		public long getTimestamp(Cursor c) {
			return c.getLong(1);
		}

		public long getduration(Cursor c) {
			return c.getLong(2);
		}

		public int getType(Cursor c) {
			return c.getInt(3);
		}

		public boolean isIncoming(Cursor c) {
			return c.getInt(4) == 1;
		}

		public String getContact(Cursor c) {
			return c.getString(5);
		}

		public double getCost(Cursor c) {
			return c.getDouble(6);
		}

	}

	public class Topups {

		private static final String	TABLE_NAME	= "topups";

		public void update(JSONArray jsonArray, boolean b) throws JSONException {
			getWritableDatabase().delete(TABLE_NAME, null, null);

			for(int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				insert(json);
			}
		}

		private void insert(JSONObject json) throws JSONException {
			ContentValues values = new ContentValues();

			values.put("amount", Double.parseDouble(json.getString("amount")));
			values.put("method", json.getString("method"));
			values.put("executed_on", getDateFromAPI(json.getString("executed_on")).getTime());
			Log.d("MVFA", json.getString("payment_received_on"));
			if(!json.getString("payment_received_on").equals("null"))
				values.put("received_on", getDateFromAPI(json.getString("payment_received_on")).getTime());
			values.put("status", json.getString("status"));

			getWritableDatabase().insert(TABLE_NAME, "timestamp", values);
		}

		public Cursor getAll() {
			return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
		}

		public double getAmount(Cursor c) {
			return c.getDouble(1);
		}

		public String getMethod(Cursor c) {
			return c.getString(2);
		}

		public long getExecutedOn(Cursor c) {
			return c.getLong(3);
		}

		public long getReceivedOn(Cursor c) {
			return c.getLong(4);
		}

		public String getStatus(Cursor c) {
			return c.getString(5);
		}

	}

	public class Msisdns {
		private static final String	TABLE_NAME	= "msisdns";

		public void update(JSONArray jsonArray) throws JSONException {
			getWritableDatabase().delete(TABLE_NAME, null, null);
			for(int i = 0; i < jsonArray.length(); i++) {
				String msisdn = jsonArray.getString(i);
				insert(msisdn);
			}
		}

		private void insert(String msisdn) throws JSONException {
			ContentValues values = new ContentValues();
			values.put("msisdn", msisdn);
			getWritableDatabase().insert(TABLE_NAME, "msisdn", values);
		}

		public Cursor getAll() {
			return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
		}

		public String[] getMsisdnList() {
			Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
			String[] result = null;
			int i = 0;
			if(c.moveToFirst()) {
				do {
					i++;
				}
				while(c.moveToNext());
				result = new String[i];
				c.moveToFirst();
				i = 0;
				do {
					if(c.getString(0) != null) {
						result[i] = c.getString(0);
						i++;
					}
				}
				while(c.moveToNext());
			}
			c.close();
			return result;
		}
	}
}
