package com.example.gamingbox.a2101011029.config;
/**
 * Alvin Santiago
 * ID: 101011029
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorIndexOutOfBoundsException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

import com.example.gamingbox.a2101011029.Model.Patient;
import com.example.gamingbox.a2101011029.Model.Test;
import com.example.gamingbox.a2101011029.Model.User;



public class Databasehelper extends SQLiteOpenHelper {


    // Logcat Tag
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "AndroidAssignmentTwoDatabase";

    // Table Names
    // Authentication Tables
    private static final String TABLE_USER = "users";
    private static final String TABLE_USER_ROLE = "user_role";
    private static final String TABLE_ROLE = "roles";
    private static final String TABLE_ROLE_PERMISSION = "role_permission";
    private static final String TABLE_PERMISSION = "permissions";
    // Object Tables
    private static final String TABLE_PATIENT = "patients";
    private static final String TABLE_TEST = "tests";

    // Common Column Names
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_CREATED_AT = "created_at";
    private static final String COLUMN_FIRST_NAME = "first_name";
    private static final String COLUMN_LAST_NAME = "last_name";
    private static final String COLUMN_DEPARTMENT = "department";

    // Authentication Column Names
    // User Table - Column Names
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_PASSWORD = "password";
    // User_Role Table - Column Names
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_ROLE_ID = "role_id";
    // Role Table - Column Names
    private static final String COLUMN_NAME = "name";
    // Role_Permission Table - Column Names
    private static final String COLUMN_PERMISSION_ID = "permission_id";
    // Permission Table - Column Names
    private static final String COLUMN_ACTION_CODE = "action_code";


    // PATIENTS Table - Column Names
    private static final String COLUMN_DOCTOR_ID = "doctor_id";
    private static final String COLUMN_ROOM = "room";

    // TESTS Table - Column Names
    private static final String COLUMN_PATIENT_ID = "patient_id";
    private static final String COLUMN_BPL = "bpl";
    private static final String COLUMN_BPH = "bph";
    private static final String COLUMN_TEMPERATURE = "temperature";

    // Table Create Statements
    // User Table SQL Create Statement
    private static final String CREATE_TABLE_USERS =
            "CREATE TABLE " + TABLE_USER + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY,"
                    + COLUMN_EMAIL + " TEXT,"
                    + COLUMN_PASSWORD + " TEXT,"
                    + COLUMN_FIRST_NAME + " TEXT,"
                    + COLUMN_LAST_NAME + " TEXT,"
                    + COLUMN_DEPARTMENT + " TEXT);";

    // User_Role Table SQL Create Statement
    private static final String CREATE_TABLE_USER_ROLE =
            "CREATE TABLE " + TABLE_USER_ROLE + "("
                    + COLUMN_USER_ID + " INTEGER,"
                    + COLUMN_ROLE_ID + " INTEGER);";

    // Role Table SQL Create Statement
    private static final String CREATE_TABLE_ROLES =
            "CREATE TABLE " + TABLE_ROLE + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY,"
                    + COLUMN_NAME + " TEXT);";

    // Role Table SQL Create Statement
    private static final String CREATE_TABLE_ROLE_PERMISSION =
            "CREATE TABLE " + TABLE_ROLE_PERMISSION + "("
                    + COLUMN_ROLE_ID + " INTEGER,"
                    + COLUMN_PERMISSION_ID + " INTEGER);";

    // Permission Table SQL Create Statement
    private static final String CREATE_TABLE_PERMISSION =
            "CREATE TABLE " + TABLE_PERMISSION + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY,"
                    + COLUMN_ACTION_CODE + " TEXT);";

    // Patient Table SQL Create Statement
    private static final String CREATE_TABLE_PATIENTS =
            "CREATE TABLE " + TABLE_PATIENT + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY,"
                    + COLUMN_DOCTOR_ID + " TEXT,"
                    + COLUMN_FIRST_NAME + " TEXT,"
                    + COLUMN_LAST_NAME + " TEXT,"
                    + COLUMN_DEPARTMENT + " TEXT,"
                    + COLUMN_ROOM + " TEXT,"
                    + COLUMN_CREATED_AT + " DATETIME);";

    // Test Table SQL Create Statement
    private static final String CREATE_TABLE_TESTS =
            "CREATE TABLE " + TABLE_TEST + "("
                    + COLUMN_ID + " INTEGER PRIMARY KEY,"
                    + COLUMN_PATIENT_ID + " INTEGER,"
                    + COLUMN_BPL + " TEXT,"
                    + COLUMN_BPH + " TEXT,"
                    + COLUMN_TEMPERATURE + " TEXT,"
                    + COLUMN_CREATED_AT + " DATETIME);";


    // Account Insert Functions

    /**
     * Inserts an account into the User table in the local database
     * @param db
     * @param email
     * @param password
     * @return The ID of the created account record
     */
    private long insertUser(SQLiteDatabase db, String email, String password, String firstName, String lastName, String department) {

        ContentValues values = new ContentValues();
        values.put(COLUMN_EMAIL, email);
        values.put(COLUMN_PASSWORD, password);
        values.put(COLUMN_FIRST_NAME, firstName);
        values.put(COLUMN_LAST_NAME, lastName);
        values.put(COLUMN_DEPARTMENT, department);

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

    /**
     * Inserts a Admin Role into the Role table of the device database
     * @param db
     * @param role
     * @return The ID of the created admin role record
     */
    private long insertRole(SQLiteDatabase db, String role) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_NAME, role);

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

    /**
     * Inserts a UserRole association record to associate an existing user with an existing role.
     * @param db
     * @param userID
     * @param roleID
     */
    private void insertUserRole(SQLiteDatabase db, long userID, long roleID) {

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_ID, userID);
        values.put(COLUMN_ROLE_ID, roleID);

        db.insert(TABLE_USER_ROLE, null, values);
    }

    /**
     * Inserts a Permission record in the Permission table
     * @param db
     * @param actionCode
     * @return The ID of the created Permission record
     */
    private long insertPermission(SQLiteDatabase db, String actionCode) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_ACTION_CODE, actionCode);

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

    /**
     * Inserts a RolePermission Record into Role_Permission table to associate an existing role with
     * an existing permission.
     * @param db
     * @param roleID
     * @param permissionID
     */
    private void insertRolePermission(SQLiteDatabase db, long roleID, long permissionID) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_ROLE_ID, roleID);
        values.put(COLUMN_PERMISSION_ID, permissionID);

        db.insert(TABLE_ROLE_PERMISSION, null, values);
    }

    /**
     * Retrieve User record from the database where the record's
     *   password = @param password
     *   AND
     *   email = @param email
     * @param email
     * @param password
     * @return
     */
    public User login(String email, String password) {
        //Initialize userID
        String role;
        Cursor c;
        User user = null;

        SQLiteDatabase db = this.getReadableDatabase();

        //Store the sql query for selecting the User Record
        String selectQuery =
                "SELECT * FROM " + TABLE_USER
                        + " WHERE " + COLUMN_EMAIL + " = \"" + email
                        + "\" AND " + COLUMN_PASSWORD + " = \"" + password + "\";";

        //Create a Cursor to store the Record and execute the Query stored in selectQuery
        c = db.rawQuery(selectQuery, null);


        if(c == null) {
            return null;
        }

        try {
            c.moveToFirst();

            role = getUserRole(c.getString(c.getColumnIndex(COLUMN_ID)));

            //Set userID to the id of the User Record
            user = new User(
                    c.getString(c.getColumnIndex(COLUMN_ID)),
                    c.getString(c.getColumnIndex(COLUMN_FIRST_NAME)),
                    c.getString(c.getColumnIndex(COLUMN_LAST_NAME)),
                    c.getString(c.getColumnIndex(COLUMN_DEPARTMENT)),
                    role
            );

        } catch(CursorIndexOutOfBoundsException e) {
            return null;
        }

        return user;
    }

    public String getUserRole(String userId) {
        String selectQuery;
        SQLiteDatabase db;
        Cursor c;
        String roleId, role;

        db = this.getReadableDatabase();

        selectQuery =
                "SELECT * FROM " + TABLE_USER_ROLE
                        + " WHERE " + COLUMN_USER_ID + " = " + userId
                        + ";";

        c = db.rawQuery(selectQuery, null);

        if(c == null) {
            return null;
        }

        try {
            c.moveToFirst();
            roleId = c.getString(c.getColumnIndex(COLUMN_ROLE_ID));
            role = getRole(roleId);

        } catch(CursorIndexOutOfBoundsException e) {
            return null;
        }

        return role;


    }

    public String getRole(String roleId) {
        String selectQuery;
        SQLiteDatabase db;
        Cursor c;
        String role;

        db = this.getReadableDatabase();

        selectQuery =
                "SELECT * FROM " + TABLE_ROLE
                        + " WHERE " + COLUMN_ID + " = " + roleId
                        + ";";

        c = db.rawQuery(selectQuery, null);

        if(c == null) {
            return null;
        }

        try {
            c.moveToFirst();
            role = c.getString(c.getColumnIndex(COLUMN_NAME));

        } catch(CursorIndexOutOfBoundsException e) {
            return null;
        }

        return role;

    }

    /**
     * Retrieves all patients in the Patient Table. Creates a Patient object with each retrieved record. Returns all created Patients in a List<Patient>.
     * @return List<Patient> containing all patients retrieved from the Patient table.
     */
    public List<Patient> getAllPatients() {

        List<Patient> patients = new ArrayList<Patient>();

        //Store the sql query for retrieving the Patient records
        String selectQuery =
                "SELECT * FROM " + TABLE_PATIENT;

        //Get the readable database
        SQLiteDatabase db = this.getReadableDatabase();
        //Execute the query and store the retrieved in a Cursor
        Cursor c = db.rawQuery(selectQuery, null);

        //if the first record exists
        if(c.moveToFirst()) {
            do {
                //Create a Patient object for each record in Cursor
                Patient patient = new Patient();
                //patient.setPatientID(c.getString(c.getColumnIndex(COLUMN_ID)));
                patient.setPatientFname(c.getString(c.getColumnIndex(COLUMN_FIRST_NAME)));
                patient.setPatientLname(c.getString(c.getColumnIndex(COLUMN_LAST_NAME)));
                patient.setPatientRoom(c.getString(c.getColumnIndex(COLUMN_ROOM)));
                patient.setPatientDepartment(c.getString(c.getColumnIndex(COLUMN_DEPARTMENT)));

                patients.add(patient);
            } while (c.moveToNext()); //Advance the Cursor
        }

        return patients;
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        // Create Tables
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_USER_ROLE);
        db.execSQL(CREATE_TABLE_ROLES);
        db.execSQL(CREATE_TABLE_ROLE_PERMISSION);
        db.execSQL(CREATE_TABLE_PERMISSION);
        db.execSQL(CREATE_TABLE_PATIENTS);
        db.execSQL(CREATE_TABLE_TESTS);

        //Insert Users
        // Insert Doctor Demo User Record
        long doctorID = insertUser(db, "doctor@doctor.com", "doctor", "Doc", "Doe", "Emergency");
        // Insert Nurse Demo User Record
        long nurseID = insertUser(db, "nurse@nurse.com", "nurse", "Nurse", "Doe", "Emergency");

        //Insert Roles
        // Insert the Doctor Role Record
        long doctorRoleID = insertRole(db, "doctor");
        // Insert the Nurse Role Record
        long nurseRoleID = insertRole(db, "nurse");

        //Insert the UserRole Records
        // Insert the Doctor Demo User Record to the Doctor Role Record association
        insertUserRole(db, doctorID, doctorRoleID);
        // Insert the Nurse Demo User Record to the Nurse Role Record association
        insertUserRole(db, nurseID, nurseRoleID);


        //Create Permissions
        // Insert Create Patient Permission
        long createPatientID = insertPermission(db, "C_PATIENT");
        // Insert Retrieve Patient Permission
        long viewPatientID = insertPermission(db, "R_PATIENT");

        // Insert Create Test Permission
        long createTestID = insertPermission(db, "C_TEST");
        // Insert Retrieve Test Permission
        long viewTestID = insertPermission(db, "R_TEST");

        // Associate Nurse Role to the following permissions
        insertRolePermission(db, nurseRoleID, createPatientID);
        insertRolePermission(db, nurseRoleID, viewPatientID);

        insertRolePermission(db, nurseRoleID, createTestID);
        insertRolePermission(db, nurseRoleID, viewTestID);

        // Associate Doctor Role to the following permissions
        insertRolePermission(db, doctorRoleID, viewPatientID);

        insertRolePermission(db, doctorRoleID, createTestID);
        insertRolePermission(db, doctorRoleID, viewTestID);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop Tables
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_USER_ROLE);
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ROLES);
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_ROLE_PERMISSION);
        db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_PERMISSION);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PATIENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEST);
    }
    /*

    public void insertPatient(Patient patient){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(COLUMN_FIRST_NAME , patient.getPatientFname());
        values.put(COLUMN_LAST_NAME, patient.getPatientLname());
        values.put(COLUMN_DEPARTMENT, patient.getPatientDepartment());
        values.put(COLUMN_ROOM, patient.getPatientRoom());
        db.insert(TABLE_PATIENT, null, values);
        db.close();



    }
    public void insertTest(Test test ){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(COLUMN_PATIENT_ID, test.getPatientId());
        values.put(COLUMN_BPL,test.getBPL());
        values.put(COLUMN_BPH,test.getBPH());
        values.put(COLUMN_TEMPERATURE,test.getTemp());
        db.insert(TABLE_TEST,null,values);
        db.close();
    }

*/
    public long insertPatient(SQLiteDatabase db, String first_name, String last_name, String department, String room){

        ContentValues values = new ContentValues();
        values.put (COLUMN_FIRST_NAME, first_name);
        values.put (COLUMN_LAST_NAME, last_name);
        values.put (COLUMN_DEPARTMENT, department);
        values.put (COLUMN_ROOM, room);
        return db.insert(CREATE_TABLE_PATIENTS, null, values);
    }

}