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

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

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

public interface NotificationLogRepository extends JpaRepository<NotificationLog, Long> {

  /**
   * Idempotency claim. Native query потому что Spring Data JPA не поддерживает
   * {@code INSERT ... ON CONFLICT DO NOTHING RETURNING} напрямую (PostgreSQL specific).
   *
   * <p>Returns inserted row's id если claim succeeded, или empty если конфликт
   * (другой consumer уже claim'ил, или sweep job не освободил stuck row).
   *
   * <p>Используется в {@code IdempotencyGuard.shouldSend()}.
   */
  @Modifying
  @Query(value = """
      INSERT INTO notification_log (event_id, event_type, draft_id, channel, recipient, status, sent_at)
      VALUES (:eventId, :eventType, :draftId, :channel, :recipient, 'PENDING', NOW())
      ON CONFLICT (event_id, channel, recipient) DO NOTHING
      RETURNING id
      """, nativeQuery = true)
  Optional<Long> tryClaim(
      @Param("eventId") UUID eventId,
      @Param("eventType") String eventType,
      @Param("draftId") UUID draftId,
      @Param("channel") String channel,
      @Param("recipient") String recipient);

  /**
   * Pending sweep — освобождает stuck PENDING rows старше {@code threshold}.
   * Returns deleted count для metrics. Использует partial index
   * {@code idx_notif_log_pending_sweep}.
   */
  @Modifying
  @Query(value = """
      DELETE FROM notification_log
      WHERE status = 'PENDING' AND sent_at < :threshold
      """, nativeQuery = true)
  int deletePendingOlderThan(@Param("threshold") OffsetDateTime threshold);

  /**
   * Retention sweep — удаляет terminal status rows старше {@code threshold}.
   * FAILED rows preserved bessrochno (для post-mortem). Partial index
   * {@code idx_notif_log_retention}.
   */
  @Modifying
  @Query(value = """
      DELETE FROM notification_log
      WHERE status IN ('SENT','RATE_LIMITED','SKIPPED_PREF','SKIPPED_TOO_OLD','UNSUBSCRIBED')
        AND sent_at < :threshold
      """, nativeQuery = true)
  int deleteRetainedOlderThan(@Param("threshold") OffsetDateTime threshold);

  /**
   * UI "Last sent" lookup для {@code GET /api/v1/me/notifications}.
   * Cache 60s в service layer.
   */
  @Query("""
      SELECT MAX(n.sentAt) FROM NotificationLog n
      WHERE n.recipient = :recipient
        AND n.channel = :channel
        AND n.status = cloud.nstart.terravault.ordinis.domain.notification.NotificationLog.Status.SENT
      """)
  Optional<OffsetDateTime> findLastSentAt(
      @Param("recipient") String recipient,
      @Param("channel") String channel);

  /** Admin endpoint pagination. */
  Page<NotificationLog> findByRecipientOrderBySentAtDesc(String recipient, Pageable pageable);

  /** Admin endpoint — full audit lookup by event. */
  Page<NotificationLog> findByEventIdOrderBySentAtDesc(UUID eventId, Pageable pageable);
}
