package com.example.drinkingapp;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

public class DatabaseHandler extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "QuestionDB";
	
	private static final String TABLE_MULTI = "multichoice";
	private static final String TABLE_QUES = "questions";
	
	// Multi Choice column names
	private static final String MULTI_KEY_ID = "id";
	private static final String MULTI_KEY_QUES_ID = "question_id";
	private static final String MULTI_KEY_VALUE = "value";
	
	// Question column names
	private static final String QUES_KEY_ID = "id";
	private static final String QUES_KEY_VAR = "variable";
	private static final String QUES_KEY_DAY = "day";
	private static final String QUES_KEY_MONTH = "month";
	private static final String QUES_KEY_YEAR = "year";
	private static final String QUES_KEY_DAY_WEEK = "day_week";
	private static final String QUES_KEY_TIME = "time";
	private static final String QUES_KEY_TYPE= "type";
	private static final String QUES_KEY_VALUE = "value";
	
	//values for the question types the db can handle
	private static final String text_type = "text";
	private static final String multi_type = "multi";
	private static final String slider_type = "slider";
	private static final String radio_type = "radio";
	private static final String number_type = "number";
	
	public DatabaseHandler(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//create Multi Question Table
		String CREATE_MULTI = "CREATE TABLE multichoice (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"question_id INTEGER, " + 
				"value TEXT ) ";
		//create the mutli-answer table
		db.execSQL(CREATE_MULTI);
		
		//create Question Table
		String CREATE_QUES = "CREATE TABLE questions (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT," +
				"variable TEXT, " +
				"month INTEGER, " +
				"day INTEGER, "+
				"year INTEGER, " + 
				"day_week TEXT, " + 
				"time TEXT, " + 
				"type TEXT, " +
				"value TEXT )";

		//create the question table
		db.execSQL(CREATE_QUES);
	}
	
	public void addTextQuestion(DatabaseStore store){
		//get reference to the database
		SQLiteDatabase db = this.getWritableDatabase();
		
		//create the ContentValues to hold column values
		ContentValues values = new ContentValues();
		values.put(QUES_KEY_DAY, store.day);
		values.put(QUES_KEY_MONTH, store.month);
		values.put(QUES_KEY_YEAR, store.year);
		values.put(QUES_KEY_TIME, store.time);
		values.put(QUES_KEY_DAY_WEEK, store.day_week);
		values.put(QUES_KEY_VALUE, store.value);
		values.put(QUES_KEY_TYPE, store.type);
		values.put(QUES_KEY_VAR, store.variable);
		
		//insert the values into the table
		db.insert(TABLE_QUES, null, values);
		
		//close the database
		db.close();
	}
	
	private List<DatabaseStore> handleCursor(Cursor cursor) throws ParseException{
		//check to see if our query returned values
		DatabaseStore store = null;
		ArrayList<DatabaseStore> store_list = new ArrayList<DatabaseStore>();
		if(cursor.moveToFirst()){
			do{
				String variable = cursor.getString(1);
				Integer month = cursor.getInt(2);
				Integer day = cursor.getInt(3);
				Integer year = cursor.getInt(4);
				String time = cursor.getString(6);
				String type = cursor.getString(7);
				String value = cursor.getString(8);
				Date date = DatabaseStore.GetDate(month, day, year, time);
				store = DatabaseStore.FromDatabase(variable, value, date, type); 
				store_list.add(store);
			} while (cursor.moveToNext());
		}else{
			return null;
		}
		return store_list;		
	}
	
	public List<DatabaseStore> getAllVarValue(String variable) throws ParseException{
		//Get reference to the database
		SQLiteDatabase db = this.getWritableDatabase();
		String query = "Select * FROM " + TABLE_QUES + "WHERE " + 
				QUES_KEY_VAR + " = " + variable + ", ";
		Cursor cursor = db.rawQuery(query, null);
		return handleCursor(cursor);
	}
	
	public List<DatabaseStore> getVarValuesForDay(String variable, Integer month, 
			Integer day, Integer year) throws ParseException{
		//get reference to the database
		SQLiteDatabase db = this.getWritableDatabase();
		String query = "Select * FROM " + TABLE_QUES + "WHERE " + 
				QUES_KEY_VAR + " = " + variable + ", " +
				QUES_KEY_DAY + " = " + day + ", " + 
				QUES_KEY_MONTH + " = " + month + ", " + 
				QUES_KEY_YEAR + " = " + year;
		Cursor cursor = db.rawQuery(query, null);
		return handleCursor(cursor);
	}
	
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}

@@ -4767,32 +4767,498 @@
 r(cursor);%0A%09%7D%0A%09%0A
+%09public List%3CDatabaseStore%3E getVarValuesForMonth(String variable, Integer month, Integer year) throws ParseException%7B%0A%09%09//get reference to the database%0A%09%09SQLiteDatabase db = this.getWritableDatabase();%0A%09%09String query = %22Select * FROM %22 + TABLE_QUES + %22WHERE %22 + %0A%09%09%09%09QUES_KEY_VAR + %22 = %22 + variable + %22, %22 +%0A%09%09%09%09QUES_KEY_MONTH + %22 = %22 + month + %22, %22 + %0A%09%09%09%09QUES_KEY_YEAR + %22 = %22 + year;%0A%09%09Cursor cursor = db.rawQuery(query, null);%0A%09%09return handleCursor(cursor);%0A%09%7D%09%0A
 %09public void onU

Add getbymonth method for db