package org.github.tess1o.geopulse.gps.repository;

import io.quarkus.hibernate.orm.panache.PanacheRepository;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.persistence.Query;
import org.github.tess1o.geopulse.gps.model.GpsPointEntity;
import org.github.tess1o.geopulse.gps.model.GpsPointFilterDTO;
import org.github.tess1o.geopulse.shared.service.TimestampUtils;
import org.github.tess1o.geopulse.streaming.model.domain.GPSPoint;
import org.locationtech.jts.geom.Point;

import java.time.Instant;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.UUID;
import java.util.function.Consumer;

@ApplicationScoped
public class GpsPointRepository implements PanacheRepository<GpsPointEntity> {
    /**
     * Find GPS points for a specific user within a time period.
     * Results are ordered by timestamp to ensure the path is in chronological order.
     *
     * @param userId    The ID of the user
     * @param startTime The start of the time period
     * @param endTime   The end of the time period
     * @return A list of GPS point entities ordered by timestamp
     */
    public List<GpsPointEntity> findByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime) {
        return list("user.id = ?1 AND timestamp >= ?2 AND timestamp <= ?3 ORDER BY timestamp ASC",
                userId, startTime, endTime);
    }

    public List<GpsPointEntity> findEligibleByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime,
                                                                  Double maxAccuracy) {
        return find(buildEligibleTimePeriodQuery(maxAccuracy) + " ORDER BY timestamp ASC",
                buildEligibleTimePeriodParams(userId, startTime, endTime, maxAccuracy))
                .list();
    }

    public List<GpsPointEntity> findMapPointsByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime, int limit) {
        return find("user.id = ?1 AND timestamp >= ?2 AND timestamp <= ?3 ORDER BY timestamp ASC",
                userId, startTime, endTime)
                .page(0, limit)
                .list();
    }

    public List<GpsPointEntity> findEligibleMapPointsByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime,
                                                                           int limit, Double maxAccuracy) {
        return find(buildEligibleTimePeriodQuery(maxAccuracy) + " ORDER BY timestamp ASC",
                buildEligibleTimePeriodParams(userId, startTime, endTime, maxAccuracy))
                .page(0, limit)
                .list();
    }

    public long countByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime) {
        return count("user.id = ?1 AND timestamp >= ?2 AND timestamp <= ?3",
                userId, startTime, endTime);
    }

    public long countEligibleByUserIdAndTimePeriod(UUID userId, Instant startTime, Instant endTime,
                                                   Double maxAccuracy) {
        return count(buildEligibleTimePeriodQuery(maxAccuracy),
                buildEligibleTimePeriodParams(userId, startTime, endTime, maxAccuracy));
    }

    private String buildEligibleTimePeriodQuery(Double maxAccuracy) {
        String query = "user.id = :userId AND timestamp >= :startTime AND timestamp <= :endTime AND coordinates IS NOT NULL";
        if (maxAccuracy != null) {
            query += " AND (accuracy IS NULL OR accuracy <= :maxAccuracy)";
        }
        return query;
    }

    private Map<String, Object> buildEligibleTimePeriodParams(UUID userId, Instant startTime, Instant endTime,
                                                              Double maxAccuracy) {
        Map<String, Object> params = new HashMap<>();
        params.put("userId", userId);
        params.put("startTime", startTime);
        params.put("endTime", endTime);
        if (maxAccuracy != null) {
            params.put("maxAccuracy", maxAccuracy);
        }
        return params;
    }

    /**
     * Get friend trail points for the current user in one SQL query.
     * For every friend who shared live location, the window is:
     * [max(friend timestamp up to endTime) - minutes, max(friend timestamp up to endTime)].
     *
     * @param userId  Current user ID
     * @param minutes Window size in minutes
     * @param endTime Upper timestamp bound for latest point selection
     * @return Trail points ordered by user and timestamp
     */
    public List<GpsPointEntity> findFriendTrailPointsForUser(UUID userId, int minutes, Instant endTime) {
        if (userId == null || minutes <= 0 || endTime == null) {
            return List.of();
        }

        String sql = """
                WITH eligible_friends AS (
                    SELECT uf.friend_id
                    FROM user_friends uf
                    JOIN user_friend_permissions ufp
                      ON ufp.user_id = uf.friend_id
                     AND ufp.friend_id = uf.user_id
                    WHERE uf.user_id = :userId
                      AND ufp.share_live_location = true
                ),
                latest_by_friend AS (
                    SELECT gp.user_id, MAX(gp.timestamp) AS max_timestamp
                    FROM gps_points gp
                    JOIN eligible_friends ef ON ef.friend_id = gp.user_id
                    WHERE gp.timestamp <= :endTime
                    GROUP BY gp.user_id
                )
                SELECT gp.*
                FROM gps_points gp
                JOIN latest_by_friend lf ON lf.user_id = gp.user_id
                WHERE gp.timestamp >= lf.max_timestamp - make_interval(mins => :minutes)
                  AND gp.timestamp <= lf.max_timestamp
                ORDER BY gp.user_id ASC, gp.timestamp ASC
                """;

        return getEntityManager()
                .createNativeQuery(sql, GpsPointEntity.class)
                .setParameter("userId", userId)
                .setParameter("endTime", endTime)
                .setParameter("minutes", minutes)
                .getResultList();
    }

    public GpsPointEntity findByUserIdLatestGpsPoint(UUID userId) {
        return find("user.id = ?1 ORDER BY timestamp DESC", userId).firstResult();
    }

    public Optional<GpsPointEntity> findLatest() {
        return find("ORDER BY timestamp DESC")
                .firstResultOptional();
    }

    public Optional<GpsPointEntity> findLatest(UUID userId) {
        return find("user.id = ?1 ORDER BY timestamp DESC", userId)
                .firstResultOptional();
    }

    public Optional<GpsPointEntity> findLatestByUserIdAtOrBeforeTimestamp(UUID userId, Instant timestamp) {
        return find("user.id = ?1 and timestamp <= ?2 order by timestamp desc", userId, timestamp)
                .firstResultOptional();
    }

    public Optional<GpsPointEntity> findEarliestByUserIdAtOrAfterTimestamp(UUID userId, Instant timestamp) {
        return find("user.id = ?1 and timestamp >= ?2 order by timestamp asc", userId, timestamp)
                .firstResultOptional();
    }

    /**
     * Find GPS points for a user within a date range with pagination and sorting.
     *
     * @param userId    The ID of the user
     * @param startTime The start of the time period
     * @param endTime   The end of the time period
     * @param page      Page number (0-based)
     * @param pageSize  Number of items per page
     * @param sortBy    Field to sort by (timestamp, altitude, battery)
     * @param sortOrder Sort order (asc or desc)
     * @return A list of GPS point entities for the page
     */
    public List<GpsPointEntity> findByUserAndDateRange(UUID userId, Instant startTime, Instant endTime,
                                                       int page, int pageSize, String sortBy, String sortOrder) {
        // Validate sort field to prevent SQL injection
        String validatedSortBy = validateSortField(sortBy);
        String validatedSortOrder = sortOrder.equalsIgnoreCase("asc") ? "ASC" : "DESC";

        String query = String.format("user.id = ?1 AND timestamp >= ?2 AND timestamp <= ?3 ORDER BY %s %s",
                validatedSortBy, validatedSortOrder);

        return find(query, userId, startTime, endTime)
                .page(page, pageSize)
                .list();
    }

    /**
     * Stream GPS points for export with keyset pagination.
     * Results are ordered by timestamp and id so duplicate timestamps are handled deterministically.
     */
    public void streamByUserAndDateRangeForExport(UUID userId, Instant startTime, Instant endTime,
                                                  int batchSize, Consumer<List<GpsPointEntity>> consumer) {
        Instant cursorTimestamp = null;
        Long cursorId = null;
        int normalizedBatchSize = Math.max(1, batchSize);

        while (true) {
            List<GpsPointEntity> batch = findExportDateRangeChunk(
                    userId, startTime, endTime, cursorTimestamp, cursorId, normalizedBatchSize);
            if (batch.isEmpty()) {
                break;
            }

            consumer.accept(batch);

            GpsPointEntity lastPoint = batch.get(batch.size() - 1);
            cursorTimestamp = lastPoint.getTimestamp();
            cursorId = lastPoint.getId();

            // Clear the persistence context to keep long exports memory-bounded.
            getEntityManager().clear();
        }
    }

    private List<GpsPointEntity> findExportDateRangeChunk(UUID userId, Instant startTime, Instant endTime,
                                                          Instant cursorTimestamp, Long cursorId, int batchSize) {
        String cursorPredicate = cursorTimestamp != null && cursorId != null
                ? " AND (gp.timestamp > :cursorTimestamp OR (gp.timestamp = :cursorTimestamp AND gp.id > :cursorId))"
                : "";

        Query query = getEntityManager().createQuery(
                        "SELECT gp FROM GpsPointEntity gp " +
                                "WHERE gp.user.id = :userId " +
                                "AND gp.timestamp IS NOT NULL " +
                                "AND gp.timestamp >= :startTime " +
                                "AND gp.timestamp <= :endTime" +
                                cursorPredicate +
                                " ORDER BY gp.timestamp ASC, gp.id ASC",
                        GpsPointEntity.class)
                .setParameter("userId", userId)
                .setParameter("startTime", startTime)
                .setParameter("endTime", endTime)
                .setMaxResults(batchSize);

        if (cursorTimestamp != null && cursorId != null) {
            query.setParameter("cursorTimestamp", cursorTimestamp);
            query.setParameter("cursorId", cursorId);
        }

        return query.getResultList();
    }

    /**
     * Validate and map sort field to database column name.
     * Prevents SQL injection by only allowing whitelisted fields.
     *
     * @param sortBy The requested sort field
     * @return The validated database column name
     */
    private String validateSortField(String sortBy) {
        return switch (sortBy.toLowerCase()) {
            case "timestamp" -> "timestamp";
            case "altitude" -> "altitude";
            case "battery" -> "battery";
            case "velocity" -> "velocity";
            case "accuracy" -> "accuracy";
            default -> "timestamp"; // Default to timestamp if invalid field
        };
    }

    /**
     * Get GPS point summary data in a single optimized query.
     * Returns: [totalCount, todayCount, firstTimestamp, lastTimestamp]
     */
    public Object[] getGpsPointSummaryData(UUID userId, Instant todayStart, Instant todayEnd) {
        return (Object[]) getEntityManager().createNativeQuery(
                        "SELECT " +
                                "  COUNT(*) as total_count, " +
                                "  COUNT(*) FILTER (WHERE timestamp >= :todayStart AND timestamp < :todayEnd) as today_count, " +
                                "  MIN(timestamp) as first_timestamp, " +
                                "  MAX(timestamp) as last_timestamp " +
                                "FROM gps_points " +
                                "WHERE user_id = :userId")
                .setParameter("userId", userId)
                .setParameter("todayStart", todayStart)
                .setParameter("todayEnd", todayEnd)
                .getSingleResult();
    }

    public void deleteByUserId(UUID userId) {
        delete("user.id = ?1", userId);
    }

    public List<GpsPointEntity> findByUserId(UUID userId) {
        return list("user.id = ?1", userId);
    }

    public Optional<GpsPointEntity> findByUniqueKey(UUID userId, Instant timestamp, Point coordinates) {
        return find("user.id = ?1 AND timestamp = ?2 AND coordinates = ?3", userId, timestamp, coordinates)
                .firstResultOptional();
    }

    /**
     * Load essential GPS data in chunks for large datasets.
     * Prevents query timeouts and provides better resource management.
     *
     * @param userId        The user ID
     * @param fromTimestamp Start timestamp for data range
     * @param cursorTimestamp Timestamp cursor from the previous chunk
     * @param cursorId      ID cursor from the previous chunk
     * @param limit         Number of points to fetch
     * @return List of lightweight GPS points for this chunk
     */
    public List<GPSPoint> findEssentialDataChunk(UUID userId, Instant fromTimestamp,
                                                 Instant cursorTimestamp, Long cursorId, int limit) {
        return findEssentialDataChunk(userId, fromTimestamp, cursorTimestamp, cursorId, limit, null);
    }

    public List<GPSPoint> findEssentialDataChunk(UUID userId, Instant fromTimestamp,
                                                 Instant cursorTimestamp, Long cursorId, int limit,
                                                 String environmentDatasetVersion) {
        String environmentSelect = environmentDatasetVersion == null
                ? "NULL::boolean as on_water "
                : "env.on_water as on_water ";
        String environmentJoin = environmentDatasetVersion == null
                ? ""
                : "LEFT JOIN gps_point_environment env ON env.gps_point_id = gp.id AND env.environment_dataset_version = :environmentDatasetVersion ";
        String cursorPredicate = cursorTimestamp != null && cursorId != null
                ? "AND (gp.timestamp > :cursorTimestamp OR (gp.timestamp = :cursorTimestamp AND gp.id > :cursorId)) "
                : "";

        Query query = getEntityManager().createNativeQuery(
                        "SELECT gp.timestamp as timestamp_utc, ST_Y(gp.coordinates) as latitude, ST_X(gp.coordinates) as longitude, " +
                                "COALESCE(gp.velocity, 0.0) / 3.6 as speed, COALESCE(gp.accuracy, 0.0) as accuracy, " +
                                environmentSelect + ", gp.id as gps_point_id " +
                                "FROM gps_points gp " +
                                environmentJoin +
                                "WHERE gp.user_id = :userId AND gp.timestamp >= :fromTimestamp " +
                                cursorPredicate +
                                "ORDER BY gp.timestamp ASC, gp.id ASC " +
                                "LIMIT :limit")
                .setParameter("userId", userId)
                .setParameter("fromTimestamp", fromTimestamp)
                .setParameter("limit", limit);

        if (cursorTimestamp != null && cursorId != null) {
            query.setParameter("cursorTimestamp", cursorTimestamp);
            query.setParameter("cursorId", cursorId);
        }
        if (environmentDatasetVersion != null) {
            query.setParameter("environmentDatasetVersion", environmentDatasetVersion);
        }

        List<Object[]> results = query.getResultList();

        return results.stream()
                .map(this::mapToGPSPoint)
                .toList();
    }

    /**
     * Load essential GPS points for a specific interval.
     */
    public List<GPSPoint> findEssentialPointsInInterval(UUID userId, Instant start, Instant end) {
        return findEssentialPointsInInterval(userId, start, end, null);
    }

    public List<GPSPoint> findEssentialPointsInInterval(UUID userId, Instant start, Instant end, String environmentDatasetVersion) {
        String environmentSelect = environmentDatasetVersion == null
                ? "NULL::boolean as on_water "
                : "env.on_water as on_water ";
        String environmentJoin = environmentDatasetVersion == null
                ? ""
                : "LEFT JOIN gps_point_environment env ON env.gps_point_id = gp.id AND env.environment_dataset_version = :environmentDatasetVersion ";

        Query query = getEntityManager().createNativeQuery(
                        "SELECT gp.timestamp as timestamp_utc, ST_Y(gp.coordinates) as latitude, ST_X(gp.coordinates) as longitude, " +
                                "COALESCE(gp.velocity, 0.0) / 3.6 as speed, COALESCE(gp.accuracy, 0.0) as accuracy, " +
                                environmentSelect +
                                "FROM gps_points gp " +
                                environmentJoin +
                                "WHERE gp.user_id = :userId AND gp.timestamp >= :start AND gp.timestamp <= :end " +
                                "ORDER BY gp.timestamp ASC, gp.id ASC")
                .setParameter("userId", userId)
                .setParameter("start", start)
                .setParameter("end", end);

        if (environmentDatasetVersion != null) {
            query.setParameter("environmentDatasetVersion", environmentDatasetVersion);
        }

        List<Object[]> results = query.getResultList();

        return results.stream()
                .map(this::mapToGPSPoint)
                .toList();
    }

    /**
     * Estimate total count of GPS points for a user from a specific timestamp.
     * Used for memory allocation optimization in chunked loading.
     *
     * @param userId        The user ID
     * @param fromTimestamp Start timestamp
     * @return Estimated count of GPS points
     */
    public Long estimatePointCount(UUID userId, Instant fromTimestamp) {
        Query query = getEntityManager().createQuery(
                "SELECT COUNT(gp) FROM GpsPointEntity gp " +
                        "WHERE gp.user.id = :userId AND gp.timestamp >= :fromTimestamp");

        query.setParameter("userId", userId);
        query.setParameter("fromTimestamp", fromTimestamp);

        return (Long) query.getSingleResult();
    }

    /**
     * Map native SQL result array to GPSPoint object.
     * Expected array: [timestamp, latitude, longitude, speed, accuracy, onWater, id?]
     */
    private GPSPoint mapToGPSPoint(Object[] row) {
        Instant timestampInstant = TimestampUtils.getInstantSafe(row[0]);
        Double latitude = ((Number) row[1]).doubleValue();
        Double longitude = ((Number) row[2]).doubleValue();
        Double speed = ((Number) row[3]).doubleValue();
        Double accuracy = ((Number) row[4]).doubleValue();
        Boolean onWater = row.length > 5 && row[5] != null
                ? (Boolean) row[5]
                : null;

        GPSPoint gpsPoint = new GPSPoint(latitude, longitude, speed, accuracy, timestampInstant);
        gpsPoint.setOnWater(onWater);
        if (row.length > 6 && row[6] != null) {
            gpsPoint.setId(((Number) row[6]).longValue());
        }
        return gpsPoint;
    }

    // =================== FILTERING METHODS ===================

    /**
     * Find GPS points with filters, pagination and sorting.
     *
     * @param userId    The ID of the user
     * @param filters   Filter criteria
     * @param page      Page number (0-based)
     * @param pageSize  Number of items per page
     * @param sortBy    Field to sort by
     * @param sortOrder Sort order (asc or desc)
     * @return A list of GPS point entities for the page
     */
    public List<GpsPointEntity> findByUserAndFilters(UUID userId, GpsPointFilterDTO filters,
                                                     int page, int pageSize, String sortBy, String sortOrder) {
        QueryBuilder queryBuilder = buildFilterQuery(userId, filters);

        // Add sorting
        String validatedSortBy = validateSortField(sortBy);
        String validatedSortOrder = sortOrder.equalsIgnoreCase("asc") ? "ASC" : "DESC";
        queryBuilder.query.append(String.format(" ORDER BY %s %s", validatedSortBy, validatedSortOrder));

        Query query = getEntityManager().createQuery(queryBuilder.query.toString(), GpsPointEntity.class);
        queryBuilder.params.forEach(query::setParameter);

        query.setFirstResult(page * pageSize);
        query.setMaxResults(pageSize);

        return query.getResultList();
    }

    public long countByUser(UUID userId) {
        Query query = getEntityManager().createQuery(
                "SELECT COUNT(gp) FROM GpsPointEntity gp WHERE gp.user.id = :userId");
        query.setParameter("userId", userId);
        return (Long) query.getSingleResult();
    }

    /**
     * Count GPS points matching filters.
     *
     * @param userId  The ID of the user
     * @param filters Filter criteria
     * @return Count of GPS points matching the filters
     */
    public long countByUserAndFilters(UUID userId, GpsPointFilterDTO filters) {
        QueryBuilder queryBuilder = buildFilterQuery(userId, filters);

        // Replace SELECT with COUNT
        String countQuery = "SELECT COUNT(gp) FROM GpsPointEntity gp WHERE " + queryBuilder.whereClause;

        Query query = getEntityManager().createQuery(countQuery, Long.class);
        queryBuilder.params.forEach(query::setParameter);

        return (Long) query.getSingleResult();
    }

    /**
     * Stream GPS points for export in batches to avoid OOM.
     * Processes results in chunks and calls consumer for each batch.
     *
     * @param userId    The ID of the user
     * @param filters   Filter criteria
     * @param batchSize Number of records to process at a time
     * @param consumer  Consumer to process each batch
     */
    public void streamByUserAndFilters(UUID userId, GpsPointFilterDTO filters,
                                       int batchSize, Consumer<List<GpsPointEntity>> consumer) {
        Instant cursorTimestamp = null;
        Long cursorId = null;
        int normalizedBatchSize = Math.max(1, batchSize);

        while (true) {
            List<GpsPointEntity> batch = findFilteredExportChunk(
                    userId, filters, cursorTimestamp, cursorId, normalizedBatchSize);
            if (batch.isEmpty()) {
                break;
            }

            consumer.accept(batch);

            GpsPointEntity lastPoint = batch.get(batch.size() - 1);
            cursorTimestamp = lastPoint.getTimestamp();
            cursorId = lastPoint.getId();

            // Clear the persistence context to free memory.
            getEntityManager().clear();
        }
    }

    private List<GpsPointEntity> findFilteredExportChunk(UUID userId, GpsPointFilterDTO filters,
                                                         Instant cursorTimestamp, Long cursorId, int batchSize) {
        QueryBuilder queryBuilder = buildFilterQuery(userId, filters);
        queryBuilder.query.append(" AND gp.timestamp IS NOT NULL");
        if (cursorTimestamp != null && cursorId != null) {
            queryBuilder.query.append(" AND (gp.timestamp > :cursorTimestamp OR (gp.timestamp = :cursorTimestamp AND gp.id > :cursorId))");
            queryBuilder.params.put("cursorTimestamp", cursorTimestamp);
            queryBuilder.params.put("cursorId", cursorId);
        }
        queryBuilder.query.append(" ORDER BY gp.timestamp ASC, gp.id ASC");

        Query query = getEntityManager().createQuery(queryBuilder.query.toString(), GpsPointEntity.class);
        queryBuilder.params.forEach(query::setParameter);
        query.setMaxResults(batchSize);

        return query.getResultList();
    }

    /**
     * Build filter query with WHERE clause and parameters.
     * Helper method to construct dynamic queries based on active filters.
     *
     * @param userId  The ID of the user
     * @param filters Filter criteria
     * @return QueryBuilder containing query string and parameters
     */
    private QueryBuilder buildFilterQuery(UUID userId, GpsPointFilterDTO filters) {
        StringBuilder query = new StringBuilder("SELECT gp FROM GpsPointEntity gp WHERE ");
        StringBuilder whereClause = new StringBuilder();
        Map<String, Object> params = new HashMap<>();

        // Always filter by user
        whereClause.append("gp.user.id = :userId");
        params.put("userId", userId);

        // If ID filtering is active, ignore all other filters and only filter by IDs
        if (filters.hasIdFilter()) {
            whereClause.append(" AND gp.id IN :gpsPointIds");
            params.put("gpsPointIds", filters.getGpsPointIds());
            query.append(whereClause);
            return new QueryBuilder(query, whereClause.toString(), params);
        }

        // Time range filters
        if (filters.getStartTime() != null) {
            whereClause.append(" AND gp.timestamp >= :startTime");
            params.put("startTime", filters.getStartTime());
        }
        if (filters.getEndTime() != null) {
            whereClause.append(" AND gp.timestamp <= :endTime");
            params.put("endTime", filters.getEndTime());
        }

        // Accuracy filters
        if (filters.getAccuracyMin() != null) {
            whereClause.append(" AND gp.accuracy >= :accuracyMin");
            params.put("accuracyMin", filters.getAccuracyMin());
        }
        if (filters.getAccuracyMax() != null) {
            whereClause.append(" AND gp.accuracy <= :accuracyMax");
            params.put("accuracyMax", filters.getAccuracyMax());
        }

        // Speed filters (velocity is stored in km/h)
        if (filters.getSpeedMin() != null) {
            whereClause.append(" AND gp.velocity >= :speedMin");
            params.put("speedMin", filters.getSpeedMin());
        }
        if (filters.getSpeedMax() != null) {
            whereClause.append(" AND gp.velocity <= :speedMax");
            params.put("speedMax", filters.getSpeedMax());
        }

        // Source type filter
        if (filters.getSourceTypes() != null && !filters.getSourceTypes().isEmpty()) {
            whereClause.append(" AND gp.sourceType IN :sourceTypes");
            params.put("sourceTypes", filters.getSourceTypes());
        }

        query.append(whereClause);

        return new QueryBuilder(query, whereClause.toString(), params);
    }

    /**
     * Helper class to encapsulate query building results.
     */
    private static class QueryBuilder {
        final StringBuilder query;
        final String whereClause;
        final Map<String, Object> params;

        QueryBuilder(StringBuilder query, String whereClause, Map<String, Object> params) {
            this.query = query;
            this.whereClause = whereClause;
            this.params = params;
        }
    }
}