--
-- LLRNet - network part of LLR
--
-- (C) 2004-2005 Vincent Penne
--
-- Released under GNU LIBRARY GENERAL PUBLIC LICENSE
-- (See file LICENSE that must be included with this software)
--

---------------------------------
-- sql support for llrnet server
---------------------------------

local ENV

-- table of reusable connections
-- a connection to the sql database is kept alive and can be reused 
-- up to one minute after its latest client released it.
sqlConnTable = { }

function SqlConnect(client)
   if not sqlUsed then return end

   if sqlDriver == "sqlite" then
      sqlSema = sqlSema or SemaCreate(1, 1)
      SemaWait(sqlSema)
   end

   ENV = ENV or luasql[sqlDriver] ()
   
   local conn, err
   -- try to get a connection from reusable connections table
   conn = tremove(sqlConnTable)
   if conn then
      conn = conn.conn
   else
      -- none available, create a new connection
      conn, err = 
	 ENV:connect (sqlDbName, sqlUser, sqlPassword, sqlServer, sqlServerPort)
   end

   client.sqlConn = conn

   if not conn then
      print(err)
   end
end

function SqlDisconnect(client)
   if not sqlUsed then return end

   if client.sqlConn then
--      client.sqlConn:close()

      -- unlock tables before storing it in the reusable table
      SqlUnlockTables(client)

      -- store it in the reusable connections table
      tinsert(sqlConnTable, { 
		 conn      = client.sqlConn,
		 timestamp = Seconds()
	      })
   end
   client.sqlConn = nil

   if sqlDriver == "sqlite" then
      SemaSignal(sqlSema)
   end
end

function SqlManageConnections(force)
   if not sqlUsed then return end

   -- close connections getting too old 
   -- (set force to close them all unconditionally)
   local i, n
   local s = Seconds() - 60 -- after one minute

   n = getn(sqlConnTable)
   for i=1,n,1 do
      local c = sqlConnTable[i]
      if force or c.timestamp < s then
	 print("Closing SQL connection #", i)
	 if not force then
	    tremove(sqlConnTable, i)
	 end
	 c.conn:close()
	 if not force then
	    return
	 end
      end
   end
   
   if force then
      sqlConnTable = { }
   end
end

function SqlNow()
   if sqlDriver == "sqlite" then
      return "'"..SqlDate().."'"
   else
      return "now()"
   end
end

function SqlCreateTables(client)
   if not client.sqlConn then return end

   local strs = {
      [[
CREATE TABLE `jobs` (
  `k` bigint(16) NOT NULL default '',
  `n` bigint(16) NOT NULL default '',
  `username` varchar(32) NOT NULL default '',
  `status` varchar(16) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `seconds` double NOT NULL default '0'
)
]],[[
CREATE TABLE `pairs` (
  `k` bigint(16) NOT NULL default '',
  `n` bigint(16) NOT NULL default '0',
  PRIMARY KEY  (`n`,`k`)
)
]],[[
CREATE TABLE `rejected` (
  `k` bigint(16) NOT NULL default '',
  `n` bigint(16) NOT NULL default '',
  `username` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `time` double NOT NULL default '0',
  `result` varchar(16) NOT NULL default '0'
)
]],[[
CREATE TABLE `results` (
  `k` bigint(16) NOT NULL default '',
  `n` bigint(16) NOT NULL default '',
  `username` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `time` double NOT NULL default '0',
  `result` varchar(16) NOT NULL default '0'
)
]],[[
CREATE TABLE `stats` (
  `server` varchar(32) NOT NULL default '',
  `started` datetime NOT NULL default '0000-00-00 00:00:00',
  `connections` text NOT NULL default '',
  `results` text NOT NULL default '',
  `errors` text NOT NULL default '',
  `primes` text NOT NULL default '',
  `rejected` text NOT NULL default '',
  PRIMARY KEY  (`server`)
)
]],[[
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(32) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `flags` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE (`username`)
)
]]}

  local i
  for i=1,getn(strs), 1 do
     local str = strs[i]
     if sqlUsersTable then
	str = string.gsub(str, "TABLE `users",   "TABLE `"..sqlUsersTable)
     end
     if sqlPairsTable then
	str = string.gsub(str, "TABLE `jobs",    "TABLE `"..sqlJobsTable)
	str = string.gsub(str, "TABLE `results", "TABLE `"..sqlResultsTable)
	str = string.gsub(str, "TABLE `rejected","TABLE `"..sqlRejectedTable)
	str = string.gsub(str, "TABLE `pairs",   "TABLE `"..sqlPairsTable)
     end
     if sqlStatsTable then
	str = string.gsub(str, "TABLE `stats",   "TABLE `"..sqlStatsTable)
     end

     local i, j, name = 
	string.find(str, ".*CREATE TABLE `([_%w]*)`")
     name = name or "?"

     if sqlDriver == "sqlite" then
	str = string.gsub(str, "`", "'")
     end

     if sqlDriver == "mysql" then
	str = str.." TYPE=MyISAM;"
     end

     --print(str)
     local cur, err = 
	client.sqlConn:execute(str)
     if cur then
	print(format("table '%s' created succesfully", name))
     else
	print(err)
	print(format("failed to create table '%s'", name))
     end

     if name == sqlJobsTable then
	cur, err = client.sqlConn:execute("CREATE INDEX n ON "..name.." (n, k)")
	if not cur then
	   print(err)
	   print("failed to create index n")
	end

	cur, err = client.sqlConn:execute("CREATE INDEX date ON "..name.." (date, status)")
	if not cur then
	   print(err)
	   print("failed to create index date")
	end
     end
  end
end

function SqlImportPairs(client)
   if not client.sqlConn then return end

   local i, n
   n = getn(knPairs)
   local error
   if sqlDriver == "sqlite" then
      client.sqlConn:execute("begin transaction")
   end
   for i=1,n,1 do
      local pair = knPairs[i]
      local name = pair.k.."/"..pair.n
      if not jobList[name] then 
	 local a, b = 
	    client.sqlConn:execute("insert into "..sqlPairsTable..
				   " (k,n) values ('"..
				      pair.k.."', '"..pair.n.."')")
	 if b then
	    print(b)
	    error = i
	    break
	 end
      end
   end

   if sqlDriver == "sqlite" then
      client.sqlConn:execute("end transaction")
   end
   if error then
      print("there was an error while importing pair #", error)
   else
      print("pairs imported succesfully");
   end
end

function SqlImportJobs(client)
   if not client.sqlConn then return end

   local error
   local i, job
   local s = Seconds()
   if sqlDriver == "sqlite" then
      client.sqlConn:execute("begin transaction")
   end
   for i,job in jobList do
      local a, b = 
	 client.sqlConn:execute("insert into "..sqlJobsTable..
				" (k,n,status,date,seconds,username)"..
				   " values ('"..job.k.."', '"..job.n..
				   "', '"..job.status.."', "..SqlNow()..", "..
				   tostring(s)..
				   ", '"..job.user.."')")
      if b then
	 print(b)
	 error = i
	 break
      end
   end

   if sqlDriver == "sqlite" then
      client.sqlConn:execute("end transaction")
   end
   if error then
      print("there was an error while importing job ", i)
   else
      print("jobs imported succesfully");
   end
end

local montab = {
   Jan = 1,
   Feb = 2,
   Mar = 3,
   Apr = 4,
   May = 5,
   Jun = 6,
   Jul = 7,
   Aug = 8,
   Sep = 9,
   Oct = 10,
   Nov = 11,
   Dec = 12
}
function SqlImportResults(client, table, filename)
   if not client.sqlConn then return end

   local file = openfile(filename, "r")
   if not file then return end

   local total, errors = 0, 0

   local l = file:read("*l")
   while l do
      local i, j, user
      i, j, user = string.find(l, "user=(.*)")

      if user then
	 --print("USER", user)
	 l = file:read("*l")
	 if not l then
	    break
	 end
      end

      local month, day, hour, minute, seconds, year
      local date

      i, j, day, month, year, hour, minute, seconds = 
	 string.find(l, "%[(%d*)/(%d*)/(%d%d)%s*(%d*):(%d*):(%d*).*%]")
      if year then
	 year = tostring(year + 2000)
      else
	 i, j, day, month, year, hour, minute, seconds = 
	    string.find(l, "%[(%d*)/(%d*)/(%d*)%s*(%d*):(%d*):(%d*).*%]")
      end

      if not year then
	 i, j, month, day, hour, minute, seconds, year = 
	    string.find(l, "%[%a*%s*(%a*)%s*(%d*) (%d*):(%d*):(%d*) (%d*)%]")
	 if year then
	    month = tostring(montab[month] or 1)
	 end
      end

      if not year then
	 i, j, date = 
	    string.find(l, "%[(.*)%]")
      end
      
      if year or date then
	 --print("YEAR", date or year)
	 l = file:read("*l")
	 --print(l)
	 if not l then
	    break
	 end
      end

      local k, n, res, time
      i, j, k, n, res, time = 
	 string.find(l, "(%d*)%*2%^(%d*)[+-]1.*is not prime.*Res64: (%w*).*Time : ([-%d%.]*) sec.*")
      if not res then
	 i, j, k, n, time = 
	    string.find(l, "(%d*)%*2%^(%d*)[+-]1.*is prime.*Time : ([-%d%.]*) sec.*")
	 res = "0"
      end

      if time then
	 --print("TIME", time)

	 local fields = "k, n, result, time, date"
	 local values = "'"..k.."', '"..n.."', '"..res.."', "..time
	 if year then
	    values = values..format(", '%s-%s-%s %d:%d:%d'", year, month, day, hour, minute, seconds)
	 elseif date then
	    values = values..", '"..date.."'"
	 else
	    values = values..", "..SqlNow()
	 end
	 if user then
	    fields = fields..", username"
	    values = values..", '"..user.."'"
	 end

	 local query = "insert into "..table..
	    " ("..fields..") values ("..values..")"

	 local cur, err = 
	    client.sqlConn:execute(query)
	 if err then
	    print("QUERY :", query)
	    print(err)
	    errors = errors + 1
	    --break
	 else
	    total = total + 1
	 end
      else
	 errors = errors + 1
	 --break
      end

      l = file:read("*l")      
   end

   file:close()

   print(format("Imported %d results from file '%s' to table '%s'\n%d errors",
		total, filename, table, errors))
end

function SqlUnlockTables(client)
   if not client.sqlLocked then
      return
   end
   if sqlDriver == "sqlite" then
      local cur, err =
	 client.sqlConn:execute("end transaction")
      if err then
	 print(err)
      end
   else
      local cur, err =
	 client.sqlConn:execute("unlock tables")
      if err then
	 print(err)
      end
   end
   client.sqlLocked = nil
end

function SqlLockTables(client, ...)
   if sqlDriver == "sqlite" then
      if client.sqlLocked then return end
      local cur, err =
	 client.sqlConn:execute("begin transaction")
      if err then
	 print(err)
      else
	 client.sqlLocked = 1
      end
      return
   end

   local i, n
   n = getn(arg)
   local str = "lock tables "

   for i=1,n,1 do
      if i > 1 then
	 str = str .. ", "
      end
      str = str..arg[i].." write"
   end

   local cur, err = 
      client.sqlConn:execute(str)
   if err then
      print(err)
   else
      client.sqlLocked = 1
   end
end

function SqlAskPair(client, socket)
   local ks, ns
   local update
   local cur, err

   SqlLockTables(client, sqlJobsTable, sqlPairsTable)

   cur, err = 
      client.sqlConn:execute("select k,n from "..
			     sqlJobsTable.." where date < "..
				SqlNow().." - interval "..
				jobMaxTime.." second"..
				" OR status != 'working'"..
				" limit 1")
   if cur then
      local pair = cur:fetch({}, "a")
      ks=pair and pair.k
      ns=pair and pair.n
      update = ks and ns
      cur:close()
   else
      print(err)
   end
   
   if not update then
      cur, err = 
	 client.sqlConn:execute("select k,n from "..
				sqlPairsTable..
				   " limit 1")
      if cur then
	 local pair = cur:fetch({}, "a")
	 ks=pair and pair.k
	 ns=pair and pair.n
	 cur:close()
      else
	 print(err)
      end
   end
   if ks and ns then
      
      print(format("Proposing pair %d/%d to %s", ks, ns, client.username))
      
      net_Send(socket, "OK")
      net_Send(socket, testType)
      net_Send(socket, ks)
      net_Send(socket, ns)
      --tremove(knPairs, 1)
      
      local k, n, t
      t = net_Recv(socket)
      k = net_Recv(socket)
      n = net_Recv(socket)
      if k and n and t == testType 
	 and k == ks and n == ns then
	 
	 if update then
	    cur, err = 
	       client.sqlConn:execute("update "..sqlJobsTable..
				      " set status='working'"..
					 ", seconds="..tostring(Seconds())..
					 ", date="..SqlNow()..
					 ", username='"..client.username.."'"..
					 " where k='"..k.."' AND n='"..n.."'")
	 else
	    cur, err = 
	       client.sqlConn:execute("insert into "..sqlJobsTable..
				      " (k,n,status,date,seconds,username)"..
					 " values ('"..k.."', '"..n..
					 "', 'working', "..SqlNow()..", "..
					 tostring(Seconds())..
					 ", '"..client.username.."')")
	    if err then
	       print(err)
	       net_Send(socket, "ERROR")
	       SqlUnlockTables(client)
	       return
	    end
	    
	    cur, err =
	       client.sqlConn:execute("delete from "..sqlPairsTable..
				      " where k='"..k.."' AND n='"..n.."'")
	 end

	 SqlUnlockTables(client)
	 if err then
	    print(err)
	    net_Send(socket, "ERROR")
	    return
	 end
	 
	 --dump(jobList[k.."/"..n])
	 
	 -- ACK the client
	 net_Send(socket, "OK")
	 return
      end
   end

   SqlUnlockTables(client)
   net_Send(socket, "ERROR")
end

function SqlGiveResult(client, socket, t, k, n, result)

   SqlLockTables(client, sqlJobsTable)

   local cur, err = 
      client.sqlConn:execute("select * from "..
			     sqlJobsTable..
				" where k='"..k.."' AND n='"..n.."'")
   
   local owned
   if cur then
      owned = cur:fetch({}, "a")
      if owned then
	 owned.seconds = tonumber(owned.seconds)
	 owned.user = owned.username
      end
      --dump(owned)
      cur:close()
   else
      print(err)
   end
   
   local job = owned
   if not job then
      SqlUnlockTables(client)
      job = {
	 user = client.username,
	 date = SqlDate(),
	 seconds = Seconds(),
	 k = k,
	 n = n,
	 status = "working"
      }
   end
   
   if (noUserCheck or job.user == client.username) 
--      and
--      job.status == "working" 
   then
      if job.status == "solved" then
	 owned = nil
      end
      if result == "CANCEL" or result == "ERROR" then
	 job.status = "abandonned"
      else
	 job.status = "solved"
      end
      job.result = result
      job.resultdate = SqlDate()
      
      -- update the job
      if job.status == "abandonned" then
	 cur, err =
	    client.sqlConn:execute("update "..sqlJobsTable..
				   " set status='abandonned'"..
				      " where k='"..k.."' AND n='"..n.."'")
	 SqlUnlockTables(client)
      else
	 local table = sqlResultsTable
	 if not owned then
	    table = sqlRejectedTable
	 else
	    SqlLockTables(client, sqlJobsTable, table)
	 end
	 cur, err = 
	    client.sqlConn:execute("insert into "..table..
				   " (k,n,date,time,username,result)"..
				      " values ('"..k.."', '"..n..
				      "', "..SqlNow()..", "..
				      tostring(Seconds()-job.seconds)..
				      ", '"..client.username..
				      "', '"..result.."')")
	 if owned then
	    if err then
	       print(err)
	       net_Send(socket, "ERROR")
	       return
	    end
	    
	    cur, err =
	       client.sqlConn:execute("delete from "..sqlJobsTable..
				      " where k='"..k.."' AND n='"..n.."'")
	    SqlUnlockTables(client)
	 end
      end
      
      if err then
	 print(err)
	 net_Send(socket, "ERROR")
	 return
      end
      
      net_Send(socket, "OK")
      
      -- forward to the master proxy
      ProxyForwardResult(t, k, n, result)
      
      if result == "CANCEL" or result == "ERROR" then
	 return
      end
      
      if owned then
	 stats.results = stats.results + 1
	 WriteResultToFile(job, resultsFile)
      else
	 stats.rejected = stats.rejected + 1
	 WriteResultToFile(job, rejectedFile)
      end
      
      if OnResult then
	 pcall(OnResult, t, k, n, result, job)
      end
      if result == "0" then
	 stats.primes = stats.primes + 1
	 if OnPrime then
	    pcall(OnPrime, t, k, n, job)
	 end
      end
      
      return
   end
   
   net_Send(socket, "ERROR")
end

function SqlDoubleCheck(client)
   if not client.sqlConn then return end

   local cur, err

   cur, err = 
      client.sqlConn:execute("select * from "..sqlRejectedTable)

   if not cur or err then
      print(err)
      return
   end

   local double = { }
   local rej = cur:fetch({}, "a")
   while rej do
      local d = double[rej.k.."/"..rej.n]
      if not d then
	 d = { }
	 double[rej.k.."/"..rej.n] = d
      end
      tinsert(d, rej)
      rej = cur:fetch({}, "a")
   end

   local i
   for i, rej in double do
      cur, err = 
	 client.sqlConn:execute(format("select * from %s where k='%s' AND n='%s'", sqlResultsTable, rej[1].k, rej[1].n))

      if not cur or err then
	 print(err)
	 return
      end

      local res = cur:fetch({}, "a")
      local alone
      if not res then
	 alone = 1
      end
      while res do
	 tinsert(rej, res)
	 res = cur:fetch({}, "a")
      end

      local j
      print(format("Result %s has been checked %d times", i, getn(rej)))
      local nd = 0
      for j=1,getn(rej) do
	 if rej[j].result ~= rej[1].result then
	    if nd == 1 then
	       print(" ***PROBLEM*** : we have several different results for this pair")
	       print(format(" %s : by '%s' on date '%s'", 
			    rej[1].result, rej[1].username, rej[1].date))
	    end
	    print(format(" %s : by '%s'\ton date '%s'", 
			 rej[j].result, rej[j].username, rej[j].date))
	    nd = nd+1
	 else
--	    print(format(" by '%s'\ton date '%s'", 
--			 rej[j].username, rej[j].date))
	 end
      end
      if alone then
	 print(" This result is in rejected table but not in result table")
      end
   end

end

function SqlUpdateStatus(client)
   if not client.sqlConn then return end

   local cur, err

   cur, err = 
      client.sqlConn:execute("select * from "..sqlStatsTable..
			     " where server='"..serverName.."'")

   if not cur or err then
      print(err)
      return
   end

   local f = cur:fetch({}, "a")

   if not f then
      cur, err = 
	 client.sqlConn:execute("insert into "..sqlStatsTable..
				" (server) values ('"..serverName.."')")
      if err then
	 print(err)
	 return
      end
      cur, err = 
	 client.sqlConn:execute("select * from "..sqlStatsTable..
				" where server='"..serverName.."'")
      if not cur or err then
	 print(err)
	 return
      end

      f = cur:fetch({}, "a")
   end

   cur:close()

   if not f then
      print("SQL could not update server stats")
      return
   end

   local query = "update "..sqlStatsTable.." set "
   local first = 1
   local i, v
   for i, v in stats do
      if not f[i] then
	 cur, err = 
	    client.sqlConn:execute("ALTER TABLE "..sqlStatsTable.." ADD "..
				   i.." TEXT NOT NULL")
	 if err then
	    print(err)
	    return
	 end
      end
      if tostring(v) ~= f[i] then
	 if not first then
	    query = query..", "
	 else
	    first = nil
	 end
	 query = query..i.."='"..tostring(v).."'"
      end
   end

   if first then return end

   query = query.." where server='"..serverName.."'"
   
   --print(query)

   cur, err = 
      client.sqlConn:execute(query)
   if err then
      print(err)
   end
end

return 1
