import datetime
from dataclasses import dataclass, field, fields
from typing import Any, Optional
from uuid import UUID, uuid4

from dataiku.sql import Constant, Dialects, toSQL

from solutions.graph.models import GraphId


def _escape_sql_value(value: Any) -> Optional[Any]:
    """
    Escapes a value for safe SQL insertion.

    Args:
        value: The value to escape.

    Returns:
        The escaped value, or None if the input is None.
    """
    if value is None:
        return None
    elif isinstance(value, (int, float, str, bool)):
        return value
    else:
        return str(value)

@dataclass
class LoggingDatasetModel:
    user: str
    title: str
    llm_name: str

    question: str
    answer: str

    graph_id: GraphId

    uuid: UUID = field(default_factory=uuid4)
    created_at: datetime.datetime = field(default_factory=datetime.datetime.now)

    feedback_value: Optional[str] = None
    feedback_choice: Optional[str] = None
    feedback_message: Optional[str] = None
    
    def to_sql_insert(self, table_name: str, dialect: Dialects) -> str:
        columns = [f.name for f in fields(self)]
        
        pre_escaped_values = [
            _escape_sql_value(getattr(self, col)) for col in columns
        ]

        # Prevent security issues
        escaped_values = [
            toSQL(Constant(value), dialect=dialect) for value in pre_escaped_values
        ]

        column_str = ", ".join(f'"{col}"' for col in columns)
        values_str = ", ".join(escaped_values)

        query = f'INSERT INTO {table_name} ({column_str}) VALUES ({values_str});'

        return query

    @classmethod
    def get_columns(cls) -> dict[str, str]:
        """
        Returns a dict mapping field names to pandas-compatible dtype strings.
        """
        type_map: dict[type, str] = {
            int: "int64",
            float: "float64",
            str: "str",
            bool: "bool",
            bytes: "bytes",
            datetime.datetime: "datetime64[ns]",
            datetime.date: "datetime64[ns]",
            UUID: "object",
        }

        result: dict[str, str] = {}
        for f in fields(cls):
            pandas_dtype = type_map.get(f.type, "object") #type: ignore
            result[f.name] = pandas_dtype

        return result
