-- =============================================================================
-- Migration: 002_korean_fts.sql
-- Description: pg_trgm 기반 한국어 전문 검색 고도화
-- 참고: 001_pgvector_setup.sql의 'simple' FTS를 trigram 유사도 검색으로 보완
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. pg_trgm 확장 활성화
-- -----------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- -----------------------------------------------------------------------------
-- 2. GIN trigram 인덱스 추가 (knowledge_chunks.content)
--    기존 idx_chunks_content_fts (tsvector 기반)는 유지하되,
--    trigram 인덱스를 추가로 생성하여 한국어 부분 매칭을 지원한다.
--    예: "보험" → "보험설계사", "보험료" 등 매칭 가능
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_chunks_content_trgm
    ON knowledge_chunks
    USING gin (content gin_trgm_ops);

-- -----------------------------------------------------------------------------
-- 3. trigram 유사도 임계값 설정
--    기본값 0.3에서 0.1로 낮춰 한국어 짧은 단어도 매칭되도록 함
--    (한국어는 영문 대비 trigram 매칭률이 낮으므로 임계값 하향 필요)
-- -----------------------------------------------------------------------------
SELECT set_limit(0.1);

-- -----------------------------------------------------------------------------
-- 4. hybrid_search() 함수 v2 — trigram 유사도 기반 키워드 검색 추가
--    기존 tsvector 기반 키워드 검색을 trigram 유사도로 교체
--    ILIKE fallback도 포함하여 정확한 부분 문자열 매칭 보장
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION hybrid_search(
    query_text      TEXT,
    query_embedding VECTOR(1536),
    match_count     INT     DEFAULT 10,
    semantic_weight FLOAT   DEFAULT 0.7,
    keyword_weight  FLOAT   DEFAULT 0.3,
    source_filter   TEXT    DEFAULT NULL
)
RETURNS TABLE (
    chunk_id       UUID,
    document_id    UUID,
    content        TEXT,
    similarity     FLOAT,
    combined_score FLOAT,
    source         TEXT,
    title          TEXT
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    WITH

    -- -------------------------------------------------------------------------
    -- 시맨틱 검색: 코사인 거리 기반 상위 청크 추출 (변경 없음)
    -- -------------------------------------------------------------------------
    semantic_results AS (
        SELECT
            kc.id                                      AS chunk_id,
            kc.document_id,
            kc.content,
            (1.0 - (kc.embedding <=> query_embedding)) AS semantic_score
        FROM knowledge_chunks kc
        JOIN knowledge_documents kd ON kd.id = kc.document_id
        WHERE
            kc.embedding IS NOT NULL
            AND (source_filter IS NULL OR kd.source = source_filter)
        ORDER BY kc.embedding <=> query_embedding
        LIMIT match_count * 2
    ),

    -- -------------------------------------------------------------------------
    -- 키워드 검색 v2: trigram 유사도 + ILIKE fallback
    --   1순위: pg_trgm similarity() 기반 유사도 검색
    --   2순위: ILIKE '%query%' 부분 문자열 매칭 (trigram 미스 시 fallback)
    --   최종 점수: GREATEST(similarity, ilike_match) — 두 방법 중 높은 점수 사용
    -- -------------------------------------------------------------------------
    keyword_results AS (
        SELECT
            kc.id                    AS chunk_id,
            kc.document_id,
            kc.content,
            GREATEST(
                similarity(kc.content, query_text),
                CASE WHEN kc.content ILIKE '%' || query_text || '%' THEN 0.5 ELSE 0.0 END
            )                        AS keyword_score
        FROM knowledge_chunks kc
        JOIN knowledge_documents kd ON kd.id = kc.document_id
        WHERE
            (
                kc.content % query_text                          -- trigram 유사도 매칭
                OR kc.content ILIKE '%' || query_text || '%'     -- ILIKE fallback
            )
            AND (source_filter IS NULL OR kd.source = source_filter)
        ORDER BY keyword_score DESC
        LIMIT match_count * 2
    ),

    -- -------------------------------------------------------------------------
    -- FULL OUTER JOIN (변경 없음)
    -- -------------------------------------------------------------------------
    combined AS (
        SELECT
            COALESCE(s.chunk_id,    k.chunk_id)    AS chunk_id,
            COALESCE(s.document_id, k.document_id) AS document_id,
            COALESCE(s.content,     k.content)     AS content,
            COALESCE(s.semantic_score, 0.0)        AS semantic_score,
            COALESCE(k.keyword_score,  0.0)        AS keyword_score
        FROM semantic_results s
        FULL OUTER JOIN keyword_results k
            ON s.chunk_id = k.chunk_id
    )

    SELECT
        c.chunk_id,
        c.document_id,
        c.content,
        c.semantic_score                                                        AS similarity,
        (semantic_weight * c.semantic_score + keyword_weight * c.keyword_score) AS combined_score,
        kd.source,
        kd.title
    FROM combined c
    JOIN knowledge_documents kd ON kd.id = c.document_id
    ORDER BY combined_score DESC
    LIMIT match_count;
END;
$$;

COMMENT ON FUNCTION hybrid_search IS
    'v2: trigram 유사도 기반 한국어 키워드 검색 지원. '
    '기존 tsvector FTS 대신 pg_trgm similarity() + ILIKE fallback 사용. '
    'semantic_weight + keyword_weight = 1.0 권장.';
