-- **************************************************************************
-- basic postgres functions
-- **************************************************************************

-- luasql postgres
local sql = luasql.postgres()
local dbh = nil

-- connect to the database (or stop with an error)
local function connect()
  local err -- error message

  if dbh then
    dbh:close()
  end

  -- Load the database parameters.
  local database = fcdb.option(fcdb.param.DATABASE)
  local user     = fcdb.option(fcdb.param.USER)
  local password = fcdb.option(fcdb.param.PASSWORD)
  local host     = fcdb.option(fcdb.param.HOST)

  dbh, err = sql:connect(database, user, password, host)
  if not dbh then
    log.error('[postgres:connect]: %s', err)
    return fcdb.status.ERROR
  else
    return fcdb.status.TRUE
  end
end

-- execute a sql query
local function execute(query)
  local res -- result handle
  local err -- error message

  if not dbh then
    return fcdb.status.ERROR, "[execute] Invalid database handle."
  end

  log.verbose("Postgres query: %s", query)

  res, err = dbh:execute(query)
  if not res then
    log.error("[postgres:execute]: %s\nquery: %s", err, query)
    return fcdb.status.ERROR, err
  else
    return fcdb.status.TRUE, res
  end
end


-- **************************************************************************
-- The freeciv database tables can be created with the following:
--
-- FIXME: update postgres tables!
--
-- CREATE TABLE fcdb_auth (
--   id SERIAL NOT NULL,
--   name varchar(48) default NULL,
--   password varchar(32) default NULL,
--   email varchar(128) default NULL,
--   createtime integer default NULL,
--   accesstime integer default NULL,
--   address varchar(255) default NULL,
--   createaddress varchar(15) default NULL,
--   logincount integer default '0',
--   PRIMARY KEY  (id)
-- );
--
-- CREATE TABLE fcdb_log (
--   id SERIAL NOT NULL,
--   name varchar(32) default NULL,
--   logintime integer default NULL,
--   address varchar(255) default NULL,
--   succeed char(1) default 'S',
--   PRIMARY KEY  (id)
-- );
--
-- N.B. if the tables are not of this format, then the select, insert,
--      and update syntax in the following functions must be changed.
-- **************************************************************************

-- **************************************************************************
-- freeciv user auth functions
-- **************************************************************************

-- load user data
function user_load(conn)
  local status  -- return value (status of the request)
  local res     -- result handle
  local numrows -- number of rows in the sql result
  local row     -- one row of the sql result
  local query   -- sql query

  local fields = 'password'

  local table_user = fcdb.option(fcdb.param.TABLE_USER)
  local table_log = fcdb.option(fcdb.param.TABLE_LOG)

  if not dbh then
    log.error("Missing database connection ...")
    return fcdb.status.ERROR
  end

  local username = dbh:escape(auth.get_username(conn))
  local ipaddr = dbh:escape(auth.get_ipaddr(conn))

  -- get the password for this user
  query = string.format([[SELECT %s FROM %s WHERE name = '%s']],
                        fields, table_user, username)
  status, res = execute(query);
  if status ~= fcdb.status.TRUE then
    return fcdb.status.ERROR
  end

  numrows = res:numrows()
  if numrows == 0 then
    res:close()
    return fcdb.status.FALSE
  end

  if numrows > 1 then
    log.error('[user_load]: multiple entries (%d) for user: %s',
              numrows, username)
    res:close()
    return fcdb.status.FALSE
  end

  row = res:fetch({}, 'a')
  auth.set_password(conn, row.password)

  res:close()

  return fcdb.status.TRUE
end

-- save an user to the database
function user_save(conn)
  local status  -- return value (status of the request)
  local res     -- result handle
  local query   -- sql query

  local table_user = fcdb.option(fcdb.param.TABLE_USER)

  if not dbh then
    log.error("Missing database connection ...")
    return fcdb.status.ERROR
  end

  local username = dbh:escape(auth.get_username(conn))
  local password = dbh:escape(auth.get_password(conn))
  local ipaddr = auth.get_ipaddr(conn)

  -- insert the user
  -- FIXME: readd timestamp (index 5 and 6)
  query = string.format([[INSERT INTO %s VALUES (DEFAULT, '%s', '%s',
                          NULL, 0, 0,
                          '%s', '%s', 0)]], table_user, username,
                        password, ipaddr, ipaddr)
  status, res = execute(query)
  if status ~= fcdb.status.TRUE then
    return fcdb.status.ERROR
  end

  -- log this session
  return user_log(conn, true)
end

-- log the session
function user_log(conn, success)
  local status  -- return value (status of the request)
  local res     -- result handle
  local query   -- sql query

  if not dbh then
    log.error("Missing database connection ...")
    return fcdb.status.ERROR
  end

  local table_user = fcdb.option(fcdb.param.TABLE_USER)
  local table_log = fcdb.option(fcdb.param.TABLE_LOG)

  local username = dbh:escape(auth.get_username(conn))
  local ipaddr = auth.get_ipaddr(conn)
  local success_str = success and 'S' or 'F'

  -- update user data
  -- FIXME: readd timestamp for accesstime
  query = string.format([[UPDATE %s SET accesstime = 0,
                          address = '%s', logincount = logincount + 1
                          WHERE name = '%s']], table_user, ipaddr, username)
  status, res = execute(query)
  if status ~= fcdb.status.TRUE then
    return fcdb.status.ERROR
  end

  -- insert the log row for this user
  -- FIXME: readd timestamp for logintime
  query = string.format([[INSERT INTO %s (name, logintime, address, succeed)
                          VALUES ('%s', 0, '%s', '%s')]],
                        table_log, username, ipaddr, success_str)
  status, res = execute(query)
  if status ~= fcdb.status.TRUE then
    return fcdb.status.ERROR
  end

  return fcdb.status.TRUE
end

-- **************************************************************************
-- freeciv database entry functions
-- **************************************************************************

-- test and initialise the database connection
function database_init()
  local status -- return value (status of the request)

  log.normal('Postgres database')
  log.verbose('Testing database connection...')

  status = connect()
  if status == fcdb.status.TRUE then
    log.verbose('Database connection successful.')
  end

  return status
end

-- free the database connection
function database_free()
  log.verbose('Closing database connection ...')

  if dbh then
    dbh:close()
  end

  return fcdb.status.TRUE;
end