#!/usr/bin/env python3
"""
utils/session_store_search.py — FTS5 메시지 전문 검색 및 스키마 DDL

SessionStore가 사용하는 SQLite 스키마 DDL 상수와
FTS5 기반 전문 검색 믹스인을 제공한다.

Usage:
    from utils.session_store_search import SCHEMA_DDL, SearchMixin
"""

from __future__ import annotations

import sqlite3
from typing import Any

from utils.logger import get_logger

logger = get_logger(__name__)

# ---------------------------------------------------------------------------
# DDL 상수
# ---------------------------------------------------------------------------

DDL_SESSIONS = """
CREATE TABLE IF NOT EXISTS sessions (
    session_id       TEXT PRIMARY KEY,
    source           TEXT NOT NULL,
    model            TEXT,
    parent_session_id TEXT,
    title            TEXT,
    created_at       TEXT NOT NULL,
    ended_at         TEXT,
    end_reason       TEXT
);
"""

DDL_MESSAGES = """
CREATE TABLE IF NOT EXISTS messages (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      TEXT NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
    role            TEXT NOT NULL,
    content         TEXT,
    tool_calls      TEXT,
    tool_call_id    TEXT,
    created_at      TEXT NOT NULL
);
"""

DDL_MESSAGES_INDEX = """
CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id, created_at);
"""

DDL_FTS5 = """
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts
USING fts5(content, role, content=messages, content_rowid=id);
"""

DDL_FTS5_TRIGGER_INSERT = """
CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
    INSERT INTO messages_fts(rowid, content, role) VALUES (new.id, new.content, new.role);
END;
"""

DDL_FTS5_TRIGGER_DELETE = """
CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
    INSERT INTO messages_fts(messages_fts, rowid, content, role)
    VALUES ('delete', old.id, old.content, old.role);
END;
"""

DDL_FTS5_TRIGGER_UPDATE = """
CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
    INSERT INTO messages_fts(messages_fts, rowid, content, role)
    VALUES ('delete', old.id, old.content, old.role);
    INSERT INTO messages_fts(rowid, content, role) VALUES (new.id, new.content, new.role);
END;
"""

# 초기화 순서대로 묶어서 노출 (session_store._setup()에서 순회)
ALL_DDL: list[str] = [
    DDL_SESSIONS,
    DDL_MESSAGES,
    DDL_MESSAGES_INDEX,
    DDL_FTS5,
    DDL_FTS5_TRIGGER_INSERT,
    DDL_FTS5_TRIGGER_DELETE,
    DDL_FTS5_TRIGGER_UPDATE,
]


# ---------------------------------------------------------------------------
# SearchMixin — FTS5 전문 검색
# ---------------------------------------------------------------------------


class SearchMixin:
    """FTS5 메시지 검색 기능을 SessionStore에 주입하는 믹스인.

    사용 클래스는 self._conn(sqlite3.Connection)을 제공해야 한다.
    """

    _conn: sqlite3.Connection

    def search_messages(
        self,
        query: str,
        session_id: str | None = None,
        limit: int = 20,
    ) -> list[dict[str, Any]]:
        """FTS5로 메시지 내용을 전문 검색한다.

        Args:
            query: FTS5 검색어 (예: ``"python AND error"``)
            session_id: 특정 세션으로 결과를 제한 (None이면 전체)
            limit: 최대 반환 개수

        Returns:
            매칭된 메시지 dict 목록 (score 내림차순)
        """
        try:
            if session_id is not None:
                cur = self._conn.execute(
                    """
                    SELECT m.*
                    FROM messages m
                    JOIN messages_fts f ON m.id = f.rowid
                    WHERE messages_fts MATCH ?
                      AND m.session_id = ?
                    ORDER BY rank
                    LIMIT ?
                    """,
                    (query, session_id, limit),
                )
            else:
                cur = self._conn.execute(
                    """
                    SELECT m.*
                    FROM messages m
                    JOIN messages_fts f ON m.id = f.rowid
                    WHERE messages_fts MATCH ?
                    ORDER BY rank
                    LIMIT ?
                    """,
                    (query, limit),
                )
            return [dict(row) for row in cur.fetchall()]
        except sqlite3.OperationalError as exc:
            logger.warning("search_messages: FTS5 query failed: %s", exc)
            return []

    def search_sessions_by_content(
        self,
        query: str,
        limit: int = 10,
    ) -> list[dict[str, Any]]:
        """메시지 내용으로 세션을 검색한다.

        Args:
            query: FTS5 검색어
            limit: 최대 반환 세션 수

        Returns:
            매칭된 session_id를 포함하는 세션 dict 목록 (중복 제거, 최신순)
        """
        try:
            cur = self._conn.execute(
                """
                SELECT DISTINCT s.*
                FROM sessions s
                JOIN messages m ON m.session_id = s.session_id
                JOIN messages_fts f ON m.id = f.rowid
                WHERE messages_fts MATCH ?
                ORDER BY s.created_at DESC
                LIMIT ?
                """,
                (query, limit),
            )
            return [dict(row) for row in cur.fetchall()]
        except sqlite3.OperationalError as exc:
            logger.warning("search_sessions_by_content: FTS5 query failed: %s", exc)
            return []