package de.effigenix.infrastructure.masterdata.persistence;

import de.effigenix.domain.masterdata.supplier.*;
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.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.List;
import java.util.Optional;

@Repository
@Profile("!no-db")
public class JdbcSupplierRepository implements SupplierRepository {

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

    private final JdbcClient jdbc;

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

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

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

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

    @Override
    public Result<RepositoryError, Void> save(Supplier supplier) {
        try {
            int rows = jdbc.sql("""
                    UPDATE suppliers
                    SET name = :name, phone = :phone, email = :email, contact_person = :contactPerson,
                        street = :street, house_number = :houseNumber, postal_code = :postalCode,
                        city = :city, country = :country,
                        payment_due_days = :paymentDueDays, payment_description = :paymentDescription,
                        quality_score = :qualityScore, delivery_score = :deliveryScore, price_score = :priceScore,
                        status = :status, updated_at = :updatedAt
                    WHERE id = :id
                    """)
                    .param("id", supplier.id().value())
                    .params(supplierParams(supplier))
                    .update();

            if (rows == 0) {
                jdbc.sql("""
                        INSERT INTO suppliers
                        (id, name, phone, email, contact_person, street, house_number, postal_code, city, country,
                         payment_due_days, payment_description, quality_score, delivery_score, price_score,
                         status, created_at, updated_at)
                        VALUES (:id, :name, :phone, :email, :contactPerson, :street, :houseNumber, :postalCode,
                                :city, :country, :paymentDueDays, :paymentDescription,
                                :qualityScore, :deliveryScore, :priceScore, :status, :createdAt, :updatedAt)
                        """)
                        .param("id", supplier.id().value())
                        .param("createdAt", supplier.createdAt())
                        .params(supplierParams(supplier))
                        .update();
            }

            saveCertificates(supplier);

            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(Supplier supplier) {
        try {
            jdbc.sql("DELETE FROM suppliers WHERE id = :id")
                    .param("id", supplier.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(SupplierName name) {
        try {
            int count = jdbc.sql("SELECT COUNT(*) FROM suppliers 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 java.util.Map<String, Object> supplierParams(Supplier supplier) {
        var params = new java.util.LinkedHashMap<String, Object>();
        params.put("name", supplier.name().value());
        params.put("phone", supplier.contactInfo() != null ? supplier.contactInfo().phone() : null);
        params.put("email", supplier.contactInfo() != null ? supplier.contactInfo().email() : null);
        params.put("contactPerson", supplier.contactInfo() != null ? supplier.contactInfo().contactPerson() : null);
        params.put("street", supplier.address() != null ? supplier.address().street() : null);
        params.put("houseNumber", supplier.address() != null ? supplier.address().houseNumber() : null);
        params.put("postalCode", supplier.address() != null ? supplier.address().postalCode() : null);
        params.put("city", supplier.address() != null ? supplier.address().city() : null);
        params.put("country", supplier.address() != null ? supplier.address().country() : null);
        params.put("paymentDueDays", supplier.paymentTerms() != null ? supplier.paymentTerms().paymentDueDays() : null);
        params.put("paymentDescription", supplier.paymentTerms() != null ? supplier.paymentTerms().description() : null);
        params.put("qualityScore", supplier.rating() != null ? supplier.rating().qualityScore() : null);
        params.put("deliveryScore", supplier.rating() != null ? supplier.rating().deliveryScore() : null);
        params.put("priceScore", supplier.rating() != null ? supplier.rating().priceScore() : null);
        params.put("status", supplier.status().name());
        params.put("updatedAt", supplier.updatedAt());
        return params;
    }

    private void saveCertificates(Supplier supplier) {
        jdbc.sql("DELETE FROM quality_certificates WHERE supplier_id = :supplierId")
                .param("supplierId", supplier.id().value())
                .update();

        for (QualityCertificate cert : supplier.certificates()) {
            jdbc.sql("""
                    INSERT INTO quality_certificates (supplier_id, certificate_type, issuer, valid_from, valid_until)
                    VALUES (:supplierId, :certificateType, :issuer, :validFrom, :validUntil)
                    """)
                    .param("supplierId", supplier.id().value())
                    .param("certificateType", cert.certificateType())
                    .param("issuer", cert.issuer())
                    .param("validFrom", cert.validFrom())
                    .param("validUntil", cert.validUntil())
                    .update();
        }
    }

    private Supplier loadCertificates(Supplier supplier) {
        var certificates = jdbc.sql(
                        "SELECT * FROM quality_certificates WHERE supplier_id = :supplierId")
                .param("supplierId", supplier.id().value())
                .query(this::mapCertificateRow)
                .list();

        return Supplier.reconstitute(
                supplier.id(), supplier.name(), supplier.address(), supplier.contactInfo(),
                supplier.paymentTerms(), certificates, supplier.rating(), supplier.status(),
                supplier.createdAt(), supplier.updatedAt()
        );
    }

    private Supplier mapSupplierRow(ResultSet rs, int rowNum) throws SQLException {
        // Address (optional)
        Address address = null;
        String street = rs.getString("street");
        if (street != null) {
            address = new Address(street, rs.getString("house_number"),
                    rs.getString("postal_code"), rs.getString("city"), rs.getString("country"));
        }

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

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

        // SupplierRating (optional)
        SupplierRating rating = null;
        Integer qualityScore = rs.getObject("quality_score", Integer.class);
        if (qualityScore != null) {
            rating = new SupplierRating(qualityScore,
                    rs.getInt("delivery_score"), rs.getInt("price_score"));
        }

        return Supplier.reconstitute(
                SupplierId.of(rs.getString("id")),
                new SupplierName(rs.getString("name")),
                address,
                contactInfo,
                paymentTerms,
                List.of(),
                rating,
                SupplierStatus.valueOf(rs.getString("status")),
                rs.getObject("created_at", OffsetDateTime.class),
                rs.getObject("updated_at", OffsetDateTime.class)
        );
    }

    private QualityCertificate mapCertificateRow(ResultSet rs, int rowNum) throws SQLException {
        return new QualityCertificate(
                rs.getString("certificate_type"),
                rs.getString("issuer"),
                rs.getObject("valid_from", LocalDate.class),
                rs.getObject("valid_until", LocalDate.class)
        );
    }
}
