-- =============================================================================
-- Migration: 001_pgvector_setup.sql
-- Description: pgvector 확장 및 지식 베이스 테이블/인덱스/함수 설정
-- =============================================================================

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


-- -----------------------------------------------------------------------------
-- 2. knowledge_documents 테이블
--    원본 문서를 저장하는 테이블. content_hash로 중복 인제스션을 방지한다.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS knowledge_documents (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    title        TEXT        NOT NULL,
    content      TEXT        NOT NULL,
    content_hash TEXT        UNIQUE,                  -- SHA-256 등 해시값, 중복 인제스션 방지
    source       TEXT        NOT NULL,                -- 예: 'insuwiki', 'insuro_fcpa'
    source_url   TEXT,
    metadata     JSONB       DEFAULT '{}',
    created_at   TIMESTAMPTZ DEFAULT now(),
    updated_at   TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE  knowledge_documents              IS '원본 지식 문서 저장 테이블';
COMMENT ON COLUMN knowledge_documents.content_hash IS 'SHA-256 등 해시값. 동일 문서 중복 인제스션 방지용';
COMMENT ON COLUMN knowledge_documents.source       IS '문서 출처 식별자 (예: insuwiki, insuro_fcpa)';


-- -----------------------------------------------------------------------------
-- 3. knowledge_chunks 테이블
--    문서를 청크 단위로 분할하여 임베딩 벡터와 함께 저장하는 테이블.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS knowledge_chunks (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID        REFERENCES knowledge_documents(id) ON DELETE CASCADE,
    chunk_index INT         NOT NULL,
    content     TEXT        NOT NULL,
    embedding   VECTOR(1536),                         -- OpenAI text-embedding-3-small 기준 1536차원
    token_count INT,
    metadata    JSONB       DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT now(),

    UNIQUE (document_id, chunk_index)                 -- 같은 문서 내 동일 청크 인덱스 중복 방지
);

COMMENT ON TABLE  knowledge_chunks              IS '문서 청크 및 임베딩 벡터 저장 테이블';
COMMENT ON COLUMN knowledge_chunks.embedding    IS 'OpenAI text-embedding-3-small 기준 1536차원 벡터';
COMMENT ON COLUMN knowledge_chunks.chunk_index  IS '문서 내 청크 순서 (0-based)';
COMMENT ON COLUMN knowledge_chunks.token_count  IS '청크의 토큰 수 (모델별 토크나이저 기준)';


-- -----------------------------------------------------------------------------
-- 4. IVFFlat 벡터 인덱스 (시맨틱 검색 성능)
--    코사인 유사도 기반. lists=100은 ~100만 건 이하 데이터에 적합한 기본값.
--    데이터 규모에 따라 lists 값 조정 권장: rows / 1000 ~ sqrt(rows)
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_chunks_embedding_ivfflat
    ON knowledge_chunks
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);


-- -----------------------------------------------------------------------------
-- 5. GIN 전문 검색 인덱스 (하이브리드 검색용)
--    Supabase 기본 환경에서는 'korean' 텍스트 검색 구성이 없을 수 있으므로
--    'simple' 구성을 기본으로 사용한다.
--
--    만약 한국어 형태소 분석이 필요하다면:
--      1) pg_bigm 확장 설치 후 GIN 인덱스를 bigm으로 교체하거나,
--      2) 커스텀 text search configuration을 생성하여 'korean' 대신 사용.
--    예시:
--      CREATE INDEX idx_chunks_content_fts
--          ON knowledge_chunks USING gin (to_tsvector('korean', content));
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_chunks_content_fts
    ON knowledge_chunks
    USING gin (to_tsvector('simple', content));


-- -----------------------------------------------------------------------------
-- 6. 소스별 필터링 인덱스
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_docs_source
    ON knowledge_documents (source);


-- -----------------------------------------------------------------------------
-- 7. updated_at 자동 갱신 트리거 (knowledge_documents)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;

-- 트리거가 이미 존재하면 재생성하지 않도록 DROP 후 CREATE
DROP TRIGGER IF EXISTS trg_documents_updated_at ON knowledge_documents;

CREATE TRIGGER trg_documents_updated_at
    BEFORE UPDATE ON knowledge_documents
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();


-- -----------------------------------------------------------------------------
-- 8. hybrid_search() 함수
--    시맨틱(벡터) 검색과 키워드(전문) 검색을 결합한 하이브리드 검색 함수.
--
--    파라미터:
--      query_text      : 키워드 검색에 사용할 원문 쿼리
--      query_embedding : 시맨틱 검색에 사용할 쿼리 임베딩 벡터
--      match_count     : 반환할 최대 결과 수 (기본 10)
--      semantic_weight : 시맨틱 점수 가중치 (기본 0.7)
--      keyword_weight  : 키워드 점수 가중치 (기본 0.3)
--      source_filter   : 특정 소스만 필터링 (NULL이면 전체 검색)
--
--    점수 계산:
--      semantic_score  = 1 - (embedding <=> query_embedding)   -- 코사인 유사도
--      keyword_score   = ts_rank(tsvector, tsquery)            -- BM25 계열 랭크
--      combined_score  = semantic_weight * semantic_score
--                      + keyword_weight  * keyword_score
-- -----------------------------------------------------------------------------
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

    -- -------------------------------------------------------------------------
    -- 시맨틱 검색: 코사인 거리 기반 상위 청크 추출
    --   source_filter가 지정된 경우 knowledge_documents와 조인하여 필터링
    -- -------------------------------------------------------------------------
    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                          -- 병합 후 재정렬을 고려해 여유분 확보
    ),

    -- -------------------------------------------------------------------------
    -- 키워드 검색: 전문 검색(FTS) 기반 상위 청크 추출
    --   'simple' 설정 사용. 한국어 형태소 분석이 필요하면 'korean'으로 교체.
    -- -------------------------------------------------------------------------
    keyword_results AS (
        SELECT
            kc.id                                                          AS chunk_id,
            kc.document_id,
            kc.content,
            ts_rank(
                to_tsvector('simple', kc.content),
                plainto_tsquery('simple', query_text)
            )                                                              AS keyword_score
        FROM knowledge_chunks kc
        JOIN knowledge_documents kd ON kd.id = kc.document_id
        WHERE
            to_tsvector('simple', kc.content) @@ plainto_tsquery('simple', query_text)
            AND (source_filter IS NULL OR kd.source = source_filter)
        ORDER BY keyword_score DESC
        LIMIT match_count * 2
    ),

    -- -------------------------------------------------------------------------
    -- FULL OUTER JOIN으로 두 결과 집합을 병합
    --   한쪽에만 존재하는 경우 상대 점수를 0으로 처리
    -- -------------------------------------------------------------------------
    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
    )

    -- -------------------------------------------------------------------------
    -- 최종 결과: combined_score 기준 내림차순 정렬 후 match_count만큼 반환
    -- -------------------------------------------------------------------------
    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
    '시맨틱(벡터) 검색과 키워드(FTS) 검색을 결합한 하이브리드 검색 함수. '
    'semantic_weight + keyword_weight = 1.0 이 되도록 조절하는 것을 권장.';
