-- s51 farm role progress store (SQL-backed)
-- Stores FarmLevel/FarmExp/FarmEnter in sm_data.farm_s51_role

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 _idx_or(name, fallback)
  if type(_G.RoleDataType) == "table" and _G.RoleDataType[name] ~= nil then
    return sql.to_int(_G.RoleDataType[name], fallback)
  end
  return fallback
end

local IDX_FarmLevel = _idx_or("FarmLevel", 31)
local IDX_FarmExp = _idx_or("FarmExp", 32)
local IDX_FarmEnter = _idx_or("FarmEnter", 33)

local function _select_role(con, roleId)
  local rid = sql.to_int(roleId, 0)
  if rid <= 0 then
    return nil
  end
  local q = string.format(
    "SELECT FarmLevelRaw, FarmExp, FarmEnter, Migrated FROM sm_data.farm_s51_role WHERE RoleID=%d LIMIT 1",
    rid
  )
  local cur = con:execute(q)
  if cur == nil or type(cur.fetch) ~= "function" then
    return nil
  end
  local lvl, exp, enter, migrated = cur:fetch()
  pcall(function() cur:close() end)
  if lvl == nil then
    return nil
  end
  return {
    farmLevelRaw = sql.to_int(lvl, 0),
    farmExp = sql.to_int(exp, 0),
    farmEnter = sql.to_int(enter, 0),
    migrated = sql.to_int(migrated, 0),
  }
end

local function _legacy_set(rid, patch)
  if type(_G.role) ~= "table" or type(_G.role.SetRoleScriptData) ~= "function" then
    return
  end
  if patch.farmLevelRaw ~= nil then
    _G.role.SetRoleScriptData(rid, 1, IDX_FarmLevel, sql.to_int(patch.farmLevelRaw, 0))
  end
  if patch.farmExp ~= nil then
    _G.role.SetRoleScriptData(rid, 1, IDX_FarmExp, sql.to_int(patch.farmExp, 0))
  end
  if patch.farmEnter ~= nil then
    _G.role.SetRoleScriptData(rid, 1, IDX_FarmEnter, sql.to_int(patch.farmEnter, 0))
  end
end

function M.ensureRole(RoleID)
  if not _ensure_schema() then
    return false
  end
  local con = sql.getCon()
  if con == nil then
    return false
  end

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

  -- Ensure row exists
  sql.exec(con, string.format("INSERT IGNORE INTO sm_data.farm_s51_role (RoleID) VALUES (%d)", rid))

  local row = _select_role(con, rid)
  if row == nil then
    return false
  end

  -- One-time migration from legacy RoleScriptData blob (still DB-backed, but we stop using it afterwards)
  if row.migrated == 0 then
    local legacyLevel = 0
    local legacyExp = 0
    local legacyEnter = 0
    if type(_G.role) == "table" and type(_G.role.GetRoleScriptData) == "function" then
      legacyLevel = sql.to_int(_G.role.GetRoleScriptData(rid, 1, IDX_FarmLevel), 0)
      legacyExp = sql.to_int(_G.role.GetRoleScriptData(rid, 1, IDX_FarmExp), 0)
      legacyEnter = sql.to_int(_G.role.GetRoleScriptData(rid, 1, IDX_FarmEnter), 0)
    end

    local upd = string.format(
      "UPDATE sm_data.farm_s51_role SET FarmLevelRaw=%d, FarmExp=%d, FarmEnter=%d, Migrated=1 WHERE RoleID=%d",
      legacyLevel, legacyExp, legacyEnter, rid
    )
    sql.exec(con, upd)
  end

  local synced = _select_role(con, rid)
  if synced ~= nil then
    _legacy_set(rid, synced)
  end

  return true
end

function M.getRole(RoleID)
  if not _ensure_schema() then
    return nil
  end
  local con = sql.getCon()
  if con == nil then
    return nil
  end
  local rid = sql.to_int(RoleID, 0)
  if rid <= 0 then
    return nil
  end
  -- Ensure row (and migrate if needed)
  M.ensureRole(rid)
  local row = _select_role(con, rid)
  if row == nil then
    return nil
  end
  return {
    farmLevelRaw = row.farmLevelRaw,
    farmExp = row.farmExp,
    farmEnter = row.farmEnter,
  }
end

function M.setRole(RoleID, patch)
  if not _ensure_schema() then
    return false
  end
  local con = sql.getCon()
  if con == nil then
    return false
  end
  local rid = sql.to_int(RoleID, 0)
  if rid <= 0 then
    return false
  end
  if type(patch) ~= "table" then
    return false
  end

  M.ensureRole(rid)

  local sets = {}
  if patch.farmLevelRaw ~= nil then
    sets[#sets + 1] = "FarmLevelRaw=" .. sql.to_int(patch.farmLevelRaw, 0)
  end
  if patch.farmExp ~= nil then
    sets[#sets + 1] = "FarmExp=" .. sql.to_int(patch.farmExp, 0)
  end
  if patch.farmEnter ~= nil then
    sets[#sets + 1] = "FarmEnter=" .. sql.to_int(patch.farmEnter, 0)
  end
  if #sets == 0 then
    return true
  end

  local upd = string.format(
    "UPDATE sm_data.farm_s51_role SET %s WHERE RoleID=%d",
    table.concat(sets, ","),
    rid
  )
  local ok = sql.exec(con, upd)
  if ok then
    _legacy_set(rid, patch)
  end
  return ok
end

return M