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

@Repository
@Profile("!no-db")
public class JdbcStockMovementRepository implements StockMovementRepository {

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

    private final JdbcClient jdbc;

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

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

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

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByStockId(StockId stockId) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE stock_id = :stockId ORDER BY performed_at DESC")
                    .param("stockId", stockId.value())
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByStockId", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

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

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByMovementType(MovementType movementType) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE movement_type = :movementType ORDER BY performed_at DESC")
                    .param("movementType", movementType.name())
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByMovementType", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByBatchReference(String batchReference) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE batch_id = :batchId ORDER BY performed_at DESC")
                    .param("batchId", batchReference)
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByBatchReference", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByPerformedAtBetween(Instant from, Instant to) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE performed_at >= :from AND performed_at <= :to ORDER BY performed_at DESC")
                    .param("from", from.atOffset(ZoneOffset.UTC))
                    .param("to", to.atOffset(ZoneOffset.UTC))
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByPerformedAtBetween", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByPerformedAtAfter(Instant from) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE performed_at >= :from ORDER BY performed_at DESC")
                    .param("from", from.atOffset(ZoneOffset.UTC))
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByPerformedAtAfter", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<StockMovement>> findAllByPerformedAtBefore(Instant to) {
        try {
            var result = jdbc.sql("SELECT * FROM stock_movements WHERE performed_at <= :to ORDER BY performed_at DESC")
                    .param("to", to.atOffset(ZoneOffset.UTC))
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findAllByPerformedAtBefore", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(StockMovement movement) {
        try {
            jdbc.sql("""
                    INSERT INTO stock_movements
                    (id, stock_id, article_id, stock_batch_id, batch_id, batch_type,
                     movement_type, direction, quantity_amount, quantity_unit,
                     reason, reference_document_id, performed_by, performed_at)
                    VALUES (:id, :stockId, :articleId, :stockBatchId, :batchId, :batchType,
                            :movementType, :direction, :quantityAmount, :quantityUnit,
                            :reason, :referenceDocumentId, :performedBy, :performedAt)
                    """)
                    .param("id", movement.id().value())
                    .param("stockId", movement.stockId().value())
                    .param("articleId", movement.articleId().value())
                    .param("stockBatchId", movement.stockBatchId().value())
                    .param("batchId", movement.batchReference().batchId())
                    .param("batchType", movement.batchReference().batchType().name())
                    .param("movementType", movement.movementType().name())
                    .param("direction", movement.direction().name())
                    .param("quantityAmount", movement.quantity().amount())
                    .param("quantityUnit", movement.quantity().uom().name())
                    .param("reason", movement.reason())
                    .param("referenceDocumentId", movement.referenceDocumentId())
                    .param("performedBy", movement.performedBy())
                    .param("performedAt", movement.performedAt().atOffset(ZoneOffset.UTC))
                    .update();

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

    private StockMovement mapRow(ResultSet rs, int rowNum) throws SQLException {
        return StockMovement.reconstitute(
                StockMovementId.of(rs.getString("id")),
                StockId.of(rs.getString("stock_id")),
                ArticleId.of(rs.getString("article_id")),
                StockBatchId.of(rs.getString("stock_batch_id")),
                new BatchReference(rs.getString("batch_id"), BatchType.valueOf(rs.getString("batch_type"))),
                MovementType.valueOf(rs.getString("movement_type")),
                MovementDirection.valueOf(rs.getString("direction")),
                Quantity.reconstitute(
                        rs.getBigDecimal("quantity_amount"),
                        UnitOfMeasure.valueOf(rs.getString("quantity_unit"))
                ),
                rs.getString("reason"),
                rs.getString("reference_document_id"),
                rs.getString("performed_by"),
                rs.getObject("performed_at", OffsetDateTime.class).toInstant()
        );
    }
}
