I've found my own solution.
Here are the changes I've made to the source and configuration code that made this work.
#+TITLE: How to Automate Footnote Citations in Org-Mode
#+AUTHOR: Sati Bodhi
This is the abstract.
* Cite Test
This is a statement with a footnote reference. [fn:2a55720f42c74dd:This is the footnote ...
[N.B This does not provide an answer to the (amended) question as the comments below make clear. The OP wants to treat footnote links within footnotes differently from footnote links in regular text and I don't think that can be done in Org mode itself. What should be possible is to use LaTeX-specific mechanisms within the Org mode file, but (currently) that ...
A feature request was published asking this in the mailing list in 2017, but it seems that it is still not possible to do this.
However, there is something you need to know: The reason why you have decided to enclose every LaTeX fragment in a #+BEGIN_EXPORT block is the fact that you can edit it the #+BEGIN_EXPORT block in an isolated buffer by executing org-...
You have to add the package to org-latex-packages-alist:
(add-to-list 'org-latex-packages-alist '("" "tcolorbox" t)))
The t is important: it tells Org mode to include that package for LaTeX preview. Otherwise, it will only be included when you export the file to LaTeX/PDF, but not when previewing LaTeX fragments....