package de.effigenix.infrastructure.inventory.persistence.repository;

import de.effigenix.domain.inventory.*;
import de.effigenix.domain.masterdata.article.ArticleId;
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.time.ZoneOffset;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcInventoryCountRepository implements InventoryCountRepository {

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

    private final JdbcClient jdbc;

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

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

    @Override
    public Result<RepositoryError, List<InventoryCount>> findAll() {
        try {
            var counts = jdbc.sql("SELECT * FROM inventory_counts ORDER BY created_at DESC")
                    .query(this::mapCountRow)
                    .list();
            return Result.success(loadChildrenForAll(counts));
        } catch (Exception e) {
            logger.warn("Database error in findAll", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<InventoryCount>> findByStorageLocationId(StorageLocationId storageLocationId) {
        try {
            var counts = jdbc.sql("SELECT * FROM inventory_counts WHERE storage_location_id = :storageLocationId ORDER BY created_at DESC")
                    .param("storageLocationId", storageLocationId.value())
                    .query(this::mapCountRow)
                    .list();
            return Result.success(loadChildrenForAll(counts));
        } catch (Exception e) {
            logger.warn("Database error in findByStorageLocationId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<InventoryCount>> findByStatus(InventoryCountStatus status) {
        try {
            var counts = jdbc.sql("SELECT * FROM inventory_counts WHERE status = :status ORDER BY created_at DESC")
                    .param("status", status.name())
                    .query(this::mapCountRow)
                    .list();
            return Result.success(loadChildrenForAll(counts));
        } catch (Exception e) {
            logger.warn("Database error in findByStatus", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsActiveByStorageLocationId(StorageLocationId storageLocationId) {
        try {
            int count = jdbc.sql("""
                    SELECT COUNT(*) FROM inventory_counts
                    WHERE storage_location_id = :storageLocationId
                      AND status IN ('OPEN', 'COUNTING')
                    """)
                    .param("storageLocationId", storageLocationId.value())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.warn("Database error in existsActiveByStorageLocationId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(InventoryCount inventoryCount) {
        try {
            int rows = jdbc.sql("""
                    UPDATE inventory_counts
                    SET storage_location_id = :storageLocationId, count_date = :countDate,
                        initiated_by = :initiatedBy, completed_by = :completedBy,
                        status = :status, cancellation_reason = :cancellationReason,
                        created_at = :createdAt
                    WHERE id = :id
                    """)
                    .param("id", inventoryCount.id().value())
                    .params(countParams(inventoryCount))
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO inventory_counts (id, storage_location_id, count_date, initiated_by, completed_by, status, cancellation_reason, created_at)
                        VALUES (:id, :storageLocationId, :countDate, :initiatedBy, :completedBy, :status, :cancellationReason, :createdAt)
                        """)
                        .param("id", inventoryCount.id().value())
                        .params(countParams(inventoryCount))
                        .update();
            }

            saveChildren(inventoryCount);

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

    // ==================== Private Helpers ====================

    private java.util.Map<String, Object> countParams(InventoryCount count) {
        var params = new java.util.LinkedHashMap<String, Object>();
        params.put("storageLocationId", count.storageLocationId().value());
        params.put("countDate", count.countDate());
        params.put("initiatedBy", count.initiatedBy());
        params.put("completedBy", count.completedBy());
        params.put("status", count.status().name());
        params.put("cancellationReason", count.cancellationReason());
        params.put("createdAt", count.createdAt().atOffset(ZoneOffset.UTC));
        return params;
    }

    private void saveChildren(InventoryCount count) {
        String countId = count.id().value();

        // Remove orphaned items no longer in the aggregate
        List<String> currentIds = count.countItems().stream()
                .map(item -> item.id().value())
                .toList();
        if (currentIds.isEmpty()) {
            jdbc.sql("DELETE FROM count_items WHERE inventory_count_id = :countId")
                    .param("countId", countId)
                    .update();
        } else {
            jdbc.sql("DELETE FROM count_items WHERE inventory_count_id = :countId AND id NOT IN (:ids)")
                    .param("countId", countId)
                    .param("ids", currentIds)
                    .update();
        }

        // Upsert each item (UPDATE → INSERT)
        for (CountItem item : count.countItems()) {
            int rows = jdbc.sql("""
                    UPDATE count_items
                    SET article_id = :articleId,
                        expected_quantity_amount = :expectedQuantityAmount,
                        expected_quantity_unit = :expectedQuantityUnit,
                        actual_quantity_amount = :actualQuantityAmount,
                        actual_quantity_unit = :actualQuantityUnit
                    WHERE id = :id
                    """)
                    .param("id", item.id().value())
                    .param("articleId", item.articleId().value())
                    .param("expectedQuantityAmount", item.expectedQuantity().amount())
                    .param("expectedQuantityUnit", item.expectedQuantity().uom().name())
                    .param("actualQuantityAmount", item.actualQuantity() != null ? item.actualQuantity().amount() : null)
                    .param("actualQuantityUnit", item.actualQuantity() != null ? item.actualQuantity().uom().name() : null)
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO count_items
                        (id, inventory_count_id, article_id,
                         expected_quantity_amount, expected_quantity_unit,
                         actual_quantity_amount, actual_quantity_unit)
                        VALUES (:id, :countId, :articleId,
                                :expectedQuantityAmount, :expectedQuantityUnit,
                                :actualQuantityAmount, :actualQuantityUnit)
                        """)
                        .param("id", item.id().value())
                        .param("countId", countId)
                        .param("articleId", item.articleId().value())
                        .param("expectedQuantityAmount", item.expectedQuantity().amount())
                        .param("expectedQuantityUnit", item.expectedQuantity().uom().name())
                        .param("actualQuantityAmount", item.actualQuantity() != null ? item.actualQuantity().amount() : null)
                        .param("actualQuantityUnit", item.actualQuantity() != null ? item.actualQuantity().uom().name() : null)
                        .update();
            }
        }
    }

    private InventoryCount loadChildren(InventoryCount count, String countId) {
        var items = jdbc.sql("SELECT * FROM count_items WHERE inventory_count_id = :countId")
                .param("countId", countId)
                .query(this::mapCountItemRow)
                .list();

        return InventoryCount.reconstitute(
                count.id(), count.storageLocationId(), count.countDate(),
                count.initiatedBy(), count.completedBy(), count.status(),
                count.cancellationReason(), count.createdAt(), items
        );
    }

    private List<InventoryCount> loadChildrenForAll(List<InventoryCount> counts) {
        return counts.stream()
                .map(c -> loadChildren(c, c.id().value()))
                .toList();
    }

    // ==================== Row Mappers ====================

    private InventoryCount mapCountRow(ResultSet rs, int rowNum) throws SQLException {
        return InventoryCount.reconstitute(
                InventoryCountId.of(rs.getString("id")),
                StorageLocationId.of(rs.getString("storage_location_id")),
                rs.getObject("count_date", LocalDate.class),
                rs.getString("initiated_by"),
                rs.getString("completed_by"),
                InventoryCountStatus.valueOf(rs.getString("status")),
                rs.getString("cancellation_reason"),
                rs.getObject("created_at", OffsetDateTime.class).toInstant(),
                List.of()
        );
    }

    private CountItem mapCountItemRow(ResultSet rs, int rowNum) throws SQLException {
        BigDecimal actualAmount = rs.getBigDecimal("actual_quantity_amount");
        String actualUnit = rs.getString("actual_quantity_unit");

        Quantity actualQuantity = null;
        if (actualAmount != null && actualUnit != null) {
            actualQuantity = Quantity.reconstitute(actualAmount, UnitOfMeasure.valueOf(actualUnit));
        }

        return CountItem.reconstitute(
                CountItemId.of(rs.getString("id")),
                ArticleId.of(rs.getString("article_id")),
                Quantity.reconstitute(
                        rs.getBigDecimal("expected_quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("expected_quantity_unit"))
                ),
                actualQuantity
        );
    }
}
