/*
 * This file is part of the AzerothCore Project. See AUTHORS file for Copyright information.
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License v2 as published by the
 * Free Software Foundation.
 */

#include "EncounterJournalMgr.h"

#include "Config.h"
#include "Common.h"
#include "DatabaseEnv.h"
#include "DBCStores.h"
#include "Field.h"
#include "Log.h"
#include "ObjectMgr.h"
#include "QueryResult.h"

#include <algorithm>
#include <cstdio>
#include <filesystem>
#include <fstream>
#include <set>
#include <sstream>
#include <string>
#include <tuple>
#include <unordered_set>

namespace MoonWell::EncounterJournal
{
    namespace
    {
        std::string EscapeJsonString(std::string const& in)
        {
            std::string out;
            out.reserve(in.size() + 2);
            for (unsigned char c : in)
            {
                switch (c)
                {
                    case '"':  out += "\\\""; break;
                    case '\\': out += "\\\\"; break;
                    case '\b': out += "\\b";  break;
                    case '\f': out += "\\f";  break;
                    case '\n': out += "\\n";  break;
                    case '\r': out += "\\r";  break;
                    case '\t': out += "\\t";  break;
                    default:
                        if (c < 0x20)
                        {
                            char buf[8];
                            std::snprintf(buf, sizeof(buf), "\\u%04x", c);
                            out += buf;
                        }
                        else
                            out += static_cast<char>(c);
                        break;
                }
            }
            return out;
        }

        // Lua single-quoted string literal escaper.
        std::string EscapeLuaString(std::string const& in)
        {
            std::string out;
            out.reserve(in.size() + 2);
            for (unsigned char c : in)
            {
                switch (c)
                {
                    case '\'': out += "\\'";  break;
                    case '\\': out += "\\\\"; break;
                    case '\n': out += "\\n";  break;
                    case '\r': out += "\\r";  break;
                    default:   out += static_cast<char>(c); break;
                }
            }
            return out;
        }

        std::string FormatFloat(float v)
        {
            char buf[32];
            // %.6g gives stable output without trailing zeros and matches Lua's
            // default number formatting closely.
            std::snprintf(buf, sizeof(buf), "%.6g", v);
            return buf;
        }

        // Stable sort key (sort_order asc, id asc).
        template <typename T>
        bool BySortOrderThenId(T const& a, T const& b)
        {
            return std::tie(a.sortOrder, a.id) < std::tie(b.sortOrder, b.id);
        }
    }

    Mgr* Mgr::instance()
    {
        static Mgr inst;
        return &inst;
    }

    void Mgr::Unload()
    {
        _tiers.clear();
        _tierInstances.clear();
        _instances.clear();
        _encounters.clear();
        _creatures.clear();
        _sections.clear();
        _loot.clear();

        _instancesSorted.clear();
        _encountersByInstance.clear();
        _creaturesByEncounter.clear();
        _lootByEncounter.clear();
        _instancesByTier.clear();
    }

    bool Mgr::Load()
    {
        Unload();

        // ----- tiers -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `name`, `flags`, `sort_order` FROM `custom_ej_tier`"))
        {
            do
            {
                Field* f = result->Fetch();
                Tier t;
                t.id        = f[0].Get<uint32>();
                t.name      = f[1].Get<std::string>();
                t.flags     = f[2].Get<uint32>();
                t.sortOrder = f[3].Get<int32>();
                _tiers.push_back(std::move(t));
            } while (result->NextRow());
        }
        std::sort(_tiers.begin(), _tiers.end(), BySortOrderThenId<Tier>);

        // ----- instances -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `name`, `description`, `button_icon`, `small_button_icon`, "
            "`background`, `lore_background`, `map_id`, `area_id`, "
            "`world_map_area_id`, `flags`, `sort_order` FROM `custom_ej_instance`"))
        {
            do
            {
                Field* f = result->Fetch();
                Instance i;
                i.id              = f[0].Get<uint32>();
                i.name            = f[1].Get<std::string>();
                i.description     = f[2].Get<std::string>();
                i.buttonIcon      = f[3].Get<std::string>();
                i.smallButtonIcon = f[4].Get<std::string>();
                i.background      = f[5].Get<std::string>();
                i.loreBackground  = f[6].Get<std::string>();
                i.mapId           = f[7].Get<uint32>();
                i.areaId          = f[8].Get<uint32>();
                i.worldMapAreaId  = f[9].Get<uint32>();
                i.flags           = f[10].Get<uint32>();
                i.sortOrder       = f[11].Get<int32>();
                _instances.emplace(i.id, std::move(i));
            } while (result->NextRow());
        }

        // ----- tier_instance -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `tier_id`, `instance_id`, `sort_order` FROM `custom_ej_tier_instance`"))
        {
            do
            {
                Field* f = result->Fetch();
                TierInstance ti;
                ti.tierId     = f[0].Get<uint32>();
                ti.instanceId = f[1].Get<uint32>();
                ti.sortOrder  = f[2].Get<int32>();
                _tierInstances.push_back(ti);
            } while (result->NextRow());
        }

        // ----- encounters -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `instance_id`, `name`, `description`, "
            "`map_position_x`, `map_position_y`, `floor_index`, "
            "`world_map_area_id`, `first_section_id`, `difficulty_mask`, "
            "`flags`, `sort_order` FROM `custom_ej_encounter`"))
        {
            do
            {
                Field* f = result->Fetch();
                Encounter e;
                e.id              = f[0].Get<uint32>();
                e.instanceId      = f[1].Get<uint32>();
                e.name            = f[2].Get<std::string>();
                e.description     = f[3].Get<std::string>();
                e.mapPositionX    = f[4].Get<float>();
                e.mapPositionY    = f[5].Get<float>();
                e.floorIndex      = f[6].Get<uint32>();
                e.worldMapAreaId  = f[7].Get<uint32>();
                e.firstSectionId  = f[8].Get<uint32>();
                e.difficultyMask  = f[9].Get<uint32>();
                e.flags           = f[10].Get<uint32>();
                e.sortOrder       = f[11].Get<int32>();
                _encounters.emplace(e.id, std::move(e));
            } while (result->NextRow());
        }

        // ----- creatures -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `encounter_id`, `creature_id`, `name`, `description`, "
            "`creature_display_id`, `icon`, `difficulty_mask`, `sort_order` "
            "FROM `custom_ej_creature`"))
        {
            do
            {
                Field* f = result->Fetch();
                Creature c;
                c.id                = f[0].Get<uint32>();
                c.encounterId       = f[1].Get<uint32>();
                c.creatureId        = f[2].Get<uint32>();
                c.name              = f[3].Get<std::string>();
                c.description       = f[4].Get<std::string>();
                c.creatureDisplayId = f[5].Get<uint32>();
                c.icon              = f[6].Get<std::string>();
                c.difficultyMask    = f[7].Get<uint32>();
                c.sortOrder         = f[8].Get<int32>();
                _creatures.emplace(c.id, std::move(c));
            } while (result->NextRow());
        }

        // ----- sections -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `encounter_id`, `parent_section_id`, `next_section_id`, "
            "`sub_section_id`, `name`, `description`, `creature_display_id`, "
            "`description_spell_id`, `icon_spell_id`, `icon`, `flags`, "
            "`icon_flags`, `sort_order`, `type`, `difficulty_mask`, `creature_id` "
            "FROM `custom_ej_section`"))
        {
            do
            {
                Field* f = result->Fetch();
                Section s;
                s.id                 = f[0].Get<uint32>();
                s.encounterId        = f[1].Get<uint32>();
                s.parentSectionId    = f[2].Get<uint32>();
                s.nextSectionId      = f[3].Get<uint32>();
                s.subSectionId       = f[4].Get<uint32>();
                s.name               = f[5].Get<std::string>();
                s.description        = f[6].Get<std::string>();
                s.creatureDisplayId  = f[7].Get<uint32>();
                s.descriptionSpellId = f[8].Get<uint32>();
                s.iconSpellId        = f[9].Get<uint32>();
                s.icon               = f[10].IsNull() ? "" : f[10].Get<std::string>();
                s.flags              = f[11].Get<uint32>();
                s.iconFlags          = f[12].Get<uint32>();
                s.sortOrder          = f[13].Get<int32>();
                s.type               = f[14].Get<uint32>();
                s.difficultyMask     = f[15].Get<uint32>();
                s.creatureId         = f[16].Get<uint32>();
                _sections.emplace(s.id, std::move(s));
            } while (result->NextRow());
        }

        // ----- loot -----
        if (QueryResult result = WorldDatabase.Query(
            "SELECT `id`, `encounter_id`, `item_id`, `difficulty_mask`, "
            "`faction_mask`, `class_mask`, `flags`, `sort_order` "
            "FROM `custom_ej_loot`"))
        {
            do
            {
                Field* f = result->Fetch();
                Loot l;
                l.id             = f[0].Get<uint32>();
                l.encounterId    = f[1].Get<uint32>();
                l.itemId         = f[2].Get<uint32>();
                l.difficultyMask = f[3].Get<uint32>();
                l.factionMask    = f[4].Get<int32>();
                l.classMask      = f[5].Get<int32>();
                l.flags          = f[6].Get<uint32>();
                l.sortOrder      = f[7].Get<int32>();
                _loot.push_back(l);
            } while (result->NextRow());
        }

        // ---- build sorted access caches ----
        _instancesSorted.reserve(_instances.size());
        for (auto const& [id, _] : _instances)
            _instancesSorted.push_back(id);
        std::sort(_instancesSorted.begin(), _instancesSorted.end(),
            [this](uint32 a, uint32 b)
            {
                Instance const& ia = _instances.at(a);
                Instance const& ib = _instances.at(b);
                return std::tie(ia.sortOrder, ia.id) < std::tie(ib.sortOrder, ib.id);
            });

        for (auto const& [id, e] : _encounters)
            _encountersByInstance[e.instanceId].push_back(id);
        for (auto& [_, ids] : _encountersByInstance)
            std::sort(ids.begin(), ids.end(),
                [this](uint32 a, uint32 b)
                {
                    Encounter const& ea = _encounters.at(a);
                    Encounter const& eb = _encounters.at(b);
                    return std::tie(ea.sortOrder, ea.id) < std::tie(eb.sortOrder, eb.id);
                });

        for (auto const& [id, c] : _creatures)
            _creaturesByEncounter[c.encounterId].push_back(id);
        for (auto& [_, ids] : _creaturesByEncounter)
            std::sort(ids.begin(), ids.end(),
                [this](uint32 a, uint32 b)
                {
                    Creature const& ca = _creatures.at(a);
                    Creature const& cb = _creatures.at(b);
                    return std::tie(ca.sortOrder, ca.id) < std::tie(cb.sortOrder, cb.id);
                });

        for (std::size_t i = 0; i < _loot.size(); ++i)
            _lootByEncounter[_loot[i].encounterId].push_back(i);
        for (auto& [_, idxs] : _lootByEncounter)
            std::sort(idxs.begin(), idxs.end(),
                [this](std::size_t a, std::size_t b)
                {
                    Loot const& la = _loot[a];
                    Loot const& lb = _loot[b];
                    return std::tie(la.sortOrder, la.id) < std::tie(lb.sortOrder, lb.id);
                });

        // tier -> instance, sorted by tier_instance.sort_order then instance_id
        {
            std::vector<TierInstance> sortedTI = _tierInstances;
            std::sort(sortedTI.begin(), sortedTI.end(),
                [](TierInstance const& a, TierInstance const& b)
                {
                    return std::tie(a.sortOrder, a.instanceId)
                         < std::tie(b.sortOrder, b.instanceId);
                });
            for (auto const& ti : sortedTI)
                _instancesByTier[ti.tierId].push_back(ti.instanceId);
        }

        LOG_INFO("server.loading",
            ">> Encounter Journal loaded: {} tiers, {} instances, "
            "{} encounters, {} creatures, {} sections, {} loot rows.",
            _tiers.size(), _instances.size(), _encounters.size(),
            _creatures.size(), _sections.size(), _loot.size());

        return true;
    }

    std::string Mgr::ResolveExportPath(std::string const& filename) const
    {
        std::string base = sConfigMgr->GetOption<std::string>(
            "EncounterJournal.Export.Path", "");

        std::filesystem::path p;
        if (base.empty())
            p = std::filesystem::current_path();
        else
            p = base;
        p /= filename;
        return p.lexically_normal().string();
    }

    bool Mgr::Export(std::vector<std::string>& writtenPaths, std::string& error)
    {
        std::string jsonName = sConfigMgr->GetOption<std::string>(
            "EncounterJournal.Export.JsonFile", "EncounterJournalData.json");
        std::string luaName  = sConfigMgr->GetOption<std::string>(
            "EncounterJournal.Export.LuaFile",  "EncounterJournalData.lua");
        std::string luaGlobal = sConfigMgr->GetOption<std::string>(
            "EncounterJournal.Export.LuaGlobal", "MoonWellEncounterJournalData");

        if (jsonName.empty() && luaName.empty())
        {
            error = "both EncounterJournal.Export.JsonFile and "
                    "EncounterJournal.Export.LuaFile are empty";
            return false;
        }

        if (!jsonName.empty())
        {
            std::string path = ResolveExportPath(jsonName);
            std::ofstream out(path, std::ios::binary | std::ios::trunc);
            if (!out)
            {
                error = "failed to open " + path + " for writing";
                return false;
            }
            out << BuildJson();
            out.close();
            writtenPaths.push_back(std::move(path));
        }

        if (!luaName.empty())
        {
            std::string path = ResolveExportPath(luaName);
            std::ofstream out(path, std::ios::binary | std::ios::trunc);
            if (!out)
            {
                error = "failed to open " + path + " for writing";
                return false;
            }
            out << BuildLua(luaGlobal);
            out.close();
            writtenPaths.push_back(std::move(path));
        }

        return true;
    }

    // -----------------------------------------------------------------------
    // JSON writer
    // -----------------------------------------------------------------------
    std::string Mgr::BuildJson() const
    {
        std::ostringstream o;
        auto const Q = [](std::string const& s) { return "\"" + EscapeJsonString(s) + "\""; };

        o << "{\n";

        // tiers : [[id, name, flags, sort_order], ...]
        o << "  \"tiers\": [";
        for (std::size_t i = 0; i < _tiers.size(); ++i)
        {
            Tier const& t = _tiers[i];
            if (i) o << ", ";
            o << "[" << t.id << ", " << Q(t.name) << ", " << t.flags
              << ", " << t.sortOrder << "]";
        }
        o << "],\n";

        // instances
        o << "  \"instances\": {";
        bool first = true;
        for (uint32 instanceId : _instancesSorted)
        {
            Instance const& i = _instances.at(instanceId);
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << instanceId << "\": ["
              << Q(i.name) << ", "
              << Q(i.description) << ", "
              << Q(i.buttonIcon) << ", "
              << Q(i.smallButtonIcon) << ", "
              << Q(i.background) << ", "
              << Q(i.loreBackground) << ", "
              << i.mapId << ", "
              << i.areaId << ", "
              << i.sortOrder << ", "
              << i.flags << ", "
              << i.id << ", "
              << i.worldMapAreaId
              << "]";
        }
        o << (first ? "" : "\n  ") << "},\n";

        // tierInstances
        o << "  \"tierInstances\": {";
        first = true;
        std::vector<uint32> tierKeys;
        tierKeys.reserve(_instancesByTier.size());
        for (auto const& [tierId, _] : _instancesByTier)
            tierKeys.push_back(tierId);
        std::sort(tierKeys.begin(), tierKeys.end());
        for (uint32 tierId : tierKeys)
        {
            auto const& ids = _instancesByTier.at(tierId);
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << tierId << "\": [";
            for (std::size_t k = 0; k < ids.size(); ++k)
            {
                if (k) o << ", ";
                o << ids[k];
            }
            o << "]";
        }
        o << (first ? "" : "\n  ") << "},\n";

        // encounters: keyed by instance_id
        o << "  \"encounters\": {";
        first = true;
        for (uint32 instanceId : _instancesSorted)
        {
            auto it = _encountersByInstance.find(instanceId);
            if (it == _encountersByInstance.end())
                continue;
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << instanceId << "\": [";
            for (std::size_t k = 0; k < it->second.size(); ++k)
            {
                Encounter const& e = _encounters.at(it->second[k]);
                if (k) o << ", ";
                o << "[" << e.id << ", "
                  << Q(e.name) << ", "
                  << Q(e.description) << ", "
                  << FormatFloat(e.mapPositionX) << ", "
                  << FormatFloat(e.mapPositionY) << ", "
                  << e.floorIndex << ", "
                  << e.worldMapAreaId << ", "
                  << e.firstSectionId << ", "
                  << e.instanceId << ", "
                  << e.difficultyMask << ", "
                  << e.flags << ", "
                  << e.sortOrder << "]";
            }
            o << "]";
        }
        o << (first ? "" : "\n  ") << "},\n";

        // creatures: keyed by encounter_id
        o << "  \"creatures\": {";
        first = true;
        std::vector<uint32> encounterKeys;
        encounterKeys.reserve(_creaturesByEncounter.size());
        for (auto const& [encId, _] : _creaturesByEncounter)
            encounterKeys.push_back(encId);
        std::sort(encounterKeys.begin(), encounterKeys.end());
        for (uint32 encounterId : encounterKeys)
        {
            auto const& ids = _creaturesByEncounter.at(encounterId);
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << encounterId << "\": [";
            for (std::size_t k = 0; k < ids.size(); ++k)
            {
                Creature const& c = _creatures.at(ids[k]);
                if (k) o << ", ";
                o << "[" << Q(c.name) << ", "
                  << Q(c.description) << ", "
                  << c.creatureDisplayId << ", "
                  << Q(c.icon) << ", "
                  << c.encounterId << ", "
                  << c.sortOrder << ", "
                  << c.id << ", "
                  << c.creatureId << ", "
                  << c.difficultyMask << "]";
            }
            o << "]";
        }
        o << (first ? "" : "\n  ") << "},\n";

        // sections: keyed by section_id (single row)
        o << "  \"sections\": {";
        first = true;
        // stable order: by id
        std::vector<uint32> sectionIds;
        sectionIds.reserve(_sections.size());
        for (auto const& [id, _] : _sections)
            sectionIds.push_back(id);
        std::sort(sectionIds.begin(), sectionIds.end());
        for (uint32 sid : sectionIds)
        {
            Section const& s = _sections.at(sid);
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << sid << "\": ["
              << s.id << ", "
              << Q(s.name) << ", "
              << Q(s.description) << ", "
              << s.creatureDisplayId << ", "
              << s.descriptionSpellId << ", "
              << s.iconSpellId << ", "
              << s.encounterId << ", "
              << s.nextSectionId << ", "
              << s.subSectionId << ", "
              << s.parentSectionId << ", "
              << s.flags << ", "
              << s.iconFlags << ", "
              << s.sortOrder << ", "
              << s.type << ", "
              << s.difficultyMask << ", "
              << s.creatureId
              << "]";
        }
        o << (first ? "" : "\n  ") << "},\n";

        // items: keyed by encounter_id
        o << "  \"items\": {";
        first = true;
        std::vector<uint32> lootKeys;
        lootKeys.reserve(_lootByEncounter.size());
        for (auto const& [encId, _] : _lootByEncounter)
            lootKeys.push_back(encId);
        std::sort(lootKeys.begin(), lootKeys.end());
        for (uint32 encounterId : lootKeys)
        {
            auto const& idxs = _lootByEncounter.at(encounterId);
            if (!first) o << ",";
            first = false;
            o << "\n    \"" << encounterId << "\": [";
            for (std::size_t k = 0; k < idxs.size(); ++k)
            {
                Loot const& l = _loot[idxs[k]];
                if (k) o << ", ";
                o << "[" << l.itemId << ", "
                  << l.encounterId << ", "
                  << l.difficultyMask << ", "
                  << l.factionMask << ", "
                  << l.flags << ", "
                  << l.id << ", "
                  << l.classMask << "]";
            }
            o << "]";
        }
        o << (first ? "" : "\n  ") << "}\n";

        o << "}\n";
        return o.str();
    }

    // -----------------------------------------------------------------------
    // Lua writer (mirrors the JSON layout 1:1; addons can index the same way)
    // -----------------------------------------------------------------------
    std::string Mgr::BuildLua(std::string const& globalName) const
    {
        std::ostringstream o;
        auto const Q = [](std::string const& s) { return "'" + EscapeLuaString(s) + "'"; };

        o << "-- Generated by mod-encounter-journal. Do not edit by hand.\n";
        o << "-- Source of truth: custom_ej_* tables in acore_world.\n\n";
        o << globalName << " = {\n";

        // tiers
        o << "    tiers = {\n";
        for (Tier const& t : _tiers)
        {
            o << "        { " << t.id << ", " << Q(t.name) << ", "
              << t.flags << ", " << t.sortOrder << " },\n";
        }
        o << "    },\n";

        // instances
        o << "    instances = {\n";
        for (uint32 id : _instancesSorted)
        {
            Instance const& i = _instances.at(id);
            o << "        [" << id << "] = { "
              << Q(i.name) << ", "
              << Q(i.description) << ", "
              << Q(i.buttonIcon) << ", "
              << Q(i.smallButtonIcon) << ", "
              << Q(i.background) << ", "
              << Q(i.loreBackground) << ", "
              << i.mapId << ", "
              << i.areaId << ", "
              << i.sortOrder << ", "
              << i.flags << ", "
              << i.id << ", "
              << i.worldMapAreaId
              << " },\n";
        }
        o << "    },\n";

        // tierInstances
        o << "    tierInstances = {\n";
        std::vector<uint32> tierKeys;
        tierKeys.reserve(_instancesByTier.size());
        for (auto const& [tierId, _] : _instancesByTier)
            tierKeys.push_back(tierId);
        std::sort(tierKeys.begin(), tierKeys.end());
        for (uint32 tierId : tierKeys)
        {
            auto const& ids = _instancesByTier.at(tierId);
            o << "        [" << tierId << "] = { ";
            for (std::size_t k = 0; k < ids.size(); ++k)
            {
                if (k) o << ", ";
                o << ids[k];
            }
            o << " },\n";
        }
        o << "    },\n";

        // encounters
        o << "    encounters = {\n";
        for (uint32 instanceId : _instancesSorted)
        {
            auto it = _encountersByInstance.find(instanceId);
            if (it == _encountersByInstance.end())
                continue;
            o << "        [" << instanceId << "] = {\n";
            for (uint32 encId : it->second)
            {
                Encounter const& e = _encounters.at(encId);
                o << "            { " << e.id << ", "
                  << Q(e.name) << ", "
                  << Q(e.description) << ", "
                  << FormatFloat(e.mapPositionX) << ", "
                  << FormatFloat(e.mapPositionY) << ", "
                  << e.floorIndex << ", "
                  << e.worldMapAreaId << ", "
                  << e.firstSectionId << ", "
                  << e.instanceId << ", "
                  << e.difficultyMask << ", "
                  << e.flags << ", "
                  << e.sortOrder << " },\n";
            }
            o << "        },\n";
        }
        o << "    },\n";

        // creatures
        o << "    creatures = {\n";
        std::vector<uint32> creatureEncIds;
        creatureEncIds.reserve(_creaturesByEncounter.size());
        for (auto const& [encId, _] : _creaturesByEncounter)
            creatureEncIds.push_back(encId);
        std::sort(creatureEncIds.begin(), creatureEncIds.end());
        for (uint32 encounterId : creatureEncIds)
        {
            auto const& ids = _creaturesByEncounter.at(encounterId);
            o << "        [" << encounterId << "] = {\n";
            for (uint32 cid : ids)
            {
                Creature const& c = _creatures.at(cid);
                o << "            { " << Q(c.name) << ", "
                  << Q(c.description) << ", "
                  << c.creatureDisplayId << ", "
                  << Q(c.icon) << ", "
                  << c.encounterId << ", "
                  << c.sortOrder << ", "
                  << c.id << ", "
                  << c.creatureId << ", "
                  << c.difficultyMask << " },\n";
            }
            o << "        },\n";
        }
        o << "    },\n";

        // sections
        o << "    sections = {\n";
        std::vector<uint32> sectionIds;
        sectionIds.reserve(_sections.size());
        for (auto const& [id, _] : _sections)
            sectionIds.push_back(id);
        std::sort(sectionIds.begin(), sectionIds.end());
        for (uint32 sid : sectionIds)
        {
            Section const& s = _sections.at(sid);
            o << "        [" << sid << "] = { "
              << s.id << ", "
              << Q(s.name) << ", "
              << Q(s.description) << ", "
              << s.creatureDisplayId << ", "
              << s.descriptionSpellId << ", "
              << s.iconSpellId << ", "
              << s.encounterId << ", "
              << s.nextSectionId << ", "
              << s.subSectionId << ", "
              << s.parentSectionId << ", "
              << s.flags << ", "
              << s.iconFlags << ", "
              << s.sortOrder << ", "
              << s.type << ", "
              << s.difficultyMask << ", "
              << s.creatureId
              << " },\n";
        }
        o << "    },\n";

        // items
        o << "    items = {\n";
        std::vector<uint32> lootEncIds;
        lootEncIds.reserve(_lootByEncounter.size());
        for (auto const& [encId, _] : _lootByEncounter)
            lootEncIds.push_back(encId);
        std::sort(lootEncIds.begin(), lootEncIds.end());
        for (uint32 encounterId : lootEncIds)
        {
            auto const& idxs = _lootByEncounter.at(encounterId);
            o << "        [" << encounterId << "] = {\n";
            for (std::size_t idx : idxs)
            {
                Loot const& l = _loot[idx];
                o << "            { " << l.itemId << ", "
                  << l.encounterId << ", "
                  << l.difficultyMask << ", "
                  << l.factionMask << ", "
                  << l.flags << ", "
                  << l.id << ", "
                  << l.classMask << " },\n";
            }
            o << "        },\n";
        }
        o << "    },\n";

        o << "}\n";
        return o.str();
    }

    // -----------------------------------------------------------------------
    // Validator
    // -----------------------------------------------------------------------
    std::size_t Mgr::Validate(std::vector<std::string>& errors)
    {
        std::size_t const startSize = errors.size();
        auto err = [&errors](std::string msg) { errors.push_back(std::move(msg)); };

        // build helper sets
        std::unordered_set<uint32> tierIds;
        for (Tier const& t : _tiers) tierIds.insert(t.id);

        std::unordered_set<uint32> instanceIds;
        for (auto const& [id, _] : _instances) instanceIds.insert(id);

        std::unordered_set<uint32> encounterIds;
        for (auto const& [id, _] : _encounters) encounterIds.insert(id);

        std::unordered_set<uint32> sectionIds;
        for (auto const& [id, _] : _sections) sectionIds.insert(id);

        // tier_instance references
        for (TierInstance const& ti : _tierInstances)
        {
            if (!tierIds.count(ti.tierId))
                err("custom_ej_tier_instance: tier_id=" + std::to_string(ti.tierId)
                    + " does not exist (instance_id=" + std::to_string(ti.instanceId) + ")");
            if (!instanceIds.count(ti.instanceId))
                err("custom_ej_tier_instance: instance_id=" + std::to_string(ti.instanceId)
                    + " does not exist (tier_id=" + std::to_string(ti.tierId) + ")");
        }

        // encounter -> instance + difficulty mask vs instance type
        constexpr uint32 INSTANCE_FLAG_RAID       = 0x10;
        constexpr uint32 RAID_DIFFICULTY_BITS     = 0xF;  // 10n|25n|10h|25h
        constexpr uint32 DUNGEON_DIFFICULTY_BITS  = 0x7;  // normal|heroic|mythic

        for (auto const& [id, e] : _encounters)
        {
            auto instIt = _instances.find(e.instanceId);
            if (instIt == _instances.end())
            {
                err("custom_ej_encounter id=" + std::to_string(e.id)
                    + ": instance_id=" + std::to_string(e.instanceId)
                    + " does not exist");
                continue;
            }
            if (e.firstSectionId != 0 && !sectionIds.count(e.firstSectionId))
                err("custom_ej_encounter id=" + std::to_string(e.id)
                    + ": first_section_id=" + std::to_string(e.firstSectionId)
                    + " does not exist");

            bool isRaid = (instIt->second.flags & INSTANCE_FLAG_RAID) != 0;
            uint32 allowed = isRaid ? RAID_DIFFICULTY_BITS : DUNGEON_DIFFICULTY_BITS;
            if (e.difficultyMask != 0 && (e.difficultyMask & ~allowed) != 0)
            {
                char hex[16];
                std::snprintf(hex, sizeof(hex), "%X", e.difficultyMask);
                err("custom_ej_encounter id=" + std::to_string(e.id)
                    + ": difficulty_mask=0x" + hex
                    + " has bits outside the "
                    + (isRaid ? "raid (0xF)" : "dungeon (0x7)") + " range");
            }
        }

        // creature.encounter_id + creature.creature_id
        for (auto const& [id, c] : _creatures)
        {
            if (!encounterIds.count(c.encounterId))
                err("custom_ej_creature id=" + std::to_string(c.id)
                    + ": encounter_id=" + std::to_string(c.encounterId)
                    + " does not exist");
            if (c.creatureId != 0 && !sObjectMgr->GetCreatureTemplate(c.creatureId))
                err("custom_ej_creature id=" + std::to_string(c.id)
                    + ": creature_id=" + std::to_string(c.creatureId)
                    + " does not exist in creature_template");
        }

        // section.encounter_id + parent/next/sub references
        for (auto const& [id, s] : _sections)
        {
            if (!encounterIds.count(s.encounterId))
                err("custom_ej_section id=" + std::to_string(s.id)
                    + ": encounter_id=" + std::to_string(s.encounterId)
                    + " does not exist");
            for (auto const& [field, value] : std::initializer_list<std::pair<char const*, uint32>>{
                     {"parent_section_id", s.parentSectionId},
                     {"next_section_id",   s.nextSectionId},
                     {"sub_section_id",    s.subSectionId}})
            {
                if (value != 0 && !sectionIds.count(value))
                    err(std::string("custom_ej_section id=") + std::to_string(s.id)
                        + ": " + field + "=" + std::to_string(value)
                        + " does not exist");
            }
            if (s.creatureId != 0 && !sObjectMgr->GetCreatureTemplate(s.creatureId))
                err("custom_ej_section id=" + std::to_string(s.id)
                    + ": creature_id=" + std::to_string(s.creatureId)
                    + " does not exist in creature_template");
        }

        // loot.encounter_id + loot.item_id + duplicate detection
        std::set<std::tuple<uint32, uint32, uint32, int32, int32>> seen;
        for (Loot const& l : _loot)
        {
            if (!encounterIds.count(l.encounterId))
                err("custom_ej_loot id=" + std::to_string(l.id)
                    + ": encounter_id=" + std::to_string(l.encounterId)
                    + " does not exist");
            if (!sObjectMgr->GetItemTemplate(l.itemId))
                err("custom_ej_loot id=" + std::to_string(l.id)
                    + ": item_id=" + std::to_string(l.itemId)
                    + " does not exist in item_template");

            auto key = std::make_tuple(
                l.encounterId, l.itemId, l.difficultyMask,
                l.factionMask, l.classMask);
            if (!seen.insert(key).second)
                err("custom_ej_loot id=" + std::to_string(l.id)
                    + ": duplicate (encounter_id, item_id, difficulty_mask, "
                      "faction_mask, class_mask)");
        }

        return errors.size() - startSize;
    }

    // -----------------------------------------------------------------------
    // Loot importer (creature_template.lootid -> creature_loot_template,
    // following one level of reference_loot_template).
    // -----------------------------------------------------------------------
    std::size_t Mgr::ImportLoot(std::vector<std::string>& warnings)
    {
        std::size_t inserted = 0;

        // Group: encounter_id -> (set of unique creature template lootids,
        //                         first creature card's difficulty mask)
        struct Bucket { uint32 lootId; uint32 difficultyMask; };
        std::vector<std::pair<uint32, Bucket>> work;

        for (auto const& [id, c] : _creatures)
        {
            if (c.creatureId == 0)
                continue;
            CreatureTemplate const* ct = sObjectMgr->GetCreatureTemplate(c.creatureId);
            if (!ct)
            {
                warnings.push_back("creature_id=" + std::to_string(c.creatureId)
                    + " (custom_ej_creature.id=" + std::to_string(c.id)
                    + ") missing in creature_template");
                continue;
            }
            if (ct->lootid == 0)
                continue;

            work.push_back({ c.encounterId, { ct->lootid, c.difficultyMask } });
        }

        // Already-present loot rows so we can skip without round-tripping
        // (the unique key would catch it anyway, but this keeps the count clean).
        std::set<std::tuple<uint32, uint32, uint32>> already;
        for (Loot const& l : _loot)
            already.emplace(l.encounterId, l.itemId, l.difficultyMask);

        for (auto const& [encounterId, b] : work)
        {
            // Pull primary loot rows (Reference < 0 represents a reference id when
            // mode + chance use the reference convention; AzerothCore's actual
            // creature_loot_template stores Reference > 0 to point at
            // reference_loot_template.Entry, with Item == 0).
            QueryResult res = WorldDatabase.Query(
                "SELECT `Item`, `Reference` FROM `creature_loot_template` "
                "WHERE `Entry` = {}", b.lootId);
            if (!res)
                continue;

            std::set<uint32> items;
            std::set<uint32> refs;
            do
            {
                Field* f = res->Fetch();
                uint32 item = f[0].Get<uint32>();
                uint32 ref  = f[1].Get<uint32>();
                if (item != 0)
                    items.insert(item);
                if (ref != 0)
                    refs.insert(ref);
            } while (res->NextRow());

            for (uint32 ref : refs)
            {
                QueryResult r2 = WorldDatabase.Query(
                    "SELECT `Item` FROM `reference_loot_template` "
                    "WHERE `Entry` = {} AND `Item` <> 0", ref);
                if (!r2) continue;
                do
                {
                    items.insert(r2->Fetch()[0].Get<uint32>());
                } while (r2->NextRow());
            }

            int32 sortOrder = 0;
            for (uint32 itemId : items)
            {
                if (already.count({ encounterId, itemId, b.difficultyMask }))
                    continue;
                if (!sObjectMgr->GetItemTemplate(itemId))
                {
                    warnings.push_back("skipping unknown item_id=" + std::to_string(itemId)
                        + " (encounter_id=" + std::to_string(encounterId) + ")");
                    continue;
                }

                WorldDatabase.DirectExecute(
                    "INSERT IGNORE INTO `custom_ej_loot` "
                    "(`encounter_id`, `item_id`, `difficulty_mask`, "
                    " `faction_mask`, `class_mask`, `flags`, `sort_order`) "
                    "VALUES ({}, {}, {}, -1, -1, 0, {})",
                    encounterId, itemId, b.difficultyMask, sortOrder);
                already.emplace(encounterId, itemId, b.difficultyMask);
                sortOrder += 10;
                ++inserted;
            }
        }

        return inserted;
    }

    // -----------------------------------------------------------------------
    // Skeleton importer: builds instance/encounter/creature/section rows from
    // standard AzerothCore tables (instance_template, instance_encounters,
    // creature_template) and the loaded Map.dbc store. Descriptions stay
    // empty — they are not derivable from server-side data on WotLK.
    //
    // ID assignment (chosen to never collide with the sample data range
    // 9000-9999 and to be re-runnable via INSERT IGNORE):
    //   tier        : 100 + expansionID                         (100/101/102)
    //   instance    : map_id                                    (1..max ~700)
    //   encounter   : 100000 + instance_encounters.entry        (1..2000ish)
    //   creature    : 100000 + instance_encounters.entry        (1:1 to enc)
    //   section     : (encounter_id * 10) + 1                   (Overview)
    // -----------------------------------------------------------------------
    std::size_t Mgr::ImportSkeleton(SkeletonCounts& counts,
                                    std::vector<std::string>& warnings)
    {
        constexpr uint32 TIER_BASE       = 100;
        constexpr uint32 ENCOUNTER_BASE  = 100000;
        constexpr uint32 INSTANCE_FLAG_RAID = 0x10;

        auto bestLocaleString = [](char const* const* names) -> std::string
        {
            // Prefer ruRU, fall back to enUS, then anything non-empty.
            if (names[LOCALE_ruRU] && *names[LOCALE_ruRU])
                return names[LOCALE_ruRU];
            if (names[LOCALE_enUS] && *names[LOCALE_enUS])
                return names[LOCALE_enUS];
            for (int i = 0; i < TOTAL_LOCALES; ++i)
                if (names[i] && *names[i])
                    return names[i];
            return std::string();
        };

        // Helper that injects a single row via DirectExecute and returns
        // whether the insert produced a new row (rows-affected > 0). We use
        // the pool's DirectExecute for synchronous semantics so the counter
        // stays accurate; the unique key / PK guarantees idempotence.
        auto execIgnore = [](std::string const& sql) -> bool
        {
            // INSERT IGNORE silently drops dup-key conflicts; we trust it.
            WorldDatabase.DirectExecute(sql);
            return true;
        };

        // ---- 1. Collect map -> set<encounter creditEntry> from spawn data --
        // We need to know which map each instance_encounters row belongs to.
        // The DBC has no direct map column, so we look up where the credit
        // creature spawns and pick the most common map id.
        std::unordered_map<uint32, std::vector<uint32>> creaturesByMap;
        if (QueryResult q = WorldDatabase.Query(
            "SELECT DISTINCT id1, map FROM creature WHERE id1 <> 0 "
            "UNION SELECT DISTINCT id2, map FROM creature WHERE id2 <> 0 "
            "UNION SELECT DISTINCT id3, map FROM creature WHERE id3 <> 0"))
        {
            do
            {
                Field* f = q->Fetch();
                creaturesByMap[f[1].Get<uint32>()].push_back(f[0].Get<uint32>());
            } while (q->NextRow());
        }
        std::unordered_map<uint32, uint32> creatureToMap;
        for (auto const& [mapId, creatures] : creaturesByMap)
            for (uint32 entry : creatures)
                if (!creatureToMap.count(entry))
                    creatureToMap[entry] = mapId;

        // ---- 2. tiers (one per expansion that has at least one instance) ---
        std::set<uint32> expansionsUsed;
        std::map<uint32, MapEntry const*> instanceMaps; // map_id -> MapEntry
        if (QueryResult q = WorldDatabase.Query(
            "SELECT DISTINCT map FROM instance_template"))
        {
            do
            {
                uint32 mapId = q->Fetch()[0].Get<uint32>();
                MapEntry const* me = sMapStore.LookupEntry(mapId);
                if (!me)
                {
                    warnings.push_back("instance_template.map=" +
                        std::to_string(mapId) + " missing in Map.dbc; skipped");
                    continue;
                }
                if (!me->IsDungeon() && !me->IsRaid())
                    continue;
                instanceMaps[mapId] = me;
                expansionsUsed.insert(me->expansionID);
            } while (q->NextRow());
        }

        char const* expansionName[] =
            { "Classic", "The Burning Crusade", "Wrath of the Lich King" };
        for (uint32 exp : expansionsUsed)
        {
            uint32 tierId = TIER_BASE + exp;
            char const* name = (exp < std::size(expansionName))
                ? expansionName[exp] : "Unknown";
            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_tier` "
                "(`id`, `name`, `flags`, `sort_order`) VALUES "
                "({}, '{}', 0, {})",
                tierId, name, exp * 10);
            ++counts.tiers;
        }

        // ---- 3. instances ----
        for (auto const& [mapId, me] : instanceMaps)
        {
            std::string name = bestLocaleString(me->name);
            if (name.empty())
                name = "Map " + std::to_string(mapId);

            // SQL escape: replace single quotes (rare in map names but safe).
            std::string esc;
            esc.reserve(name.size());
            for (char c : name) { if (c == '\'') esc += "''"; else esc += c; }

            uint32 flags = me->IsRaid() ? INSTANCE_FLAG_RAID : 0u;

            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_instance` "
                "(`id`, `name`, `description`, `button_icon`, `small_button_icon`, "
                " `background`, `lore_background`, `map_id`, `area_id`, "
                " `world_map_area_id`, `flags`, `sort_order`) "
                "VALUES ({}, '{}', '', '', '', '', '', {}, 0, 0, {}, {})",
                mapId, esc, mapId, flags, mapId);
            ++counts.instances;

            uint32 tierId = TIER_BASE + me->expansionID;
            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_tier_instance` "
                "(`tier_id`, `instance_id`, `sort_order`) VALUES ({}, {}, {})",
                tierId, mapId, mapId);
            ++counts.tierInstances;
        }

        // ---- 4. encounters + creatures + sections from instance_encounters
        QueryResult ienc = WorldDatabase.Query(
            "SELECT `entry`, `creditType`, `creditEntry`, `comment` "
            "FROM `instance_encounters` "
            "WHERE `creditType` = 0 ORDER BY `entry`");
        if (!ienc)
        {
            warnings.push_back("instance_encounters table empty or missing");
            return counts.tiers + counts.instances + counts.tierInstances;
        }

        int32 sortPerInstance = 10;
        uint32 lastInstanceId = 0;
        do
        {
            Field* f = ienc->Fetch();
            uint32 entry      = f[0].Get<uint32>();
            uint32 creature   = f[2].Get<uint32>();
            std::string cmt   = f[3].Get<std::string>();

            auto mIt = creatureToMap.find(creature);
            if (mIt == creatureToMap.end())
            {
                warnings.push_back("instance_encounters.entry=" +
                    std::to_string(entry) + " creature=" +
                    std::to_string(creature) +
                    " has no spawn — cannot infer map; skipped");
                continue;
            }
            uint32 mapId = mIt->second;
            auto mapIt = instanceMaps.find(mapId);
            if (mapIt == instanceMaps.end())
                continue; // creature spawns outside known instance

            MapEntry const* me = mapIt->second;
            uint32 difficultyMask = me->IsRaid() ? 0xFu : 0x3u;

            CreatureTemplate const* ct = sObjectMgr->GetCreatureTemplate(creature);
            std::string crName = (ct && !ct->Name.empty()) ? ct->Name : cmt;
            uint32 displayId = 0;
            if (ct && !ct->Models.empty())
                displayId = ct->Models[0].CreatureDisplayID;

            std::string crEsc;
            crEsc.reserve(crName.size());
            for (char c : crName) { if (c == '\'') crEsc += "''"; else crEsc += c; }
            std::string cmtEsc;
            cmtEsc.reserve(cmt.size());
            for (char c : cmt) { if (c == '\'') cmtEsc += "''"; else cmtEsc += c; }

            uint32 encounterId = ENCOUNTER_BASE + entry;
            uint32 firstSection = encounterId * 10 + 1;

            // reset sort within an instance whenever map changes
            if (mapId != lastInstanceId)
            {
                lastInstanceId = mapId;
                sortPerInstance = 10;
            }

            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_encounter` "
                "(`id`, `instance_id`, `name`, `description`, "
                " `map_position_x`, `map_position_y`, `floor_index`, "
                " `world_map_area_id`, `first_section_id`, `difficulty_mask`, "
                " `flags`, `sort_order`) "
                "VALUES ({}, {}, '{}', '', 0, 0, 1, 0, {}, {}, 0, {})",
                encounterId, mapId, cmtEsc, firstSection,
                difficultyMask, sortPerInstance);
            ++counts.encounters;

            // 1:1 creature card
            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_creature` "
                "(`id`, `encounter_id`, `creature_id`, `name`, `description`, "
                " `creature_display_id`, `icon`, `difficulty_mask`, `sort_order`) "
                "VALUES ({}, {}, {}, '{}', '', {}, '', {}, 10)",
                encounterId, encounterId, creature, crEsc, displayId,
                difficultyMask);
            ++counts.creatures;

            // Overview section placeholder
            WorldDatabase.DirectExecute(
                "INSERT IGNORE INTO `custom_ej_section` "
                "(`id`, `encounter_id`, `parent_section_id`, `next_section_id`, "
                " `sub_section_id`, `name`, `description`, `creature_display_id`, "
                " `description_spell_id`, `icon_spell_id`, `icon`, `flags`, "
                " `icon_flags`, `sort_order`, `type`, `difficulty_mask`, "
                " `creature_id`) "
                "VALUES ({}, {}, 0, 0, 0, 'Overview', '', 0, 0, 0, NULL, 1, "
                "0, 10, 0, {}, {})",
                firstSection, encounterId, difficultyMask, creature);
            ++counts.sections;

            sortPerInstance += 10;
        } while (ienc->NextRow());

        return counts.tiers + counts.instances + counts.tierInstances
             + counts.encounters + counts.creatures + counts.sections;
    }
}
