package com.example.ugotthis;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "UserManager.db";

    // Table names
    private static final String TABLE_USER = "user";
    private static final String TABLE_TASK = "task";

    // User Table Columns names
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USER_NAME = "user_name";
    private static final String COLUMN_USER_EMAIL = "user_email";
    private static final String COLUMN_USER_PASSWORD = "user_password";

    // Task Table Columns names
    private static final String COLUMN_TASK_ID = "task_id";
    private static final String COLUMN_TASK_USER_ID = "task_user_id";
    private static final String COLUMN_TASK_TITLE = "task_title";
    private static final String COLUMN_TASK_DESC = "task_desc";
    private static final String COLUMN_TASK_DATE = "task_date";
    private static final String COLUMN_TASK_ADDRESS = "task_address";
    private static final String COLUMN_TASK_TIME = "task_time";
    private static final String COLUMN_TASK_ACTIVE_FLAG = "task_active_flag";

    // create user table sql query
    private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_USER_NAME + " TEXT,"
            + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";

    // drop user table sql query
    private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;


    // create task table sql query
    private String CREATE_TASK_TABLE = "CREATE TABLE " + TABLE_TASK + "("
            + COLUMN_TASK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_TASK_USER_ID + " INTEGER,"
            + COLUMN_TASK_TITLE + " TEXT,"
            + COLUMN_TASK_DESC + " TEXT,"
            + COLUMN_TASK_DATE + " TEXT,"
            + COLUMN_TASK_ADDRESS + " TEXT,"
            + COLUMN_TASK_TIME + " TEXT,"
            + COLUMN_TASK_ACTIVE_FLAG + " INTEGER" + ")";

    // drop task table sql query
    private String DROP_TASK_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;

    /**
     * Constructor
     *
     * @param context
     */
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CREATE_USER_TABLE);
        db.execSQL(CREATE_TASK_TABLE);

    }


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

        //Drop User Table if exist
        db.execSQL(DROP_USER_TABLE);
        db.execSQL(DROP_TASK_TABLE);

        // Create tables again
        onCreate(db);

    }

    /**
     * This method is to create user record
     *
     * @param user
     */
    public void addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_NAME, user.getName());
        values.put(COLUMN_USER_EMAIL, user.getEmail());
        values.put(COLUMN_USER_PASSWORD, user.getPassword());

        // Inserting Row
        db.insert(TABLE_USER, null, values);
        db.close();
    }

    /**
     * This method is to create task record
     *
     * @param task
     */
    public void addTask(Task task) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_TASK_USER_ID, task.getUserId());
        values.put(COLUMN_TASK_TITLE, task.getTitle());
        values.put(COLUMN_TASK_DESC, task.getDescription());
        values.put(COLUMN_TASK_DATE, task.getDate());
        values.put(COLUMN_TASK_ADDRESS, task.getAddress());
        values.put(COLUMN_TASK_TIME, task.getTime());
        values.put(COLUMN_TASK_ACTIVE_FLAG, 0);

        // Inserting Row
        db.insert(TABLE_TASK, null, values);
        db.close();
    }

    /**
     * This method is to fetch all user and return the list of task records
     *
     * @return list
     */
    public List<Task> getAllTask(String userId, String date) {

        Log.d("User ID, Helper", userId);
        Log.d("Date, Helper", date);

        // array of columns to fetch
        String[] columns = {
                COLUMN_TASK_ID,
                COLUMN_TASK_USER_ID,
                COLUMN_TASK_ACTIVE_FLAG,
                COLUMN_TASK_TITLE,
                COLUMN_TASK_DESC,
                COLUMN_TASK_DATE,
                COLUMN_TASK_ADDRESS,
                COLUMN_TASK_TIME
        };

        // selection criteria
        String selection = COLUMN_TASK_USER_ID + " = ?" + " AND " + COLUMN_TASK_DATE + " = ? AND "
                + COLUMN_TASK_ACTIVE_FLAG + " != 2" ;

        // selection argument
        String[] selectionArgs = {userId, date};

        // sorting orders
        String sortOrder =
                COLUMN_TASK_TIME + " ASC";
        List<Task> taskList = new ArrayList<Task>();

        SQLiteDatabase db = this.getReadableDatabase();

        // query the task table
        /**
         * Here query function is used to fetch records from user table this function works like we use sql query.
         * SQL query equivalent to this query function is
         * SELECT user_id,user_name,user_email,user_password FROM user ORDER BY user_name;
         */
        Cursor cursor = db.query(TABLE_TASK, //Table to query
                columns,    //columns to return
                selection,        //columns for the WHERE clause
                selectionArgs,        //The values for the WHERE clause
                null,       //group the rows
                null,       //filter by row groups
                sortOrder); //The sort order


        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                Log.d("User ID, Helper", cursor.getString(cursor.getColumnIndex(COLUMN_TASK_USER_ID)));
                Log.d("Date, Helper", cursor.getString(cursor.getColumnIndex(COLUMN_TASK_DATE)));

                Task task = new Task();
                task.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ID))));
                task.setUserId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_USER_ID))));
                task.setActiveFlag(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ACTIVE_FLAG))));
                task.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_TITLE)));
                task.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_DESC)));
                task.setDate(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_DATE)));
                task.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ADDRESS)));
                task.setTime(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_TIME)));
                // Adding task record to list
                taskList.add(task);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();

        // return task list
        return taskList;
    }

    /**
     * This method is to fetch all user and return the list of task records
     *
     * @return list
     */
    public Task getTask(String taskID) {

        Log.d("Task ID, Helper", taskID);

        // array of columns to fetch
        String[] columns = {
                COLUMN_TASK_ID,
                COLUMN_TASK_USER_ID,
                COLUMN_TASK_ACTIVE_FLAG,
                COLUMN_TASK_TITLE,
                COLUMN_TASK_DESC,
                COLUMN_TASK_DATE,
                COLUMN_TASK_ADDRESS,
                COLUMN_TASK_TIME
        };

        // selection criteria
        String selection = COLUMN_TASK_ID + " = ?";

        // selection argument
        String[] selectionArgs = {taskID};

        // sorting orders
        String sortOrder =
                COLUMN_TASK_TIME + " ASC";

        SQLiteDatabase db = this.getReadableDatabase();

        // query the task table
        /**
         * Here query function is used to fetch records from user table this function works like we use sql query.
         * SQL query equivalent to this query function is
         * SELECT user_id,user_name,user_email,user_password FROM user ORDER BY user_name;
         */
        Cursor cursor = db.query(TABLE_TASK, //Table to query
                columns,    //columns to return
                selection,        //columns for the WHERE clause
                selectionArgs,        //The values for the WHERE clause
                null,       //group the rows
                null,       //filter by row groups
                sortOrder); //The sort order

        Task task = new Task();

        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                Log.d("Task ID, Helper", cursor.getString(cursor.getColumnIndex(COLUMN_TASK_USER_ID)));
                task.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ID))));
                task.setUserId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_USER_ID))));
                task.setActiveFlag(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ACTIVE_FLAG))));
                task.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_TITLE)));
                task.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_DESC)));
                task.setDate(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_DATE)));
                task.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ADDRESS)));
                task.setTime(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_TIME)));

            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();

        // return task list
        return task;
    }


    /**
     * This method is to fetch all user and return the list of user records
     *
     * @return list
     */
    public List<User> getAllUser() {
        // array of columns to fetch
        String[] columns = {
                COLUMN_USER_ID,
                COLUMN_USER_EMAIL,
                COLUMN_USER_NAME,
                COLUMN_USER_PASSWORD
        };

        // sorting orders
        String sortOrder =
                COLUMN_USER_NAME + " ASC";
        List<User> userList = new ArrayList<User>();

        SQLiteDatabase db = this.getReadableDatabase();

        // query the user table
        /**
         * Here query function is used to fetch records from user table this function works like we use sql query.
         * SQL query equivalent to this query function is
         * SELECT user_id,user_name,user_email,user_password FROM user ORDER BY user_name;
         */
        Cursor cursor = db.query(TABLE_USER, //Table to query
                columns,    //columns to return
                null,        //columns for the WHERE clause
                null,        //The values for the WHERE clause
                null,       //group the rows
                null,       //filter by row groups
                sortOrder); //The sort order


        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                user.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ID))));
                user.setName(cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME)));
                user.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
                user.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
                // Adding user record to list
                userList.add(user);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();

        // return user list
        return userList;
    }

    /*
            * This method to update task record
     *
             * @param user
     */
    public void updateTask(Task task) {

        SQLiteDatabase db = this.getWritableDatabase();

        Log.d("Update task, Helper", String.valueOf(task.getId()));

        ContentValues values = new ContentValues();
        values.put(COLUMN_TASK_USER_ID, task.getUserId());
        values.put(COLUMN_TASK_ACTIVE_FLAG, task.getActiveFlag());
        values.put(COLUMN_TASK_TITLE, task.getTitle());
        values.put(COLUMN_TASK_DESC, task.getDescription());
        values.put(COLUMN_TASK_ADDRESS, task.getAddress());
        values.put(COLUMN_TASK_DATE, task.getDate());
        values.put(COLUMN_TASK_TIME, task.getTime());



        // updating row
        db.update(TABLE_TASK, values, COLUMN_TASK_ID + " = ?",
                new String[]{String.valueOf(task.getId())});
        db.close();
    }

    /*
     * This method to update task record
     *
     * @param user
     */
    public void deleteTask(int taskID) {

        SQLiteDatabase db = this.getWritableDatabase();

        Log.d("Delete task, Helper", String.valueOf(taskID));

        ContentValues values = new ContentValues();
        values.put(COLUMN_TASK_ACTIVE_FLAG, 2);



        // updating row
        db.update(TABLE_TASK, values, COLUMN_TASK_ID + " = ?",
                new String[]{String.valueOf(taskID)});
        db.close();
    }

    /**
     * This method to update user record
     *
     * @param user
     */
    public void updateUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_NAME, user.getName());
        values.put(COLUMN_USER_EMAIL, user.getEmail());
        values.put(COLUMN_USER_PASSWORD, user.getPassword());

        // updating row
        db.update(TABLE_USER, values, COLUMN_USER_ID + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.close();
    }

    /**
     * This method is to delete user record
     *
     * @param user
     */
    public void deleteUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        // delete user record by id
        db.delete(TABLE_USER, COLUMN_USER_ID + " = ?",
                new String[]{String.valueOf(user.getId())});
        db.close();
    }

    /**
     * This method to check user exist or not
     *
     * @param user
     * @param password
     * @return true/false
     */
    public int checkUser(String user, String password) {

        int iResult = -1; //Default

        // array of columns to fetch
        String[] columns = {
                COLUMN_USER_ID
        };
        SQLiteDatabase db = this.getReadableDatabase();
        // selection criteria
        String selection = COLUMN_USER_NAME + " = ?" + " AND " + COLUMN_USER_PASSWORD + " = ?";

        // selection arguments
        String[] selectionArgs = {user, password};

        // query user table with conditions
        /**
         * Here query function is used to fetch records from user table this function works like we use sql query.
         * SQL query equivalent to this query function is
         * SELECT user_id FROM user WHERE user_email = 'jack@androidtutorialshub.com' AND user_password = 'qwerty';
         */
        Cursor cursor = db.query(TABLE_USER, //Table to query
                columns,                    //columns to return
                selection,                  //columns for the WHERE clause
                selectionArgs,              //The values for the WHERE clause
                null,                       //group the rows
                null,                       //filter by row groups
                null);                      //The sort order


        // Traversing through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                iResult = (Integer.parseInt(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ID))));
            } while (cursor.moveToNext());
        }

        int cursorCount = cursor.getCount();

        cursor.close();
        db.close();
        return iResult;
    }
}