package enkay.avbio.Sql;

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;

import enkay.avbio.Model.User;

/**
 * Created by Robin on 2/17/2018.
 */

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "UserManager.db";

    private static final String TABLE_USER = "user";


    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";
    private static final String COLUMN_USER_ADDRESS = "user_address";
    private static final String CULUMN_USER_COMPANY_NAME = "user_company_name";
    private static final String CULUMN_USER_COMPANY_ADDRESS = "user_company_address";
    private static final String CULUMN_USER_GST_NO = "user_gst_no";
    private static final String CULUMN_USER_PAN_NO = "user_pan_no" , COL_LOGO ="LOGO";



    private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_USER_NAME + " TEXT, "
            + COLUMN_USER_ADDRESS + " TEXT, "
            + CULUMN_USER_COMPANY_NAME + " TEXT, "
            + CULUMN_USER_GST_NO + " TEXT, " + CULUMN_USER_PAN_NO + " TEXT,"
            + CULUMN_USER_COMPANY_ADDRESS + " TEXT, " + COLUMN_USER_EMAIL + " TEXT, "
            + COLUMN_USER_PASSWORD + " TEXT ," +
            "" + COL_LOGO + " BLOB"+
            "); ";


    private String DROP_USER_TABLE = "DROP TABLE IF EXISTS" + TABLE_USER;


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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_USER_TABLE);
        onCreate(db);
    }


    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.getPasswod());
        values.put(COLUMN_USER_ADDRESS, user.getAddress());
        values.put(CULUMN_USER_COMPANY_NAME, user.getCompanyName());
        values.put(CULUMN_USER_COMPANY_ADDRESS, user.getCompanyAddress());
        values.put(CULUMN_USER_GST_NO, user.getGstNo());
        values.put(CULUMN_USER_PAN_NO, user.getPanNo());
        values.put(COL_LOGO,user.getImageByte());

        //code to sahi hai


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


    public List<User> getAllUser() {
        String[] columns = {
                COLUMN_USER_ID,
                COLUMN_USER_NAME,
                COLUMN_USER_EMAIL,
                COLUMN_USER_PASSWORD,
                COLUMN_USER_ADDRESS,
                CULUMN_USER_COMPANY_NAME,
                CULUMN_USER_COMPANY_ADDRESS,
                CULUMN_USER_GST_NO,
                CULUMN_USER_PAN_NO,
               // KEY_IMAGE,


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

        SQLiteDatabase db = this.getReadableDatabase();


        Cursor cursor = db.query(TABLE_USER, //Table to query

                null,
                null,    //no need to pass column array if you want all the columns
                null,        //columns for the WHERE clause
                null,        //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
        while (cursor.moveToNext()) {

            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.setPasswod(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
            user.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ADDRESS)));
            user.setCompanyName(cursor.getString(cursor.getColumnIndex(CULUMN_USER_COMPANY_NAME)));
            user.setCompanyAddress(cursor.getString(cursor.getColumnIndex(CULUMN_USER_COMPANY_ADDRESS)));
            user.setGstNo(cursor.getString(cursor.getColumnIndex(CULUMN_USER_GST_NO)));
            user.setPanNo(cursor.getString(cursor.getColumnIndex(CULUMN_USER_PAN_NO)));
            //user.setImage(cursor.getBlob(cursor.getColumnIndex(KEY_IMAGE)));

            // Adding user record to lis
            userList.add(user);
        }
        cursor.close();
        db.close();

        Log.i(TAG, "getAllUser: List empty = "+userList.isEmpty());



        // return user list
        return userList;
    }

    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.getPasswod());
        values.put(COLUMN_USER_ADDRESS, user.getAddress());
        values.put(CULUMN_USER_COMPANY_NAME, user.getCompanyName());
        values.put(CULUMN_USER_COMPANY_ADDRESS, user.getCompanyAddress());
        values.put(CULUMN_USER_GST_NO, user.getGstNo());
        values.put(CULUMN_USER_PAN_NO, user.getPanNo());

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

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


    String TAG = DatabaseHelper.class.getSimpleName();

    public boolean checkUserInfo(String email, String password) {
        String[] colums = {
                COLUMN_USER_ID
        };
        SQLiteDatabase db = this.getReadableDatabase();
        String selection = COLUMN_USER_EMAIL + "= ?";

        String[] SelectionArgs = {email};

        String query = "SELECT * from "+TABLE_USER+" " +
                "WHERE "+COLUMN_USER_EMAIL +" = '"+email+"'" +
                " AND "+COLUMN_USER_PASSWORD +" = '"+password+"'";

        Log.i(TAG, "checkUser: query  =" +query);

        Cursor c = db.rawQuery(query, null);

        while(c.moveToNext()) {
            Log.i(TAG, "checkUser: email = " + c.getString(c.getColumnIndex(COLUMN_USER_EMAIL)));

            Log.i(TAG, "checkUser: pass = "+c.getString(c.getColumnIndex(COLUMN_USER_PASSWORD)));
        }
        return c.getCount()!= 0;

//
//        Cursor cursor = db.query(TABLE_USER,
//
//                colums,
//                selection,
//                SelectionArgs,
//                null,
//                null,
//                null);
//        int cursetorCount = cursor.getCount();
//        cursor.close();
//        db.close();
//
//        if (cursetorCount > 0) {
//            return true;
//
//        }
//        return false;
    }

    public boolean checkUser(String email) {
        String[] colums = {
                COLUMN_USER_ID
        };
        SQLiteDatabase db = this.getReadableDatabase();
        String selection = COLUMN_USER_EMAIL + "= ?";

        String[] SelectionArgs = {email};

        String query = "SELECT * from "+TABLE_USER+" " +
                "WHERE "+COLUMN_USER_EMAIL +" = '"+email+"'" ;

        Log.i(TAG, "checkUser: query  =" +query);

        Cursor c = db.rawQuery(query, null);

        while(c.moveToNext()) {
            Log.i(TAG, "checkUser: email = " + c.getString(c.getColumnIndex(COLUMN_USER_EMAIL)));

        }
        return c.getCount()!= 0;

//
//        Cursor cursor = db.query(TABLE_USER,
//
//                colums,
//                selection,
//                SelectionArgs,
//                null,
//                null,
//                null);
//        int cursetorCount = cursor.getCount();
//        cursor.close();
//        db.close();
//
//        if (cursetorCount > 0) {
//            return true;
//
//        }
//        return false;
    }


    public User getUserDetail(String email){

        User user = new User();

        String query = "SELECT * FROM  "+TABLE_USER
                +" WHERE "+COLUMN_USER_EMAIL+" = '"+email+"'";

        Log.i(TAG, "getUserDetail: query = "+query);

        Cursor cursor = getReadableDatabase().rawQuery(query, null);

        while(cursor.moveToNext()){
            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.setPasswod(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
            user.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_USER_ADDRESS)));
            user.setCompanyName(cursor.getString(cursor.getColumnIndex(CULUMN_USER_COMPANY_NAME)));
            user.setCompanyAddress(cursor.getString(cursor.getColumnIndex(CULUMN_USER_COMPANY_ADDRESS)));
            user.setGstNo(cursor.getString(cursor.getColumnIndex(CULUMN_USER_GST_NO)));
            user.setPanNo(cursor.getString(cursor.getColumnIndex(CULUMN_USER_PAN_NO)));
            user.setImageByte(cursor.getBlob(cursor.getColumnIndex(COL_LOGO)));


        }


        return user;
    }


    public boolean checkUser(String email, String password) {

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

        // selection arguments
        String[] selectionArgs = {email, 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

        int cursorCount = cursor.getCount();

        cursor.close();
        db.close();
        if (cursorCount > 0) {
            return true;
        }

        return false;
    }
}