class AddRandomIdGenerator < ActiveRecord::Migration
  # ref: http://www.rightbrainnetworks.com/blog/base36-conversion-in-postgresql/
  # ref: http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/
  def up
    func_sql = <<-SQL
      CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
        RETURNS varchar AS $$
              DECLARE
            chars char[];
            ret varchar;
            val bigint;
          BEGIN
          chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
            ,'a','b','c','d','e','f','g','h','i','j','k','l','m'
            ,'n','o','p','q','r','s','t','u','v','w','x','y','z'];
          val := digits;
          ret := '';
          IF val < 0 THEN
            val := val * -1;
          END IF;
          WHILE val != 0 LOOP
            ret := chars[(val % 36)+1] || ret;
            val := val / 36;
          END LOOP;

          IF min_width > 0 AND char_length(ret) < min_width THEN
            ret := lpad(ret, min_width, '0');
          END IF;

          RETURN ret;

      END;
      $$ LANGUAGE 'plpgsql' IMMUTABLE;
    SQL
    execute func_sql



    sql = <<-SQL
      create sequence global_id_sequence;

      CREATE OR REPLACE FUNCTION generate_random_id() RETURNS varchar AS $$
      DECLARE
          our_epoch bigint := 1472533855842;
          seq_id bigint;
          now_millis bigint;
          result bigint;
          ret varchar;
      BEGIN
          SELECT nextval('global_id_sequence') % 1024 INTO seq_id;

          SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
          result := (now_millis - our_epoch) << 10;
          result := result | (seq_id);
          ret := base36_encode(result);

          RETURN ret;
      END;
      $$ LANGUAGE PLPGSQL;
    SQL

    execute sql
  end

  def down
    execute "drop function if exists generate_random_id()"
    execute "drop function if exists base36_encode()"
    execute "drop sequence if exists global_id_sequence"
  end
end