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

  def up do
    # 1. Add nullable organization_id column
    alter table(:devices) do
      add :organization_id, references(:organizations, type: :binary_id, on_delete: :delete_all)
    end

    # 2. Backfill from existing site relationships
    execute """
    UPDATE devices d
    SET organization_id = s.organization_id
    FROM sites s
    WHERE d.site_id = s.id
    """

    # 3. Make organization_id NOT NULL (data is backfilled)
    alter table(:devices) do
      modify :organization_id, :binary_id, null: false
    end

    # 4. Add indexes
    create index(:devices, [:organization_id])
    create index(:devices, [:organization_id, :display_order, :name])

    # Note: We validate site belongs to same org in application code (changeset)
    # PostgreSQL doesn't support subqueries in CHECK constraints
  end

  def down do
    drop index(:devices, [:organization_id, :display_order, :name])
    drop index(:devices, [:organization_id])

    alter table(:devices) do
      remove :organization_id
    end
  end
end
