Trinitycore: DB/Gossip: Add correct broadcast texts and language to some gossip texts part 1

[offl]

Didn't changed 1-500 because it's hard to find proper text for them.
From what I see after checking all these texts the language was changed after adding broadcast texts, I also checked some texts and seems like we can change language too.
Also added missing texts & conditions to portal trainers.

Selfnotes:
These texts must be checked later because I can't change broadcast text since current text and correct text does not match:

SELECT * FROM npc_text WHERE TEXT0_0 LIKE '%then I would be delighted to train you%'

Can't find correct id, since all except one are without verified build, I presume they're incorrect

SELECT * FROM npc_text WHERE TEXT0_0 = 'Greetings, $n.'

--
UPDATE `npc_text` SET `BroadcastTextID0` = 2503, `lang0` = 7 WHERE `ID` = 539;
UPDATE `npc_text` SET `BroadcastTextID0` = 2524 WHERE `ID` = 559;
UPDATE `npc_text` SET `BroadcastTextID0` = 2528, `lang0` = 1 WHERE `ID` = 563;
UPDATE `npc_text` SET `BroadcastTextID0` = 2821, `lang0` = 7 WHERE `ID` = 820;
UPDATE `npc_text` SET `BroadcastTextID0` = 2941, `lang0` = 7 WHERE `ID` = 918;
UPDATE `npc_text` SET `BroadcastTextID0` = 3368 WHERE `ID` = 1231;
UPDATE `npc_text` SET `BroadcastTextID0` = 3371 WHERE `ID` = 1232;
UPDATE `npc_text` SET `BroadcastTextID0` = 3385 WHERE `ID` = 1238;
UPDATE `npc_text` SET `BroadcastTextID0` = 3386 WHERE `ID` = 1239;
UPDATE `npc_text` SET `BroadcastTextID0` = 3410 WHERE `ID` = 1257;
UPDATE `npc_text` SET `BroadcastTextID0` = 3412 WHERE `ID` = 1259;
UPDATE `npc_text` SET `BroadcastTextID0` = 3425, `lang0` = 1 WHERE `ID` = 1272;
UPDATE `npc_text` SET `BroadcastTextID0` = 3458, `lang0` = 1 WHERE `ID` = 1300;
UPDATE `npc_text` SET `BroadcastTextID0` = 3459, `lang0` = 1 WHERE `ID` = 1301;
UPDATE `npc_text` SET `BroadcastTextID0` = 4584 WHERE `ID` = 2193;
UPDATE `npc_text` SET `BroadcastTextID0` = 4857, `lang0` = 1 WHERE `ID` = 2593;
UPDATE `npc_text` SET `BroadcastTextID0` = 4858 WHERE `ID` = 2594;
UPDATE `npc_text` SET `BroadcastTextID0` = 4878 WHERE `ID` = 2599;
UPDATE `npc_text` SET `BroadcastTextID0` = 5077 WHERE `ID` = 2760;
UPDATE `npc_text` SET `BroadcastTextID0` = 5089 WHERE `ID` = 2766;
UPDATE `npc_text` SET `BroadcastTextID0` = 5113 WHERE `ID` = 2793;
UPDATE `npc_text` SET `BroadcastTextID0` = 5240 WHERE `ID` = 2848;
UPDATE `npc_text` SET `BroadcastTextID0` = 7647 WHERE `ID` = 3061;
UPDATE `npc_text` SET `BroadcastTextID0` = 5888 WHERE `ID` = 3541;
UPDATE `npc_text` SET `BroadcastTextID0` = 5901 WHERE `ID` = 3542;
UPDATE `npc_text` SET `BroadcastTextID0` = 5907, `lang0` = 1 WHERE `ID` = 3543;
UPDATE `npc_text` SET `BroadcastTextID0` = 5935 WHERE `ID` = 3550;
UPDATE `npc_text` SET `BroadcastTextID0` = 6479 WHERE `ID` = 3939;
UPDATE `npc_text` SET `BroadcastTextID0` = 6502 WHERE `ID` = 3961;
UPDATE `npc_text` SET `BroadcastTextID0` = 6602 WHERE `ID` = 4035;
UPDATE `npc_text` SET `BroadcastTextID0` = 6617 WHERE `ID` = 4036;
UPDATE `npc_text` SET `BroadcastTextID0` = 6630, `lang0` = 1 WHERE `ID` = 4037;
UPDATE `npc_text` SET `BroadcastTextID0` = 6700 WHERE `ID` = 4069;
UPDATE `npc_text` SET `BroadcastTextID0` = 6705 WHERE `ID` = 4070;
UPDATE `npc_text` SET `BroadcastTextID0` = 6718, `lang0` = 1 WHERE `ID` = 4072;
UPDATE `npc_text` SET `BroadcastTextID0` = 6788, `lang0` = 1 WHERE `ID` = 4097;
UPDATE `npc_text` SET `BroadcastTextID0` = 6906 WHERE `ID` = 4259;
UPDATE `npc_text` SET `BroadcastTextID0` = 7158, `lang0` = 7 WHERE `ID` = 4435;
UPDATE `npc_text` SET `BroadcastTextID0` = 7165 WHERE `ID` = 4437;
UPDATE `npc_text` SET `BroadcastTextID0` = 7163, `lang0` = 1 WHERE `ID` = 4439;
UPDATE `npc_text` SET `BroadcastTextID0` = 7162 WHERE `ID` = 4441;
UPDATE `npc_text` SET `BroadcastTextID0` = 7492, `lang0` = 7 WHERE `ID` = 4835;
UPDATE `npc_text` SET `BroadcastTextID0` = 7493, `lang0` = 7 WHERE `ID` = 4837;
UPDATE `npc_text` SET `BroadcastTextID0` = 7647 WHERE `ID` = 4997;
UPDATE `npc_text` SET `BroadcastTextID0` = 7652 WHERE `ID` = 5001;
UPDATE `npc_text` SET `BroadcastTextID0` = 7660 WHERE `ID` = 5006;
UPDATE `npc_text` SET `BroadcastTextID0` = 8189 WHERE `ID` = 5565;
UPDATE `npc_text` SET `BroadcastTextID0` = 8322 WHERE `ID` = 5739;
UPDATE `npc_text` SET `BroadcastTextID0` = 8364 WHERE `ID` = 5813;
-- Elissa Dumas
UPDATE `npc_text` SET `BroadcastTextID0` = 8440, `lang0` = 7 WHERE `ID` = 5873;
UPDATE `npc_text` SET `BroadcastTextID0` = 8441, `lang0` = 7 WHERE `ID` = 5874;

DELETE FROM `gossip_menu` WHERE `MenuID` = 4821 AND `TextID` = 5874;
INSERT INTO `gossip_menu` (`MenuID`,`TextID`) VALUES
(4821,5874);

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId` = 14 AND `SourceGroup` = 4821;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorType`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(14,4821,5873,0,0,15,0,128,0,0,1,0,0,"","Group 0: Show gossip text 5873 if player is not a mage"),
(14,4821,5874,0,0,15,0,128,0,0,0,0,0,"","Group 0: Show gossip text 5874 if player is a mage");
-- Larimaine Purdue, both texts are used but without conditions
UPDATE `npc_text` SET `BroadcastTextID0` = 8443, `lang0` = 7 WHERE `ID` = 5875;
UPDATE `npc_text` SET `BroadcastTextID0` = 8444, `lang0` = 7 WHERE `ID` = 5876;

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId` = 14 AND `SourceGroup` = 4822;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorType`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(14,4822,5875,0,0,15,0,128,0,0,1,0,0,"","Group 0: Show gossip text 5875 if player is not a mage"),
(14,4822,5876,0,0,15,0,128,0,0,0,0,0,"","Group 0: Show gossip text 5876 if player is a mage");
-- Milstaff Stormeye, conditions already added
UPDATE `npc_text` SET `BroadcastTextID0` = 8445, `lang0` = 7 WHERE `ID` = 5877;
UPDATE `npc_text` SET `BroadcastTextID0` = 8446, `lang0` = 7 WHERE `ID` = 5878;
-- Narinth, Lunaraa, why too low ids are used in TBC? Who used them previously?
-- Why only male text is available in npc_text?
-- There's no other texts with proper language and male or female or only female text
-- Risky.
UPDATE `npc_text` SET `BroadcastTextID0` = 8447, `lang0` = 0 WHERE `ID` = 5879;
UPDATE `npc_text` SET `BroadcastTextID0` = 8448, `lang0` = 0 WHERE `ID` = 5880;

DELETE FROM `gossip_menu` WHERE `MenuID` = 4824 AND `TextID` = 5880;
INSERT INTO `gossip_menu` (`MenuID`,`TextID`) VALUES
(4824,5880);

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId` = 14 AND `SourceGroup` = 4824;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorType`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(14,4824,5879,0,0,15,0,128,0,0,1,0,0,"","Group 0: Show gossip text 5879 if player is not a mage"),
(14,4824,5880,0,0,15,0,128,0,0,0,0,0,"","Group 0: Show gossip text 5880 if player is a mage");
-- Birgitte Cranston, ids and language are already correct, only text is missing
UPDATE `npc_text` SET `BroadcastTextID0` = 8453, `lang0` = 1 WHERE `ID` = 5881;
UPDATE `npc_text` SET `BroadcastTextID0` = 8449, `lang0` = 1 WHERE `ID` = 5882;

DELETE FROM `gossip_menu` WHERE `MenuID` = 4825 AND `TextID` = 5882;
INSERT INTO `gossip_menu` (`MenuID`,`TextID`) VALUES
(4825,5882);
-- Thuul
UPDATE `npc_text` SET `BroadcastTextID0` = 8454, `lang0` = 1 WHERE `ID` = 5883;
UPDATE `npc_text` SET `BroadcastTextID0` = 8455, `lang0` = 1 WHERE `ID` = 5884;
-- Lexington Mortaim
UPDATE `npc_text` SET `BroadcastTextID0` = 8456, `lang0` = 1 WHERE `ID` = 5885;
UPDATE `npc_text` SET `BroadcastTextID0` = 8457, `lang0` = 1 WHERE `ID` = 5886;

DELETE FROM `gossip_menu` WHERE `MenuID` = 4827 AND `TextID` = 5886;
INSERT INTO `gossip_menu` (`MenuID`,`TextID`) VALUES
(4827,5886);

DELETE FROM `conditions` WHERE `SourceTypeOrReferenceId` = 14 AND `SourceGroup` = 4827;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`,`SourceGroup`,`SourceEntry`,`SourceId`,`ElseGroup`,`ConditionTypeOrReference`,`ConditionTarget`,`ConditionValue1`,`ConditionValue2`,`ConditionValue3`,`NegativeCondition`,`ErrorType`,`ErrorTextId`,`ScriptName`,`Comment`) VALUES
(14,4827,5885,0,0,15,0,128,0,0,1,0,0,"","Group 0: Show gossip text 5885 if player is not a mage"),
(14,4827,5886,0,0,15,0,128,0,0,0,0,0,"","Group 0: Show gossip text 5886 if player is a mage");

UPDATE `npc_text` SET `BroadcastTextID0` = 8473 WHERE `ID` = 5918;
UPDATE `npc_text` SET `BroadcastTextID0` = 8802 WHERE `ID` = 6073;
UPDATE `npc_text` SET `BroadcastTextID0` = 8803 WHERE `ID` = 6217;
UPDATE `npc_text` SET `BroadcastTextID0` = 8804 WHERE `ID` = 6218;
UPDATE `npc_text` SET `BroadcastTextID0` = 8805 WHERE `ID` = 6219;
UPDATE `npc_text` SET `BroadcastTextID0` = 8806 WHERE `ID` = 6220;
UPDATE `npc_text` SET `BroadcastTextID0` = 8809 WHERE `ID` = 6223;
UPDATE `npc_text` SET `BroadcastTextID0` = 9132 WHERE `ID` = 6735;
UPDATE `npc_text` SET `BroadcastTextID0` = 9133 WHERE `ID` = 6736;
UPDATE `npc_text` SET `BroadcastTextID0` = 9131 WHERE `ID` = 6737;
UPDATE `npc_text` SET `BroadcastTextID0` = 9148 WHERE `ID` = 6755;
UPDATE `npc_text` SET `BroadcastTextID0` = 9153 WHERE `ID` = 6775;
UPDATE `npc_text` SET `BroadcastTextID0` = 9156 WHERE `ID` = 6777;
UPDATE `npc_text` SET `BroadcastTextID0` = 9210 WHERE `ID` = 6793;
UPDATE `npc_text` SET `BroadcastTextID0` = 9276 WHERE `ID` = 6826;
UPDATE `npc_text` SET `BroadcastTextID0` = 9531 WHERE `ID` = 6948;
UPDATE `npc_text` SET `BroadcastTextID0` = 9555 WHERE `ID` = 6959;

UPDATE `npc_text` SET `BroadcastTextID1` = 3369 WHERE `ID` = 1231;
UPDATE `npc_text` SET `BroadcastTextID1` = 3372 WHERE `ID` = 1232;
-- 6 similar texts, pff
UPDATE `npc_text` SET `BroadcastTextID0` = 3376 WHERE `ID` = 1235;
UPDATE `npc_text` SET `BroadcastTextID0` = 3379 WHERE `ID` = 1236;
UPDATE `npc_text` SET `BroadcastTextID0` = 3382 WHERE `ID` = 1237;
UPDATE `npc_text` SET `BroadcastTextID1` = 3377 WHERE `ID` = 1235;
UPDATE `npc_text` SET `BroadcastTextID1` = 3380 WHERE `ID` = 1236;
UPDATE `npc_text` SET `BroadcastTextID1` = 3383 WHERE `ID` = 1237;
UPDATE `npc_text` SET `BroadcastTextID2` = 3378 WHERE `ID` = 1235;
UPDATE `npc_text` SET `BroadcastTextID2` = 3381 WHERE `ID` = 1236;
UPDATE `npc_text` SET `BroadcastTextID2` = 3384 WHERE `ID` = 1237;

UPDATE `npc_text` a LEFT JOIN `broadcast_text` b ON a.`BroadcastTextID0` = b.`ID` SET a.`text0_1` = b.`Text1` WHERE a.`ID` IN (1272,2593,3543,3961,4037,4072,4097,4259,4437,4439,5001,5006,5879,5880);

Branch: 3.3.5
TC rev. hash/commit: 8675fd756ff9

[offl]

SELECT * FROM creature_template WHERE gossip_menu_id IN (SELECT menuid FROM gossip_menu WHERE textid=4437) ORDER BY faction

This text 'Elune be with you, $c.' is used by undeads and dwarfs and since language is common, Horde players will not understand it
Just to note in case of npc_text I don't change existing text. Only broadcast text id to use closest id to another ids, all texts are the same but language and id are different(for example portal trainers)

This also indeed fixes language issues because without this fix Horde creatures will speak in Common

[offl]

For some reason I completely forgot that I can always compare that stuff between old and newest DB.
Got these errors:

Row 1272 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 3425
Row 2593 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 4857
Row 3543 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 5907
Row 3961 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 6502
Row 4037 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 6630
Row 4072 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 6718
Row 4097 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 6788
Row 4259 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 6906
Row 4437 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 7165
Row 4439 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 7163
Row 5001 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 7652
Row 5006 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 7660
Row 5879 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 8447
Row 5880 in table `npc_text` has mismatch between text0_1 and the corresponding Text1 in `broadcast_text` row 8448

Checked in old DB and now I see that not only the language was changed when broadcast texts were added, but also actual texts, changing back everything.

Surprisingly female creatures still can use male text. In case of creature_text it will not work at all.

[offl]

Just checked alternative texts in 500-7000 (started to check them too only for 7000+) and found only one case-selfnote:

UPDATE `npc_text` SET `BroadcastTextId0`=2750, `BroadcastTextId1`=44595 WHERE `id`= 764;

This update added in https://github.com/TrinityCore/TrinityCore/issues/14810 and I checked old DB and found old text with WDBVerified 11723, also text's id looks too high for classic
Also text 0 is different but its id is correct:

The Bank of Stormwind is located in the Trade District just northwest of the city gates. Here, let me show you on your map.
The bank is located in the Trade District just northwest of the city gates. Here, let me show you on your map.

[illfated]

Nice. I am happy to see that you care about the details.