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

  def up do
    # Drop the existing table and recreate with correct primary key type
    drop_if_exists table(:snmp_neighbors)

    create table(:snmp_neighbors, primary_key: false) do
      add :id, :binary_id, primary_key: true

      add :equipment_id, references(:equipment, type: :binary_id, on_delete: :delete_all),
        null: false

      add :interface_id, references(:snmp_interfaces, type: :binary_id, on_delete: :delete_all),
        null: false

      # Discovery protocol (lldp, cdp)
      add :protocol, :string, null: false

      # Remote device identification
      add :remote_chassis_id, :string
      add :remote_system_name, :string
      add :remote_system_description, :text
      add :remote_platform, :string

      # Remote interface/port information
      add :remote_port_id, :string
      add :remote_port_description, :string

      # Remote device address (IP)
      add :remote_address, :string

      # Capabilities as a list of strings (router, switch, bridge, etc.)
      add :remote_capabilities, {:array, :string}, default: []

      # Last time this neighbor was seen
      add :last_discovered_at, :utc_datetime, null: false

      timestamps(type: :utc_datetime)
    end

    # Indexes for performance
    create index(:snmp_neighbors, [:equipment_id])
    create index(:snmp_neighbors, [:interface_id])
    create index(:snmp_neighbors, [:protocol])
    create index(:snmp_neighbors, [:last_discovered_at])

    # Unique constraint: one neighbor per interface + remote chassis + protocol
    create unique_index(:snmp_neighbors, [:interface_id, :remote_chassis_id, :protocol])
  end

  def down do
    drop table(:snmp_neighbors)
  end
end
