package de.avatic.lcc.repositories.rates;

import de.avatic.lcc.dto.generic.TransportType;
import de.avatic.lcc.model.rates.ContainerRate;
import de.avatic.lcc.model.rates.ValidityPeriodState;
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.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

@Repository
public class ContainerRateRepository {

    private final JdbcTemplate jdbcTemplate;

    public ContainerRateRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public SearchQueryResult<ContainerRate> listRatesByPeriodId(SearchQueryPagination pagination, Integer periodId) {
        String query = "SELECT * FROM container_rate WHERE validity_period_id = ? ORDER BY id LIMIT ? OFFSET ?";
        String countQuery = "SELECT COUNT(*) FROM container_rate WHERE validity_period_id = ?";
        Integer totalCount = jdbcTemplate.queryForObject(countQuery, Integer.class, periodId);

        return new SearchQueryResult<>(jdbcTemplate.query(query, new ContainerRateMapper(), periodId, pagination.getLimit(), pagination.getOffset()), pagination.getPage(), totalCount, pagination.getLimit());
    }

    public Optional<ContainerRate> getById(Integer id) {
        String query = "SELECT * FROM container_rate WHERE id = ?";

        var rate = jdbcTemplate.query(query, new ContainerRateMapper(), id);

        if(rate.isEmpty())
            return Optional.empty();

        return Optional.of(rate.getFirst());
    }

    public List<ContainerRate> listAllRatesByPeriodId(Integer periodId) {
        String query = "SELECT * FROM container_rate WHERE validity_period_id = ?";

        return jdbcTemplate.query(query, new ContainerRateMapper());
    }

    @Transactional
    public List<ContainerRate> findRoutesByStartNodeIdAndDestinationCountryId(Integer startNodeId, List<Integer> destinationCountryIds) {

        if (startNodeId == null || destinationCountryIds == null || destinationCountryIds.isEmpty()) {
            return Collections.emptyList();
        }


        String destinationCountryPlaceholders = String.join(",", Collections.nCopies(destinationCountryIds.size(), "?"));

        String query = """
                SELECT container_rate.id AS id,
                       container_rate.validity_period_id AS validity_period_id,
                       container_rate.container_rate_type AS container_rate_type,
                       container_rate.from_node_id AS from_node_id,
                       container_rate.to_node_id AS to_node_id,
                       container_rate.rate_feu AS rate_feu,
                       container_rate.rate_teu AS rate_teu,
                       container_rate.rate_hc AS rate_hc,
                       container_rate.lead_time AS lead_time,
                       to_node.country_id as to_country_id,
                       from_node.country_id as from_country_id
                FROM container_rate
                LEFT JOIN node AS to_node ON to_node.id = container_rate.to_node_id
                LEFT JOIN  node AS from_node ON from_node.id = container_rate.from_node_id
                LEFT JOIN validity_period ON validity_period.id = container_rate.validity_period_id
                WHERE validity_period.state = ?
                AND (container_rate.container_rate_type = ? OR container_rate.container_rate_type = ?)
                AND container_rate.from_node_id = ? AND to_node.country_id IN (%s)""".formatted(
                destinationCountryPlaceholders);

        List<Object> params = new ArrayList<>();
        params.add(ValidityPeriodState.VALID.name());
        params.add(TransportType.SEA.name());
        params.add(TransportType.RAIL.name());
        params.add(startNodeId);
        params.addAll(destinationCountryIds);

        return jdbcTemplate.query(query, new ContainerRateMapper(true), params.toArray());
    }

    @Transactional
    public List<ContainerRate> getPostRunsFor(ContainerRate mainRun) {

        String query = """
                SELECT container_rate.id AS id,
                container_rate.validity_period_id AS validity_period_id,
                container_rate.container_rate_type AS container_rate_type,
                container_rate.from_node_id AS from_node_id,
                container_rate.to_node_id AS to_node_id,
                container_rate.rate_feu AS rate_feu,
                container_rate.rate_teu AS rate_teu,
                container_rate.rate_hc AS rate_hc,
                container_rate.lead_time AS lead_time,
                to_node.country_id as to_country_id,
                from_node.country_id as from_country_id
                FROM container_rate
                LEFT JOIN node AS to_node ON to_node.id = container_rate.to_node_id
                LEFT JOIN  node AS from_node ON from_node.id = container_rate.from_node_id
                LEFT JOIN validity_period ON validity_period.id = container_rate.validity_period_id
                WHERE validity_period.state = ?
                AND container_rate.from_node_id = ? AND container_rate.container_rate_type = ?""";

        return jdbcTemplate.query(query, new ContainerRateMapper(true), ValidityPeriodState.VALID.name(), mainRun.getToNodeId(), TransportType.POST_RUN.name());
    }

    public Optional<ContainerRate> findRoute(Integer fromNodeId, Integer toNodeId, TransportType type) {
        String query = """
                SELECT * FROM container_rate WHERE from_node_id = ? AND to_node_id = ? AND container_rate_type = ?
                """;

        var route = jdbcTemplate.query(query, new ContainerRateMapper(), fromNodeId, toNodeId, type.name());

        if(route.isEmpty())
            return Optional.empty();

        return Optional.of(route.getFirst());
    }


    private static class ContainerRateMapper implements RowMapper<ContainerRate> {

        private final boolean fetchCountryIds;

        public ContainerRateMapper(boolean fetchCountryIds) {
            this.fetchCountryIds = fetchCountryIds;
        }

        public ContainerRateMapper() {
            this(false);
        }

        @Override
        public ContainerRate mapRow(ResultSet rs, int rowNum) throws SQLException {
            var entity = new ContainerRate();

            entity.setId(rs.getInt("id"));
            entity.setValidityPeriodId(rs.getInt("validity_period_id"));
            entity.setFromNodeId(rs.getInt("from_node_id"));
            entity.setToNodeId(rs.getInt("to_node_id"));
            entity.setType(TransportType.valueOf(rs.getString("container_rate_type")));
            entity.setLeadTime(rs.getInt("lead_time"));
            entity.setRateFeu(rs.getBigDecimal("rate_feu"));
            entity.setRateTeu(rs.getBigDecimal("rate_teu"));
            entity.setRateHc(rs.getBigDecimal("rate_hc"));

            if (fetchCountryIds) {
                entity.setToCountryId(rs.getInt("to_country_id"));
                entity.setFromCountryId(rs.getInt("from_country_id"));
            }

            return entity;
        }
    }
}
