sagents

defmodule <%= @repo %>.Migrations.Create<%= Macro.camelize(@table_prefix) %>Persistence do
  use Ecto.Migration

  def change do
    # Conversations table
    create table(:<%= @table_prefix %>conversations, primary_key: false) do
      add :id, :binary_id, primary_key: true
<%= if @owner_type != "none" do %>      add :<%= @owner_field %>, references(:<%= @owner_type %>s, on_delete: :delete_all), null: false
<% end %>      add :title, :string
      add :version, :integer, default: 1, null: false
      add :metadata, :map, default: %{}

      timestamps(type: :utc_datetime_usec)
    end

<%= if @owner_type != "none" do %>    create index(:<%= @table_prefix %>conversations, [:<%= @owner_field %>])
<% end %>    create index(:<%= @table_prefix %>conversations, [:updated_at])

    # Agent states table (one per conversation)
    create table(:<%= @table_prefix %>agent_states, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :conversation_id, references(:<%= @table_prefix %>conversations, on_delete: :delete_all, type: :binary_id), null: false
      add :state_data, :map, null: false
      add :version, :integer, null: false

      timestamps(type: :utc_datetime_usec)
    end

    create unique_index(:<%= @table_prefix %>agent_states, [:conversation_id])

    # Display messages table (multi-content type support)
    create table(:<%= @table_prefix %>display_messages, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :conversation_id, references(:<%= @table_prefix %>conversations, on_delete: :delete_all, type: :binary_id), null: false
      # "user", "assistant", "tool", "system"
      add :message_type, :string, null: false
      # JSONB storage for flexible content
      add :content, :map, null: false
      # Content type for rendering
      add :content_type, :string, null: false, default: "text"
      # Tool execution status: "pending", "executing", "completed", "failed"
      add :status, :string, default: "completed"
      # Message-local ordering (0-based within same timestamp)
      add :sequence, :integer, default: 0, null: false
      add :metadata, :map, default: %{}

      timestamps(type: :utc_datetime_usec, updated_at: false)
    end

    create index(:<%= @table_prefix %>display_messages, [:conversation_id, :inserted_at, :sequence])
    # For filtering by content type
    create index(:<%= @table_prefix %>display_messages, [:content_type])
    # For filtering by status (e.g., pending tools)
    create index(:<%= @table_prefix %>display_messages, [:status])

    # Unique partial index to prevent duplicate tool call IDs per conversation
    create unique_index(
             :<%= @table_prefix %>display_messages,
             [:conversation_id, "(content->>'call_id')"],
             name: :unique_tool_call_per_conversation,
             where: "content_type = 'tool_call'"
           )
  end
end