package com.example.diustudentsupport;

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;
import android.util.Log;

public class DBHelper {

    // teacher info table columns
    public static final String COL_ID = "id";
    public static final String COL_NAME = "name";
    public static final String COL_TI = "teacher_initial";
    public static final String COL_EMP_ID = "employee_id";
    public static final String COL_DESIGNATION = "designation";
    public static final String COL_DEPARTMENT = "department";
    public static final String COL_FACULTY = "faculty";
    public static final String COL_P_WEB_PAGE = "personal_web_page";
    public static final String COL_EMAIL = "email";
    public static final String COL_CELL_PH_NO = "cell_phone_no";
    public static final String KEY_SEARCH = "searchData";

    private static final String TAG = "DBHelper";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "StudentSupport";
    private static final String FTS_VIRTUAL_TABLE = "TeacherInfo"; //TABLE NAME
    private static final int DATABASE_VERSION = 1;

    //Create a FTS3 Virtual Table for fast searches
    private static final String DATABASE_CREATE =
            "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + " USING fts3(" +
                    COL_NAME + "," +
                    COL_TI + "," +
                    COL_EMP_ID + "," +
                    COL_DESIGNATION + "," +
                    COL_DEPARTMENT + "," +
                    COL_FACULTY + "," +
                    COL_P_WEB_PAGE + "," +
                    COL_EMAIL + "," +
                    COL_CELL_PH_NO + "," +
                    KEY_SEARCH + "," +
                    " UNIQUE (" + COL_NAME + "));";


    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

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


        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE);
        }

        @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 " + FTS_VIRTUAL_TABLE);
            onCreate(db);
        }
    }

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

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

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }

    public long addInfo(String name, String teacher_initial, String em_id, String designation, String dept,
                        String faculty, String p_webpage, String email, String cell) {

        ContentValues initialValues = new ContentValues();
        String searchValue =
                name + " " +
                teacher_initial + " " +
                em_id + " " +
                designation + " " +
                dept + " " +
                faculty + " " +
                p_webpage + " " +
                email + " " +
                cell;
        initialValues.put(COL_NAME, name);
        initialValues.put(COL_TI, teacher_initial);
        initialValues.put(COL_EMP_ID, em_id);
        initialValues.put(COL_DESIGNATION, designation);
        initialValues.put(COL_DEPARTMENT, dept);
        initialValues.put(COL_FACULTY, faculty);
        initialValues.put(COL_P_WEB_PAGE, p_webpage);
        initialValues.put(COL_EMAIL, email);
        initialValues.put(COL_CELL_PH_NO, cell);
        initialValues.put(KEY_SEARCH, searchValue);

        return mDb.insert(FTS_VIRTUAL_TABLE, null, initialValues);
    }

    // Search function
    public Cursor searchInfo(String inputText) throws SQLException {
        Log.w(TAG, inputText);
        String query = "SELECT docid as _id," +
                COL_NAME + "," +
                COL_TI + "," +
                COL_EMP_ID + "," +
                COL_DESIGNATION + "," +
                COL_DEPARTMENT + "," +
                COL_FACULTY + "," +
                COL_P_WEB_PAGE + "," +
                COL_EMAIL + "," +
                COL_CELL_PH_NO +
                " from " + FTS_VIRTUAL_TABLE +
                " where " +  KEY_SEARCH + " MATCH '" + inputText + "';";
        Log.w(TAG, query);
        Cursor mCursor = mDb.rawQuery(query,null);

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    public boolean deleteAllInfo() {

        int doneDelete = 0;
        doneDelete = mDb.delete(FTS_VIRTUAL_TABLE, null , null);
        Log.w(TAG, Integer.toString(doneDelete));
        return doneDelete > 0;

    }

}