--
--  rhnop
--
--  Copyright (C) 2011 Christian Pointner <equinox@helsinki.at>
--
--  This file is part of rhnop.
--
--  rhnop is free software: you can redistribute it and/or modify
--  it under the terms of the GNU General Public License as published by
--  the Free Software Foundation, either version 3 of the License, or
--  any later version.
--
--  rhnop is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU General Public License for more details.
--
--  You should have received a copy of the GNU General Public License
--  along with rhnop. If not, see <http://www.gnu.org/licenses/>.
--

luasql = require "luasql.mysql"

-- for destination database
-- CREATE DATABASE nop
-- GRANT select,insert,update,delete ON nop.* TO 'nopcollectd' IDENTIFIED BY '<password>';
-- GRANT select ON nop.* TO 'nop' IDENTIFIED BY '<password>';
-- USE nop
-- CREATE TABLE IF NOT EXISTS master (timestamp BIGINT UNSIGNED PRIMARY KEY NOT NULL, cart INT NOT NULL, len INT, showtitle VARCHAR(255), title VARCHAR(255), artist VARCHAR(255), album VARCHAR(255), ismusic BOOLEAN);
-- CREATE TABLE IF NOT EXISTS standby (timestamp BIGINT UNSIGNED PRIMARY KEY NOT NULL, cart INT NOT NULL, len INT, showtitle VARCHAR(255), title VARCHAR(255), artist VARCHAR(255), album VARCHAR(255), ismusic BOOLEAN);
-- CREATE TABLE IF NOT EXISTS state (timestamp BIGINT UNSIGNED PRIMARY KEY NOT NULL, state VARCHAR(32));
-- GRANT select,insert,update ON nop.state TO 'nopsysstated' IDENTIFIED BY '<password>';

local db = {}

function db.init(db, user, pwd, host, port, table)
   local mydb = {}

   mydb.table = table

   local err

   mydb.env, err = luasql.mysql()
   if mydb.env == nil then
      return nil, err
   end

   mydb.con, err = mydb.env:connect(db, user, pwd, host, port)
   if mydb.con == nil then
      return nil, err
   end

   local ret, err = mydb.con:setautocommit(true)
   if ret == nil then
      return nil, err
   end

   function mydb:getLastEntry()
      local cur, err = self.con:execute("SELECT MAX(timestamp) FROM " .. self.table)
      if cur == nil then
         return nil, err
      end
      
      local timestamp = cur:fetch()
      if timestamp == nil then timestamp = 0 end
      return timestamp
   end
   
   function mydb:findMissingEntries(lasttimestamp)
      local lastts = self.con:escape(lasttimestamp)
      return self.con:execute("SELECT * FROM " .. self.table .. " WHERE timestamp > " .. lastts)
   end
   
   function mydb:getNextMissingEntry(cur)
      local data = {}
      data = cur:fetch(data, "a")
      if data == nil then
         return nil, "that's all folks"
      end
      return data
   end
   
   function mydb:getEntry(timestamp)
      local ts = self.con:escape(timestamp)
      local cur, err = self.con:execute("SELECT * FROM " .. self.table .. " WHERE timestamp = " .. ts)
      if cur == nil then
         return nil, err
      end
      
      local data = {}
      data = cur:fetch(data, "a")
      if data == nil then
         return nil, "nothing found"
      end
      return data
   end
   
   function mydb:addEntry(data)
      local timestamp = self.con:escape(data.timestamp)
      local cart = self.con:escape(data.cart)
      local len = self.con:escape(data.len)
      local showtitle = self.con:escape(data.showtitle)
      local title = self.con:escape(data.title)
      local artist = self.con:escape(data.artist)
      local album = self.con:escape(data.album)
      local ismusic = self.con:escape(data.ismusic)
      
      local cur, err = self.con:execute("REPLACE INTO " .. self.table .. " VALUES('" .. timestamp .. "', '" .. cart .. "', '" .. len .. "', '" .. showtitle .. "', '" .. title .. "', '" .. artist .."', '" .. album .. "', '" .. ismusic .. "')")
      if cur == nil then
         return nil, err
      end
      
      return true
   end
   
   function mydb:getLastSysState()
      local cur, err = self.con:execute("SELECT state FROM " .. self.table .. " WHERE timestamp = (SELECT MAX(timestamp) FROM " .. self.table .. ")")
      if cur == nil then
         return nil, err
      end
      
      local state = cur:fetch()
      if state == nil then state = "unknown" end
      return state
   end

   function mydb:updateSysState(timestamp, state)
      local t = self.con:escape(timestamp)
      local s = self.con:escape(state)

      local cur, err = self.con:execute("INSERT INTO " .. self.table .. " VALUES('" .. t .. "', '" .. s .. "')")
      if cur == nil then
         return nil, err
      end
      return true
   end

   function mydb:close()
      if self.con then
         self.con:close()
      end
      
      if self.env then
         self.env:close()
      end
   end

   return mydb
end

return db