package gr.mybook.mydatabase;

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

public class MyHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "MyDB.db";
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_TABLE = "students";
	
	public static final String COLUMN_ID = "_id";
	public static final String STUDENT_ID = "serial";
	public static final String STUDENT_NAME = "name";
	public static final String STUDENT_PHONE = "phone";
	
	private static final String DATABASE_CREATE = "create table students (_id integer primary key, " + "serial text not null, name text not null, phone text not null);";
	private static final String DATABASE_UPGRADE = "DROP TABLE IF EXISTS"+DATABASE_TABLE;
	
	private SQLiteDatabase db;
	
	public MyHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}
	
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL(DATABASE_UPGRADE);
			onCreate(db);
		}
	
	public void addStudent(String serial, String name, String phone) {
		db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(STUDENT_ID, serial);
		values.put(STUDENT_NAME, name);
		values.put(STUDENT_PHONE, phone);
		db.insert(DATABASE_TABLE, null, values);
		db.close();
	}
	
	public boolean deleteStudent(String name) {
		db = this.getWritableDatabase();
		boolean result = false;
		String q = "SELECT * FROM students WHERE name = \"" + name + "\"";
		Cursor cursor = db.rawQuery(q, null);
		if (cursor.moveToFirst()) {
			int id = Integer.parseInt(cursor.getString(0));
			db.delete(DATABASE_TABLE, COLUMN_ID + " = ?", new String[] {String.valueOf(id)});
			result = true;
		}
		cursor.close();
		db.close();
		return result;
	}
	
	public Cursor findStudent(String name) {
		db = this.getWritableDatabase();
		String q = "SELECT * FROM students WHERE name = \"" + name + "\"";
		Cursor cursor = db.rawQuery(q, null);
		return cursor;
	}
	
	public boolean updateStudent(String serial, String name, String phone) {
		db = this.getWritableDatabase();
		boolean result = false;
		ContentValues values = new ContentValues();
		values.put(STUDENT_ID, serial);
		values.put(STUDENT_NAME, name);
		values.put(STUDENT_PHONE, phone);
		String q = "SELECT * FROM students WHERE name = \"" + name + "\"";
		Cursor cursor = db.rawQuery(q, null);
		if (cursor.moveToFirst()) {
			int id = Integer.parseInt(cursor.getString(0));
			db.update(DATABASE_TABLE, values, COLUMN_ID + " = ?", new String[] {String.valueOf(id)});
			result = true;
		}
		cursor.close();
		db.close();
		return result;
	}

}
