"""CRUD operations for watch_columns configuration."""

from __future__ import annotations

import logging
from uuid import UUID

from database.connection_manager import ConnectionManager

logger = logging.getLogger(__name__)


class WatchConfig:
    """Manage which columns to watch for changes during sync."""

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

    async def get_watch_columns(self, table_id: str) -> list[dict]:
        rows = await self._cm.execute(
            "SELECT * FROM v3q_config.watch_columns WHERE table_id = :table_id ORDER BY column_name",
            {"table_id": table_id},
        )
        return [dict(r) for r in rows]

    async def get_key_columns(self, table_id: str) -> list[str]:
        rows = await self._cm.execute(
            "SELECT column_name FROM v3q_config.watch_columns WHERE table_id = :table_id AND is_key_column = true",
            {"table_id": table_id},
        )
        return [r["column_name"] for r in rows]

    async def get_status_columns(self, table_id: str) -> list[str]:
        rows = await self._cm.execute(
            "SELECT column_name FROM v3q_config.watch_columns WHERE table_id = :table_id AND is_status_column = true",
            {"table_id": table_id},
        )
        return [r["column_name"] for r in rows]

    async def get_doc_number_column(self, table_id: str) -> str | None:
        """Return the column name marked as doc_number for this table, or None."""
        row = await self._cm.fetch_one(
            "SELECT column_name FROM v3q_config.watch_columns WHERE table_id = :table_id AND is_doc_number_column = true",
            {"table_id": table_id},
        )
        return row["column_name"] if row else None

    async def get_all_watched_column_names(self, table_id: str) -> list[str]:
        rows = await self._cm.execute(
            "SELECT column_name FROM v3q_config.watch_columns WHERE table_id = :table_id",
            {"table_id": table_id},
        )
        return [r["column_name"] for r in rows]

    async def configure(self, table_id: str, columns: list[dict]) -> list[dict]:
        """Set watch columns for a table. Replaces existing configuration.

        Each column dict: {"column_name": str, "is_status_column": bool, "is_key_column": bool, "is_doc_number_column": bool}
        """
        await self._cm.execute_write(
            "DELETE FROM v3q_config.watch_columns WHERE table_id = :table_id",
            {"table_id": table_id},
        )
        results = []
        for col in columns:
            row = await self._cm.fetch_one(
                """INSERT INTO v3q_config.watch_columns (table_id, column_name, is_status_column, is_key_column, is_doc_number_column)
                   VALUES (:table_id, :column_name, :is_status_column, :is_key_column, :is_doc_number_column)
                   RETURNING *""",
                {
                    "table_id": table_id,
                    "column_name": col["column_name"],
                    "is_status_column": col.get("is_status_column", False),
                    "is_key_column": col.get("is_key_column", False),
                    "is_doc_number_column": col.get("is_doc_number_column", False),
                },
            )
            results.append(dict(row))
        logger.info(f"Configured {len(results)} watch columns for table {table_id}")
        return results

    async def has_watch_config(self, table_id: str) -> bool:
        row = await self._cm.fetch_one(
            "SELECT count(*) as cnt FROM v3q_config.watch_columns WHERE table_id = :table_id",
            {"table_id": table_id},
        )
        return row["cnt"] > 0
