#!/usr/bin/perl -w

use File::Temp "tempfile";
use Text::Unidecode;

sub myunidecode {
  my ($text) = @_;
  $text = unidecode($text);
  $text =~ s/\@/a/g;
  return $text;
}

@intlglob = glob("sources/geonames_dd_dms_date_*.txt");
$intlsrc = $intlglob[$#intlglob];

$uscitysrc = "sources/POP_PLACES.txt";
$usbigsrc = "sources/US_CONCISE.txt";
$majorsrc = "major-cities.txt";
$weathersrc = "sources/nsd_cccc.txt";

$locationdb = "locationdb.sqlite";
unlink $locationdb;

($states, $states_filename) = tempfile();
binmode $states, ":utf8";
($counties, $counties_filename) = tempfile();
binmode $counties, ":utf8";
($cities, $cities_filename) = tempfile();
binmode $cities, ":utf8";

# The columns in $intlsrc: (interesting ones in caps)
# RC UFI uni LAT LONG dms_lat dms_long mgrs jog fc DSG PC CC1 ADM1 adm2 POP ELEV CC2 NT LC SHORT_FORM generic SORT_NAME FULL_NAME FULL_NAME_ND modify_date

print "Reading $intlsrc\n";
open INTLSRC, "-|:utf8", "perl ./city-fixups.pl < $intlsrc";
while (<INTLSRC>) {
  # skip header, and historical records
  next if $. == 1 || /\(\(/ || /\(historical\)/i;

  @fields = split /\t/;
  next if $#fields != 25;

  ($rc, $id, $junk, $latitude, $longitude, $junk, $junk, $junk,
   $junk, $junk, $designation, $pc, $country_code, $state_code, $junk,
   $population, $elevation, $cc2, $name_type, $language, $short_name,
   $junk, $flat_name, $long_name, $long_name_nd, $junk) = @fields;

  # Don't use wacky complex transliteration rules for non-European
  # names; just use the non-diacritic version of the name instead
  if ($name_type eq 'N' && (!$rc || $rc > 2)) {
    $long_name = myunidecode($long_name);
    $short_name = myunidecode($short_name);
  } elsif ($name_type eq 'NS' && (!$rc || $rc > 2)) {
    $long_name = $long_name_nd;
  }

  # Also fix smart quotes and dumb spacing
  $short_name =~ s/\x{2019}/'/g;
  $short_name =~ s/  +/ /;
  $short_name =~ s/ $//;
  $long_name =~ s/\x{2019}/'/g;
  $long_name =~ s/  +/ /;
  $long_name =~ s/ $//;

  if ($designation =~ /^PCL/) {
    # /^PCL/ matches countries and other ISO-3166-like things:
    # PCLD - dependent political entity
    # PCLF - freely associated state
    # PCLI - independent political entity (ie, "country")
    # PCLIX - section of independent political entity

    if ($short_name) { $countries{$short_name} = $country_code; }
    if ($long_name) { $countries{$long_name} = $country_code; }

  } elsif ($designation eq "ADM1") {
    # ID COUNTRY_CODE STATE_CODE NAME_TYPE LANGUAGE SHORT_NAME
    # LONG_NAME FLAT_NAME
    print $states "intl-$id\t$country_code\t$country_code$state_code\t$name_type\t$language\t$short_name\t$long_name\t$flat_name\n";

  } elsif ($designation =~ /^PPL/) {
    # "PPL*" is "Populated Place"

    # Assign an "importance" to the city. Having a positive
    # $importance makes a city "bigger" in terms of figuring out how
    # close it is to a given coordinate, and having negative
    # $importance makes it "smaller". Furthermore, a negative
    # importance city can never match solely on coordinates; it needs
    # to match based on name as well.
    #
    # $importance = 2 means furthermore that if we haven't found a
    # weather station for the city after going through the whole
    # stations table, then we'll add an entry using whatever weather
    # station is closest. $importance = 3 means it's a national
    # capital.
    #
    # Our current definition of "important" is that it has designation
    # of "PPLC" (capital of a country [that is, a /PCL.*/]) or "PPLA"
    # (capital of an ADM1). We also look at the "Populated Place
    # Classification" ($pc) and population, but as of 2008-05 those
    # aren't filled in yet for most countries.
    #
    # If $pc is 4 or 5 ("unimportant"), or $designation is "PPLX"
    # (neighborhood/subdivision), we give the city $importance = -1,
    # meaning it will only be used when a weather station actually
    # matches its name; it won't get pulled in as a "nearest city"
    # match.
    if ($designation eq "PPLC") {
      $importance = 3;
    } elsif ($population && $population > 100000) {
      $importance = 2;
    } elsif ($designation eq "PPLA" || $pc eq "1" || $pc eq "2") {
      $importance = 1;
    } elsif ($designation eq "PPLX" || $pc eq "4" || $pc eq "5") {
      $importance = -1;
    } else {
      $importance = 0;
    }

    # either sqlite or pysqlite treats ".9" as a string rather than a
    # number, so make sure we say "0.9" instead
    $latitude = 0 + $latitude;
    $longitude = 0 + $longitude;

    # ID LATITUDE LONGITUDE ELEVATION IMPORTANCE COUNTRY_CODE
    # STATE_CODE COUNTY_CODE NAME_TYPE LANGUAGE SHORT_NAME LONG_NAME
    # FLAT_NAME
    print $cities "intl-$id\t$latitude\t$longitude\t$elevation\t$importance\t$country_code\t$country_code$state_code\t\t$name_type\t$language\t$short_name\t$long_name\t$flat_name\n";

    # The dataset gives two countries for some features, usually
    # meaning either that they cross a national border, or are
    # disputed.
    if ($cc2) {
      print $cities "intl-$id\t$latitude\t$longitude\t$elevation\t$importance\t$cc2\t\t\t$name_type\t$language\t$short_name\t$long_name\t$flat_name\n";
    }
  }
}
close INTLSRC;

# $usbigsrc is the "Concise Features Gazetteer", containing "Large
# features that should be labeled on maps with a scale of 1:250,000".
# We use this to find medium-to-large cities. After reading through
# that, we read $uscitysrc, the "Populated Places Gazetteer", and add
# any remaining cities from there with $importance=-1. Both of these
# files have DOS newlines. (After importing the data into sqlite,
# we'll use $majorsrc to set $importance=2 on the largest US cities.)
%uscities = ();
$/ = "\r\n";

# The columns in $usbigsrc:
# feature_id feature_name class st_alpha st_num county county_num primary_lat_dms primary_lon_dms primary_lat_dec primary_lon_dec source_lat_dms source_lon_dms source_lat_dec source_lon_dec elev map_name

print "Reading $usbigsrc\n";
open USBIGSRC, "<:utf8", $usbigsrc;
while (<USBIGSRC>) {
  # skip header, and historical records
  next if $. == 1 || /\(historical\)/i;

  # remove CRLF and split
  chomp;
  @fields = split /\t/;
  next if $#fields != 16;
  ($feature_id, $name, $class, $state, $state_num, $junk, $county_num, $junk,
   $junk, $latitude, $longitude, $junk, $junk, $junk, $junk,
   $elevation, $junk) = @fields;
  $flat = uc(myunidecode($name));

  if ($class eq "Populated Place") {
    $importance = 0;
  } else {
    next;
  }

  # ID LATITUDE LONGITUDE ELEVATION IMPORTANCE COUNTRY_CODE STATE_CODE
  # COUNTY_CODE NAME_TYPE LANGUAGE SHORT_NAME LONG_NAME FLAT_NAME
  print $cities "us-$feature_id\t$latitude\t$longitude\t$elevation\t$importance\tUS\tUS$state_num\t$county_num\tN\teng\t$name\t\t$flat\n";
  $uscities{$feature_id} = 1;
}
close USBIGSRC;

# The columns in $uscitysrc:
# feature_id feature_name class	st_alpha st_num county county_num primary_lat_dms primary_lon_dms primary_lat_dec primary_lon_dec elev map_name
print "Reading $uscitysrc\n";
open USCITYSRC, "<:utf8", $uscitysrc;
while (<USCITYSRC>) {
  # skip header, and historical records
  next if $. == 1 || /\(historical\)/i;

  # remove CRLF and split
  chomp;
  @fields = split /\t/;
  next if $#fields != 12;
  ($feature_id, $name, $class, $state, $state_num, $county, $county_num,
   $junk, $junk, $latitude, $longitude, $elevation, $junk) = @fields;
  $flat = uc(myunidecode($name));

  if (!$uscities{$feature_id}) {
    # ID LATITUDE LONGITUDE ELEVATION IMPORTANCE COUNTRY_CODE STATE_CODE
    # COUNTY_CODE NAME_TYPE LANGUAGE SHORT_NAME LONG_NAME FLAT_NAME
    print $cities "us-$feature_id\t$latitude\t$longitude\t$elevation\t-1\tUS\tUS$state_num\t$county_num\tN\teng\t$name\t\t$flat\n";
  }

  # There are no separate entries for counties, so we just extract
  # that data out of the cities, keeping track of which counties we've
  # already added
  $county_id = "us-$state_num-$county_num";
  if (!$counties{$county_id}) {
    $counties{$county_id} = 1;

    # ID COUNTRY_CODE STATE_CODE COUNTY_CODE NAME_TYPE LANGUAGE
    # SHORT_NAME LONG_NAME FLAT_NAME
    print $counties "$county_id\tUS\tUS$state_num\t$county_num\tN\teng\t$county\t\t\n";
  }
}
close USCITYSRC;

# Now add some additional data that's not in any source file...

# $intlsrc has no entry for the US or its dependencies
$countries{"United States"} = "US";
$countries{"United States of America"} = "US";
$countries{"United States Minor Outlying Islands"} = "US";
$countries{"Micronesia, Federated States of"} = "US";
# These are in $intlsrc, but not listed as countries
$countries{"Åland Islands"} = "FI01";
$countries{"Antarctica"} = "AY";
$countries{"Faroe Islands"} = "FO";
$countries{"French Guiana"} = "FG";
$countries{"French Polynesia"} = "FP";
$countries{"Greenland"} = "GL";
$countries{"Guadeloupe"} = "GP";
$countries{"Martinique"} = "MB";
$countries{"New Caledonia"} = "NC";
$countries{"Reunion"} = "RE";
$countries{"Saint Pierre and Miquelon"} = "SB";
$countries{"Svalbard"} = "SV";
$countries{"Wallis and Futuna"} = "WF";
# These are variations that appear in $weathersrc
$countries{"Congo, Republic of the"} = "CF";
$countries{"Congo, Democratic Republic of the"} = "CG";
$countries{"Cote d'Ivoire"} = "IV";
$countries{"Falkland Islands, Islas Malvinas"} = "FK";
$countries{"Gambia, The"} = "GA";
$countries{"Korea, North"} = "KN";
$countries{"Korea, South"} = "KS";
$countries{"Macedonia, The Republic of"} = "MK";
$countries{"People's Republic of China"} = "CH";
$countries{"Viet Nam"} = "VM";

$usstatedata = << "EOF";
01	AL	Alabama
02	AK	Alaska
04	AZ	Arizona
05	AR	Arkansas
06	CA	California
08	CO	Colorado
09	CT	Connecticut
10	DE	Delaware
11	DC	District of Columbia
12	FL	Florida
13	GA	Georgia
15	HI	Hawaii
16	ID	Idaho
17	IL	Illinois
18	IN	Indiana
19	IA	Iowa
20	KS	Kansas
21	KY	Kentucky
22	LA	Louisiana
23	ME	Maine
24	MD	Maryland
25	MA	Massachusetts
26	MI	Michigan
27	MN	Minnesota
28	MS	Mississippi
29	MO	Missouri
30	MT	Montana
31	NE	Nebraska
32	NV	Nevada
33	NH	New Hampshire
34	NJ	New Jersey
35	NM	New Mexico
36	NY	New York
37	NC	North Carolina
38	ND	North Dakota
39	OH	Ohio
40	OK	Oklahoma
41	OR	Oregon
42	PA	Pennsylvania
44	RI	Rhode Island
45	SC	South Carolina
46	SD	South Dakota
47	TN	Tennessee
48	TX	Texas
49	UT	Utah
50	VT	Vermont
51	VA	Virginia
53	WA	Washington
54	WV	West Virginia
55	WI	Wisconsin
56	WY	Wyoming
60	AS	American Samoa
64	FM	Federated States of Micronesia
66	GU	Guam
69	MP	Northern Mariana Islands
70	PW	Palau
72	PR	Puerto Rico
74	UM	United States Minor Outlying Islands
78	VI	United States Virgin Islands
EOF
# ID COUNTRY_CODE STATE_CODE NAME_TYPE LANGUAGE SHORT_NAME
# LONG_NAME FLAT_NAME
for $usstate (split(/\n/, $usstatedata)) {
  ($fips, $abbrev, $name) = split(/\t/, $usstate);
  ($flat = uc $name) =~ s/ //g;
  $states{$name} = "US$fips";
  $states{$abbrev} = "US$fips";
  print $states "us-$fips\tUS\tUS$fips\tN\teng\t$abbrev\t$name\t$flat\n";
}

close $states;
close $counties;
close $cities;

print "\nCreating adm1 ('states') table\n";
open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "CREATE TABLE adm1 (id TEXT, country_code TEXT, state_code TEXT, name_type TEXT, language TEXT, short_name TEXT, long_name TEXT, flat_name TEXT);\n";
print SQLITE ".separator '\t'\n";
print SQLITE ".import $states_filename adm1\n";
close SQLITE;
#unlink $states_filename;

print "Creating adm2 ('counties') table\n";
open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "CREATE TABLE adm2 (id TEXT, country_code TEXT, state_code TEXT, county_code TEXT, name_type TEXT, language TEXT, short_name TEXT, long_name TEXT, flat_name TEXT);\n";
print SQLITE ".separator '\t'\n";
print SQLITE ".import $counties_filename adm2\n";
close SQLITE;
#unlink $counties_filename;

print "Creating cities table\n";
open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "CREATE TABLE cities (id TEXT, latitude REAL, longitude REAL, elevation REAL, importance INTEGER, country_code TEXT, state_code TEXT, county_code TEXT, name_type TEXT, language TEXT, short_name TEXT, long_name TEXT, flat_name TEXT);\n";
print SQLITE ".separator '\t'\n";
print SQLITE ".import $cities_filename cities\n";
close SQLITE;
#unlink $cities_filename;

print "Indexing cities\n";
open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "CREATE INDEX short_name ON cities (country_code, short_name);\n";
print SQLITE "CREATE INDEX long_name ON cities (country_code, long_name);\n";
print SQLITE "CREATE INDEX flat_name ON cities (country_code, flat_name);\n";
print SQLITE "CREATE INDEX long_lat ON cities (country_code, longitude, latitude);\n";
print SQLITE "CREATE INDEX id ON cities (id);\n";
close SQLITE;

# Update cities by marking cities "important" if they have the same
# name as their ADM1. (This seems to do a good job of picking up major
# cities in some countries that don't have PC or POP data.)
open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "UPDATE cities SET importance=1 WHERE importance < 1 AND id IN (SELECT cities.id FROM cities INNER JOIN adm1 ON cities.country_code = adm1.country_code AND cities.state_code = adm1.state_code AND cities.flat_name = adm1.flat_name);\n";
print SQLITE "UPDATE cities SET importance=1 WHERE importance < 1 AND id IN (SELECT cities.id FROM cities INNER JOIN adm1 ON cities.country_code = adm1.country_code AND cities.state_code = adm1.state_code AND cities.long_name = adm1.short_name);\n";
close SQLITE;

# Update cities table with additional information about major cities
$/ = "\n";
open MAJORSRC, "<:utf8", $majorsrc;
open SQLITE, "|-:utf8", "sqlite3 $locationdb";

while (<MAJORSRC>) {
  chomp;
  s/\s*#.*//;

  @fields = split /\t/;
  next if $#fields != 4;
  ($country, $latitude, $longitude, $importance, $city) = @fields;

  $city =~ s/'/''/g; # for SQL

  if ($latitude && $longitude) {
    $latlo = $latitude - 0.1;
    $lathi = $latitude + 0.1;
    $lonlo = $longitude - 0.1;
    $lonhi = $longitude + 0.1;
    print SQLITE "UPDATE cities SET importance=$importance WHERE ( short_name='$city' OR long_name='$city' ) AND country_code='$country' AND importance!=-1 AND latitude BETWEEN $latlo AND $lathi AND longitude BETWEEN $lonlo AND $lonhi;\n";
  } else {
    print SQLITE "UPDATE cities SET importance=$importance WHERE ( short_name='$city' OR long_name='$city' ) AND country_code='$country' AND importance!=-1;\n";
  }
}

close MAJORSRC;
close SQLITE;

# The columns in $weathersrc are
# CODE wmo_zone wmo_id NAME STATE COUNTRY region LATITUDE_DMS LONGITUDE_DMS ue_lat ue_long ELEVATION upper_elevation primary
# (however, some lines are mising one or both of the last two fields!)
#
# The columns in the stations table will be
# CODE NAME STATE COUNTRY LATITUDE LONGITUDE ELEVATION

sub dms_to_dec {
  my ($coord) = @_;
  return 0 if $coord !~ /(\d+)-(\d+)(-(\d+))?([NSEW])/;
  my $dec = $1 + $2 / 60;
  if ($4) { $dec += $4 / 3600; }
  if ($5 =~ /[SW]/) { $dec = -$dec; }
  return $dec;
}

print "\nCreating weather stations\n";
($stations, $stations_filename) = tempfile();
open WEATHERSRC, "-|:utf8", "perl ./station-fixups.pl < $weathersrc";
while (<WEATHERSRC>) {
  # remove CRLF and split
  chomp;
  @fields = split /;/;
  next if $#fields < 11;
  
  ($code, $junk, $id, $location, $state, $country, $junk, $latitude, $longitude, $junk, $junk, $elevation) = @fields;

  $latitude = dms_to_dec($latitude);
  $longitude = dms_to_dec($longitude);

  $country_code = $countries{$country};
  if (!$country_code) {
    $alt_country = s/^(.*), (.*)$/$2 $1/;
    $country_code = $countries{$alt_country};
  }
  if (!$country_code) {
    if ($country) { print "No country for '$country'\n"; }
    $country_code = '';
  }
  if ($state) {
    $state_code = $states{$state};
    if (!$state_code) {
      print "No state for '$state'\n";
      next;
    }
  } else {
    $state_code = "";
  }

  print $stations "$code;$location;$state_code;$country_code;$latitude;$longitude;$elevation\n";
}
close WEATHERSRC;
close $stations;

open SQLITE, "|-:utf8", "sqlite3 $locationdb";
print SQLITE "CREATE TABLE stations (code TEXT, name TEXT, state TEXT, country TEXT, latitude REAL, longitude REAL, elevation REAL);\n";
print SQLITE ".separator ';'\n";
print SQLITE ".import $stations_filename stations\n";
print SQLITE "CREATE INDEX station_long_lat ON stations (longitude, latitude);\n";
close SQLITE;

# unlink $stations_filename;