class CustomMasterSectionSort < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      CREATE OR REPLACE FUNCTION extract_number_multi_separator(text TEXT)
      RETURNS integer[] AS
      $$
      DECLARE
        number_part text;
      BEGIN
        number_part := (regexp_match(text, '^(\d+\.?\d*)[-_]'))[1];
      #{'  '}
        IF number_part IS NULL THEN
          RETURN ARRAY[0, 0];
        END IF;
      #{'  '}
        RETURN ARRAY[
          split_part(number_part, '.', 1)::integer,
          COALESCE(split_part(number_part, '.', 2), '0')::integer
        ];
      END;
      $$
      LANGUAGE plpgsql;
    SQL
  end

  def down
    execute "DROP FUNCTION IF EXISTS extract_number_multi_separator(text);"
  end
end