use v5.24;
use strict;
use warnings;
use utf8;

# core modules

# CPAN modules
use DBI;
my $dsn = "DBI:mysql:database=otobo;host=db";
my $dbh = DBI->connect($dsn, 'otobo', 'otobo-docker-databasepw');

$dbh->do( <<'END_SQL' );
CREATE TABLE test_countries (
  country_en VARCHAR(100),
  country_de VARCHAR(100),
  country_si VARCHAR(100)
);
END_SQL

# country translations, sorted by the English name
my @Countries = (
    [ 'Austria',  'Österreich', 'ඔස්ට්රියාව',    1 ],
    [ 'Colombia', 'Kolumbien',   'කොලොම්බියාව', 1 ],
    [ 'Germany',  'Deutschland', 'ජර්මනිය',             1 ],
);

for my $Country ( @Countries ) {

    $dbh->do(
        "INSERT INTO test_countries ( country_en, country_de, country_si ) VALUES (?, ?, ? )",
        undef,
        $Country->@*,
    );
}
my $sel_outer_sth = $dbh->prepare( "SELECT country_de FROM test_countries" );
$sel_outer_sth->execute;
while ( my ($country_de) = $sel_outer_sth->fetchrow_array ) {
    say "deutsch: $country_de";

    my $sel_inner_sth = $dbh->prepare( "SELECT country_si FROM test_countries WHERE country_de = ?" );
    $sel_inner_sth->execute($country_de);
    while ( my ($country_si) = $sel_inner_sth->fetchrow_array ) {
        say "sinhala: $country_si";
    }
}
$dbh->do( 'DROP TABLE test_countries' );