"""INFORMATION_SCHEMA queries for table/column discovery from remote databases."""

from __future__ import annotations

from dataclasses import dataclass
from typing import Any

from database.remote_executor import RemoteExecutor


@dataclass
class ColumnInfo:
    name: str
    data_type: str
    nullable: bool
    is_primary_key: bool
    max_length: int | None
    numeric_precision: int | None = None
    numeric_scale: int | None = None


@dataclass
class TableInfo:
    schema: str
    name: str
    row_count: int | None = None
    columns: list[ColumnInfo] | None = None


class SchemaReader:
    """Read schema metadata from remote databases via INFORMATION_SCHEMA."""

    def __init__(self, executor: RemoteExecutor, db_type: str = "sqlserver") -> None:
        self._executor = executor
        self._db_type = db_type

    async def list_schemas(self) -> list[str]:
        if self._db_type == "sqlserver":
            query = """
                SELECT DISTINCT TABLE_SCHEMA
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE'
                ORDER BY TABLE_SCHEMA
            """
        elif self._db_type == "sybase":
            # Use sp_tables — sysobjects requires elevated permissions on some SAP installs
            query = "EXEC sp_tables '%'"
            rows, _ = await self._executor.execute(query)
            schemas = set()
            for row in rows:
                owner = row.get("table_owner", "").strip()
                if owner:
                    schemas.add(owner)
            return sorted(schemas)
        elif self._db_type == "hana":
            query = """
                SELECT DISTINCT SCHEMA_NAME AS TABLE_SCHEMA
                FROM SYS.TABLES
                WHERE IS_SYSTEM_TABLE = 'FALSE'
                ORDER BY SCHEMA_NAME
            """
        elif self._db_type == "mysql":
            query = """
                SELECT DISTINCT table_schema AS TABLE_SCHEMA
                FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
                  AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
                ORDER BY table_schema
            """
        else:
            query = """
                SELECT DISTINCT table_schema AS TABLE_SCHEMA
                FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
                  AND table_schema NOT IN ('pg_catalog', 'information_schema')
                ORDER BY table_schema
            """

        rows, _ = await self._executor.execute(query)
        return [r["TABLE_SCHEMA"] for r in rows]

    async def list_tables(self, schema: str | None = None) -> list[TableInfo]:
        if self._db_type == "sqlserver":
            where = f"AND t.TABLE_SCHEMA = '{schema}'" if schema else ""
            query = f"""
                SELECT t.TABLE_SCHEMA, t.TABLE_NAME,
                       p.rows AS ROW_COUNT
                FROM INFORMATION_SCHEMA.TABLES t
                LEFT JOIN sys.partitions p
                    ON p.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
                    AND p.index_id IN (0, 1)
                WHERE t.TABLE_TYPE = 'BASE TABLE' {where}
                ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME
            """
        elif self._db_type == "sybase":
            query = "EXEC sp_tables '%', '%', NULL, \"'TABLE'\""
            rows, _ = await self._executor.execute(query)
            tables = []
            for r in rows:
                table_schema = r.get("table_owner", "").strip()
                table_name = r.get("table_name", "").strip()
                if not table_name:
                    continue
                if schema and table_schema != schema:
                    continue
                tables.append(TableInfo(schema=table_schema, name=table_name, row_count=None))
            return tables
        elif self._db_type == "hana":
            where = f"AND SCHEMA_NAME = '{schema}'" if schema else ""
            query = f"""
                SELECT SCHEMA_NAME AS TABLE_SCHEMA, TABLE_NAME,
                       RECORD_COUNT AS ROW_COUNT
                FROM SYS.M_TABLES
                WHERE IS_SYSTEM_TABLE = 'FALSE' {where}
                ORDER BY SCHEMA_NAME, TABLE_NAME
            """
        else:
            where = f"AND table_schema = '{schema}'" if schema else ""
            query = f"""
                SELECT table_schema AS TABLE_SCHEMA, table_name AS TABLE_NAME
                FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
                  AND table_schema NOT IN ('pg_catalog', 'information_schema')
                  {where}
                ORDER BY table_schema, table_name
            """

        rows, _ = await self._executor.execute(query)
        return [
            TableInfo(
                schema=r["TABLE_SCHEMA"],
                name=r["TABLE_NAME"],
                row_count=r.get("ROW_COUNT"),
            )
            for r in rows
        ]

    async def get_columns(self, schema: str, table: str) -> list[ColumnInfo]:
        if self._db_type == "sqlserver":
            query = f"""
                SELECT
                    c.COLUMN_NAME,
                    c.DATA_TYPE,
                    CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS IS_NULLABLE,
                    CASE WHEN kcu.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IS_PK,
                    c.CHARACTER_MAXIMUM_LENGTH,
                    c.NUMERIC_PRECISION,
                    c.NUMERIC_SCALE
                FROM INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
                    ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = c.TABLE_NAME
                    AND kcu.COLUMN_NAME = c.COLUMN_NAME
                    AND kcu.CONSTRAINT_NAME IN (
                        SELECT CONSTRAINT_NAME
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                          AND TABLE_SCHEMA = '{schema}'
                          AND TABLE_NAME = '{table}'
                    )
                WHERE c.TABLE_SCHEMA = '{schema}' AND c.TABLE_NAME = '{table}'
                ORDER BY c.ORDINAL_POSITION
            """
        elif self._db_type == "sybase":
            query = f"EXEC sp_columns '{table}', '{schema}'"
            rows, _ = await self._executor.execute(query)
            return [
                ColumnInfo(
                    name=r["column_name"].strip(),
                    data_type=r["type_name"].strip(),
                    nullable=r["nullable"] == 1,
                    is_primary_key=False,
                    max_length=r.get("length"),
                    numeric_precision=r.get("precision"),
                    numeric_scale=r.get("scale"),
                )
                for r in rows
            ]
        elif self._db_type == "hana":
            query = f"""
                SELECT
                    c.COLUMN_NAME,
                    c.DATA_TYPE_NAME AS DATA_TYPE,
                    CASE WHEN c.IS_NULLABLE = 'TRUE' THEN 1 ELSE 0 END AS IS_NULLABLE,
                    CASE WHEN ic.CONSTRAINT = 'PRIMARY KEY' THEN 1 ELSE 0 END AS IS_PK,
                    c.LENGTH AS CHARACTER_MAXIMUM_LENGTH,
                    c.LENGTH AS NUMERIC_PRECISION,
                    c.SCALE AS NUMERIC_SCALE
                FROM SYS.TABLE_COLUMNS c
                LEFT JOIN SYS.INDEX_COLUMNS ic
                    ON ic.SCHEMA_NAME = c.SCHEMA_NAME
                    AND ic.TABLE_NAME = c.TABLE_NAME
                    AND ic.COLUMN_NAME = c.COLUMN_NAME
                    AND ic.CONSTRAINT = 'PRIMARY KEY'
                WHERE c.SCHEMA_NAME = '{schema}' AND c.TABLE_NAME = '{table}'
                ORDER BY c.POSITION
            """
        elif self._db_type == "mysql":
            query = f"""
                SELECT
                    c.COLUMN_NAME AS COLUMN_NAME,
                    c.DATA_TYPE AS DATA_TYPE,
                    CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS IS_NULLABLE,
                    CASE WHEN c.COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END AS IS_PK,
                    c.CHARACTER_MAXIMUM_LENGTH AS CHARACTER_MAXIMUM_LENGTH,
                    c.NUMERIC_PRECISION AS NUMERIC_PRECISION,
                    c.NUMERIC_SCALE AS NUMERIC_SCALE
                FROM information_schema.columns c
                WHERE c.TABLE_SCHEMA = '{schema}' AND c.TABLE_NAME = '{table}'
                ORDER BY c.ORDINAL_POSITION
            """
        else:
            query = f"""
                SELECT
                    c.column_name AS COLUMN_NAME,
                    c.data_type AS DATA_TYPE,
                    CASE WHEN c.is_nullable = 'YES' THEN 1 ELSE 0 END AS IS_NULLABLE,
                    CASE WHEN kcu.column_name IS NOT NULL THEN 1 ELSE 0 END AS IS_PK,
                    c.character_maximum_length AS CHARACTER_MAXIMUM_LENGTH,
                    c.numeric_precision AS NUMERIC_PRECISION,
                    c.numeric_scale AS NUMERIC_SCALE
                FROM information_schema.columns c
                LEFT JOIN information_schema.key_column_usage kcu
                    ON kcu.table_schema = c.table_schema
                    AND kcu.table_name = c.table_name
                    AND kcu.column_name = c.column_name
                WHERE c.table_schema = '{schema}' AND c.table_name = '{table}'
                ORDER BY c.ordinal_position
            """

        rows, _ = await self._executor.execute(query)
        return [
            ColumnInfo(
                name=r["COLUMN_NAME"],
                data_type=r["DATA_TYPE"],
                nullable=bool(r["IS_NULLABLE"]),
                is_primary_key=bool(r["IS_PK"]),
                max_length=r.get("CHARACTER_MAXIMUM_LENGTH"),
                numeric_precision=r.get("NUMERIC_PRECISION"),
                numeric_scale=r.get("NUMERIC_SCALE"),
            )
            for r in rows
        ]
