"""Crawl tables/columns from connected databases and populate registries."""

from __future__ import annotations

import logging

from database.connection_manager import ConnectionManager
from database.remote_executor import RemoteExecutor
from database.schema_reader import SchemaReader
from knowledge.table_registry import TableRegistry
from knowledge.column_registry import ColumnRegistry

logger = logging.getLogger(__name__)


class SchemaDiscovery:
    """Discover and register schema metadata from connected databases."""

    def __init__(
        self,
        cm: ConnectionManager,
        table_registry: TableRegistry,
        column_registry: ColumnRegistry,
    ) -> None:
        self._cm = cm
        self._table_reg = table_registry
        self._col_reg = column_registry

    async def discover_connection(self, connection_id: str) -> dict:
        """Discover all tables and columns from a database connection.

        Returns: {"tables_found": int, "columns_found": int, "schemas": [str]}
        """
        conn = await self._cm.fetch_one(
            "SELECT * FROM v3q_config.database_connections WHERE id = :id",
            {"id": connection_id},
        )
        if not conn:
            raise ValueError(f"Connection {connection_id} not found")

        executor = RemoteExecutor(conn["connection_string"], conn["db_type"])
        reader = SchemaReader(executor, conn["db_type"])

        # Discover schemas
        schemas = await reader.list_schemas()
        logger.info(f"Found {len(schemas)} schemas in {conn['name']}")

        tables_found = 0
        columns_found = 0

        for schema in schemas:
            tables = await reader.list_tables(schema)
            for table_info in tables:
                # Register table
                existing = await self._cm.fetch_one("""
                    SELECT id FROM v3q_config.table_registry
                    WHERE connection_id = :cid AND source_schema = :schema AND source_table = :table
                """, {"cid": connection_id, "schema": schema, "table": table_info.name})

                if existing:
                    table_id = str(existing["id"])
                    await self._table_reg.update(
                        table_id, row_count=table_info.row_count
                    )
                else:
                    reg = await self._table_reg.create(
                        connection_id=connection_id,
                        source_schema=schema,
                        source_table=table_info.name,
                        source_system=conn.get("source_system"),
                        row_count=table_info.row_count,
                    )
                    table_id = str(reg["id"])
                tables_found += 1

                # Discover columns
                try:
                    columns = await reader.get_columns(schema, table_info.name)
                    for col in columns:
                        await self._col_reg.create(
                            table_id=table_id,
                            column_name=col.name,
                            data_type=col.data_type,
                            nullable=col.nullable,
                            is_primary_key=col.is_primary_key,
                            max_length=col.max_length,
                            numeric_precision=col.numeric_precision,
                            numeric_scale=col.numeric_scale,
                        )
                        columns_found += 1
                except Exception as e:
                    logger.warning(f"Failed to read columns for {schema}.{table_info.name}: {e}")

        return {
            "tables_found": tables_found,
            "columns_found": columns_found,
            "schemas": schemas,
        }

    async def browse_tables(
        self,
        connection_id: str,
        schema_filter: str | None = None,
        table_filter: str | None = None,
        limit: int = 500,
    ) -> dict:
        """Browse tables from a remote database without registering anything.

        Pure read-only: queries INFORMATION_SCHEMA only.
        Returns: {tables: [...], schemas: [...], total_found, returned, truncated}
        """
        conn = await self._cm.fetch_one(
            "SELECT * FROM v3q_config.database_connections WHERE id = :id",
            {"id": connection_id},
        )
        if not conn:
            raise ValueError(f"Connection {connection_id} not found")

        executor = RemoteExecutor(conn["connection_string"], conn["db_type"])
        reader = SchemaReader(executor, conn["db_type"])

        schemas = await reader.list_schemas()
        logger.info(f"Browse: found {len(schemas)} schemas in {conn['name']}")

        # Filter schemas
        target_schemas = schemas
        if schema_filter:
            sf = schema_filter.lower()
            target_schemas = [s for s in schemas if sf in s.lower()]

        all_tables = []
        for schema in target_schemas:
            tables = await reader.list_tables(schema)
            for t in tables:
                all_tables.append({
                    "schema": t.schema,
                    "table": t.name,
                    "row_count": t.row_count,
                })

        # Filter by table name
        if table_filter:
            tf = table_filter.lower()
            all_tables = [t for t in all_tables if tf in t["table"].lower()]

        total_found = len(all_tables)
        truncated = total_found > limit
        returned_tables = all_tables[:limit]

        # Check which are already registered
        existing = await self._cm.execute(
            "SELECT source_schema, source_table, is_synced FROM v3q_config.table_registry WHERE connection_id = :cid",
            {"cid": connection_id},
        )
        registered_set = {(r["source_schema"], r["source_table"]) for r in existing}
        synced_set = {(r["source_schema"], r["source_table"]) for r in existing if r["is_synced"]}

        for t in returned_tables:
            key = (t["schema"], t["table"])
            t["already_registered"] = key in registered_set
            t["is_synced"] = key in synced_set

        return {
            "tables": returned_tables,
            "schemas": schemas,
            "total_found": total_found,
            "returned": len(returned_tables),
            "truncated": truncated,
        }

    async def register_selected_tables(
        self,
        connection_id: str,
        selections: list[dict],
    ) -> dict:
        """Register selected tables (metadata only — no data copy).

        Each selection: {"schema": str, "table": str}
        Tables appear in TableRegistry; user then clicks Sync to copy data.
        Returns: {tables_registered, columns_registered, errors: []}
        """
        conn = await self._cm.fetch_one(
            "SELECT * FROM v3q_config.database_connections WHERE id = :id",
            {"id": connection_id},
        )
        if not conn:
            raise ValueError(f"Connection {connection_id} not found")

        executor = RemoteExecutor(conn["connection_string"], conn["db_type"])
        reader = SchemaReader(executor, conn["db_type"])

        tables_registered = 0
        columns_registered = 0
        errors = []

        for sel in selections:
            schema = sel["schema"]
            table = sel["table"]
            try:
                # Upsert table
                existing = await self._cm.fetch_one("""
                    SELECT id FROM v3q_config.table_registry
                    WHERE connection_id = :cid AND source_schema = :schema AND source_table = :table
                """, {"cid": connection_id, "schema": schema, "table": table})

                if existing:
                    table_id = str(existing["id"])
                else:
                    reg = await self._table_reg.create(
                        connection_id=connection_id,
                        source_schema=schema,
                        source_table=table,
                        source_system=conn.get("source_system"),
                    )
                    table_id = str(reg["id"])
                tables_registered += 1

                # Fetch and upsert columns
                columns = await reader.get_columns(schema, table)
                for col in columns:
                    await self._col_reg.create(
                        table_id=table_id,
                        column_name=col.name,
                        data_type=col.data_type,
                        nullable=col.nullable,
                        is_primary_key=col.is_primary_key,
                        max_length=col.max_length,
                        numeric_precision=col.numeric_precision,
                        numeric_scale=col.numeric_scale,
                    )
                    columns_registered += 1
            except Exception as e:
                logger.warning(f"Failed to register {schema}.{table}: {e}")
                errors.append(f"{schema}.{table}: {e}")

        return {
            "tables_registered": tables_registered,
            "columns_registered": columns_registered,
            "errors": errors,
        }
