defmodule Towerops.Repo.Migrations.CreateGeoipTables do
  use Ecto.Migration

  def change do
    # Locations table - maps geoname_id to location details (country, city, etc.)
    create table(:geoip_locations, primary_key: false) do
      add :geoname_id, :integer, primary_key: true
      add :country_code, :string, size: 2
      add :country_name, :string
      add :city_name, :string
      add :subdivision_1_name, :string
      add :subdivision_2_name, :string
      add :latitude, :float
      add :longitude, :float

      timestamps(type: :utc_datetime)
    end

    # Blocks table - maps IP ranges to geoname_id
    create table(:geoip_blocks, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :network, :string
      add :start_ip_int, :bigint, null: false
      add :end_ip_int, :bigint, null: false
      add :geoname_id, references(:geoip_locations, column: :geoname_id, type: :integer)
      add :registered_country_geoname_id, :integer

      timestamps(type: :utc_datetime)
    end

    # Index for fast IP range lookups (most important query)
    create index(:geoip_blocks, [:start_ip_int, :end_ip_int])

    # Index for geoname_id lookups
    create index(:geoip_blocks, [:geoname_id])
  end
end
