#!/usr/bin/ruby

require 'mysql'

$LOAD_PATH.push '/usr/local/etc'
require 'pshbspool-cfg'

include PSHBSpool

user, pwd = ARGV
puts("usage: #{$0} user pwd") unless pwd

db = Mysql.connect(STORAGE[:srv], user, pwd)
db.query(<<ENDSQL)
  CREATE DATABASE IF NOT EXISTS #{STORAGE[:db]}
  CHARACTER SET utf8
ENDSQL
begin
  db.query("CREATE USER '#{STORAGE[:usr]}'@'#{STORAGE[:cli]}'")
rescue Mysql::Error
end
sql = <<ENDSQL
  SET PASSWORD FOR '#{STORAGE[:usr]}'@'#{STORAGE[:cli]}'
  = PASSWORD('#{STORAGE[:pwd]}')
ENDSQL
$stderr.puts sql if $DEBUG
db.query(sql)
db.query(<<ENDSQL)
  CREATE TABLE IF NOT EXISTS
  \`#{STORAGE[:db]}\`.verified(
    ip    VARCHAR(40) NOT NULL,
    mode  VARCHAR(15) NOT NULL,
    topic VARCHAR(255) NOT NULL,
    chal  VARCHAR(510) NOT NULL,
    lsec  integer NOT NULL,
    vtok  VARCHAR(255) NOT NULL,
    ins   DATETIME NOT NULL,
    INDEX (topic)
  )
ENDSQL
db.query(<<ENDSQL)
  CREATE TABLE IF NOT EXISTS
  \`#{STORAGE[:db]}\`.posted(
    id     SERIAL PRIMARY KEY,
    ins    DATETIME NOT NULL,
    ip     VARCHAR(63) NOT NULL,
    body   BLOB NOT NULL
  )
ENDSQL
db.query(<<ENDSQL)
  CREATE TABLE IF NOT EXISTS
  \`#{STORAGE[:db]}\`.msgs(
    postid BIGINT UNSIGNED NOT NULL,
    ins    DATETIME NOT NULL,
    uri    VARCHAR(63) PRIMARY KEY,
    title  VARCHAR(255) NOT NULL,
    upd    VARCHAR(63) NOT NULL,
    author VARCHAR(63) NOT NULL,
    link   VARCHAR(127) NOT NULL,
    body   MEDIUMBLOB NOT NULL,
    wgetst VARCHAR(127) NOT NULL,
    INDEX (postid),
    INDEX (ins)
  )
ENDSQL
db.query(<<ENDSQL)
  GRANT INSERT, SELECT, DELETE
  ON \`#{STORAGE[:db]}\`.*
  TO '#{STORAGE[:usr]}'@'#{STORAGE[:cli]}'
ENDSQL
db.close