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

  def change do
    # Main profile table
    create table(:device_profiles, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :name, :string, null: false
      add :vendor, :string, null: false
      add :detection_pattern, :text
      add :detection_oid, :text
      add :priority, :integer, default: 100
      add :enabled, :boolean, default: true

      timestamps(type: :utc_datetime)
    end

    create unique_index(:device_profiles, [:name])
    create index(:device_profiles, [:enabled])
    create index(:device_profiles, [:priority])

    # Device identification OIDs (serial number, firmware version, model, etc.)
    create table(:profile_device_oids, primary_key: false) do
      add :id, :binary_id, primary_key: true

      add :profile_id,
          references(:device_profiles, type: :binary_id, on_delete: :delete_all), null: false

      add :field, :string, null: false
      add :mib_name, :string, null: false

      timestamps(type: :utc_datetime)
    end

    create index(:profile_device_oids, [:profile_id])
    create unique_index(:profile_device_oids, [:profile_id, :field])

    # Sensor discovery OIDs
    create table(:profile_sensor_oids, primary_key: false) do
      add :id, :binary_id, primary_key: true

      add :profile_id,
          references(:device_profiles, type: :binary_id, on_delete: :delete_all), null: false

      add :sensor_type, :string, null: false
      add :mib_name, :string, null: false
      add :sensor_descr, :string
      add :sensor_unit, :string
      add :sensor_divisor, :integer, default: 1

      timestamps(type: :utc_datetime)
    end

    create index(:profile_sensor_oids, [:profile_id])
    create index(:profile_sensor_oids, [:sensor_type])
  end
end
