-- Up

ALTER TABLE nouns
    ADD COLUMN words TEXT NOT NULL DEFAULT '{}';

UPDATE nouns
SET words = json_set(words, '$.masc.singular',
                     json('["' || replace(replace(replace(masc, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE masc != '';
UPDATE nouns
SET words = json_set(words, '$.masc.plural',
                     json('["' || replace(replace(replace(mascPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE mascPl != '';
UPDATE nouns
SET words = json_set(words, '$.fem.singular',
                     json('["' || replace(replace(replace(fem, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE fem != '';
UPDATE nouns
SET words = json_set(words, '$.fem.plural',
                     json('["' || replace(replace(replace(femPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE femPl != '';
UPDATE nouns
SET words = json_set(words, '$.neutr.singular',
                     json('["' || replace(replace(replace(neutr, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE neutr != '';
UPDATE nouns
SET words = json_set(words, '$.neutr.plural',
                     json('["' || replace(replace(replace(neutrPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE neutrPl != '';
UPDATE nouns
SET words = json_set(words, '$.nb.singular',
                     json('["' || replace(replace(replace(nb, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE nb != '';
UPDATE nouns
SET words = json_set(words, '$.nb.plural',
                     json('["' || replace(replace(replace(nbPl, '\', '\\'), '"', '\"'), '|', '","') || '"]'))
WHERE nbPl != '';

ALTER TABLE nouns
    ADD COLUMN key TEXT;
UPDATE nouns
SET key = lower(trim(json(words) -> '$.masc.singular[0]', '"'))
WHERE 1 = 1;
UPDATE nouns
SET key = replace(key, 'der ', '')
WHERE locale = 'de';

CREATE INDEX nouns_key ON nouns (key);

DROP INDEX nouns_masc;
ALTER TABLE nouns
    DROP COLUMN masc;
ALTER TABLE nouns
    DROP COLUMN mascPl;
ALTER TABLE nouns
    DROP COLUMN fem;
ALTER TABLE nouns
    DROP COLUMN femPl;
ALTER TABLE nouns
    DROP COLUMN neutr;
ALTER TABLE nouns
    DROP COLUMN neutrPl;
ALTER TABLE nouns
    DROP COLUMN nb;
ALTER TABLE nouns
    DROP COLUMN nbPl;

-- Down

ALTER TABLE nouns
    ADD COLUMN masc TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN mascPl TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN fem TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN femPl TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN neutr TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN neutrPl TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN nb TEXT NOT NULL DEFAULT '';
ALTER TABLE nouns
    ADD COLUMN nbPl TEXT NOT NULL DEFAULT '';

CREATE INDEX nouns_masc ON nouns (masc);

DROP INDEX nouns_key;
ALTER TABLE nouns
    DROP COLUMN key;

UPDATE
    nouns
SET masc    = coalesce(replace(replace(replace(trim(json(words) -> '$.masc.singular', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    mascPl  = coalesce(replace(replace(replace(trim(json(words) -> '$.masc.plural', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    fem     = coalesce(replace(replace(replace(trim(json(words) -> '$.fem.singular', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    femPl   = coalesce(replace(replace(replace(trim(json(words) -> '$.fem.plural', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    neutr   = coalesce(replace(replace(replace(trim(json(words) -> '$.neutr.singular', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    neutrPl = coalesce(replace(replace(replace(trim(json(words) -> '$.neutr.plural', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    nb      = coalesce(replace(replace(replace(trim(json(words) -> '$.nb.singular', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), ''),
    nbPl    = coalesce(replace(replace(replace(trim(json(words) -> '$.nb.plural', '["]'),
                                               '","', '|'), '\"', '"'), '\\', '\'), '')
WHERE 1 = 1;

ALTER TABLE nouns
    DROP COLUMN words;