package sk.tuke.smart.bakalar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.icu.text.LocaleDisplayNames;
import android.util.Log;

import java.security.Key;
import java.util.ArrayList;
import java.util.List;

import sk.tuke.smart.bakalar.constants.GameTitles;

public class DatabaseHelper extends SQLiteOpenHelper
{
    // Logcat tag
    private static final String TAG = "DatabaseHelper";

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

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

    // Table Names
    private static final String TABLE_USER = "users";
    private static final String TABLE_GAME = "games";
    private static final String TABLE_LEVEL = "levels";

    // Common column names
    private static final String KEY_ID = "id";

    // USERS Table - column names
    private static final String GAME_TAG = "game_tag";
    private static final String FIRST_NAME = "first_name";
    private static final String LAST_NAME = "last_name";
    private static final String AGE = "age";
    private static final String SEX = "sex";

    // GAMES Table - column names
    private static final String USER_ID = "user_id";
    private static final String GAME_LABEL = "game_label";
    private static final String PLAYED_AT = "played";
    private static final String GAME_SCORE = "score";
    private static final String GOODCHOICE = "goodchoice";
    private static final String WRONGCHOICE = "wrongchoice";

    // LEVEL Table - column names
    private static final String GAME_ID = "game_id";
    private static final String FIRST_NUMBER = "first_number";
    private static final String MARK = "mark";
    private static final String SECOND_NUMBER = "second_number";
    private static final String RESULT = "result";
    private static final String LEVEL_DURATION = "level_duration";
    private static final String LEVEL_SCORE = "level_score";
    private static final String DIRECTION = "direction";
    private static final String IMAGE_COMBINATION = "image_combination";

    // Table Create Statements
    private static final String CREATE_TABLE_USER = "CREATE TABLE "
            + TABLE_USER + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + GAME_TAG + " TEXT, "
            + FIRST_NAME + " TEXT, "
            + LAST_NAME + " TEXT, "
            + SEX + " TEXT, "
            + AGE + " INTEGER)";

    private static final String CREATE_TABLE_GAME = "CREATE TABLE "
            + TABLE_GAME + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + USER_ID + " INTEGER, "
            + GAME_LABEL + " TEXT, "
            + PLAYED_AT + " DATETIME, "
            + GAME_SCORE + " INTEGER, "
            + GOODCHOICE + " INTEGER, "
            + WRONGCHOICE + " INTEGER, "
            + " FOREIGN KEY (" + USER_ID + ") REFERENCES " + TABLE_USER + "(" + KEY_ID + "))";

    private static final String CREATE_TABLE_LEVEL = "CREATE TABLE "
            + TABLE_LEVEL + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + GAME_ID + " INTEGER, "
            + LEVEL_DURATION + " INTEGER, "
            + LEVEL_SCORE + " INTEGER, "
            + FIRST_NUMBER + " INTEGER, "
            + MARK + " TEXT, "
            + SECOND_NUMBER + " INTEGER, "
            + RESULT + " INTEGER, "
            + IMAGE_COMBINATION + " TEXT, "
            + DIRECTION + " TEXT, "
            + " FOREIGN KEY (" + GAME_ID + ") REFERENCES " + TABLE_GAME + "(" + KEY_ID + "))";

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

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase)
    {
        // creating required tables
        sqLiteDatabase.execSQL(CREATE_TABLE_USER);
        sqLiteDatabase.execSQL(CREATE_TABLE_GAME);
        sqLiteDatabase.execSQL(CREATE_TABLE_LEVEL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1)
    {
        // on upgrade drop older tables
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_GAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_LEVEL);

        // create new tables
        this.onCreate(sqLiteDatabase);
    }

    public boolean checkIfUserExists(String name)
    {
        SQLiteDatabase db = this.getReadableDatabase();

//        String query = "SELECT " + GAME_TAG + " FROM " + TABLE_USER + " WHERE " + GAME_TAG + " =?";
//        String[] selection = {name} ;
//        Cursor c = db.rawQuery(query, selection);

        String query2 = "SELECT * FROM " + TABLE_USER + " WHERE " + GAME_TAG + " = '" + name + "'";
        Cursor c = db.rawQuery(query2, null);

        if (c.getCount() > 0)
        {
            c.close();
            db.close();
            return true;
        }
        else
        {
            c.close();
            db.close();
            return false;
        }
    }


    // CRUD for user table
    public boolean addUser(User user)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(GAME_TAG, user.getGameTag());
        values.put(FIRST_NAME, user.getFirstName());
        values.put(LAST_NAME, user.getLastName());
        values.put(SEX, "M");
        values.put(AGE, user.getAge());

        long result = db.insert(TABLE_USER, null, values);
        db.close();

        if (result == -1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public List getAllUsers()
    {
        List users = new ArrayList();
        String query = "SELECT * FROM " + TABLE_USER + " ORDER BY " + KEY_ID;

        Log.d(TAG, query);

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

        if (c.moveToFirst())
        {
            do
            {
                User user = new User();
                user.setId(c.getInt(0));
                user.setGameTag(c.getString(1));
                user.setFirstName(c.getString(2));
                user.setLastName(c.getString(3));
                user.setSex(c.getString(c.getColumnIndex(SEX)));
                user.setAge(c.getInt(c.getColumnIndex(AGE)));

                // adding to the list
                users.add(user.getId() + " " + user.getGameTag() + " " + user.getFirstName() + " " + user.getLastName() + " " + user.getSex() + " " + user.getAge());
            } while (c.moveToNext());
        }
        db.close();
        return users;
    }

    public User getUser(int user_id)
    {
        User user = new User();
        SQLiteDatabase db = this.getReadableDatabase();

        String[] columns = {KEY_ID, GAME_TAG, FIRST_NAME, LAST_NAME};

        String selection = KEY_ID + " = ?";

        String[] selectionArgs = {String.valueOf(user_id)};

        Cursor c = db.query(TABLE_USER, columns, selection, selectionArgs, null, null, null);

        if (c != null)
        {
            c.moveToFirst();
            user.setId(c.getInt(0));
            user.setGameTag(c.getString(1));
            user.setFirstName(c.getString(2));
            user.setLastName(c.getString(3));
        }
        c.close();
        db.close();
        return user;
    }

    public boolean updateUser(User user)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(GAME_TAG, user.getGameTag());
        values.put(FIRST_NAME, user.getFirstName());
        values.put(LAST_NAME, user.getLastName());
        values.put(SEX, user.getSex());
        values.put(AGE, user.getAge());

        long result = db.update(TABLE_USER, values, KEY_ID + " = ?", new String[] {String.valueOf(user.getId())});

        db.close();
        if(result == -1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public void deleteUser(int user_id)
    {

    }

    public User getSignedUser(String gameTag)
    {
        User user = new User();
        SQLiteDatabase db = this.getReadableDatabase();

        String[] columns = {KEY_ID, GAME_TAG, FIRST_NAME, LAST_NAME, SEX, AGE};

        String selection = GAME_TAG + " = ?";

        String[] selectionArgs = {gameTag};

        Cursor c = db.query(TABLE_USER, columns, selection, selectionArgs, null, null, null);

        if (c != null)
        {
            c.moveToFirst();
            user.setId(c.getInt(c.getColumnIndex(KEY_ID)));
            user.setGameTag(c.getString(c.getColumnIndex(GAME_TAG)));
            user.setFirstName(c.getString(c.getColumnIndex(FIRST_NAME)));
            user.setLastName(c.getString(c.getColumnIndex(LAST_NAME)));
            user.setSex(c.getString(c.getColumnIndex(SEX)));
            user.setAge(c.getInt(c.getColumnIndex(AGE)));
        }

        c.close();
        db.close();
        return user;
    }


    // CRUD for game table
    public boolean addGame(Game game)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(USER_ID, game.getUser_id());
        values.put(GAME_LABEL, game.getLabel());
        values.put(PLAYED_AT, game.getPlayed());
        values.put(GAME_SCORE, game.getScore());
        values.put(GOODCHOICE, game.getGoodchoice());
        values.put(WRONGCHOICE, game.getWrongchoice());

        long result = db.insert(TABLE_GAME, null, values);
        db.close();

        if (result == -1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public List<Game> getAllGames()
    {
        List games = new ArrayList();
        String query = "SELECT * FROM " + TABLE_GAME + " ORDER BY " + KEY_ID;

        Log.d(TAG, query);

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

        if (c.moveToFirst())
        {
            do
            {
                Game game = new Game();
                game.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                game.setLabel(c.getString(c.getColumnIndex(GAME_LABEL)));
                /***
                 * TODO: TU POKRAČOVAŤ
                 */
                games.add(game.getId() + " " + game.getLabel());
            } while (c.moveToNext());
        }
        db.close();
        return games;
    }

    public List<Game> getAllGamesForSpecificUser(long user_id)
    {
        Log.d(TAG, "HLADAM HRY PRE ID: " + user_id);

        List<Game> games = new ArrayList();

        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_GAME + " WHERE " + USER_ID + " = '" + user_id + "'";
        Cursor c = db.rawQuery(query, null);

        if (c.moveToFirst())
        {
            do
            {
                Game game = new Game();
                game.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                game.setLabel(c.getString(c.getColumnIndex(GAME_LABEL)));
                game.setPlayed(c.getString(c.getColumnIndex(PLAYED_AT)));
                game.setScore(c.getInt(c.getColumnIndex(GAME_SCORE)));
                game.setGoodchoice(c.getInt(c.getColumnIndex(GOODCHOICE)));
                game.setWrongchoice(c.getInt(c.getColumnIndex(WRONGCHOICE)));
                games.add(game);

            } while (c.moveToNext());
            c.close();
        }
        db.close();
        return games;
    }

    /*public Game getGame(int game_id)
    {

    }*/

    public Game getLastGame()
    {
        Game game = new Game();
        SQLiteDatabase db = this.getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_GAME + " ORDER BY " + KEY_ID + " DESC LIMIT 1";

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

        if (c.moveToFirst())
        {
            game.setId(c.getInt(c.getColumnIndex(KEY_ID)));
            game.setUser_id(c.getInt(c.getColumnIndex(USER_ID)));
            game.setLabel(c.getString(c.getColumnIndex(GAME_LABEL)));
            game.setScore(c.getInt(c.getColumnIndex(GAME_SCORE)));
            game.setGoodchoice(c.getInt(c.getColumnIndex(GOODCHOICE)));
            game.setWrongchoice(c.getInt(c.getColumnIndex(WRONGCHOICE)));
        }
        return game;
    }

    public boolean updateGame(Game game)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(GAME_SCORE, game.getScore());
        values.put(GOODCHOICE, game.getGoodchoice());
        values.put(WRONGCHOICE, game.getWrongchoice());

        long result = db.update(TABLE_GAME, values, KEY_ID + " = ?", new String[] {String.valueOf(game.getId())});

        db.close();
        if (result == -1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public void deleteLastGame(String name)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        Game game = getLastGame();
        Log.d(TAG, "VYMAZAVM POSLEDNU HRU: " + game.getId() + " " + name);
        db.delete(TABLE_GAME, KEY_ID + " = " + game.getId(), null);
    }

    public int getBestScoreFromGame(String gameLabel, long user_id)
    {
        Game game = new Game();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_GAME + " WHERE " + GAME_LABEL + " = '" + gameLabel + "' AND " + USER_ID + " = " + user_id + " ORDER BY " + GAME_SCORE + " DESC";
        Cursor c = db.rawQuery(query, null);

        if (c.moveToFirst())
        {
            game.setScore(c.getInt(c.getColumnIndex(GAME_SCORE)));
            c.close();
        }

        db.close();
        return game.getScore();
    }

    public int getTotalPlayedGames(String gameLabel, long user_id)
    {
        int totalPlayedGames = 0;
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT COUNT(*) FROM " + TABLE_GAME + " WHERE " + GAME_LABEL + " = '" + gameLabel + "' AND " + USER_ID + " = " + user_id;
        Cursor c = db.rawQuery(query, null);

        if (c.moveToFirst())
        {
            totalPlayedGames = c.getInt(c.getColumnIndex("COUNT(*)"));
            c.close();
        }
        db.close();
        return totalPlayedGames;
    }

    public int getSumOfGoodchoicesInGame(String gameLabel, long user_id)
    {
        int totalGoodchoices = 0;
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT SUM(" + GOODCHOICE + ") FROM " + TABLE_GAME + " WHERE " + GAME_LABEL + " = '" + gameLabel + "' AND " + USER_ID + " = " + user_id;

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

        if (c.moveToFirst())
        {
            totalGoodchoices = c.getInt(c.getColumnIndex("SUM(" + GOODCHOICE + ")"));
            c.close();
        }

        db.close();
        return totalGoodchoices;
    }

    public int getSumOfWrongchoicesInGame(String gameLabel, long user_id)
    {
        int totalGoodchoices = 0;
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT SUM(" + WRONGCHOICE + ") FROM " + TABLE_GAME + " WHERE " + GAME_LABEL + " = '" + gameLabel + "' AND " + USER_ID + " = " + user_id;

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

        if (c.moveToFirst())
        {
            totalGoodchoices = c.getInt(c.getColumnIndex("SUM(" + WRONGCHOICE + ")"));
            c.close();
        }

        db.close();
        return totalGoodchoices;
    }

    //CRUD for level table
    public boolean addLevel(Level level)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(GAME_ID, level.getGame_id());
        values.put(LEVEL_DURATION, level.getLevel_duration());
        values.put(LEVEL_SCORE, level.getLevel_score());
        values.put(FIRST_NUMBER, level.getFirst_number());
        values.put(MARK, level.getMark());
        values.put(SECOND_NUMBER, level.getSecond_number());
        values.put(RESULT, level.getResult());
        values.put(DIRECTION, level.getDirection());

        long result = db.insert(TABLE_LEVEL, null, values);
        db.close();

        if (result == -1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

//    public List getAllLevels()
//    {
//
//    }
//
//    public boolean updateLevel()
//    {
//
//    }
//
//    public boolean deleteLevel()
//    {
//
//    }

    public String getNameOfPlayer(long game_id)
    {
        SQLiteDatabase db = getReadableDatabase();
        String query = "SELECT * FROM " + TABLE_USER + " WHERE " + KEY_ID + " = '" + game_id + "'";
        Cursor c = db.rawQuery(query, null);

        String name = null;

        if (c.moveToFirst())
        {
            name = c.getString(c.getColumnIndex(GAME_TAG));
            c.close();
        }
        db.close();

        return name;
    }

    public List getAllLevelsForMathGame(long game_id, String gameLabel)
    {
        Log.d(TAG, "HLADAM levely PRE ID: " + game_id + " Kategoria hry: " + gameLabel);

        List levels = new ArrayList();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_LEVEL + " WHERE " + GAME_ID + " = '" + game_id + "'";

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


        if (c.moveToFirst())
        {
            if (gameLabel.equals(GameTitles.imagesGame))
            {
                do
                {
                    Level level = new Level();
                    level.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                    level.setLevel_duration(c.getInt(c.getColumnIndex(LEVEL_DURATION)));
                    level.setLevel_score(c.getInt(c.getColumnIndex(LEVEL_SCORE)));
                    level.setDirection(c.getString(c.getColumnIndex(DIRECTION)));

                    levels.add(i++ + ".\nKombináciu našiel za: " + level.getLevel_duration()
                            + "\nSkóre za nájdenú kombináciu: " + level.getLevel_score()
                            + "\nSmer nájdenej kombinácie: " + level.getDirection());
                } while (c.moveToNext());
            }
            else if (gameLabel.equals(GameTitles.additionGame) || gameLabel.equals(GameTitles.additionAndSubtractionGame) || gameLabel.equals(GameTitles.subtractionGame))
            {
                do
                {
                    Level level = new Level();
                    level.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                    level.setFirst_number(c.getInt(c.getColumnIndex(FIRST_NUMBER)));
                    level.setMark(c.getString(c.getColumnIndex(MARK)));
                    level.setSecond_number(c.getInt(c.getColumnIndex(SECOND_NUMBER)));
                    level.setResult(c.getInt(c.getColumnIndex(RESULT)));
                    level.setLevel_duration(c.getInt(c.getColumnIndex(LEVEL_DURATION)));
                    level.setLevel_score(c.getInt(c.getColumnIndex(LEVEL_SCORE)));

                    levels.add(i++ + "."
                            + "\nPríklad: " + level.getFirst_number() + " " + level.getMark() + " " + level.getSecond_number() + " = " + level.getResult()
                            + "\nČas za ktorý ho vypočítal: " + level.getLevel_duration()
                            + "\nSkóre za príklad: " + level.getLevel_score());
                } while (c.moveToNext());
            }
            else if (gameLabel.equals(GameTitles.ladybugGame))
            {
                do
                {
                    Level level = new Level();
                    level.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                    level.setLevel_duration(c.getInt(c.getColumnIndex(LEVEL_DURATION)));

                    levels.add(i++ + ".\nPlochu vyriešil za: " + level.getLevel_duration());
                } while (c.moveToNext());
            }
            else if (gameLabel.equals(GameTitles.quickSortGame))
            {
                levels.add("Táto hra zatiaľ nezaznamenáva žiadne dodatočné údaje.");
            }
            else if (gameLabel.equals(GameTitles.squareAdditionGame))
            {
                do
                {
                    Level level = new Level();
                    level.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                    level.setLevel_duration(c.getInt(c.getColumnIndex(LEVEL_DURATION)));
                    level.setLevel_score(c.getInt(c.getColumnIndex(LEVEL_SCORE)));

                    levels.add(i++ + ".\nPlochu vyriešil za: " + level.getLevel_duration()
                             + "\nSkóre za vypočítanú plochu: " + level.getLevel_score());
                } while (c.moveToNext());
            }
            else if (gameLabel.equals(GameTitles.lacesGame))
            {
                do
                {
                    Level level = new Level();
                    level.setId(c.getInt(c.getColumnIndex(KEY_ID)));
                    level.setLevel_duration(c.getInt(c.getColumnIndex(LEVEL_DURATION)));
                    level.setLevel_score(c.getInt(c.getColumnIndex(LEVEL_SCORE)));

                    levels.add(i++ + ".\nPlochu vyriešil za: " + level.getLevel_duration()
                            + "\nSkóre za vyriešenú plochu: " + level.getLevel_score());
                } while (c.moveToNext());
            }
        }

        c.close();
        db.close();
        return levels;
    }
}