package com.applemand.zmanim.core.location

import androidx.room.Dao
import androidx.room.Query

@Dao
interface CityDao {

    /**
     * FTS prefix match ranked by population.  Suffix `*` enables prefix matching.
     * FTS4 `MATCH` supports phrase queries; we normalize to lower-case and append `*`
     * in [LocationSearchQuery] before calling.
     */
    @Query(
        """
        SELECT c.* FROM cities c
        JOIN cities_fts f ON c.id = f.rowid
        WHERE cities_fts MATCH :query
        ORDER BY c.population DESC
        LIMIT :limit
        """
    )
    suspend fun searchByName(query: String, limit: Int = 50): List<CityEntity>

    /**
     * Returns the N cities closest to the given coordinate using a cheap bounding-box
     * prefilter (±1° lat/lng) followed by a haversine sort.  Cities DB is indexed by
     * `lat` for the prefilter; full-table scan is avoided even without a spatial index.
     */
    @Query(
        """
        SELECT * FROM cities
        WHERE lat BETWEEN :lat - 1.0 AND :lat + 1.0
          AND lng BETWEEN :lng - 1.5 AND :lng + 1.5
        """
    )
    suspend fun candidatesNear(lat: Double, lng: Double): List<CityEntity>

    @Query("SELECT * FROM cities WHERE id = :id LIMIT 1")
    suspend fun byId(id: Long): CityEntity?

    @Query("SELECT * FROM cities ORDER BY population DESC LIMIT :limit")
    suspend fun topPopulous(limit: Int = 200): List<CityEntity>
}
