#!/usr/bin/env python3
"""
Oracle Database Metadata Operations

This module handles metadata operations like listing tables, describing tables,
and retrieving database schema information.
"""

import logging
import os
from typing import Any, Dict, List, Optional

import cx_Oracle

from server.oracle.pool import OraclePool


class OracleMetadata:
    """
    Handles Oracle database metadata operations.
    """

    def __init__(self, pool: OraclePool):
        """
        Initialize the metadata handler with a connection pool.

        Args:
            pool: OraclePool instance
        """
        self.pool = pool
        self.logger = logging.getLogger(__name__)
        self.max_rows = int(os.getenv("MCP_MAX_ROWS", "500"))
        self.max_text_size = int(os.getenv("MCP_MAX_TEXT_SIZE", "5000"))

    async def list_tables(self, schema: Optional[str] = None) -> Dict[str, Any]:
        """
        List all tables in the database.

        Args:
            schema: Schema name (optional, defaults to current user)

        Returns:
            dict: List of tables with metadata
        """
        conn = None
        cursor = None

        try:
            conn = self.pool.get_connection()
            cursor = conn.cursor()

            if schema:
                # List tables for specific schema
                cursor.execute(
                    """
                    SELECT TABLE_NAME, TABLE_TYPE, NUM_ROWS, LAST_ANALYZED
                    FROM ALL_TABLES
                    WHERE OWNER = UPPER(:schema)
                    ORDER BY TABLE_NAME
                """,
                    schema=schema,
                )
            else:
                # List tables for current user
                cursor.execute(
                    """
                    SELECT TABLE_NAME, TABLE_TYPE, NUM_ROWS, LAST_ANALYZED
                    FROM USER_TABLES
                    ORDER BY TABLE_NAME
                """
                )

            tables = []
            for row in cursor:
                tables.append(
                    {
                        "name": row[0],
                        "type": row[1],
                        "num_rows": row[2],
                        "last_analyzed": row[3].isoformat() if row[3] else None,
                    }
                )

            cursor.close()

            return {
                "status": "success",
                "schema": schema or "current_user",
                "tables": tables,
                "count": len(tables),
            }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error listing tables: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error listing tables: {e}")
            return {"status": "error", "message": f"Error listing tables: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    async def describe_table(
        self, table_name: str, schema: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Get table structure and column information.

        Args:
            table_name: Name of the table
            schema: Schema name (optional, defaults to current user)

        Returns:
            dict: Table structure with column details
        """
        conn = None
        cursor = None

        try:
            if not table_name or not table_name.strip():
                return {"status": "error", "message": "Table name is required"}

            conn = self.pool.get_connection()
            cursor = conn.cursor()

            # Determine which view to use based on schema
            if schema:
                # Use ALL_TABLES and ALL_TAB_COLUMNS for specific schema
                table_query = """
                    SELECT TABLE_NAME, TABLE_TYPE, NUM_ROWS, LAST_ANALYZED
                    FROM ALL_TABLES
                    WHERE OWNER = UPPER(:schema) AND TABLE_NAME = UPPER(:table_name)
                """
                columns_query = """
                    SELECT 
                        COLUMN_NAME,
                        DATA_TYPE,
                        DATA_TYPE_MOD,
                        DATA_TYPE_OWNER,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        NULLABLE,
                        COLUMN_ID,
                        DEFAULT_LENGTH,
                        DATA_DEFAULT
                    FROM ALL_TAB_COLUMNS
                    WHERE OWNER = UPPER(:schema) AND TABLE_NAME = UPPER(:table_name)
                    ORDER BY COLUMN_ID
                """
                params = {"schema": schema, "table_name": table_name}
            else:
                # Use USER_TABLES and USER_TAB_COLUMNS for current user
                table_query = """
                    SELECT TABLE_NAME, TABLE_TYPE, NUM_ROWS, LAST_ANALYZED
                    FROM USER_TABLES
                    WHERE TABLE_NAME = UPPER(:table_name)
                """
                columns_query = """
                    SELECT 
                        COLUMN_NAME,
                        DATA_TYPE,
                        DATA_TYPE_MOD,
                        DATA_TYPE_OWNER,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        NULLABLE,
                        COLUMN_ID,
                        DEFAULT_LENGTH,
                        DATA_DEFAULT
                    FROM USER_TAB_COLUMNS
                    WHERE TABLE_NAME = UPPER(:table_name)
                    ORDER BY COLUMN_ID
                """
                params = {"table_name": table_name}

            # Get table information
            cursor.execute(table_query, params)
            table_row = cursor.fetchone()

            if not table_row:
                return {"status": "error", "message": f"Table '{table_name}' not found"}

            table_info = {
                "name": table_row[0],
                "type": table_row[1],
                "num_rows": table_row[2],
                "last_analyzed": table_row[3].isoformat() if table_row[3] else None,
            }

            # Get column information
            cursor.execute(columns_query, params)
            columns = []

            for row in cursor:
                column = {
                    "name": row[0],
                    "data_type": row[1],
                    "data_type_mod": row[2],
                    "data_type_owner": row[3],
                    "data_length": row[4],
                    "data_precision": row[5],
                    "data_scale": row[6],
                    "nullable": row[7] == "Y",
                    "column_id": row[8],
                    "default_length": row[9],
                    "default_value": row[10],
                }
                columns.append(column)

            cursor.close()

            return {
                "status": "success",
                "table": table_info,
                "columns": columns,
                "column_count": len(columns),
            }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error describing table: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error describing table: {e}")
            return {"status": "error", "message": f"Error describing table: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def list_schemas(self) -> Dict[str, Any]:
        """
        List all available schemas from ALL_USERS.

        Returns:
            dict: List of schemas
        """
        conn = None
        cursor = None

        try:
            conn = self.pool.get_connection()
            cursor = conn.cursor()

            cursor.execute(
                """
                SELECT USERNAME
                FROM ALL_USERS
                WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'MDSYS', 'ORDSYS', 'XDB', 'WMSYS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'ORDSYS', 'ORDDATA', 'OLAPSYS', 'OUTLN', 'WMSYS', 'XDB')
                ORDER BY USERNAME
            """
            )

            schemas = []
            row_count = 0

            for row in cursor:
                if row_count >= self.max_rows:
                    break
                schemas.append(row[0])
                row_count += 1

            cursor.close()

            total_rows = row_count
            if cursor.rowcount > 0:
                total_rows = cursor.rowcount

            result = {
                "status": "success",
                "schemas": schemas,
                "count": len(schemas),
                "limit": self.max_rows,
                "truncated": row_count >= self.max_rows,
            }

            if result["truncated"]:
                result["warning"] = (
                    f"Results truncated to {self.max_rows} schemas. Use schema-specific queries for complete results."
                )

            return result

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error listing schemas: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error listing schemas: {e}")
            return {"status": "error", "message": f"Error listing schemas: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def list_objects(self, owner: Optional[str] = None) -> Dict[str, Any]:
        """
        List objects from ALL_OBJECTS with optional owner filtering.

        Args:
            owner: Optional owner to filter by (defaults to current user if not specified)

        Returns:
            dict: List of objects
        """
        conn = None
        cursor = None

        try:
            conn = self.pool.get_connection()
            cursor = conn.cursor()

            if owner:
                # List objects for specific owner
                cursor.execute(
                    """
                    SELECT
                        OWNER,
                        OBJECT_NAME,
                        SUBOBJECT_NAME,
                        OBJECT_TYPE,
                        CREATED,
                        LAST_DDL_TIME,
                        STATUS,
                        TIMESTAMP
                    FROM ALL_OBJECTS
                    WHERE OWNER = UPPER(:owner)
                    ORDER BY OBJECT_TYPE, OBJECT_NAME
                """,
                    owner=owner,
                )
            else:
                # List objects for current user
                cursor.execute(
                    """
                    SELECT
                        USER as OWNER,
                        OBJECT_NAME,
                        SUBOBJECT_NAME,
                        OBJECT_TYPE,
                        CREATED,
                        LAST_DDL_TIME,
                        STATUS,
                        TIMESTAMP
                    FROM USER_OBJECTS
                    ORDER BY OBJECT_TYPE, OBJECT_NAME
                """
                )

            objects = []
            row_count = 0

            for row in cursor:
                if row_count >= self.max_rows:
                    break

                objects.append(
                    {
                        "owner": row[0],
                        "name": row[1],
                        "subobject_name": row[2],
                        "type": row[3],
                        "created": row[4].isoformat() if row[4] else None,
                        "last_ddl_time": row[5].isoformat() if row[5] else None,
                        "status": row[6],
                        "timestamp": row[7],
                    }
                )
                row_count += 1

            cursor.close()

            total_rows = row_count
            if cursor.rowcount > 0:
                total_rows = cursor.rowcount

            result = {
                "status": "success",
                "owner": owner or "current_user",
                "objects": objects,
                "count": len(objects),
                "limit": self.max_rows,
                "truncated": row_count >= self.max_rows,
            }

            if result["truncated"]:
                result["warning"] = (
                    f"Results truncated to {self.max_rows} objects. Use schema-specific queries for complete results."
                )

            return result

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error listing objects: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error listing objects: {e}")
            return {"status": "error", "message": f"Error listing objects: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    async def get_table_indexes(
        self, table_name: str, schema: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Get index information for a table.

        Args:
            table_name: Name of the table
            schema: Schema name (optional)

        Returns:
            dict: Index information
        """
        conn = None
        cursor = None

        try:
            if not table_name or not table_name.strip():
                return {"status": "error", "message": "Table name is required"}

            conn = self.pool.get_connection()
            cursor = conn.cursor()

            if schema:
                cursor.execute(
                    """
                    SELECT 
                        INDEX_NAME, INDEX_TYPE, UNIQUENESS, TABLESPACE_NAME,
                        STATUS, FUNCIDX_STATUS, JOIN_INDEX, PARTITIONED,
                        TEMPORARY, GENERATED, SECONDARY, BUFFER_POOL,
                        USER_STATS, DURATION, PCT_DIRECT_ACCESS, ITYP_OWNER,
                        ITYP_NAME, PARAMETERS, GLOBAL_STATS, DOMIDX_STATUS,
                        DOMIDX_OPSTATUS, FUNCIDX_STATUS, VISIBILITY
                    FROM ALL_INDEXES
                    WHERE OWNER = UPPER(:schema) AND TABLE_NAME = UPPER(:table_name)
                    ORDER BY INDEX_NAME
                """,
                    schema=schema,
                    table_name=table_name,
                )
            else:
                cursor.execute(
                    """
                    SELECT 
                        INDEX_NAME, INDEX_TYPE, UNIQUENESS, TABLESPACE_NAME,
                        STATUS, FUNCIDX_STATUS, JOIN_INDEX, PARTITIONED,
                        TEMPORARY, GENERATED, SECONDARY, BUFFER_POOL,
                        USER_STATS, DURATION, PCT_DIRECT_ACCESS, ITYP_OWNER,
                        ITYP_NAME, PARAMETERS, GLOBAL_STATS, DOMIDX_STATUS,
                        DOMIDX_OPSTATUS, FUNCIDX_STATUS, VISIBILITY
                    FROM USER_INDEXES
                    WHERE TABLE_NAME = UPPER(:table_name)
                    ORDER BY INDEX_NAME
                """,
                    table_name=table_name,
                )

            indexes = []
            for row in cursor:
                index = {
                    "name": row[0],
                    "type": row[1],
                    "uniqueness": row[2],
                    "tablespace": row[3],
                    "status": row[4],
                    "partitioned": row[6] == "YES",
                    "temporary": row[8] == "Y",
                    "generated": row[9] == "Y",
                    "secondary": row[10] == "Y",
                    "buffer_pool": row[11],
                    "user_stats": row[12] == "YES",
                    "visibility": row[19],
                }
                indexes.append(index)

            cursor.close()

            return {
                "status": "success",
                "table": table_name,
                "schema": schema or "current_user",
                "indexes": indexes,
                "count": len(indexes),
            }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(
                f"Database error getting table indexes: {error_obj.message}"
            )
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error getting table indexes: {e}")
            return {
                "status": "error",
                "message": f"Error getting table indexes: {str(e)}",
            }
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def describe_object(
        self,
        object_name: str,
        object_type: Optional[str] = None,
        schema: Optional[str] = None,
    ) -> Dict[str, Any]:
        """
        Show columns for tables/views or arguments for procedures/functions.

        Args:
            object_name: Name of the object to describe
            object_type: Type of object (TABLE, VIEW, PROCEDURE, FUNCTION, etc.)
            schema: Schema name (optional, defaults to current user)

        Returns:
            dict: Object description with columns or arguments
        """
        conn = None
        cursor = None

        try:
            if not object_name or not object_name.strip():
                return {"status": "error", "message": "Object name is required"}

            conn = self.pool.get_connection()
            cursor = conn.cursor()

            # Determine if we need to detect object type
            if not object_type:
                # Try to detect the object type
                if schema:
                    cursor.execute(
                        """
                        SELECT DISTINCT OBJECT_TYPE
                        FROM ALL_OBJECTS
                        WHERE OWNER = UPPER(:schema) AND OBJECT_NAME = UPPER(:object_name)
                    """,
                        schema=schema,
                        object_name=object_name,
                    )
                else:
                    cursor.execute(
                        """
                        SELECT DISTINCT OBJECT_TYPE
                        FROM USER_OBJECTS
                        WHERE OBJECT_NAME = UPPER(:object_name)
                    """,
                        object_name=object_name,
                    )

                result = cursor.fetchone()
                if not result:
                    return {
                        "status": "error",
                        "message": f"Object '{object_name}' not found",
                    }

                object_type = result[0]

            object_type = object_type.upper()

            if object_type in ["TABLE", "VIEW"]:
                # Describe table or view columns
                return self._describe_table_columns(object_name, schema)
            elif object_type in ["PROCEDURE", "FUNCTION", "PACKAGE"]:
                # Describe procedure/function arguments
                return self._describe_procedure_arguments(
                    object_name, object_type, schema
                )
            else:
                return {
                    "status": "error",
                    "message": f"Object type '{object_type}' not supported for description",
                }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error describing object: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error describing object: {e}")
            return {"status": "error", "message": f"Error describing object: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def _describe_table_columns(
        self, table_name: str, schema: Optional[str] = None
    ) -> Dict[str, Any]:
        """Helper method to describe table/view columns."""
        conn = None
        cursor = None

        try:
            conn = self.pool.get_connection()
            cursor = conn.cursor()

            if schema:
                cursor.execute(
                    """
                    SELECT
                        COLUMN_NAME,
                        DATA_TYPE,
                        DATA_TYPE_MOD,
                        DATA_TYPE_OWNER,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        NULLABLE,
                        COLUMN_ID,
                        DEFAULT_LENGTH,
                        DATA_DEFAULT
                    FROM ALL_TAB_COLUMNS
                    WHERE OWNER = UPPER(:schema) AND TABLE_NAME = UPPER(:table_name)
                    ORDER BY COLUMN_ID
                """,
                    schema=schema,
                    table_name=table_name,
                )
            else:
                cursor.execute(
                    """
                    SELECT
                        COLUMN_NAME,
                        DATA_TYPE,
                        DATA_TYPE_MOD,
                        DATA_TYPE_OWNER,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        NULLABLE,
                        COLUMN_ID,
                        DEFAULT_LENGTH,
                        DATA_DEFAULT
                    FROM USER_TAB_COLUMNS
                    WHERE TABLE_NAME = UPPER(:table_name)
                    ORDER BY COLUMN_ID
                """,
                    table_name=table_name,
                )

            columns = []
            for row in cursor:
                column = {
                    "name": row[0],
                    "data_type": row[1],
                    "data_type_mod": row[2],
                    "data_type_owner": row[3],
                    "data_length": row[4],
                    "data_precision": row[5],
                    "data_scale": row[6],
                    "nullable": row[7] == "Y",
                    "column_id": row[8],
                    "default_length": row[9],
                    "default_value": row[10],
                }
                columns.append(column)

            cursor.close()

            return {
                "status": "success",
                "object_name": table_name,
                "object_type": "TABLE",
                "schema": schema or "current_user",
                "columns": columns,
                "column_count": len(columns),
            }

        except Exception as e:
            self.logger.error(f"Error describing table columns: {e}")
            return {
                "status": "error",
                "message": f"Error describing table columns: {str(e)}",
            }
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def _describe_procedure_arguments(
        self, proc_name: str, proc_type: str, schema: Optional[str] = None
    ) -> Dict[str, Any]:
        """Helper method to describe procedure/function arguments."""
        conn = None
        cursor = None

        try:
            conn = self.pool.get_connection()
            cursor = conn.cursor()

            if schema:
                cursor.execute(
                    """
                    SELECT
                        OBJECT_NAME,
                        ARGUMENT_NAME,
                        POSITION,
                        SEQUENCE,
                        DATA_TYPE,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        IN_OUT,
                        DATA_LEVEL,
                        DEFAULTED,
                        DEFAULT_VALUE
                    FROM ALL_ARGUMENTS
                    WHERE OWNER = UPPER(:schema) AND OBJECT_NAME = UPPER(:proc_name)
                    ORDER BY SEQUENCE, POSITION
                """,
                    schema=schema,
                    proc_name=proc_name,
                )
            else:
                cursor.execute(
                    """
                    SELECT
                        OBJECT_NAME,
                        ARGUMENT_NAME,
                        POSITION,
                        SEQUENCE,
                        DATA_TYPE,
                        DATA_LENGTH,
                        DATA_PRECISION,
                        DATA_SCALE,
                        IN_OUT,
                        DATA_LEVEL,
                        DEFAULTED,
                        DEFAULT_VALUE
                    FROM USER_ARGUMENTS
                    WHERE OBJECT_NAME = UPPER(:proc_name)
                    ORDER BY SEQUENCE, POSITION
                """,
                    proc_name=proc_name,
                )

            arguments = []
            for row in cursor:
                argument = {
                    "object_name": row[0],
                    "argument_name": row[1],
                    "position": row[2],
                    "sequence": row[3],
                    "data_type": row[4],
                    "data_length": row[5],
                    "data_precision": row[6],
                    "data_scale": row[7],
                    "in_out": row[8],
                    "data_level": row[9],
                    "defaulted": row[10] == "Y" if row[10] else False,
                    "default_value": row[11],
                }
                arguments.append(argument)

            cursor.close()

            return {
                "status": "success",
                "object_name": proc_name,
                "object_type": proc_type,
                "schema": schema or "current_user",
                "arguments": arguments,
                "argument_count": len(arguments),
            }

        except Exception as e:
            self.logger.error(f"Error describing procedure arguments: {e}")
            return {
                "status": "error",
                "message": f"Error describing procedure arguments: {str(e)}",
            }
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def get_ddl(
        self, object_type: str, object_name: str, schema: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        Return DDL using DBMS_METADATA.GET_DDL.

        Args:
            object_type: Type of object (TABLE, VIEW, PROCEDURE, FUNCTION, etc.)
            object_name: Name of the object
            schema: Schema name (optional, defaults to current user)

        Returns:
            dict: DDL statement and metadata
        """
        conn = None
        cursor = None

        try:
            if not object_type or not object_name:
                return {
                    "status": "error",
                    "message": "Object type and name are required",
                }

            conn = self.pool.get_connection()
            cursor = conn.cursor()

            # Set up DBMS_METADATA to return formatted DDL
            cursor.execute(
                "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE); END;"
            )
            cursor.execute(
                "BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE); END;"
            )

            # Build the DDL query
            if schema:
                ddl_query = """
                    SELECT DBMS_METADATA.GET_DDL(:object_type, :object_name, :schema) as ddl
                    FROM DUAL
                """
                cursor.execute(
                    ddl_query,
                    {
                        "object_type": object_type.upper(),
                        "object_name": object_name.upper(),
                        "schema": schema.upper(),
                    },
                )
            else:
                ddl_query = """
                    SELECT DBMS_METADATA.GET_DDL(:object_type, :object_name) as ddl
                    FROM DUAL
                """
                cursor.execute(
                    ddl_query,
                    {
                        "object_type": object_type.upper(),
                        "object_name": object_name.upper(),
                    },
                )

            result = cursor.fetchone()
            if not result or not result[0]:
                return {
                    "status": "error",
                    "message": f"DDL not found for {object_type} '{object_name}'",
                }

            ddl_obj = result[0]

            # Debug: record raw type/repr (truncated) to help diagnose LOB handling
            try:
                self.logger.debug(
                    f"get_ddl raw result type={type(ddl_obj)}, repr(truncated)={repr(ddl_obj)[:500]}"
                )
            except Exception:
                pass

            # Normalize the DDL to a Python string safely:
            #  - If object has a read() method (e.g. cx_Oracle.LOB), call it.
            #  - If read() returns bytes, decode to utf-8.
            #  - Otherwise use str() to avoid returning DB driver objects.
            ddl_statement = ""
            try:
                read_fn = getattr(ddl_obj, "read", None)
                if callable(read_fn):
                    candidate = read_fn()
                else:
                    candidate = ddl_obj

                if isinstance(candidate, (bytes, bytearray)):
                    ddl_statement = candidate.decode("utf-8", errors="replace")
                else:
                    ddl_statement = str(candidate) if candidate is not None else ""
            except Exception as e:
                self.logger.warning(f"Failed to read/convert DDL LOB: {e}")
                try:
                    ddl_statement = str(ddl_obj)
                except Exception:
                    ddl_statement = ""

            cursor.close()

            ddl_length = len(ddl_statement) if ddl_statement is not None else 0
            return {
                "status": "success",
                "object_type": object_type.upper(),
                "object_name": object_name.upper(),
                "schema": schema or "current_user",
                "ddl": ddl_statement,
                "ddl_length": ddl_length,
            }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error getting DDL: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error getting DDL: {e}")
            return {"status": "error", "message": f"Error getting DDL: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)

    def explain_plan(self, sql_statement: str) -> Dict[str, Any]:
        """
        Run EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY.

        Args:
            sql_statement: SQL statement to explain

        Returns:
            dict: Execution plan information
        """
        conn = None
        cursor = None

        try:
            if not sql_statement or not sql_statement.strip():
                return {"status": "error", "message": "SQL statement is required"}

            conn = self.pool.get_connection()
            cursor = conn.cursor()

            # First, run EXPLAIN PLAN FOR
            explain_sql = f"EXPLAIN PLAN FOR {sql_statement}"
            cursor.execute(explain_sql)

            # Get the plan table contents
            cursor.execute("SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)")

            plan_lines = []
            for row in cursor:
                plan_lines.append(row[0] if row[0] else "")

            # Also get the plan in format for better display
            cursor.execute("SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY)")

            formatted_plan = []
            for row in cursor:
                if row[0]:  # Only add non-null lines
                    formatted_plan.append(row[0])

            cursor.close()

            plan_text = "\n".join(formatted_plan)

            return {
                "status": "success",
                "sql_statement": sql_statement,
                "plan": plan_text,
                "plan_lines": len(formatted_plan),
                "plan_table_lines": len(plan_lines),
            }

        except cx_Oracle.DatabaseError as e:
            (error_obj,) = e.args
            self.logger.error(f"Database error explaining plan: {error_obj.message}")
            return {
                "status": "error",
                "message": f"Database error: {error_obj.message}",
                "code": error_obj.code,
            }
        except Exception as e:
            self.logger.error(f"Error explaining plan: {e}")
            return {"status": "error", "message": f"Error explaining plan: {str(e)}"}
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                self.pool.release_connection(conn)
