#!/usr/bin/ruby

require 'cgi'
require 'rubygems'
require 'sqlite3'

class App

  def initialize gisc
    if gisc.nil? then
      print <<EOF
usage: #{$0} GISC [SQL-FRAGMENT]

CAUTION: NEVER USE THIS PROGRAM WITH COMMANDLINE GIVEN FROM EXTERNAL USER.
/SQL-FRAGMENT/ IS JOINED TO SQL STATEMENT WITHOUT QUOTATION.
EOF
      exit 1
    end
    infile = 'wmo9.sqlite'
    @idb = SQLite3::Database.new(infile)
    @idb.execute "ATTACH DATABASE 'blog.sqlite' AS blog"
    @gisc = gisc
  end

  def close
    @idb.close
  end

  def prepare
    puts %w(ttaaii cccc gisc url).join("\t")
  end

  def store ttaaii, cccc, cntname, categ, res40
    category = CGI.escape(categ)
    tag = CGI.escape(cntname)
    base = 'http://www.shiken.kishou.go.jp/roller/'
    url = case res40
    when 'E', nil
      "#{base}tokyofile/page/filter?category=#{category}&tag=#{tag}"
    else
      "#{base}restrictedfile/page/filter?category=#{category}&tag=#{tag}"
    end
    puts [ttaaii, cccc, @gisc, url].join("\t")
  end

  def convert selection
    sql = <<-ENDSQL
      SELECT
	blog.blog.ttaaii,
	blog.blog.cccc,
	blog.blog.cntname,
	blog.blog.categ,
	volc1.category
      FROM
	blog.blog
	JOIN volc1
	  ON blog.blog.ttaaii = volc1.ttaaii
	  AND blog.blog.cccc = volc1.cccc
      #{selection}
    ENDSQL
    @idb.execute(sql) { |row|
      store(*row)
    }
  end

  def run selection
    prepare
    convert(selection)
  ensure
    close
  end

end

App.new(ARGV.shift).run(ARGV.join(' '))