package com.effectivesoft.caloriesmanager.repository;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.effectivesoft.caloriesmanager.CaloriesManagerApplication;
import com.effectivesoft.caloriesmanager.data.model.Dish;
import com.effectivesoft.caloriesmanager.data.model.Meal;
import com.effectivesoft.caloriesmanager.data.model.PhysicalExercise;
import com.effectivesoft.caloriesmanager.data.model.PhysicalTraining;
import com.effectivesoft.caloriesmanager.data.model.Record;
import com.effectivesoft.caloriesmanager.data.model.Water;
import com.effectivesoft.caloriesmanager.database.DbHelper;
import com.effectivesoft.caloriesmanager.util.DateUtils;

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

public class Repository {

    private final static String LOG_TAG = "Repository";

    private static volatile Repository instance;

    private SQLiteDatabase database;

    private Repository() {
        DbHelper dbHelper = new DbHelper(CaloriesManagerApplication.getAppContext());
        database = dbHelper.getWritableDatabase();
    }

    public static Repository getInstance() {
        if (instance == null) {
            synchronized (Repository.class) {
                if (instance == null) {
                    instance = new Repository();
                }
            }
        }
        return instance;
    }

    public void writeRecordList(List<Record> records, boolean writeOnlyUniqueRecords) {
        for (Record record : records) {
            writeRecord(record, writeOnlyUniqueRecords);
        }
    }

    public void writeRecord(Record record, boolean writeOnlyUniqueRecords) {
        if (record instanceof Meal) {
            writeMeal((Meal) record, writeOnlyUniqueRecords);
        } else if (record instanceof PhysicalTraining) {
            writeTraining((PhysicalTraining) record, writeOnlyUniqueRecords);
        } else if (record instanceof Water) {
            writeWater((Water) record, writeOnlyUniqueRecords);
        }
    }

    public void deleteRecord(Record record) {
        String tableName = "";
        if (record instanceof Meal) {
            tableName = DbHelper.TABLE_MEALS_NAME;
        } else if (record instanceof PhysicalTraining) {
            tableName = DbHelper.TABLE_TRAININGS_NAME;
        } else if (record instanceof Water) {
            tableName = DbHelper.TABLE_WATER_NAME;
        }

        database.delete(tableName, DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_DATE + " = ?",
                new String[]{record.getName(), record.getDate()});
    }

    private void writeMeal(final Meal meal, boolean writeOnlyUniqueMeals) {
        if (!writeOnlyUniqueMeals || isUniqueMeal(meal)) {
            long rowId = database.insert(DbHelper.TABLE_MEALS_NAME,
                    null, RepositoryConverter.mealToContentValues(meal));
            logWriteIntoDatabase(DbHelper.TABLE_MEALS_NAME, rowId);
        }
    }

    private boolean isUniqueMeal(Meal meal) {
        Cursor cursor = database.query(DbHelper.TABLE_MEALS_NAME, DbHelper.MEALS_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_DATE + " = ? ",
                new String[]{meal.getName(), meal.getDate()}, null, null, null);
        boolean isUnique = !cursor.moveToFirst();
        cursor.close();
        return isUnique;
    }

    private int getMealId(Meal meal) {
        Cursor cursor = database.query(DbHelper.TABLE_MEALS_NAME, new String[]{DbHelper.COLUMN_MEALS_ID},
                DbHelper.COLUMN_DATE + " = ? AND " + DbHelper.COLUMN_NAME + " = ?",
                new String[]{meal.getDate(), meal.getName()}, null, null, null);

        if (cursor.moveToFirst()) {
            int mealId = Integer.parseInt(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_MEALS_ID)));
            cursor.close();
            return mealId;
        } else {
            cursor.close();
            return -1;
        }
    }

    public List<Dish> findDishesByMeal(Meal meal) {
        int mealId = getMealId(meal);

        String JOIN_QUERY =
                DbHelper.TABLE_MEALS_NAME + " INNER JOIN " + DbHelper.TABLE_MEALS_DISHES_NAME + " ON "
                        + DbHelper.TABLE_MEALS_DISHES_NAME + "." + DbHelper.COLUMN_MEALS_ID + " =  "
                        + DbHelper.TABLE_MEALS_NAME + "." + DbHelper.COLUMN_MEALS_ID
                        + " INNER JOIN " + DbHelper.TABLE_DISHES_NAME + " ON " + DbHelper.TABLE_DISHES_NAME +
                        "." + DbHelper.COLUMN_DISHES_ID + " = " + DbHelper.TABLE_MEALS_DISHES_NAME +
                        "." + DbHelper.COLUMN_DISHES_ID;

        Cursor cursor = database.query(JOIN_QUERY, DbHelper.FIND_DISHES_BY_MEAL_COLUMN_ARRAY,
                DbHelper.TABLE_MEALS_NAME + "." + DbHelper.COLUMN_MEALS_ID + " = ?",
                new String[]{String.valueOf(mealId)}, null, null, null);

        List<Dish> dishes = new ArrayList<>();
        while (cursor.moveToNext()) {
            dishes.add(RepositoryConverter.cursorToDishWithWeight(cursor));
        }
        cursor.close();

        return dishes;
    }

    public List<PhysicalExercise> findExercisesByTraining(PhysicalTraining physicalTraining) {
        int physicalTrainingId = getTrainingId(physicalTraining);

        String JOIN_QUERY =
                DbHelper.TABLE_TRAININGS_NAME
                        + " INNER JOIN " + DbHelper.TABLE_TRAININGS_EXERCISES_NAME + " ON "
                        + DbHelper.TABLE_TRAININGS_EXERCISES_NAME + "." + DbHelper.COLUMN_TRAININGS_ID
                        + " =  " + DbHelper.TABLE_TRAININGS_NAME + "." + DbHelper.COLUMN_TRAININGS_ID
                        + " INNER JOIN " + DbHelper.TABLE_EXERCISES_NAME + " ON "
                        + DbHelper.TABLE_EXERCISES_NAME + "." + DbHelper.COLUMN_EXERCISES_ID
                        + " = " + DbHelper.TABLE_TRAININGS_EXERCISES_NAME +
                        "." + DbHelper.COLUMN_EXERCISES_ID;

        Cursor cursor = database.query(JOIN_QUERY, DbHelper.FIND_EXERCISES_BY_TRAINING_COLUMN_ARRAY,
                DbHelper.TABLE_TRAININGS_NAME + "." + DbHelper.COLUMN_TRAININGS_ID + " = ?",
                new String[]{String.valueOf(physicalTrainingId)}, null, null, null);

        List<PhysicalExercise> exercises = new ArrayList<>();
        while (cursor.moveToNext()) {
            exercises.add(RepositoryConverter.cursorToExerciseWithTime(cursor));
        }
        cursor.close();

        return exercises;
    }

    public void writeDish(final Dish dish) {
        long rowId = database.insert(DbHelper.TABLE_DISHES_NAME,
                null, RepositoryConverter.dishToContentValues(dish));
        logWriteIntoDatabase(DbHelper.TABLE_DISHES_NAME, rowId);
    }

    private int getDishId(Dish dish) {
        Cursor cursor = database.query(DbHelper.TABLE_DISHES_NAME, new String[]{DbHelper.COLUMN_DISHES_ID},
                DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_CALORIES + " = ? AND " +
                        DbHelper.COLUMN_PROTEIN + " = ? AND " + DbHelper.COLUMN_FAT + " = ? AND " +
                        DbHelper.COLUMN_CARBOHYDRATES + " = ?",
                new String[]{dish.getName(), String.valueOf(dish.getCalories()),
                        String.valueOf(dish.getProtein()), String.valueOf(dish.getFat()),
                        String.valueOf(dish.getCarbohydrates())}, null, null, null);

        if (cursor.moveToFirst()) {
            int dishId = Integer.parseInt(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_DISHES_ID)));
            cursor.close();
            return dishId;
        } else {
            cursor.close();
            return -1;
        }
    }

    public boolean isDishesByDayExist(Calendar calendar) {
        for (Meal meal : findMealsByDate(calendar)) {
            if (!findDishesByMeal(meal).isEmpty()) {
                return true;
            }
        }
        return false;
    }

    public void writeMealDish(Meal meal, Dish dish) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DbHelper.COLUMN_MEALS_ID, getMealId(meal));
        contentValues.put(DbHelper.COLUMN_DISHES_ID, getDishId(dish));
        contentValues.put(DbHelper.COLUMN_WEIGHT, dish.getWeight());
        database.insert(DbHelper.TABLE_MEALS_DISHES_NAME, null, contentValues);
    }

    public void writeTrainingExercise(PhysicalTraining training, PhysicalExercise exercise) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DbHelper.COLUMN_TRAININGS_ID, getTrainingId(training));
        contentValues.put(DbHelper.COLUMN_EXERCISES_ID, getExerciseId(exercise));
        contentValues.put(DbHelper.COLUMN_TIME, exercise.getTime());
        database.insert(DbHelper.TABLE_TRAININGS_EXERCISES_NAME, null, contentValues);
    }

    private int getTrainingId(PhysicalTraining training) {
        Cursor cursor = database.query(DbHelper.TABLE_TRAININGS_NAME, new String[]{DbHelper.COLUMN_TRAININGS_ID},
                DbHelper.COLUMN_DATE + " = ? AND " + DbHelper.COLUMN_NAME + " = ?",
                new String[]{training.getDate(), training.getName()}, null, null, null);

        if (cursor.moveToFirst()) {
            int mealId = Integer.parseInt(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_TRAININGS_ID)));
            cursor.close();
            return mealId;
        } else {
            cursor.close();
            return -1;
        }
    }

    private int getExerciseId(PhysicalExercise exercise) {
        Cursor cursor = database.query(DbHelper.TABLE_EXERCISES_NAME, new String[]{DbHelper.COLUMN_EXERCISES_ID},
                DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_CALORIES + " = ?",
                new String[]{exercise.getName(), String.valueOf(exercise.getCalories())}, null, null, null);

        if (cursor.moveToFirst()) {
            int dishId = Integer.parseInt(cursor.getString(cursor.getColumnIndex(DbHelper.COLUMN_EXERCISES_ID)));
            cursor.close();
            return dishId;
        } else {
            cursor.close();
            return -1;
        }
    }

    public List<Meal> findMealsByDate(Calendar calendar) {
        Cursor cursor = database.query(DbHelper.TABLE_MEALS_NAME, DbHelper.MEALS_COLUMN_ARRAY,
                DbHelper.COLUMN_DATE + " = ?", new String[]{DateUtils.calendarToString(calendar)},
                null, null, null);

        List<Meal> meals = new ArrayList<>();
        while (cursor.moveToNext()) {
            meals.add(RepositoryConverter.cursorToMeal(cursor));
        }
        cursor.close();

        return meals;
    }

    private void writeTraining(PhysicalTraining training, boolean writeOnlyUniqueTrainings) {
        if (!writeOnlyUniqueTrainings || isUniqueTraining(training)) {
            long rowId = database.insert(DbHelper.TABLE_TRAININGS_NAME,
                    null, RepositoryConverter.trainingToContentValues(training));
            logWriteIntoDatabase(DbHelper.TABLE_TRAININGS_NAME, rowId);
        }
    }

    private boolean isUniqueTraining(PhysicalTraining training) {
        Cursor cursor = database.query(DbHelper.TABLE_TRAININGS_NAME, DbHelper.TRAININGS_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_DATE + " = ? ",
                new String[]{training.getName(), training.getDate()}, null, null, null);
        boolean isUnique = !cursor.moveToFirst();
        cursor.close();
        return isUnique;
    }

    public List<PhysicalTraining> findTrainingsByDate(Calendar calendar) {
        Cursor cursor = database.query(DbHelper.TABLE_TRAININGS_NAME, DbHelper.TRAININGS_COLUMN_ARRAY,
                DbHelper.COLUMN_DATE + " = ?", new String[]{DateUtils.calendarToString(calendar)},
                null, null, null);

        List<PhysicalTraining> trainings = new ArrayList<>();
        while (cursor.moveToNext()) {
            trainings.add(RepositoryConverter.cursorToTraining(cursor));
        }
        cursor.close();

        return trainings;
    }

    public void addDishesFromCsv(List<String[]> lineList) {
        if (lineList != null) {
            database.beginTransaction();
            for (String[] dishValuesArray : lineList) {
                ContentValues dishContentValues =
                        RepositoryConverter.dishValuesArrayToContentValues(dishValuesArray);
                if (dishContentValues != null) {
                    database.insert(DbHelper.TABLE_DISHES_NAME, null, dishContentValues);
                }
            }
            database.setTransactionSuccessful();
            database.endTransaction();
        }
    }

    public void addPhysicalExercisesFromCsv(List<String[]> lineList) {
        if (lineList != null) {
            database.beginTransaction();
            for (String[] exercisesValuesArray : lineList) {
                ContentValues exercisesContentValues =
                        RepositoryConverter.exerciseValuesArrayToContentValues(exercisesValuesArray);
                if (exercisesContentValues != null) {
                    database.insert(DbHelper.TABLE_EXERCISES_NAME, null, exercisesContentValues);
                }
            }
            database.setTransactionSuccessful();
            database.endTransaction();
        }
    }

    private void writeWater(Water water, boolean writeOnlyUniqueWater) {
        if (!writeOnlyUniqueWater || isUniqueWater(water)) {
            long rowId = database.insert(DbHelper.TABLE_WATER_NAME,
                    null, RepositoryConverter.waterToContentValues(water));
            logWriteIntoDatabase(DbHelper.TABLE_WATER_NAME, rowId);
        }
    }

    public Water findWaterByDate(Calendar calendar) {
        Cursor cursor = database.query(DbHelper.TABLE_WATER_NAME, DbHelper.WATER_COLUMN_ARRAY,
                DbHelper.COLUMN_DATE + " = ?", new String[]{DateUtils.calendarToString(calendar)},
                null, null, null);

        Water water = null;
        if (cursor.moveToFirst()) {
            water = RepositoryConverter.cursorToWater(cursor);
        }
        cursor.close();
        return water;
    }

    private boolean isUniqueWater(Water water) {
        Cursor cursor = database.query(DbHelper.TABLE_WATER_NAME, new String[]{DbHelper.COLUMN_DATE},
                DbHelper.COLUMN_NAME + " = ? AND " + DbHelper.COLUMN_DATE + " = ? ",
                new String[]{water.getName(), water.getDate()}, null, null, null);
        boolean isUnique = !cursor.moveToFirst();
        cursor.close();
        return isUnique;
    }

    private void logWriteIntoDatabase(String tableName, long rowId) {
        Log.d(LOG_TAG, tableName + " : row inserted, ID = " + rowId);
    }

    public List<Dish> findDishByNameInFavorite(String findString) {
        Cursor findCursor = database.query(DbHelper.TABLE_DISHES_NAME, DbHelper.DISHES_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " LIKE ? and " + DbHelper.COLUMN_FAVORITE + "=?",
                new String[]{"%" + findString + "%", "1"}, null, null, DbHelper.COLUMN_CUSTOM + " DESC");
        return RepositoryConverter.cursorToDishArrayList(findCursor);
    }

    public List<Dish> findDishByNameInAllDatabase(String findString) {
        Cursor findCursor = database.query(DbHelper.TABLE_DISHES_NAME, DbHelper.DISHES_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " LIKE ?",
                new String[]{"%" + findString + "%"},
                null, null, DbHelper.COLUMN_FAVORITE + " DESC" + "," + DbHelper.COLUMN_CUSTOM + " DESC");
        return RepositoryConverter.cursorToDishArrayList(findCursor);
    }

    public List<Dish> findDishByNameInCustom(String findString) {
        Cursor findCursor = database.query(DbHelper.TABLE_DISHES_NAME, DbHelper.DISHES_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " LIKE ? and " + DbHelper.COLUMN_CUSTOM + "=?",
                new String[]{"%" + findString + "%", "1"},
                null, null, DbHelper.COLUMN_FAVORITE + " DESC");
        return RepositoryConverter.cursorToDishArrayList(findCursor);
    }

    public int updateFavorite(Dish dish) {
        ContentValues cv = new ContentValues();
        cv.put(DbHelper.COLUMN_FAVORITE, dish.isFavorite());
        return database.update(DbHelper.TABLE_DISHES_NAME, cv, DbHelper.COLUMN_DISHES_ID + " = ?",
                new String[]{String.valueOf(dish.getId())});
    }

    public List<PhysicalExercise> findExercisesByName(String exerciseName) {
        Cursor findCursor = database.query(DbHelper.TABLE_EXERCISES_NAME, DbHelper.EXERCISES_COLUMN_ARRAY,
                DbHelper.COLUMN_NAME + " LIKE ?",
                new String[]{"%" + exerciseName + "%"},
                null, null, DbHelper.COLUMN_NAME);
        return RepositoryConverter.cursorToExerciseArrayList(findCursor);
    }
}