#!/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

  # fixes Noyori accent
  REWORD = {
    'area' => 'bbox',
    'Area' => 'bbox',
    'linkText' => 'prodid',
    'LinkText' => 'prodid',
    'type' => 'cat1',
    'Type' => 'cat1',
    'category' => 'cat2',
    'Category' => 'cat2',
    'subcategory' => 'cat3',
    'Subcategory' => 'cat3',
    'location' => 'centre',
    'Location' => 'centre',
    'indicator' => 'cccc',
    'Indicator' => 'cccc',
    'grid' => 'gridType',
    'Grid' => 'gridType',
    'Level' => 'level',
    'referenceTime(hour)' => 'hours',
    'ReferenceTime(hour)' => 'hours',
    'Satellite' => 'satellite',
    'Discipline' => 'discipline',
    'Published' => 'published',
    'Updated' => 'updated',
    'Access' => 'access',
    'Resolution' => 'resolution',
    'Region' => 'region'
  }

  def mkdb
    @db.execute_batch(<<-ENDSQL)
      CREATE TABLE IF NOT EXISTS blist(
        published	FLOAT NOT NULL,
	updated		FLOAT NOT NULL,
	region		TEXT NOT NULL,
	centre		TEXT NOT NULL,
	cccc		TEXT NOT NULL,
	prodid		TEXT NOT NULL,
	cat1		TEXT NOT NULL,
	cat2		TEXT NOT NULL,
	cat3		TEXT,
	satellite	TEXT,
	discipline	TEXT,
	gridType	TEXT,
	resolution	TEXT,
	bbox		TEXT,
	level		TEXT,
	hours		TEXT,
	access		TEXT,
	content		TEXT,
	CONSTRAINT heading UNIQUE (cccc, prodid)
      );
    ENDSQL
  end

  def storerow row
    begin
      @db.execute(<<-ENDSQL, row)
	INSERT OR IGNORE INTO blist(
	  published, updated, region, centre, cccc, prodid,
	  cat1, cat2, cat3, satellite, discipline,
	  gridType, resolution, bbox, level, hours, access
	)
	VALUES (
	  :published, :updated, :region, :centre, :cccc, :prodid,
	  :cat1, :cat2, :cat3, :satellite, :discipline, :gridType,
	  :resolution, :bbox, :level, :hours, :access
	)
      ENDSQL
    rescue Exception => e
      p row
      raise e
    end
  end

  def convfile
    @db.transaction do
      fp = File.open(@file)
      rowsym = nil
      for line in fp
        row = line.chomp.sub(/^"/, '').sub(/"$/, '').split(/","/)
	unless rowsym then
	  rowsym = row.map{|s| (REWORD[s] || s).to_sym}
	else
	  hrow = Hash[rowsym.zip(row)]
          storerow hrow 
	end
      end
      @db.execute <<EOF
update blist set content = (
  select content from volc1
  where volc1.ttaaii = blist.prodid and volc1.cccc = blist.cccc)
EOF
    end
  end

  def run
    mkdb
    convfile
    close
  end

end

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