package com.pothier.eric.marc.splitty;

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

import java.util.ArrayList;

/**
 * Created by etiennelaprise on 2017-11-22.
 */

public class DatabaseManager extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "splittyDB";
    private static final int DATABASE_VERSION = 1;

    // Person Table
    private static final String TABLE_PARTICIPANT = "participant";
    private static final String PARTICIPANT_ID = "participant_id";
    private static final String FIRST_NAME = "first_name";
    private static final String LAST_NAME = "last_name";
    private static final String PHONE = "phone";
    private static final String EMAIL = "email";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";

    // Event Table
    private static final String TABLE_EVENT = "event";
    private static final String EVENT_ID = "event_id";
    private static final String EVENT_NAME = "event_name";
    private static final String EVENT_ACTIVE = "event_active";

    // Event_Participant Table
    private static final String TABLE_EVENT_PARTICIPANT = "event_participant";
    private static final String AMOUNT_PAID = "amount_paid";
    private static final String FK_EVENT_ID = "fk_event_id";
    private static final String FK_PARTICIPANT_ID = "fk_participant_id";

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

    // SQL create table
    public void onCreate(SQLiteDatabase db) {
        // Create Participant Table
        String sqlCreateParticipant = "create table "
                + TABLE_PARTICIPANT + "("
                + PARTICIPANT_ID + " integer primary key autoincrement, "
                + FIRST_NAME + " text, "
                + LAST_NAME + " text, "
                + PHONE + " text, "
                + EMAIL + " text, "
                + USERNAME + " text, "
                + PASSWORD + " text)";

        // Create Event Table
        String sqlCreateEvent = "create table "
                + TABLE_EVENT + "("
                + EVENT_ID + " integer primary key autoincrement, "
                + EVENT_NAME + " text, "
                + EVENT_ACTIVE + " boolean)";

        // Create Event_Participant Table
        String sqlCreateEvent_Participant = "create table "
                + TABLE_EVENT_PARTICIPANT + "("
                + FK_EVENT_ID + " integer,"
                + " FOREIGN KEY (" + FK_EVENT_ID + ") REFERENCES " + TABLE_EVENT + "(" + EVENT_ID +")"
                + FK_PARTICIPANT_ID + " integer,"
                + " FOREIGN KEY (" + FK_PARTICIPANT_ID + ") REFERENCES " + TABLE_PARTICIPANT + "(" + PARTICIPANT_ID +")"
                + AMOUNT_PAID + " double,"
                + ");";

        db.execSQL(sqlCreateParticipant);
        db.execSQL(sqlCreateEvent);
        db.execSQL(sqlCreateEvent_Participant);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table id exists " + TABLE_PARTICIPANT);
        db.execSQL("drop table id exists " + TABLE_EVENT);
        db.execSQL("drop table id exists " + TABLE_EVENT_PARTICIPANT);

        onCreate(db);
    }

    /// Insert Statement ///
    // Add new participant record to Participant Table
    public void insertParticipant(Participant participant) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlInsert = "insert into " + TABLE_PARTICIPANT
                + " values(null, '"
                + participant.getFirstName() + "', '"
                + participant.getLastName() + "', '"
                + participant.getPhone() + "', '"
                + participant.getEmail() + "', '"
                + participant.getUserName() + "', '"
                + participant.getPassword() + "')";

        db.execSQL(sqlInsert);
        db.close();
    }
    // Add new event record to Event Table
    public void insertEvent(Event event) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlInsert = "insert into " + TABLE_EVENT
                + " values(null, '"
                + event.getEventName() + "', '"
                + event.getEventActive() + "')";

        db.execSQL(sqlInsert);
        db.close();
    }
    // Add new event_participant record to Event_Participant Table
    public void insertEvent_Participant(Event_Participant event_participant) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlInsert = "insert into " + TABLE_EVENT_PARTICIPANT
                + " values("
                + event_participant.getEvent_id() + "', '"
                + event_participant.getParticipant_id() + "', '"
                + event_participant.getAmountPaid() + "')";

        db.execSQL(sqlInsert);
        db.close();
    }

    /// Select using ID ///
    //Select a participant using ID
    public Participant selectParticipantById(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlQuery = "select * from " + TABLE_PARTICIPANT + " where " + PARTICIPANT_ID + " = "
                + id;
        Cursor cursor = db.rawQuery(sqlQuery, null);
        Participant participant = null;
        if (cursor.moveToFirst())
            participant = new Participant(
                    Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4));
        return participant;
    }
    public Event selectEventById(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlQuery = "select * from " + TABLE_EVENT + " where " + EVENT_ID + " = " + id;
        Cursor cursor = db.rawQuery(sqlQuery, null);
        Event event = null;
        if (cursor.moveToFirst())
            event = new Event(
                    Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),
                    Boolean.parseBoolean(cursor.getString(2)));
        return event;
    }
    public Event_Participant selectEventParticipantById(int eventID, int participantID) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlQuery = "select * from " + TABLE_EVENT_PARTICIPANT + " where " + FK_EVENT_ID
                + " = " + eventID + " and " + FK_PARTICIPANT_ID + " = " + participantID;
        Cursor cursor = db.rawQuery(sqlQuery, null);
        Event_Participant event_participant = null;
        if (cursor.moveToFirst())
            event_participant = new Event_Participant(
                    Integer.parseInt(cursor.getString(0)),
                    Integer.parseInt(cursor.getString(1)),
                    Double.parseDouble(cursor.getString(2)));
        return event_participant;
    }

    /// Select All Statements //
    // Select all participants
    public ArrayList<Participant> selectAllParticipant(){
        SQLiteDatabase db = this.getWritableDatabase();

        String sqlQuery = "select * from " + TABLE_PARTICIPANT;

        Cursor cursor = db.rawQuery(sqlQuery, null);

        ArrayList<Participant> participants = new ArrayList<>();
        while (cursor.moveToNext()){
            Participant currentParticipant = new Participant(
                    Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4));
            participants.add(currentParticipant);
        }
        db.close();
        return participants;
    }
    // Select all events
    public ArrayList<Event> selectAllEvent(){
        SQLiteDatabase db = this.getWritableDatabase();

        String sqlQuery = "select * from " + TABLE_EVENT;

        Cursor cursor = db.rawQuery(sqlQuery, null);

        ArrayList<Event> events = new ArrayList<>();
        while (cursor.moveToNext()){
            Event currentEvent = new Event(
                    Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),
                    Boolean.parseBoolean(cursor.getString(2)));
            events.add(currentEvent);
        }
        db.close();
        return events;
    }
    // Select all event_participants
    public ArrayList<Event_Participant> selectAllEventParticipant(){
        SQLiteDatabase db = this.getWritableDatabase();

        String sqlQuery = "select * from " + TABLE_EVENT_PARTICIPANT;

        Cursor cursor = db.rawQuery(sqlQuery, null);

        ArrayList<Event_Participant> event_participants = new ArrayList<>();
        while (cursor.moveToNext()){
            Event_Participant currentEventParticipant = new Event_Participant(
                    Integer.parseInt(cursor.getString(0)),
                    Integer.parseInt(cursor.getString(1)),
                    Double.parseDouble(cursor.getString(2)));
            event_participants.add(currentEventParticipant);
        }
        db.close();
        return event_participants;
    }

    /// Delete Statements ///
    // Delete participant record using ID
    public void deleteParticipantById(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlDelete = "delete from " + TABLE_PARTICIPANT + " where " + PARTICIPANT_ID + " = " + id;

        db.execSQL(sqlDelete);
        db.close();
    }
    // Delete event record using ID
    public void deleteEventById(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlDelete = "delete from " + TABLE_EVENT + " where " + EVENT_ID + " = " + id;

        db.execSQL(sqlDelete);
        db.close();
    }
    // Delete event_participant record using ID
    public void deleteEventParticipantById(int eventID, int participantID) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlDelete = "delete from " + TABLE_EVENT_PARTICIPANT + " where "
                + FK_EVENT_ID + " = " + eventID + " and " + FK_PARTICIPANT_ID + " = " + participantID;

        db.execSQL(sqlDelete);
        db.close();
    }

    /// Update Statements ///
    //Edit participant record using ID
    public void updateParticipantById(int id, String firstName, String lastName, String phone, String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlUpdate = "update " + TABLE_PARTICIPANT + " set " + FIRST_NAME + " = '" + firstName
                + "', " + LAST_NAME + " = '" + lastName + "', " + EMAIL + " = '" + email + "' where "
                + PARTICIPANT_ID + " = " + id;

        db.execSQL(sqlUpdate);
        db.close();
    }
    //Edit participant record using ID
    public void updateEventById(int id, String eventName, boolean eventActive) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlUpdate = "update " + TABLE_EVENT + " set " + EVENT_NAME + " = '" + eventName
                + "', " + EVENT_ACTIVE + " = '" + eventActive + "' where " + EVENT_ID + " = " + id;

        db.execSQL(sqlUpdate);
        db.close();
    }
    //Edit event_participant record using ID
    public void updateEventParticipantById(int eventID, int participantID, double amountPaid) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sqlUpdate = "update " + TABLE_EVENT_PARTICIPANT + " set " + AMOUNT_PAID + " = '"
                + amountPaid + "' where " + FK_EVENT_ID + " = " + eventID + " and "
                + FK_PARTICIPANT_ID + " = " + participantID;

        db.execSQL(sqlUpdate);
        db.close();
    }

    public boolean loginValidation(String usernamre, String password){
        SQLiteDatabase db = this.getWritableDatabase();

        String sqlQuery = "select " + USERNAME + ", " + PASSWORD + " from " + TABLE_PARTICIPANT
                + " WHERE " + USERNAME + " = " + usernamre + " AND " + PASSWORD + " = " + password;

        Cursor cursor = db.rawQuery(sqlQuery, null);

        if(cursor!=null)
            return true;
        else
            return false;
    }

}