"""ag_conversation / ag_message 的数据访问。**只有本模块可以触这两张表**。"""

from __future__ import annotations

from datetime import UTC, datetime
from decimal import Decimal

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from packages.shared.models import AgConversation, AgMessage

ROLE_USER = "user"
ROLE_ASSISTANT = "assistant"


async def create_conversation(
    session: AsyncSession, *, conversation_id: int, user_id: int
) -> AgConversation:
    conversation = AgConversation(conversation_id=conversation_id, user_id=user_id)
    session.add(conversation)
    await session.flush()
    return conversation


async def get_conversation(session: AsyncSession, conversation_id: int) -> AgConversation | None:
    return await session.get(AgConversation, conversation_id)


async def list_conversations(
    session: AsyncSession, *, user_id: int, limit: int = 20
) -> list[AgConversation]:
    """会话列表：按最近活跃倒序；从未发言的会话（last_message_at 为 NULL）排最后。"""
    result = await session.execute(
        select(AgConversation)
        .where(AgConversation.user_id == user_id)
        .order_by(AgConversation.last_message_at.desc().nulls_last())
        .limit(limit)
    )
    return list(result.scalars().all())


async def list_messages(
    session: AsyncSession, *, conversation_id: int, limit: int = 100
) -> list[AgMessage]:
    """历史接口用：按时间正序返回。"""
    result = await session.execute(
        select(AgMessage)
        .where(AgMessage.conversation_id == conversation_id)
        .order_by(AgMessage.created_at.asc(), AgMessage.message_id.asc())
        .limit(limit)
    )
    return list(result.scalars().all())


async def list_recent_messages(
    session: AsyncSession, *, conversation_id: int, limit: int
) -> list[AgMessage]:
    """上下文组装用：取最近 ``limit`` 条，返回时按时间正序（旧→新）。"""
    result = await session.execute(
        select(AgMessage)
        .where(AgMessage.conversation_id == conversation_id)
        .order_by(AgMessage.created_at.desc(), AgMessage.message_id.desc())
        .limit(limit)
    )
    return list(reversed(result.scalars().all()))


async def append_message(
    session: AsyncSession,
    *,
    message_id: int,
    conversation: AgConversation,
    role: str,
    content: str,
    call_id: int | None = None,
    model: str | None = None,
    tokens_in: int | None = None,
    tokens_out: int | None = None,
    tokens_cached: int | None = None,
    latency_ms: int | None = None,
    cost_cents: Decimal | None = None,
) -> AgMessage:
    """追加消息并推进会话的 message_count / last_message_at / title。

    title 只在首条用户消息时落定（截断前 20 字），之后不再改。
    """
    message = AgMessage(
        message_id=message_id,
        conversation_id=conversation.conversation_id,
        user_id=conversation.user_id,
        role=role,
        content=content,
        call_id=call_id,
        model=model,
        tokens_in=tokens_in,
        tokens_out=tokens_out,
        tokens_cached=tokens_cached,
        latency_ms=latency_ms,
        cost_cents=cost_cents,
    )
    session.add(message)
    conversation.message_count += 1
    conversation.last_message_at = datetime.now(UTC)
    if not conversation.title and role == ROLE_USER:
        conversation.title = content[:20]
    await session.flush()
    return message