public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "FYPSQLApplicationA.db";

    private static int DATABASE_VERSION = 3;
    private SQLiteDatabase SQLITEDATABASE;


    /*Users Table*/
    public static final String USERS = "USERS";
   // public static final String COLUMN_ID = "USER_ID";
    public static final String COLUMN_USERNAME = "USERNAME";
    public static final String COLUMN_PASSWORD = "PASSWORD";
    public static final String COLUMN_NAME = "NAME";
    public static final String COLUMN_EMAIL = "EMAIL";
    public static final String COLUMN_PHRASE = "PHRASE";

    private static final String CREATE_USERS = "CREATE TABLE " + USERS +
            "( " +
            COLUMN_USERNAME + " TEXT PRIMARY KEY , " +
            COLUMN_PASSWORD + " TEXT NOT NULL , " +
            COLUMN_NAME + " TEXT , " +
            COLUMN_EMAIL + " TEXT , " +
            COLUMN_PHRASE + " TEXT NOT NULL );";

 // MAY INCLUDE A USER ID COLUMN   COLUMN_ID + " INT NOT NULL , " +

    /*Topics Table*/
    public static final String TOPICS = "TOPICS";
    public static final String TOPIC_ID = "TOPIC_ID";
    public static final String TOPIC_NAME = "TOPIC_NAME";
    public static final String CHAPTER_ID = "CHAPTER_ID";
    public static final String CHAPTER_NAME = "CHAPTER_NAME";

    private static final String CREATE_TOPICS = " CREATE TABLE " + TOPICS +
            " ( "
            + TOPIC_ID + " INT PRIMARY KEY , "
            + TOPIC_NAME + " TEXT NOT NULL , "
            + CHAPTER_ID + " INT NOT NULL , "
            + CHAPTER_NAME + " TEXT NOT NULL " + ");";


    /*Results Table*/
    private static final String RESULTS = "RESULTS";
//    private static final String TEST_COLUMN_USERNAME = "USERNAME";
    private static final String COLUMN_SCORE = "SCORE";
//    private static final String COLUMN_TIMES_TAKEN = "TIMES_TAKEN";
    private static final String COLUMN_TIMESTAMP = "TIMESTAMP";

    private static final String CREATE_RESULTS = " CREATE TABLE " + RESULTS +
            " ( " +
            COLUMN_USERNAME + "  TEXT NOT NULL , " +
            TOPIC_ID + " INT NOT NULL , " +
            COLUMN_SCORE + " SCORE INT NOT NULL , " +
            COLUMN_TIMESTAMP + " TIMESTAMP TEXT NOT NULL , " +
            "FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " +
            "FOREIGN KEY(USERNAME) REFERENCES USERS(USERNAME) " + "); ";


    /*Questions Table*/
    private static final String QUESTIONS = "QUESTIONS";
    private static final String QUESTIONS_ID = "QUESTION_ID";
    private static final String QUESTIONTEXT = "QUESTION";
    private static final String ANSWER_A = "ANSWER_A"; //option a
    private static final String ANSWER_B = "ANSWER_B"; //option b
    private static final String ANSWER_C = "ANSWER_C"; //option c
    private static final String QUESTION_ANSWER = "ANSWER"; //correct option

    private static final String CREATE_QUESTIONS = " CREATE TABLE " +
            QUESTIONS + " ( " +
            TOPIC_ID + " INT NOT NULL , " +
            QUESTIONS_ID + " INT PRIMARY KEY , " +
            QUESTIONTEXT + " TEXT NOT NULL , " +
            ANSWER_A + " TEXT NOT NULL , " +
            ANSWER_B + " TEXT NOT NULL , " +
            ANSWER_C + " TEXT NOT NULL , " +
            QUESTION_ANSWER + " INT NOT NULL , " +
            "FOREIGN KEY(TOPIC_ID) REFERENCES TOPICS(TOPIC_ID) " + "); ";



    private static Context context;

    public DatabaseHelper(Context c, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(c, name, factory, version);
    }
    public DatabaseHelper(Context c) {
        super(context = c, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        SQLITEDATABASE = db;
        db.execSQL(CREATE_USERS);
        db.execSQL(CREATE_TOPICS);
        db.execSQL(CREATE_QUESTIONS);
        db.execSQL(CREATE_RESULTS);
        addQuestions();
        addTopics();
        this.SQLITEDATABASE = db;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
        // Drop older table if existed
        String queryuser = "DROP TABLE IF EXISTS " + USERS;
        String queryquestion = "DROP TABLE IF EXISTS " + QUESTIONS;
        String querytopic = "DROP TABLE IF EXISTS " + TOPICS;
        String queryresult = "DROP TABLE IF EXISTS " + RESULTS;

        db.execSQL(queryuser);
        db.execSQL(queryquestion);
        db.execSQL(querytopic);
        db.execSQL(queryresult);

        // Create tables again
        onCreate(db);
    }








    public void insertScore (ScoreDetails SD) {
        SQLITEDATABASE = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(COLUMN_USERNAME, SD.GetUsername());
        values.put(TOPIC_ID, SD.GetTopic());
        values.put(COLUMN_SCORE, SD.GetScore());
        values.put(COLUMN_TIMESTAMP, SD.getCurrentTimeStamp());


        SQLITEDATABASE.insert(RESULTS, null, values);
        SQLITEDATABASE.close();
    }


    // Getting All Contacts
    public List<ScoreDetails> getAllResults() {
        List<ScoreDetails> ResultList = new ArrayList<ScoreDetails>();
        // Select All Query

        GlobalVariables GlobalUser = new GlobalVariables();

    String selectQuery = "SELECT  * FROM " + RESULTS
            + " WHERE USERNAME = ' " + GlobalUser.GetUsername() + " ' ";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    /*        Cursor cursor = db.rawQuery("SELECT * FROM RESULTS WHERE " +
            " USERNAME = " + GlobalUser.GetUsername(), null);*/

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                ScoreDetails SD = new ScoreDetails();
                SD.SetUsername(cursor.getString(0));
                SD.SetTopic(Integer.parseInt(cursor.getString(1)));
                SD.SetScore(Integer.parseInt(cursor.getString(2)));
                SD.SetCurrentTimeStamp(cursor.getString(3));

                String name = cursor.getString(0) +"n"+ cursor.getString(1) +"n"+ cursor.getString(2)
                        +"n"+ cursor.getString(3);
               Display_results.ArrayofResults.add(name);
                // Adding contact to list
                ResultList.add(SD);
            } while (cursor.moveToNext());
        }
        // return contact list
        return ResultList;
    }


}

public class Display_results extends AppCompatActivity {
private ListView gridView;
public static ArrayList<String> ArrayofResults = new ArrayList<String>();


@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.display_results_layout);

    DatabaseHelper db = new DatabaseHelper(this);
    List<ScoreDetails> results = db.getAllResults();

    for (ScoreDetails SD : results) {
        String log = "Id: "+ SD.GetUsername()
                +" ,Name: " + SD.GetTopic()
                + " ,Phone: " + SD.GetScore()
                + " ,Phone: " + SD.getCurrentTimeStamp() ;
        // Writing Contacts to log
        Log.d("Name: ", log);

    }

    /*db.close();*/

    gridView = (ListView) findViewById(R.id.contentlist);

    ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
            android.R.layout.simple_list_item_1, ArrayofResults);

    gridView.setAdapter(adapter);

    gridView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        public void onItemClick(AdapterView<?> parent, View v,
                                int position, long id) {
            Toast.makeText(getApplicationContext(),
                    ((TextView) v).getText(), Toast.LENGTH_SHORT).show();
        }
    });

}

/*
    public void onPause() {
        super.onPause();
        finish();
    }*/
}