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.LocalDate;
import java.time.OffsetDateTime;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcProductionOrderRepository implements ProductionOrderRepository {

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

    private final JdbcClient jdbc;

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

    @Override
    public Result<RepositoryError, Optional<ProductionOrder>> findById(ProductionOrderId id) {
        try {
            var order = jdbc.sql("SELECT * FROM production_orders WHERE id = :id")
                    .param("id", id.value())
                    .query(this::mapRow)
                    .optional();
            return Result.success(order);
        } catch (Exception e) {
            logger.trace("Database error in findById", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, List<ProductionOrder>> findByDateRange(LocalDate from, LocalDate to) {
        try {
            var orders = jdbc.sql("SELECT * FROM production_orders WHERE planned_date BETWEEN :from AND :to ORDER BY planned_date, created_at DESC")
                    .param("from", from)
                    .param("to", to)
                    .query(this::mapRow)
                    .list();
            return Result.success(orders);
        } catch (Exception e) {
            logger.trace("Database error in findByDateRange", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, List<ProductionOrder>> findByDateRangeAndStatus(LocalDate from, LocalDate to, ProductionOrderStatus status) {
        try {
            var orders = jdbc.sql("SELECT * FROM production_orders WHERE planned_date BETWEEN :from AND :to AND status = :status ORDER BY planned_date, created_at DESC")
                    .param("from", from)
                    .param("to", to)
                    .param("status", status.name())
                    .query(this::mapRow)
                    .list();
            return Result.success(orders);
        } catch (Exception e) {
            logger.trace("Database error in findByDateRangeAndStatus", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(ProductionOrder order) {
        try {
            int rows = jdbc.sql("""
                    UPDATE production_orders
                    SET status = :status, batch_id = :batchId, priority = :priority,
                        notes = :notes, cancelled_reason = :cancelledReason,
                        planned_date = :plannedDate,
                        updated_at = :updatedAt, version = version + 1
                    WHERE id = :id AND version = :version
                    """)
                    .param("id", order.id().value())
                    .param("status", order.status().name())
                    .param("batchId", order.batchId() != null ? order.batchId().value() : null)
                    .param("priority", order.priority().name())
                    .param("notes", order.notes())
                    .param("cancelledReason", order.cancelledReason())
                    .param("plannedDate", order.plannedDate())
                    .param("updatedAt", order.updatedAt())
                    .param("version", order.version())
                    .update();

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

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

                jdbc.sql("""
                        INSERT INTO production_orders
                        (id, recipe_id, status, planned_quantity_amount, planned_quantity_unit,
                         planned_date, priority, batch_id, notes, cancelled_reason,
                         created_at, updated_at, version)
                        VALUES (:id, :recipeId, :status, :plannedQuantityAmount, :plannedQuantityUnit,
                                :plannedDate, :priority, :batchId, :notes, :cancelledReason,
                                :createdAt, :updatedAt, 0)
                        """)
                        .param("id", order.id().value())
                        .param("recipeId", order.recipeId().value())
                        .param("status", order.status().name())
                        .param("plannedQuantityAmount", order.plannedQuantity().amount())
                        .param("plannedQuantityUnit", order.plannedQuantity().uom().name())
                        .param("plannedDate", order.plannedDate())
                        .param("priority", order.priority().name())
                        .param("batchId", order.batchId() != null ? order.batchId().value() : null)
                        .param("notes", order.notes())
                        .param("cancelledReason", order.cancelledReason())
                        .param("createdAt", order.createdAt())
                        .param("updatedAt", order.updatedAt())
                        .update();
            }

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

    private ProductionOrder mapRow(ResultSet rs, int rowNum) throws SQLException {
        return ProductionOrder.reconstitute(
                ProductionOrderId.of(rs.getString("id")),
                RecipeId.of(rs.getString("recipe_id")),
                ProductionOrderStatus.valueOf(rs.getString("status")),
                rs.getString("batch_id") != null ? BatchId.of(rs.getString("batch_id")) : null,
                Quantity.reconstitute(
                        rs.getBigDecimal("planned_quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("planned_quantity_unit"))
                ),
                rs.getObject("planned_date", java.time.LocalDate.class),
                Priority.valueOf(rs.getString("priority")),
                rs.getString("notes"),
                rs.getString("cancelled_reason"),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class),
                rs.getLong("version")
        );
    }
}
