package com.questionquiz;

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

public class DatabaseAdapter {

	public static final String CATEGORIES_ID = "_id";
	public static final String CATEGORIES_NAME = "categories_name";
	public static final String LEVEL_ID = "level_id";
	public static final String LEVEL_NAME = "level_name";
	public static final String QUESTION_NAME = "question";
	public static final String QUESTION_ID = "question_id";
	public static final String RIGHT_ANSWER = "answer_right";
	public static final String WRONG_ANSWER = "wrong_answer";

	private static final String DATABASE_NAME = "DBQuiz";
	private static final String TABLE_CATEGORIES = "categories";
	private static final String TABLE_LEVELS = "levelCategories";
	private static final String TABLE_QUESTION = "questions";
	private static final String TABLE_ANSWER = "answers";
	private static final int DATABASE_VERSION = 1;

	private static final String CREATETABLE_CATEGORIES = "create table categories (_id integer primary key autoincrement, "
			+ "categories_name text not null);";

	private static final String CREATETABLE_LEVEL = "create table levelCategories (_id integer, "
			+ "level_id integer primary key autoincrement, "
			+ "level_name text not null);";

	private static final String CREATETABLE_QUESTION = "create table questions (level_id integer, "
			+ "question_id integer primary key autoincrement, "
			+ "question text not null);";

	private static final String CREATETABLE_ANSWER = "create table answers (question_id integer, "
			+ "answer_right text, " + "wrong_answer text );";

	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DatabaseAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL(CREATETABLE_CATEGORIES);
			db.execSQL(CREATETABLE_LEVEL);
			db.execSQL(CREATETABLE_QUESTION);
			db.execSQL(CREATETABLE_ANSWER);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES + "");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_LEVELS + "");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUESTION + "");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANSWER + "");
			onCreate(db);
		}
	}

	public DatabaseAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

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

	public long insetCategories(String categoriesName) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(CATEGORIES_NAME, categoriesName);

		return db.insert(TABLE_CATEGORIES, null, initialValues);
	}

	public Cursor getCategories() throws SQLException {

		Cursor mCursor = db.query(true, TABLE_CATEGORIES,
				new String[] { CATEGORIES_NAME }, null, null, null, null, null,
				null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor getCategoriesId(String categoriesName) throws SQLException {

		Cursor mCursor = db.query(true, TABLE_CATEGORIES,
				new String[] { CATEGORIES_ID }, CATEGORIES_NAME + "='"
						+ categoriesName + "'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public long insertLevel(int categoriesId, String level) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(CATEGORIES_ID, categoriesId);
		initialValues.put(LEVEL_NAME, level);

		return db.insert(TABLE_LEVELS, null, initialValues);
	}

	public Cursor getLevel(int categoriesId) {
		Cursor mCursor = db.query(true, TABLE_LEVELS, new String[] { LEVEL_ID,
				LEVEL_NAME }, CATEGORIES_ID + "=" + categoriesId + "", null,
				null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public long insertQuestion(int levelId, String question) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(LEVEL_ID, levelId);
		initialValues.put(QUESTION_NAME, question);

		return db.insert(TABLE_QUESTION, null, initialValues);
	}

	public Cursor getQuestion(int levelId) {
		Cursor mCursor = db.rawQuery("SELECT * FROM questions WHERE level_id="
				+ levelId + "", null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public long insertAnswerRight(int questionId, String answerRight) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(QUESTION_ID, questionId);
		initialValues.put(RIGHT_ANSWER, answerRight);

		return db.insert(TABLE_ANSWER, null, initialValues);
	}

	public long insertAnswerWrong(int questionId, String answerWrong) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(QUESTION_ID, questionId);
		initialValues.put(WRONG_ANSWER, answerWrong);

		return db.insert(TABLE_ANSWER, null, initialValues);
	}

	public Cursor getAnswer(int questionNo) {
		Cursor mCursor = db.query(true, TABLE_ANSWER,
				new String[] { WRONG_ANSWER }, QUESTION_ID + "='" + questionNo
						+ "'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public Cursor getAnswerRight(int questionId) {
		Cursor mCursor = db.query(true, TABLE_ANSWER,
				new String[] { RIGHT_ANSWER }, QUESTION_ID + "='" + questionId
						+ "'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

}