package ru.mge.estimate.repository

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.Query
import org.springframework.data.repository.query.Param
import org.springframework.stereotype.Repository
import ru.mge.estimate.model.local.LocalSection
import java.util.UUID

@Repository
interface LocalSectionRepository : JpaRepository<LocalSection, UUID> {
    @Query("""
       WITH filtered_sections AS (
            SELECT s.* FROM local_section s
            JOIN local_context lc ON s.local_context_id = lc.id
            LEFT JOIN local_position pTsn ON pTsn.local_section_id = s.id AND pTsn.errors_count_tsn > 0
            LEFT JOIN local_position pCalc ON pCalc.local_section_id = s.id AND pCalc.errors_count_calculation > 0
            WHERE lc.estimate_id = :estimateId
                AND (
                    (:tsn = false AND :calculations = false)
                    OR
                    (:tsn = true AND :calculations = true AND 
                        (s.errors_count > 0 OR pTsn.id IS NOT NULL OR pCalc.id IS NOT NULL))
                    OR
                    (:tsn = true AND :calculations = false AND pTsn.id IS NOT NULL)
                    OR
                    (:tsn = false AND :calculations = true AND pCalc.id IS NOT NULL)
                )
                AND CASE
                    WHEN :search IS NULL THEN TRUE
                    ELSE (
                        EXISTS (
                            SELECT 1 
                            FROM jsonb_array_elements(pTsn.json_position_data->'tsn') AS t(item)
                            WHERE (item->'code'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'prefix'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'name'->>'value') ILIKE '%' || :search || '%'
                        )
                        OR EXISTS (
                            SELECT 1 
                            FROM jsonb_array_elements(pCalc.json_position_data->'tsn') AS t(item)
                            WHERE (item->'code'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'prefix'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'name'->>'value') ILIKE '%' || :search || '%'
                        )
                    )
                END
            GROUP BY s.id
        )
        SELECT * FROM filtered_sections
        ORDER BY (json_section_data->'code'->>'value')::numeric ASC
    """,
        countQuery = """
            SELECT COUNT(DISTINCT s.id) FROM local_section s
            JOIN local_context lc ON s.local_context_id = lc.id
            LEFT JOIN local_position pTsn ON pTsn.local_section_id = s.id AND pTsn.errors_count_tsn > 0
            LEFT JOIN local_position pCalc ON pCalc.local_section_id = s.id AND pCalc.errors_count_calculation > 0
            WHERE lc.estimate_id = :estimateId
                AND (
                    (:tsn = false AND :calculations = false)
                    OR
                    (:tsn = true AND :calculations = true AND 
                        (s.errors_count > 0 OR pTsn.id IS NOT NULL OR pCalc.id IS NOT NULL))
                    OR
                    (:tsn = true AND :calculations = false AND pTsn.id IS NOT NULL)
                    OR
                    (:tsn = false AND :calculations = true AND pCalc.id IS NOT NULL)
                )
                AND CASE
                    WHEN :search IS NULL THEN TRUE
                    ELSE (
                        EXISTS (
                            SELECT 1 
                            FROM jsonb_array_elements(pTsn.json_position_data->'tsn') AS t(item)
                            WHERE (item->'code'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'prefix'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'name'->>'value') ILIKE '%' || :search || '%'
                        )
                        OR EXISTS (
                            SELECT 1 
                            FROM jsonb_array_elements(pCalc.json_position_data->'tsn') AS t(item)
                            WHERE (item->'code'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'prefix'->>'value') ILIKE '%' || :search || '%'
                               OR (item->'name'->>'value') ILIKE '%' || :search || '%'
                        )
                    )
                END
            """,
        nativeQuery = true)
    fun findSortedSections(
        @Param("estimateId") estimateId: UUID,
        @Param("tsn") tsn: Boolean,
        @Param("calculations") calculations: Boolean,
        @Param("search") search: String?,
        pageable: Pageable
    ): Page<LocalSection>
}

package ru.mge.estimate.repository

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.Query
import org.springframework.data.repository.query.Param
import org.springframework.stereotype.Repository
import ru.mge.estimate.model.local.LocalPosition
import java.util.*

@Repository
interface LocalPositionRepository : JpaRepository<LocalPosition, UUID> {
    @Query(
        value = """
            SELECT p.* FROM local_position p
            JOIN local_section s ON p.local_section_id = s.id
            JOIN local_context lc ON s.local_context_id = lc.id
            WHERE lc.estimate_id = :estimateId AND s.id = :sectionId
              AND (
                    (:tsn = false AND :calculations = false)
                    OR (:tsn = true AND p.errors_count_tsn > 0)
                    OR (:calculations = true AND p.errors_count_calculation > 0)
                    OR (:tsn = true AND :calculations = true AND p.errors_count_tsn > 0 AND p.errors_count_calculation > 0)
                  )
              AND (
                    :search IS NULL
                    OR EXISTS (
                        SELECT 1 FROM jsonb_array_elements(p.json_position_data->'tsn') AS t(item)
                        WHERE item->>'code' ILIKE CONCAT('%', :search, '%')
                           OR item->>'prefix' ILIKE CONCAT('%', :search, '%')
                           OR item->>'name' ILIKE CONCAT('%', :search, '%')
                    )
                  )
        """,
        countQuery = """
            SELECT COUNT(*) FROM local_position p
            JOIN local_section s ON p.local_section_id = s.id
            JOIN local_context lc ON s.local_context_id = lc.id
            WHERE lc.estimate_id = :estimateId AND s.id = :sectionId
              AND (
                    (:tsn = false AND :calculations = false)
                    OR (:tsn = true AND p.errors_count_tsn > 0)
                    OR (:calculations = true AND p.errors_count_calculation > 0)
                    OR (:tsn = true AND :calculations = true AND p.errors_count_tsn > 0 AND p.errors_count_calculation > 0)
                  )
              AND (
                    :search IS NULL
                    OR EXISTS (
                        SELECT 1 FROM jsonb_array_elements(p.json_position_data->'tsn') AS t(item)
                        WHERE item->>'code' ILIKE CONCAT('%', :search, '%')
                           OR item->>'prefix' ILIKE CONCAT('%', :search, '%')
                           OR item->>'name' ILIKE CONCAT('%', :search, '%')
                    )
              )
        """,
        nativeQuery = true
    )
    fun findAllByEstimateIdAndSectionId(
        @Param("estimateId") estimateId: UUID,
        @Param("sectionId") sectionId: UUID,
        @Param("tsn") tsn: Boolean,
        @Param("calculations") calculations: Boolean,
        @Param("search") search: String?,
        pageable: Pageable
    ): Page<LocalPosition>
}

Чувствительны ли эти запросы к регистру? Допускают все символы