-- Two more things the Joomla scrape left in the public pages.
--
-- 1. An injected link on the publication-fee page. It wraps the single letter
--    "i" in the word "is" — <a href="http://www.speechemical.com/shodh">i</a>s
--    preferred — so it reads as ordinary prose while carrying an outbound link
--    to a domain with no connection to the journal. That is what SEO spam on a
--    compromised Joomla site looks like, and it is on the page about money.
--    The letter stays; the anchor goes.
UPDATE cms_content
SET value_hi = regexp_replace(value_hi,
        '<a href="https?://(?:www\.)?speechemical\.com[^"]*"[^>]*>([^<]*)</a>', '\1', 'g'),
    value_en = regexp_replace(value_en,
        '<a href="https?://(?:www\.)?speechemical\.com[^"]*"[^>]*>([^<]*)</a>', '\1', 'g')
WHERE value_hi LIKE '%speechemical%' OR value_en LIKE '%speechemical%';

-- 2. Joomla cloaked email addresses behind JavaScript and served a placeholder
--    to anything that did not run it. The scrape ran nothing, so three pages
--    carry the placeholder sentence where an address belongs — and on the
--    funding page it is worse than cosmetic: the address was captured as
--    "mailto:% This e-mail address is being protected from spambots…", a live
--    link that opens the reader's mail client addressed to that text.
--
--    The addresses themselves are not recoverable from the scrape and are not
--    invented here. The dead links and the placeholder prose go; where a label
--    is left pointing at nothing it goes too, and the editor can put a real
--    address back through the CMS if one is wanted.

-- The broken mailto, anchor and all.
UPDATE cms_content
SET value_hi = regexp_replace(value_hi, '<a href="mailto:%[^"]*"[^>]*>[^<]*</a>', '', 'g'),
    value_en = regexp_replace(value_en, '<a href="mailto:%[^"]*"[^>]*>[^<]*</a>', '', 'g')
WHERE value_hi LIKE '%mailto:%%' OR value_en LIKE '%mailto:%%';

-- The orphaned Hindi label on the guidelines page: "e-mail address -" followed
-- by the placeholder. Authors submit through the platform now, and the sentence
-- it sits in already offers the submission form, so the label is not replaced.
UPDATE cms_content
SET value_hi = regexp_replace(value_hi,
        'ई-मेल पता -\s*(<br\s*/?>)?\s*This e-mail address is being protected from spambots[^<]*', '', 'g'),
    value_en = regexp_replace(value_en,
        'ई-मेल पता -\s*(<br\s*/?>)?\s*This e-mail address is being protected from spambots[^<]*', '', 'g')
WHERE value_hi LIKE '%ई-मेल पता -%' OR value_en LIKE '%ई-मेल पता -%';

-- Any remaining placeholder, including the empty paragraph it sits in on the
-- editorial board page.
UPDATE cms_content
SET value_hi = regexp_replace(regexp_replace(value_hi,
        '<p>\s*This e-mail address is being protected from spambots[^<]*</p>', '', 'g'),
        '\s*This e-mail address is being protected from spambots\.? You need JavaScript enabled to view it\s*', '', 'g'),
    value_en = regexp_replace(regexp_replace(value_en,
        '<p>\s*This e-mail address is being protected from spambots[^<]*</p>', '', 'g'),
        '\s*This e-mail address is being protected from spambots\.? You need JavaScript enabled to view it\s*', '', 'g')
WHERE value_hi ILIKE '%protected from spambots%' OR value_en ILIKE '%protected from spambots%';