class SetPositionsForExistingMasterSections < ActiveRecord::Migration[7.0]
  def up
    # SQL Version
    execute <<-SQL
      WITH numbered_sections AS (
        SELECT
          id,
          ROW_NUMBER() OVER (
            PARTITION BY respondent_category_id
            ORDER BY (extract_number_multi_separator(name))[1],
                     (extract_number_multi_separator(name))[2],
                     name
          ) as new_position
        FROM master_sections
      )
      UPDATE master_sections
      SET position = numbered_sections.new_position
      FROM numbered_sections
      WHERE master_sections.id = numbered_sections.id
    SQL

    # ActiveRecord Version (commented out - use either this or the SQL version)
    # MasterSection.group_by(&:respondent_category_id).each do |category_id, sections|
    #   sections.sort_by do |section|
    #     # Using Arel to execute the PostgreSQL function
    #     result = ActiveRecord::Base.connection.execute(
    #       "SELECT (extract_number_multi_separator('#{section.name}'))[1] as num1,
    #               (extract_number_multi_separator('#{section.name}'))[2] as num2"
    #     ).first
    #     [result['num1'].to_i, result['num2'].to_i, section.name]
    #   end.each_with_index do |section, index|
    #     section.update_column(:position, index + 1)
    #   end
    # end
  end

  def down
    execute "UPDATE master_sections SET position = NULL"
  end
end