defmodule ComponentsElixir.Repo.Migrations.CreateComponents do
  use Ecto.Migration

  def change do
    create table(:components) do
      add :name, :string, null: false
      add :description, :text
      add :keywords, :string
      add :position, :string
      add :count, :integer, default: 0
      add :datasheet_url, :string
      add :image_filename, :string
      add :category_id, references(:categories, on_delete: :restrict), null: false

      timestamps()
    end

    create index(:components, [:category_id])
    create index(:components, [:name])

    # Full-text search indexes for PostgreSQL
    execute "CREATE INDEX components_search_idx ON components USING gin(to_tsvector('english', name || ' ' || coalesce(description, '') || ' ' || coalesce(keywords, '')))",
            "DROP INDEX components_search_idx"
  end
end