"""Create command_log table

Revision ID: a1b2c3d4e5f6
Revises: 0093c15b4bbf
Create Date: 2026-03-21 12:00:00.000000

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "a1b2c3d4e5f6"
down_revision: Union[str, Sequence[str], None] = "0093c15b4bbf"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    op.create_table(
        "command_log",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("timestamp", sa.DateTime(), nullable=False),
        sa.Column("command", sa.String(length=64), nullable=False),
        sa.Column("params", sa.Text(), nullable=False, server_default="{}"),
        sa.Column("reasoning", sa.Text(), nullable=False, server_default=""),
        sa.Column(
            "perception_snapshot", sa.Text(), nullable=False, server_default="{}"
        ),
        sa.Column("outcome", sa.Text(), nullable=True),
        sa.Column(
            "agent_id",
            sa.String(length=64),
            nullable=False,
            server_default="timmy",
        ),
        sa.Column("episode_id", sa.String(length=128), nullable=True),
        sa.Column("cell", sa.String(length=255), nullable=True),
        sa.Column(
            "protocol_version",
            sa.String(length=16),
            nullable=False,
            server_default="1.0.0",
        ),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_command_log_timestamp"), "command_log", ["timestamp"], unique=False
    )
    op.create_index(
        op.f("ix_command_log_command"), "command_log", ["command"], unique=False
    )
    op.create_index(
        op.f("ix_command_log_agent_id"), "command_log", ["agent_id"], unique=False
    )
    op.create_index(
        op.f("ix_command_log_episode_id"),
        "command_log",
        ["episode_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_command_log_cell"), "command_log", ["cell"], unique=False
    )
    op.create_index(
        "ix_command_log_cmd_cell", "command_log", ["command", "cell"], unique=False
    )
    op.create_index(
        "ix_command_log_episode",
        "command_log",
        ["episode_id", "timestamp"],
        unique=False,
    )


def downgrade() -> None:
    """Downgrade schema."""
    op.drop_index("ix_command_log_episode", table_name="command_log")
    op.drop_index("ix_command_log_cmd_cell", table_name="command_log")
    op.drop_index(op.f("ix_command_log_cell"), table_name="command_log")
    op.drop_index(op.f("ix_command_log_episode_id"), table_name="command_log")
    op.drop_index(op.f("ix_command_log_agent_id"), table_name="command_log")
    op.drop_index(op.f("ix_command_log_command"), table_name="command_log")
    op.drop_index(op.f("ix_command_log_timestamp"), table_name="command_log")
    op.drop_table("command_log")