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

  @disable_ddl_transaction true
  @disable_migration_lock true

  @redundant_indexes [
    # BRIN on partitioned table where each partition is small — B-tree covers this.
    "idx_packets_received_brin",
    # Standalone lat/lon — GiST on location handles all spatial queries.
    "idx_packets_lat",
    "idx_packets_lon",
    # Redundant with idx_packets_sender_received (same leading column).
    "idx_packets_sender_id_desc",
    # Geometry GiST — app uses geography casts (ST_DWithin ::geography).
    "idx_packets_location",
    # text_pattern_ops — callsign queries use upper() equality, not LIKE 'xxx%'.
    "idx_packets_sender_pattern",
    # Weather filters are always by sender/time; no query filters by raw metric values.
    "idx_packets_temperature",
    "idx_packets_humidity",
    "idx_packets_pressure",
    "idx_packets_wind_speed",
    "idx_packets_received_temp",
    "idx_packets_sender_temp",
    # idx_packets_has_weather + idx_packets_sender_weather_lookup cover these.
    "idx_packets_weather_history",
    "idx_packets_weather_selective",
    "idx_packets_weather_symbol"
  ]

  def up do
    execute("SET LOCAL statement_timeout = '0'")

    for idx <- @redundant_indexes do
      execute("DROP INDEX IF EXISTS #{idx}")
    end

    # has_weather is now computed in Elixir (packet_consumer.ex prepare_packet_for_insert).
    execute("DROP TRIGGER IF EXISTS update_has_weather_trigger ON packets")
  end

  def down do
    execute("SET LOCAL statement_timeout = '0'")

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_received_brin ON packets USING brin (received_at)"
    )

    execute("CREATE INDEX IF NOT EXISTS idx_packets_lat ON packets (lat)")
    execute("CREATE INDEX IF NOT EXISTS idx_packets_lon ON packets (lon)")
    execute("CREATE INDEX IF NOT EXISTS idx_packets_sender_id_desc ON packets (sender, id DESC)")

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_location ON packets USING gist (location) WHERE has_position = true"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_sender_pattern ON packets (sender text_pattern_ops)"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_temperature ON packets (temperature) WHERE temperature IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_humidity ON packets (humidity) WHERE humidity IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_pressure ON packets (pressure) WHERE pressure IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_wind_speed ON packets (wind_speed) WHERE wind_speed IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_received_temp ON packets (received_at, temperature) WHERE temperature IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_sender_temp ON packets (sender, temperature) WHERE temperature IS NOT NULL"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_weather_history ON packets (sender, data_type, received_at) WHERE data_type = 'weather'"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_weather_selective ON packets (received_at DESC) WHERE data_type IN ('weather', 'Weather', 'WX', 'wx')"
    )

    execute(
      "CREATE INDEX IF NOT EXISTS idx_packets_weather_symbol ON packets (received_at DESC, lat, lon) WHERE (symbol_table_id = '/' AND symbol_code = '_') OR (symbol_table_id = E'\\\\' AND symbol_code = '_')"
    )

    execute(
      "CREATE TRIGGER update_has_weather_trigger BEFORE INSERT OR UPDATE ON packets FOR EACH ROW EXECUTE FUNCTION update_has_weather()"
    )
  end
end
