package de.avatic.lcc.repositories.rates;

import de.avatic.lcc.model.rates.MatrixRate;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * Repository for managing {@link MatrixRate} entities, including retrieval and mapping of MatrixRate rows
 * from the database.
 */
@Repository
public class MatrixRateRepository {

    private final JdbcTemplate jdbcTemplate;

    /**
     * Instantiates the repository by injecting a {@link JdbcTemplate}.
     *
     * @param jdbcTemplate the {@link JdbcTemplate} to be used for database interactions
     */
    public MatrixRateRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Retrieves a paginated list of all {@link MatrixRate} entries from the database.
     *
     * @param pagination the {@link SearchQueryPagination} object containing limit, offset, and page details
     * @return a {@link SearchQueryResult} containing a list of {@link MatrixRate} entities, total count,
     * and pagination details
     */
    @Transactional
    public SearchQueryResult<MatrixRate> listRates(SearchQueryPagination pagination) {
        String query = "SELECT * FROM country_matrix_rate ORDER BY id LIMIT ? OFFSET ?";
        var totalCount = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM country_matrix_rate", Integer.class);
        return new SearchQueryResult<>(jdbcTemplate.query(query, new MatrixRateMapper(), pagination.getLimit(), pagination.getOffset()), pagination.getPage(), totalCount, pagination.getLimit());
    }

    /**
     * Retrieves a paginated list of {@link MatrixRate} entries filtered by a specific validity period ID.
     *
     * @param pagination the {@link SearchQueryPagination} object containing limit, offset, and page details
     * @param periodId   the ID of the validity period to filter the rates by
     * @return a {@link SearchQueryResult} containing a list of filtered {@link MatrixRate} entities,
     * total count, and pagination details
     */
    @Transactional
    public SearchQueryResult<MatrixRate> listRatesByPeriodId(SearchQueryPagination pagination, Integer periodId) {
        String query = "SELECT * FROM country_matrix_rate WHERE validity_period_id = ? ORDER BY id LIMIT ? OFFSET ?";
        var totalCount = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM country_matrix_rate WHERE validity_period_id = ?", Integer.class, periodId);
        return new SearchQueryResult<>(jdbcTemplate.query(query, new MatrixRateMapper(), periodId, pagination.getLimit(), pagination.getOffset()), pagination.getPage(), totalCount, pagination.getLimit());
    }

    /**
     * Retrieves a {@link MatrixRate} entity from the database by its unique identifier.
     *
     * @param id the unique identifier of the {@link MatrixRate} entity to retrieve
     * @return the {@link MatrixRate} entity associated with the specified ID
     */
    @Transactional
    public MatrixRate getById(Integer id) {
        String query = "SELECT * FROM country_matrix_rate WHERE id = ?";
        return jdbcTemplate.queryForObject(query, new MatrixRateMapper(), id);
    }

    @Transactional
    public List<MatrixRate> listAllRatesByPeriodId(Integer periodId) {
        String query = "SELECT * FROM country_matrix_rate WHERE validity_period_id = ?";
        return jdbcTemplate.query(query, new MatrixRateMapper());
    }

    @Transactional
    public Optional<MatrixRate> getByCountryIds(Integer fromCountryId, Integer toCountryId) {
        String query = "SELECT * FROM country_matrix_rate WHERE from_country_id = ? AND to_country_id = ?";
        var rates = jdbcTemplate.query(query, new MatrixRateMapper(), fromCountryId, toCountryId);

        if(rates.isEmpty())
            return Optional.empty();
        else
            return Optional.of(rates.getFirst());
    }

    /**
     * Maps rows of a {@link ResultSet} to {@link MatrixRate} objects as required by
     * the {@link JdbcTemplate}.
     */
    private static class MatrixRateMapper implements RowMapper<MatrixRate> {
        /**
         * Maps a single row from the {@link ResultSet} to a {@link MatrixRate} entity.
         *
         * @param rs     the {@link ResultSet} containing rows from the database
         * @param rowNum the index of the current row
         * @return the mapped {@link MatrixRate} entity
         * @throws SQLException if an SQL exception occurs while accessing the {@link ResultSet}
         */
        @Override
        public MatrixRate mapRow(ResultSet rs, int rowNum) throws SQLException {
            MatrixRate entity = new MatrixRate();

            entity.setId(rs.getInt("id"));
            entity.setRate(rs.getBigDecimal("rate"));
            entity.setFromCountry(rs.getInt("from_country_id"));
            entity.setToCountry(rs.getInt("to_country_id"));

            return entity;
        }
    }
}
