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

  def change do
    # Add column to track where the community string was inherited from
    alter table(:devices) do
      add :snmp_community_source, :string, default: "device"
    end

    # Backfill existing devices: if they have a community, mark as "device", otherwise "organization"
    execute(
      """
      UPDATE devices
      SET snmp_community_source = CASE
        WHEN snmp_community IS NOT NULL AND snmp_community != '' THEN 'device'
        ELSE 'organization'
      END
      """,
      # Rollback: just set everything back to "device"
      "UPDATE devices SET snmp_community_source = 'device'"
    )

    # Create index for faster lookups when propagating changes
    create index(:devices, [:snmp_community_source])
  end
end
