package de.avatic.lcc.repositories;

import de.avatic.lcc.dto.generic.NodeType;
import de.avatic.lcc.model.nodes.Node;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import org.apache.commons.lang3.NotImplementedException;
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.*;

@Repository
public class NodeRepository {


    private final JdbcTemplate jdbcTemplate;

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

    @Transactional
    public Optional<Node> getById(Integer id) {

        String query = """
                SELECT *
                FROM node
                WHERE node.id = ?""";

        var node = jdbcTemplate.query(query, new NodeMapper(), id);

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

        return Optional.ofNullable(node.getFirst());
    }

    public List<Node> getByIds(List<Integer> nodeIds) {

        String placeholders = String.join(",", Collections.nCopies(nodeIds.size(), "?"));
        String query = """
                SELECT *
                FROM node
                WHERE node.id IN (""" + placeholders + ")";

        return nodeIds.isEmpty() ? Collections.emptyList() : jdbcTemplate.query(query, new NodeMapper(), nodeIds.toArray());
    }

    private List<Map<Integer, Integer>> getPredecessorsOf(Integer id) {

        String queryChains = """
                SELECT chain.id AS id FROM node_predecessor_chain AS chain WHERE chain.node_id = ? ORDER BY chain.id
                """;

        return jdbcTemplate.query(queryChains, (chainRs, rowNum) -> {

            Integer chainId = chainRs.getInt("id");

            String query = """
                    SELECT entry.node_id AS predecessor_node_id , entry.sequence_number as sequence_number
                    FROM node_predecessor_entry AS entry
                    WHERE entry.node_predecessor_chain_id = ? ORDER BY entry.sequence_number""";

            return jdbcTemplate.query(query, rs -> {
                Map<Integer, Integer> predecessors = new HashMap<>();

                while (rs.next()) {
                    predecessors.put(rs.getInt("sequence_number"), rs.getInt("predecessor_node_id"));
                }

                return predecessors;
            }, chainId);
        }, id);
    }

    private Collection<Integer> getOutboundCountriesOf(Integer id) {
        String query = """
                SELECT outbound_country_mapping.country_id
                FROM outbound_country_mapping
                WHERE outbound_country_mapping.node_id = ?""";

        return jdbcTemplate.queryForList(query, Integer.class, id);
    }

    @Transactional
    public SearchQueryResult<Node> listNodes(String filter, Boolean excludeDeprecated, SearchQueryPagination pagination) {
        String query = buildQuery(filter, excludeDeprecated, pagination);
        String countQuery = buildCountQuery(filter, excludeDeprecated);
        List<Node> entities = null;
        Integer totalCount = 0;

        if (filter == null) {
            entities = jdbcTemplate.query(query, new NodeMapper(), pagination.getLimit(), pagination.getOffset());
            totalCount = jdbcTemplate.queryForObject(countQuery, Integer.class);
        } else {
            entities = jdbcTemplate.query(query, new NodeMapper(), "%" + filter + "%", "%" + filter + "%", "%" + filter + "%", pagination.getLimit(), pagination.getOffset());
            totalCount = jdbcTemplate.queryForObject(countQuery, Integer.class, "%" + filter + "%", "%" + filter + "%", "%" + filter + "%");
        }


        return new SearchQueryResult<>(entities, pagination.getPage(), totalCount, pagination.getLimit());
    }

    private String buildCountQuery(String filter, boolean excludeDeprecated) {
        StringBuilder queryBuilder = new StringBuilder("""
                SELECT COUNT(*)
                FROM node
                LEFT JOIN country ON country.id = node.country_id
                WHERE 1=1""");

        if (excludeDeprecated) {
            queryBuilder.append(" AND node.is_deprecated = FALSE");
        }
        if (filter != null) {
            queryBuilder.append(" AND (node.name LIKE ? OR node.address LIKE ? OR country.iso_code LIKE ?)");
        }

        return queryBuilder.toString();
    }

    private String buildQuery(String filter, Boolean excludeDeprecated, SearchQueryPagination searchQueryPagination) {
        StringBuilder queryBuilder = new StringBuilder("""
                SELECT *
                FROM node
                LEFT JOIN country ON country.id = node.country_id
                WHERE 1=1
                """);

        if (excludeDeprecated) {
            queryBuilder.append(" AND node.is_deprecated = FALSE");
        }
        if (filter != null) {
            queryBuilder.append(" AND (node.name LIKE ? OR node.address LIKE ? OR country.iso_code LIKE ?)");
        }
        queryBuilder.append(" ORDER BY node.id LIMIT ? OFFSET ?");
        return queryBuilder.toString();
    }

    @Transactional
    public Optional<Integer> setDeprecatedById(Integer id) {
        String query = "UPDATE node SET is_deprecated = TRUE WHERE id = ?";
        return Optional.ofNullable(jdbcTemplate.update(query, id) == 0 ? null : id);
    }

    @Transactional
    public Optional<Integer> update(Node node) {

        throw new NotImplementedException("Update of nodes is not yet implemented!");
        //TODO update predecessors and outbound_countries too
        //TODO implement correctly

        //TODO if node is updated set all linked RouteNodes to outdated!

//        String query = "UPDATE node SET name = ?, address = ?, country_id = ?, is_source = ?, is_destination = ?, is_intermediate = ?, predecessor_required = ? WHERE id = ?";
//
//        var nodeId = jdbcTemplate.update(query,
//                node.getName(),
//                node.getAddress(),
//                node.getCountryId(),
//                node.getSource(),
//                node.getDestination(),
//                node.getIntermediate(),
//                node.getPredecessorRequired(),
//                node.getId()) == 0 ? null : node.getId();
//
//        return Optional.ofNullable(nodeId);
    }

    public List<Node> searchNode(String filter, int limit, NodeType nodeType, boolean excludeDeprecated) {
        StringBuilder queryBuilder = new StringBuilder().append("SELECT * FROM node");
        List<Object> parameters = new ArrayList<>();

        boolean hasWhereClause = false;

        // Add filter conditions only if filter is not null
        if (filter != null && !filter.trim().isEmpty()) {
            queryBuilder.append(" WHERE (name LIKE ? OR address LIKE ?)");
            parameters.add("%" + filter + "%");
            parameters.add("%" + filter + "%");
            hasWhereClause = true;
        }

        // Add node type conditions
        if (nodeType != null) {
            if (!hasWhereClause) {
                queryBuilder.append(" WHERE ");
                hasWhereClause = true;
            } else {
                queryBuilder.append(" AND ");
            }

            if (nodeType.equals(NodeType.SOURCE)) {
                queryBuilder.append("is_source = true");
            } else if (nodeType.equals(NodeType.DESTINATION)) {
                queryBuilder.append("is_destination = true");
            } else if (nodeType.equals(NodeType.INTERMEDIATE)) {
                queryBuilder.append("is_intermediate = true");
            }
        }

        // Add deprecated filter
        if (excludeDeprecated) {
            if (!hasWhereClause) {
                queryBuilder.append(" WHERE ");
            } else {
                queryBuilder.append(" AND ");
            }
            queryBuilder.append("is_deprecated = false");
        }

        queryBuilder.append(" LIMIT ?");
        parameters.add(limit);

        return jdbcTemplate.query(queryBuilder.toString(), new NodeMapper(), parameters.toArray());
    }

    public List<Node> listAllNodes(boolean onlySources) {
        StringBuilder queryBuilder = new StringBuilder("SELECT * FROM node");
        if (onlySources) {
            queryBuilder.append(" WHERE is_source = true");
        }
        queryBuilder.append("  ORDER BY id");

        return jdbcTemplate.query(queryBuilder.toString(), new NodeMapper());
    }

    public Optional<Node> getByExternalMappingId(String mappingId) {
        String query = """
                SELECT *
                FROM node
                WHERE node.external_mapping_id = ?""";

        var node = jdbcTemplate.queryForObject(query, new NodeMapper(), mappingId);

        return Optional.ofNullable(node);
    }

    @Transactional
    public List<List<Node>> findNodeListsForReportingByMaterialId(Integer materialId) {

        String validityPeriodSql = """
                SELECT DISTINCT cj.validity_period_id
                FROM premise p
                INNER JOIN calculation_job cj ON p.id = cj.premise_id
                WHERE p.material_id = ?
                """;

        List<Integer> validityPeriodIds = jdbcTemplate.queryForList(validityPeriodSql, Integer.class, materialId);

        // For each validity period, get the set of supplier_node_ids
        List<List<Node>> nodes = new ArrayList<>();

        for (Integer validityPeriodId : validityPeriodIds) {

            String suppliersSql = """
                    SELECT DISTINCT n.*
                    FROM premise p
                    INNER JOIN calculation_job cj ON p.id = cj.premise_id
                    INNER JOIN node n ON p.supplier_node_id = n.id
                    WHERE p.material_id = ?
                    AND cj.validity_period_id = ?
                    AND p.supplier_node_id IS NOT NULL
                    """;

            String userSuppliersSql = """
                    SELECT DISTINCT un.*
                    FROM premise p
                    INNER JOIN calculation_job cj ON p.id = cj.premise_id
                    INNER JOIN sys_user_node un ON p.user_supplier_node_id = un.id
                    WHERE p.material_id = ?
                    AND cj.validity_period_id = ?
                    AND p.user_supplier_node_id IS NOT NULL
                    """;

            var periodNodes = new ArrayList<>(jdbcTemplate.query(suppliersSql, new NodeMapper(), materialId, validityPeriodId));
            periodNodes.addAll(jdbcTemplate.query(userSuppliersSql, new NodeMapper(), materialId, validityPeriodId));
            nodes.add(periodNodes);
        }

        return nodes;
    }


    /**
     * Resolves chains of predecessors for a specified destination chain by its ID.
     * If the destination chain does not require predecessors, an empty list is returned.
     * Otherwise, it constructs a list of chains, where each chain represents a sequence of predecessor nodes.
     *
     * @param destinationId The ID of the destination chain whose predecessor chains need to be resolved.
     *                      Must not be null and must correspond to an existing chain.
     * @return A list of chains, where each chain is a list of nodes.
     * Each list represents a sequence of predecessor nodes for the given destination chain.
     * If the destination chain does not require predecessors, a list containing an empty list is returned.
     * @throws RuntimeException If a predecessor chain is not found for a given sequence number in the chain.
     */
    @Transactional
    public List<List<Node>> getChainsById(Integer destinationId) {
        List<List<Node>> resolvedChains = new ArrayList<>();

        /*
         * First we collect all chains. If chain usage is not mandatory we also add
         * an empty chain. So that the chains can be by-passed.
         */

        Node destination = getById(destinationId).orElseThrow();

        if (!destination.getPredecessorRequired())
            resolvedChains.add(Collections.emptyList());

        List<Map<Integer, Integer>> chains = destination.getNodePredecessors();

        chains.forEach(chain -> {

            var currentChain = new ArrayList<Node>(Collections.nCopies(chain.size(), null));
            resolvedChains.add(currentChain);

            /*
             * Going through the key set of the chain's HashMap and putting
             * the Nodes in order of the sequence numbers into a list.
             */

            chain.keySet().forEach(sequenceNumber -> {
                var predecessor = getById(chain.get(sequenceNumber));
                if (predecessor.isEmpty()) {
                    throw new RuntimeException("Predecessor not found for chain " + chain + " and sequence number " + sequenceNumber);
                }
                currentChain.set(sequenceNumber - 1, predecessor.get());
            });

        });


        return resolvedChains;
    }


    @Transactional
    public List<Node> getByDistance(Node node, Integer regionRadius) {

        String query = """
                SELECT * FROM node
                WHERE is_deprecated = FALSE AND
                      (
                        6371 * acos(
                            cos(radians(?)) *
                            cos(radians(geo_lat)) *
                            cos(radians(geo_lng) - radians(?)) +
                            sin(radians(?)) *
                            sin(radians(geo_lat))
                        )
                    ) <= ?
                """;

        return jdbcTemplate.query(query, new NodeMapper(), node.getGeoLat(), node.getGeoLng(), node.getGeoLat(),regionRadius);
    }


    /**
     * Retrieves a list of nodes that are outbound from a given country.
     * The method considers nodes that are either explicitly mapped to the given country
     * or are intermediate nodes within the same country.
     *
     * @param countryId The ID of the country for which outbound nodes are to be retrieved.
     *                  Must not be null.
     * @return A list of nodes that are outbound for the specified country.
     * Returns an empty list if no outbound nodes are found.
     */
    public List<Node> getAllOutboundFor(Integer countryId) {
        String query = """
                SELECT node.*
                FROM node
                LEFT JOIN outbound_country_mapping ON outbound_country_mapping.node_id = node.id
                WHERE node.is_deprecated = FALSE AND (outbound_country_mapping.country_id = ? OR (node.is_intermediate = TRUE AND node.country_id = ?))
                """;

        return jdbcTemplate.query(query, new NodeMapper(), countryId, countryId);
    }

    public List<Node> findNodeListsForReportingByPeriodId(Integer materialId, Integer periodId) {


        String suppliersSql = """
                SELECT DISTINCT n.*
                FROM premise p
                INNER JOIN calculation_job cj ON p.id = cj.premise_id
                INNER JOIN node n ON p.supplier_node_id = n.id
                WHERE p.material_id = ?
                AND cj.validity_period_id = ?
                AND p.supplier_node_id IS NOT NULL
                """;


        return jdbcTemplate.query(suppliersSql, new NodeMapper(), materialId, periodId);
    }

    public Optional<Node> getByDestinationId(Integer id) {

        String query = "SELECT node.* FROM node INNER JOIN premise_destination WHERE node.id = premise_destination.destination_node_id AND premise_destination.id = ?";

        var node = jdbcTemplate.query(query, new NodeMapper(), id);

        if (node.isEmpty()) {
            return Optional.empty();
        }

        return Optional.ofNullable(node.getFirst());

    }

    private class NodeMapper implements RowMapper<Node> {

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

            data.setId(rs.getInt("id"));
            data.setName(rs.getString("name"));
            data.setAddress(rs.getString("address"));
            data.setCountryId(rs.getInt("country_id"));
            data.setDestination(rs.getBoolean("is_destination"));
            data.setSource(rs.getBoolean("is_source"));
            data.setIntermediate(rs.getBoolean("is_intermediate"));
            data.setPredecessorRequired(rs.getBoolean("predecessor_required"));

            data.setGeoLat(rs.getBigDecimal("geo_lat"));
            data.setGeoLng(rs.getBigDecimal("geo_lng"));

            data.setDeprecated(rs.getBoolean("is_deprecated"));

            data.setExternalMappingId(rs.getString("external_mapping_id"));

            data.setNodePredecessors(getPredecessorsOf(data.getId()));
            data.setOutboundCountries(getOutboundCountriesOf(data.getId()));

            return data;
        }
    }
}
