package de.effigenix.infrastructure.masterdata.persistence;

import de.effigenix.domain.masterdata.*;
import de.effigenix.domain.masterdata.article.ArticleId;
import de.effigenix.domain.masterdata.customer.*;
import de.effigenix.shared.common.*;
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.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.*;

@Repository
@Profile("!no-db")
public class JdbcCustomerRepository implements CustomerRepository {

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

    private final JdbcClient jdbc;

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

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

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

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

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

    @Override
    public Result<RepositoryError, Void> save(Customer customer) {
        try {
            int rows = jdbc.sql("""
                    UPDATE customers
                    SET name = :name, type = :type, phone = :phone, email = :email,
                        contact_person = :contactPerson,
                        billing_street = :billingStreet, billing_house_number = :billingHouseNumber,
                        billing_postal_code = :billingPostalCode, billing_city = :billingCity,
                        billing_country = :billingCountry,
                        payment_due_days = :paymentDueDays, payment_description = :paymentDescription,
                        status = :status, updated_at = :updatedAt
                    WHERE id = :id
                    """)
                    .param("id", customer.id().value())
                    .params(customerParams(customer))
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO customers
                        (id, name, type, phone, email, contact_person,
                         billing_street, billing_house_number, billing_postal_code, billing_city, billing_country,
                         payment_due_days, payment_description, status, created_at, updated_at)
                        VALUES (:id, :name, :type, :phone, :email, :contactPerson,
                                :billingStreet, :billingHouseNumber, :billingPostalCode, :billingCity, :billingCountry,
                                :paymentDueDays, :paymentDescription, :status, :createdAt, :updatedAt)
                        """)
                        .param("id", customer.id().value())
                        .param("createdAt", customer.createdAt())
                        .params(customerParams(customer))
                        .update();
            }

            saveDeliveryAddresses(customer);
            savePreferences(customer);
            saveFrameContract(customer);

            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(Customer customer) {
        try {
            jdbc.sql("DELETE FROM frame_contracts WHERE customer_id = :customerId")
                    .param("customerId", customer.id().value())
                    .update();
            jdbc.sql("DELETE FROM customers WHERE id = :id")
                    .param("id", customer.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(CustomerName name) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM customers 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 Map<String, Object> customerParams(Customer customer) {
        var params = new LinkedHashMap<String, Object>();
        params.put("name", customer.name().value());
        params.put("type", customer.type().name());
        params.put("phone", customer.contactInfo() != null ? customer.contactInfo().phone() : null);
        params.put("email", customer.contactInfo() != null ? customer.contactInfo().email() : null);
        params.put("contactPerson", customer.contactInfo() != null ? customer.contactInfo().contactPerson() : null);
        params.put("billingStreet", customer.billingAddress().street());
        params.put("billingHouseNumber", customer.billingAddress().houseNumber());
        params.put("billingPostalCode", customer.billingAddress().postalCode());
        params.put("billingCity", customer.billingAddress().city());
        params.put("billingCountry", customer.billingAddress().country());
        params.put("paymentDueDays", customer.paymentTerms() != null ? customer.paymentTerms().paymentDueDays() : null);
        params.put("paymentDescription", customer.paymentTerms() != null ? customer.paymentTerms().description() : null);
        params.put("status", customer.status().name());
        params.put("updatedAt", customer.updatedAt());
        return params;
    }

    private void saveDeliveryAddresses(Customer customer) {
        jdbc.sql("DELETE FROM delivery_addresses WHERE customer_id = :customerId")
                .param("customerId", customer.id().value())
                .update();

        for (DeliveryAddress da : customer.deliveryAddresses()) {
            jdbc.sql("""
                    INSERT INTO delivery_addresses
                    (customer_id, label, street, house_number, postal_code, city, country, contact_person, delivery_notes)
                    VALUES (:customerId, :label, :street, :houseNumber, :postalCode, :city, :country, :contactPerson, :deliveryNotes)
                    """)
                    .param("customerId", customer.id().value())
                    .param("label", da.label())
                    .param("street", da.address().street())
                    .param("houseNumber", da.address().houseNumber())
                    .param("postalCode", da.address().postalCode())
                    .param("city", da.address().city())
                    .param("country", da.address().country())
                    .param("contactPerson", da.contactPerson())
                    .param("deliveryNotes", da.deliveryNotes())
                    .update();
        }
    }

    private void savePreferences(Customer customer) {
        jdbc.sql("DELETE FROM customer_preferences WHERE customer_id = :customerId")
                .param("customerId", customer.id().value())
                .update();

        for (CustomerPreference pref : customer.preferences()) {
            jdbc.sql("INSERT INTO customer_preferences (customer_id, preference) VALUES (:customerId, :preference)")
                    .param("customerId", customer.id().value())
                    .param("preference", pref.name())
                    .update();
        }
    }

    private void saveFrameContract(Customer customer) {
        jdbc.sql("DELETE FROM frame_contracts WHERE customer_id = :customerId")
                .param("customerId", customer.id().value())
                .update();

        if (customer.frameContract() != null) {
            var fc = customer.frameContract();
            jdbc.sql("""
                    INSERT INTO frame_contracts (id, customer_id, valid_from, valid_until, delivery_rhythm)
                    VALUES (:id, :customerId, :validFrom, :validUntil, :deliveryRhythm)
                    """)
                    .param("id", fc.id().value())
                    .param("customerId", customer.id().value())
                    .param("validFrom", fc.validFrom())
                    .param("validUntil", fc.validUntil())
                    .param("deliveryRhythm", fc.deliveryRhythm().name())
                    .update();

            saveContractLineItems(fc);
        }
    }

    private void saveContractLineItems(FrameContract fc) {
        jdbc.sql("DELETE FROM contract_line_items WHERE frame_contract_id = :fcId")
                .param("fcId", fc.id().value())
                .update();

        for (ContractLineItem item : fc.lineItems()) {
            jdbc.sql("""
                    INSERT INTO contract_line_items
                    (frame_contract_id, article_id, agreed_price_amount, agreed_price_currency, agreed_quantity, unit)
                    VALUES (:fcId, :articleId, :priceAmount, :priceCurrency, :quantity, :unit)
                    """)
                    .param("fcId", fc.id().value())
                    .param("articleId", item.articleId().value())
                    .param("priceAmount", item.agreedPrice().amount())
                    .param("priceCurrency", item.agreedPrice().currency().getCurrencyCode())
                    .param("quantity", item.agreedQuantity())
                    .param("unit", item.unit() != null ? item.unit().name() : null)
                    .update();
        }
    }

    private Customer loadChildren(Customer customer) {
        var deliveryAddresses = jdbc.sql(
                        "SELECT * FROM delivery_addresses WHERE customer_id = :customerId")
                .param("customerId", customer.id().value())
                .query(this::mapDeliveryAddressRow)
                .list();

        var preferences = jdbc.sql(
                        "SELECT preference FROM customer_preferences WHERE customer_id = :customerId")
                .param("customerId", customer.id().value())
                .query((rs, rowNum) -> CustomerPreference.valueOf(rs.getString("preference")))
                .set();

        FrameContract frameContract = loadFrameContract(customer.id().value());

        return Customer.reconstitute(
                customer.id(), customer.name(), customer.type(), customer.billingAddress(),
                customer.contactInfo(), customer.paymentTerms(), deliveryAddresses,
                frameContract, preferences, customer.status(), customer.createdAt(), customer.updatedAt()
        );
    }

    private FrameContract loadFrameContract(String customerId) {
        var fcOpt = jdbc.sql("SELECT * FROM frame_contracts WHERE customer_id = :customerId")
                .param("customerId", customerId)
                .query(this::mapFrameContractRow)
                .optional();

        if (fcOpt.isEmpty()) {
            return null;
        }

        var fc = fcOpt.get();
        var lineItems = jdbc.sql(
                        "SELECT * FROM contract_line_items WHERE frame_contract_id = :fcId")
                .param("fcId", fc.id().value())
                .query(this::mapContractLineItemRow)
                .list();

        return FrameContract.reconstitute(fc.id(), fc.validFrom(), fc.validUntil(),
                fc.deliveryRhythm(), lineItems);
    }

    private Customer mapCustomerRow(ResultSet rs, int rowNum) throws SQLException {
        Address billingAddress = new Address(
                rs.getString("billing_street"),
                rs.getString("billing_house_number"),
                rs.getString("billing_postal_code"),
                rs.getString("billing_city"),
                rs.getString("billing_country")
        );

        ContactInfo contactInfo = new ContactInfo(
                rs.getString("phone"), rs.getString("email"), rs.getString("contact_person"));

        PaymentTerms paymentTerms = null;
        Integer dueDays = rs.getObject("payment_due_days", Integer.class);
        if (dueDays != null) {
            paymentTerms = new PaymentTerms(dueDays, rs.getString("payment_description"));
        }

        return Customer.reconstitute(
                CustomerId.of(rs.getString("id")),
                new CustomerName(rs.getString("name")),
                CustomerType.valueOf(rs.getString("type")),
                billingAddress,
                contactInfo,
                paymentTerms,
                List.of(),
                null,
                Set.of(),
                CustomerStatus.valueOf(rs.getString("status")),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class)
        );
    }

    private DeliveryAddress mapDeliveryAddressRow(ResultSet rs, int rowNum) throws SQLException {
        Address address = new Address(
                rs.getString("street"),
                rs.getString("house_number"),
                rs.getString("postal_code"),
                rs.getString("city"),
                rs.getString("country")
        );
        return new DeliveryAddress(
                rs.getString("label"), address,
                rs.getString("contact_person"), rs.getString("delivery_notes")
        );
    }

    private FrameContract mapFrameContractRow(ResultSet rs, int rowNum) throws SQLException {
        return FrameContract.reconstitute(
                FrameContractId.of(rs.getString("id")),
                rs.getObject("valid_from", LocalDate.class),
                rs.getObject("valid_until", LocalDate.class),
                DeliveryRhythm.valueOf(rs.getString("delivery_rhythm")),
                List.of()
        );
    }

    private ContractLineItem mapContractLineItemRow(ResultSet rs, int rowNum) throws SQLException {
        String unitStr = rs.getString("unit");
        return new ContractLineItem(
                ArticleId.of(rs.getString("article_id")),
                new Money(rs.getBigDecimal("agreed_price_amount"),
                        Currency.getInstance(rs.getString("agreed_price_currency"))),
                rs.getObject("agreed_quantity", BigDecimal.class),
                unitStr != null ? Unit.valueOf(unitStr) : null
        );
    }
}
