package com.domandtom.antiaging;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;


public class AntiAgingItemsProvider {

	public static final String PROVIDER_NAME = "com.domandtom.provider.AntiAgingItemsProvider";
	public static final Uri CONTENT_URI = Uri.parse("content://com.domandtom.provider.AntiAgingItemsProvider/anti_aging_items");

	// TODO: Complete
	public static final String HAIR = "hair";
	public static final String SKIN = "skin";

	private static final String[] INGREDIENT_STAR 	= { "_id", "_name", "about", "category" };
	private static final String PRODUCTS_TABLE = "products";
	private static final String INGREDIENTS_TABLE = "ingredients";

	public static final int ANTI_AGING_ITEMS = 1;
	private static final int PRODUCT_ITEM_ID = 2;

	private static final UriMatcher uriMatcher;
	static
	{
		uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
		uriMatcher.addURI(PROVIDER_NAME, "anti_aging_items", ANTI_AGING_ITEMS);
		uriMatcher.addURI(PROVIDER_NAME, "anti_aging_items/#", PRODUCT_ITEM_ID);
	}

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String TAG = "AntiAgingItemsProvider";
	private static final String DATABASE_NAME = "AntiAgingItems";
	private static final int DATABASE_VERSION = 1;

	private static final String INGREDIENT_CREATE =
		"CREATE TABLE " + INGREDIENTS_TABLE + " ( " +
			"_id 		INTEGER PRIMARY KEY NOT NULL, " +
			"name		TEXT NOT NULL, " +
			"about		TEXT NOT NULL, " +
			"category	TEXT NOT NULL  " +
		"); ";

	private static final String PRODUCT_CREATE =
		"CREATE TABLE " + PRODUCTS_TABLE + " ( " +
			"_id				INTEGER PRIMARY KEY NOT NULL AUTO INCREMENT, " +
			"price				REAL, " +
			"is_favorited		NUMERIC, " +
			"name				TEXT, " +
			"about				TEXT, " +
			"category			TEXT, " +
			"available_at		TEXT, " +
			"award_name			TEXT, " +
			"award_type			TEXT, " +
			"before_after_img	TEXT, " +
			"problems			TEXT, " +
			"body_parts			TEXT, " +
			"test_time			TEXT, " +
			"test_results		TEXT, " +
			"tip				TEXT, " +
			"url				TEXT  " +
		"); ";

	private static final String INGREDIENT_PRODUCT_CREATE =
		"CREATE TABLE ingredient_product ( " +
			"product_id 				INTEGER NOT NULL, " +
			"ingredient_id 				INTEGER NOT NULL, " +
			"PRIMARY KEY (product_id, ingredient_id), " +
			"FOREIGN KEY(product_id) 	REFERENCES product(_id), " +
			"FOREIGN KEY(ingredient_id) REFERENCES ingredient(_id) " +
		"); ";

	private static final String DATABASE_CREATE =
		INGREDIENT_CREATE +
		PRODUCT_CREATE	+
		INGREDIENT_PRODUCT_CREATE;

	private final Context mCtx;


	public AntiAgingItemsProvider(Context ctx) {
		this.mCtx = ctx;
	}

	public AntiAgingItemsProvider open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}


	public long insertProduct(ContentValues values) {
		return mDb.insert(PRODUCTS_TABLE, null, values);
	}

	public long insertIngredient(ContentValues values) {
		return mDb.insert(INGREDIENTS_TABLE, null, values);
	}

	public void createLink(long product_id, long ingredient_id)	{
		ContentValues values = new ContentValues();
		values.put("ingredient_id", ingredient_id);
		values.put("product_id", product_id);
		mDb.insert("ingredient_product", null, values);
	}

	public boolean deleteRow(long rowId, String database_table) {
		return mDb.delete(database_table, "_id = " + rowId, null) > 0;
	}


	/* * * * * * * * * * */
	/* Fetch Ingredients */
	/* * * * * * * * * * */

	public List<Ingredient> fetchIngredients() {
		return fetchIngredients(null);
	}

	/**
	 * @param id							ID of row
	 */
	public List<Ingredient> fetchIngredientsById(long id)	{
		return fetchIngredients("_id = " + id);
	}

	private List<Ingredient> fetchIngredients(String whereClause)	{
		List<Ingredient> list = new ArrayList<Ingredient>();
		Cursor c = mDb.query(INGREDIENTS_TABLE, INGREDIENT_STAR, whereClause, null, null, null, null);

		if(c.moveToFirst())	{
			do	{
				list.add(new Ingredient(c.getLong(0), c.getString(1), c.getString(2), c.getString(3)));
			} while(c.moveToNext());
		}

		if (c != null && !c.isClosed()) {
	         c.close();
		}

	    return list;
	}

	/* * * * * * * * * */
	/* Fetch Products  */
	/* * * * * * * * * */

	/**
	 * @param type							AntiAgingItemsProvider.HAIR or AntiAgingItemsProvider.SKIN
	 * @throws IllegalArgumentException		If type is not SKIN or HAIR constant from this class.
	 */
	public List<Product> fetchProductsByType(String type)	{
		if(type != null && (type.equals(HAIR) || type.equals(SKIN)))
			return fetchProducts("WHERE category = " + type, "");
		else
			throw new IllegalArgumentException();
	}

	/**
	 * @param id							ID of row
	 */
	public List<Product> fetchProductsById(long id)	{
		return fetchProducts(
				"WHERE _id = " + id,
				"");
	}

	public List<Product> fetchProductByIngredientId(long id)	{
		return fetchProducts(
				"WHERE _id = " + id,

				"INNER JOIN ingredient_product AS ip" +
				"ON ip.product_id = products._id" +
				"INNER JOIN ingredients AS ingredients" +
				"ON ingredients._id = ip.ingredient_id");
	}

	private List<Product> fetchProducts(String whereClause, String joinClause) {
		List<Product> list = new ArrayList<Product>();
		Cursor c = mDb.rawQuery("SELECT * FROM products " + joinClause + " " + whereClause, null);

		if(c.moveToFirst())	{
			do	{
				list.add(new Product(c.getLong(0), c.getDouble(1), c.getInt(2) > 0, c.getString(3), c.getString(4), c.getString(5), c.getString(6),
						c.getString(7), c.getString(8), c.getString(9), c.getString(10), c.getString(11), c.getString(12), c.getString(13),
						c.getString(14), c.getString(15)));

			} while(c.moveToNext());
		}

		if (c != null && !c.isClosed()) {
	         c.close();
		}

	    return list;
	}

	public void setFavorite(long rowId, boolean favorite)	{
		mDb.execSQL("UPDATE products " +
					"SET is_favorited = " + favorite +
					"WHERE _id = " + rowId );
	}


	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
			if (!db.isReadOnly()) {
				db.execSQL("PRAGMA foreign_keys = ON;");
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}
}