"""
RAG Chunk Repository - PostgreSQL 實作
=======================================
Phase 33 ADR-067: rag_chunks 表 CRUD 操作

職責: rag_chunks 的向量搜尋、插入、刪除、統計
設計: raw SQL via SQLAlchemy text()（表由 phase28_rag_pgvector.sql 建立）

版本: v1.0
建立: 2026-04-10 (台北時區)
建立者: Claude Sonnet 4.6 (架構審查 C1 修正 — Service 不直接碰 DB)
"""

import json
from typing import Any

import structlog
from sqlalchemy import text

from src.db.base import get_db_context

logger = structlog.get_logger(__name__)


async def search_chunks(embedding: list[float], top_k: int) -> list[dict[str, Any]]:
    """向量近鄰搜尋，回傳最相似的 top_k 筆"""
    try:
        vec_str = "[" + ",".join(str(v) for v in embedding) + "]"
        async with get_db_context() as db:
            rows = await db.execute(
                text("""
                    SELECT source, source_id, title, chunk_text,
                           1 - (embedding <=> CAST(:vec AS vector)) AS similarity
                    FROM rag_chunks
                    ORDER BY embedding <=> CAST(:vec AS vector)
                    LIMIT :k
                """),
                {"vec": vec_str, "k": top_k},
            )
            return [dict(r._mapping) for r in rows]
    except Exception as e:
        logger.error("rag_chunk_search_failed", error=str(e))
        return []


async def insert_chunk(
    source: str,
    source_id: str,
    title: str,
    chunk_text: str,
    embedding: list[float],
    metadata: dict,
) -> bool:
    """插入單筆 chunk"""
    try:
        vec_str = "[" + ",".join(str(v) for v in embedding) + "]"
        async with get_db_context() as db:
            await db.execute(
                text("""
                    INSERT INTO rag_chunks (source, source_id, title, chunk_text, embedding, metadata)
                    VALUES (:source, :source_id, :title, :chunk_text,
                            CAST(:embedding AS vector), CAST(:metadata AS jsonb))
                """),
                {
                    "source": source,
                    "source_id": source_id,
                    "title": title,
                    "chunk_text": chunk_text,
                    "embedding": vec_str,
                    "metadata": json.dumps(metadata, ensure_ascii=False),
                },
            )
        return True
    except Exception as e:
        logger.error("rag_chunk_insert_failed", error=str(e))
        return False


async def delete_by_source_id(source_id: str) -> None:
    """刪除指定 source_id 的所有 chunks（重新索引前去重）"""
    try:
        async with get_db_context() as db:
            await db.execute(
                text("DELETE FROM rag_chunks WHERE source_id = :source_id"),
                {"source_id": source_id},
            )
    except Exception as e:
        logger.warning("rag_chunk_delete_failed", source_id=source_id, error=str(e))


async def create_ivfflat_index() -> dict:
    """建立 ivfflat 向量索引（需 >100 chunks，加速 KNN 搜尋）"""
    try:
        async with get_db_context() as db:
            await db.execute(
                text("""
                    CREATE INDEX IF NOT EXISTS idx_rag_chunks_embedding
                    ON rag_chunks
                    USING ivfflat (embedding vector_cosine_ops)
                    WITH (lists = 100)
                """)
            )
            rows = await db.execute(
                text("SELECT indexname FROM pg_indexes WHERE tablename = 'rag_chunks'")
            )
            indexes = [r[0] for r in rows]
        logger.info("rag_ivfflat_index_created", indexes=indexes)
        return {"status": "ok", "indexes": indexes}
    except Exception as e:
        logger.error("rag_ivfflat_index_failed", error=str(e))
        return {"status": "error", "error": str(e)}


async def get_stats() -> dict:
    """取得 chunk 數量與來源統計"""
    try:
        async with get_db_context() as db:
            row = await db.execute(
                text("SELECT COUNT(*) as total, COUNT(DISTINCT source) as sources FROM rag_chunks")
            )
            r = row.one()
            return {"total_chunks": r.total, "sources": r.sources}
    except Exception as e:
        logger.error("rag_chunk_stats_failed", error=str(e))
        return {"total_chunks": 0, "sources": 0}
