"""
Question Models
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
from sqlalchemy.sql import func
from app.core.database import Base


class Question(Base):
    """Questions table"""
    __tablename__ = "questions"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Text, nullable=False)
    agent_id = Column(Text, nullable=False)
    username = Column(Text, nullable=False)
    question = Column(Text, nullable=False)
    sql = Column(Text)
    chart = Column(Text, nullable=False)
    timestamp = Column(Text, nullable=False)
    api_endpoint = Column(Text)
    api_params = Column(Text)
    api_response_validator = Column(Text)
    sql_query_summaries = Column(Text, nullable=True)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())


class EmailQuestion(Base):
    """Email questions table"""
    __tablename__ = "email_questions"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    question = Column(Text, nullable=False)
    username = Column(Text, nullable=False)
    user_id = Column(Text, nullable=False)
    schedule_time = Column(DateTime, nullable=False)
    distribution_list_id = Column(Integer, ForeignKey("distribution_lists.id"), nullable=False)
    status = Column(Text, default="pending")
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
    sent_at = Column(DateTime)


class UserQuestionLog(Base):
    """User question log table"""
    __tablename__ = "user_question_log"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Text, nullable=False)
    agent_id = Column(Text, nullable=False)
    question = Column(Text, nullable=False)
    sql = Column(Text, nullable=False)
    chart = Column(Text, nullable=False)
    web = Column(Text, nullable=False)
    executed = Column(Text, nullable=False)
    execution_time = Column(Text, nullable=False)
    api_endpoint = Column(Text, nullable=False)
    api_params = Column(Text, nullable=False)
    api_response_validator = Column(Text, nullable=False)
    # Normalized question key for reuse cache (trim, lower, whitespace → underscores). Nullable until backfilled.
    question_normalized = Column(Text, nullable=True)
    input_tokens = Column(Integer, nullable=True)
    output_tokens = Column(Integer, nullable=True)
    total_tokens = Column(Integer, nullable=True)
    sql_query_summaries = Column(Text, nullable=True)
    created_at = Column(Text, nullable=False)
    updated_at = Column(Text, nullable=False)


class InitialSuggestionQuestion(Base):
    """Initial suggestion questions table"""
    __tablename__ = "initial_suggestion_question"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    question = Column(Text, nullable=False)
    sql = Column(Text, nullable=False)
    api_endpoint = Column(Text, nullable=False)
    api_params = Column(Text, nullable=False)
    api_response_validator = Column(Text, nullable=False)
    agent_id = Column(Text, nullable=False)
    question_data_source = Column(Text, nullable=False)
    question_type = Column(Text, nullable=False)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())


class TalkbackSessionState(Base):
    """Server-side session state for talkback orchestration."""
    __tablename__ = "talkback_session_state"

    id = Column(Integer, primary_key=True, autoincrement=True)
    session_key = Column(Text, nullable=False, unique=True, index=True)
    user_id = Column(Text, nullable=False, index=True)
    agent_scope = Column(Text, nullable=False, default="")
    openai_response_id = Column(Text, nullable=True)
    history_json = Column(Text, nullable=False, default="[]")
    pending_clarification = Column(Integer, nullable=False, default=0)
    clarification_prompt = Column(Text, nullable=True)
    clarification_context_json = Column(Text, nullable=True)
    created_at = Column(DateTime, default=func.now())
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
