"""Persist assistant conversations so an administrator can audit what was said.

Revision ID: 488
Revises: 487

The audit spine already records that a turn happened. It deliberately does not
record the transcript: free employee text does not belong in ``audit_logs``, and
an answer's evidence (which tools ran, which model, what it cost) has a shape of
its own. These two tables carry that, and are what the admin conversation-audit
surface reads.

``seq`` exists because both messages of one turn are written inside the same
clock second: ``created_at`` cannot order them, and a transcript that renders
the answer above the question is not an audit record. The server assigns it.

``chat_id`` is only an external correlation handle, so it stays a plain string;
the FK columns take their type and collation from the parent tables via
``id_type_for`` — a mixed-collation child is exactly what broke the 2026-07-29
deploy on TiDB while passing on local MySQL.
"""
import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "488"
down_revision = "487"
branch_labels = None
depends_on = None

CONVERSATIONS = "assistant_conversations"
MESSAGES = "assistant_messages"


def _table_exists(bind, table: str) -> bool:
    return sa.inspect(bind).has_table(table)


def upgrade() -> None:
    bind = op.get_bind()
    person_id = id_type_for("identity_persons")

    if not _table_exists(bind, CONVERSATIONS):
        op.create_table(
            CONVERSATIONS,
            sa.Column("id", person_id, primary_key=True),
            sa.Column(
                "person_id", person_id,
                sa.ForeignKey("identity_persons.id"), nullable=False,
            ),
            sa.Column(
                "channel", sa.String(32), nullable=False, server_default="feishu",
            ),
            sa.Column("chat_id", sa.String(128)),
            sa.Column("turn_count", sa.Integer, nullable=False, server_default="0"),
            sa.Column(
                "started_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.Column(
                "last_message_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            **table_kw_for("identity_persons"),
        )
        op.create_index(
            "idx_assistant_conv_person", CONVERSATIONS,
            ["person_id", "last_message_at"],
        )
        op.create_index(
            "idx_assistant_conv_chat", CONVERSATIONS, ["channel", "chat_id"],
        )

    if not _table_exists(bind, MESSAGES):
        conversation_id = id_type_for(CONVERSATIONS)
        op.create_table(
            MESSAGES,
            sa.Column("id", conversation_id, primary_key=True),
            sa.Column(
                "conversation_id", conversation_id,
                sa.ForeignKey(f"{CONVERSATIONS}.id"), nullable=False,
            ),
            sa.Column("seq", sa.Integer, nullable=False, server_default="0"),
            sa.Column("role", sa.String(16), nullable=False),
            sa.Column("content", sa.Text, nullable=False),
            sa.Column("tool_calls", sa.JSON),
            sa.Column("outcome", sa.String(32)),
            sa.Column("provider", sa.String(32)),
            sa.Column("latency_ms", sa.Integer),
            sa.Column("trace_id", sa.String(64)),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            **table_kw_for(CONVERSATIONS),
        )
        op.create_index(
            "idx_assistant_msg_conv", MESSAGES, ["conversation_id", "seq"],
        )
        op.create_index("idx_assistant_msg_created", MESSAGES, ["created_at"])


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, MESSAGES):
        op.drop_table(MESSAGES)
    if _table_exists(bind, CONVERSATIONS):
        op.drop_table(CONVERSATIONS)