#!/usr/bin/env ruby

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'sqlite3'
end

db = SQLite3::Database.new('./db.sqlite')

db.execute <<-SQL
CREATE TABLE IF NOT EXISTS "emails" (
  "id" TEXT PRIMARY KEY,
  "sent_at" DATETIME,
  "message_type" TEXT
)
SQL


DATA = [
  %w(017f00907cbbfd80abbf8f926e4a0ea0 2018-01-01T01:20:53+00:00 outgoing),
  %w(b6d2882f2e1ea37ccd126cd1059d3c68 2018-01-01T02:27:06+00:00 outgoing),
  %w(ead5c2ef7fdc15082805f8f427ec8465 2018-01-01T10:38:52+08:00 incoming),
  %w(6a5ce0e4fbeb7c0b1c9f58b12dbf3792 2018-01-03T07:32:02+00:00 incoming),
  %w(e4f452230860d3da0b0ddaaaaa705100 2018-01-05T07:50:23+00:00 outgoing),
  %w(bfb56bacb927eefed6c25aada7fe0384 2018-01-05T07:55:46+00:00 incoming),
  %w(8f6d08517caf28cac8fc0b48cd0a7359 2018-01-07T08:37:39+00:00 incoming)
]

data_with_keys = DATA.map do |row_data|
  { message_id: row_data[0],
    sent_at: row_data[1],
    type: row_data[2] }
end

data_with_keys.each do |row|
  id = row[:message_id]
  sent_at = row[:sent_at]
  message_type = row[:type]

  # https://stackoverflow.com/a/19343100/
  db.execute <<-SQL
  INSERT OR IGNORE
  INTO emails ('id', 'sent_at', 'message_type')
  VALUES('#{id}', '#{sent_at}', '#{message_type}');
  SQL
end