-- This migration renames the 'no' locale to 'nb' in all tables where it is used.
-- When the Bokmål locale was created, the "no" language code was used. However,
-- "no" refers to the macrolanguage, Norwegian, not an individual language. "nb",
-- which stands for "norsk bokmål", is the correct language code for Bokmål.

-- Up

UPDATE blog_reactions
SET locale = 'nb'
WHERE locale = 'no';

UPDATE census
SET locale = 'nb'
WHERE locale = 'no';

UPDATE census_deduplication
SET locale = 'nb'
WHERE locale = 'no';

UPDATE downloads
SET locale = 'nb'
WHERE locale = 'no';

UPDATE inclusive
SET locale = 'nb'
WHERE locale = 'no';

UPDATE names
SET locale = 'nb'
WHERE locale = 'no';

UPDATE nouns
SET locale = 'nb'
WHERE locale = 'no';

UPDATE profiles
SET locale = 'nb'
WHERE locale = 'no';

UPDATE sources
SET locale = 'nb'
WHERE locale = 'no';

UPDATE stats
SET locale = 'nb'
WHERE locale = 'no';

UPDATE subscriptions
SET locale = 'nb'
WHERE locale = 'no';

UPDATE terms
SET locale = 'nb'
WHERE locale = 'no';

UPDATE translations
SET locale = 'nb'
WHERE locale = 'no';

-- The users.roles column is a pipe-separated string of items like:
--   <locale>-<endpoint>
--   e.g. "nl-some-route|nn-*|en-*"
--
-- We want to rename "no-" to "nb-", but *only* if it is at the start of
-- the role token (immediately after "|" or at the start of the string).
-- We do NOT rename "en-no-something".

-- a) Replace occurrences after a pipe: "|no-" => "|nb-"
UPDATE users
SET roles = REPLACE(roles, '|no-', '|nb-')
WHERE roles LIKE '%|no-%';

-- b) If the string *starts* with "no-", rename it to "nb-"
--    (We add a check for roles starting with 'no-')
UPDATE users
SET roles = 'nb-' || SUBSTR(roles, 4) -- remove "no-" and prepend "nb-"
WHERE roles LIKE 'no-%';

-- Down

UPDATE blog_reactions
SET locale = 'no'
WHERE locale = 'nb';

UPDATE census
SET locale = 'no'
WHERE locale = 'nb';

UPDATE census_deduplication
SET locale = 'no'
WHERE locale = 'nb';

UPDATE downloads
SET locale = 'no'
WHERE locale = 'nb';

UPDATE inclusive
SET locale = 'no'
WHERE locale = 'nb';

UPDATE names
SET locale = 'no'
WHERE locale = 'nb';

UPDATE nouns
SET locale = 'no'
WHERE locale = 'nb';

UPDATE profiles
SET locale = 'no'
WHERE locale = 'nb';

UPDATE sources
SET locale = 'no'
WHERE locale = 'nb';

UPDATE stats
SET locale = 'no'
WHERE locale = 'nb';

UPDATE subscriptions
SET locale = 'no'
WHERE locale = 'nb';

UPDATE terms
SET locale = 'no'
WHERE locale = 'nb';

UPDATE translations
SET locale = 'no'
WHERE locale = 'nb';

-- Same as above, but for nb -> no

UPDATE users
SET roles = REPLACE(roles, '|nb-', '|no-')
WHERE roles LIKE '%|nb-%';

UPDATE users
SET roles = 'no-' || SUBSTR(roles, 4)
WHERE roles LIKE 'nb-%';