package de.effigenix.infrastructure.production.persistence;

import de.effigenix.domain.production.*;
import de.effigenix.shared.common.Quantity;
import de.effigenix.shared.common.RepositoryError;
import de.effigenix.shared.common.Result;
import de.effigenix.shared.common.UnitOfMeasure;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcRecipeRepository implements RecipeRepository {

    private static final Logger logger = LoggerFactory.getLogger(JdbcRecipeRepository.class);

    private final JdbcClient jdbc;

    public JdbcRecipeRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    @Override
    public Result<RepositoryError, Optional<Recipe>> findById(RecipeId id) {
        try {
            var recipeOpt = jdbc.sql("SELECT * FROM recipes WHERE id = :id")
                    .param("id", id.value())
                    .query(this::mapRecipeRow)
                    .optional();
            if (recipeOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            var recipe = loadChildren(recipeOpt.get());
            return Result.success(Optional.of(recipe));
        } catch (Exception e) {
            logger.trace("Database error in findById", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Recipe>> findAll() {
        try {
            var recipes = jdbc.sql("SELECT * FROM recipes ORDER BY name, version")
                    .query(this::mapRecipeRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(recipes);
        } catch (Exception e) {
            logger.trace("Database error in findAll", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(Recipe recipe) {
        try {
            int rows = jdbc.sql("""
                    UPDATE recipes
                    SET name = :name, version = :version, type = :type, description = :description,
                        yield_percentage = :yieldPercentage, shelf_life_days = :shelfLifeDays,
                        output_quantity = :outputQuantity, output_uom = :outputUom,
                        article_id = :articleId, status = :status, updated_at = :updatedAt
                    WHERE id = :id
                    """)
                    .param("id", recipe.id().value())
                    .param("name", recipe.name().value())
                    .param("version", recipe.version())
                    .param("type", recipe.type().name())
                    .param("description", recipe.description())
                    .param("yieldPercentage", recipe.yieldPercentage().value())
                    .param("shelfLifeDays", recipe.shelfLifeDays())
                    .param("outputQuantity", recipe.outputQuantity().amount())
                    .param("outputUom", recipe.outputQuantity().uom().name())
                    .param("articleId", recipe.articleId())
                    .param("status", recipe.status().name())
                    .param("updatedAt", recipe.updatedAt())
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO recipes
                        (id, name, version, type, description, yield_percentage, shelf_life_days,
                         output_quantity, output_uom, article_id, status, created_at, updated_at)
                        VALUES (:id, :name, :version, :type, :description, :yieldPercentage, :shelfLifeDays,
                                :outputQuantity, :outputUom, :articleId, :status, :createdAt, :updatedAt)
                        """)
                        .param("id", recipe.id().value())
                        .param("name", recipe.name().value())
                        .param("version", recipe.version())
                        .param("type", recipe.type().name())
                        .param("description", recipe.description())
                        .param("yieldPercentage", recipe.yieldPercentage().value())
                        .param("shelfLifeDays", recipe.shelfLifeDays())
                        .param("outputQuantity", recipe.outputQuantity().amount())
                        .param("outputUom", recipe.outputQuantity().uom().name())
                        .param("articleId", recipe.articleId())
                        .param("status", recipe.status().name())
                        .param("createdAt", recipe.createdAt())
                        .param("updatedAt", recipe.updatedAt())
                        .update();
            }

            saveIngredients(recipe);
            saveProductionSteps(recipe);

            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in save", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> delete(Recipe recipe) {
        try {
            jdbc.sql("DELETE FROM recipes WHERE id = :id")
                    .param("id", recipe.id().value())
                    .update();
            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in delete", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByNameAndVersion(String name, int version) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM recipes WHERE name = :name AND version = :version")
                    .param("name", name)
                    .param("version", version)
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByNameAndVersion", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Recipe>> findByStatus(RecipeStatus status) {
        try {
            var recipes = jdbc.sql("SELECT * FROM recipes WHERE status = :status ORDER BY name, version")
                    .param("status", status.name())
                    .query(this::mapRecipeRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(recipes);
        } catch (Exception e) {
            logger.trace("Database error in findByStatus", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Recipe>> findByArticleId(String articleId) {
        try {
            var recipes = jdbc.sql("SELECT * FROM recipes WHERE article_id = :articleId ORDER BY name, version")
                    .param("articleId", articleId)
                    .query(this::mapRecipeRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(recipes);
        } catch (Exception e) {
            logger.trace("Database error in findByArticleId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    private void saveIngredients(Recipe recipe) {
        jdbc.sql("DELETE FROM recipe_ingredients WHERE recipe_id = :recipeId")
                .param("recipeId", recipe.id().value())
                .update();

        for (Ingredient ingredient : recipe.ingredients()) {
            jdbc.sql("""
                    INSERT INTO recipe_ingredients
                    (id, recipe_id, position, article_id, quantity, uom, sub_recipe_id, substitutable)
                    VALUES (:id, :recipeId, :position, :articleId, :quantity, :uom, :subRecipeId, :substitutable)
                    """)
                    .param("id", ingredient.id().value())
                    .param("recipeId", recipe.id().value())
                    .param("position", ingredient.position())
                    .param("articleId", ingredient.articleId())
                    .param("quantity", ingredient.quantity().amount())
                    .param("uom", ingredient.quantity().uom().name())
                    .param("subRecipeId", ingredient.subRecipeId())
                    .param("substitutable", ingredient.substitutable())
                    .update();
        }
    }

    private void saveProductionSteps(Recipe recipe) {
        jdbc.sql("DELETE FROM recipe_production_steps WHERE recipe_id = :recipeId")
                .param("recipeId", recipe.id().value())
                .update();

        for (ProductionStep step : recipe.productionSteps()) {
            jdbc.sql("""
                    INSERT INTO recipe_production_steps
                    (id, recipe_id, step_number, description, duration_minutes, temperature_celsius)
                    VALUES (:id, :recipeId, :stepNumber, :description, :durationMinutes, :temperatureCelsius)
                    """)
                    .param("id", step.id().value())
                    .param("recipeId", recipe.id().value())
                    .param("stepNumber", step.stepNumber())
                    .param("description", step.description())
                    .param("durationMinutes", step.durationMinutes())
                    .param("temperatureCelsius", step.temperatureCelsius())
                    .update();
        }
    }

    private Recipe loadChildren(Recipe recipe) {
        var ingredients = jdbc.sql(
                        "SELECT * FROM recipe_ingredients WHERE recipe_id = :recipeId ORDER BY position ASC")
                .param("recipeId", recipe.id().value())
                .query(this::mapIngredientRow)
                .list();

        var steps = jdbc.sql(
                        "SELECT * FROM recipe_production_steps WHERE recipe_id = :recipeId ORDER BY step_number ASC")
                .param("recipeId", recipe.id().value())
                .query(this::mapStepRow)
                .list();

        return Recipe.reconstitute(
                recipe.id(), recipe.name(), recipe.version(), recipe.type(),
                recipe.description(), recipe.yieldPercentage(), recipe.shelfLifeDays(),
                recipe.outputQuantity(), recipe.articleId(), recipe.status(),
                ingredients, steps, recipe.createdAt(), recipe.updatedAt()
        );
    }

    private Recipe mapRecipeRow(ResultSet rs, int rowNum) throws SQLException {
        return Recipe.reconstitute(
                RecipeId.of(rs.getString("id")),
                new RecipeName(rs.getString("name")),
                rs.getInt("version"),
                RecipeType.valueOf(rs.getString("type")),
                rs.getString("description"),
                new YieldPercentage(rs.getInt("yield_percentage")),
                rs.getObject("shelf_life_days", Integer.class),
                Quantity.reconstitute(
                        rs.getBigDecimal("output_quantity"),
                        UnitOfMeasure.valueOf(rs.getString("output_uom"))
                ),
                rs.getString("article_id"),
                RecipeStatus.valueOf(rs.getString("status")),
                List.of(),
                List.of(),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class)
        );
    }

    private Ingredient mapIngredientRow(ResultSet rs, int rowNum) throws SQLException {
        return Ingredient.reconstitute(
                IngredientId.of(rs.getString("id")),
                rs.getInt("position"),
                rs.getString("article_id"),
                Quantity.reconstitute(
                        rs.getBigDecimal("quantity"),
                        UnitOfMeasure.valueOf(rs.getString("uom"))
                ),
                rs.getString("sub_recipe_id"),
                rs.getBoolean("substitutable")
        );
    }

    private ProductionStep mapStepRow(ResultSet rs, int rowNum) throws SQLException {
        return ProductionStep.reconstitute(
                ProductionStepId.of(rs.getString("id")),
                rs.getInt("step_number"),
                rs.getString("description"),
                rs.getObject("duration_minutes", Integer.class),
                rs.getObject("temperature_celsius", Integer.class)
        );
    }
}
