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.sql.ResultSet;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcStockRepository implements StockRepository {

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

    private final JdbcClient jdbc;

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

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

    @Override
    public Result<RepositoryError, Optional<Stock>> findByArticleIdAndStorageLocationId(ArticleId articleId, StorageLocationId storageLocationId) {
        try {
            var stockOpt = jdbc.sql("SELECT * FROM stocks WHERE article_id = :articleId AND storage_location_id = :storageLocationId")
                    .param("articleId", articleId.value())
                    .param("storageLocationId", storageLocationId.value())
                    .query(this::mapStockRow)
                    .optional();
            if (stockOpt.isEmpty()) {
                return Result.success(Optional.empty());
            }
            var stock = stockOpt.get();
            return Result.success(Optional.of(loadChildren(stock, stock.id().value())));
        } catch (Exception e) {
            logger.trace("Database error in findByArticleIdAndStorageLocationId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByArticleIdAndStorageLocationId(ArticleId articleId, StorageLocationId storageLocationId) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM stocks WHERE article_id = :articleId AND storage_location_id = :storageLocationId")
                    .param("articleId", articleId.value())
                    .param("storageLocationId", storageLocationId.value())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByArticleIdAndStorageLocationId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Stock>> findAll() {
        try {
            var stocks = jdbc.sql("SELECT * FROM stocks")
                    .query(this::mapStockRow)
                    .list();
            return Result.success(loadChildrenForAll(stocks));
        } catch (Exception e) {
            logger.trace("Database error in findAll", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Stock>> findAllByStorageLocationId(StorageLocationId storageLocationId) {
        try {
            var stocks = jdbc.sql("SELECT * FROM stocks WHERE storage_location_id = :storageLocationId")
                    .param("storageLocationId", storageLocationId.value())
                    .query(this::mapStockRow)
                    .list();
            return Result.success(loadChildrenForAll(stocks));
        } catch (Exception e) {
            logger.trace("Database error in findAllByStorageLocationId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Stock>> findAllByArticleId(ArticleId articleId) {
        try {
            var stocks = jdbc.sql("SELECT * FROM stocks WHERE article_id = :articleId")
                    .param("articleId", articleId.value())
                    .query(this::mapStockRow)
                    .list();
            return Result.success(loadChildrenForAll(stocks));
        } catch (Exception e) {
            logger.trace("Database error in findAllByArticleId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Stock>> findAllWithExpiryRelevantBatches(LocalDate referenceDate) {
        try {
            var stocks = jdbc.sql("""
                    SELECT DISTINCT s.* FROM stocks s
                    JOIN stock_batches b ON b.stock_id = s.id
                    WHERE (b.status IN ('AVAILABLE', 'EXPIRING_SOON') AND b.expiry_date < :today)
                       OR (s.minimum_shelf_life_days IS NOT NULL AND b.status = 'AVAILABLE'
                           AND b.expiry_date >= :today
                           AND b.expiry_date < :today + s.minimum_shelf_life_days * INTERVAL '1 day')
                    """)
                    .param("today", referenceDate)
                    .query(this::mapStockRow)
                    .list();
            return Result.success(loadChildrenForAll(stocks));
        } catch (Exception e) {
            logger.trace("Database error in findAllWithExpiryRelevantBatches", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Stock>> findAllBelowMinimumLevel() {
        try {
            var stocks = jdbc.sql("""
                    SELECT DISTINCT s.* FROM stocks s
                    LEFT JOIN stock_batches b ON b.stock_id = s.id AND b.status IN ('AVAILABLE', 'EXPIRING_SOON')
                    WHERE s.minimum_level_amount IS NOT NULL
                    GROUP BY s.id
                    HAVING COALESCE(SUM(b.quantity_amount), 0) < s.minimum_level_amount
                    """)
                    .query(this::mapStockRow)
                    .list();
            return Result.success(loadChildrenForAll(stocks));
        } catch (Exception e) {
            logger.trace("Database error in findAllBelowMinimumLevel", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(Stock stock) {
        try {
            int rows = jdbc.sql("""
                    UPDATE stocks
                    SET article_id = :articleId, storage_location_id = :storageLocationId,
                        minimum_level_amount = :minimumLevelAmount, minimum_level_unit = :minimumLevelUnit,
                        minimum_shelf_life_days = :minimumShelfLifeDays
                    WHERE id = :id
                    """)
                    .param("id", stock.id().value())
                    .params(stockParams(stock))
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO stocks (id, article_id, storage_location_id,
                                           minimum_level_amount, minimum_level_unit, minimum_shelf_life_days)
                        VALUES (:id, :articleId, :storageLocationId,
                                :minimumLevelAmount, :minimumLevelUnit, :minimumShelfLifeDays)
                        """)
                        .param("id", stock.id().value())
                        .params(stockParams(stock))
                        .update();
            }

            saveChildren(stock);

            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in save", e);
            if (e.getMessage() != null && e.getMessage().contains("duplicate key")
                    || e.getMessage() != null && e.getMessage().contains("unique constraint")) {
                return Result.failure(new RepositoryError.DuplicateEntry(
                        "Stock already exists for article " + stock.articleId().value()
                                + " at location " + stock.storageLocationId().value()));
            }
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    private java.util.Map<String, Object> stockParams(Stock stock) {
        var params = new java.util.LinkedHashMap<String, Object>();
        params.put("articleId", stock.articleId().value());
        params.put("storageLocationId", stock.storageLocationId().value());
        params.put("minimumLevelAmount", stock.minimumLevel() != null ? stock.minimumLevel().quantity().amount() : null);
        params.put("minimumLevelUnit", stock.minimumLevel() != null ? stock.minimumLevel().quantity().uom().name() : null);
        params.put("minimumShelfLifeDays", stock.minimumShelfLife() != null ? stock.minimumShelfLife().days() : null);
        return params;
    }

    private void saveChildren(Stock stock) {
        String stockId = stock.id().value();

        // Delete reservations first (cascades to allocations via FK ON DELETE CASCADE)
        jdbc.sql("DELETE FROM reservations WHERE stock_id = :stockId")
                .param("stockId", stockId)
                .update();

        // Upsert batches: stock_movements has FK to stock_batches (RESTRICT),
        // so we cannot delete-all + re-insert. Instead: remove stale, upsert current.
        var currentBatchIds = stock.batches().stream()
                .map(b -> b.id().value())
                .toList();

        if (currentBatchIds.isEmpty()) {
            jdbc.sql("DELETE FROM stock_batches WHERE stock_id = :stockId")
                    .param("stockId", stockId)
                    .update();
        } else {
            jdbc.sql("DELETE FROM stock_batches WHERE stock_id = :stockId AND id NOT IN (:batchIds)")
                    .param("stockId", stockId)
                    .param("batchIds", currentBatchIds)
                    .update();
        }

        for (StockBatch batch : stock.batches()) {
            int updated = jdbc.sql("""
                    UPDATE stock_batches
                    SET batch_id = :batchId, batch_type = :batchType,
                        quantity_amount = :quantityAmount, quantity_unit = :quantityUnit,
                        expiry_date = :expiryDate, status = :status, received_at = :receivedAt
                    WHERE id = :id AND stock_id = :stockId
                    """)
                    .param("id", batch.id().value())
                    .param("stockId", stockId)
                    .param("batchId", batch.batchReference().batchId())
                    .param("batchType", batch.batchReference().batchType().name())
                    .param("quantityAmount", batch.quantity().amount())
                    .param("quantityUnit", batch.quantity().uom().name())
                    .param("expiryDate", batch.expiryDate())
                    .param("status", batch.status().name())
                    .param("receivedAt", batch.receivedAt().atOffset(ZoneOffset.UTC))
                    .update();

            if (updated == 0) {
                jdbc.sql("""
                        INSERT INTO stock_batches
                        (id, stock_id, batch_id, batch_type, quantity_amount, quantity_unit, expiry_date, status, received_at)
                        VALUES (:id, :stockId, :batchId, :batchType, :quantityAmount, :quantityUnit, :expiryDate, :status, :receivedAt)
                        """)
                        .param("id", batch.id().value())
                        .param("stockId", stockId)
                        .param("batchId", batch.batchReference().batchId())
                        .param("batchType", batch.batchReference().batchType().name())
                        .param("quantityAmount", batch.quantity().amount())
                        .param("quantityUnit", batch.quantity().uom().name())
                        .param("expiryDate", batch.expiryDate())
                        .param("status", batch.status().name())
                        .param("receivedAt", batch.receivedAt().atOffset(ZoneOffset.UTC))
                        .update();
            }
        }

        for (Reservation reservation : stock.reservations()) {
            jdbc.sql("""
                    INSERT INTO reservations
                    (id, stock_id, reference_type, reference_id, quantity_amount, quantity_unit, priority, reserved_at)
                    VALUES (:id, :stockId, :referenceType, :referenceId, :quantityAmount, :quantityUnit, :priority, :reservedAt)
                    """)
                    .param("id", reservation.id().value())
                    .param("stockId", stockId)
                    .param("referenceType", reservation.referenceType().name())
                    .param("referenceId", reservation.referenceId())
                    .param("quantityAmount", reservation.quantity().amount())
                    .param("quantityUnit", reservation.quantity().uom().name())
                    .param("priority", reservation.priority().name())
                    .param("reservedAt", reservation.reservedAt().atOffset(ZoneOffset.UTC))
                    .update();

            for (StockBatchAllocation allocation : reservation.allocations()) {
                jdbc.sql("""
                        INSERT INTO stock_batch_allocations
                        (id, reservation_id, stock_batch_id, allocated_quantity_amount, allocated_quantity_unit)
                        VALUES (:id, :reservationId, :stockBatchId, :allocatedQuantityAmount, :allocatedQuantityUnit)
                        """)
                        .param("id", allocation.id().value())
                        .param("reservationId", reservation.id().value())
                        .param("stockBatchId", allocation.stockBatchId().value())
                        .param("allocatedQuantityAmount", allocation.allocatedQuantity().amount())
                        .param("allocatedQuantityUnit", allocation.allocatedQuantity().uom().name())
                        .update();
            }
        }
    }

    private Stock loadChildren(Stock stock, String stockId) {
        var batches = jdbc.sql("SELECT * FROM stock_batches WHERE stock_id = :stockId")
                .param("stockId", stockId)
                .query(this::mapBatchRow)
                .list();

        var reservations = loadReservations(stockId);

        return Stock.reconstitute(
                stock.id(), stock.articleId(), stock.storageLocationId(),
                stock.minimumLevel(), stock.minimumShelfLife(),
                batches, reservations
        );
    }

    private List<Stock> loadChildrenForAll(List<Stock> stocks) {
        return stocks.stream()
                .map(s -> loadChildren(s, s.id().value()))
                .toList();
    }

    private List<Reservation> loadReservations(String stockId) {
        var reservationRows = jdbc.sql("SELECT * FROM reservations WHERE stock_id = :stockId")
                .param("stockId", stockId)
                .query(this::mapReservationRow)
                .list();

        if (reservationRows.isEmpty()) {
            return List.of();
        }

        List<String> reservationIds = reservationRows.stream()
                .map(r -> r.id().value())
                .toList();

        var allAllocations = jdbc.sql("SELECT * FROM stock_batch_allocations WHERE reservation_id IN (:reservationIds)")
                .param("reservationIds", reservationIds)
                .query(this::mapAllocationRow)
                .list();

        // Group allocations by reservation_id
        var allocationsByReservation = new java.util.HashMap<String, List<StockBatchAllocation>>();
        for (var entry : allAllocations) {
            allocationsByReservation.computeIfAbsent(entry.reservationId(), k -> new ArrayList<>())
                    .add(entry.allocation());
        }

        return reservationRows.stream()
                .map(r -> new Reservation(
                        r.id(), r.referenceType(), r.referenceId(),
                        r.quantity(), r.priority(), r.reservedAt(),
                        allocationsByReservation.getOrDefault(r.id().value(), List.of())
                ))
                .toList();
    }

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

    private Stock mapStockRow(ResultSet rs, int rowNum) throws SQLException {
        MinimumLevel minimumLevel = null;
        var levelAmount = rs.getBigDecimal("minimum_level_amount");
        String levelUnit = rs.getString("minimum_level_unit");
        if (levelAmount != null && levelUnit != null) {
            minimumLevel = new MinimumLevel(Quantity.reconstitute(levelAmount, UnitOfMeasure.valueOf(levelUnit)));
        }

        MinimumShelfLife minimumShelfLife = null;
        int shelfLifeDays = rs.getInt("minimum_shelf_life_days");
        if (!rs.wasNull()) {
            minimumShelfLife = new MinimumShelfLife(shelfLifeDays);
        }

        return Stock.reconstitute(
                StockId.of(rs.getString("id")),
                ArticleId.of(rs.getString("article_id")),
                StorageLocationId.of(rs.getString("storage_location_id")),
                minimumLevel, minimumShelfLife,
                List.of(), List.of()
        );
    }

    private StockBatch mapBatchRow(ResultSet rs, int rowNum) throws SQLException {
        return StockBatch.reconstitute(
                StockBatchId.of(rs.getString("id")),
                new BatchReference(rs.getString("batch_id"), BatchType.valueOf(rs.getString("batch_type"))),
                Quantity.reconstitute(
                        rs.getBigDecimal("quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("quantity_unit"))
                ),
                rs.getObject("expiry_date", LocalDate.class),
                StockBatchStatus.valueOf(rs.getString("status")),
                rs.getObject("received_at", OffsetDateTime.class).toInstant()
        );
    }

    private ReservationRow mapReservationRow(ResultSet rs, int rowNum) throws SQLException {
        return new ReservationRow(
                ReservationId.of(rs.getString("id")),
                ReferenceType.valueOf(rs.getString("reference_type")),
                rs.getString("reference_id"),
                Quantity.reconstitute(
                        rs.getBigDecimal("quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("quantity_unit"))
                ),
                ReservationPriority.valueOf(rs.getString("priority")),
                rs.getObject("reserved_at", OffsetDateTime.class).toInstant()
        );
    }

    private AllocationRow mapAllocationRow(ResultSet rs, int rowNum) throws SQLException {
        return new AllocationRow(
                rs.getString("reservation_id"),
                new StockBatchAllocation(
                        AllocationId.of(rs.getString("id")),
                        StockBatchId.of(rs.getString("stock_batch_id")),
                        Quantity.reconstitute(
                                rs.getBigDecimal("allocated_quantity_amount"),
                                UnitOfMeasure.valueOf(rs.getString("allocated_quantity_unit"))
                        )
                )
        );
    }

    private record ReservationRow(
            ReservationId id, ReferenceType referenceType, String referenceId,
            Quantity quantity, ReservationPriority priority, Instant reservedAt
    ) {}

    private record AllocationRow(String reservationId, StockBatchAllocation allocation) {}
}
