require 'rubygems'
require 'sqlite3'

# updates synop-*.tsv using wmo9.sqlite

class App

  def initialize argv
    @dbfile = argv.shift
    @db = nil
  end

  def lookup idxnum
    sql = <<-endsql
      SELECT lat, lon FROM vola
      WHERE idxnum = ?
      ORDER BY idxsub ASC LIMIT 1
    endsql
    rows = @db.execute(sql, idxnum.to_i)
    raise "station #{idxnum} missing" if rows.empty?
    lat, lon = rows.first
    format('%+05.1f%+06.1f/', lat, lon) 
  end

  def convert fp
    for line in fp
      words = line.chomp.split(/\t/)
      words[2] = lookup(words[0]) if /^\d\d\d\d\d$/ === words[0]
      puts words.join("\t")
    end
  end

  def run argv
    @db = SQLite3::Database.new(@dbfile)
    for file in argv
      File.open(file, 'r') {|fp| convert(fp) }
    end
    convert(STDIN) if argv.empty?
  ensure
    @db.close
  end

end

App.new(ARGV).run(ARGV)