"""Store the assistant's action proposals so a confirmation means something.

Revision ID: 489
Revises: 488

A proposal is an offer the model made and the server froze: validated parameters
plus the digest the employee is shown. Confirmation re-reads this row and
compares that digest, so pressing the button submits exactly the request that
was on screen — the model is not consulted a second time and cannot influence
what runs.

``expires_at`` is a row-level bound on top of the action token's own expiry: a
forgotten card in a chat history must not stay pressable into a state that no
longer resembles the one it was written for.
"""
import sqlalchemy as sa

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

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

TABLE = "assistant_action_proposals"


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


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        return

    person_id = id_type_for("identity_persons")
    conversation_id = id_type_for("assistant_conversations")
    op.create_table(
        TABLE,
        sa.Column("id", person_id, primary_key=True),
        sa.Column(
            "person_id", person_id,
            sa.ForeignKey("identity_persons.id"), nullable=False,
        ),
        sa.Column(
            "conversation_id", conversation_id,
            sa.ForeignKey("assistant_conversations.id"),
        ),
        sa.Column("action_key", sa.String(64), nullable=False),
        sa.Column("params", sa.JSON),
        sa.Column("params_hash", sa.String(64), nullable=False),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
        sa.Column("result", sa.JSON),
        sa.Column("trace_id", sa.String(64)),
        sa.Column("expires_at", sa.DateTime(timezone=True)),
        sa.Column("confirmed_at", sa.DateTime(timezone=True)),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        **table_kw_for("identity_persons"),
    )
    op.create_index("idx_assistant_prop_person", TABLE, ["person_id", "created_at"])
    op.create_index("idx_assistant_prop_status", TABLE, ["status", "expires_at"])


def downgrade() -> None:
    if _table_exists(op.get_bind(), TABLE):
        op.drop_table(TABLE)