local inspect = require("inspect")

DB:exec([[DROP TABLE channel_members]])
DB:exec([[CREATE TABLE IF NOT EXISTS channel_members (
  nick VARCHAR(100) NOT NULL,
  channel VARCHAR(100) NOT NULL,
  PRIMARY KEY (nick, channel))]])

local function update (channel, from, action)
  local stmt = DB:prepare("INSERT OR REPLACE INTO channel_members VALUES (?, ?)")
  stmt:bind_values(from, channel)
  stmt:step()
  stmt:finalize()
end

local function remove (channel, from, action)
  local stmt = DB:prepare("DELETE FROM channel_members WHERE nick = ? AND channel = ?")
  stmt:bind_values(from, channel)
  stmt:step()
  stmt:finalize()
end

local function botcheck (name)
  local bots = {"botmeat", "11bot", "arduinobot", "commanderroot"}
  for _,bot in pairs(bots) do
    if name == bot then return true else return false end
  end
end

local function chanusers (chan)
  local stmt = DB:prepare("SELECT nick FROM channel_members WHERE channel = ? ORDER BY NICK")
  local status = stmt:bind_values(chan:lower())
  local result = {}
  if status == sqlite3.OK then
    for row in stmt:nrows() do
      if "#"..row.nick ~= chan and not botcheck(row.nick) then
        table.insert(result, row.nick)
      end
    end
  end
  stmt:finalize()
  return result
end

on_join.give = function(target, from)
  update(target, from)
end

callback.give = function(target, from)
  update(target, from)
end

on_part.give = function (target, from, msg)
  remove(target, from)
end

plugin.gacount = function (target, from, arg)
  if arg ~= nil and arg ~= "" then chan = arg else chan = target end
  local players = chanusers(chan)
  irc.say(target, "I found " .. #players .. " eligible users in channel " .. chan .. "." )
end

plugin.gausers = function (target, from, arg)
  if arg ~= nil and arg ~= "" then chan = arg else chan = target end
  local players = chanusers(chan)
  if #players > 0 then irc.say(target, inspect(players)) else irc.say(target, "No users found!") end
end

plugin.giveaway = function (target, from, arg)
  if arg ~= nil and arg ~= "" then chan = arg else chan = target end
  local players = chanusers(chan)
  if #players < 5 then 
    irc.say(target, "I found less than 5 eligible users in channel " .. chan .. ", get some more players in there!")
  else
    irc.say(target, "I chose >>> " .. players[math.random(#players)] .. " <<< out of " .. #players .. " eligible users!")
  end
end