from sqlalchemy import (
  Column, String, Boolean, DateTime, Integer,
  Text, ForeignKey, Index
)
from sqlalchemy.sql import func
from .base import Base

class ChatSession(Base):
  __tablename__ = "chat_sessions"
  
  id = Column(String(36), primary_key=True)
  # UUID
  user_hash = Column(String(64), nullable=False, index=True)
  tenant_id = Column(String(64), nullable=True, index=True)
  
  # Title (auto-generated by ChatIntelligence)
  title = Column(String(200), default="New Conversation")
  
  # Context
  project_id = Column(String(36), nullable=True, index=True)
  tier_context = Column(String(20), default="personal")
  # personal | business | institutional
  
  # Organization
  status = Column(String(30), default="active")
  # active | waiting_data | waiting_confirm | completed | archived
  
  is_pinned = Column(Boolean, default=False)
  is_starred = Column(Boolean, default=False)
  
  # Auto-generated metadata
  thread_summary = Column(Text)
  tags = Column(Text, default="[]")
  # JSON list: ["tax", "paye", "salary"]
  
  # Linked content
  linked_files = Column(Text, default="[]")
  linked_project_id = Column(String(36), nullable=True)
  
  # Stats
  message_count = Column(Integer, default=0)
  
  # Reminders
  reminder_date = Column(DateTime, nullable=True)
  reminder_note = Column(String(500), nullable=True)
  
  # Timestamps
  created_at = Column(DateTime, server_default=func.now())
  updated_at = Column(DateTime, onupdate=func.now())
  last_message_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_chats_user", "user_hash"),
    Index("ix_chats_user_pinned", "user_hash", "is_pinned"),
    Index("ix_chats_user_last", "user_hash", "last_message_at"),
    Index("ix_chats_project", "project_id"),
    Index("ix_chats_tenant", "tenant_id"),
    Index("ix_chats_status", "status"),
  )

class Message(Base):
  __tablename__ = "messages"
  
  id = Column(Integer, primary_key=True)
  chat_session_id = Column(String(36), nullable=False, index=True)
  user_hash = Column(String(64), nullable=False, index=True)
  
  role = Column(String(20), nullable=False)
  # user | assistant | system
  
  content = Column(Text, nullable=False)
  
  # Structured outputs (from Senti responses)
  calculations = Column(Text)
  # JSON: list of calculation results
  generated_files = Column(Text)
  # JSON: list of file names
  missing_data = Column(Text)
  # JSON: list of missing fields
  
  # Source tracking
  engine_used = Column(String(50))
  # deterministic | rag | web_search | llm | hybrid
  sources_cited = Column(Text)
  # JSON: list of sources
  
  # Quality
  user_rating = Column(Integer, nullable=True)
  # 1 (thumbs down) | 5 (thumbs up)
  
  created_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_messages_session", "chat_session_id"),
    Index("ix_messages_user", "user_hash"),
    Index("ix_messages_created", "created_at"),
  )

class StarredMessage(Base):
  __tablename__ = "starred_messages"
  
  id = Column(Integer, primary_key=True)
  user_hash = Column(String(64), nullable=False, index=True)
  chat_session_id = Column(String(36), nullable=False, index=True)
  message_id = Column(Integer, nullable=False)
  content_preview = Column(String(300))
  note = Column(String(200))
  starred_at = Column(DateTime, server_default=func.now())
  
  __table_args__ = (
    Index("ix_starred_user", "user_hash"),
  )