package de.effigenix.infrastructure.masterdata.persistence;

import de.effigenix.domain.masterdata.*;
import de.effigenix.domain.masterdata.article.*;
import de.effigenix.domain.masterdata.productcategory.ProductCategoryId;
import de.effigenix.domain.masterdata.supplier.SupplierId;
import de.effigenix.shared.common.Money;
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.time.OffsetDateTime;
import java.util.*;

@Repository
@Profile("!no-db")
public class JdbcArticleRepository implements ArticleRepository {

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

    private final JdbcClient jdbc;

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

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

    @Override
    public Result<RepositoryError, List<Article>> findAll() {
        try {
            var articles = jdbc.sql("SELECT * FROM articles ORDER BY name")
                    .query(this::mapArticleRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(articles);
        } catch (Exception e) {
            logger.trace("Database error in findAll", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Article>> findByCategory(ProductCategoryId categoryId) {
        try {
            var articles = jdbc.sql("SELECT * FROM articles WHERE category_id = :categoryId ORDER BY name")
                    .param("categoryId", categoryId.value())
                    .query(this::mapArticleRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(articles);
        } catch (Exception e) {
            logger.trace("Database error in findByCategory", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, List<Article>> findByStatus(ArticleStatus status) {
        try {
            var articles = jdbc.sql("SELECT * FROM articles WHERE status = :status ORDER BY name")
                    .param("status", status.name())
                    .query(this::mapArticleRow)
                    .list()
                    .stream()
                    .map(this::loadChildren)
                    .toList();
            return Result.success(articles);
        } catch (Exception e) {
            logger.trace("Database error in findByStatus", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    @Override
    public Result<RepositoryError, Void> save(Article article) {
        try {
            int rows = jdbc.sql("""
                    UPDATE articles
                    SET name = :name, article_number = :articleNumber, category_id = :categoryId,
                        status = :status, updated_at = :updatedAt
                    WHERE id = :id
                    """)
                    .param("id", article.id().value())
                    .param("name", article.name().value())
                    .param("articleNumber", article.articleNumber().value())
                    .param("categoryId", article.categoryId().value())
                    .param("status", article.status().name())
                    .param("updatedAt", article.updatedAt())
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO articles (id, name, article_number, category_id, status, created_at, updated_at)
                        VALUES (:id, :name, :articleNumber, :categoryId, :status, :createdAt, :updatedAt)
                        """)
                        .param("id", article.id().value())
                        .param("name", article.name().value())
                        .param("articleNumber", article.articleNumber().value())
                        .param("categoryId", article.categoryId().value())
                        .param("status", article.status().name())
                        .param("createdAt", article.createdAt())
                        .param("updatedAt", article.updatedAt())
                        .update();
            }

            saveSalesUnits(article);
            saveSupplierReferences(article);

            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(Article article) {
        try {
            jdbc.sql("DELETE FROM articles WHERE id = :id")
                    .param("id", article.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> existsByArticleNumber(ArticleNumber articleNumber) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM articles WHERE article_number = :articleNumber")
                    .param("articleNumber", articleNumber.value())
                    .query(Integer.class)
                    .single();
            return Result.success(count > 0);
        } catch (Exception e) {
            logger.trace("Database error in existsByArticleNumber", e);
            return Result.failure(new RepositoryError.DatabaseError(e.getMessage()));
        }
    }

    private void saveSalesUnits(Article article) {
        jdbc.sql("DELETE FROM sales_units WHERE article_id = :articleId")
                .param("articleId", article.id().value())
                .update();

        for (SalesUnit su : article.salesUnits()) {
            jdbc.sql("""
                    INSERT INTO sales_units (id, article_id, unit, price_model, price_amount, price_currency)
                    VALUES (:id, :articleId, :unit, :priceModel, :priceAmount, :priceCurrency)
                    """)
                    .param("id", su.id().value())
                    .param("articleId", article.id().value())
                    .param("unit", su.unit().name())
                    .param("priceModel", su.priceModel().name())
                    .param("priceAmount", su.price().amount())
                    .param("priceCurrency", su.price().currency().getCurrencyCode())
                    .update();
        }
    }

    private void saveSupplierReferences(Article article) {
        jdbc.sql("DELETE FROM article_suppliers WHERE article_id = :articleId")
                .param("articleId", article.id().value())
                .update();

        for (SupplierId supplierId : article.supplierReferences()) {
            jdbc.sql("INSERT INTO article_suppliers (article_id, supplier_id) VALUES (:articleId, :supplierId)")
                    .param("articleId", article.id().value())
                    .param("supplierId", supplierId.value())
                    .update();
        }
    }

    private Article loadChildren(Article article) {
        var salesUnits = jdbc.sql("SELECT * FROM sales_units WHERE article_id = :articleId")
                .param("articleId", article.id().value())
                .query(this::mapSalesUnitRow)
                .list();

        var supplierIds = jdbc.sql("SELECT supplier_id FROM article_suppliers WHERE article_id = :articleId")
                .param("articleId", article.id().value())
                .query((rs, rowNum) -> SupplierId.of(rs.getString("supplier_id")))
                .set();

        return Article.reconstitute(
                article.id(), article.name(), article.articleNumber(), article.categoryId(),
                salesUnits, article.status(), supplierIds, article.createdAt(), article.updatedAt()
        );
    }

    private Article mapArticleRow(ResultSet rs, int rowNum) throws SQLException {
        return Article.reconstitute(
                ArticleId.of(rs.getString("id")),
                new ArticleName(rs.getString("name")),
                new ArticleNumber(rs.getString("article_number")),
                ProductCategoryId.of(rs.getString("category_id")),
                List.of(),
                ArticleStatus.valueOf(rs.getString("status")),
                Set.of(),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class)
        );
    }

    private SalesUnit mapSalesUnitRow(ResultSet rs, int rowNum) throws SQLException {
        return SalesUnit.reconstitute(
                SalesUnitId.of(rs.getString("id")),
                Unit.valueOf(rs.getString("unit")),
                PriceModel.valueOf(rs.getString("price_model")),
                new Money(rs.getBigDecimal("price_amount"),
                        java.util.Currency.getInstance(rs.getString("price_currency")))
        );
    }
}
