app/models/database.py

# app/models/database.py
from datetime import datetime, timezone
import uuid
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
    Text,
    DateTime,
    Float,
    Boolean,
    ForeignKey,
    MetaData,
)

# from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, declarative_base
from sqlalchemy.dialects.postgresql import UUID

# Absolute import from project root
from app.utils.config import get_logger, config_manager

config = config_manager.get_config()

logger = get_logger(__name__)

convention = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}

Base = declarative_base(metadata=MetaData(naming_convention=convention))

engine = create_engine(
    config.database_url,
    echo=False,  # Set to True for SQL debugging
    pool_pre_ping=True,  # Verify connections before use
    pool_recycle=300,  # Recycle connections every 5 minutes
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


class User(Base):
    __tablename__ = "users"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    session_id = Column(String(255), unique=True, index=True)
    preferred_language = Column(String(10), default="en")
    first_seen = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    last_seen = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    total_messages = Column(Integer, default=0)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id"), nullable=True)

    # Relationships
    conversations = relationship(
        "Conversation", back_populates="user", cascade="all, delete-orphan"
    )
    preferences = relationship(
        "UserPreference", back_populates="user", cascade="all, delete-orphan"
    )
    insights = relationship(
        "UserInsight", back_populates="user", cascade="all, delete-orphan"
    )
    tenant = relationship("Tenant", back_populates="users")

    def __repr__(self):
        return (
            f"<User(session_id={self.session_id}, language={self.preferred_language})>"
        )


class Conversation(Base):
    __tablename__ = "conversations"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    started_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    last_message_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    message_count = Column(Integer, default=0)
    is_active = Column(Boolean, default=True)
    tenant_id = Column(UUID(as_uuid=True), ForeignKey("tenants.id"), nullable=True)

    # Relationship
    user = relationship("User", back_populates="conversations")
    messages = relationship(
        "Message", back_populates="conversation", cascade="all, delete-orphan"
    )
    topics = relationship(
        "ConversationTopic", back_populates="conversation", cascade="all, delete-orphan"
    )
    tenant = relationship("Tenant", back_populates="conversations")

    def __repr__(self):
        return f"<Conversation(id={self.id}, user_id={self.user_id}, messages={self.message_count})>"


class Message(Base):
    __tablename__ = "messages"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    conversation_id = Column(
        UUID(as_uuid=True), ForeignKey("conversations.id"), nullable=False
    )

    # Message content
    user_input = Column(Text, nullable=False)
    bot_response = Column(Text, nullable=False)

    # NLP analysis
    detected_language = Column(String(10))
    intent = Column(String(100))
    confidence = Column(Float)
    entities = Column(Text)  # JSON string

    # Metadata
    timestamp = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    response_time_ms = Column(Integer)  # How long the bot took to respond

    # Relationships
    conversation = relationship("Conversation", back_populates="messages")

    def __repr__(self):
        return f"<Message(id={self.id}, intent={self.intent}, confidence={self.confidence})>"


def init_database():
    # create all tables in the database
    Base.metadata.create_all(bind=engine)
    logger.info("Database tables created successfully")


def health_check():
    """Check if database connection is working"""
    try:
        with SessionLocal() as session:
            from sqlalchemy import text  # pylint: disable=import-outside-toplevel

            session.execute(text("SELECT 1"))
        logger.info("Database health check passed")
        return True
    except Exception as e:  # pylint: disable=broad-exception-caught
        logger.error("Database health check failed: %s: %s", type(e).__name__, str(e))
        return False


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()