"""
Database schema management for rateloader.

Provides functionality to execute SQL schema scripts with dependency ordering
and generate database objects from application data models.
"""

import os
import re
from datetime import datetime
from typing import List, Dict, Set, Optional, Tuple
from dataclasses import dataclass
from enum import Enum

from core.oracle import OracleWrapper, create_oracle_wrapper
from core.logger import get_rateloader_logger
from core.rateloader_schema import get_embedded_schema


class SQLObjectType(Enum):
    """Types of SQL objects that can be created."""
    SEQUENCE = "sequence"
    TABLE = "table"
    INDEX = "index"
    VIEW = "view"
    FUNCTION = "function"
    PROCEDURE = "procedure"
    TRIGGER = "trigger"
    CONSTRAINT = "constraint"


@dataclass
class SQLObject:
    """Represents a SQL object with its dependencies."""
    name: str
    object_type: SQLObjectType
    sql_content: str
    dependencies: Set[str]
    line_number: int


class DependencyGraph:
    """Manages SQL object dependencies and execution order."""

    def __init__(self):
        self.objects: Dict[str, SQLObject] = {}
        self.dependency_map: Dict[str, Set[str]] = {}

    def add_object(self, obj: SQLObject):
        """Add a SQL object to the dependency graph."""
        self.objects[obj.name] = obj
        self.dependency_map[obj.name] = obj.dependencies.copy()

    def get_execution_order(self) -> List[str]:
        """
        Get the execution order based on dependencies using topological sort.

        Returns:
            List of object names in execution order
        """
        # Kahn's algorithm for topological sorting
        in_degree = {name: 0 for name in self.objects}
        for deps in self.dependency_map.values():
            for dep in deps:
                if dep in in_degree:
                    in_degree[dep] += 1

        queue = [name for name, degree in in_degree.items() if degree == 0]
        result = []

        while queue:
            current = queue.pop(0)
            result.append(current)

            # Find objects that depend on current (reverse dependencies)
            dependents = []
            for name, deps in self.dependency_map.items():
                if current in deps:
                    dependents.append(name)

            for dependent in dependents:
                in_degree[dependent] -= 1
                if in_degree[dependent] == 0:
                    queue.append(dependent)

        if len(result) != len(self.objects):
            raise ValueError("Circular dependency detected in SQL objects")

        return result


class DatabaseSchemaManager:
    """
    Manages database schema operations including SQL script execution
    and schema generation from data models.
    """

    def __init__(self, sql_file_path: Optional[str] = None):
        """
        Initialize the schema manager.

        Args:
            sql_file_path: Path to the SQL schema file. If None, uses embedded schema.
        """
        self.sql_file_path = sql_file_path
        self.logger = get_rateloader_logger()
        self.oracle_wrapper: Optional[OracleWrapper] = None

    def connect(self) -> bool:
        """
        Establish database connection.

        Returns:
            True if connection successful, False otherwise
        """
        try:
            self.oracle_wrapper = create_oracle_wrapper()
            return self.oracle_wrapper.connect()
        except Exception as e:
            self.logger.error(f"Failed to connect to database: {str(e)}")
            return False

    def disconnect(self):
        """Close database connection."""
        if self.oracle_wrapper:
            self.oracle_wrapper.disconnect()
            self.oracle_wrapper = None

    def parse_sql_file(self) -> DependencyGraph:
        """
        Parse the SQL file and build a dependency graph.

        Returns:
            DependencyGraph containing all SQL objects with dependencies
        """
        graph = DependencyGraph()

        try:
            # Use embedded schema if no file path provided
            if self.sql_file_path is None:
                content = get_embedded_schema()
                self.logger.info("Using embedded SQL schema")
            else:
                if not os.path.exists(self.sql_file_path):
                    raise FileNotFoundError(f"SQL file not found: {self.sql_file_path}")
                with open(self.sql_file_path, 'r', encoding='utf-8') as f:
                    content = f.read()

            # Split into individual statements (basic approach)
            statements = self._split_sql_statements(content)

            for i, (statement, line_num) in enumerate(statements):
                obj = self._parse_sql_statement(statement.strip(), line_num)
                if obj:
                    graph.add_object(obj)

        except Exception as e:
            self.logger.error(f"Error parsing SQL file: {str(e)}")
            raise

        return graph

    def _split_sql_statements(self, content: str) -> List[Tuple[str, int]]:
        """
        Split SQL content into individual statements.

        Returns:
            List of (statement, line_number) tuples
        """
        statements = []
        current_statement = []
        line_number = 1
        start_line = 1
        in_comment = False
        in_string = False
        string_char = None

        lines = content.split('\n')

        for line in lines:
            stripped = line.strip()

            # Handle multi-line comments
            if '/*' in line and not in_string:
                in_comment = True
            if '*/' in line and in_comment:
                in_comment = False
                line_number += 1
                continue

            if in_comment:
                line_number += 1
                continue

            # Handle string literals
            if not in_string and (line.count("'") % 2 == 1 or line.count('"') % 2 == 1):
                in_string = True
                string_char = "'" if "'" in line else '"'
            elif in_string and string_char in line:
                in_string = False
                string_char = None

            current_statement.append(line)

            # Check for statement end
            if stripped.endswith(';') and not in_string:
                statement = '\n'.join(current_statement)
                statements.append((statement, start_line))
                current_statement = []
                start_line = line_number + 1

            line_number += 1

        return statements

    def _parse_sql_statement(self, statement: str, line_number: int) -> Optional[SQLObject]:
        """
        Parse a single SQL statement and extract object information.

        Args:
            statement: SQL statement to parse
            line_number: Line number where statement starts

        Returns:
            SQLObject if statement creates a database object, None otherwise
        """
        statement_upper = statement.upper()

        # Extract object type and name
        object_patterns = {
            SQLObjectType.SEQUENCE: r'CREATE\s+SEQUENCE\s+(\w+)',
            SQLObjectType.TABLE: r'CREATE\s+TABLE\s+(\w+)',
            SQLObjectType.VIEW: r'CREATE\s+(?:OR\s+REPLACE\s+)?VIEW\s+(\w+)',
            SQLObjectType.FUNCTION: r'CREATE\s+(?:OR\s+REPLACE\s+)?FUNCTION\s+(\w+)',
            SQLObjectType.PROCEDURE: r'CREATE\s+(?:OR\s+REPLACE\s+)?PROCEDURE\s+(\w+)',
            SQLObjectType.TRIGGER: r'CREATE\s+(?:OR\s+REPLACE\s+)?TRIGGER\s+(\w+)',
        }

        for obj_type, pattern in object_patterns.items():
            match = re.search(pattern, statement_upper, re.IGNORECASE)
            if match:
                obj_name = match.group(1)
                dependencies = self._extract_dependencies(statement)
                return SQLObject(
                    name=obj_name,
                    object_type=obj_type,
                    sql_content=statement,
                    dependencies=dependencies,
                    line_number=line_number
                )

        # Handle ALTER TABLE for constraints and indexes
        if statement_upper.startswith('ALTER TABLE'):
            # This could be adding constraints or indexes
            # For now, we'll treat it as a constraint
            table_match = re.search(r'ALTER\s+TABLE\s+(\w+)', statement_upper, re.IGNORECASE)
            if table_match:
                table_name = table_match.group(1)
                return SQLObject(
                    name=f"alter_{table_name}_{line_number}",
                    object_type=SQLObjectType.CONSTRAINT,
                    sql_content=statement,
                    dependencies={table_name},
                    line_number=line_number
                )

        return None

    def _extract_dependencies(self, statement: str) -> Set[str]:
        """
        Extract dependencies from a SQL statement.

        Args:
            statement: SQL statement to analyze

        Returns:
            Set of dependency names
        """
        dependencies = set()
        statement_upper = statement.upper()

        # Common dependency patterns
        patterns = [
            r'REFERENCES\s+(\w+)',  # Foreign key references
            r'FROM\s+(\w+)',        # Table references in views/functions
            r'JOIN\s+(\w+)',        # Join references
            r'INTO\s+(\w+)',        # Insert into references
            r'UPDATE\s+(\w+)',      # Update references
            r'DELETE\s+FROM\s+(\w+)', # Delete references
        ]

        for pattern in patterns:
            matches = re.findall(pattern, statement_upper, re.IGNORECASE)
            dependencies.update(matches)

        return dependencies

    def execute_schema(self, graph: Optional[DependencyGraph] = None) -> Dict[str, any]:
        """
        Execute the database schema in dependency order.

        Args:
            graph: Pre-parsed dependency graph, or None to parse from file

        Returns:
            Dictionary with execution results
        """
        if not self.oracle_wrapper or not self.oracle_wrapper.is_connected():
            raise ConnectionError("Database connection not established")

        if graph is None:
            graph = self.parse_sql_file()

        execution_order = graph.get_execution_order()
        results = {
            "total_objects": len(execution_order),
            "successful": 0,
            "failed": 0,
            "errors": [],
            "executed_objects": []
        }

        self.logger.info(f"Starting schema execution with {len(execution_order)} objects")

        for obj_name in execution_order:
            obj = graph.objects[obj_name]

            try:
                self.logger.info(f"Executing {obj.object_type.value}: {obj_name}")

                # Execute the SQL statement
                cursor = self.oracle_wrapper.connection.cursor()
                cursor.execute(obj.sql_content)
                self.oracle_wrapper.connection.commit()
                cursor.close()

                results["successful"] += 1
                results["executed_objects"].append({
                    "name": obj_name,
                    "type": obj.object_type.value,
                    "status": "success",
                    "line_number": obj.line_number
                })

                self.logger.info(f"Successfully executed {obj_name}")

            except Exception as e:
                error_msg = f"Failed to execute {obj_name}: {str(e)}"
                self.logger.error(error_msg)

                results["failed"] += 1
                results["errors"].append({
                    "object": obj_name,
                    "type": obj.object_type.value,
                    "error": str(e),
                    "line_number": obj.line_number
                })

        self.logger.info(f"Schema execution completed. Success: {results['successful']}, Failed: {results['failed']}")
        return results

    def generate_schema_from_model(self, data_model: Dict) -> str:
        """
        Generate SQL schema from application data model.

        Args:
            data_model: Dictionary describing the data model with structure:
                {
                    "tables": {
                        "table_name": {
                            "columns": [
                                {"name": "col_name", "type": "VARCHAR2(100)", "nullable": False, "default": None},
                                ...
                            ],
                            "primary_key": ["col1", "col2"],
                            "indexes": [
                                {"name": "idx_name", "columns": ["col1"], "unique": False},
                                ...
                            ],
                            "constraints": [
                                {"name": "fk_name", "type": "FOREIGN KEY", "columns": ["col1"], "references": "other_table(col)"},
                                ...
                            ]
                        }
                    },
                    "sequences": ["seq_name1", "seq_name2"],
                    "views": {
                        "view_name": "SELECT * FROM table_name WHERE condition"
                    }
                }

        Returns:
            Generated SQL schema as string
        """
        try:
            sql_parts = []
            sql_parts.append("-- Generated SQL Schema from Data Model")
            sql_parts.append(f"-- Generated on: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
            sql_parts.append("")

            # Generate sequences
            if "sequences" in data_model:
                for seq_name in data_model["sequences"]:
                    sql_parts.append(f"CREATE SEQUENCE {seq_name};")
                sql_parts.append("")

            # Generate tables
            if "tables" in data_model:
                for table_name, table_def in data_model["tables"].items():
                    sql_parts.extend(self._generate_table_sql(table_name, table_def))
                    sql_parts.append("")

            # Generate views
            if "views" in data_model:
                for view_name, view_sql in data_model["views"].items():
                    sql_parts.append(f"CREATE OR REPLACE VIEW {view_name} AS")
                    sql_parts.append(f"{view_sql};")
                    sql_parts.append("")

            # Generate indexes (separate from tables for better organization)
            if "tables" in data_model:
                for table_name, table_def in data_model["tables"].items():
                    if "indexes" in table_def:
                        for index_def in table_def["indexes"]:
                            index_sql = self._generate_index_sql(table_name, index_def)
                            if index_sql:
                                sql_parts.append(index_sql)
                if any("indexes" in t for t in data_model["tables"].values()):
                    sql_parts.append("")

            generated_sql = "\n".join(sql_parts)
            self.logger.info(f"Generated SQL schema with {len(generated_sql.split(';'))-1} statements")
            return generated_sql

        except Exception as e:
            error_msg = f"Error generating schema from data model: {str(e)}"
            self.logger.error(error_msg)
            return f"-- {error_msg}"

    def _generate_table_sql(self, table_name: str, table_def: Dict) -> List[str]:
        """
        Generate CREATE TABLE SQL for a single table.

        Args:
            table_name: Name of the table
            table_def: Table definition dictionary

        Returns:
            List of SQL statements for the table
        """
        sql_lines = []
        sql_lines.append(f"CREATE TABLE {table_name} (")

        # Generate column definitions
        column_defs = []
        if "columns" in table_def:
            for col in table_def["columns"]:
                col_def = f"  {col['name']} {col['type']}"

                if not col.get("nullable", True):
                    col_def += " NOT NULL"

                if "default" in col and col["default"] is not None:
                    if isinstance(col["default"], str):
                        col_def += f" DEFAULT '{col['default']}'"
                    else:
                        col_def += f" DEFAULT {col['default']}"

                column_defs.append(col_def)

        # Add primary key constraint
        if "primary_key" in table_def and table_def["primary_key"]:
            pk_cols = ", ".join(table_def["primary_key"])
            column_defs.append(f"  CONSTRAINT pk_{table_name} PRIMARY KEY ({pk_cols})")

        # Add other constraints
        if "constraints" in table_def:
            for constraint in table_def["constraints"]:
                if constraint["type"].upper() == "FOREIGN KEY":
                    fk_cols = ", ".join(constraint["columns"])
                    column_defs.append(f"  CONSTRAINT {constraint['name']} FOREIGN KEY ({fk_cols}) REFERENCES {constraint['references']}")

        # Join all column/constraint definitions
        sql_lines.append(",\n".join(column_defs))
        sql_lines.append(");")

        return sql_lines

    def _generate_index_sql(self, table_name: str, index_def: Dict) -> Optional[str]:
        """
        Generate CREATE INDEX SQL for a single index.

        Args:
            table_name: Name of the table
            index_def: Index definition dictionary

        Returns:
            SQL statement for the index, or None if invalid
        """
        try:
            index_name = index_def.get("name", f"idx_{table_name}_{'_'.join(index_def['columns'])}")
            columns = ", ".join(index_def["columns"])
            unique = "UNIQUE " if index_def.get("unique", False) else ""

            return f"CREATE {unique}INDEX {index_name} ON {table_name} ({columns});"

        except KeyError as e:
            self.logger.warning(f"Invalid index definition for table {table_name}: missing {e}")
            return None

    def validate_schema(self) -> Dict[str, any]:
        """
        Validate the current database schema against expected structure.

        Returns:
            Validation results
        """
        if not self.oracle_wrapper or not self.oracle_wrapper.is_connected():
            raise ConnectionError("Database connection not established")

        results = {
            "valid": True,
            "missing_objects": [],
            "extra_objects": [],
            "issues": []
        }

        try:
            # Query for existing objects
            query = """
            SELECT object_type, object_name
            FROM all_objects
            WHERE owner = USER
            AND object_type IN ('TABLE', 'VIEW', 'SEQUENCE', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
            ORDER BY object_type, object_name
            """

            existing_objects = {}
            for row in self.oracle_wrapper.stream_query(query):
                obj_type, obj_name = row
                if obj_type not in existing_objects:
                    existing_objects[obj_type] = set()
                existing_objects[obj_type].add(obj_name)

            # Compare with expected schema
            graph = self.parse_sql_file()
            expected_objects = {}
            for obj in graph.objects.values():
                obj_type = obj.object_type.value.upper()
                if obj_type not in expected_objects:
                    expected_objects[obj_type] = set()
                expected_objects[obj_type].add(obj.name)

            # Check for missing objects
            for obj_type, expected_names in expected_objects.items():
                existing_names = existing_objects.get(obj_type, set())
                missing = expected_names - existing_names
                if missing:
                    results["missing_objects"].extend([f"{obj_type}.{name}" for name in missing])

            # Check for extra objects (optional - might be too strict)
            # for obj_type, existing_names in existing_objects.items():
            #     expected_names = expected_objects.get(obj_type, set())
            #     extra = existing_names - expected_names
            #     if extra:
            #         results["extra_objects"].extend([f"{obj_type}.{name}" for name in extra])

            results["valid"] = len(results["missing_objects"]) == 0

        except Exception as e:
            results["valid"] = False
            results["issues"].append(f"Validation error: {str(e)}")
            self.logger.error(f"Schema validation failed: {str(e)}")

        return results

    def __enter__(self):
        """Context manager entry."""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """Context manager exit."""
        self.disconnect()


# Convenience functions
def execute_sql_schema(sql_file_path: str = "docs/rateloader.sql") -> Dict[str, any]:
    """
    Execute SQL schema from file.

    Args:
        sql_file_path: Path to SQL schema file

    Returns:
        Execution results
    """
    with DatabaseSchemaManager(sql_file_path) as manager:
        graph = manager.parse_sql_file()
        return manager.execute_schema(graph)


def validate_database_schema(sql_file_path: str = "docs/rateloader.sql") -> Dict[str, any]:
    """
    Validate database schema against expected structure.

    Args:
        sql_file_path: Path to SQL schema file

    Returns:
        Validation results
    """
    with DatabaseSchemaManager(sql_file_path) as manager:
        return manager.validate_schema()