SELECT lang.lang_code, lang.lang_belongs_to_pg_xenophile,
    lang_l10n.l10n_lang_code, lang_l10n.l10n_columns_belong_to_extension_name,
    lang_l10n.l10n_columns_belong_to_extension_version, lang_l10n.name
   FROM lang
     LEFT JOIN lang_l10n ON lang.lang_code::text = lang_l10n.lang_code::text AND lang_l10n.l10n_lang_code::text = 'en'::text;

 SELECT country.country_code, country.country_code_alpha3,
    country.country_code_num, country.calling_code, country.currency_code,
    country.country_belongs_to_pg_xenophile, country_l10n.l10n_lang_code,
    country_l10n.l10n_columns_belong_to_extension_name,
    country_l10n.l10n_columns_belong_to_extension_version, country_l10n.name
   FROM country
     LEFT JOIN country_l10n ON country.country_code::text = country_l10n.country_code::text AND country_l10n.l10n_lang_code::text = 'en'::text;

 SELECT country_subdivision.subdivision_code, country_subdivision.country_code,
    country_subdivision.subdivision_postal_abbreviation_code,
    country_subdivision.subdivision_type_handle,
    country_subdivision_l10n.l10n_lang_code,
    country_subdivision_l10n.l10n_columns_belong_to_extension_name,
    country_subdivision_l10n.l10n_columns_belong_to_extension_version,
    country_subdivision_l10n.name
   FROM country_subdivision
     LEFT JOIN country_subdivision_l10n ON country_subdivision.subdivision_code::text = country_subdivision_l10n.subdivision_code::text AND country_subdivision_l10n.l10n_lang_code::text = 'en'::text;

CREATE OR REPLACE FUNCTION xeno.pg_xenophile_base_lang_code()
 RETURNS lang_code_alpha2
 LANGUAGE sql
 STABLE LEAKPROOF
 SET "pg_readme.include_this_routine_definition" TO 'true'
 SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app.settings.i18n.base_lang_code'::text, true), current_setting('pg_xenophile.base_lang_code'::text, true), 'en'::text))::lang_code_alpha2

CREATE OR REPLACE FUNCTION xeno.pg_xenophile_target_lang_codes()
 RETURNS lang_code_alpha2[]
 LANGUAGE sql
 STABLE LEAKPROOF
 SET "pg_readme.include_this_routine_definition" TO 'true'
 SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app.settings.i18n.target_lang_codes'::text, true), current_setting('pg_xenophile.target_lang_codes'::text, true), '{}'::text))::lang_code_alpha2[]

CREATE OR REPLACE FUNCTION xeno.pg_xenophile_user_lang_code()
 RETURNS lang_code_alpha2
 LANGUAGE sql
 STABLE LEAKPROOF
 SET "pg_readme.include_this_routine_definition" TO 'true'
 SET search_path TO 'xeno', 'public', 'pg_temp'
RETURN (COALESCE(current_setting('app.settings.i18n.user_lang_code'::text, true), current_setting('pg_xenophile.user_lang_code'::text, true), regexp_replace(current_setting('lc_messages'::text), '^([a-z]{2}).*$'::text, ''::text), 'en'::text))::lang_code_alpha2

CREATE OR REPLACE PROCEDURE xeno.test_dump_restore__l10n_table(IN "test_stage$" text)
 LANGUAGE plpgsql
 SET search_path TO 'xeno', 'public', 'pg_temp'
 SET "plpgsql.check_asserts" TO 'true'
AS $procedure$
declare
    _en_expected record;
    _nl_expected record;
    _pt_expected record;
    _es_expected record;
    _en_actual record;
    _nl_actual record;
    _pt_actual record;
    _es_actual record;
begin
    assert test_stage$ in ('pre-dump', 'post-restore');

    if test_stage$ = 'pre-dump' then
        -- Create the table that will be translated.
        create table test_uni (
            uni_abbr text
                primary key
        );

        insert into l10n_table
            (base_table_name, l10n_column_definitions, base_lang_code, target_lang_codes)
        values (
            'test_uni'
            ,array['name TEXT NOT NULL']
            ,'en'::lang_code_alpha2
            ,array['nl', 'fr']::lang_code_alpha2[]
        );

        assert to_regclass('test_uni_l10n') is not null,
            'The `_l10n` table should have been created as result of the preceding INSERT into the meta table.';
    end if;

    -- Set up the expected data, now that we for sure have the `test_uni_l10n_*` types,
    -- regardless of `test_stage$`.
    _en_expected := row('AX-UNI', 'en', 'Axe University')::test_uni_l10n_en;
    _nl_expected := row('AX-UNI', 'nl', 'Bijl Universiteit')::test_uni_l10n_nl;

    if test_stage$ = 'pre-dump' then
        insert into test_uni_l10n_en
            (uni_abbr, name)
        values
            (_en_expected.uni_abbr, _en_expected.name)
        returning
            *
        into
            _en_actual
        ;
        update
            test_uni_l10n_nl
        set
            name = _nl_expected.name
        where
            uni_abbr = _en_expected.uni_abbr
        returning
            *
        into
            _nl_actual
        ;
    elsif test_stage$ = 'post-restore' then
        select * into _en_actual from test_uni_l10n_en where uni_abbr = _en_expected.uni_abbr;
        select * into _nl_actual from test_uni_l10n_nl where uni_abbr = _nl_expected.uni_abbr;
    end if;

    assert _en_expected = _en_actual;
    assert _nl_expected = _nl_actual;

    --
    -- Go test a dependent extension (that has its own `l10n_table`) now…
    --

    if test_stage$ = 'pre-dump' then
        create extension l10n_table_dependent_extension;
    end if;

    _pt_expected := row(
        '👋'
        ,10
        ,false
        ,'pt'
        ,null
        ,null
        ,'tchau'
    )::subextension_tbl_l10n_pt;
    _es_expected := row(
        '👋'
        ,10
        ,false
        ,'es'
        ,null
        ,null
        ,'adiós'
    )::subextension_tbl_l10n_es;

    if test_stage$ = 'pre-dump' then
        insert into subextension_tbl_l10n_pt
            (natural_key, base_tbl_col, localized_text)
        values
            (_pt_expected.natural_key, _pt_expected.base_tbl_col, _pt_expected.localized_text)
        returning
            *
        into
            _pt_actual
        ;
        update
            subextension_tbl_l10n_es
        set
            localized_text = _es_expected.localized_text
        where
            natural_key = _es_expected.natural_key
        returning
            *
        into
            _es_actual
        ;
    elsif test_stage$ = 'post-restore' then
        select * into _pt_actual from subextension_tbl_l10n_pt where natural_key = _pt_expected.natural_key;
        select * into _es_actual from subextension_tbl_l10n_es where natural_key = _es_expected.natural_key;
    end if;

    assert _pt_actual = _pt_expected,
        format('%s != %s', _pt_actual, _pt_expected);
    assert _es_actual = _es_expected,
        format('%s != %s', _es_actual, _es_expected);

    --
    -- Test with the dependent subextension and a Portuguese row that has been inserted during installation…
    --

    _pt_expected := row(
        '👍'
        ,null
        ,true
        ,'pt'
        ,'l10n_table_dependent_extension'
        ,'forever'  -- Yes, this is a version string.
        ,'bem'
    )::subextension_tbl_l10n_pt;
    _es_expected := row(
        '👍'
        ,null
        ,true
        ,'es'
        ,null
        ,null
        ,'buen'
    )::subextension_tbl_l10n_es;

    if test_stage$ = 'pre-dump' then
        select * into _pt_actual from subextension_tbl_l10n_pt where natural_key = _pt_expected.natural_key;
        update
            subextension_tbl_l10n_es
        set
            localized_text = _es_expected.localized_text
        where
            natural_key = _es_expected.natural_key
        returning
            *
        into
            _es_actual
        ;
    elsif test_stage$ = 'post-restore' then
        select * into _pt_actual from subextension_tbl_l10n_pt where natural_key = _pt_expected.natural_key;
        select * into _es_actual from subextension_tbl_l10n_es where natural_key = _es_expected.natural_key;
    end if;

    assert _pt_actual = _pt_expected,
        format('%s != %s', _pt_actual, _pt_expected);
    assert _es_actual = _es_expected,
        format('%s != %s', _es_actual, _es_expected);
end;
$procedure$

CREATE OR REPLACE PROCEDURE xeno.test__l10n_table()
 LANGUAGE plpgsql
 SET search_path TO 'xeno', 'public', 'pg_temp'
 SET client_min_messages TO 'WARNING'
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
declare
    _row record;
    _l10n_table l10n_table;
begin
    -- Create the table that will be translated.
    create table test_uni (
        id bigint
            primary key
            generated always as identity
        ,uni_abbr text
            not null
            unique
        -- We need to have more than one non-PK column, to ensure that we're hitting the requirement to agg.
        -- Also, let's put a space in the column name, so that we're testing proper quoting as well.
        ,"student rating" bigint
            default 5
    );

    <<with_redundant_target_lang>>
    begin
        -- This tests that the trigger(s) on `l10n_table` tries to create the `_l10n_nl`-suffixed view
        -- only once and doesn't crash because of trying to create it twice.
        insert into l10n_table
            (base_table_name, l10n_column_definitions, base_lang_code, target_lang_codes)
        values (
            'test_uni'
            ,array['name TEXT NOT NULL', '"description (short)" TEXT NOT NULL']
            ,'nl'::lang_code_alpha2  -- Apologies for the Dutch East India Company mentality.
            ,array['nl']::lang_code_alpha2[]
        );
        raise transaction_rollback;
    exception
        when transaction_rollback then
    end with_redundant_target_lang;

    -- Register `test_uni` with the meta table, to activate all the l10n magic.
    insert into l10n_table
        (base_table_name, l10n_column_definitions, base_lang_code, target_lang_codes)
    values (
        'test_uni'
        ,array['name TEXT NOT NULL', '"description (short)" TEXT NOT NULL']
        ,'nl'::lang_code_alpha2  -- Apologies for the Dutch East India Company mentality.
        ,array['en', 'fr']::lang_code_alpha2[]
    );

    assert to_regclass('test_uni_l10n') is not null,
        'The `_l10n` table should have been created as result of the preceding INSERT into the meta table.';

    assert (
            select
                array_agg(pg_class.relname order by pg_class.relname)::name[]
            from
                pg_catalog.pg_class
            where
                pg_class.relkind = 'v'
                and pg_class.relnamespace = current_schema::regnamespace
                and pg_class.relname like 'test\_uni\_l10n\___'
        ) = array['test_uni_l10n_en', 'test_uni_l10n_fr', 'test_uni_l10n_nl']::name[]
        ,'3 `_l10n_<lang_code>`-suffixed views should have been created, one for the base language'
            || ' and 2 for the target languages.';

    <<upsert_into_l10n_lang_view>>
    declare
        _nl_expected record := row(
            1, 'AX-UNI', 5, 'nl', 'Bijl Universiteit', 'De trainingsleider in bijlonderhoud en gebruik'
        )::test_uni_l10n_nl;
        _en_expected record := row(
            1, 'AX-UNI', 5, 'en', 'Axe University', 'The leader in axe maintenance and usage training'
        )::test_uni_l10n_en;
    begin
        insert into test_uni_l10n_nl
            (uni_abbr, name, "description (short)")
        values
            (_nl_expected.uni_abbr, _nl_expected.name, _nl_expected."description (short)")
        returning
            *
        into
            _row
        ;

        assert _row = _nl_expected, format(
            'The `RETURNING` clause did not return the data as inserted; %s ≠ %s'
            ,_row, _nl_expected
        );

        assert _nl_expected = (select row(tbl.*)::test_uni_l10n_nl from test_uni_l10n_nl as tbl),
            'The `RETURNING` clause should have returned the same row data as this separate `SELECT`.';

        assert exists(select from test_uni_l10n_en where id = _nl_expected.id),
            'Even though the row for English doesn''t exist in `test_uni_l10n` yet, it should exist in the'
            ' `test_l10n_en` view, with NULL values for all the l10n columns.';

        update
            test_uni_l10n_en
        set
            "name" = _en_expected."name"
            ,"description (short)" = _en_expected."description (short)"
        where
            id = _nl_expected.id
        returning
            *
        into
            _row
        ;

        assert found, 'The `UPDATE` should have found a row to update in the `test_uni_l10n_en` view.';

        assert _row = _en_expected,
            format('%s ≠ %s; the `RETURNING` clause did not return the data as upserted.', _row, _en_expected);

        assert _en_expected = (select row(tbl.*)::test_uni_l10n_en from test_uni_l10n_en as tbl),
            'The `RETURNING` clause should have returned the same row data as this separate `SELECT`.';
    end upsert_into_l10n_lang_view;

    <<try_to_override_generated_pk>>
    declare
        _expected_id bigint := currval('test_uni_id_seq') + 1;
        _nl_expected record := row(
            _expected_id, 'SIMP-UNI', 2, 'nl', 'Simpschool', 'Simpen voor Elon en Jeff'
        )::test_uni_l10n_nl;
    begin
        insert into test_uni_l10n_nl
            (id, uni_abbr, "student rating", name, "description (short)")
        values (
            _nl_expected.id
            ,_nl_expected.uni_abbr
            ,_nl_expected."student rating"
            ,_nl_expected.name
            ,_nl_expected."description (short)"
        )
        returning
            *
        into
            _row
        ;

        raise assert_failure using
            message = 'It should not be possible to explicitly specify a PK value on insert'
                'if that PK is `GENERATED ALWAYS`.';
    exception
        when generated_always then
    end try_to_override_generated_pk;

    <<insert_instead_of_update_on_missing_l10n_record>>
    declare
        _expected_id bigint := currval('test_uni_id_seq') + 1;
        _nl_expected record := row(
            _expected_id, 'PO-UNI', 7, 'nl', 'Poep-Universiteit', 'De Beste Plek om Te Leren Legen'
        )::test_uni_l10n_nl;
        _en_expected record := row(
            _expected_id, 'PO-UNI', 7, 'en', 'Pooversity', 'The Best Place To Empty Yourself'
        )::test_uni_l10n_nl;
    begin
        insert into test_uni_l10n_nl
            (uni_abbr, "student rating", name, "description (short)")
        values (
            _nl_expected.uni_abbr
            ,_nl_expected."student rating"
            ,_nl_expected.name
            ,_nl_expected."description (short)"
        )
        returning
            *
        into
            _row
        ;

        -- Test that the trigger `test_uni_l10n_en` does an INSERT instead of an UPDATE if no row for this
        -- PK + lang_code combo exists yet in `test_uni_l10n`.
        update
            test_uni_l10n_en
        set
            uni_abbr = _en_expected.uni_abbr
            ,name = _en_expected.name
            ,"description (short)" = _en_expected."description (short)"
        where
            id = _en_expected.id
        returning
            *
        into
            _row
        ;

        assert _row = _en_expected, format('%s ≠ %s', _row, _en_expected);
    end insert_instead_of_update_on_missing_l10n_record;

    <<delete_via_l10n_view>>
    declare
        _expected_id bigint := currval('test_uni_id_seq') + 1;
        _fr_expected record := row(
            _expected_id, 'MOI-UNI', null, 'fr', 'Moiversitee', 'La Premier Bla'
        )::test_uni_l10n_fr;
    begin
        insert into test_uni_l10n_fr
            (uni_abbr, name, "description (short)")
        values
            (_fr_expected.uni_abbr, _fr_expected.name, _fr_expected."description (short)")
        returning
            *
        into
            _row
        ;
        delete from test_uni_l10n_fr where id = _row.id;
        assert found;
        assert not exists (select from test_uni where id = _row.id),
            'The base table record should have been deleted.';
        assert not exists (
                select from test_uni_l10n where id = _row.id and l10n_lang_code = _row.l10n_lang_code
            )
            ,'The l10n record should have been deleted, via the `ON DELETE CASCADE` to the base table.';
    end delete_via_l10n_view;

    <<trigger_alter_table_events>>
    begin
        alter table test_uni_l10n
            add description2 text;

        update test_uni_l10n
            set description2 = 'Something to satisfy NOT NULL';  -- Because we want to make it NOT NULL.

        alter table test_uni_l10n
            alter column description2 set not null;

        select * into _l10n_table from l10n_table where base_table_name = 'test_uni';

        assert _l10n_table.l10n_column_definitions[3] = 'description2 text NOT NULL',
            'The `l10n_table__track_alter_table_events` event trigger should have updated the list of l10n'
            ' columns.';

        assert exists(
                select
                from    pg_attribute
                where   attrelid = 'test_uni_l10n_fr'::regclass
                        and attname = 'description2'
            ), 'The `description2` column should have been added to the view.';

        alter table test_uni_l10n
            drop column description2
            cascade;

        select * into _l10n_table from l10n_table where base_table_name = 'test_uni';

        assert array_length(_l10n_table.l10n_column_definitions, 1) = 2,
            'The dropped column should have been removed from the `l10n_table` meta table.';

        assert not exists(
                select
                from    pg_attribute
                where   attrelid = 'test_uni_l10n_nl'::regclass
                        and attname = 'description2'
            ), 'The `description2` column should have disappeared from the views.';

        alter table test_uni
            add non_l10n_col int
                not null
                default 6;

        select * into _l10n_table from l10n_table where base_table_name = 'test_uni';

        assert _l10n_table.base_column_definitions[4] = 'non_l10n_col integer NOT NULL DEFAULT 6',
            'The `l10n_table__track_alter_table_events` event trigger should have updated the list of base'
            ' columns.';

        assert (select non_l10n_col from test_uni_l10n_nl where id = 2) = 6;

        alter table test_uni
            drop column non_l10n_col
            cascade;

        assert not exists(
                select
                from    pg_attribute
                where   attrelid = 'test_uni_l10n_nl'::regclass
                        and attname = 'non_l10n_col'
            ), 'The `non_l10n_col` column should have disappeared from the views.';

        <<add_base_column_with_default_value>>
        declare
            _nl_expected record;
        begin
            alter table test_uni
                add column base_col_with_default text
                    not null
                    default 'I am default.';

            alter table test_uni_l10n
                add column localized_image text
                    not null
                    default 'fallback.png';

            select * into _l10n_table from l10n_table where base_table_name = 'test_uni';

            assert _l10n_table.base_column_definitions[4]
                    = 'base_col_with_default text NOT NULL DEFAULT ''I am default.''::text',
                format(
                    'The `l10n_table__track_alter_table_events` event trigger should have updated the list of'
                    ' base columns; base_column_definitions = ''%s'''
                    ,_l10n_table.base_column_definitions
                );

            assert _l10n_table.l10n_column_definitions[3]
                    = 'localized_image text NOT NULL DEFAULT ''fallback.png''::text',
                format(
                    'The `l10n_table__track_alter_table_events` event trigger should have updated the list of'
                    ' l10n columns; l10n_column_definitions = ''%s'''
                    ,_l10n_table.l10n_column_definitions
                );

            -- Now, let's test how the defaults behave on insert…

            _nl_expected := row(
                1, 'HOF', 5, 'I am default.', 'nl', 'Wim Hof', 'De Ijsman', 'fallback.png'
            )::test_uni_l10n_nl;

            insert into test_uni_l10n_nl
                (uni_abbr, name, "description (short)")
            values
                (_nl_expected.uni_abbr, _nl_expected.name, _nl_expected."description (short)")
            returning
                *
            into
                _row
            ;

            assert _row.base_col_with_default = _nl_expected.base_col_with_default,
                'Default should have propegated from the base table to view.';
            assert _row.localized_image = _nl_expected.localized_image,
                'Default should have propegated from the l10n table to view.';
        end add_base_column_with_default_value;

        <<l10n_table_rename_attempt>>
        begin
            alter table test_uni_l10n rename to test_university_l10n;
            raise assert_failure using
                message = 'Directly renaming the l10n table should be impossible.';
        exception
            when integrity_constraint_violation then
        end l10n_table_rename_attempt;

        <<base_table_rename>>
        begin
            alter table test_uni rename to test_university;
        end base_table_rename;

        <<drop_base_table>>
        begin
            drop table test_university cascade;

            assert not exists (select from l10n_table where base_table_name = 'test_university');

            raise transaction_rollback;  -- I could have used any error code, but this one seemed to fit best.
        exception
            when transaction_rollback then
        end drop_base_table;
    end trigger_alter_table_events;

    -- DELETE-ing the meta info for our l10n table should cascade cleanly, without crashing.
    delete from l10n_table where base_table_regclass = 'test_university'::regclass;

    assert to_regclass('test_university_l10n') is null,
        'The actual `_l10n` table should have been removed when deleting the meta row from `l10n_table`.';

    <<insert_natural_key>>
    declare
        _expected record;
    begin
        -- Let's make a table with a natural primary key that is _not_ `GENERATED ALWAYS`.
        create table test_species (
            scientific_name text
                primary key
            -- Just so you know: without the `year_first_described` column, the `INSERT INTO l10n_table`
            -- would not trigger a certain bug, so please do not allow regressions to occur by removing
            -- this column.
            ,year_first_described int
        );

        -- Register `test_species` with the meta table, to activate all the l10n magic.
        insert into l10n_table
            (base_table_name, l10n_column_definitions, base_lang_code)
        values
            ('test_species' ,'{common_name TEXT}' ,'en')
        ;

        insert into test_species_l10n_en
            (scientific_name, common_name, year_first_described)
        values
            ('Taraxacum officinale', 'common dandelion', 1753)
        ;

        <<insert_duplicate_natural_key>>
        begin
            insert into test_species_l10n_en
                (scientific_name, common_name)
            values
                ('Taraxacum officinale', 'uncommon dandelion')
            ;
            raise assert_failure using message = 'Duplicating a primary key shouldn''t have been possible.';
        exception
            when unique_violation then
        end insert_duplicate_natural_key;
    end insert_natural_key;

    raise transaction_rollback;  -- I could have used any error code, but this one seemed to fit best.
exception
    when transaction_rollback then
end;
$procedure$

CREATE DOMAIN currency_code AS text
  CHECK ((VALUE ~ '^[A-Z]{3}$'::text));

CREATE DOMAIN country_code_alpha2 AS text
  CHECK ((VALUE ~ '^[A-Z]{2}$'::text));

CREATE DOMAIN country_subdivision_code AS text
  CHECK ((VALUE ~ '^[A-Z]{2}-[A-Z0-9]{1,3}$'::text));

CREATE DOMAIN country_subdivision_postal_abbreviation_code AS text
  CHECK ((VALUE ~ '^[A-Z0-9]{1,3}$'::text));

CREATE DOMAIN lang_code_alpha2 AS text
  CHECK ((VALUE ~ '^[a-z]{2}$'::text));

CREATE DOMAIN lang_code_alhpa3 AS text
  CHECK ((VALUE ~ '^[a-z]{3}$'::text));