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

import de.effigenix.domain.inventory.*;
import de.effigenix.shared.common.RepositoryError;
import de.effigenix.shared.common.Result;
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.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcStorageLocationRepository implements StorageLocationRepository {

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

    private final JdbcClient jdbc;

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

    @Override
    public Result<RepositoryError, Optional<StorageLocation>> findById(StorageLocationId id) {
        try {
            var result = jdbc.sql("SELECT * FROM storage_locations 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<StorageLocation>> findAll() {
        try {
            var result = jdbc.sql("SELECT * FROM storage_locations ORDER BY name")
                    .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<StorageLocation>> findByStorageType(StorageType storageType) {
        try {
            var result = jdbc.sql("SELECT * FROM storage_locations WHERE storage_type = :storageType ORDER BY name")
                    .param("storageType", storageType.name())
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findByStorageType", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<StorageLocation>> findActive() {
        try {
            var result = jdbc.sql("SELECT * FROM storage_locations WHERE active = true ORDER BY name")
                    .query(this::mapRow)
                    .list();
            return Result.success(result);
        } catch (Exception e) {
            logger.trace("Database error in findActive", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByName(StorageLocationName name) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM storage_locations WHERE name = :name")
                    .param("name", name.value())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByName", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByNameAndIdNot(StorageLocationName name, StorageLocationId id) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM storage_locations WHERE name = :name AND id != :id")
                    .param("name", name.value())
                    .param("id", id.value())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByNameAndIdNot", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(StorageLocation location) {
        try {
            int rows = jdbc.sql("""
                    UPDATE storage_locations
                    SET name = :name, storage_type = :storageType,
                        min_temperature = :minTemperature, max_temperature = :maxTemperature,
                        active = :active
                    WHERE id = :id
                    """)
                    .param("id", location.id().value())
                    .params(locationParams(location))
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO storage_locations (id, name, storage_type, min_temperature, max_temperature, active)
                        VALUES (:id, :name, :storageType, :minTemperature, :maxTemperature, :active)
                        """)
                        .param("id", location.id().value())
                        .params(locationParams(location))
                        .update();
            }

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

    private java.util.Map<String, Object> locationParams(StorageLocation location) {
        var params = new java.util.LinkedHashMap<String, Object>();
        params.put("name", location.name().value());
        params.put("storageType", location.storageType().name());
        params.put("minTemperature", location.temperatureRange() != null ? location.temperatureRange().minTemperature() : null);
        params.put("maxTemperature", location.temperatureRange() != null ? location.temperatureRange().maxTemperature() : null);
        params.put("active", location.active());
        return params;
    }

    private StorageLocation mapRow(ResultSet rs, int rowNum) throws SQLException {
        TemperatureRange temperatureRange = null;
        BigDecimal minTemp = rs.getBigDecimal("min_temperature");
        BigDecimal maxTemp = rs.getBigDecimal("max_temperature");
        if (minTemp != null && maxTemp != null) {
            temperatureRange = new TemperatureRange(minTemp, maxTemp);
        }

        return StorageLocation.reconstitute(
                StorageLocationId.of(rs.getString("id")),
                new StorageLocationName(rs.getString("name")),
                StorageType.valueOf(rs.getString("storage_type")),
                temperatureRange,
                rs.getBoolean("active")
        );
    }
}
