package cloud.nstart.terravault.ordinis.domain.record;

import cloud.nstart.terravault.ordinis.domain.DataScope;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.OffsetDateTime;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

public interface DictionaryRecordRepository extends JpaRepository<DictionaryRecord, UUID> {

  /**
   * Все версии записи по бизнес-ключу (включая прошлые и будущие). Sorted by valid_from desc.
   */
  List<DictionaryRecord> findByDictionaryIdAndBusinessKeyOrderByValidFromDesc(
      UUID dictionaryId, String businessKey);

  /**
   * Активная версия в момент {@code at}. Использует EXCLUSION CONSTRAINT — гарантировано не больше одной.
   */
  @Query("""
      SELECT r FROM DictionaryRecord r
      WHERE r.dictionaryId = :dictionaryId
        AND r.businessKey = :businessKey
        AND r.validFrom <= :at
        AND r.validTo > :at
      """)
  Optional<DictionaryRecord> findActiveAt(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("businessKey") String businessKey,
      @Param("at") OffsetDateTime at);

  /**
   * Все активные записи в справочнике в момент {@code at}, отфильтрованные по доступным scope'ам
   * для consumer'а. Используется read-api после locale negotiation.
   */
  @Query("""
      SELECT r FROM DictionaryRecord r
      WHERE r.dictionaryId = :dictionaryId
        AND r.dataScope IN :allowedScopes
        AND r.validFrom <= :at
        AND r.validTo > :at
      """)
  List<DictionaryRecord> findActiveByDictionaryAndScopes(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("allowedScopes") List<DataScope> allowedScopes,
      @Param("at") OffsetDateTime at);

  /**
   * Поиск активных записей в момент {@code at} по значению поля внутри JSONB data.
   * Используется для x-unique валидации: ищем записи с тем же значением, исключая
   * текущую версию (если update).
   */
  @Query(value = """
      SELECT * FROM dictionary_records r
      WHERE r.dictionary_id = :dictionaryId
        AND r.valid_from <= :at
        AND r.valid_to > :at
        AND r.data ->> :fieldName = :fieldValue
      """, nativeQuery = true)
  List<DictionaryRecord> findActiveByJsonField(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("at") OffsetDateTime at,
      @Param("fieldName") String fieldName,
      @Param("fieldValue") String fieldValue);

  /**
   * Активные записи (validFrom <= now < validTo), сгруппированные по словарю и
   * отфильтрованные по доступным consumer'у scope'ам. Один запрос на весь список
   * справочников — без N+1.
   *
   * @return массивы [dictionaryId UUID, count Long]
   */
  @Query("""
      SELECT r.dictionaryId, COUNT(r) FROM DictionaryRecord r
      WHERE r.dataScope IN :allowedScopes
        AND r.validFrom <= :at
        AND r.validTo > :at
      GROUP BY r.dictionaryId
      """)
  List<Object[]> countActiveGroupedByDictionary(
      @Param("allowedScopes") Collection<DataScope> allowedScopes,
      @Param("at") OffsetDateTime at);

  /**
   * Spatial filter: активные записи которые intersect bbox (SRID 4326).
   * Используется read-api endpoint {@code GET /api/v1/{dict}/records?bbox=...}.
   * PostGIS {@code ST_Intersects} использует GiST index на geometry колонке —
   * O(log n) lookup, не sequential scan.
   *
   * <p>Native query (не JPA) потому что hibernate-spatial 7.x не имеет clean
   * mapping для {@code ST_MakeEnvelope}. Native + ST_MakeEnvelope экономит
   * один parse vs передачи WKT строки.
   *
   * <p>{@code data_scope IN (...)} нативно требует постгресовский
   * ANY(string_array) — но у нас scopes небольшой enum, ставим IN-list через
   * JPA String parameter.
   */
  @Query(value = """
      SELECT * FROM dictionary_records r
      WHERE r.dictionary_id = :dictionaryId
        AND r.data_scope = ANY(CAST(:scopesCsv AS TEXT[]))
        AND r.valid_from <= :at
        AND r.valid_to > :at
        AND r.geometry IS NOT NULL
        AND ST_Intersects(
              r.geometry,
              ST_MakeEnvelope(:west, :south, :east, :north, 4326))
      """, nativeQuery = true)
  List<DictionaryRecord> findActiveByBbox(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("scopesCsv") String[] scopesCsv,
      @Param("at") OffsetDateTime at,
      @Param("west") double west,
      @Param("south") double south,
      @Param("east") double east,
      @Param("north") double north);

  /**
   * Spatial filter по произвольному polygon в формате GeoJSON. PostGIS
   * {@code ST_GeomFromGeoJSON} парсит на DB-side, ST_SetSRID вешает 4326
   * (GeoJSON по spec не имеет SRID, нужен explicit cast). GiST index
   * на geometry колонке используется для ST_Intersects.
   *
   * <p>Если GeoJSON malformed, Postgres вернёт SQL error → Hibernate
   * выбросит DataAccessException → перехватывается в exception handler
   * как 400 Bad Request.
   */
  @Query(value = """
      SELECT * FROM dictionary_records r
      WHERE r.dictionary_id = :dictionaryId
        AND r.data_scope = ANY(CAST(:scopesCsv AS TEXT[]))
        AND r.valid_from <= :at
        AND r.valid_to > :at
        AND r.geometry IS NOT NULL
        AND ST_Intersects(
              r.geometry,
              ST_SetSRID(ST_GeomFromGeoJSON(:polygonGeoJson), 4326))
      """, nativeQuery = true)
  List<DictionaryRecord> findActiveByPolygonGeoJson(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("scopesCsv") String[] scopesCsv,
      @Param("at") OffsetDateTime at,
      @Param("polygonGeoJson") String polygonGeoJson);

  /**
   * Bucketed list snapshot points: timestamps когда records появлялись
   * (validFrom) в заданном time window. Используется для TimeTravel
   * slider marks на admin-UI — каждая mark = "interesting" moment где
   * минимум одна запись changed.
   *
   * <p>Granularity: PostgreSQL date_trunc unit ({@code hour}, {@code day},
   * {@code week}). Возвращает Object[] = {bucket OffsetDateTime,
   * changedSinceLast Long}. Caller'а потом мапит на DTO.
   *
   * @param scopes — CSV array (passed как text[] для unnest in WHERE), скоупы
   *                которые caller видит. EMPTY → no records visible (caller
   *                anonymous и scope filter empty).
   */
  @Query(value = """
      SELECT date_trunc(:granularity, valid_from)::timestamptz AS bucket,
             count(*) AS changed_since_last
      FROM dictionary_records
      WHERE dictionary_id = :dictionaryId
        AND data_scope = ANY (string_to_array(:scopesCsv, ','))
        AND valid_from >= :from
        AND valid_from < :to
      GROUP BY bucket
      ORDER BY bucket DESC
      """, nativeQuery = true)
  List<Object[]> findSnapshotBuckets(
      @Param("dictionaryId") UUID dictionaryId,
      @Param("scopesCsv") String scopesCsv,
      @Param("from") OffsetDateTime from,
      @Param("to") OffsetDateTime to,
      @Param("granularity") String granularity);
}
