# coding: utf-8
class UserDB
  def initialize(db = "db/otp.sq3")
    @from = MAILFROM
    @expire_after = '+5 minutes'
    @db = SQLite3::Database.new(db)
    @db.execute_batch(<<~EOF)
	CREATE TABLE IF NOT EXISTS users(
          user TEXT PRIMARY KEY,
          gecos TEXT,
          last DATETIME,
          expire DATETIME
	);
	CREATE TABLE IF NOT EXISTS session(
          user TEXT,
          keytype TEXT,		-- 'tmpkey' or 'sesskey'
          key TEXT,		-- key itself
          expire DATETIME,	-- expiration date-time
          UNIQUE(user, keytype, key)
        );
        CREATE TABLE IF NOT EXISTS property(
          user TEXT,
          prop TEXT,
          val  TEXT,
          time DATETIME,
          FOREIGN KEY(user) REFERENCES users(user)
          ON UPDATE CASCADE ON DELETE CASCADE
        );
        PRAGMA FOREIGN_KEYS=on;
	EOF
    @db
  end
  def sendmail(rcpt, subj, body)
    # INPUT:	rcpt = string or array of to-addresses
    #		subj = Subject header of email
    #		body = Message body
    require 'nkf'
    rcpt = rcpt.join(" ") if rcpt.is_a?(Array)
    sj = NKF.nkf('-jM', subj)
    body = NKF.nkf('-j', body)
    ENV["PATH"] = "/bin:/usr/bin:/usr/sbin:/usr/local/bin "
    open("| sendmail -f #{@from} #{rcpt}", "w") do |m|
      m.puts(<<~EOF)
        Subject: #{sj}
        From: OTPsample Admin <#{@from}>
        Mime-Version: 1.0
        Content-type: text/plain; charset=iso-2022-jp

        EOF
      m.puts(body)
    end
  end
  def sendPasscode(email)
    if /.+@.+\..+/ =~ email
      passcode = sprintf("%04d", rand(10000))
      sendmail(email, "OTP: passcode", "Your passcode of OTP = #{passcode}")
      passcode
    end
  end
  def expire
    @db.execute("DELETE FROM session WHERE expire<datetime('now','localtime')")
  end
  def genKey(n=50)
    rand(10**n).to_s(36)		# 10^50の乱数の36進数表記
  end
  def genSkey(user)
    expire				# まず時間切れのキーを消す
    sKey = genKey
    @db.execute("INSERT INTO session VALUES(?, 'sesskey', ?,
	datetime('now', 'localtime', '#{@expire_after}'));", user, sKey)
    sKey
  end
  def genTmpkey(user)
    passcode = sendPasscode(user)
    STDERR.puts "passcode: #{passcode}"
    tmpkey   = genKey
    joined   = tmpkey + "-" + passcode
    @db.execute("INSERT INTO session VALUES(?, 'tmpkey', ?,
	datetime('now', 'localtime', '#{@expire_after}'));", user, joined)
    tmpkey
  end
  def confirmUserAdd(user)
    r = @db.execute("SELECT user from users WHERE user=?", user)
    if !r[0]
      @db.execute("INSERT INTO users(user) VALUES(?)", user)
    end
  end
  def authTmpKey(user, tmpkey, code)	# tmpkeyとパスコードの組み合わせで認証
    expire				# 先に期限切れのキーを消す
    joined = tmpkey + "-" + code
    r = @db.execute(
      "SELECT user FROM session WHERE user=? AND keytype='tmpkey'
	AND key=?", user, joined)
    if r[0] && r[0].length==1
      # このように認証が通ったときは単に true を返すのではなく
      # その後の処理に使い回せる値を返す(updateSkeyも参照)
      updateSkey(user, genKey)
    end
  end
  def authSkey(user, skey)		# ブラウザに残るセッションキーで認証
    expire
    r = @db.execute(
      "SELECT user FROM session WHERE user=? AND keytype='sesskey' AND key=?",
      user, skey)
    if r[0] && r[0].length==1
      r = @db.execute("SELECT user FROM users WHERE user=?", user)
      expire = "datetime('now', 'localtime', '+50 days')"
      if r[0]                   # 既にユーザがいる場合はUPDATE
        @db.execute(<<~EOF, user)
	  UPDATE users
	   SET last=datetime('now', 'localtime'), expire=#{expire}
         WHERE user=?
	EOF
      else
        # 既存ユーザに対して REPLACE INTO を使うと
        # 一瞬削除されるのでFOREIGN KEY制約で関連データが消える
        @db.execute(<<~EOF, user)
	INSERT INTO users(user, last, expire) VALUES(?,
	  datetime('now', 'localtime'),
	  datetime('now', 'localtime', '+50 days'))
	EOF
      end
      updateSkey(user, skey)
    end
  end
  def updateSkey(user, sKey)
    expire				# まず時間切れのキーを消す
    confirmUserAdd(user)
    @db.execute("REPLACE INTO session VALUES(?, 'sesskey', ?,
	datetime('now', 'localtime', '#{@expire_after}'));", user, sKey)
    addProps(user, "login", Time.now.strftime("%F %T"))
    sKey	# updateした値そのものを返すことで呼び主が次の処理に使える
  end
  # ここから下のメソッドは遊びチャットなので無視してよい
  def addProps(user, prop, val)
    @db.execute(<<~EOF, user, prop, val)
	INSERT INTO property VALUES(?, ?, ?, datetime('now', 'localtime'))
	EOF
  end
  def countProps(user, prop, val)
    r = @db.execute(<<~EOF, user, prop, val)
	SELECT count(*) FROM property
	WHERE user=? AND prop=? AND val=?
	EOF
    # p r[0]
    (r[0] && r[0][0].to_i > 0) ? r[0][0].to_i : 0 # count>1ならそれ さもなくば0
  end
  def notAI(hash, user)         # これも遊びメソッドなので無視してよい
    if (msg = hash["note"])
      addProps(user, "note", msg)
      if msg == "?"
        r = @db.execute("SELECT COUNT(*) FROM property WHERE user=?", user)
        r[0] and return sprintf("主に会うのは%d度目じゃ", r[0][0].to_i)
      end
      case countProps(user, "note", msg)
      when 1
        %w(ほう なるほど ふむ それは? わからぬ しらぬ おっぉう はて
        ああ 御意 まて そうか だな んだす んぐ ... まじか え、
        初耳だ それな けしからぬ).sample
      when 5, 10
        "しつこい"
      when 4
        msg+"が好きなようじゃな。もっと深く語ってくれてもよいぞ。"
      when 2..11
        "それは聞いた"
      else
        "暇なのか? わしは暇だ"
      end
    end
  end
end