def param(name, space=" ")
    name_json = "\"#{name}\":#{space}\""
    name_index = "LOCATE('#{name_json}', params)"
    value_index = "#{name_index} + #{name_json.length}"
    value_end_index = "LOCATE('\"', params, #{value_index} )"
    "substring( params FROM #{value_index} FOR ( #{value_end_index} - #{name_index} - #{name_json.length} ) )"
  end

  up do
    create_table :email_notifications do
      column :id,           Serial
      column :profile_id,   Integer,                      :nullable => false
      column :type,         String,                       :nullable => true
      column :ueid,         String,                       :nullable => true
      column :message_id,   Integer,                      :nullable => true
      column :iteration,    String,                       :nullable => true
      column :logins,       Text, :size => 4096,          :nullable => true
      column :created_at,   DateTime
    end

    execute "ALTER TABLE email_notifications ADD INDEX idx_iteration (iteration)"
    execute "ALTER TABLE email_notifications ADD INDEX idx_type (type)"
    execute "ALTER TABLE email_notifications ADD INDEX idx_created_at (created_at)"
    execute "ALTER TABLE email_notifications ADD INDEX idx_profile_id (profile_id)"

    # THIS PART ISN'T DONE YET
    execute <<-SQL
      INSERT INTO email_notifications (profile_id, created_at, os, ip, browser, referer)
        SELECT profile_id, at, #{param('os')}, #{param('ip')}, #{param('browser')}, #{param('referrer')}
        FROM profile_logs WHERE action = 'email_notification' and params LIKE '%": %'
    SQL
    execute <<-SQL
      INSERT INTO email_notifications (profile_id, created_at, os, ip, browser, referer)
        SELECT profile_id, at, #{param('os','')}, #{param('ip','')}, #{param('browser','')}, #{param('referrer','')}
        FROM profile_logs WHERE action = 'email_notification' and params NOT LIKE '%": %'
    SQL
  end