"""CRUD operations for v3q_config.column_registry."""

from __future__ import annotations

from database.connection_manager import ConnectionManager


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

    async def list_by_table(self, table_id: str) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_config.column_registry WHERE table_id = :tid ORDER BY column_name",
            {"tid": table_id},
        )

    async def get(self, column_id: str) -> dict | None:
        return await self._cm.fetch_one(
            "SELECT * FROM v3q_config.column_registry WHERE id = :id",
            {"id": column_id},
        )

    async def create(
        self,
        table_id: str,
        column_name: str,
        data_type: str,
        nullable: bool = True,
        is_primary_key: bool = False,
        max_length: int | None = None,
        numeric_precision: int | None = None,
        numeric_scale: int | None = None,
        display_name: str | None = None,
        business_description: str | None = None,
    ) -> dict:
        return await self._cm.fetch_one("""
            INSERT INTO v3q_config.column_registry
                (table_id, column_name, data_type, nullable, is_primary_key,
                 max_length, numeric_precision, numeric_scale,
                 display_name, business_description)
            VALUES (:table_id, :column_name, :data_type, :nullable, :is_primary_key,
                    :max_length, :numeric_precision, :numeric_scale,
                    :display_name, :business_description)
            ON CONFLICT (table_id, column_name) DO UPDATE SET
                data_type = EXCLUDED.data_type,
                nullable = EXCLUDED.nullable,
                is_primary_key = EXCLUDED.is_primary_key,
                max_length = EXCLUDED.max_length,
                numeric_precision = EXCLUDED.numeric_precision,
                numeric_scale = EXCLUDED.numeric_scale
            RETURNING *
        """, {
            "table_id": table_id,
            "column_name": column_name,
            "data_type": data_type,
            "nullable": bool(nullable),
            "is_primary_key": bool(is_primary_key),
            "max_length": int(max_length) if max_length is not None else None,
            "numeric_precision": int(numeric_precision) if numeric_precision is not None else None,
            "numeric_scale": int(numeric_scale) if numeric_scale is not None else None,
            "display_name": display_name,
            "business_description": business_description,
        })

    async def bulk_create(self, table_id: str, columns: list[dict]) -> int:
        """Insert multiple columns for a table. Returns count created."""
        count = 0
        for col in columns:
            await self.create(table_id=table_id, **col)
            count += 1
        return count

    async def update(self, column_id: str, **kwargs) -> dict | None:
        if not kwargs:
            return await self.get(column_id)
        set_clause = ", ".join(f"{k} = :{k}" for k in kwargs)
        kwargs["id"] = column_id
        return await self._cm.fetch_one(
            f"UPDATE v3q_config.column_registry SET {set_clause} WHERE id = :id RETURNING *",
            kwargs,
        )

    async def delete_by_table(self, table_id: str) -> None:
        await self._cm.execute_write(
            "DELETE FROM v3q_config.column_registry WHERE table_id = :tid",
            {"tid": table_id},
        )
