package at.procon.ted.repository;

import at.procon.ted.model.entity.*;
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.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

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

/**
 * Repository for ProcurementDocument entities.
 * Provides standard CRUD operations plus custom queries for search and statistics.
 * 
 * @author Martin.Schweitzer@procon.co.at and claude.ai
 */
@Repository
public interface ProcurementDocumentRepository extends 
        JpaRepository<ProcurementDocument, UUID>, 
        JpaSpecificationExecutor<ProcurementDocument> {

    /**
     * Find document by its SHA-256 hash (for idempotent processing).
     */
    Optional<ProcurementDocument> findByDocumentHash(String documentHash);

    /**
     * Check if a document with the given hash already exists.
     */
    boolean existsByDocumentHash(String documentHash);

    /**
     * Find document by TED publication ID.
     */
    Optional<ProcurementDocument> findByPublicationId(String publicationId);

    /**
     * Find document by TED notice URL.
     * Example: https://ted.europa.eu/en/notice/-/detail/786665-2025
     */
    Optional<ProcurementDocument> findByNoticeUrl(String noticeUrl);

    /**
     * Find documents by buyer country code.
     */
    Page<ProcurementDocument> findByBuyerCountryCode(String countryCode, Pageable pageable);

    /**
     * Find documents by notice type.
     */
    Page<ProcurementDocument> findByNoticeType(NoticeType noticeType, Pageable pageable);

    /**
     * Find documents pending vectorization (ordered by creation date).
     */
    @Query("SELECT d FROM ProcurementDocument d WHERE d.vectorizationStatus = :status ORDER BY d.createdAt ASC")
    List<ProcurementDocument> findByVectorizationStatus(
            @Param("status") VectorizationStatus status,
            Pageable pageable);

    /**
     * Get only text content for vectorization (memory efficient - does not load XML).
     */
    @Query("SELECT p.textContent FROM ProcurementDocument p WHERE p.id = :id")
    String findTextContentById(@Param("id") UUID id);

    /**
     * Find document IDs by vectorization status (memory efficient - does not load full entities).
     */
    @Query("SELECT d.id FROM ProcurementDocument d WHERE d.vectorizationStatus = :status ORDER BY d.createdAt ASC")
    List<UUID> findIdsByVectorizationStatus(@Param("status") VectorizationStatus status, Pageable pageable);

    /**
     * Update vectorization status for a document.
     */
    @Modifying
    @Query("UPDATE ProcurementDocument d SET d.vectorizationStatus = :status, " +
           "d.vectorizationError = :error, d.vectorizedAt = :vectorizedAt " +
           "WHERE d.id = :id")
    int updateVectorizationStatus(
            @Param("id") UUID id,
            @Param("status") VectorizationStatus status,
            @Param("error") String error,
            @Param("vectorizedAt") OffsetDateTime vectorizedAt);

    /**
     * Update document vector after successful vectorization.
     */
    @Modifying
    @Query(value = "UPDATE ted.procurement_document SET content_vector = CAST(:vectorData AS vector), " +
                   "vectorization_status = 'COMPLETED', vectorized_at = CURRENT_TIMESTAMP, " +
                   "vectorization_error = NULL, embedding_token_count = :tokenCount WHERE id = :id",
           nativeQuery = true)
    int updateContentVector(@Param("id") UUID id, @Param("vectorData") String vectorData, @Param("tokenCount") Integer tokenCount);

    /**
     * Simple semantic search using cosine similarity without filters.
     * Returns document IDs and similarity scores sorted by similarity.
     * Note: We only select id and similarity to avoid XML column deserialization issues.
     */
    @Query(value = """
            SELECT d.id, 1 - (d.content_vector <=> CAST(:queryVector AS vector)) AS similarity
            FROM ted.procurement_document d
            WHERE d.content_vector IS NOT NULL
            AND (1 - (d.content_vector <=> CAST(:queryVector AS vector))) >= :threshold
            ORDER BY similarity DESC
            LIMIT :limit
            """, nativeQuery = true)
    List<Object[]> findBySimilarity(
            @Param("queryVector") String queryVector,
            @Param("threshold") double threshold,
            @Param("limit") int limit);

    /**
     * Semantic search using cosine similarity with filters.
     * Returns documents sorted by similarity score.
     */
    @Query(value = """
            SELECT d.*, 1 - (d.content_vector <=> CAST(:queryVector AS vector)) AS similarity
            FROM ted.procurement_document d
            WHERE d.content_vector IS NOT NULL
            AND (1 - (d.content_vector <=> CAST(:queryVector AS vector))) >= :threshold
            AND (:countryCode IS NULL OR d.buyer_country_code = :countryCode)
            AND (:noticeType IS NULL OR d.notice_type = :noticeType)
            AND (:contractNature IS NULL OR d.contract_nature = :contractNature)
            AND (:cpvPrefix IS NULL OR EXISTS (
                SELECT 1 FROM unnest(d.cpv_codes) code WHERE code LIKE :cpvPrefix || '%'
            ))
            AND (CAST(:dateFrom AS DATE) IS NULL OR d.publication_date >= CAST(:dateFrom AS DATE))
            AND (CAST(:dateTo AS DATE) IS NULL OR d.publication_date <= CAST(:dateTo AS DATE))
            ORDER BY similarity DESC
            LIMIT :limit OFFSET :offset
            """, nativeQuery = true)
    List<Object[]> findBySemanticSearch(
            @Param("queryVector") String queryVector,
            @Param("threshold") double threshold,
            @Param("countryCode") String countryCode,
            @Param("noticeType") String noticeType,
            @Param("contractNature") String contractNature,
            @Param("cpvPrefix") String cpvPrefix,
            @Param("dateFrom") LocalDate dateFrom,
            @Param("dateTo") LocalDate dateTo,
            @Param("limit") int limit,
            @Param("offset") int offset);

    /**
     * Count total documents for semantic search (for pagination).
     */
    @Query(value = """
            SELECT COUNT(*)
            FROM ted.procurement_document d
            WHERE d.content_vector IS NOT NULL
            AND (1 - (d.content_vector <=> CAST(:queryVector AS vector))) >= :threshold
            AND (:countryCode IS NULL OR d.buyer_country_code = :countryCode)
            AND (:noticeType IS NULL OR d.notice_type = :noticeType)
            AND (:contractNature IS NULL OR d.contract_nature = :contractNature)
            """, nativeQuery = true)
    long countBySemanticSearch(
            @Param("queryVector") String queryVector,
            @Param("threshold") double threshold,
            @Param("countryCode") String countryCode,
            @Param("noticeType") String noticeType,
            @Param("contractNature") String contractNature);

    /**
     * Get document count by country.
     */
    @Query("SELECT d.buyerCountryCode, COUNT(d) FROM ProcurementDocument d " +
           "WHERE d.buyerCountryCode IS NOT NULL " +
           "GROUP BY d.buyerCountryCode ORDER BY COUNT(d) DESC")
    List<Object[]> countByCountry();

    /**
     * Get document count by notice type.
     */
    @Query("SELECT d.noticeType, COUNT(d) FROM ProcurementDocument d " +
           "GROUP BY d.noticeType ORDER BY COUNT(d) DESC")
    List<Object[]> countByNoticeType();

    /**
     * Get vectorization statistics.
     */
    @Query("SELECT d.vectorizationStatus, COUNT(d) FROM ProcurementDocument d " +
           "GROUP BY d.vectorizationStatus")
    List<Object[]> countByVectorizationStatus();

    /**
     * Find documents with submission deadline in the future.
     */
    @Query("SELECT d FROM ProcurementDocument d " +
           "WHERE d.submissionDeadline > CURRENT_TIMESTAMP " +
           "ORDER BY d.submissionDeadline ASC")
    Page<ProcurementDocument> findUpcomingDeadlines(Pageable pageable);

    /**
     * Full-text search on text content using PostgreSQL trigram matching.
     */
    @Query(value = "SELECT * FROM ted.procurement_document d " +
                   "WHERE d.text_content ILIKE '%' || :query || '%' " +
                   "ORDER BY similarity(d.text_content, :query) DESC",
           nativeQuery = true)
    List<ProcurementDocument> findByTextContentContaining(@Param("query") String query, Pageable pageable);


    /**
     * First lightweight cursor query for resumable legacy -> DOC/projection backfill.
     */
    @Query(value = """
            SELECT p.id AS id, p.created_at AS createdAt
            FROM ted.procurement_document p
            ORDER BY p.created_at ASC, p.id ASC
            LIMIT :limit
            """, nativeQuery = true)
    List<LegacyTedMigrationCursor> findFirstMigrationBatch(@Param("limit") int limit);

    /**
     * Next lightweight cursor query for resumable legacy -> DOC/projection backfill.
     */
    @Query(value = """
            SELECT p.id AS id, p.created_at AS createdAt
            FROM ted.procurement_document p
            WHERE p.created_at > :lastCreatedAt
               OR (p.created_at = :lastCreatedAt AND p.id > :lastId)
            ORDER BY p.created_at ASC, p.id ASC
            LIMIT :limit
            """, nativeQuery = true)
    List<LegacyTedMigrationCursor> findNextMigrationBatch(@Param("lastCreatedAt") OffsetDateTime lastCreatedAt,
                                                          @Param("lastId") UUID lastId,
                                                          @Param("limit") int limit);

    @Query("SELECT p FROM ProcurementDocument p WHERE p.id IN :ids")
    List<ProcurementDocument> findAllByIdInForMigration(@Param("ids") Collection<UUID> ids);

    @Query("SELECT l FROM ProcurementLot l WHERE l.document.id IN :ids")
    List<ProcurementLot> findLotsByDocumentIdInForMigration(@Param("ids") Collection<UUID> ids);

    @Query("SELECT o FROM Organization o WHERE o.document.id IN :ids")
    List<Organization> findOrganizationsByDocumentIdInForMigration(@Param("ids") Collection<UUID> ids);

    @Query(value = """
            SELECT p.id AS id,
                   CAST(p.content_vector AS text) AS vectorText,
                   p.embedding_token_count AS tokenCount
            FROM ted.procurement_document p
            WHERE p.id IN :ids
              AND p.content_vector IS NOT NULL
            """, nativeQuery = true)
    List<LegacyTedEmbeddingSnapshot> findEmbeddingSnapshotsByIdsForMigration(@Param("ids") Collection<UUID> ids);


    /**
     * Delete all documents created before the specified date.
     * Cascading deletes will automatically remove related lots, organizations, and logs.
     *
     * @param cutoffDate Documents created before this date will be deleted
     * @return Number of deleted documents
     */
    @Modifying
    @Query(value = "DELETE FROM ted.procurement_document WHERE created_at < :cutoffDate", nativeQuery = true)
    int deleteByCreatedAtBefore(@Param("cutoffDate") OffsetDateTime cutoffDate);

    /**
     * Count documents created before the specified date.
     *
     * @param cutoffDate Documents created before this date will be counted
     * @return Number of documents
     */
    long countByCreatedAtBefore(OffsetDateTime cutoffDate);

    @Query(value = """
        SELECT p.id AS id,
               CAST(p.content_vector AS text) AS vectorText,
               p.embedding_token_count AS tokenCount
        FROM ted.procurement_document p
        WHERE p.id = :id
          AND p.content_vector IS NOT NULL
        """, nativeQuery = true)
    Optional<LegacyTedEmbeddingSnapshot> findEmbeddingSnapshotByIdForMigration(@Param("id") UUID id);
}