-- s51 farm plot/plant state store (SQL-backed)
-- Each plot is keyed by (MapID, InstanceID, X, Y, Z)

local sql = dofile("server_data/script/Map/s51/farm/sql.lua")

local M = {}

local _schema_ok = false

local function _ensure_schema()
  if _schema_ok then
    return true
  end
  _schema_ok = sql.ensureSchema()
  return _schema_ok
end

local function _pos_int(v)
  -- Positions in this codebase are treated as integer coordinates.
  return sql.to_int(v, 0)
end

local function _ensure_row(con, MapID, InstanceID, X, Y, Z)
  local mid = sql.to_int(MapID, 0)
  local iid = sql.to_int(InstanceID, -1)
  local x = _pos_int(X)
  local y = _pos_int(Y)
  local z = _pos_int(Z)
  if mid <= 0 then
    return false
  end
  local ins = string.format(
    "INSERT IGNORE INTO sm_data.farm_s51_plot (MapID, InstanceID, X, Y, Z) VALUES (%d, %d, %d, %d, %d)",
    mid, iid, x, y, z
  )
  return sql.exec(con, ins)
end

local function _select_plot(con, MapID, InstanceID, X, Y, Z)
  local mid = sql.to_int(MapID, 0)
  local iid = sql.to_int(InstanceID, -1)
  local x = _pos_int(X)
  local y = _pos_int(Y)
  local z = _pos_int(Z)
  if mid <= 0 then
    return nil
  end

  local q = string.format(
    "SELECT PlantId, Stage, OwnerRoleID, StageStartTime, LastPlotTime FROM sm_data.farm_s51_plot WHERE MapID=%d AND InstanceID=%d AND X=%d AND Y=%d AND Z=%d LIMIT 1",
    mid, iid, x, y, z
  )
  local cur = con:execute(q)
  if cur == nil or type(cur.fetch) ~= "function" then
    return nil
  end
  local plantId, stage, owner, stageStart, lastPlot = cur:fetch()
  pcall(function() cur:close() end)
  if plantId == nil then
    return nil
  end
  return {
    MapID = mid,
    InstanceID = iid,
    X = x,
    Y = y,
    Z = z,
    PlantId = sql.to_int(plantId, 0),
    Stage = sql.to_int(stage, 0),
    OwnerRoleID = sql.to_int(owner, 0),
    StageStartTime = sql.to_int(stageStart, 0),
    LastPlotTime = sql.to_int(lastPlot, 0),
  }
end

function M.getPlot(MapID, InstanceID, X, Y, Z)
  if not _ensure_schema() then
    return nil
  end
  local con = sql.getCon()
  if con == nil then
    return nil
  end
  _ensure_row(con, MapID, InstanceID, X, Y, Z)
  return _select_plot(con, MapID, InstanceID, X, Y, Z)
end

function M.setPlot(MapID, InstanceID, X, Y, Z, patch)
  if not _ensure_schema() then
    return false
  end
  if type(patch) ~= "table" then
    return false
  end
  local con = sql.getCon()
  if con == nil then
    return false
  end

  local mid = sql.to_int(MapID, 0)
  local iid = sql.to_int(InstanceID, -1)
  local x = _pos_int(X)
  local y = _pos_int(Y)
  local z = _pos_int(Z)
  if mid <= 0 then
    return false
  end

  _ensure_row(con, mid, iid, x, y, z)

  local sets = {}
  if patch.PlantId ~= nil then
    sets[#sets + 1] = "PlantId=" .. sql.to_int(patch.PlantId, 0)
  end
  if patch.Stage ~= nil then
    sets[#sets + 1] = "Stage=" .. sql.to_int(patch.Stage, 0)
  end
  if patch.OwnerRoleID ~= nil then
    sets[#sets + 1] = "OwnerRoleID=" .. sql.to_int(patch.OwnerRoleID, 0)
  end
  if patch.StageStartTime ~= nil then
    sets[#sets + 1] = "StageStartTime=" .. sql.to_int(patch.StageStartTime, 0)
  end
  if patch.LastPlotTime ~= nil then
    sets[#sets + 1] = "LastPlotTime=" .. sql.to_int(patch.LastPlotTime, 0)
  end
  if #sets == 0 then
    return true
  end

  local upd = string.format(
    "UPDATE sm_data.farm_s51_plot SET %s WHERE MapID=%d AND InstanceID=%d AND X=%d AND Y=%d AND Z=%d",
    table.concat(sets, ","),
    mid, iid, x, y, z
  )
  local ok = sql.exec(con, upd)
  return ok
end

function M.countActivePlants(OwnerRoleID)
  if not _ensure_schema() then
    return 0
  end
  local con = sql.getCon()
  if con == nil then
    return 0
  end
  local rid = sql.to_int(OwnerRoleID, 0)
  if rid <= 0 then
    return 0
  end
  local q = string.format(
    "SELECT COUNT(*) FROM sm_data.farm_s51_plot WHERE OwnerRoleID=%d AND Stage<>0",
    rid
  )
  local v = sql.select1(con, q)
  return sql.to_int(v, 0)
end

return M