-- f1 pet plot/slot store (SQL-backed)
-- Stores 5 holes (fixed coords per instance) + which pet is placed + last feed time.
--
-- Keyed by (RoleID, SlotID) with unique (RoleID, X, Y, Z) to allow lookup by coordinate.

local function _script_dir()
  local src = debug.getinfo(1, "S").source
  if type(src) ~= "string" then
    return ""
  end
  if src:sub(1, 1) == "@" then
    src = src:sub(2)
  end
  src = src:gsub("\\", "/")
  local dir = src:match("^(.*)/")
  if dir == nil then
    return ""
  end
  return dir .. "/"
end

local function _dofile_rel(name)
  return dofile(_script_dir() .. name)
end

local sql = _dofile_rel("sql.lua")

local M = {}

local _schema_ok = false

local function _ensure_schema()
  if _schema_ok then
    return true
  end

  local con = sql.getCon()
  if con == nil then
    return false
  end

  local exists = sql.select1(con, [[
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'sm_data' AND table_name = 'farm_pet_plot'
]])

  if sql.to_int(exists, 0) < 1 then
    _G._farm_pet_plot_sql_last_error = "farm_pet_plot schema missing: run server_data/script/activity/провер/Ферма/schema_pet.sql"
    return false
  end

  _schema_ok = true
  return true
end

local function _pos_int(v)
  return sql.to_int(v, 0)
end

local function _select_by_coord(con, RoleID, X, Y, Z)
  local rid = sql.to_int(RoleID, 0)
  local x = _pos_int(X)
  local y = _pos_int(Y)
  local z = _pos_int(Z)
  if rid <= 0 then
    return nil
  end
  local q = string.format(
    "SELECT SlotID, X, Y, Z, PetTypeID, LastFeedTime FROM sm_data.farm_pet_plot WHERE RoleID=%d AND X=%d AND Y=%d AND Z=%d LIMIT 1",
    rid, x, y, z
  )
  local cur = con:execute(q)
  if cur == nil or type(cur.fetch) ~= "function" then
    return nil
  end
  local slotId, rx, ry, rz, petType, lastFeed = cur:fetch()
  pcall(function() cur:close() end)
  if slotId == nil then
    return nil
  end
  return {
    RoleID = rid,
    SlotID = sql.to_int(slotId, 0),
    X = sql.to_int(rx, x),
    Y = sql.to_int(ry, y),
    Z = sql.to_int(rz, z),
    PetTypeID = sql.to_int(petType, 0),
    LastFeedTime = sql.to_int(lastFeed, 0),
  }
end

local function _next_slot_id(con, RoleID)
  local rid = sql.to_int(RoleID, 0)
  if rid <= 0 then
    return 0
  end
  local q = string.format("SELECT COALESCE(MAX(SlotID),0)+1 FROM sm_data.farm_pet_plot WHERE RoleID=%d", rid)
  return sql.to_int(sql.select1(con, q), 0)
end

local function _insert_slot(con, RoleID, SlotID, X, Y, Z)
  local rid = sql.to_int(RoleID, 0)
  local sid = sql.to_int(SlotID, 0)
  local x = _pos_int(X)
  local y = _pos_int(Y)
  local z = _pos_int(Z)
  if rid <= 0 or sid <= 0 then
    return false
  end
  local ins = string.format(
    "INSERT IGNORE INTO sm_data.farm_pet_plot (RoleID, SlotID, X, Y, Z) VALUES (%d, %d, %d, %d, %d)",
    rid, sid, x, y, z
  )
  return sql.exec(con, ins)
end

-- Ensure there is a slot row for this coordinate; returns the slot row (with SlotID).
function M.ensureSlotForCoord(RoleID, X, Y, Z)
  if not _ensure_schema() then
    return nil
  end
  local con = sql.getCon()
  if con == nil then
    return nil
  end

  local row = _select_by_coord(con, RoleID, X, Y, Z)
  if row ~= nil then
    return row
  end

  local sid = _next_slot_id(con, RoleID)
  if sid <= 0 or sid > 32 then
    sid = 1
  end
  _insert_slot(con, RoleID, sid, X, Y, Z)
  return _select_by_coord(con, RoleID, X, Y, Z)
end

function M.getByCoord(RoleID, X, Y, Z)
  if not _ensure_schema() then
    return nil
  end
  local con = sql.getCon()
  if con == nil then
    return nil
  end
  return _select_by_coord(con, RoleID, X, Y, Z)
end

function M.setByCoord(RoleID, 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 row = M.ensureSlotForCoord(RoleID, X, Y, Z)
  if row == nil then
    return false
  end

  local rid = sql.to_int(RoleID, 0)
  local sid = sql.to_int(row.SlotID, 0)
  if rid <= 0 or sid <= 0 then
    return false
  end

  local sets = {}
  if patch.PetTypeID ~= nil then
    sets[#sets + 1] = "PetTypeID=" .. sql.to_int(patch.PetTypeID, 0)
  end
  if patch.LastFeedTime ~= nil then
    sets[#sets + 1] = "LastFeedTime=" .. sql.to_int(patch.LastFeedTime, 0)
  end
  if #sets == 0 then
    return true
  end

  local upd = string.format(
    "UPDATE sm_data.farm_pet_plot SET %s WHERE RoleID=%d AND SlotID=%d",
    table.concat(sets, ","),
    rid,
    sid
  )
  return sql.exec(con, upd)
end

function M.schemaReady()
  return _ensure_schema()
end

return M