package de.effigenix.infrastructure.production.persistence;

import de.effigenix.domain.masterdata.article.ArticleId;
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.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcBatchRepository implements BatchRepository {

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

    private final JdbcClient jdbc;

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

    @Override
    public Result<RepositoryError, Optional<Batch>> findById(BatchId id) {
        try {
            var batchOpt = jdbc.sql("SELECT * FROM batches WHERE id = :id")
                    .param("id", id.value())
                    .query(this::mapBatchRow)
                    .optional();
            if (batchOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            var consumptions = loadConsumptions(id.value());
            return Result.success(Optional.of(reconstituteWithConsumptions(batchOpt.get(), consumptions)));
        } catch (Exception e) {
            logger.trace("Database error in findById", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, Optional<Batch>> findByBatchNumber(BatchNumber batchNumber) {
        try {
            var batchOpt = jdbc.sql("SELECT * FROM batches WHERE batch_number = :batchNumber")
                    .param("batchNumber", batchNumber.value())
                    .query(this::mapBatchRow)
                    .optional();
            if (batchOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            var consumptions = loadConsumptions(batchOpt.get().id().value());
            return Result.success(Optional.of(reconstituteWithConsumptions(batchOpt.get(), consumptions)));
        } catch (Exception e) {
            logger.trace("Database error in findByBatchNumber", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, List<Batch>> findByProductionDate(LocalDate date) {
        try {
            var batches = jdbc.sql("SELECT * FROM batches WHERE production_date = :date ORDER BY created_at DESC")
                    .param("date", date)
                    .query(this::mapBatchRow)
                    .list()
                    .stream()
                    .map(this::loadWithConsumptions)
                    .toList();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByProductionDate", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findByRecipeIds(List<RecipeId> recipeIds) {
        try {
            List<String> ids = recipeIds.stream().map(RecipeId::value).toList();
            var batches = jdbc.sql("SELECT * FROM batches WHERE recipe_id IN (:ids) ORDER BY created_at DESC")
                    .param("ids", ids)
                    .query(this::mapBatchRow)
                    .list()
                    .stream()
                    .map(this::loadWithConsumptions)
                    .toList();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByRecipeIds", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findAllSummary() {
        try {
            var batches = jdbc.sql("SELECT * FROM batches ORDER BY created_at DESC")
                    .query(this::mapBatchRow)
                    .list();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findAllSummary", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findByStatusSummary(BatchStatus status) {
        try {
            var batches = jdbc.sql("SELECT * FROM batches WHERE status = :status ORDER BY created_at DESC")
                    .param("status", status.name())
                    .query(this::mapBatchRow)
                    .list();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByStatusSummary", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findByProductionDateSummary(LocalDate date) {
        try {
            var batches = jdbc.sql("SELECT * FROM batches WHERE production_date = :date ORDER BY created_at DESC")
                    .param("date", date)
                    .query(this::mapBatchRow)
                    .list();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByProductionDateSummary", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findByRecipeIdsSummary(List<RecipeId> recipeIds) {
        try {
            List<String> ids = recipeIds.stream().map(RecipeId::value).toList();
            var batches = jdbc.sql("SELECT * FROM batches WHERE recipe_id IN (:ids) ORDER BY created_at DESC")
                    .param("ids", ids)
                    .query(this::mapBatchRow)
                    .list();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByRecipeIdsSummary", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Batch>> findByInputBatchId(BatchId inputBatchId) {
        try {
            var batches = jdbc.sql("""
                    SELECT b.* FROM batches b
                    JOIN batch_consumptions bc ON b.id = bc.batch_id
                    WHERE bc.input_batch_id = :inputBatchId
                    """)
                    .param("inputBatchId", inputBatchId.value())
                    .query(this::mapBatchRow)
                    .list();
            return Result.success(batches);
        } catch (Exception e) {
            logger.trace("Database error in findByInputBatchId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<TracedBatch>> traceForward(BatchId startBatchId, int maxDepth) {
        try {
            String startId = startBatchId.value();
            var results = new ArrayList<TracedBatch>();
            var visited = new HashSet<String>();
            visited.add(startId);

            var currentLevel = List.of(startId);
            for (int depth = 1; depth <= maxDepth && !currentLevel.isEmpty(); depth++) {
                int currentDepth = depth;
                var children = jdbc.sql("""
                        SELECT DISTINCT b.*
                        FROM batches b
                        JOIN batch_consumptions bc ON b.id = bc.batch_id
                        WHERE bc.input_batch_id IN (:parentIds)
                        """)
                        .param("parentIds", currentLevel)
                        .query(this::mapBatchRow)
                        .list();

                var nextLevel = new ArrayList<String>();
                for (Batch child : children) {
                    if (visited.add(child.id().value())) {
                        results.add(new TracedBatch(child, currentDepth));
                        nextLevel.add(child.id().value());
                    }
                }
                currentLevel = nextLevel;
            }

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

    @Override
    public Result<RepositoryError, List<TracedBatch>> traceBackward(BatchId startBatchId, int maxDepth) {
        try {
            String startId = startBatchId.value();
            var results = new ArrayList<TracedBatch>();
            var visited = new HashSet<String>();
            visited.add(startId);

            var currentLevel = List.of(startId);
            for (int depth = 1; depth <= maxDepth && !currentLevel.isEmpty(); depth++) {
                int currentDepth = depth;
                var parents = jdbc.sql("""
                        SELECT DISTINCT b.*
                        FROM batches b
                        JOIN batch_consumptions bc ON b.id = bc.input_batch_id
                        WHERE bc.batch_id IN (:childIds)
                        """)
                        .param("childIds", currentLevel)
                        .query(this::mapBatchRow)
                        .list();

                var nextLevel = new ArrayList<String>();
                for (Batch parent : parents) {
                    if (visited.add(parent.id().value())) {
                        results.add(new TracedBatch(parent, currentDepth));
                        nextLevel.add(parent.id().value());
                    }
                }
                currentLevel = nextLevel;
            }

            return Result.success(results);
        } catch (Exception e) {
            logger.warn("Database error in traceBackward", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(Batch batch) {
        try {
            int rows = jdbc.sql("""
                    UPDATE batches
                    SET status = :status, actual_quantity_amount = :actualQuantityAmount,
                        actual_quantity_unit = :actualQuantityUnit, waste_amount = :wasteAmount,
                        waste_unit = :wasteUnit, remarks = :remarks, updated_at = :updatedAt,
                        completed_at = :completedAt, cancellation_reason = :cancellationReason,
                        cancelled_at = :cancelledAt, version = version + 1
                    WHERE id = :id AND version = :version
                    """)
                    .param("id", batch.id().value())
                    .param("status", batch.status().name())
                    .param("actualQuantityAmount", batch.actualQuantity() != null ? batch.actualQuantity().amount() : null)
                    .param("actualQuantityUnit", batch.actualQuantity() != null ? batch.actualQuantity().uom().name() : null)
                    .param("wasteAmount", batch.waste() != null ? batch.waste().amount() : null)
                    .param("wasteUnit", batch.waste() != null ? batch.waste().uom().name() : null)
                    .param("remarks", batch.remarks())
                    .param("updatedAt", batch.updatedAt())
                    .param("completedAt", batch.completedAt())
                    .param("cancellationReason", batch.cancellationReason())
                    .param("cancelledAt", batch.cancelledAt())
                    .param("version", batch.version())
                    .update();

            if (rows == 0) {
                boolean exists = jdbc.sql("SELECT COUNT(*) FROM batches WHERE id = :id")
                        .param("id", batch.id().value())
                        .query(Integer.class)
                        .single() > 0;

                if (exists) {
                    return Result.failure(new RepositoryError.ConcurrentModification(
                            "Batch was modified by another transaction"));
                }

                jdbc.sql("""
                        INSERT INTO batches
                        (id, batch_number, recipe_id, status, planned_quantity_amount, planned_quantity_unit,
                         production_date, best_before_date, actual_quantity_amount, actual_quantity_unit,
                         waste_amount, waste_unit, remarks, created_at, updated_at,
                         completed_at, cancellation_reason, cancelled_at, version)
                        VALUES (:id, :batchNumber, :recipeId, :status, :plannedQuantityAmount, :plannedQuantityUnit,
                                :productionDate, :bestBeforeDate, :actualQuantityAmount, :actualQuantityUnit,
                                :wasteAmount, :wasteUnit, :remarks, :createdAt, :updatedAt,
                                :completedAt, :cancellationReason, :cancelledAt, 0)
                        """)
                        .param("id", batch.id().value())
                        .param("batchNumber", batch.batchNumber().value())
                        .param("recipeId", batch.recipeId().value())
                        .param("status", batch.status().name())
                        .param("plannedQuantityAmount", batch.plannedQuantity().amount())
                        .param("plannedQuantityUnit", batch.plannedQuantity().uom().name())
                        .param("productionDate", batch.productionDate())
                        .param("bestBeforeDate", batch.bestBeforeDate())
                        .param("actualQuantityAmount", batch.actualQuantity() != null ? batch.actualQuantity().amount() : null)
                        .param("actualQuantityUnit", batch.actualQuantity() != null ? batch.actualQuantity().uom().name() : null)
                        .param("wasteAmount", batch.waste() != null ? batch.waste().amount() : null)
                        .param("wasteUnit", batch.waste() != null ? batch.waste().uom().name() : null)
                        .param("remarks", batch.remarks())
                        .param("createdAt", batch.createdAt())
                        .param("updatedAt", batch.updatedAt())
                        .param("completedAt", batch.completedAt())
                        .param("cancellationReason", batch.cancellationReason())
                        .param("cancelledAt", batch.cancelledAt())
                        .update();
            }

            saveConsumptions(batch);

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

    private void saveConsumptions(Batch batch) {
        var existingIds = jdbc.sql("SELECT id FROM batch_consumptions WHERE batch_id = :batchId")
                .param("batchId", batch.id().value())
                .query((rs, rowNum) -> rs.getString("id"))
                .list();

        var existingIdSet = new HashSet<>(existingIds);

        for (Consumption c : batch.consumptions()) {
            if (!existingIdSet.contains(c.id().value())) {
                jdbc.sql("""
                        INSERT INTO batch_consumptions
                        (id, batch_id, input_batch_id, article_id, quantity_used_amount, quantity_used_unit, consumed_at)
                        VALUES (:id, :batchId, :inputBatchId, :articleId, :quantityUsedAmount, :quantityUsedUnit, :consumedAt)
                        """)
                        .param("id", c.id().value())
                        .param("batchId", batch.id().value())
                        .param("inputBatchId", c.inputBatchId().value())
                        .param("articleId", c.articleId().value())
                        .param("quantityUsedAmount", c.quantityUsed().amount())
                        .param("quantityUsedUnit", c.quantityUsed().uom().name())
                        .param("consumedAt", c.consumedAt())
                        .update();
            }
        }
    }

    private List<Consumption> loadConsumptions(String batchId) {
        return jdbc.sql("SELECT * FROM batch_consumptions WHERE batch_id = :batchId ORDER BY consumed_at")
                .param("batchId", batchId)
                .query(this::mapConsumptionRow)
                .list();
    }

    private Batch loadWithConsumptions(Batch summary) {
        var consumptions = loadConsumptions(summary.id().value());
        return reconstituteWithConsumptions(summary, consumptions);
    }

    private Batch reconstituteWithConsumptions(Batch batch, List<Consumption> consumptions) {
        return Batch.reconstitute(
                batch.id(), batch.batchNumber(), batch.recipeId(), batch.status(),
                batch.plannedQuantity(), batch.actualQuantity(), batch.waste(),
                batch.remarks(), batch.productionDate(), batch.bestBeforeDate(),
                batch.createdAt(), batch.updatedAt(), batch.completedAt(),
                batch.cancellationReason(), batch.cancelledAt(), batch.version(),
                consumptions
        );
    }

    private Batch mapBatchRow(ResultSet rs, int rowNum) throws SQLException {
        BigDecimal actualQtyAmount = rs.getBigDecimal("actual_quantity_amount");
        Quantity actualQuantity = actualQtyAmount != null
                ? Quantity.reconstitute(actualQtyAmount, UnitOfMeasure.valueOf(rs.getString("actual_quantity_unit")))
                : null;

        BigDecimal wasteAmount = rs.getBigDecimal("waste_amount");
        Quantity waste = wasteAmount != null
                ? Quantity.reconstitute(wasteAmount, UnitOfMeasure.valueOf(rs.getString("waste_unit")))
                : null;

        return Batch.reconstitute(
                BatchId.of(rs.getString("id")),
                new BatchNumber(rs.getString("batch_number")),
                RecipeId.of(rs.getString("recipe_id")),
                BatchStatus.valueOf(rs.getString("status")),
                Quantity.reconstitute(
                        rs.getBigDecimal("planned_quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("planned_quantity_unit"))
                ),
                actualQuantity,
                waste,
                rs.getString("remarks"),
                rs.getObject("production_date", LocalDate.class),
                rs.getObject("best_before_date", LocalDate.class),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class),
                rs.getObject("completed_at", OffsetDateTime.class),
                rs.getString("cancellation_reason"),
                rs.getObject("cancelled_at", OffsetDateTime.class),
                rs.getLong("version"),
                List.of()
        );
    }

    private Consumption mapConsumptionRow(ResultSet rs, int rowNum) throws SQLException {
        return Consumption.reconstitute(
                ConsumptionId.of(rs.getString("id")),
                BatchId.of(rs.getString("input_batch_id")),
                ArticleId.of(rs.getString("article_id")),
                Quantity.reconstitute(
                        rs.getBigDecimal("quantity_used_amount"),
                        UnitOfMeasure.valueOf(rs.getString("quantity_used_unit"))
                ),
                rs.getObject("consumed_at", OffsetDateTime.class)
        );
    }
}
