defmodule Aprsme.Repo.Migrations.AddObjectNameAndItemNameIndexes do
  use Ecto.Migration

  @disable_ddl_transaction true
  @disable_migration_lock true

  @parent_object_idx "idx_packets_upper_object_name"
  @parent_item_idx "idx_packets_upper_item_name"

  def up do
    if Aprsme.Repo.config()[:pool] == Ecto.Adapters.SQL.Sandbox do
      # Test env (Sandbox does not support CONCURRENTLY). Build directly on parent
      # so tests that exercise these queries still hit an index.
      execute("""
      CREATE INDEX IF NOT EXISTS #{@parent_object_idx}
      ON packets (upper(object_name))
      WHERE object_name IS NOT NULL
      """)

      execute("""
      CREATE INDEX IF NOT EXISTS #{@parent_item_idx}
      ON packets (upper(item_name))
      WHERE item_name IS NOT NULL
      """)
    else
      # Production path: build per-partition CONCURRENTLY, then ATTACH to parent.
      execute("""
      CREATE INDEX IF NOT EXISTS #{@parent_object_idx}
      ON ONLY packets (upper(object_name))
      WHERE object_name IS NOT NULL
      """)

      execute("""
      CREATE INDEX IF NOT EXISTS #{@parent_item_idx}
      ON ONLY packets (upper(item_name))
      WHERE item_name IS NOT NULL
      """)

      partitions =
        Aprsme.Repo.query!("""
        SELECT inhrelid::regclass::text
        FROM pg_inherits
        WHERE inhparent = 'packets'::regclass
        """).rows
        |> List.flatten()

      Enum.each(partitions, &build_and_attach/1)
    end
  end

  def down do
    execute("DROP INDEX IF EXISTS #{@parent_object_idx}")
    execute("DROP INDEX IF EXISTS #{@parent_item_idx}")
  end

  defp build_and_attach(partition) do
    partition_unquoted = String.trim(partition, "\"")
    safe = String.replace(partition_unquoted, ~r/[^a-zA-Z0-9_]/, "_")
    object_idx = "#{safe}_upper_object_name_idx"
    item_idx = "#{safe}_upper_item_name_idx"

    execute("""
    CREATE INDEX CONCURRENTLY IF NOT EXISTS #{object_idx}
    ON #{partition} (upper(object_name))
    WHERE object_name IS NOT NULL
    """)

    execute("""
    CREATE INDEX CONCURRENTLY IF NOT EXISTS #{item_idx}
    ON #{partition} (upper(item_name))
    WHERE item_name IS NOT NULL
    """)

    execute("ALTER INDEX #{@parent_object_idx} ATTACH PARTITION #{object_idx}")
    execute("ALTER INDEX #{@parent_item_idx} ATTACH PARTITION #{item_idx}")
  end
end
