package kimiko.coreen.app.data.base;

import java.util.Collection;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import kimiko.coreen.app.data.mots.Mot;

public class MotDAO extends DAOBase {
	public static final String MOT_KEY = "_id";
	public static final String MOT_HANGEUL = "hangeul";
	public static final String MOT_HANJA = "hanja";
	public static final String MOT_ROMANIZATION = "romanization";
	public static final String MOT_TRADUCTION = "traduction";
	public static final String MOT_TYPE = "type";

	public static final String MOT_TABLE_NAME = "mots";
	public static final String MOT_TABLE_CREATE =
			"CREATE TABLE " + MOT_TABLE_NAME + " (" +
					MOT_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					MOT_HANGEUL + " TEXT, " +
					MOT_HANJA + " TEXT, " +
					MOT_ROMANIZATION + " TEXT, " +
					MOT_TRADUCTION + " TEXT, " +
					MOT_TYPE + " TEXT);";
	public static final String MOT_TABLE_DROP = "DROP TABLE IF EXISTS " + MOT_TABLE_NAME + ";";
	
	public MotDAO(Context pContext) {
		super(pContext);
		Log.v(this.getClass().getSimpleName(), "Instanciation.");
	}
	
	public static void creer(SQLiteDatabase db) {
		db.execSQL(MOT_TABLE_CREATE);
	}
	
	public static void supprimer(SQLiteDatabase db) {
		db.execSQL(MOT_TABLE_DROP);
	}
	
	
	public void ajouter(Mot m) {
		ajouter(m, mDb);
	}
	public static void ajouter(Mot m, SQLiteDatabase db) {
		final String requete = "INSERT INTO " +MOT_TABLE_NAME+ " ("+MOT_HANGEUL+", "+MOT_HANJA+", "+MOT_ROMANIZATION+", "+MOT_TRADUCTION+", "+MOT_TYPE+") " +
				"VALUES ("+ DatabaseUtils.sqlEscapeString(m.getHangeul()) +", "+ DatabaseUtils.sqlEscapeString(m.getHanja()) +", "+ DatabaseUtils.sqlEscapeString(m.getRomanization()) +", "+ DatabaseUtils.sqlEscapeString(m.getTraduction()) +", "+ DatabaseUtils.sqlEscapeString(m.getType()) +")";
		Log.v(DAOBase.class.getSimpleName(), "Ajout du mot : " + m + " dans : " + db);
		db.execSQL(requete);
	}
	
	

	public void supprimer(long id) {
		mDb.delete(MOT_TABLE_NAME, MOT_KEY + " = ?", new String[] {String.valueOf(id)});
	}

	public Cursor chercher(String entree) {
		entree = "%" +  entree + "%";
		return mDb.rawQuery("select " + MOT_KEY + ", " + MOT_HANGEUL + ", " + MOT_HANJA + ", " + MOT_ROMANIZATION + ", " + MOT_TRADUCTION + ", " + MOT_TYPE + " from " + MOT_TABLE_NAME +
				" where "+MOT_HANGEUL+" like "+ DatabaseUtils.sqlEscapeString(entree) + " " +
						"or "+MOT_HANJA+" like "+ DatabaseUtils.sqlEscapeString(entree) + " " +
								"or "+MOT_ROMANIZATION+" like "+ DatabaseUtils.sqlEscapeString(entree) + " " +
										"or "+MOT_TRADUCTION+" like "+ DatabaseUtils.sqlEscapeString(entree), new String[]{});
	}

	public Mot random() {
		Cursor cursor = mDb.query(MOT_TABLE_NAME +" Order BY RANDOM() LIMIT 1", new String[] { "*" }, null, null, null, null, null);
		cursor.moveToNext();
		return new Mot(cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5));
	}

	public Cursor chercherSimple(String entree) {
		entree = entree + "%";
		return mDb.rawQuery("select " + MOT_KEY + ", " + MOT_HANGEUL + ", " + MOT_TRADUCTION + " from " + MOT_TABLE_NAME +
				" where "+MOT_HANGEUL+" like "+ DatabaseUtils.sqlEscapeString(entree) + " " +
								"or "+MOT_ROMANIZATION+" like "+ DatabaseUtils.sqlEscapeString(entree) + " " +
										"or "+MOT_TRADUCTION+" like "+ DatabaseUtils.sqlEscapeString(entree), new String[]{});
	}

	public Cursor chercherHangeul(String entree) {
		return mDb.rawQuery("select " + MOT_KEY + ", " + MOT_HANGEUL + ", " + MOT_HANJA + ", " + MOT_ROMANIZATION + ", " + MOT_TRADUCTION + ", " + MOT_TYPE + " from " + MOT_TABLE_NAME +
				" where "+MOT_HANGEUL+" like "+ DatabaseUtils.sqlEscapeString(entree), new String[]{});
	}

	public Cursor chercherType(String type) {
		return mDb.rawQuery("select " + MOT_KEY + ", " + MOT_HANGEUL + ", " + MOT_HANJA + ", " + MOT_ROMANIZATION + ", " + MOT_TRADUCTION + ", " + MOT_TYPE + " from " + MOT_TABLE_NAME +
				" where "+MOT_TYPE+" = "+ DatabaseUtils.sqlEscapeString(type), new String[]{});
	}

	public Cursor chercherHangeul(Collection<String> listmot) {
		StringBuilder sb = new StringBuilder("select " + MOT_KEY + ", " + MOT_HANGEUL + ", " + MOT_HANJA + ", " + MOT_ROMANIZATION + ", " + MOT_TRADUCTION + ", " + MOT_TYPE + " from " + MOT_TABLE_NAME +
				" where "+MOT_HANGEUL+" like ");
		for(String s : listmot)
		{
			sb.append(DatabaseUtils.sqlEscapeString(s)).append(" or "+MOT_HANGEUL+" like ");
		}
		sb.append("0000000");
		Log.v(this.getClass().getSimpleName(), sb.toString());
		return mDb.rawQuery(sb.toString(), new String[]{});
	}
}
