#!/usr/bin/env ruby

require 'rubygems' rescue nil 
require 'csv'
require 'ostruct'
require 'optparse'
require 'sqlite3'
require 'pp'

module SQLite3
  class CSVImporter
    VERSION = '0.1.0'

    def parse_cli_opts(args)
      ret = OpenStruct.new

      # set up default options
      ret.use_sqlite2 = false
      ret.database = nil
      ret.debug = false

      # create option parser
      opts = OptionParser.new do |opts|
        opts.banner = 'Usage: sqlite_import.rb [options]'

        # required options
        help_str = 'Path to SQLite database file.'
        opts.on('-d', '--database DB', help_str) { |db| ret.database = db }

        help_strs = ['Table to import CSV values to.',
                     "(will be created if it doesn't exist)"]
        opts.on('-t', '--table TABLE', *help_strs) { |tbl| ret.table = tbl }

        help_str = 'Input CSV file.'
        opts.on('-i', '--input CSV', help_str) { |csv| ret.csv = csv }

        # optional options
        help_str = 'Enable debugging output.'
        opts.on('-D', '--debug', help_str) { ret.debug = true }

        opts.on_tail("-h", "--help", "Show this message.") do
          puts opts
          exit
        end

        opts.on_tail("-V", "--version", "Show version information.") do
          puts "#{File.basename($0)} #{VERSION}."
          exit
        end
      end

      # parse CLI args, return result
      opts.parse!(args)
      ret
    end

    def initialize(args)
      @opt = parse_cli_opts(args)
    end

    def csv_quote(row)
      row.map { |col| '"' << col.gsub(/"/, '""') << '"' } * ','
    end

    def run
      # read csv and open database
      csv = CSV.parse(File.read(@opt.csv))
      db = SQLite3::Database.new(@opt.database)

      # grab column headers
      cols = csv.shift
      safe_cols = cols.map { |col| col.gsub(/[^a-z0-9_]/i, '_') }

      # if table doesn't exist, then create it
      if db.table_info(@opt.table).size == 0
        sql = "CREATE TABLE #{@opt.table} (" << 
              safe_cols.map { |col| col }.join(', ') <<
              ')'
        $stderr.puts sql if @opt.debug
        db.query(sql) { }
      end
      
      cols_str = safe_cols * ','
      vals_str = safe_cols.map { |col| '?'} * ','
      sql = "INSERT INTO #{@opt.table}(#{cols_str}) VALUES (#{vals_str})"
      $stderr.puts sql if @opt.debug

      db.prepare(sql) { |st|
        # add all rows to table as a transaction
        db.transaction { 
          csv.each { |row| 
            $stderr.puts 'row: ' << csv_quote(row) if @opt.debug
            st.execute(*row) 
          } 
        }
      }

      # close database
      db.close
    end
  end
end

if __FILE__ == $0
  importer = SQLite3::CSVImporter.new(ARGV)
  importer.run
end