"""Agent 会话与消息表（FR-AGT-09 / T-P4-01）。

  ag_conversation — 会话主表；title 取首条用户消息截断，last_message_at 是
                    列表排序与 90 天保留期清理（合规方案 §6）的时间锚点
  ag_message      — 消息表；retrieval_trace / rule_hits 是「每条回答可复盘」
                    的锚点，检索步骤（架构 §7.2 ④）落地前留空；assistant 行
                    以 call_id 关联 ob_ai_call，回复可一路追到成本账本

Reversible: 是。
"""

from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0007"
down_revision: str | None = "0006"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "ag_conversation",
        sa.Column("conversation_id", sa.BigInteger(), nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("title", sa.String(length=64), nullable=False, server_default=sa.text("''")),
        sa.Column("message_count", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("last_message_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
        sa.PrimaryKeyConstraint("conversation_id"),
    )
    op.create_index(
        "ix_ag_conversation_user_active", "ag_conversation", ["user_id", "last_message_at"]
    )

    op.create_table(
        "ag_message",
        sa.Column("message_id", sa.BigInteger(), nullable=False),
        sa.Column("conversation_id", sa.BigInteger(), nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("role", sa.String(length=16), nullable=False),
        sa.Column("content", sa.Text(), nullable=False),
        sa.Column("call_id", sa.BigInteger(), nullable=True),
        sa.Column("model", sa.String(length=64), nullable=True),
        sa.Column("tokens_in", sa.Integer(), nullable=True),
        sa.Column("tokens_out", sa.Integer(), nullable=True),
        sa.Column("tokens_cached", sa.Integer(), nullable=True),
        sa.Column("latency_ms", sa.Integer(), nullable=True),
        sa.Column("cost_cents", sa.Numeric(14, 6), nullable=True),
        sa.Column("retrieval_trace", postgresql.JSONB(), nullable=False),
        sa.Column("rule_hits", postgresql.JSONB(), nullable=False),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
        ),
        sa.PrimaryKeyConstraint("message_id"),
        sa.ForeignKeyConstraint(
            ["conversation_id"], ["ag_conversation.conversation_id"], ondelete="CASCADE"
        ),
    )
    op.create_index(
        "ix_ag_message_conversation_created", "ag_message", ["conversation_id", "created_at"]
    )
    op.create_index("ix_ag_message_user_created", "ag_message", ["user_id", "created_at"])


def downgrade() -> None:
    op.drop_index("ix_ag_message_user_created", table_name="ag_message")
    op.drop_index("ix_ag_message_conversation_created", table_name="ag_message")
    op.drop_table("ag_message")
    op.drop_index("ix_ag_conversation_user_active", table_name="ag_conversation")
    op.drop_table("ag_conversation")