class AddOrganizationToAssistants < ActiveRecord::Migration[7.0]
  class Assistant < ApplicationRecord; end
  class AssistantScope < ApplicationRecord; end

  def up
    # 1. Add column allowing nulls initially
    add_reference :assistants, :organization, foreign_key: true, null: true
    Assistant.reset_column_information

    # 2. Backfill existing assistants by tracing their scopes back to the project
    Assistant.find_each do |assistant|
      scope = AssistantScope.find_by(assistant_id: assistant.id)
      next unless scope

      org_id = nil

      if scope.scopable_type == "Project"
        org_id = ActiveRecord::Base.connection.select_value(
          "SELECT organization_id FROM projects WHERE id = #{scope.scopable_id}"
        )
      elsif scope.scopable_type == "RespondentCategory"
        project_id = ActiveRecord::Base.connection.select_value(
          "SELECT project_id FROM respondent_categories WHERE id = #{scope.scopable_id}"
        )
        if project_id
          org_id = ActiveRecord::Base.connection.select_value(
            "SELECT organization_id FROM projects WHERE id = #{project_id}"
          )
        end
      end

      # Update the assistant with the found organization
      assistant.update_columns(organization_id: org_id) if org_id
    end

    # 3. Clean up any orphaned assistants that somehow have no organization
    Assistant.where(organization_id: nil).destroy_all

    # 4. Enforce strict database constraint
    change_column_null :assistants, :organization_id, false
  end

  def down
    remove_reference :assistants, :organization, foreign_key: true
  end
end