-- f1 pet + quest role progress store (SQL-backed)
-- Stores Petf* and F1_zakaz_* and Pet_f1_* fields in sm_data.farm_pet_role

local M = {}

local _schema_ok = false

local function _ensure_sql()
  if type(_G.Sql_con) == "function" then
    return true
  end
  pcall(require, "Sql_con")
  if type(_G.Sql_con) == "function" then
    return true
  end
  pcall(dofile, "server_data/script/Sql_con.lua")
  if type(_G.Sql_con) == "function" then
    return true
  end
  pcall(dofile, "../server_data/script/Sql_con.lua")
  return type(_G.Sql_con) == "function"
end

local function _to_int(v, default)
  local n = tonumber(v)
  if n == nil then
    return default or 0
  end
  if n ~= n or n == math.huge or n == -math.huge then
    return default or 0
  end
  return math.floor(n)
end

local function _exec(con, sql)
  if con == nil or type(con.execute) ~= "function" then
    return false
  end
  local ok, r1, r2 = pcall(function()
    return con:execute(sql)
  end)
  if ok and r1 ~= nil then
    return true
  end
  _G._farm_pet_sql_last_error = tostring(ok and r2 or r1)
  return false
end

local function _select1(con, sql)
  if con == nil or type(con.execute) ~= "function" then
    return nil
  end
  local ok, cur_or_val, err = pcall(function()
    return con:execute(sql)
  end)
  if not ok or cur_or_val == nil then
    _G._farm_pet_sql_last_error = tostring(ok and err or cur_or_val)
    return nil
  end
  local cur = cur_or_val
  local v = nil
  if type(cur) == "userdata" and type(cur.fetch) == "function" then
    v = cur:fetch()
    pcall(function() cur:close() end)
  end
  return v
end

local function _idx_or(name, fallback)
  if type(_G.RoleDataType) == "table" and _G.RoleDataType[name] ~= nil then
    return _to_int(_G.RoleDataType[name], fallback)
  end
  return fallback
end

local IDX_Petf1Level = _idx_or("Petf1Level", 114)
local IDX_Petf1Exp = _idx_or("Petf1Exp", 115)
local IDX_Pet_f1_Step = _idx_or("Pet_f1_Step", 116)
local IDX_Pet_f1_Update = _idx_or("Pet_f1_Update", 117)
local IDX_F1_zakaz_gi = _idx_or("F1_zakaz_gi", 118)
local IDX_F1_zakaz_exp = _idx_or("F1_zakaz_exp", 119)
local IDX_Petf2Level = _idx_or("Petf2Level", 120)
local IDX_Petf2Exp = _idx_or("Petf2Exp", 121)
local IDX_Petf3Level = _idx_or("Petf3Level", 122)
local IDX_Petf3Exp = _idx_or("Petf3Exp", 123)
local IDX_Pet_f1_Step_qixu = _idx_or("Pet_f1_Step_qixu", 124)

local function _getCon()
  _ensure_sql()
  if type(_G.Sql_con) ~= "function" then
    return nil
  end
  return _G.Sql_con()
end

local function _ensure_schema()
  if _schema_ok then
    return true
  end
  local con = _getCon()
  if con == nil then
    return false
  end
  local exists = _select1(con, [[
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'sm_data' AND table_name = 'farm_pet_role'
]])
  if _to_int(exists) < 1 then
    _G._farm_pet_sql_last_error = "farm_pet_role schema missing: run server_data/script/activity/провер/Ферма/schema_pet.sql"
    return false
  end
  _schema_ok = true
  return true
end

local function _select_role(con, roleId)
  local rid = _to_int(roleId, 0)
  if rid <= 0 then
    return nil
  end
  local q = string.format(
    "SELECT Petf1Level, Petf1Exp, Petf2Level, Petf2Exp, Petf3Level, Petf3Exp, F1_zakaz_gi, F1_zakaz_exp, Pet_f1_Step, Pet_f1_Update, Pet_f1_Step_qixu, Migrated FROM sm_data.farm_pet_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 l1, e1, l2, e2, l3, e3, zg, ze, step, upd, stepq, migrated = cur:fetch()
  pcall(function() cur:close() end)
  if l1 == nil then
    return nil
  end
  return {
    petf1Level = _to_int(l1, 0),
    petf1Exp = _to_int(e1, 0),
    petf2Level = _to_int(l2, 0),
    petf2Exp = _to_int(e2, 0),
    petf3Level = _to_int(l3, 0),
    petf3Exp = _to_int(e3, 0),
    zakazGi = _to_int(zg, 0),
    zakazExp = _to_int(ze, 0),
    petStep = _to_int(step, 0),
    petUpdate = _to_int(upd, 0),
    petStepQixu = _to_int(stepq, 0),
    migrated = _to_int(migrated, 0),
  }
end

function M.ensureRole(RoleID)
  if not _ensure_schema() then
    return false
  end
  local con = _getCon()
  if con == nil then
    return false
  end
  local rid = _to_int(RoleID, 0)
  if rid <= 0 then
    return false
  end

  _exec(con, string.format("INSERT IGNORE INTO sm_data.farm_pet_role (RoleID) VALUES (%d)", rid))
  local row = _select_role(con, rid)
  if row == nil then
    return false
  end

  if row.migrated == 0 then
    local legacy = M.legacyGet(rid)
    local upd = string.format(
      "UPDATE sm_data.farm_pet_role SET Petf1Level=%d, Petf1Exp=%d, Petf2Level=%d, Petf2Exp=%d, Petf3Level=%d, Petf3Exp=%d, F1_zakaz_gi=%d, F1_zakaz_exp=%d, Pet_f1_Step=%d, Pet_f1_Update=%d, Pet_f1_Step_qixu=%d, Migrated=1 WHERE RoleID=%d",
      legacy.petf1Level, legacy.petf1Exp,
      legacy.petf2Level, legacy.petf2Exp,
      legacy.petf3Level, legacy.petf3Exp,
      legacy.zakazGi, legacy.zakazExp,
      legacy.petStep, legacy.petUpdate, legacy.petStepQixu,
      rid
    )
    _exec(con, upd)
  end

  return true
end

function M.getRole(RoleID)
  if not _ensure_schema() then
    return nil
  end
  local con = _getCon()
  if con == nil then
    return nil
  end
  local rid = _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 {
    petf1Level = row.petf1Level,
    petf1Exp = row.petf1Exp,
    petf2Level = row.petf2Level,
    petf2Exp = row.petf2Exp,
    petf3Level = row.petf3Level,
    petf3Exp = row.petf3Exp,
    zakazGi = row.zakazGi,
    zakazExp = row.zakazExp,
    petStep = row.petStep,
    petUpdate = row.petUpdate,
    petStepQixu = row.petStepQixu,
  }
end

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

  M.ensureRole(rid)

  local sets = {}
  local function _set(name, v)
    sets[#sets + 1] = name .. "=" .. _to_int(v, 0)
  end

  if patch.petf1Level ~= nil then _set("Petf1Level", patch.petf1Level) end
  if patch.petf1Exp ~= nil then _set("Petf1Exp", patch.petf1Exp) end
  if patch.petf2Level ~= nil then _set("Petf2Level", patch.petf2Level) end
  if patch.petf2Exp ~= nil then _set("Petf2Exp", patch.petf2Exp) end
  if patch.petf3Level ~= nil then _set("Petf3Level", patch.petf3Level) end
  if patch.petf3Exp ~= nil then _set("Petf3Exp", patch.petf3Exp) end
  if patch.zakazGi ~= nil then _set("F1_zakaz_gi", patch.zakazGi) end
  if patch.zakazExp ~= nil then _set("F1_zakaz_exp", patch.zakazExp) end
  if patch.petStep ~= nil then _set("Pet_f1_Step", patch.petStep) end
  if patch.petUpdate ~= nil then _set("Pet_f1_Update", patch.petUpdate) end
  if patch.petStepQixu ~= nil then _set("Pet_f1_Step_qixu", patch.petStepQixu) end

  if #sets == 0 then
    return true
  end

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

function M.legacyGet(RoleID)
  if type(_G.role) ~= "table" or type(_G.role.GetRoleScriptData) ~= "function" then
    return {
      petf1Level = 0, petf1Exp = 0,
      petf2Level = 0, petf2Exp = 0,
      petf3Level = 0, petf3Exp = 0,
      zakazGi = 0, zakazExp = 0,
      petStep = 0, petUpdate = 0, petStepQixu = 0,
    }
  end
  return {
    petf1Level = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf1Level), 0),
    petf1Exp = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf1Exp), 0),
    petf2Level = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf2Level), 0),
    petf2Exp = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf2Exp), 0),
    petf3Level = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf3Level), 0),
    petf3Exp = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Petf3Exp), 0),
    zakazGi = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_F1_zakaz_gi), 0),
    zakazExp = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_F1_zakaz_exp), 0),
    petStep = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Pet_f1_Step), 0),
    petUpdate = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Pet_f1_Update), 0),
    petStepQixu = _to_int(_G.role.GetRoleScriptData(RoleID, 1, IDX_Pet_f1_Step_qixu), 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
  local function s(idx, v)
    _G.role.SetRoleScriptData(RoleID, 1, idx, _to_int(v, 0))
  end
  if patch.petf1Level ~= nil then s(IDX_Petf1Level, patch.petf1Level) end
  if patch.petf1Exp ~= nil then s(IDX_Petf1Exp, patch.petf1Exp) end
  if patch.petf2Level ~= nil then s(IDX_Petf2Level, patch.petf2Level) end
  if patch.petf2Exp ~= nil then s(IDX_Petf2Exp, patch.petf2Exp) end
  if patch.petf3Level ~= nil then s(IDX_Petf3Level, patch.petf3Level) end
  if patch.petf3Exp ~= nil then s(IDX_Petf3Exp, patch.petf3Exp) end
  if patch.zakazGi ~= nil then s(IDX_F1_zakaz_gi, patch.zakazGi) end
  if patch.zakazExp ~= nil then s(IDX_F1_zakaz_exp, patch.zakazExp) end
  if patch.petStep ~= nil then s(IDX_Pet_f1_Step, patch.petStep) end
  if patch.petUpdate ~= nil then s(IDX_Pet_f1_Update, patch.petUpdate) end
  if patch.petStepQixu ~= nil then s(IDX_Pet_f1_Step_qixu, patch.petStepQixu) end
  return true
end

function M.schemaReady()
  return _ensure_schema()
end

return M