-- f1 farm role progress store (SQL-backed)
-- Stores UsadbLevelRaw/UsadbExp/UsadbEnter in sm_data.farm_f1_role

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
  _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_UsadbLevel = _idx_or("UsadbLevel", 92)
local IDX_UsadbExp = _idx_or("UsadbExp", 93)
local IDX_UsadbEnter = _idx_or("UsadbEnter", 94)

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 UsadbLevelRaw, UsadbExp, UsadbEnter, Migrated FROM sm_data.farm_f1_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 {
    usadbLevelRaw = sql.to_int(lvl, 0),
    usadbExp = sql.to_int(exp, 0),
    usadbEnter = sql.to_int(enter, 0),
    migrated = sql.to_int(migrated, 0),
  }
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

  sql.exec(con, string.format("INSERT IGNORE INTO sm_data.farm_f1_role (RoleID) VALUES (%d)", rid))

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

  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_UsadbLevel), 0)
      legacyExp = sql.to_int(_G.role.GetRoleScriptData(rid, 1, IDX_UsadbExp), 0)
      legacyEnter = sql.to_int(_G.role.GetRoleScriptData(rid, 1, IDX_UsadbEnter), 0)
    end

    local upd = string.format(
      "UPDATE sm_data.farm_f1_role SET UsadbLevelRaw=%d, UsadbExp=%d, UsadbEnter=%d, Migrated=1 WHERE RoleID=%d",
      legacyLevel, legacyExp, legacyEnter, rid
    )
    sql.exec(con, upd)
  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
  M.ensureRole(rid)
  local row = _select_role(con, rid)
  if row == nil then
    return nil
  end
  return {
    usadbLevelRaw = row.usadbLevelRaw,
    usadbExp = row.usadbExp,
    usadbEnter = row.usadbEnter,
  }
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.usadbLevelRaw ~= nil then
    sets[#sets + 1] = "UsadbLevelRaw=" .. sql.to_int(patch.usadbLevelRaw, 0)
  end
  if patch.usadbExp ~= nil then
    sets[#sets + 1] = "UsadbExp=" .. sql.to_int(patch.usadbExp, 0)
  end
  if patch.usadbEnter ~= nil then
    sets[#sets + 1] = "UsadbEnter=" .. sql.to_int(patch.usadbEnter, 0)
  end
  if #sets == 0 then
    return true
  end

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

-- Safe legacy fallback helpers (used only if schema is missing)
function M.legacyGet(RoleID)
  if type(_G.role) ~= "table" or type(_G.role.GetRoleScriptData) ~= "function" then
    return { usadbLevelRaw = 0, usadbExp = 0, usadbEnter = 0 }
  end
  return {
    usadbLevelRaw = sql.to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_UsadbLevel), 0),
    usadbExp = sql.to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_UsadbExp), 0),
    usadbEnter = sql.to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_UsadbEnter), 0),
  }
end

function M.legacySet(RoleID, patch)
  if type(patch) ~= "table" then
    return false
  end
  if type(_G.role) ~= "table" or type(_G.role.SetRoleScriptData) ~= "function" then
    return false
  end
  if patch.usadbLevelRaw ~= nil then
    _G.role.SetRoleScriptData(RoleID, 1, IDX_UsadbLevel, sql.to_int(patch.usadbLevelRaw, 0))
  end
  if patch.usadbExp ~= nil then
    _G.role.SetRoleScriptData(RoleID, 1, IDX_UsadbExp, sql.to_int(patch.usadbExp, 0))
  end
  if patch.usadbEnter ~= nil then
    _G.role.SetRoleScriptData(RoleID, 1, IDX_UsadbEnter, sql.to_int(patch.usadbEnter, 0))
  end
  return true
end

function M.schemaReady()
  return _ensure_schema()
end

return M