package at.ac.tuwien.sepm.assignment.groupphase.demeter.dao;

import at.ac.tuwien.sepm.assignment.groupphase.demeter.dao.InterfacesDAO.IDishDAO;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dao.InterfacesDAO.IImageManagementDAO;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.Allergen;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.Dish;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.DishSearch;
import at.ac.tuwien.sepm.assignment.groupphase.demeter.dto.Ingredient;
import at.ac.tuwien.sepm.assignment.groupphase.exception.FileException;
import at.ac.tuwien.sepm.assignment.groupphase.exception.PersistenceException;
import at.ac.tuwien.sepm.assignment.groupphase.util.Category;
import at.ac.tuwien.sepm.assignment.groupphase.util.JDBCConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.lang.invoke.MethodHandles;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class DishDAO implements IDishDAO {
    private static final Logger LOG = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());

    private static final String GET_ALL_DISHES_QUERY = "SELECT * FROM dish WHERE deleted = false";
    private static final String GET_ALL_INGREDIENTS_FOR_THIS_DISH = "SELECT * FROM ingredient WHERE id IN (SELECT ingredient_id FROM dish_ingredient WHERE dish_id = ?)";
    private static final String ADD_NEW_DISH = "INSERT INTO dish (name, description, price, category, deleted) VALUES (?, ?, ?, ?, ?)";
    private static final String ADD_INGREDIENTS_TO_DISHES = "INSERT INTO dish_ingredient (dish_id, ingredient_id) VALUES (?, ?)";
    private static final String UPDATE_DISH = "UPDATE dish SET name = ?, description = ?, price = ?, category = ? WHERE id = ? ";
    private static final String DELETE_INGREDIENTS_IN_DISH = "DELETE FROM dish_ingredient WHERE dish_id = ?";
    private static final String DELETE_DISH = "UPDATE dish SET deleted = true WHERE id = ?";
    private static final String GET_ALLERGENS_IN_DISH = "SELECT * FROM allergen WHERE symbol IN (SELECT allergen_symbol FROM dish_allergen WHERE dish_id = ?)";
    private static final String ADD_ALLERGENS_TO_DISH = "INSERT INTO dish_allergen (dish_id, allergen_symbol) VALUES (?,?)";
    private static final String DELETE_ALLERGENS_IN_DISH = "DELETE FROM dish_allergen WHERE dish_id = ?";
    private static final String SEARCH_DISHES = "select dish.id, dish.name, dish.description, dish.price, dish.category FROM dish JOIN (SELECT dish_id, ingredient.name FROM dish_ingredient JOIN ingredient ON ingredient_id = ingredient.id where ingredient.name regexp ?) ing ON dish.id = ing.dish_id where dish.id not in (SELECT dish_id AS id FROM dish_allergen JOIN ALLERGEN ON allergen_symbol = symbol WHERE allergen_symbol regexp ?) group by dish.id having count(*) LIKE ? AND dish.deleted = false AND dish.name ILIKE ? AND dish.price BETWEEN ? AND ?";

    private IImageManagementDAO imageManagement;
    private Connection connection;

    public DishDAO(JDBCConnectionManager jdbcConnectionManager, IImageManagementDAO imageManagement) throws PersistenceException {
        try {
            connection = jdbcConnectionManager.getConnection();
            this.imageManagement = imageManagement;
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }


    @Override
    public ArrayList<Dish> getAllDishes() throws PersistenceException {
        ArrayList<Dish> searchResult = new ArrayList<>();
        LOG.info("Retrieving all dishes from the database");
        try {
            var getAllDishesStatement = connection.prepareStatement(GET_ALL_DISHES_QUERY);
            var resultSet = getAllDishesStatement.executeQuery();
            while (resultSet.next()){
                var dish = new Dish();
                dish.setId(resultSet.getLong(1));
                dish.setName(resultSet.getString(2));
                dish.setDescription(resultSet.getString(3));
                dish.setPrice(resultSet.getDouble(4));
                dish.setCategory(Category.valueOf(resultSet.getString(5)));
                getIngredients(dish);
                getAllergensInDish(dish);

                try {
                    dish.setImageFile(imageManagement.getImageFile(dish.getId()));
                    if(dish.getImageFile()==null){
                        dish.setPathOfImage(null);
                    }
                    else {
                        dish.setPathOfImage(dish.getImageFile().getPath());
                    }
                }catch (FileException e){
                    dish.setImageFile(null);
                }

                searchResult.add(dish);
            }
            getAllDishesStatement.close();
            resultSet.close();
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
        return searchResult;
    }

    @Override
    public ArrayList<Dish> searchDishes(DishSearch ds) throws PersistenceException {
        ArrayList<Dish> searchResult = new ArrayList<>();
        LOG.info("Searching dishes in the database");
        try {
            var searchStatement = connection.prepareStatement(SEARCH_DISHES);

            List<Ingredient> ingredients = ds.getIngredients();
            String ingNr = "%";
            StringBuilder ingRegEx = new StringBuilder(".*");
            if(!ingredients.isEmpty()){
                ingNr = String.valueOf(ingredients.size());
                ingRegEx = new StringBuilder();
                for (Ingredient in: ingredients) {
                    ingRegEx.append(in.getName()).append("|");
                }
                ingRegEx.deleteCharAt(ingRegEx.length()-1);
            }
            searchStatement.setString(1,ingRegEx.toString());
            searchStatement.setString(3,ingNr);

            StringBuilder allRegEx = new StringBuilder(" ");
            List<Allergen> allergens = ds.getAllergens();
            if(!allergens.isEmpty()){
                allRegEx = new StringBuilder();
                for (Allergen all: allergens) {
                    allRegEx.append(all.getSymbol()).append("|");
                }
                allRegEx.deleteCharAt(allRegEx.length()-1);
            }
            searchStatement.setString(2,allRegEx.toString());

            searchStatement.setString(4,"%" + ds.getName() + "%");

            if(ds.getPriceFrom() == null){
                searchStatement.setDouble(5,0d);
            }else{
                searchStatement.setDouble(5,ds.getPriceFrom());
            }

            if(ds.getPriceTo() == null){
                searchStatement.setDouble(6,999999d);
            }else{
                searchStatement.setDouble(6,ds.getPriceTo());
            }

            LOG.debug("Executing query: " + searchStatement.toString());

            var resultSet = searchStatement.executeQuery();
            while (resultSet.next()){
                var dish = new Dish();
                dish.setId(resultSet.getLong(1));
                dish.setName(resultSet.getString(2));
                dish.setDescription(resultSet.getString(3));
                dish.setPrice(resultSet.getDouble(4));
                dish.setCategory(Category.valueOf(resultSet.getString(5)));
                getIngredients(dish);
                getAllergensInDish(dish);

                try {
                    dish.setImageFile(imageManagement.getImageFile(dish.getId()));
                    if(dish.getImageFile() != null){
                        dish.setPathOfImage(dish.getImageFile().getPath());
                    }
                }catch (FileException e){
                    dish.setImageFile(null);
                }

                searchResult.add(dish);
            }

            searchStatement.close();
            resultSet.close();

        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }

        LOG.debug("Returning list: " + searchResult.toString());
        return searchResult;
    }


    @Override
    public ArrayList<Ingredient> getIngredients(Dish dish) throws PersistenceException {
        ArrayList<Ingredient> searchResult = new ArrayList<>();
        LOG.info("Retrieving all ingredients for this dish");
        try {
            var getAllIngredientsStatement = connection.prepareStatement(GET_ALL_INGREDIENTS_FOR_THIS_DISH);
            getAllIngredientsStatement.setLong(1, dish.getId());
            var resultSet = getAllIngredientsStatement.executeQuery();
            while (resultSet.next()){
                var ingredient = new Ingredient();
                ingredient.setId(resultSet.getLong(1));
                ingredient.setName(resultSet.getString(2));
                ingredient.setDeleted(resultSet.getBoolean(3));
                searchResult.add(ingredient);
            }
            getAllIngredientsStatement.close();
            resultSet.close();
        }catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
        dish.setListOfIngredients(searchResult);
        return searchResult;
    }

    @Override
    public void addNewDish(Dish dish) throws PersistenceException {
        LOG.info("Adding new dish");
        try{
            var addNewDishStatement = connection.prepareStatement(ADD_NEW_DISH, Statement.RETURN_GENERATED_KEYS);
            addNewDishStatement.setString(1, dish.getName());
            addNewDishStatement.setString(2, dish.getDescription());
            addNewDishStatement.setDouble(3, dish.getPrice());
            addNewDishStatement.setString(4, dish.getCategory().name());
            addNewDishStatement.setBoolean(5, false);
            addNewDishStatement.executeUpdate();

            var generatedKeys = addNewDishStatement.getGeneratedKeys();
            generatedKeys.next();
            dish.setId(generatedKeys.getLong(1));

            if (dish.getImageFile() != null && dish.getImageFile().exists()){
                imageManagement.createImageFile(dish.getImageFile(), dish.getId());
            }

            saveDishIngredients(dish);
            saveDishAllergens(dish);

            addNewDishStatement.close();
        }catch (SQLException | FileException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void saveDishAllergens(Dish dish) throws PersistenceException {
        try {
            var addAllergensToDish = connection.prepareStatement(ADD_ALLERGENS_TO_DISH);
            for (var i : dish.getListOfAllergens()) {
                addAllergensToDish.setLong(1, dish.getId());
                addAllergensToDish.setString(2, i.getSymbol().toString());
                addAllergensToDish.executeUpdate();
            }
            addAllergensToDish.close();
        }catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void saveDishIngredients(Dish dish) throws PersistenceException {
        try{
            var addIngredientsToDish = connection.prepareStatement(ADD_INGREDIENTS_TO_DISHES);
            for (var i : dish.getListOfIngredients()) {
                addIngredientsToDish.setLong(1, dish.getId());
                addIngredientsToDish.setLong(2, i.getId());
                addIngredientsToDish.executeUpdate();
            }
            addIngredientsToDish.close();
        }catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void deleteDishIngredientsAllergens(Dish dish, String query) throws PersistenceException {
        try {
            var deleteIngredientsStatement = connection.prepareStatement(query);
            deleteIngredientsStatement.setLong(1, dish.getId());
            deleteIngredientsStatement.executeUpdate();
            deleteIngredientsStatement.close();

            if (query.equals("UPDATE dish SET deleted = true WHERE id = ?")){
                imageManagement.deleteImageFile(dish);
            }

        } catch (SQLException | FileException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void updateDish(Dish dish) throws PersistenceException {
        LOG.info("Updating dish");
        try {
            var updateDishStatement = connection.prepareStatement(UPDATE_DISH);
            updateDishStatement.setString(1, dish.getName());
            updateDishStatement.setString(2, dish.getDescription());
            updateDishStatement.setDouble(3, dish.getPrice());
            updateDishStatement.setString(4, dish.getCategory().name());
            updateDishStatement.setLong(5, dish.getId());

            updateDishStatement.executeUpdate();
            deleteDishIngredientsAllergens(dish, DELETE_INGREDIENTS_IN_DISH);
            saveDishIngredients(dish);
            deleteDishIngredientsAllergens(dish, DELETE_ALLERGENS_IN_DISH);
            saveDishAllergens(dish);

            if (dish.getPathOfImage() != null) {
                imageManagement.deleteImageFile(dish);
                if (dish.getImageFile() != null && dish.getImageFile().exists()) {
                    imageManagement.createImageFile(dish.getImageFile(), dish.getId());
                }
            }

            updateDishStatement.close();
        } catch (SQLException | FileException e) {
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
    }

    @Override
    public void deleteDish(Dish dish) throws PersistenceException {
        LOG.info("Deleting dish.");
        deleteDishIngredientsAllergens(dish, DELETE_DISH);
        deleteDishIngredientsAllergens(dish, DELETE_INGREDIENTS_IN_DISH);
        deleteDishIngredientsAllergens(dish, DELETE_ALLERGENS_IN_DISH);
    }

    @Override
    public ArrayList<Allergen> getAllergensInDish(Dish dish) throws PersistenceException {
        ArrayList<Allergen> searchResult = new ArrayList<>();
        try {
            var getAllergensInDish = connection.prepareStatement(GET_ALLERGENS_IN_DISH);
            getAllergensInDish.setLong(1, dish.getId());
            var resultSet = getAllergensInDish.executeQuery();
            while (resultSet.next()){
                var allergen = new Allergen();
                allergen.setName(resultSet.getString(2));
                allergen.setSymbol(resultSet.getString(1).charAt(0));
                searchResult.add(allergen);
            }
            getAllergensInDish.close();
        }catch (SQLException e){
            LOG.error(e.getMessage());
            throw new PersistenceException(e);
        }
        dish.setListOfAllergens(searchResult);
        return searchResult;
    }
}