#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

my $bycode;
my $byname;
open F, 'country.tab';
while (<F>) {
    chomp;
    my ( $id, $name, $code ) = split "\t";
    $bycode->{$code} = $id;
    $byname->{ uc($name) } = $id;
}
close F;

my $href;
my $error;

while (<DATA>) {
    chomp;
    my ( $name, $code, $rate, $nonrate, $alt ) = split '\t';
    $rate /= 100;
    next if $rate eq .3;

    my $row = {
        'name' => $name,
        'code' => defined $alt ? "$code($alt)" : $code,
        'rate' => $rate,
    };

    if ( defined $alt and exists $bycode->{$alt} ) {
        $row->{'id'} = $bycode->{$alt};
    }
    if ( not defined $row->{'id'} and exists $bycode->{$code} ) {
        $row->{'id'} = $bycode->{$code};
    }

    if ( not defined $row->{'id'} and exists $byname->{ uc($name) } ) {
        $row->{'id'} = $byname->{ uc($name) };
    }

    if ( defined $row->{'id'} ) {
        $href->{ $row->{'id'} } = $row;
    } else {
        push @{$error}, $row;
    }

}

my $pred;
foreach my $id ( sort { $a <=> $b } keys %{$href} ) {
    my $data = $href->{$id};
    push @{ $pred->{ $data->{'rate'} }->{'ids'} },   $data->{'id'};
    push @{ $pred->{ $data->{'rate'} }->{'codes'} }, $data->{'code'};
}
my $x;

foreach my $rate ( sort { $b <=> $a } keys %{$pred} ) {
    my $in  = join ",", @{ $pred->{$rate}->{'ids'} };
    my $com = join ",", @{ $pred->{$rate}->{'codes'} };
    print "-- $com\n";
    print "WHEN id IN ($in)\nTHEN $rate\n";
}

#my $x;

__DATA__
Afghanistan	AF	30	30
Akrotiri	AX	30	30
Albania	AL	30	30
Algeria	AG	30	30
American Samoa	AQ	30	30
Andorra	AN	30	30
Angola	AO	30	30
Anguilla	AV	30	30
Antarctica	AY	30	30
Antigua & Barbuda	AC	30	30
Argentina	AR	30	30
Armenia	AM	0	30
Aruba	AA	30	30
Ashmore and Cartier Islands	AT	30	30
Australia	AS	5	30	AU
Austria	AU	0	30	AT
Azerbaijan	AJ	0	30
Bahamas	BF	30	30
Bahrain	BA	30	30
Baker Island	FQ	30	30
Bangladesh	BG	10	30
Barbados	BB	5	30
Belarus	BO	0	30	BY
Belgium	BE	0	30
Belize	BH	30	30
Benin	BN	30	30
Bermuda	BD	30	30
Bhutan	BT	30	30
Bolivia	BL	30	30 BO
Bosnia-Herzegovina	BK	30	30
Botswana	BC	30	30
Bouvet Island	BV	30	30
Brazil	BR	30	30
British Indian Ocean Territory	IO	30	30
British Virgin Islands	VI	30	30
Brunei	BX	30	30
Bulgaria	BU	5	30
Burkina Faso	UV	30	30
Burma	BM	30	30
Burundi	BY	30	30
Cambodia	CB	30	30
Cameroon	CM	30	30
Canada	CA	0	30
Cape Verde	CV	30	30
Cayman Islands	CJ	30	30
Central African Republic	CT	30	30
Chad	CD	30	30
Chile	CI	30	30
China	CH	10	30
Christmas Island	KT	30	30
Clipperton Island	IP	30	30
Cocos (Keeling) Islands	CK	30	30
Colombia	CO	30	30
Comoros	CN	30	30
Congo (Brazzaville)	CF	30	30
Congo (Kinshasa)	CG	30	30
Cook Islands	CW	30	30
Coral Sea Islands	CR	30	30
Costa Rica	CS	30	30
Cote D'Ivoire (Ivory Coast)	IV	30	30
Croatia	HR	30	30
Cuba	CU	30	30
Curacao	UC	30	30
Cyprus	CY	0	30
Czech Republic	EZ	0	30
Denmark	DA	0	30
Dhekelia	DX	30	30
Djibouti	DJ	30	30
Dominica	DO	30	30
Dominican Republic	DR	30	30
East Timor	TT	30	30
Ecuador	EC	30	30
Egypt	EG	15	30
El Salvador	ES	30	30
Equatorial Guinea	EK	30	30
Eritrea	ER	30	30
Estonia	EN	10	30
Ethiopia	ET	30	30
Falkland Islands (Islas Malvinas)	FK	30	30
Faroe Islands	FO	30	30
Federated States of Micronesia	FM	30	30
Fiji	FJ	30	30
Finland	FI	0	30
France	FR	0	30
French Polynesia	FP	30	30
French Southern and Antarctic Lands	FS	30	30
Gabon	GB	30	30	GA
The Gambia	GA	30	30
Georgia	GG	0	30
Germany	GM	0	30	DE
Ghana	GH	30	30
Gibraltar	GI	30	30
Greece	GR	0	30
Greenland	GL	30	30
Grenada	GJ	30	30
Guam	GQ	30	30
Guatemala	GT	30	30
Guernsey	GK	30	30
Guinea	GV	30	30
Guinea-Bissau	PU	30	30
Guyana	GY	30	30
Haiti	HA	30	30
Heard Island and McDonald Islands	HM	30	30
Holy See	VT	30	30
Honduras	HO	30	30
Hong Kong	HK	30	30
Howland Island	HQ	30	30
Hungary	HU	0	30
Iceland	IC	0	30	IS
India	IN	15	30
Indonesia	ID	10	30
Iran	IR	30	30
Iraq	IZ	30	30
Ireland	EI	0	30
Israel	IS	10	30	IL
Italy	IT	0	30
Jamaica	JM	10	30
Jan Mayen	JN	30	30
Japan	JA	0	30
Jarvis Island	DQ	30	30
Jersey	JE	30	30
Johnston Atoll	JQ	30	30
Jordan	JO	30	30
Kazakhstan	KZ	10	30
Kenya	KE	30	30
Kingman Reef	KQ	30	30
Kiribati	KR	30	30 KI
Korea, Democratic People's Republic of (North)	KN	30	30
Korea, Republic of (South)	KS	10	30	KR
Kosovo	KV	30	30
Kuwait	KU	30	30
Kyrgyzstan	KG	0	30
Laos	LA	30	30
Latvia	LG	10	30
Lebanon	LE	30	30
Lesotho	LT	30	30
Liberia	LI	30	30
Libya	LY	30	30
Liechtenstein	LS	30	30
Lithuania	LH	10	30
Luxembourg	LU	0	30
Macau	MC	30	30
Macedonia	MK	30	30
Madagascar	MA	30	30
Malawi	MI	30	30
Malaysia	MY	30	30
Maldives	MV	30	30
Mali	ML	30	30
Malta	MT	10	30
Man, Isle of	IM	30	30
Marshall Islands	RM	30	30
Mauritania	MR	30	30
Mauritius	MP	30	30
Mexico	MX	10	30
Midway Islands	MQ	30	30
Moldova	MD	0	30
Monaco	MN	30	30
Mongolia	MG	30	30
Montenegro	MJ	30	30
Montserrat	MH	30	30
Morocco	MO	10	30
Mozambique	MZ	30	30
Namibia	WA	30	30
Nauru	NR	30	30
Navassa Island	BQ	30	30
Nepal	NP	30	30
Netherlands	NL	0	30
New Caledonia	NC	30	30
New Zealand	NZ	5	30
Nicaragua	NU	30	30
Niger	NG	30	30
Nigeria	NI	30	30
Niue	NE	30	30
Norfolk Island	NF	30	30
Northern Mariana Islands	CQ	30	30
Norway	NO	0	30
Oman	MU	30	30
Other Country	OC	30	30
Pakistan	PK	0	30
Palau	PS	30	30
Palmyra Atoll	LQ	30	30
Panama	PM	30	30
Papua-New Guinea	PP	30	30
Paracel Islands	PF	30	30
Paraguay	PA	30	30
Peru	PE	30	30
Philippines	RP	15	30
Pitcairn Islands	PC	30	30
Poland	PL	10	30
Portugal	PO	10	30
Puerto Rico	RQ	30	30
Qatar	QA	30	30
Romania	RO	10	30
Russia	RS	0	30
Rwanda	RW	30	30
Saint Barthelemy	TB	30	30
Saint Martin	RN	30	30
Samoa	WS	30	30
San Marino	SM	30	30
Sao Tome and Principe	TP	30	30
Saudi Arabia	SA	30	30
Senegal	SG	30	30
Serbia	RI	30	30
Seychelles	SE	30	30
Sierra Leone	SL	30	30
Singapore	SN	30	30
Sint Maarten	NN	30	30
Slovakia	LO	0	30
Slovenia	SI	5	30
Solomon Islands	BP	30	30
Somalia	SO	30	30
South Africa	SF	0	30
South Georgia and the South Sandwich Islands	SX	30	30
South Sudan	OD	30	30
Spain	SP	0	30
Spratly Islands	PG	30	30
Sri Lanka	CE	10	30
St. Helena	SH	30	30
St. Kitts and Nevis	SC	30	30
St. Lucia Island	ST	30	30
St. Pierre and Miquelon	SB	30	30
St. Vincent and the Grenadines	VC	30	30
Sudan	SU	30	30
Suriname	NS	30	30
Svalbard	SV	30	30
Swaziland	WZ	30	30
Sweden	SW	0	30
Switzerland	SZ	0	30	CH
Syria	SY	30	30
Taiwan	TW	30	30
Tajikistan	TI	0	30
Tanzania	TZ	30	30
Thailand	TH	5	30
Togo	TO	30	30
Tokelau	TL	30	30
Tonga	TN	30	30
Trinidad and Tobago	TD	0	30	TT
Tunisia	TS	15	30
Turkey	TU	10	30
Turkmenistan	TX	0	30
Turks and Caicos Islands	TK	30	30
Tuvalu	TV	30	30
Uganda	UG	30	30
Ukraine	UP	10	30
United Arab Emirates	AE	30	30
United Kingdom (England, Northern Ireland, Scotland, and Wales)	UK	0	30	GB
Uruguay	UY	30	30
Uzbekistan	UZ	0	30
Vanuatu	NH	30	30
Venezuela	VE	10	30
Vietnam	VM	30	30
Virgin Islands	VQ	30	30
Wake Island	WQ	30	30
Wallis and Futuna	WF	30	30
Western Sahara	WI	30	30
Yemen (Aden)	YM	30	30
Zambia	ZA	30	30
Zimbabwe	ZI	30	30