Implements saving of loot (items / money) contained inside lootable inventory items.

* Unlooted items / money persist across player sessions
* Loot inside items is tied to the item rather than the player so if trading
  partially looted items becomes possible, this implementation will still work

* New tables added:

    characters_database.sql (first time users)
    characters_create_item_loot.sql (existing users)

Implementation Can be tested with:

 Watertight Trunk [21113]
 Bulging Sack of Gems [25422]
 Fat Sack of Coins [11937]

Closes #2048

3 files changed, 225 insertions, 1 deletions

diff --git a/src/server/game/Entities/Item/Item.cpp b/src/server/game/Entities/Item/Item.cpp
index bdaf11ad9b8..5303fb8dc38 100644
--- a/src/server/game/Entities/Item/Item.cpp
+++ b/src/server/game/Entities/Item/Item.cpp
@@ -378,6 +378,10 @@ void Item::SaveToDB(SQLTransaction& trans)
             if (!isInTransaction)
                 CharacterDatabase.CommitTransaction(trans);
 
+            // Delete the items if this is a container
+            if (!loot.isLooted())
+                ItemContainerDeleteLootMoneyAndLootItemsFromDB();
+
             delete this;
             return;
         }
@@ -483,6 +487,10 @@ void Item::DeleteFromDB(SQLTransaction& trans, uint32 itemGuid)
 void Item::DeleteFromDB(SQLTransaction& trans)
 {
     DeleteFromDB(trans, GetGUIDLow());
+
+    // Delete the items if this is a container
+    if (!loot.isLooted())
+        ItemContainerDeleteLootMoneyAndLootItemsFromDB();
 }
 
 /*static*/
@@ -1198,3 +1206,191 @@ bool Item::CheckSoulboundTradeExpire()
 
     return false;
 }
+
+void Item::ItemContainerSaveLootToDB()
+{
+    // Saves the money and item loot associated with an openable item to the DB
+
+    if (loot.isLooted()) // no money and no loot
+        return;
+
+    uint32 container_id = GetGUIDLow();
+    SQLTransaction trans = CharacterDatabase.BeginTransaction();
+
+    loot.containerID = container_id; // Save this for when a LootItem is removed
+
+    // Save money
+    if (loot.gold > 0)
+    {
+        PreparedStatement* stmt_money = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEMCONTAINER_MONEY);
+        stmt_money->setUInt32(0, container_id);
+        trans->Append(stmt_money);
+
+        stmt_money = CharacterDatabase.GetPreparedStatement(CHAR_INS_ITEMCONTAINER_MONEY);
+        stmt_money->setUInt32(0, container_id);
+        stmt_money->setUInt32(1, loot.gold);
+        trans->Append(stmt_money);
+    }
+
+    // Save items
+    if (!loot.isLooted())
+    {
+        
+        PreparedStatement* stmt_items = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEMCONTAINER_ITEMS);
+        stmt_items->setUInt32(0, container_id);
+        trans->Append(stmt_items);
+
+        // Now insert the items
+        for (LootItemList::const_iterator _li = loot.items.begin(); _li != loot.items.end(); _li++)
+        {
+            // When an item is looted, it doesn't get removed from the items collection
+            //  but we don't want to resave it.
+            if (!_li->canSave)
+                continue;
+
+            stmt_items = CharacterDatabase.GetPreparedStatement(CHAR_INS_ITEMCONTAINER_ITEMS);
+
+            // container_id, item_id, item_count, follow_rules, ffa, blocked, counted, under_threshold, needs_quest, rnd_prop, rnd_suffix
+            stmt_items->setUInt32(0, container_id);
+            stmt_items->setUInt32(1, _li->itemid);
+            stmt_items->setUInt32(2, _li->count);
+            stmt_items->setBool(3, _li->follow_loot_rules);
+            stmt_items->setBool(4, _li->freeforall);
+            stmt_items->setBool(5, _li->is_blocked);
+            stmt_items->setBool(6, _li->is_counted);
+            stmt_items->setBool(7, _li->is_underthreshold);
+            stmt_items->setBool(8, _li->needs_quest);
+            stmt_items->setUInt32(9, _li->randomPropertyId);
+            stmt_items->setUInt32(10, _li->randomSuffix);
+            trans->Append(stmt_items);
+        }
+    }
+
+    CharacterDatabase.CommitTransaction(trans);
+}
+
+bool Item::ItemContainerLoadLootFromDB()
+{
+    // Loads the money and item loot associated with an openable item from the DB
+
+    // Default. If there are no records for this item then it will be rolled for in Player::SendLoot()
+    m_lootGenerated = false;
+
+    uint32 container_id = GetGUIDLow();
+
+    // Save this for later use
+    loot.containerID = container_id;
+
+    // First, see if there was any money loot. This gets added directly to the container.
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SEL_ITEMCONTAINER_MONEY);
+    stmt->setUInt32(0, container_id);
+    PreparedQueryResult money_result = CharacterDatabase.Query(stmt);
+
+    if (money_result)
+    {
+        Field* fields = money_result->Fetch();
+        loot.gold = fields[0].GetUInt32();
+    }
+
+    // Next, load any items that were saved
+    stmt = CharacterDatabase.GetPreparedStatement(CHAR_SEL_ITEMCONTAINER_ITEMS);
+    stmt->setUInt32(0, container_id);
+    PreparedQueryResult item_result = CharacterDatabase.Query(stmt);
+
+    if (item_result)
+    {
+        // Get a LootTemplate for the container item. This is where
+        //  the saved loot was originally rolled from, we will copy conditions from it
+        LootTemplate const* lt = LootTemplates_Item.GetLootFor(GetEntry());
+
+        if (lt)
+        {
+            do
+            {
+                // Create an empty LootItem
+                LootItem loot_item = LootItem();
+
+                // Fill in the rest of the LootItem from the DB
+                Field* fields = item_result->Fetch();
+
+                // item_id, itm_count, follow_rules, ffa, blocked, counted, under_threshold, needs_quest, rnd_prop, rnd_suffix
+                loot_item.itemid = fields[0].GetUInt32();
+                loot_item.count = fields[1].GetUInt32();
+                loot_item.follow_loot_rules = fields[2].GetBool();
+                loot_item.freeforall = fields[3].GetBool();
+                loot_item.is_blocked = fields[4].GetBool();
+                loot_item.is_counted = fields[5].GetBool();
+                loot_item.canSave = true;
+                loot_item.is_underthreshold = fields[6].GetBool();
+                loot_item.needs_quest = fields[7].GetBool();
+                loot_item.randomPropertyId = fields[8].GetUInt32();
+                loot_item.randomSuffix = fields[9].GetUInt32();
+
+                // Copy the extra loot conditions from the item in the loot template
+                lt->CopyConditions(&loot_item);
+
+                // If container item is in a bag, add that player as an allowed looter
+                if (GetBagSlot())
+                    loot_item.allowedGUIDs.insert(GetOwner()->GetGUIDLow());
+
+                // Finally add the LootItem to the container
+                loot.items.push_back(loot_item);
+                
+                // Increment unlooted count
+                loot.unlootedCount++;
+
+            } while (item_result->NextRow());
+        }
+    }
+
+   // Mark the item if it has loot so it won't be generated again on open
+   m_lootGenerated = !loot.isLooted();
+
+   return m_lootGenerated;
+}
+
+void Item::ItemContainerDeleteLootItemsFromDB()
+{
+    // Deletes items associated with an openable item from the DB
+
+    uint32 containerId = GetGUIDLow();
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEMCONTAINER_ITEMS);
+    stmt->setUInt32(0, containerId);
+    SQLTransaction trans = CharacterDatabase.BeginTransaction();
+    trans->Append(stmt);
+    CharacterDatabase.CommitTransaction(trans);
+}
+
+void Item::ItemContainerDeleteLootItemFromDB(uint32 itemID)
+{
+    // Deletes a single item associated with an openable item from the DB
+
+    uint32 containerId = GetGUIDLow();
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEMCONTAINER_ITEM);
+    stmt->setUInt32(0, containerId);
+    stmt->setUInt32(1, itemID);
+    SQLTransaction trans = CharacterDatabase.BeginTransaction();
+    trans->Append(stmt);
+    CharacterDatabase.CommitTransaction(trans);
+}
+
+void Item::ItemContainerDeleteLootMoneyFromDB()
+{
+    // Deletes the money loot associated with an openable item from the DB
+
+    uint32 containerId = GetGUIDLow();
+    PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_ITEMCONTAINER_MONEY);
+    stmt->setUInt32(0, containerId);
+    SQLTransaction trans = CharacterDatabase.BeginTransaction();
+    trans->Append(stmt);
+    CharacterDatabase.CommitTransaction(trans);
+}
+
+void Item::ItemContainerDeleteLootMoneyAndLootItemsFromDB()
+{
+    // Deletes money and items associated with an openable item from the DB
+
+    ItemContainerDeleteLootMoneyFromDB();
+    ItemContainerDeleteLootItemsFromDB();
+}
+
diff --git a/src/server/game/Entities/Item/Item.h b/src/server/game/Entities/Item/Item.h
index a5f9ed5c008..2e1956250f3 100644
--- a/src/server/game/Entities/Item/Item.h
+++ b/src/server/game/Entities/Item/Item.h
@@ -231,6 +231,15 @@ class Item : public Object
         static void DeleteFromDB(SQLTransaction& trans, uint32 itemGuid);
         virtual void DeleteFromDB(SQLTransaction& trans);
         static void DeleteFromInventoryDB(SQLTransaction& trans, uint32 itemGuid);
+        
+        // Lootable items and their contents
+        void ItemContainerSaveLootToDB();
+        bool ItemContainerLoadLootFromDB();
+        void ItemContainerDeleteLootItemsFromDB();
+        void ItemContainerDeleteLootItemFromDB(uint32 itemID);
+        void ItemContainerDeleteLootMoneyFromDB();
+        void ItemContainerDeleteLootMoneyAndLootItemsFromDB();
+
         void DeleteFromInventoryDB(SQLTransaction& trans);
         void SaveRefundDataToDB();
         void DeleteRefundDataFromDB(SQLTransaction* trans);
diff --git a/src/server/game/Entities/Player/Player.cpp b/src/server/game/Entities/Player/Player.cpp
index cf573ec8c5a..34d132f6dce 100644
--- a/src/server/game/Entities/Player/Player.cpp
+++ b/src/server/game/Entities/Player/Player.cpp
@@ -8903,7 +8903,9 @@ void Player::SendLoot(uint64 guid, LootType loot_type)
 
         loot = &item->loot;
 
-        if (!item->m_lootGenerated)
+        // If item doesn't already have loot, attempt to load it. If that
+        //  fails then this is first time opening, generate loot
+        if (!item->m_lootGenerated && !item->ItemContainerLoadLootFromDB())
         {
             item->m_lootGenerated = true;
             loot->clear();
@@ -8922,6 +8924,12 @@ void Player::SendLoot(uint64 guid, LootType loot_type)
                 default:
                     loot->generateMoneyLoot(item->GetTemplate()->MinMoneyLoot, item->GetTemplate()->MaxMoneyLoot);
                     loot->FillLoot(item->GetEntry(), LootTemplates_Item, this, true, loot->gold != 0);
+
+                    // Force save the loot and money items that were just rolled
+                    //  Also saves the container item ID in Loot struct (not to DB)
+                    if (loot->gold > 0 || loot->unlootedCount > 0)
+                        item->ItemContainerSaveLootToDB();
+
                     break;
             }
         }
@@ -12675,6 +12683,12 @@ void Player::DestroyItem(uint8 bag, uint8 slot, bool update)
         else if (Bag* pBag = GetBagByPos(bag))
             pBag->RemoveItem(slot, update);
 
+        // Delete rolled money / loot from db.
+        // MUST be done before RemoveFromWorld() or GetTemplate() fails
+        if (ItemTemplate const* pTmp = pItem->GetTemplate())
+            if (pTmp->Flags & ITEM_PROTO_FLAG_OPENABLE)
+                pItem->ItemContainerDeleteLootMoneyAndLootItemsFromDB();
+
         if (IsInWorld() && update)
         {
             pItem->RemoveFromWorld();
@@ -24055,6 +24069,11 @@ void Player::StoreLootItem(uint8 lootSlot, Loot* loot)
         UpdateAchievementCriteria(ACHIEVEMENT_CRITERIA_TYPE_LOOT_ITEM, item->itemid, item->count);
         UpdateAchievementCriteria(ACHIEVEMENT_CRITERIA_TYPE_LOOT_TYPE, loot->loot_type, item->count);
         UpdateAchievementCriteria(ACHIEVEMENT_CRITERIA_TYPE_LOOT_EPIC_ITEM, item->itemid, item->count);
+
+        // LootItem is being removed (looted) from the container, delete it from the DB.
+        if (loot->containerID > 0)
+            loot->DeleteLootItemFromContainerItemDB(item->itemid);
+
     }
     else
         SendEquipError(msg, NULL, NULL, item->itemid);