"""Store and retrieve successful SQL patterns for learning."""

from __future__ import annotations

from database.connection_manager import ConnectionManager


class QueryPatterns:
    def __init__(self, cm: ConnectionManager) -> None:
        self._cm = cm

    async def store(self, nl_question: str, sql_template: str, tables_used: list[str]) -> dict:
        return await self._cm.fetch_one("""
            INSERT INTO v3q_config.query_patterns (nl_question, sql_template, tables_used)
            VALUES (:nl_question, :sql_template, :tables_used)
            RETURNING *
        """, {
            "nl_question": nl_question,
            "sql_template": sql_template,
            "tables_used": tables_used,
        })

    async def increment(self, pattern_id: str) -> None:
        await self._cm.execute_write("""
            UPDATE v3q_config.query_patterns
            SET success_count = success_count + 1, last_used_at = now()
            WHERE id = :id
        """, {"id": pattern_id})

    async def find_similar(self, tables: list[str], limit: int = 5) -> list[dict]:
        """Find query patterns that use any of the specified tables."""
        return await self._cm.execute("""
            SELECT * FROM v3q_config.query_patterns
            WHERE tables_used && :tables
            ORDER BY success_count DESC, last_used_at DESC
            LIMIT :limit
        """, {"tables": tables, "limit": limit})

    async def list_recent(self, limit: int = 20) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_config.query_patterns ORDER BY last_used_at DESC LIMIT :limit",
            {"limit": limit},
        )
