/**
 * @version 1
 * @author Pratik Butani
 */
public class DatabaseHelper {

    /**
     * DATABASE_NAME and DATABASE_VERSION
     * DATABASE_NAME will decided when user login first time
     */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME =/*android.os.Environment.getExternalStorageDirectory()
                                                                            + File.separator
																			+ "App"
																			+ File.separator +*/ "demo.sqlite";
    public static String TAG = "TAG";
    /***
     ** STORE TABLES DATA
     ***/
    /*** USER TABLE ***/
    public static final String TABLE_USER = "users";
    /** Used while syncing server_id */
    public static String COMMON_ID = "id";

    /** Columns of USERS TABLE */
    public static String USER_ID = "_id";
    public static String USER_SERVER_ID = "server_id";
    public static String USER_STAFF_ID = "staff_id";
    public static String USER_USERNAME = "username";
    public static String USER_PASSWORD = "password";
    public static String USER_UTP = "utp";
    public static String USER_STATUS = "status";
    /**
     * Database Variable
     */
    public final Context context;
    public SQLiteDatabase db;
    public DBHelper dbHelper;

    /************************************************************************/
    /************* METHODS OF INSERT, UPDATE, DELETE RECORDS ****************/
    /******
     *** GET DATA FROM MYSQL and INSERT into SQLITE
     ******/

    /**
     * DatabaseHelper Constructor
     */
    public DatabaseHelper(Context context) {
        this.context = context;
    }

    /** INSERT in TABLE_USERS **/
    public long insertUsers(int user_id, int staff_id, String username,
                            String password, int status, String utp) {

        ContentValues values = new ContentValues();

        values.put(USER_SERVER_ID, user_id);
        values.put(USER_STAFF_ID, staff_id);
        values.put(USER_USERNAME, username);
        values.put(USER_PASSWORD, password);
        values.put(USER_STATUS, status); // Curretly it is 1, change if user deactivated...
        values.put(USER_UTP, utp);

        return db.insert(TABLE_USER, null, values);
    }

    /*** Update TABLE_USER */
    public int updateUsers(int user_id, int staff_id, String username,
                           String password, int status, String utp) {

        ContentValues values = new ContentValues();

        values.put(USER_STAFF_ID, staff_id);
        values.put(USER_USERNAME, username);
        values.put(USER_PASSWORD, password);
        values.put(USER_STATUS, status); // Curretly it is 1, change if user deactivated...
        values.put(USER_UTP, utp);

        return db.update(TABLE_USER, values, USER_SERVER_ID + " =? ",
                new String[]{Integer.toString(user_id)});
    }

    /** USERS TABLE QUERIES */
    public Cursor getUserData(String uname) {
        return db.query(
                TABLE_USER,
                null,
                USER_USERNAME + "=?",
                new String[]{uname},
                null,
                null,
                null
        );
    }

    /** CHANGE PASSWORD */
    public long changePassword(String oldEmail, String newEmail, String newPassword) {

        ContentValues values = new ContentValues();

        values.put(USER_USERNAME, newEmail);
        values.put(USER_PASSWORD, newPassword);

        return db.update(TABLE_USER, values, USER_USERNAME + " =? ",
                new String[]{oldEmail});
    }

    /** For OPEN database **/
    public synchronized DatabaseHelper open() throws SQLiteException {
        dbHelper = new DBHelper(context);
        db = dbHelper.getWritableDatabase();
        return this;
    }

    /** For CLOSE database **/
    public void close() {
        dbHelper.close();
    }

    /** CREATE CLASS FOR DATABASE OPERATION **/
    public class DBHelper extends SQLiteOpenHelper {

        String sql;

        /** Constructor **/
        public DBHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        /****** ** ** CREATE TABLE ** ** ******/
        @Override
        public void onCreate(SQLiteDatabase db) {

			/* Creating Table USERS */
            sql = "CREATE TABLE " + TABLE_USER
                    + "("
                    + USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + USER_SERVER_ID + " INTEGER,"
                    + USER_STAFF_ID + " INTEGER,"
                    + USER_USERNAME + " TEXT,"
                    + USER_PASSWORD + " TEXT,"
                    + USER_STATUS + " INTEGER,"
                    + USER_UTP + " TIMESTAMP"
                    + ")";

            db.execSQL(sql);

            Log.d(TAG, "ALL TABLES ARE CREATED");
        }

        /** UPGRADE DATABASE **/
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i(TAG, "Database Version: OLD: " + oldVersion + " = NEW: " + newVersion);
            if (context.deleteDatabase(DATABASE_NAME))
                Log.i(TAG, "Database Deleted....");
            else
                Log.i(TAG, "Database Not Deleted..");
        }
    }
}