-- s51 farm SQL helpers (Lua 5.4.6)
-- Uses project-standard MySQL connector: server_data/script/Sql_con.lua

local M = {}

local function _ensure_sql()
  pcall(dofile, "server_data/script/MYSQL_SETTINGS.lua")
  pcall(dofile, "MYSQL_SETTINGS.lua")
  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
    _G._farm_s51_sql_last_error = "no connection or execute"
    return false
  end
  local ok, err = pcall(function()
    con:execute(sql)
  end)
  if ok then
    return true
  end
  _G._farm_s51_sql_last_error = tostring(err or "execute error")
  return false
end

local function _create_schema(con)
  if con == nil then
    return false
  end
  local stmts = {
    "CREATE DATABASE IF NOT EXISTS `sm_data` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
    [[CREATE TABLE IF NOT EXISTS `sm_data`.`farm_s51_role` (
  `RoleID` BIGINT NOT NULL,
  `FarmLevelRaw` INT NOT NULL DEFAULT 0,
  `FarmExp` INT NOT NULL DEFAULT 0,
  `FarmEnter` TINYINT NOT NULL DEFAULT 0,
  `Migrated` TINYINT NOT NULL DEFAULT 0,
  `UpdatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`RoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4]],
    [[CREATE TABLE IF NOT EXISTS `sm_data`.`farm_s51_plot` (
  `MapID` BIGINT NOT NULL,
  `InstanceID` BIGINT NOT NULL,
  `X` INT NOT NULL,
  `Y` INT NOT NULL,
  `Z` INT NOT NULL,
  `PlantId` INT NOT NULL DEFAULT 0,
  `Stage` INT NOT NULL DEFAULT 0,
  `OwnerRoleID` BIGINT NOT NULL DEFAULT 0,
  `StageStartTime` INT NOT NULL DEFAULT 0,
  `LastPlotTime` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`MapID`, `InstanceID`, `X`, `Y`, `Z`),
  INDEX `idx_owner` (`OwnerRoleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4]],
  }
  for _, sql_stmt in ipairs(stmts) do
    if not _exec(con, sql_stmt) then
      return false
    end
  end
  return true
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_s51_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.fetch) == "function" then
    v = cur:fetch()
    pcall(function() cur:close() end)
  end
  return v
end

function M.getCon()
  _ensure_sql()
  if type(_G.Sql_con) ~= "function" then
    _G._farm_s51_sql_last_error = "Sql_con is not available"
    return nil
  end
  local con = _G.Sql_con()
  if con == nil then
    _G._farm_s51_sql_last_error = tostring(_G._sql_con_last_error or "Sql_con returned nil")
  end
  return con
end

function M.ensureSchema()
  local con = M.getCon()
  if con == nil then
    return false
  end

  if not _create_schema(con) then
    _G._farm_s51_sql_last_error = "farm_s51 schema create failed: "
      .. tostring(_G._farm_s51_sql_last_error)
    return false
  end

  return true
end

-- Expose safe numeric helpers
M.to_int = _to_int
M.exec = _exec
M.select1 = _select1

return M