-- The legacy pages were scraped from the Joomla site, and the scrape kept the
-- site's own furniture. Every one of them ends with Joomla's article pager — a
-- borderless table holding "< Prev" and "Next >" — whose links are absolute URLs
-- back to shodh.net. The public site injects this HTML verbatim, so a reader who
-- reached the guidelines page inside the new application and clicked "Next >"
-- was thrown back onto the old one.
--
-- Three separate defects, all of them the same scrape's doing.

-- 1. The article pager. It is always the last table on the page, and everything
--    from it to the end of the content is pager. Guarded so that only a trailing
--    table that actually holds Prev/Next is cut: a page whose last table is real
--    content is left alone.
UPDATE cms_content
SET value_hi = rtrim(regexp_replace(value_hi, '^(.*)\s*<table[\s\S]*$', '\1', 's')),
    value_en = rtrim(regexp_replace(value_en, '^(.*)\s*<table[\s\S]*$', '\1', 's'))
WHERE regexp_replace(value_hi, '^.*<table', '<table', 's') ~ '(&lt;\s*Prev|Next\s*&gt;)'
   OR regexp_replace(value_en, '^.*<table', '<table', 's') ~ '(&lt;\s*Prev|Next\s*&gt;)';

-- 2. External links the scrape resolved against the wrong host. The funding-body
--    list carries eighteen of the form https://shodh.net/www.example.org/... —
--    an external address with the old site's domain glued to the front, so every
--    one of them lands on shodh.net instead of the body it names.
UPDATE cms_content
SET value_hi = replace(value_hi, 'https://shodh.net/www.', 'http://www.'),
    value_en = replace(value_en, 'https://shodh.net/www.', 'http://www.')
WHERE value_hi LIKE '%https://shodh.net/www.%'
   OR value_en LIKE '%https://shodh.net/www.%';

-- 3. Links to pages this application now has of its own. "Submit your article"
--    pointed at the old Joomla submission form, which is the worst of these:
--    an author following it would have submitted their paper into the system we
--    are replacing.
UPDATE cms_content
SET value_hi = regexp_replace(value_hi,
        'https://shodh\.net/index\.php\?option=com_mad4joomla[^"]*', '/submit', 'g'),
    value_en = regexp_replace(value_en,
        'https://shodh\.net/index\.php\?option=com_mad4joomla[^"]*', '/submit', 'g')
WHERE value_hi LIKE '%com_mad4joomla%' OR value_en LIKE '%com_mad4joomla%';

UPDATE cms_content
SET value_hi = regexp_replace(value_hi,
        'https://shodh\.net/index\.php\?option=com_content[^"]*id=24[^"]*',
        '/author-desk/peer-review', 'g'),
    value_en = regexp_replace(value_en,
        'https://shodh\.net/index\.php\?option=com_content[^"]*id=24[^"]*',
        '/author-desk/peer-review', 'g')
WHERE section = 'AUTHOR_PUBLICATION_FEE'
  AND (value_hi LIKE '%id=24%' OR value_en LIKE '%id=24%');

-- 4. Two more casualties of the same bad resolution, in the funding-body list:
--    a Surrey link that lost its host entirely, and a set that had their
--    filename doubled. Straightening them does not make them current — these
--    addresses were collected years ago and many of the pages behind them will
--    have moved — but a well-formed external link is at least honest about
--    where it is going, which "shodh.net/gen-int.htm" was not.
UPDATE cms_content
SET value_hi = replace(replace(value_hi,
        'https://shodh.net/gen-int.htm', 'http://www.surrey.ac.uk/Arts/funding/gen-int.htm'),
        'gen-int.htmgen-int.htm', 'gen-int.htm'),
    value_en = replace(replace(value_en,
        'https://shodh.net/gen-int.htm', 'http://www.surrey.ac.uk/Arts/funding/gen-int.htm'),
        'gen-int.htmgen-int.htm', 'gen-int.htm')
WHERE value_hi LIKE '%gen-int.htm%' OR value_en LIKE '%gen-int.htm%';