package de.effigenix.infrastructure.masterdata.persistence;

import de.effigenix.domain.masterdata.productcategory.CategoryName;
import de.effigenix.domain.masterdata.productcategory.ProductCategory;
import de.effigenix.domain.masterdata.productcategory.ProductCategoryId;
import de.effigenix.domain.masterdata.productcategory.ProductCategoryRepository;
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.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcProductCategoryRepository implements ProductCategoryRepository {

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

    private final JdbcClient jdbc;

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

    @Override
    public Result<RepositoryError, Optional<ProductCategory>> findById(ProductCategoryId id) {
        try {
            var result = jdbc.sql("SELECT * FROM product_categories 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<ProductCategory>> findAll() {
        try {
            var result = jdbc.sql("SELECT * FROM product_categories 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, Void> save(ProductCategory category) {
        try {
            int rows = jdbc.sql("""
                    UPDATE product_categories SET name = :name, description = :description
                    WHERE id = :id
                    """)
                    .param("id", category.id().value())
                    .param("name", category.name().value())
                    .param("description", category.description())
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO product_categories (id, name, description)
                        VALUES (:id, :name, :description)
                        """)
                        .param("id", category.id().value())
                        .param("name", category.name().value())
                        .param("description", category.description())
                        .update();
            }

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

    @Override
    public Result<RepositoryError, Void> delete(ProductCategory category) {
        try {
            jdbc.sql("DELETE FROM product_categories WHERE id = :id")
                    .param("id", category.id().value())
                    .update();
            return Result.success(null);
        } catch (Exception e) {
            logger.trace("Database error in delete", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Boolean> existsByName(CategoryName name) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM product_categories 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()));
        }
    }

    private ProductCategory mapRow(ResultSet rs, int rowNum) throws SQLException {
        return ProductCategory.reconstitute(
                ProductCategoryId.of(rs.getString("id")),
                new CategoryName(rs.getString("name")),
                rs.getString("description")
        );
    }
}
