"""CRUD operations for v3q_config.table_registry."""

from __future__ import annotations

from typing import Any
from uuid import UUID

from database.connection_manager import ConnectionManager


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

    async def list_all(self) -> list[dict]:
        return await self._cm.execute("""
            SELECT tr.*, dc.name AS connection_name, dc.db_type, dc.source_system AS conn_source_system
            FROM v3q_config.table_registry tr
            LEFT JOIN v3q_config.database_connections dc ON dc.id = tr.connection_id
            ORDER BY tr.source_system, tr.source_schema, tr.source_table
        """)

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

    async def get_by_local_table(self, local_table: str) -> dict | None:
        return await self._cm.fetch_one(
            "SELECT * FROM v3q_config.table_registry WHERE local_table = :t",
            {"t": local_table},
        )

    async def get_by_source(
        self, connection_id: str, source_schema: str, source_table: str
    ) -> dict | None:
        return await self._cm.fetch_one(
            """SELECT * FROM v3q_config.table_registry
               WHERE connection_id = :cid AND source_schema = :ss AND source_table = :st""",
            {"cid": connection_id, "ss": source_schema, "st": source_table},
        )

    async def create(
        self,
        connection_id: str | None,
        source_schema: str | None,
        source_table: str,
        local_table: str | None = None,
        display_name: str | None = None,
        business_description: str | None = None,
        source_system: str | None = None,
        row_count: int | None = None,
        is_synced: bool = False,
    ) -> dict:
        return await self._cm.fetch_one("""
            INSERT INTO v3q_config.table_registry
                (connection_id, source_schema, source_table, local_table,
                 display_name, business_description, source_system, row_count, is_synced)
            VALUES (:connection_id, :source_schema, :source_table, :local_table,
                    :display_name, :business_description, :source_system, :row_count, :is_synced)
            RETURNING *
        """, {
            "connection_id": connection_id,
            "source_schema": source_schema,
            "source_table": source_table,
            "local_table": local_table,
            "display_name": display_name,
            "business_description": business_description,
            "source_system": source_system,
            "row_count": row_count,
            "is_synced": is_synced,
        })

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

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

    async def list_synced(self) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_config.table_registry WHERE is_synced = true ORDER BY source_table"
        )

    async def list_by_connection(self, connection_id: str) -> list[dict]:
        return await self._cm.execute(
            "SELECT * FROM v3q_config.table_registry WHERE connection_id = :cid ORDER BY source_table",
            {"cid": connection_id},
        )
