#!/usr/bin/ruby

require 'rubygems'
require 'sqlite3'

class Loader

  def initialize file
    @db = SQLite3::Database.new('wmo9.sqlite')
    @file = file
  end

  def close
    @db.close
  end

=begin
flatfile format
---------------------
regionid	1
regionname	AFRICA / AFRIQUE
countryname	ALGERIA / ALGERIE
countrycode	1030
stationid	57
indexnumber	60351
indexsubnumber	0
stationname	JIJEL- ACHOUAT
latitude	36 48N
longitude	05 53E
hp	10
hpflag	
hha	8
hhaflag	
pressuredefid	
so1	X
so2	X
so3	X
so4	X
so5	X
so6	X
so7	X
so8	X
obshs	H00-24
ua1	.
ua2	.
ua3	.
ua4	.
obsrems	A;CLIMAT(C);EVAP;M/B;METAR;SOILTEMP;SPECI;SUNDUR
=end

  def mkdb
    @db.execute_batch(<<-ENDSQL)
      CREATE TABLE IF NOT EXISTS vola(
	region	INTEGER NOT NULL,
	regname	TEXT NOT NULL,
	cntname	TEXT NOT NULL,
	cntid	INTEGER NOT NULL,
	stnid	INTEGER NOT NULL,
	idxnum	INTEGER NOT NULL,
	idxsub	INTEGER NOT NULL,
	stnname	TEXT NOT NULL,
	lat	FLOAT NOT NULL,
	lon	FLOAT NOT NULL,
	hp	INTEGER NOT NULL,
	hpflag	TEXT,
	hha	INTEGER NOT NULL,
	hhaflag	TEXT,
	presid	TEXT NOT NULL,
	so1	TEXT NOT NULL,
	so2	TEXT NOT NULL,
	so3	TEXT NOT NULL,
	so4	TEXT NOT NULL,
	so5	TEXT NOT NULL,
	so6	TEXT NOT NULL,
	so7	TEXT NOT NULL,
	so8	TEXT NOT NULL,
	obshs	TEXT NOT NULL,
	ua1	TEXT NOT NULL,
	ua2	TEXT NOT NULL,
	ua3	TEXT NOT NULL,
	ua4	TEXT NOT NULL,
	obsrems	TEXT,
	CONSTRAINT indexnumber UNIQUE (idxnum, idxsub)
      );
    ENDSQL
  end

  def degmin str
    case str
    when /^(\d+) (\d+)\s*[NE]?$/
      $1.to_i + $2.to_f / 60.0
    when /^(\d+) (\d+) (\d+)\s*[NE]?$/
      $1.to_i + $2.to_f / 60.0 + $3.to_f / 3600.0
    when /^(\d+) (\d+)\s*[SW]$/
      -($1.to_i + $2.to_f / 60.0)
    when /^(\d+) (\d+) (\d+)\s*[SW]$/
      -($1.to_i + $2.to_f / 60.0 + $3.to_f / 3600.0)
    else raise str
    end
  end

  def convline words
    row = {}
    row[:region] = words[0].to_i
    row[:regname] = words[1].split(/\s*\/\s*/).first
    row[:cntname] = words[2].split(/\s*\/\s*/).first
    row[:cntid] = words[3].to_i
    row[:stnid] = words[4].to_i
    row[:idxnum] = words[5].to_i
    row[:idxsub] = words[6].to_i
    row[:stnname] = words[7]
    row[:lat] = degmin(words[8])
    row[:lon] = degmin(words[9])
    row[:hp] = words[10].to_i
    row[:hpflag] = words[11]
    row[:hha] = words[12].to_i
    row[:hhaflag] = words[13]
    row[:presid] = words[14]
    row[:so1] = words[15]
    row[:so2] = words[16]
    row[:so3] = words[17]
    row[:so4] = words[18]
    row[:so5] = words[19]
    row[:so6] = words[20]
    row[:so7] = words[21]
    row[:so8] = words[22]
    row[:obshs] = words[23]
    row[:ua1] = words[24]
    row[:ua2] = words[25]
    row[:ua3] = words[26]
    row[:ua4] = words[27]
    row[:obsrems] = words[28]
    row
  end

  def storerow row
    begin
      @db.execute(<<-ENDSQL, row)
	REPLACE INTO vola(
	  region, regname, cntname, cntid,
	  stnid, idxnum, idxsub, stnname,
	  lat, lon, hp, hpflag, hha, hhaflag, presid, so1,
	  so2, so3, so4, so5, so6, so7, so8, obshs,
	  ua1, ua2, ua3, ua4, obsrems
	)
	VALUES (
	  :region, :regname, :cntname, :cntid,
	  :stnid, :idxnum, :idxsub, :stnname,
	  :lat, :lon, :hp, :hpflag, :hha, :hhaflag, :presid, :so1,
	  :so2, :so3, :so4, :so5, :so6, :so7, :so8, :obshs,
	  :ua1, :ua2, :ua3, :ua4, :obsrems
	)
      ENDSQL
    rescue Exception => e
      p row
      raise e
    end
  end

  def convfile
    @db.transaction do
      fp = File.open(@file)
      fp.gets
      for line in fp
        row = convline(line.sub(/\r?\n/, '').split(/\t/))
        storerow row 
      end
    end
  end

  def run
    mkdb
    convfile
    close
  end

end

for file in ARGV
  Loader.new(file).run
end