Core/Items: Implement azerite essences

* Implement inspecting heart of azeroth data
* Fixed heart of azeroth item level - bonuses now apply

3 files changed, 200 insertions, 2 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index 6058fffde31..098ea47c894 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -2867,8 +2867,24 @@ CREATE TABLE `item_instance_azerite` (
   `xp` bigint(20) unsigned NOT NULL DEFAULT '0',
   `level` int(10) unsigned NOT NULL DEFAULT '1',
   `knowledgeLevel` int(10) unsigned NOT NULL DEFAULT '0',
+  `selectedAzeriteEssences1specId` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences1azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences1azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences1azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences2specId` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences2azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences2azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences2azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences3specId` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences3azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences3azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences3azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences4specId` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences4azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences4azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  `selectedAzeriteEssences4azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
   PRIMARY KEY (`itemGuid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -2881,6 +2897,53 @@ LOCK TABLES `item_instance_azerite` WRITE;
 UNLOCK TABLES;
 
 --
+-- Table structure for table `item_instance_azerite_milestone_power`
+--
+
+DROP TABLE IF EXISTS `item_instance_azerite_milestone_power`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `item_instance_azerite_milestone_power` (
+  `itemGuid` bigint(20) unsigned NOT NULL,
+  `azeriteItemMilestonePowerId` int(10) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`itemGuid`,`azeriteItemMilestonePowerId`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `item_instance_azerite_milestone_power`
+--
+
+LOCK TABLES `item_instance_azerite_milestone_power` WRITE;
+/*!40000 ALTER TABLE `item_instance_azerite_milestone_power` DISABLE KEYS */;
+/*!40000 ALTER TABLE `item_instance_azerite_milestone_power` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `item_instance_azerite`
+--
+
+DROP TABLE IF EXISTS `item_instance_azerite_unlocked_essence`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `item_instance_azerite_unlocked_essence` (
+  `itemGuid` bigint(20) unsigned NOT NULL,
+  `azeriteEssenceId` int(10) unsigned NOT NULL DEFAULT '0',
+  `rank` int(10) unsigned NOT NULL DEFAULT '0',
+  PRIMARY KEY (`itemGuid`,`azeriteEssenceId`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `item_instance_azerite_unlocked_essence`
+--
+
+LOCK TABLES `item_instance_azerite_unlocked_essence` WRITE;
+/*!40000 ALTER TABLE `item_instance_azerite_unlocked_essence` DISABLE KEYS */;
+/*!40000 ALTER TABLE `item_instance_azerite_unlocked_essence` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
 -- Table structure for table `item_instance_gems`
 --
 
@@ -3596,7 +3659,8 @@ INSERT INTO `updates` VALUES
 ('2019_10_26_00_characters.sql','F1090ACDEB876A7BB5ED8829373F6305A217949A','RELEASED','2019-10-25 23:04:42',0),
 ('2019_10_26_01_characters.sql','59D5860930D02AB77D2AAA704C564957A9143760','RELEASED','2019-10-26 22:04:46',0),
 ('2019_11_03_00_characters.sql','DC789597F85B890E9A7901B4443DAD9CAEE2A02A','RELEASED','2019-11-03 14:13:27',0),
-('2019_11_12_00_characters.sql','D4C642B4D48DAE9F56329BDE51C258323A132A91','RELEASED','2019-11-12 16:31:29',0);
+('2019_11_12_00_characters.sql','D4C642B4D48DAE9F56329BDE51C258323A132A91','RELEASED','2019-11-12 16:31:29',0),
+('2019_11_22_00_characters.sql','95DFA71DBD75542C098CD86E9C0051C9690902F0','RELEASED','2019-11-20 15:10:12',0);
 /*!40000 ALTER TABLE `updates` ENABLE KEYS */;
 UNLOCK TABLES;
 
diff --git a/sql/updates/characters/master/2019_11_22_00_characters.sql b/sql/updates/characters/master/2019_11_22_00_characters.sql
new file mode 100644
index 00000000000..97671dfbbde
--- /dev/null
+++ b/sql/updates/characters/master/2019_11_22_00_characters.sql
@@ -0,0 +1,38 @@
+ALTER TABLE `item_instance_azerite` 
+  ADD `selectedAzeriteEssences1specId` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences1azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences1azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences1azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences2specId` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences2azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences2azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences2azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences3specId` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences3azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences3azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences3azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences4specId` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences4azeriteEssenceId1` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences4azeriteEssenceId2` int(10) unsigned NULL DEFAULT '0',
+  ADD `selectedAzeriteEssences4azeriteEssenceId3` int(10) unsigned NULL DEFAULT '0';
+
+--
+-- Table structure for table `item_instance_azerite_milestone_power`
+--
+DROP TABLE IF EXISTS `item_instance_azerite_milestone_power`;
+CREATE TABLE `item_instance_azerite_milestone_power` (
+  `itemGuid` bigint(20) unsigned NOT NULL,
+  `azeriteItemMilestonePowerId` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`itemGuid`,`azeriteItemMilestonePowerId`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `item_instance_azerite_unlocked_essence`
+--
+DROP TABLE IF EXISTS `item_instance_azerite_unlocked_essence`;
+CREATE TABLE `item_instance_azerite_unlocked_essence` (
+  `itemGuid` bigint(20) unsigned NOT NULL,
+  `azeriteEssenceId` int(10) unsigned NOT NULL,
+  `rank` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`itemGuid`,`azeriteEssenceId`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/sql/updates/hotfixes/master/2019_11_22_00_hotfixes.sql b/sql/updates/hotfixes/master/2019_11_22_00_hotfixes.sql
new file mode 100644
index 00000000000..5951dec9aa2
--- /dev/null
+++ b/sql/updates/hotfixes/master/2019_11_22_00_hotfixes.sql
@@ -0,0 +1,96 @@
+--
+-- Table structure for table `azerite_essence`
+--
+DROP TABLE IF EXISTS `azerite_essence`;
+CREATE TABLE `azerite_essence` (
+  `Name` text,
+  `Description` text,
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `SpecSetID` int(11) NOT NULL DEFAULT '0',
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `azerite_essence_locale`
+--
+DROP TABLE IF EXISTS `azerite_essence_locale`;
+CREATE TABLE `azerite_essence_locale` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `locale` varchar(4) NOT NULL,
+  `Name_lang` text,
+  `Description_lang` text,
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`,`locale`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `azerite_essence_power`
+--
+DROP TABLE IF EXISTS `azerite_essence_power`;
+CREATE TABLE `azerite_essence_power` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `SourceAlliance` text,
+  `SourceHorde` text,
+  `AzeriteEssenceID` int(11) NOT NULL DEFAULT '0',
+  `Tier` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `MajorPowerDescription` int(11) NOT NULL DEFAULT '0',
+  `MinorPowerDescription` int(11) NOT NULL DEFAULT '0',
+  `MajorPowerActual` int(11) NOT NULL DEFAULT '0',
+  `MinorPowerActual` int(11) NOT NULL DEFAULT '0',
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `azerite_essence_power_locale`
+--
+DROP TABLE IF EXISTS `azerite_essence_power_locale`;
+CREATE TABLE `azerite_essence_power_locale` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `locale` varchar(4) NOT NULL,
+  `SourceAlliance_lang` text,
+  `SourceHorde_lang` text,
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`,`locale`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `azerite_item_milestone_power`
+--
+DROP TABLE IF EXISTS `azerite_item_milestone_power`;
+CREATE TABLE `azerite_item_milestone_power` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `RequiredLevel` int(11) NOT NULL DEFAULT '0',
+  `AzeritePowerID` int(11) NOT NULL DEFAULT '0',
+  `Type` int(11) NOT NULL DEFAULT '0',
+  `AutoUnlock` int(11) NOT NULL DEFAULT '0',
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `azerite_power`
+--
+DROP TABLE IF EXISTS `azerite_power`;
+CREATE TABLE `azerite_power` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `SpellID` int(11) NOT NULL DEFAULT '0',
+  `ItemBonusListID` int(11) NOT NULL DEFAULT '0',
+  `SpecSetID` int(11) NOT NULL DEFAULT '0',
+  `Flags` int(11) NOT NULL DEFAULT '0',
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `spec_set_member`
+--
+DROP TABLE IF EXISTS `spec_set_member`;
+CREATE TABLE `spec_set_member` (
+  `ID` int(10) unsigned NOT NULL DEFAULT '0',
+  `ChrSpecializationID` int(11) NOT NULL DEFAULT '0',
+  `SpecSetID` int(11) NOT NULL DEFAULT '0',
+  `VerifiedBuild` smallint(6) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`ID`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;