from datetime import datetime
from uuid import UUID

from personal_agent.database.database import Database
from personal_agent.memory.models import LongTermMemory, MemoryCategory


class LongTermMemoryStore:
    def __init__(self, database: Database) -> None:
        self.database = database

    def add(self, memory: LongTermMemory) -> None:
        created_at = memory.created_at.isoformat().replace("+00:00", "Z")

        with self.database.connect() as connection:
            connection.execute(
                """
                INSERT INTO memories (id, content, category, created_at)
                VALUES (?, ?, ?, ?)
                """,
                (str(memory.id), memory.content, memory.category.value, created_at),
            )

            connection.execute(
                """
                INSERT INTO memories_fts(rowid, content)
                SELECT rowid, content
                FROM memories
                WHERE id = ?
                """,
                (str(memory.id),),
            )

    def get(self, memory_id: UUID) -> LongTermMemory | None:
        with self.database.connect() as connection:
            row = connection.execute(
                """
                SELECT id, content, category, created_at
                FROM memories
                WHERE id = ?
                """,
                (str(memory_id),),
            ).fetchone()

        if row is None:
            return None

        return self._from_row(row)

    def list(self) -> list[LongTermMemory]:
        with self.database.connect() as connection:
            rows = connection.execute(
                """
                SELECT id, content, category, created_at
                FROM memories
                ORDER BY id
                """
            ).fetchall()

        return [self._from_row(row) for row in rows]

    def delete(self, memory_id: UUID) -> None:
        with self.database.connect() as connection:
            connection.execute(
                """
                DELETE FROM memories_fts
                WHERE rowid = (
                    SELECT rowid
                    FROM memories
                    WHERE id = ?
                )
                """,
                (str(memory_id),),
            )

            connection.execute(
                "DELETE FROM memories WHERE id = ?",
                (str(memory_id),),
            )

    def search(
        self,
        query: str,
        category: MemoryCategory | None = None,
        limit: int = 10,
    ) -> list[LongTermMemory]:
        if limit <= 0:
            raise ValueError("Search limit must be positive")

        if not query.strip():
            raise ValueError("Search query must not be empty")

        terms = query.split()
        fts_query = " OR ".join(
            f'"{term.replace(chr(34), chr(34) * 2)}"' for term in terms
        )

        sql = """
            SELECT m.id, m.content, m.category, m.created_at
            FROM memories_fts as f
            JOIN memories AS m ON m.rowid = f.rowid
            WHERE memories_fts MATCH ?
        """
        parameters: list[str | int] = [fts_query]

        if category is not None:
            sql += " AND m.category = ?"
            parameters.append(category.value)

        sql += " ORDER BY bm25(memories_fts) LIMIT ?"
        parameters.append(limit)

        with self.database.connect() as connection:
            rows = connection.execute(sql, parameters).fetchall()

        return [self._from_row(row) for row in rows]

    @staticmethod
    def _from_row(row: tuple[str, str, str, str]) -> LongTermMemory:
        memory_id, content, category, created_at = row

        return LongTermMemory(
            id=UUID(memory_id),
            content=content,
            category=MemoryCategory(category),
            created_at=datetime.fromisoformat(created_at),
        )